How to clone Oracle Software fast and easy

This is the approach I’m usually taking to clone the Oracle Software from one server to another.

[oracle@PROD product]$ zip -r 11204_clone.zip 11.2.0.4/
 adding: 11.2.0.4/ (stored 0%)
 adding: 11.2.0.4/owm/ (stored 0%)
 adding: 11.2.0.4/owm/jlib/ (stored 0%)
 adding: 11.2.0.4/owm/jlib/owm-images.jar (deflated 2%)

..........

  adding: 11.2.0.4/bin/okinit0 (stored 0%)  adding: 11.2.0.4/bin/okinit0 (stored 0%)  adding: 11.2.0.4/bin/nmozip warning: Permission denied zip warning: could not open for reading: 11.2.0.4/bin/nmo  adding: 11.2.0.4/bin/agtctlO (stored 0%)  adding: 11.2.0.4/bin/nmcbufp (deflated 65%)  adding: 11.2.0.4/bin/impdpO (stored 0%)

...........

adding: 11.2.0.4/odbc/mesg/oraodbcus.msb (deflated 68%)
 adding: 11.2.0.4/odbc/mesg/oraodbcja.msb (deflated 68%)

zip warning: Not all files were readable
 files/entries read: 40416 (4.9G bytes) skipped: 3 (144K bytes)
[oracle@PROD product]$ exit

Now send the zip file to the target server:

[oracle@PROD software]$ scp /u01/app/oracle/product/11204_clone.zip DEV:/tmp

Create the same oracle path for the ORACLE_HOME

mkdir -p /u01/app/oracle/product

Unzip the archive to previously created folder:

unzip 11204_clone.zip -d /u01/app/oracle/product/

Navigate to this clone folder within the unzipped oracle software:

cd /u01/app/oracle/product/11.2.0.4/clone/bin

[oracle@DEV bin]$ pwd
/u01/app/oracle/product/11.2.0.4/clone/bin
[oracle@DEV bin]$ perl clone.pl ORACLE_BASE=/u01/app/oracle ORACLE_HOME=/u01/app/oracle/product/11.2.0.4 ORACLE_HOME_NAME=OH_11204
./runInstaller -clone -waitForCompletion "ORACLE_BASE=/u01/app/oracle" "ORACLE_HOME=/u01/app/oracle/product/11.2.0.4" "ORACLE_HOME_NAME=OH_11204" -silent -noConfig -nowait 
Starting Oracle Universal Installer...

Checking swap space: must be greater than 500 MB. Actual 20479 MB Passed
Preparing to launch Oracle Universal Installer from /tmp/OraInstall2017-10-11_03-40-32PM. Please wait ...Oracle Universal Installer, Version 11.2.0.4.0 Production
Copyright (C) 1999, 2013, Oracle. All rights reserved.

You can find the log of this install session at:
 /u01/app/oraInventory/logs/cloneActions2017-10-11_03-40-32PM.log
.................................................................................................... 100% Done.




Installation in progress (Wednesday, October 11, 2017 3:40:39 PM UTC)
.............................................................................. 78% Done.
Install successful

Linking in progress (Wednesday, October 11, 2017 3:40:42 PM UTC)
Link successful

Setup in progress (Wednesday, October 11, 2017 3:41:02 PM UTC)
Setup successful

End of install phases.(Wednesday, October 11, 2017 3:41:24 PM UTC)
WARNING:A new inventory has been created in this session. However, it has not yet been registered as the central inventory of this system.
To register the new inventory please run the script '/u01/app/oraInventory/orainstRoot.sh' with root privileges. 
If you do not register the inventory, you may not be able to update or patch the products you installed.
The following configuration scripts need to be executed as the "root" user.
/u01/app/oraInventory/orainstRoot.sh
/u01/app/oracle/product/11.2.0.4/root.sh
To execute the configuration scripts:
 1. Open a terminal window
 2. Log in as "root"
 3. Run the scripts
 
