Oracle 11g duplicate active database to establish physical DG 02/27 Update SLTechnology News&Howtos

Oracle 11g duplicate active database to establish physical DG

2025-02-27 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >

Shulou(Shulou.com)06/01 Report--

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

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

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

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

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

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

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

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

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.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.

*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.