Network Security Internet Technology Development Database Servers Mobile Phone Android Software Apple Software Computer Software News IT Information

In addition to Weibo, there is also WeChat

Please pay attention

WeChat public account

Shulou

Oracle11gR2 uses RMAN duplicate to replicate the database-- active database duplicate

2025-01-23 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >

Share

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

11gR2's RMAN duplicate feels much more advanced than 10g, which needs to be replicated on the basis of rman backup, using RMAN duplicate to create a database with identical data but different DBID. 11g RMAN duplicate can be realized by Active database duplicate and Backup-based duplicate. The test here uses Active database duplicate, because Active database duplicate is powerful, so there is no need to backup the target database with rman first. As long as the target database is in archive mode, it can directly copy the database through the network, and automatically open the database after the completion of copy. This has obvious advantages for big data, especially for T-level databases, because there is no need for backup before replication, which reduces the time for backup and backup transmission, and saves backup space. Let's do the specific duplicate operation.

Application scenarios:

1. The old library can be used and the network is smooth

Experimental environment:

Target db:

Ip 192.168.56.10

Oracle_sid=mydb

Oracle_version=11.2.0.3

Auxiliary db:

Ip 192.168.56.150

Oracle_sid=oradu

Oracle_version=11.2.0.3

1. Create a parameter file in the new library and start the instance to nomount status

-- execute on auxiliary db

[oracle@localhost ~] $cat initoradu.ora

Db_name=oradu

Db_block_size=8192

Db_file_name_convert= ('/ u01Accord oradataPlacement')

Log_file_name_convert= ('/ u01Accord oradataPlacement')

-- because different instances are used here, db_file_name_convert and log_file_name_convert must be added, otherwise an error will be reported during replication and the data file cannot be created. If the data file is copied with the same instance name and the two data directories are exactly the same, these two parameters can be omitted.

-- data files for creating new libraries on auxiliary db are in the directory where they are stored.

Mkdir-p / u01/app/oracle/oradata/oradu/

[oracle@localhost ~] $export ORACLE_SID=oradu

[oracle@localhost ~] $sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Sun Mar 20 12:56:36 2016

Copyright (c) 1982, 2011, Oracle. All rights reserved.

Connected to an idle instance.

SQL > startup nomount pfile=/home/oracle/initoradu.ora

ORACLE instance started.

Total System Global Area 238034944 bytes

Fixed Size 2227136 bytes

Variable Size 180356160 bytes

Database Buffers 50331648 bytes

Redo Buffers 5120000 bytes

SQL >

2. Create a password file

-- the passwords of target DB and auxiliary DB must be kept consistent. Here, I directly copy the password file of target db to the corresponding directory of auxiliary db and rename it.

-- execute on target db

[oracle@localhost ~] $scp / u01/app/oracle/product/11.2.0/db/dbs/orapwmydb oracle@192.168.56.150:/u01/app/oracle/product/11.2.0/db/dbs/orapworadu

The authenticity of host '192.168.56.150 (192.168.56.150)' can't be established.

RSA key fingerprint is 58:71:ed:0c:e0:2a:57:68:3e:fe:79:52:8b:72:2e:00.

Are you sure you want to continue connecting (yes/no)? Yes

Warning: Permanently added '192.168.56.150' (RSA) to the list of known hosts.

Oracle@192.168.56.150's password:

Orapwmydb 100% 1536 1.5KB/s 00:00

3. Configure target db and auxiliary db snooping

-- auxiliary db must use static snooping, otherwise RMAN-04006: error from auxiliary database: ORA-12528: TNS:listener: all appropriate instances are blocking new connections is reported.

-- auxiliary db

Vi / u01/app/oracle/product/11.2.0/db/network/admin/listener.ora

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(ORACLE_HOME = / u01/app/oracle/product/11.2.0/db)

(SID_NAME=oradu)

)

)

Vi / u01/app/oracle/product/11.2.0/db/network/admin/tnsnames.ora

