Thursday, September 4, 2014

Creating a duplicate database on the same host

I was asked to do this, and after my first abortive attempt that some how crashed the source database (see the last post) tried again. This time it worked, very easily, and I have no idea why the first attempt failed.
This was on RAC, so ignore the srvctl commands for the listener and just do a lsnrctl stop and lsnrctl start if you're not on RAC.

DUPDB creation from SRCDB on the same host

Add entry to /etc/oratab (or /var/opt/oracle/oratab)
DUPDB:/u01/app/oracle/product/11.2.0.4/dbhome_1:N

Create basic init ora file
. oraenv
DUPDB
cd $ORACLE_HOME/dbs
vi initDUPDB.ora

DB_NAME=DUPDB
control_files='+DATA/DUPDB/CONTROLFILE/DUPDB.ctl'
db_file_name_convert='+DATA/SRCDB','+DATA/DUPDB'
log_file_name_convert='+REDO/SRCDB','+REDO/DUPDB'
log_file_dest_1='LOCATION=+RECO'
compatible=11.2.0.4
db_unique_name='DUPDB'

compatible is needed otherwise it won’t find the ASM disk group.
ORA-19504: failed to create file "+DATA/dupdb/controlfile/dupdb.ctl"
ORA-17502: ksfdcre:3 Failed to create file +DATA/dupdb/controlfile/dupdb.ctl
ORA-15001: diskgroup "DATA" does not exist or is not mounted
ORA-15204: database version 11.2.0.0.0 is incompatible with diskgroup DATA

Create audit dest
oracle@devsrv[DUPDB]/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs> mkdir /u01/app/oracle/admin/DUPDB
oracle@devsrv[DUPDB]/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs> mkdir /u01/app/oracle/admin/DUPDB/adump

Backup listener.ora and add entry for new database
(SID_DESC = (SID_NAME = DUPDB) (GLOBAL_DBNAME=DUPDB) (ORACLE_HOME = /u01/app/oracle/product/11.2.0.4/dbhome_1))

Stop and restart the listener
srvctl stop listener -n devsrv -l listener
srvctl start listener -n devsrv -l listener

or
lsnrctl stop
lsnrctl start 

Backup the source database (when I tried to do it from the active database it overwrote the source control file and I had to recreate it and recover the database). You will use this backup to create the document.
. oraenv
SRCDB

rman
connect target /
run
{
  allocate channel d1 device type disk format '/backup/%U.bkp';
  backup database include current controlfile;
  backup archivelog all;
  release channel d1;
}


. oraenv
DUPDB

Start the database in nomount mode
sys.DUPDB[DUPDB]> startup nomount
ORACLE instance started.

Total System Global Area  634753024 bytes
Fixed Size                  2255752 bytes
Variable Size             486540408 bytes
Database Buffers          134217728 bytes
Redo Buffers               11739136 bytes

Connect to rman and run the duplicate command
oracle@devsrv[DUPDB]> rman

RMAN> connect target sys/password@//devsrv/SRCDB

connected to target database: SRCDB (DBID=4454375200)

RMAN> connect auxiliary sys/password@//devsrv/DUPDB

connected to auxiliary database: DUPDB (not mounted)

RMAN> duplicate target database to DUPDB;

Starting Duplicate Db at 04-SEP-14
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=64 device type=DISK

contents of Memory Script:
{
   sql clone "create spfile from memory";
}
executing Memory Script

sql statement: create spfile from memory

contents of Memory Script:
{
   shutdown clone immediate;
   startup clone nomount;
}
executing Memory Script

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area     634753024 bytes

Fixed Size                     2255752 bytes
Variable Size                486540408 bytes
Database Buffers             134217728 bytes
Redo Buffers                  11739136 bytes

contents of Memory Script:
{
   sql clone "alter system set  db_name =
 ''SRCDB'' comment=
 ''Modified by RMAN duplicate'' scope=spfile";
   sql clone "alter system set  db_unique_name =
 ''DUPDB'' comment=
 ''Modified by RMAN duplicate'' scope=spfile";
   shutdown clone immediate;
   startup clone force nomount
   restore clone primary controlfile;
   alter clone database mount;
}
executing Memory Script

sql statement: alter system set  db_name =  ''SRCDB'' comment= ''Modified by RMAN duplicate'' scope=spfile

sql statement: alter system set  db_unique_name =  ''DUPDB'' comment= ''Modified by RMAN duplicate'' scope=spfile

Oracle instance shut down

Oracle instance started

Total System Global Area     634753024 bytes

Fixed Size                     2255752 bytes
Variable Size                486540408 bytes
Database Buffers             134217728 bytes
Redo Buffers                  11739136 bytes

Starting restore at 04-SEP-14
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=64 device type=DISK

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: reading from backup piece /backup/02phlndl_1_1.bkp
channel ORA_AUX_DISK_1: piece handle=/backup/02phlndl_1_1.bkp tag=TAG20140904T141003
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03
output file name=+DATA/dupdb/controlfile/dupdb.ctl
Finished restore at 04-SEP-14