The cloning of OH_11204 was successful.
Please check '/u01/app/oraInventory/logs/cloneActions2017-10-11_03-40-32PM.log' for more details.
[oracle@DEV bin]$ /u01/app/oracle/product/11.2.0.4/OPatch/opatch lsinventory
Oracle Interim Patch Installer version 11.2.0.3.12
Copyright (c) 2017, Oracle Corporation. All rights reserved.




Oracle Home : /u01/app/oracle/product/11.2.0.4
Central Inventory : /u01/app/oraInventory
 from : /u01/app/oracle/product/11.2.0.4/oraInst.loc
OPatch version : 11.2.0.3.12
OUI version : 11.2.0.4.0
Log file location : /u01/app/oracle/product/11.2.0.4/cfgtoollogs/opatch/opatch2017-10-11_15-43-56PM_1.log

Lsinventory Output file location : /u01/app/oracle/product/11.2.0.4/cfgtoollogs/opatch/lsinv/lsinventory2017-10-11_15-43-56PM.txt

Execute the 2 scripts as root and you’re done:

/u01/app/oraInventory/orainstRoot.sh
/u01/app/oracle/product/11.2.0.4/root.sh

 

End of story.

Advertisements

ORA-16631: operation requires shutdown of database or instance

On short, after upgrading one of my databases from 11203 to 11204, I’ve noticed that the standby database was in a DISABLED state:

 

DGMGRL> show configuration

Configuration - DG_CONFIG

Protection Mode: MaxPerformance
 Databases:
DB_PRI- Primary database
 DB_STANDBY - Physical standby database (disabled)

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

 

I tried to set it to ENABLED state like this:

DGMGRL> enable database 'DB_STANDBY'
Error: ORA-16631: operation requires shutdown of database or instance ""

Failed.
DGMGRL> exit

To fix this, I’ve found the following document on Metalink:

Ora-16631: Operation Requires Shutdown Of Database Or Instance “” On Physical Standby (Doc ID 1258074.1)

DGMGRL> show configuration

Configuration - DG_CONFIG

Protection Mode: MaxPerformance
 Databases:
 DB_PRI - Primary database
 DB_STANDBY - Physical standby database (disabled)

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

DGMGRL> edit database 'DB_STANDBY' set state='ONLINE';
Error: ORA-16635: network connection failed during transmission

Failed.
DGMGRL> show configuration

Configuration - DG_CONFIG

Protection Mode: MaxPerformance
 Databases:
 DB_PRI - Primary database
 DB_STANDBY - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

DGMGRL>

 

ORA-01450 maximum key length (3118) exceeded

All of a sudden, deploying a new version of the apps, I got this error. The fix is simple. The error is related to the block size of the tablespace where you want to deploy your object. In my case it was about the creation of an index an the key was exceeding the size of the default block size of the default tablespace. All I had to do is to enable the 16K block size and to create a new tablespace using the 16k block size.

SQL> ALTER TABLE "USER"."TABLE1" ADD CONSTRAINT "CONSTR_UK" UNIQUE ("COL") USING INDEX TABLESPACE "TBS_DEFAULT";
ALTER TABLE "USER"."TABLE1" ADD CONSTRAINT "CONSTR_UK" UNIQUE ("COL") USING INDEX TABLESPACE "TBS_DEFAULT"
*
ERROR at line 1:
ORA-01450: maximum key length (3118) exceeded

SQL> show parameter db_16

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_16k_cache_size big integer 0
SQL> alter system set db_16k_cache_size=500M;

System altered.

SQL> CREATE TABLESPACE "NEW_TBS" DATAFILE ...... SIZE 100M BLOCKSIZE 16K;

Tablespace created.

SQL> ALTER TABLE "USER"."TABLE1" ADD CONSTRAINT "CONSTR_UK" UNIQUE ("COL") USING INDEX TABLESPACE "NEW_TBS";