Mydb =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.56.10) (PORT = 1521))

)

(CONNECT_DATA =

(SERVICE_NAME = mydb)

(SERVER = DEDICATED)

)

)

-- target db

Vi / u01/app/oracle/product/11.2.0/db/network/admin/listener.ora

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(ORACLE_HOME = / u01/app/oracle/product/11.2.0/db)

(ORACLE_SID = mydb)

)

)

Vi / u01/app/oracle/product/11.2.0/db/network/admin/tnsnames.ora

Oradu =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.56.150) (PORT = 1521))

)

(CONNECT_DATA =

(SERVICE_NAME = oradu)

(SERVER = DEDICATED)

)

)

-- restart the monitoring of both machines

Lsnrctl stop

Lsnrctl start

4. Start replication

-- execute on target db

[oracle@localhost ~] $rman target / auxiliary sys/123456@oradu

Recovery Manager: Release 11.2.0.3.0-Production on Sun Mar 20 14:09:39 2016

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

Connected to target database: MYDB (DBID=2820637901)

Connected to auxiliary database: ORADU (not mounted)

RMAN > duplicate target database to oradu from active database

Starting Duplicate Db at 20-MAR-16

Using target database control file instead of recovery catalog

Allocated channel: ORA_AUX_DISK_1

Channel ORA_AUX_DISK_1: SID=20 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 238034944 bytes

Fixed Size 2227136 bytes

Variable Size 180356160 bytes

Database Buffers 50331648 bytes

Redo Buffers 5120000 bytes

Contents of Memory Script:

{

Sql clone "alter system set db_name =

'' MYDB'' comment=

'' Modified by RMAN duplicate'' scope=spfile''

Sql clone "alter system set db_unique_name =

'' ORADU'' comment=

'' Modified by RMAN duplicate'' scope=spfile''

Shutdown clone immediate

Startup clone force nomount

Backup as copy current controlfile auxiliary format'/ u01qapplash oracle.productUniverse 11.2.0DbAccord dbsUniverse cntrloradu.dbf'

Alter clone database mount

}

Executing Memory Script

Sql statement: alter system set db_name =''MYDB'' comment=' 'Modified by RMAN duplicate'' scope=spfile

Sql statement: alter system set db_unique_name =''ORADU'' comment=' 'Modified by RMAN duplicate'' scope=spfile

Oracle instance shut down

Oracle instance started

Total System Global Area 238034944 bytes

Fixed Size 2227136 bytes

Variable Size 180356160 bytes

Database Buffers 50331648 bytes

Redo Buffers 5120000 bytes

Starting backup at 20-MAR-16

Allocated channel: ORA_DISK_1

Channel ORA_DISK_1: SID=34 device type=DISK

Channel ORA_DISK_1: starting datafile copy

Copying current control file

Output file name=/u01/app/oracle/product/11.2.0/db/dbs/snapcf_mydb.f tag=TAG20160320T140956 RECID=31 STAMP=906991797

Channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03

Finished backup at 20-MAR-16

Database mounted

Contents of Memory Script:

{

Set newname for datafile 1 to

"/ u01/app/oracle/oradata/oradu/system01.dbf"

Set newname for datafile 2 to

"/ u01/app/oracle/oradata/oradu/sysaux01.dbf"

Set newname for datafile 3 to

"/ u01/app/oracle/oradata/oradu/undotbs01.dbf"

Set newname for datafile 4 to

"/ u01/app/oracle/oradata/oradu/users01.dbf"

Set newname for datafile 5 to

"/ u01/app/oracle/oradata/oradu/test.dbf"

Set newname for datafile 6 to

"/ u01/app/oracle/oradata/oradu/store_01.dbf"

Set newname for datafile 7 to

"/ u01/app/oracle/oradata/oradu/store_02.dbf"

Set newname for datafile 8 to

"/ u01/app/oracle/oradata/oradu/pitr01.dbf"

Backup as copy reuse

Datafile 1 auxiliary format

"/ u01/app/oracle/oradata/oradu/system01.dbf" datafile

2 auxiliary format

"/ u01/app/oracle/oradata/oradu/sysaux01.dbf" datafile

3 auxiliary format

"/ u01/app/oracle/oradata/oradu/undotbs01.dbf" datafile

4 auxiliary format

"/ u01/app/oracle/oradata/oradu/users01.dbf" datafile

5 auxiliary format

"/ u01/app/oracle/oradata/oradu/test.dbf" datafile

6 auxiliary format

"/ u01/app/oracle/oradata/oradu/store_01.dbf" datafile

7 auxiliary format

"/ u01/app/oracle/oradata/oradu/store_02.dbf" datafile

8 auxiliary format

"/ u01/app/oracle/oradata/oradu/pitr01.dbf"

Sql 'alter system archive log current'

}

