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!