database mounted
RMAN-05529: WARNING: DB_FILE_NAME_CONVERT resulted in invalid ASM names; names changed to disk group only.

contents of Memory Script:
{
   set until scn  7668683392612;
   set newname for datafile  1 to
 "+data";
   set newname for datafile  2 to
 "+data";
   set newname for datafile  3 to
 "+data";
.
.
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00001 to +data
.
.
channel ORA_AUX_DISK_1: reading from backup piece /backup/01phlmts_1_1.bkp


Duplicate will continue

Run asmcmd –p and check the files are getting written:
ASMCMD [+DATA/DUPDB/DATAFILE] > ls
ARCHIVE_DEV.3229.857401553
DATA_FIX_TAB01.3228.857401627
OMS_DATA.3237.857401553

RMAN duplicate finished
datafile 41 switched to datafile copy
input datafile copy RECID=40 STAMP=857402281 file name=+DATA/dupdb/datafile/po_simulation.3271.857401757
datafile 42 switched to datafile copy
input datafile copy RECID=41 STAMP=857402281 file name=+DATA/dupdb/datafile/po_simulation_idx.3272.857401757
datafile 43 switched to datafile copy
input datafile copy RECID=42 STAMP=857402281 file name=+DATA/dupdb/datafile/streams_data.3273.857401757
datafile 44 switched to datafile copy
input datafile copy RECID=43 STAMP=857402281 file name=+DATA/dupdb/datafile/ulog_tab01.3274.857401757
datafile 45 switched to datafile copy
input datafile copy RECID=44 STAMP=857402281 file name=+DATA/dupdb/datafile/xdb.3275.857401757

contents of Memory Script:
{
   Alter clone database open resetlogs;
}
executing Memory Script

database opened
Finished Duplicate Db at 04-SEP-14

Put the listener.ora back to remove the static entry and restart
cp listener.ora20140904 listener.ora
srvctl stop listener -n devsrv -l listener
srvctl start listener -n devsrv -l listener

or
lsnrctl stop
lsnrctl start

lsnrctl stat
Service "DUPDB" has 1 instance(s).
  Instance "DUPDB", status READY, has 1 handler(s) for this service...
Service "DUPDBXDB" has 1 instance(s).
  Instance "DUPDB", status READY, has 1 handler(s) for this service...



Copy the SRCDB spfile to a pfile and update the DUPDB parameters
. oraenv
SRCDB
Sqlplus “/ as sysdba”
Create pfile=/tmp/initDUPDB.ora from spfile;

Edit the file and change all the SRCDB to DUPDB and remove references to RAC instances if present:
*.archive_lag_target=900
*.audit_file_dest='/u01/app/oracle/admin/DUPDB/adump'
*.audit_trail='none'
*.cluster_database=FALSE
*.compatible='11.2.0.4.0'
*.control_files='+DATA/DUPDB/controlfile/current.3233.857401533'
*.cursor_sharing='similar'
*.db_block_checksum='true'
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_file_multiblock_read_count=30
*.db_name='DUPDB'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=DUPDBXDB)'
*.fast_start_mttr_target=180
*.filesystemio_options='none'
*.global_names=false
*.job_queue_processes=0
*.log_archive_dest_1='LOCATION=+RECO'
*.log_archive_format='%t_%s_%r.arc'
*.open_cursors=300
*.optimizer_index_caching=80
*.optimizer_index_cost_adj=20
*.pga_aggregate_target=419430400
*.processes=1000
*.remote_listener='xd1-scan1:1521'
*.remote_login_passwordfile='exclusive'
*.session_cached_cursors=60
*.sessions=1105
*.sga_target=2000m
*.shared_pool_reserved_size=19293798
*.timed_os_statistics=10
*.undo_retention=3600

Stop and restart the database and implement the new spfile
NOLOG> startup pfile=/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/initDUPDB.ora
ORACLE instance started.
NOLOG> create spfile='+DATA/DUPDB/PARAMETERFILE/spfileDUPDB.ora' from pfile;

File created.
NOLOG> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

Edit the initDUPDB.ora file to point to the spfile:
SPFILE='+DATA/DUPDB/PARAMETERFILE/spfileDUPDB.ora'

NOLOG> startup
ORACLE instance started.

Total System Global Area 2087780352 bytes
Fixed Size                  2254824 bytes
Variable Size             822085656 bytes
Database Buffers         1241513984 bytes
Redo Buffers               21925888 bytes
Database mounted.
Database opened.

sys.DUPDB[DUPDB]> show parameter spfile

NAME                                 TYPE                             VALUE
------------------------------------ -------------------------------- ------------------------------
spfile                               string                           +DATA/dupdb/parameterfile/spfiledupdb.ora

Make sure the archived logs are pointing to the right location
NOLOG> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            +RECO
Oldest online log sequence     86
Next log sequence to archive   87
Current log sequence           87



All done! 

No comments:

Post a Comment