Table altered.

SQL>

 

 

DIA-48322: Relation [INCIDENT] of ADR V[2] incompatible with V[2] tool

I got this issue while using ADRCI tool to maintain the logs. This is how I fixed it:

[oracle@dbserver ~]$ adrci

ADRCI: Release 11.2.0.3.0 - Production on Wed May 31 09:43:29 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

ADR base = "/u01/app/oracle"
adrci> show homes
ADR Homes: 
diag/diagtool/user_oracle/host_3933716914_11
diag/clients/user_oracle/host_3933716914_11
diag/rdbms/dbinst/DBINST
diag/tnslsnr/dbserver/listener
adrci> set home diag/diagtool/user_oracle/host_3933716914_11
adrci> purge
DIA-48322: Relation [INCIDENT] of ADR V[2] incompatible with V[2] tool
DIA-48210: Relation Not Found
DIA-48166: error with opening ADR block file because file does not exist [/u01/app/oracle/diag/diagtool/user_oracle/host_3933716914_11/metadata/INCIDENT.ams] [0]

adrci> migrate schema
Schema migrated.
adrci> purge
adrci> quit

 

Database upgrade from 10.2.0.5.0 to 11.2.0.4

The following document is the best one you can start documenting yourself from:

11gr2-upgrade-bp-apr2012-1610082.pdf

From the above document, I followed:

  • Note 251.1: Database Upgrades from 10.2 to 11.2

On that note, on the PLAN tab, I followed:

Complete Checklist for Manual Upgrades to 11gR2 (Doc ID 837570.1)

In this document you’ll find a bunch of steps to be followed. The most important one is the pre-upgrade one, which you have to run it on the SOURCE database. This script is meant to display back to you a lot of useful information in respect to what needs to be changed/adapted on the source (prior upgrade) or on the target (after the upgrade). The script can be found here:

$ORACLE_HOME/rdbms/admin/utlu112i.sql

If you run the upgrade on the same server as the source database is on, it’s better if you copy this file in a different place like /tmp or HOME folder.

In my case, between other advices, I got the following warnings:

**********************************************************************
Miscellaneous Warnings
**********************************************************************
WARNING: --> Database is using a timezone file older than version 14.
.... After the release migration, it is recommended that DBMS_DST package
.... be used to upgrade the 10.2.0.5.0 database timezone version
.... to the latest version which comes with the new release.
WARNING: --> Your recycle bin contains 4 object(s).
.... It is REQUIRED that the recycle bin is empty prior to upgrading
.... your database. The command:
 PURGE DBA_RECYCLEBIN
.... must be executed immediately prior to executing your upgrade.
WARNING: --> JOB_QUEUE_PROCESS value must be updated
.... Your current setting of "20" is too low.

.... Starting with Oracle Database 11g Release 2 (11.2), setting
.... JOB_QUEUE_PROCESSES to 0 causes both DBMS_SCHEDULER and
.... DBMS_JOB jobs to not run. Previously, setting JOB_QUEUE_PROCESSES
.... to 0 caused DBMS_JOB jobs to not run, but DBMS_SCHEDULER jobs were
.... unaffected and would still run. This parameter must be updated to
.... a value greater than 48 (default value is 1000) prior to upgrade.
.... Not doing so will affect the running of utlrp.sql after the upgrade
.
**********************************************************************

As you can see, the first advice should be executed on the TARGET database whereas the last 2 on the SOURCE database.

SQL> EXECUTE dbms_stats.gather_dictionary_stats;

PL/SQL procedure successfully completed.
SQL> alter system set job_queue_processes=100 scope=both;

System altered.

SQL> PURGE DBA_RECYCLEBIN;

DBA Recyclebin purged.

Now I just run the usual scripts in order to upgrade to 11204 version:

cd $ORACLE_HOME/rdbms/admin

sqlplus /nolog
conn / as sysdba
STARTUP UPGRADE
SPOOL upgrade11204_DB.log
@catupgrd.sql