Executing Memory Script

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 20-MAR-16

Using channel ORA_DISK_1

Channel ORA_DISK_1: starting datafile copy

Input datafile file number=00001 name=/u01/app/oracle/oradata/mydb/system01.dbf

Output file name=/u01/app/oracle/oradata/oradu/system01.dbf tag=TAG20160320T141004

Channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:05

Channel ORA_DISK_1: starting datafile copy

Input datafile file number=00002 name=/u01/app/oracle/oradata/mydb/sysaux01.dbf

Output file name=/u01/app/oracle/oradata/oradu/sysaux01.dbf tag=TAG20160320T141004

Channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:45

Channel ORA_DISK_1: starting datafile copy

Input datafile file number=00003 name=/u01/app/oracle/oradata/mydb/undotbs01.dbf

Output file name=/u01/app/oracle/oradata/oradu/undotbs01.dbf tag=TAG20160320T141004

Channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25

Channel ORA_DISK_1: starting datafile copy

Input datafile file number=00004 name=/u01/app/oracle/oradata/mydb/users01.dbf

Output file name=/u01/app/oracle/oradata/oradu/users01.dbf tag=TAG20160320T141004

Channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15

Channel ORA_DISK_1: starting datafile copy

Input datafile file number=00007 name=/u01/app/oracle/oradata/mydb/store_02.dbf

Output file name=/u01/app/oracle/oradata/oradu/store_02.dbf tag=TAG20160320T141004

Channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03

Channel ORA_DISK_1: starting datafile copy

Input datafile file number=00005 name=/u01/app/oracle/oradata/mydb/test.dbf

Output file name=/u01/app/oracle/oradata/oradu/test.dbf tag=TAG20160320T141004

Channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03

Channel ORA_DISK_1: starting datafile copy

Input datafile file number=00006 name=/u01/app/oracle/oradata/mydb/store_01.dbf

Output file name=/u01/app/oracle/oradata/oradu/store_01.dbf tag=TAG20160320T141004

Channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07

Channel ORA_DISK_1: starting datafile copy

Input datafile file number=00008 name=/u01/app/oracle/oradata/mydb/pitr01.dbf

Output file name=/u01/app/oracle/oradata/oradu/pitr01.dbf tag=TAG20160320T141004

Channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03

Finished backup at 20-MAR-16

Sql statement: alter system archive log current

Contents of Memory Script:

{

Backup as copy reuse

Archivelog like "/ u01/app/oracle/product/11.2.0/db/dbs/arch/1_22_906314379.dbf" auxiliary format

"/ u01/app/oracle/product/11.2.0/db/dbs/arch2_22_906314379.dbf"

Catalog clone archivelog "/ u01/app/oracle/product/11.2.0/db/dbs/arch2_22_906314379.dbf"

Switch clone datafile all

}

Executing Memory Script

Starting backup at 20-MAR-16

Using channel ORA_DISK_1

Channel ORA_DISK_1: starting archived log copy

Input archived log thread=1 sequence=22 RECID=44 STAMP=906991972

Output file name=/u01/app/oracle/product/11.2.0/db/dbs/arch2_22_906314379.dbf RECID=0 STAMP=0

