Oracle 11g duplicate active database to establish physical DG
Environment:
Primary:
OS: Red Hat Enterprise 5.8
IP: 10.131.119.118
DBVersion: 11.2.0.3.0
Db_name: orcl
Db_unique_name: orcl
Standby:
OS: Red Hat Enterprise 5.8
IP: 10.131.119.119
DBVersion: 11.2.0.3.0
Db_name: orcl
Db_unique_name: std
Step 1: set the main library to archive mode and force_logging mode
SQL > shutdown immediate
SQL > startup mount
SQL > alter database archivelog
SQL > alter database open
SQL > alter database force logging
SQL > select DBID,NAME,LOG_MODE,FORCE_LOGGING from v$database
DBID NAME LOG_MODE FOR
1414106639 ORCL ARCHIVELOG YES
Step 2: copy the pfile file and password file of the main library to the standby library
[oracle@CS-SI-DG01 dbs] $sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Wed Jan 27 16:05:21 2016
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0-64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL > create pfile from spfile
File created.
SQL > exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0-64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@CS-SI-DG01 dbs] $scp initorcl.ora 10.131.119.119:/u2/oracle/product/11.2.0/dbhome_1/dbs/initstd.ora
Oracle@10.131.119.119's password:
Stty: standard input: Invalid argument
Initorcl.ora 100% 1112 1.1KB/s 00:00
[oracle@CS-SI-DG01 dbs] $scp orapworcl 10.131.119.119:/u2/oracle/product/11.2.0/dbhome_1/dbs/orapwstd
Oracle@10.131.119.119's password:
Stty: standard input: Invalid argument
Orapworcl 100% 1536 1.5KB/s 00:00
Step 3: set up the listening and TNS of the main library
Monitoring:
[oracle@CS-SI-DG01 admin] $cat listener.ora
# listener.ora Network Configuration File: / u2/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = orcl)
(ORACLE_HOME = / u2/oracle/product/11.2.0/dbhome_1)
(SID_NAME = orcl)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP) (HOST = 10.131.119.118) (PORT = 1521))
)
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC) (KEY = EXTPROC1521))
)
)
ADR_BASE_LISTENER = / u2/oracle
[oracle@CS-SI-DG01 ~] $lsnrctl reload
TNS:
[oracle@CS-SI-DG01 admin] $cat tnsnames.ora
# tnsnames.ora Network Configuration File: / u2/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP) (HOST = 10.131.119.118) (PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
Std =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP) (HOST = 10.131.119.119) (PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = std)
)
)
Step 4: modify the parameters of the main library
[oracle@CS-SI-DG01 admin] $sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Wed Jan 27 16:19:40 2016
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0-64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL > alter system set log_archive_config='dg_config= (orcl,std)'
System altered.
SQL > alter system set log_archive_dest_2='SERVICE=std ASYNC VALID_FOR= (ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=std'
System altered.
SQL > alter system set log_archive_dest_state_2=enable
System altered.
SQL > alter system set log_archive_dest_state_1=enable
System altered.
SQL > alter system set fal_server='std'
System altered.
SQL > alter system set fal_client='orcl'
System altered.
SQL > alter system set standby_file_management='auto'
System altered.
Note: restart the database after modifying the parameters
Step 5: modify the parameter file of the standby library
[oracle@CS-SI-DG02 dbs] $cat initstd.ora
*. _ undo_autotune=TRUE
* .audit_file_dest='/u2/oracle/admin/std/adump'
* .audit_trail='db'
* .compatible='11.2.0.0.0'
* .control_files='/oradata/std/control01.ctl','/oradata/std/control02.ctl'#Restore Controlfile
* .db_block_size=8192
* .db_domain=''
* .db_name='orcl'
* .db_recovery_file_dest='/u2/oracle/fast_recovery_area'
* .db_recovery_file_dest_size=8589934592
* .diagnostic_dest='/u2/oracle'
* .dispatchers=' (PROTOCOL=TCP) (SERVICE=stdXDB)'
* .job_queue_processes=1000
* .log_archive_dest_1='location=/oradata/arch/std'
* .log_archive_dest_10=''
* .memory_max_target=0
* .memory_target=0
* .open_cursors=300
* .pga_aggregate_target=171966464
* .processes=150
* .remote_login_passwordfile='EXCLUSIVE'
* .session_cached_cursors=240
* .sga_target=335544320
* .undo_retention=60
* .undo_tablespace='UNDOTBS2'
* .log_archive_dest_2='SERVICE=orcl ASYNC VALID_FOR= (ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcl'
* .log_archive_config='dg_config= (orcl,std)'
* .log_archive_dest_state_2=enable
* .log_archive_dest_state_1=enable
* .fal_server='orcl'
* .fal_client='std'
* .standby_file_management='auto'
* .log_file_name_convert= ('/ oradata/orcl','/oradata/std')
* .db_file_name_convert= ('/ oradata/orcl','/oradata/std')
* .db_unique_name='std'
Step 6: create a directory in the repository
[oracle@CS-SI-DG02 dbs] $mkdir-p / u2/oracle/admin/std/adump
[oracle@CS-SI-DG02 dbs] $mkdir-p / oradata/std/
[oracle@CS-SI-DG02 dbs] $mkdir-p / oradata/arch/std
Step 7: set the listening and TNS of the slave database
Monitoring:
[oracle@CS-SI-DG02 admin] $cat listener.ora
# listener.ora Network Configuration File: / u2/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = std)
(ORACLE_HOME = / u2/oracle/product/11.2.0/dbhome_1)
(SID_NAME = std)
)
)
ADR_BASE_LISTENER = / u2/oracle
STENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP) (HOST = 10.131.119.119) (PORT = 1521))
)
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC) (KEY = EXTPROC1521))
)
)
[oracle@CS-SI-DG02 admin] $lsnrctl reload
TNS:
[oracle@CS-SI-DG02 admin] $cat tnsnames.ora
# tnsnames.ora Network Configuration File: / u2/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
ORCL =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP) (HOST = 10.131.119.118) (PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = orcl)
)
)
Std =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP) (HOST = 10.131.119.119) (PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = std)
)
)
Step 8: start the slave library to the nomount state
[oracle@CS-SI-DG02 admin] $export ORACLE_SID=std
[oracle@CS-SI-DG02 admin] $sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Wed Jan 27 16:56:58 2016
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to an idle instance.
SQL > startup nomount
ORACLE instance started.
Total System Global Area 509411328 bytes
Fixed Size 2229824 bytes
Variable Size 285215168 bytes
Database Buffers 213909504 bytes
Redo Buffers 8056832 bytes
Step 9: restore the database in the standby database
[oracle@CS-SI-DG02 admin] $rman target sys/oracle@orcl auxiliary sys/oracle@std nocatalog
Recovery Manager: Release 11.2.0.3.0-Production on Wed Jan 27 17:30:29 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to target database: ORCL (DBID=1414106639)
Using target database control file instead of recovery catalog
Connected to auxiliary database: ORCL (not mounted)
RMAN > duplicate target database for standby from active database
Starting Duplicate Db at 27-JAN-16
Allocated channel: ORA_AUX_DISK_1
Channel ORA_AUX_DISK_1: SID=134 device type=DISK
Contents of Memory Script:
{
Backup as copy reuse
Targetfile'/ U2Universe oracle.productUniverse 11.2.0; dbhomeowners 1; dbsUniplex; orapworcl' auxiliary format
'/ u2Universe oracleActionproductUniverse 11.2.0 Universe dbhomeowners 1Compact dbsUniverse orapwstd`
}
Executing Memory Script
Starting backup at 27-JAN-16
Allocated channel: ORA_DISK_1
Channel ORA_DISK_1: SID=22 device type=DISK
Finished backup at 27-JAN-16
Contents of Memory Script:
{
Backup as copy current controlfile for standby auxiliary format'/ oradata/std/control01.ctl'
Restore clone controlfile to'/ oradata/std/control02.ctl' from
'/ oradata/std/control01.ctl'
}
Executing Memory Script
Starting backup at 27-JAN-16
Using channel ORA_DISK_1
Channel ORA_DISK_1: starting datafile copy
Copying standby control file
Output file name=/u2/oracle/product/11.2.0/dbhome_1/dbs/snapcf_orcl.f tag=TAG20160127T173104 RECID=25 STAMP=902251864
Channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 27-JAN-16
Starting restore at 27-JAN-16
Using channel ORA_AUX_DISK_1
Channel ORA_AUX_DISK_1: copied control file copy
Finished restore at 27-JAN-16
Contents of Memory Script:
{
Sql clone 'alter database mount standby database'
}
Executing Memory Script
Sql statement: alter database mount standby database
Contents of Memory Script:
{
Set newname for tempfile 1 to
"/ oradata/std/temp01.dbf"
Switch clone tempfile all
Set newname for datafile 1 to
"/ oradata/std/system01.dbf"
Set newname for datafile 2 to
"/ oradata/std/sysaux01.dbf"
Set newname for datafile 3 to
"/ oradata/std/test01.dbf"
Set newname for datafile 4 to
"/ oradata/std/users01.dbf"
Set newname for datafile 6 to
"/ oradata/std/undotbs02.dbf"
Set newname for datafile 7 to
"/ oradata/std/tbspart01.dbf"
Set newname for datafile 8 to
"/ oradata/std/tbspart02.dbf"
Set newname for datafile 9 to
"/ oradata/std/tbspart03.dbf"
Set newname for datafile 11 to
"/ oradata/std/assm01.dbf"
Set newname for datafile 12 to
"/ oradata/std/tbspart04.dbf"
Set newname for datafile 13 to
"/ oradata/std/rman01.dbf"
Set newname for datafile 14 to
"/ oradata/std/test1.dbf"
Set newname for datafile 15 to
"/ oradata/std/test2.dbf"
Set newname for datafile 16 to
"/ oradata/std/linktbs01.dbf"
Set newname for datafile 17 to
"/ oradata/std/ogg01.dbf"
Set newname for datafile 18 to
"/ oradata/std/tom101.dbf"
Backup as copy reuse
Datafile 1 auxiliary format
"/ oradata/std/system01.dbf" datafile
2 auxiliary format
"/ oradata/std/sysaux01.dbf" datafile
3 auxiliary format
"/ oradata/std/test01.dbf" datafile
4 auxiliary format
"/ oradata/std/users01.dbf" datafile
6 auxiliary format
"/ oradata/std/undotbs02.dbf" datafile
7 auxiliary format
"/ oradata/std/tbspart01.dbf" datafile
8 auxiliary format
"/ oradata/std/tbspart02.dbf" datafile
9 auxiliary format
"/ oradata/std/tbspart03.dbf" datafile
11 auxiliary format
"/ oradata/std/assm01.dbf" datafile
12 auxiliary format
"/ oradata/std/tbspart04.dbf" datafile
13 auxiliary format
"/ oradata/std/rman01.dbf" datafile
14 auxiliary format
"/ oradata/std/test1.dbf" datafile
15 auxiliary format
"/ oradata/std/test2.dbf" datafile
16 auxiliary format
"/ oradata/std/linktbs01.dbf" datafile
17 auxiliary format
"/ oradata/std/ogg01.dbf" datafile
18 auxiliary format
"/ oradata/std/tom101.dbf"
Sql 'alter system archive log current'
}
Executing Memory Script
Executing command: SET NEWNAME
Renamed tempfile 1 to / oradata/std/temp01.dbf in control file
Executing command: SET NEWNAME
Executing command: SET NEWNAME
Executing command: SET NEWNAME
Executing command: SET NEWNAME
Executing command: SET NEWNAME
Executing command: SET NEWNAME
Executing command: SET NEWNAME
Executing command: SET NEWNAME
Executing command: SET NEWNAME
Executing command: SET NEWNAME
Executing command: SET NEWNAME
Executing command: SET NEWNAME
Executing command: SET NEWNAME
Executing command: SET NEWNAME
Executing command: SET NEWNAME
Executing command: SET NEWNAME
Starting backup at 27-JAN-16
Using channel ORA_DISK_1
Channel ORA_DISK_1: starting datafile copy
Input datafile file number=00002 name=/oradata/orcl/sysaux01.dbf
Output file name=/oradata/std/sysaux01.dbf tag=TAG20160127T173112
Channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:06
Channel ORA_DISK_1: starting datafile copy
Input datafile file number=00001 name=/oradata/orcl/system01.dbf
Output file name=/oradata/std/system01.dbf tag=TAG20160127T173112
Channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:46
Channel ORA_DISK_1: starting datafile copy
Input datafile file number=00006 name=/oradata/orcl/undotbs02.dbf
Output file name=/oradata/std/undotbs02.dbf tag=TAG20160127T173112
Channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
Channel ORA_DISK_1: starting datafile copy
Input datafile file number=00003 name=/oradata/orcl/test01.dbf
Output file name=/oradata/std/test01.dbf tag=TAG20160127T173112
Channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
Channel ORA_DISK_1: starting datafile copy
Input datafile file number=00011 name=/oradata/orcl/assm01.dbf
Output file name=/oradata/std/assm01.dbf tag=TAG20160127T173112
Channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
Channel ORA_DISK_1: starting datafile copy
Input datafile file number=00017 name=/oradata/orcl/ogg01.dbf
Output file name=/oradata/std/ogg01.dbf tag=TAG20160127T173112
Channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
Channel ORA_DISK_1: starting datafile copy
Input datafile file number=00018 name=/oradata/orcl/tom101.dbf
Output file name=/oradata/std/tom101.dbf tag=TAG20160127T173112
Channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
Channel ORA_DISK_1: starting datafile copy
Input datafile file number=00004 name=/oradata/orcl/users01.dbf
Output file name=/oradata/std/users01.dbf tag=TAG20160127T173112
Channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
Channel ORA_DISK_1: starting datafile copy
Input datafile file number=00007 name=/oradata/orcl/tbspart01.dbf
Output file name=/oradata/std/tbspart01.dbf tag=TAG20160127T173112
Channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Channel ORA_DISK_1: starting datafile copy
Input datafile file number=00008 name=/oradata/orcl/tbspart02.dbf
Output file name=/oradata/std/tbspart02.dbf tag=TAG20160127T173112
Channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Channel ORA_DISK_1: starting datafile copy
Input datafile file number=00009 name=/oradata/orcl/tbspart03.dbf
Output file name=/oradata/std/tbspart03.dbf tag=TAG20160127T173112
Channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Channel ORA_DISK_1: starting datafile copy
Input datafile file number=00012 name=/oradata/orcl/tbspart04.dbf
Output file name=/oradata/std/tbspart04.dbf tag=TAG20160127T173112
Channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Channel ORA_DISK_1: starting datafile copy
Input datafile file number=00013 name=/oradata/orcl/rman01.dbf
Output file name=/oradata/std/rman01.dbf tag=TAG20160127T173112
Channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Channel ORA_DISK_1: starting datafile copy
Input datafile file number=00014 name=/oradata/orcl/test1.dbf
Output file name=/oradata/std/test1.dbf tag=TAG20160127T173112
Channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Channel ORA_DISK_1: starting datafile copy
Input datafile file number=00015 name=/oradata/orcl/test2.dbf
Output file name=/oradata/std/test2.dbf tag=TAG20160127T173112
Channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Channel ORA_DISK_1: starting datafile copy
Input datafile file number=00016 name=/oradata/orcl/linktbs01.dbf
Output file name=/oradata/std/linktbs01.dbf tag=TAG20160127T173112
Channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 27-JAN-16
Sql statement: alter system archive log current
Contents of Memory Script:
{
Switch clone datafile all
}
Executing Memory Script
Datafile 1 switched to datafile copy
Input datafile copy RECID=25 STAMP=902252046 file name=/oradata/std/system01.dbf
Datafile 2 switched to datafile copy
Input datafile copy RECID=26 STAMP=902252046 file name=/oradata/std/sysaux01.dbf
Datafile 3 switched to datafile copy
Input datafile copy RECID=27 STAMP=902252046 file name=/oradata/std/test01.dbf
Datafile 4 switched to datafile copy
Input datafile copy RECID=28 STAMP=902252046 file name=/oradata/std/users01.dbf
Datafile 6 switched to datafile copy
Input datafile copy RECID=29 STAMP=902252046 file name=/oradata/std/undotbs02.dbf
Datafile 7 switched to datafile copy
Input datafile copy RECID=30 STAMP=902252046 file name=/oradata/std/tbspart01.dbf
Datafile 8 switched to datafile copy
Input datafile copy RECID=31 STAMP=902252046 file name=/oradata/std/tbspart02.dbf
Datafile 9 switched to datafile copy
Input datafile copy RECID=32 STAMP=902252046 file name=/oradata/std/tbspart03.dbf
Datafile 11 switched to datafile copy
Input datafile copy RECID=33 STAMP=902252046 file name=/oradata/std/assm01.dbf
Datafile 12 switched to datafile copy
Input datafile copy RECID=34 STAMP=902252046 file name=/oradata/std/tbspart04.dbf
Datafile 13 switched to datafile copy
Input datafile copy RECID=35 STAMP=902252046 file name=/oradata/std/rman01.dbf
Datafile 14 switched to datafile copy
Input datafile copy RECID=36 STAMP=902252046 file name=/oradata/std/test1.dbf
Datafile 15 switched to datafile copy
Input datafile copy RECID=37 STAMP=902252046 file name=/oradata/std/test2.dbf
Datafile 16 switched to datafile copy
Input datafile copy RECID=38 STAMP=902252046 file name=/oradata/std/linktbs01.dbf
Datafile 17 switched to datafile copy
Input datafile copy RECID=39 STAMP=902252046 file name=/oradata/std/ogg01.dbf
Datafile 18 switched to datafile copy
Input datafile copy RECID=40 STAMP=902252046 file name=/oradata/std/tom101.dbf
Finished Duplicate Db at 27-JAN-16
Step 10: create a standby log in the repository
[oracle@CS-SI-DG02 dbs] $sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Wed Jan 27 18:06:26 2016
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0-64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL > select open_mode from v$database
OPEN_MODE
-
MOUNTED
SQL > select * from v$logfile
GROUP# STATUS TYPE MEMBER IS_
-
1 ONLINE / oradata/std/redo03.log NO
2 ONLINE / oradata/std/redo02.log NO
3 ONLINE / oradata/std/redo01.log NO
3 rows selected.
SQL > alter database add standby logfile group 4'/ oradata/std/stb_redo04.log' size 50m
Database altered.
SQL > alter database add standby logfile group 5'/ oradata/std/stb_redo5.log' size 50m
Database altered.
SQL > alter database add standby logfile group 6'/ oradata/std/stb_redo06.log' size 50m
2
Database altered.
SQL > alter database add standby logfile group 7'/ oradata/std/stb_redo07.log' size 50m
Database altered.
SQL > select * from v$logfile
GROUP# STATUS TYPE MEMBER IS_
-
1 ONLINE / oradata/std/redo03.log NO
2 ONLINE / oradata/std/redo02.log NO
3 ONLINE / oradata/std/redo01.log NO
4 STANDBY / oradata/std/stb_redo04.log NO
5 STANDBY / oradata/std/stb_redo5.log NO
6 STANDBY / oradata/std/stb_redo06.log NO
7 STANDBY / oradata/std/stb_redo07.log NO
7 rows selected.
Step 11: start DG
[oracle@CS-SI-DG02 admin] $sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Wed Jan 27 18:19:34 2016
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0-64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL > alter database open read only
Database altered.
SQL > alter database recover managed standby database disconnect from session using current logfile
Database altered.