-- perform upgrade actions that do not require the database to be in UPGRADE mode
@catuppst.sql

@$ORACLE_HOME/ctx/admin/ctxf102.sql
@utlrp.sql

-- Check for invalid entries:
col comp_name format a80
set linesize 200
set pagesize 2000
SELECT COMP_NAME,VERSION,STATUS FROM DBA_REGISTRY;

-- Check for invalid objects:
SELECT count(*) FROM dba_invalid_objects;
select owner, object_type, object_name from dba_invalid_objects;

### CPU Patch (if that's the case)

@catbundle cpu apply
@utlrp

In the end the, upgrade was successful. However, I still need to fix the timezone issue which appeared in the upgrade check script in the very first step:

SQL> SELECT version FROM v$timezone_file;

VERSION
----------
 4

SQL> SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME LIKE 'DST_%' ORDER BY PROPERTY_NAME;

PROPERTY_NAME
------------------------------
VALUE
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
DST_PRIMARY_TT_VERSION
4

DST_SECONDARY_TT_VERSION
0

DST_UPGRADE_STATE
NONE


SQL> !ls -ltr $ORACLE_HOME/oracore/zoneinfo/time*14*
-rw-r--r-- 1 oracle oinstall 344448 Jul 16 2013 /u01/app/oracle/product/11.2.0.4/oracore/zoneinfo/timezone_14.dat
-rw-r--r-- 1 oracle oinstall 791430 Jul 16 2013 /u01/app/oracle/product/11.2.0.4/oracore/zoneinfo/timezlrg_14.dat

SQL> exec DBMS_DST.BEGIN_PREPARE(14);
A prepare window has been successfully started.

PL/SQL procedure successfully completed.

SQL> SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME LIKE 'DST_%' ORDER BY PROPERTY_NAME;

PROPERTY_NAME
------------------------------
VALUE
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
DST_PRIMARY_TT_VERSION
4

DST_SECONDARY_TT_VERSION
14

DST_UPGRADE_STATE
PREPARE


SQL> TRUNCATE TABLE SYS.DST$TRIGGER_TABLE;

Table truncated.

SQL> TRUNCATE TABLE sys.dst$affected_tables;

Table truncated.

SQL> TRUNCATE TABLE sys.dst$error_table;

Table truncated.

SQL> BEGIN
DBMS_DST.FIND_AFFECTED_TABLES
(affected_tables => 'sys.dst$affected_tables',
log_errors => TRUE,
log_errors_table => 'sys.dst$error_table');
END;
/


PL/SQL procedure successfully completed.

SQL> SQL> SELECT * FROM sys.dst$affected_tables;

no rows selected

SQL> EXEC DBMS_DST.END_PREPARE;
A prepare window has been successfully ended.

PL/SQL procedure successfully completed.

SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup upgrade
ORACLE instance started.

Total System Global Area 4275781632 bytes
Fixed Size 2260088 bytes
Variable Size 1090519944 bytes
Database Buffers 3170893824 bytes
Redo Buffers 12107776 bytes
Database mounted.
Database opened.
SQL> spool DST_upgrade.log
SQL> EXEC DBMS_DST.BEGIN_UPGRADE(14);

PL/SQL procedure successfully completed.

SQL> SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME LIKE 'DST_%' ORDER BY PROPERTY_NAME;

PROPERTY_NAME
------------------------------
VALUE
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
DST_PRIMARY_TT_VERSION
14

DST_SECONDARY_TT_VERSION
4

DST_UPGRADE_STATE
UPGRADE


SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 4275781632 bytes
Fixed Size 2260088 bytes
Variable Size 1090519944 bytes
Database Buffers 3170893824 bytes
Redo Buffers 12107776 bytes
Database mounted.
Database opened.
SQL> SELECT version FROM v$timezone_file;

VERSION
----------
 14