Channel ORA_DISK_1: archived log copy complete, elapsed time: 00:00:01

Finished backup at 20-MAR-16

Cataloged archived log

Archived log file name=/u01/app/oracle/product/11.2.0/db/dbs/arch2_22_906314379.dbf RECID=44 STAMP=906989788

Datafile 1 switched to datafile copy

Input datafile copy RECID=31 STAMP=906989788 file name=/u01/app/oracle/oradata/oradu/system01.dbf

Datafile 2 switched to datafile copy

Input datafile copy RECID=32 STAMP=906989788 file name=/u01/app/oracle/oradata/oradu/sysaux01.dbf

Datafile 3 switched to datafile copy

Input datafile copy RECID=33 STAMP=906989788 file name=/u01/app/oracle/oradata/oradu/undotbs01.dbf

Datafile 4 switched to datafile copy

Input datafile copy RECID=34 STAMP=906989788 file name=/u01/app/oracle/oradata/oradu/users01.dbf

Datafile 5 switched to datafile copy

Input datafile copy RECID=35 STAMP=906989788 file name=/u01/app/oracle/oradata/oradu/test.dbf

Datafile 6 switched to datafile copy

Input datafile copy RECID=36 STAMP=906989788 file name=/u01/app/oracle/oradata/oradu/store_01.dbf

Datafile 7 switched to datafile copy

Input datafile copy RECID=37 STAMP=906989788 file name=/u01/app/oracle/oradata/oradu/store_02.dbf

Datafile 8 switched to datafile copy

Input datafile copy RECID=38 STAMP=906989788 file name=/u01/app/oracle/oradata/oradu/pitr01.dbf

Contents of Memory Script:

{

Set until scn 2809336

Recover

Clone database

Delete archivelog

}

Executing Memory Script

Executing command: SET until clause

Starting recover at 20-MAR-16

Allocated channel: ORA_AUX_DISK_1

Channel ORA_AUX_DISK_1: SID=18 device type=DISK

Starting media recovery

Archived log for thread 1 with sequence 22 is already on disk as file / u01/app/oracle/product/11.2.0/db/dbs/arch2_22_906314379.dbf

Archived log file name=/u01/app/oracle/product/11.2.0/db/dbs/arch2_22_906314379.dbf thread=1 sequence=22

Media recovery complete, elapsed time: 00:00:01

Finished recover at 20-MAR-16

Oracle instance started

Total System Global Area 238034944 bytes

Fixed Size 2227136 bytes

Variable Size 180356160 bytes

Database Buffers 50331648 bytes

Redo Buffers 5120000 bytes

Contents of Memory Script:

{

Sql clone "alter system set db_name =

'' ORADU'' comment=

'' Reset to original value by RMAN'' scope=spfile''

Sql clone "alter system reset db_unique_name scope=spfile"

Shutdown clone immediate

Startup clone nomount

}

Executing Memory Script

Sql statement: alter system set db_name =''ORADU'' comment=' 'Reset to original value by RMAN'' scope=spfile

Sql statement: alter system reset db_unique_name scope=spfile

Oracle instance shut down

Connected to auxiliary database (not started)

Oracle instance started

Total System Global Area 238034944 bytes

Fixed Size 2227136 bytes

Variable Size 180356160 bytes

Database Buffers 50331648 bytes

Redo Buffers 5120000 bytes

Sql statement: CREATE CONTROLFILE REUSE SET DATABASE "ORADU" RESETLOGS ARCHIVELOG

MAXLOGFILES 16

MAXLOGMEMBERS 3

MAXDATAFILES 100

MAXINSTANCES 8

MAXLOGHISTORY 292

LOGFILE

GROUP 1 ('/ u01 REUSE SIZE SIZE REUSE)

GROUP 2 ('/ u01 REUSE SIZE SIZE REUSE)

GROUP 3 ('/ u01 SIZE REUSE) SIZE 100m REUSE

GROUP 4 ('/ u01 SIZE REUSE) SIZE 100m REUSE

DATAFILE

'/ u01 apprenticeship oradata'oradata 'system 01.dbf'

CHARACTER SET ZHS16GBK

Contents of Memory Script:

{

Set newname for tempfile 1 to

"/ u01/app/oracle/oradata/oradu/temp01.dbf"

Switch clone tempfile all

Catalog clone datafilecopy "/ u01/app/oracle/oradata/oradu/sysaux01.dbf"

"/ u01/app/oracle/oradata/oradu/undotbs01.dbf"

"/ u01/app/oracle/oradata/oradu/users01.dbf"

"/ u01/app/oracle/oradata/oradu/test.dbf"

"/ u01/app/oracle/oradata/oradu/store_01.dbf"

"/ u01/app/oracle/oradata/oradu/store_02.dbf"

"/ u01/app/oracle/oradata/oradu/pitr01.dbf"

Switch clone datafile all

}

Executing Memory Script

Executing command: SET NEWNAME

Renamed tempfile 1 to / u01/app/oracle/oradata/oradu/temp01.dbf in control file

Cataloged datafile copy

Datafile copy file name=/u01/app/oracle/oradata/oradu/sysaux01.dbf RECID=1 STAMP=906989800

Cataloged datafile copy

Datafile copy file name=/u01/app/oracle/oradata/oradu/undotbs01.dbf RECID=2 STAMP=906989800

Cataloged datafile copy

Datafile copy file name=/u01/app/oracle/oradata/oradu/users01.dbf RECID=3 STAMP=906989800

Cataloged datafile copy

Datafile copy file name=/u01/app/oracle/oradata/oradu/test.dbf RECID=4 STAMP=906989800

Cataloged datafile copy

Datafile copy file name=/u01/app/oracle/oradata/oradu/store_01.dbf RECID=5 STAMP=906989800

Cataloged datafile copy

Datafile copy file name=/u01/app/oracle/oradata/oradu/store_02.dbf RECID=6 STAMP=906989800

Cataloged datafile copy

Datafile copy file name=/u01/app/oracle/oradata/oradu/pitr01.dbf RECID=7 STAMP=906989800

Datafile 2 switched to datafile copy

Input datafile copy RECID=1 STAMP=906989800 file name=/u01/app/oracle/oradata/oradu/sysaux01.dbf

Datafile 3 switched to datafile copy

Input datafile copy RECID=2 STAMP=906989800 file name=/u01/app/oracle/oradata/oradu/undotbs01.dbf

Datafile 4 switched to datafile copy

Input datafile copy RECID=3 STAMP=906989800 file name=/u01/app/oracle/oradata/oradu/users01.dbf

Datafile 5 switched to datafile copy

Input datafile copy RECID=4 STAMP=906989800 file name=/u01/app/oracle/oradata/oradu/test.dbf

Datafile 6 switched to datafile copy

Input datafile copy RECID=5 STAMP=906989800 file name=/u01/app/oracle/oradata/oradu/store_01.dbf

Datafile 7 switched to datafile copy

Input datafile copy RECID=6 STAMP=906989800 file name=/u01/app/oracle/oradata/oradu/store_02.dbf

Datafile 8 switched to datafile copy

Input datafile copy RECID=7 STAMP=906989800 file name=/u01/app/oracle/oradata/oradu/pitr01.dbf

Reenabling controlfile options for auxiliary database

Executing: alter database add supplemental log data

Contents of Memory Script:

{

Alter clone database open resetlogs

}

Executing Memory Script

Database opened

Finished Duplicate Db at 20-MAR-16

RMAN >

5. Verify whether the migration is successful

-- execute in auxiliary db

SQL > select name,open_mode from v$database

NAME OPEN_MODE

--

ORADU READ WRITE

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.

Views: 0

*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.

Share To

Database

Wechat

© 2024 shulou.com SLNews company. All rights reserved.

12
Report