Basic environment:
- 172.17.4.60 Operating system: Linux 6.4 Database: Oracle11gR2 (source database)
- 172.17.4.61 Operating system: Linux 6.4 database :Oracle11gR2 (test library)
Purpose: to restore from the source database to the test library by means of physical recovery of RMAN.
How to verify, insert a few pieces of data in the source database as follows:
[emailprotected]> create user zsd identified by zsd;User created.[emailprotected]> grant dba to zsd;Grant succeeded.[emailprotected]> conn zsd/zsd;Connected.[emailprotected]> create table t1(name varchar2(20));Table created.[emailprotected]> insert into t1 values('zsd' );1 row created.[email protected]> insert into t1 values('lzh');1 row created.[email protected]> insert into t1 values('zjf');1 row created.[email protected]> commit ;Commit complete.[email protected]> select * from t1;NAME--------------------zsdlzhzjf
Step 0. Host configuration of the basic environment
The following configurations are required for both servers.
[[email protected] ~]$ cat /etc/hosts127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4::1 localhost localhost.localdomain localhost6 localhost6.localdomain6172.17.4 .60 db1172.17.4.61 db2
Source database side (172.17.4.60):
[[emailprotected] ~]$ cat /etc/sysconfig/ networkNETWORKING=yesHOSTNAME=db1
Test the database side (172.17.4.61):
[[emailprotected] ~]$ cat /etc/sysconfig/network
NETWORKING=yes
HOSTNAME=db2
Step 1. Modify environment variables
[[emailprotected] dbs] $ cd ~[[email protected] ~]$ vi .bash_profile
Modify the ORACLE_SID to be consistent with the source database, as follows:
ORACLE_SID=oradb
Step 2. Create a pfile dummy file
[o[emailprotected] ~]$ cd $ORACLE_HOME/dbs[[emailprotected] dbs]$ vi dummy. oradb_name='zsddummy'
Step 3. Copy the password file from the source database (172.17.4.60) to Test library (172.17.4.61).
[[email protected] dbs]$ pwd/u01/app/product/11.2.0/db_1/dbs[[email protected] dbs]$ scp 172.17.4.60:/u01 /app/product/11.2.0/db_1/dbs/orapworadb ./[[email protected] dbs]$ ls -lht total usage 20K-rw-r-----. 1 oracle oinstall 1.5K December 29 15: 48 orapworadb-rw-r--r--. 1 oracle oinstall 19 December 29 15:44 dummy.ora-rw-rw----. 1 oracle oinstall 1.6K December 29 15:27 hc_oradb.dat-rw -r-----. 1 oracle oinstall 24 May 15 2017 lkORADB-rw-r--r--. 1 oracle oinstall 2.8K May 15 2009 init.ora
Step 4. Create the corresponding directory
- 4.1 Audit directory
Source database side (172.17.4.60):
[email Protected]> show parameter audit_file_destNAME TYPE VALUE-------------------------------- ---- ----------- ------------------------------audit_file_dest string /u01/ app/admin/oradb/adump
Test the database side (172.17.4.61):
[[emailprotected] oradb]$ mkdir -p /u01/app /admin/oradb/adump
- 4.2 Fast recovery area directory
Source database side (172.17.4.60):
[emailprotected]> show parameter db_recovery_file_destNAME TYPE VALUE ------------------------------------ ----------- --- ---------------------------db_recovery_file_dest string /u01/app/fast_recovery_areadb_recovery_file_dest_size big integer 4182M
Test database (172.17 .4.61):
[[email protected] oradb]$ mkdir -p /u01/app/fast_recovery_area
- 4.3 Data directory
Source database side (172.17.4.60):
[email protected]> select name from v$dbfile;NAME--------- -------------------------------------------------- ---------------------/u01/app/oradata/oradb/users01.dbf/u01/app/oradata/oradb/undotbs01.dbf/u01/app/ oradata/oradb/sysaux01.dbf/u01/app/oradata/oradb/system01.dbf
Test the database side (172.17.4.61):
[[email protected] oradb]$ mkdir -p /u01/app/oradata/oradb/[[email protected] oradb]$ ls -ld / u01/app/oradata/oradb/drwxr-x---. 2 oracle oinstall 4096 December 29 15:27 /u01/app/oradata/oradb/
Step 5.net service
Test the database side (172.17.4.61):
[[emailprotected] ~]$ cat /u01/app/product/ 11.2.0/db_1/network/admin/listener.ora# listener.ora Network Configuration File: /u01/app/product/11.2.0/db_1/network/admin/listener.ora# Generated by Oracle configuration tools.LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = db2)(PORT = 1521)) (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))) )SID_LIST_LISTENER = (SID_LIST = (SID_DESC = ( GLOBAL_DBNAME = oradb.zsd.com) (ORACLE_HOME=/u01/app/product/11.2.0/db_1) (SID_NAME = oradb) ))ADR_BASE_LISTENER = /u01/app
The monitoring start of the test library:
[[email protected] admin]$ lsnrctl startLSNRCTL for Linux: Version 11.2.0.4.0-Production on 29-DEC-2018 16:03:01Copyright (c) 1991, 2013 , Oracle. All rights reserved.Starting /u01/app/product/11.2.0/db_ 1/bin/tnslsnr: please wait...TNSLSNR for Linux: Version 11.2.0.4.0-ProductionSystem parameter file is /u01/app/product/11.2.0/db_1/network/admin/listener.oraLog messages written to / u01/app/diag/tnslsnr/db/listener/alert/log.xmlListening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=db2)(PORT=1521)))Listening on: (DESCRIPTION=(ADDRESS =(PROTOCOL=ipc)(KEY=EXTPROC1521)))Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=db2)(PORT=1521)))STATUS of the LISTENER-------- ----------------Alias LISTENERVersion TNSLSNR for Linux: Version 11.2.0.4.0-ProductionStart Date 29-DEC-2018 16:03:01Uptime 0 days 0 hr. 0 min. 10 secTrace Level offSecurity ON: Local OS AuthenticationSNMP OFFListener Parameter File /u01/app/product/11.2.0/db_1/network/admin/listener.oraListener Log File /u01/app/diag/tnslsnr/db/listener/alert/log. xmlListening Endpoints Summary... (DES CRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=db2)(PORT=1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))Services Summary...Service "oradb.zsd .com" has 1 instance(s). Instance "oradb", status UNKNOWN, has 1 handler(s) for this service...The command completed successfully
Source data terminal: 172.17.4.60
[[emailprotected] ~]$ cat /u01/app/product/11.2.0/db_1/network/admin/listener.ora# listener.ora Network Configuration File: /u01/ app/product/11.2.0/db_1/network/admin/listener.ora# Generated by Oracle configuration tools.LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = db1)(PORT = 1521) ) (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))) )SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = oradb.zsd.com) (SID_NAME = oradb) ))ADR_BASE_LISTENER = /u01/Start monitoring of source data:
[[emailprotected] admin]$ lsnrctl startLSNRCTL for Linux: Version 11.2.0.4.0-Production on 29-DEC-2018 16:06:02Copyright (c) 1991, 2013, Oracle. All rights reserved.Starting /u01/app/product/11.2.0/db_1/bin/tnslsnr: please wait...TNSLSNR for Linux: Version 11.2.0.4. 0-ProductionSystem parameter file is /u01/app/product/11.2.0/db_1/network/admin/listener.oraLog messages written to /u01/app/diag/tnslsnr/db/listener/alert/log.xmlListening on: ( DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=db1)(PORT=1521)))Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))Connecting to (DESCRIPTION=(ADDRESS =(PROTOCOL=TCP)(HOST=db1)(PORT=1521)))STATUS of the LISTENER------------------------Alias LISTENERVersion TNSLSNR for Linux: Version 11.2.0.4.0-ProductionStart Date 29-DEC-2018 16:06:03Uptime 0 days 0 hr. 0 min. 11 secTrace Level offSecurity ON: Local OS AuthenticationSNMP OFFListener Parameter File /u01/app/product/11.2. 0/db_1/network/admin/listener.oraListener L og File /u01/app/diag/tnslsnr/db/listener/alert/log.xmlListening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=db1)(PORT=1521))) (DESCRIPTION =(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))Services Summary...Service "oradb.zsd.com" has 1 instance(s). Instance "oradb", status UNKNOWN, has 1 handler(s) for this service...The command completed successfullyConfigure the tns file on both ends (the configuration of 60 and 61 is the same)
vi $ORACLE_HOME/network/admin/ tnsnames.oraZSDDB = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = db1)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = oradb.zsd.com)) )aux = (DESCRIPTION = ( ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = db2)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = oradb.zsd.com)) )Source data port (172.17.4.60 )Connection test library
[[email protected] ~]$ tnsping auxTNS Ping Utility for Linux: Version 11.2.0.4.0-Production on 29-DEC-2018 16:16:52Copyright (c) 1997, 2013, Oracle. All rights reserved.Used parameter files:Used TNSNAMES adapter to resolve the aliasAttempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = db2)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = oradb.zsd.com)))OK (0 msec)[[emailprotected] ~]$ sqlplus sys/[emailprotected] as sysdbaSQL*Plus: Release 11.2.0.4.0 Production on Sat Dec 29 16:17 :06 2018Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to an idle instance.[email protected]>Test terminal (172.17.4.61): Test source database
< pre class="sql">[[email protected] ~]$ tnsping zsddbTNS Ping Utility for Linux: Version 11.2.0.4.0-Production on 29-DEC-2018 16:21:23Copyright (c) 1997, 2013, Oracle. All rights reserved.Used parameter files:Used TNSNAMES adapter to resolve the aliasAttempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = db1)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = oradb .zsd.com)))OK (70 msec)[[email protected] ~]$ sqlplus sys/[email p rotected] as sysdbaSQL*Plus: Release 11.2.0.4.0 Production on Sat Dec 29 16:21:47 2018 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4. 0-64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing options[email protected]>
Step 6. Test The database (172.17.4.61) is opened in nomout format.
[[email protected] ~]$ sqlplus / as sysdbaSQL*Plus: Release 11.2.0.4.0 Production on Sat Dec 29 16:23:55 2018Copyright (c) 1982, 2013 , Oracle. All rights reserved.Connected to an idle instance.[email protected]> startup nomount pfile=$ORACLE_HOME/dbs/dummy.oraORACLE instance started.Total System Global Area 263090176 bytesFixed Size 2252256 bytesVariable Size 205521440 bytesDatabase Buffers 50331648 bytesRedo Buffers 4984832 bytesThe test terminal (172.17.4.61) rman connects the source database and auxiliary library:
[[email protected] ~]$ rman target sys/[email protected] ] auxiliary sys/[emailprotected]Recovery Manager: Release 11.2.0.4.0-Production on Sat Dec 29 16:25:33 2018Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.connected to target database: ORADB (DBID=2697310494)connected to auxiliary database: ZSDDUMMY (not mounted)Step 7. Start RMAN duplicate Copy the database through the network
The test terminal (172.17.4.61) started to copy the database through the network
RMAN> duplicate target database to zsddb from active database spfile nofilenamecheck;< /pre>The log is as follows:
Starting Duplicate Db at 02-JAN-19 using target database control file instead of recovery catalogallocated channel: ORA_AUX_DISK_1channel ORA_AUX_DISK_1: SID=429 device type=DISKcontents of Memory Script:{ backup as copy reuse targetfile'/u01/app/product/11.2.0/db_1/dbs/spfileoradb.ora' auxiliary format'/u01/app/product/11.2.0/db_1/dbs/spfileoradb. ora'; sql clone "alter system set spfile=''/u01/app/product/11.2.0/db_1/dbs/spfileoradb.ora''";}executing Memory ScriptStarting backup at 02-JAN-19allocated channel: ORA_DISK_1channel ORA_DISK_1 : SID=10 device type=DISKFinished backup at 02-JAN-19sql statement: alter system set spfile=''/u01/app/product/11.2.0/db_1/dbs/spfileoradb.ora``contents of Memory Script:{ sql clone "alter system set db_name =''ZSDDB'' comment =''duplicate'' scope=spfile"; shutdown clone immediate; startup clone nomount;}executing Memory Scriptsql statement: alter system set db_name =''ZSDDB'' comment=''duplicate'' scope=spfileOracle instance shut downconnected to auxiliary database (not started)Oracle instance startedTotal System Global Area 1202556928 bytesFixed Size 2252704 bytesVariable Size 419430496 bytesDatabase Buffers 771751936 bytesRedo Buffers 9121792 bytescontents of Memory Script:{ sql clone "alter system set db_name =''ORADB'' comment =''Modified by RMAN duplicate'' scope=spfile "; sql clone "alter system set db_unique_name =''ZSDDB'' comment=''Modified by RMAN duplicate'' scope=spfile"; shutdown clone immediate; startup clone force nomount backup as copy current controlfile auxiliary format'/u01/app /oradata/oradb/control01.ctl'; restore clone controlfile to'/u01/app/fast_recovery_area/oradb/control02.ctl' from'/u01/app/oradata /oradb/control01.ctl'; alter clone database mount;}executing Memory Scriptsql statement: alter system set db_name =''ORADB'' comment =''Modified by RMAN duplicate'' scope=spfilesql statement: alter system set db_unique_name = ' 'ZSDDB'' comment=''Modified by RMAN duplicate'' scope=spfileOracle instance shut downOracle instance startedTotal System Global Area 1202556928 bytesFixed Size 2252704 bytesVariable Size 419430496 bytesDatabase Buffers 771751936 bytesRedo Buffers 9121792 bytesStarting backup at 02-JAN-19using channel ORA_DISK_1channel ORA_DISK_1channel ORA_DISK_1channel starting datafile copycopying current control fileoutput file name=/u01/app/product/11.2.0/db_1/dbs/snapcf_oradb.f tag=TAG20190102T144945 RECID=2 STAMP=996504585channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01Finished backup at 02-JAN-19Starting restore at 02-JAN-19allocated channel: ORA_AUX_DISK_1channel ORA_AUX_DISK_1: SID=85 device type=DISKc hannel ORA_AUX_DISK_1: copied control file copyFinished restore at 02-JAN-19database mountedRMAN-05538: WARNING: implicitly using DB_FILE_NAME_CONVERTcontents of Memory Script:{ set newname for datafile 1 to "/u01/app/oradata/oradb/system01.dbf"; set newname for datafile 2 to "/u01/app/oradata/oradb/sysaux01.dbf"; set newname for datafile 3 to "/u01/app/oradata/oradb/undotbs01.dbf"; set newname for datafile 4 to "/u01 /app/oradata/oradb/users01.dbf"; backup as copy reuse datafile 1 auxiliary format "/u01/app/oradata/oradb/system01.dbf" datafile 2 auxiliary format "/u01/app/oradata/oradb/sysaux01. dbf" datafile 3 auxiliary format "/u01/app/oradata/oradb/undotbs01.dbf" datafile 4 auxiliary format "/u01/app/oradata/oradb/users01.dbf"; sql'alter system archive log current';}executing Memory Scriptexecuting command: SET NEWNAMEexecuting command: SET NEWNAMEexecuting command: SET NEWNAMEexecuting command: SET NEWNAMEStarting backup at 02-JAN-19using chan nel ORA_DISK_1channel ORA_DISK_1: starting datafile copyinput datafile file number=00001 name=/u01/app/oradata/oradb/system01.dbfoutput file name=/u01/app/oradata/oradb/system01.dbf tag=TAG20190102T144953channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:35channel ORA_DISK_1: starting datafile copyinput datafile file number=00002 name=/u01/app/oradata/oradb/sysaux01.dbfoutput file name=/u01/app/oradata/oradb/sysaux01.dbf tag=TAG20190102T144953channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25channel ORA_DISK_1: starting datafile copyinput datafile file number=00003 name=/u01/app/oradata/oradb/undotbs01.dbfoutput file name=/u01/app/oradata/oradb/ undotbs01.dbf tag=TAG20190102T144953channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03channel ORA_DISK_1: starting datafile copyinput datafile file number=00004 name=/u01/app/oradata/oradb/users01.dbfoutput file name=/u01/ app/oradata/oradb/users01.dbf tag=TAG20190102T144953channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03Finished backup at 02-JAN-19sql statement: alter system archive log currentcontents of Memory Script:{ backup as copy reuse archivelog like "/u01/app/fast_recovery_area/ORADB/archivelog/2019_01_02/ o1_mf_1_12_g2rqto2s_.arc" auxiliary format "/u01/app/fast_recovery_area/ZSDDB/archivelog/2019_01_02/o1_mf_1_12_%u_.arc"; catalog clone recovery area; switch clone datafile all;}executing Channel ORA_DISKStarting backup at 02-JAN-19using channel ScriptStarting backup at 02-JAN-19 ORA_DISK_1: starting archived log copyinput archived log thread=1 sequence=12 RECID=7 STAMP=996504662output file name=/u01/app/fast_recovery_area/ZSDDB/archivelog/2019_01_02/o1_mf_1_12_0btmat2m_.arc RECID=0 STAMP=0:channel archive d_copy complete, elapsed time: 00:00:01Finished backup at 02-JAN-19searching for all files in the recovery areaList of Files Unknown to the Database==================== =================File Name: /u01/app/fast_recovery_area/ZSDDB/archivelog/2019_0 1_02/o1_mf_1_12_0btmat2m_.arccataloging files...cataloging done List of Cataloged Files=======================File Name: /u01/app/fast_recovery_area/ZSDDB/archivelog /2019_01_02/o1_mf_1_12_0btmat2m_.arcdatafile 1 switched to datafile copyinput datafile copy RECID=2 STAMP=996504659 file name=/u01/app/oradata/oradb/system01.dbfdatafile 2 switched to datafile copyinput datafile copy RECID=3 STAMP=996504659 file name= /u01/app/oradata/oradb/sysaux01.dbfdatafile 3 switched to datafile copyinput datafile copy RECID=4 STAMP=996504659 file name=/u01/app/oradata/oradb/undotbs01.dbfdatafile 4 switched to datafile copyinput datafile copy RECID=5 STAMP=996504659 file name=/u01/app/oradata/oradb/users01.dbfcontents of Memory Script:{ set until scn 1150015; recover clone database delete archivelog ;}executing Memory Scriptexecuting command: SET until clauseStarting recover at 02-JAN-19using channel ORA_AUX_DISK_1starting media recoveryarchived log for thread 1 with sequence 12 is already on disk as file /u01/app/fast_recovery_area/ZSDDB/archivelog/2019_01_02/o1_mf_1_12_0btmat2m_.arcarchived log file name=/u01/app/fast_recovery_area/ZSDDB/archivelog/2019_01_02/o1_mf_1mf_1m_12.0 complete time elapsed sequence, elapse=12. : 00:00:00Finished recover at 02-JAN-19Oracle instance startedTotal System Global Area 1202556928 bytesFixed Size 2252704 bytesVariable Size 419430496 bytesDatabase Buffers 771751936 bytesRedo Buffers 9121792 bytescontents of Memory Script:{ sql clone "alter system set db_name =''ZSDDB'' 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 Scriptsql statement: alter system set db_name ='' ZSDDB'' comment=''Reset to original value by RMAN'' scope=spfilesql statement: alter system reset db_unique_name scope=spfileOracle inst ance shut downconnected to auxiliary database (not started)Oracle instance startedTotal System Global Area 1202556928 bytesFixed Size 2252704 bytesVariable Size 419430496 bytesDatabase Buffers 771751936 bytesRedo Buffers 9121792 bytessql statement: CREATE CONTROLFILE REUSE SET DATABASE "ZSDDB" RESETLOGFILES ARCHIVELOG MAXMEMS MAXLOGFILES 16 MAXLOGHISTORY 292 LOGFILE GROUP 1 SIZE 50 M, GROUP 2 SIZE 50 M, GROUP 3 SIZE 50 M DATAFILE'/u01/app/oradata/oradb/system01.dbf' CHARACTER SET ZHS16GBKcontents of Memory Script:{ set newname for tempfile 1 to " /u01/app/oradata/oradb/temp01.dbf"; switch clone tempfile all; catalog clone datafilecopy "/u01/app/oradata/oradb/sysaux01.dbf", "/u01/app/oradata/oradb/undotbs01.dbf ", "/u01/app/oradata/oradb/users01.dbf"; switch clone datafile all;}executing Memory Scriptexecuting command: SET NEWNA MErenamed tempfile 1 to /u01/app/oradata/oradb/temp01.dbf in control filecataloged datafile copydatafile copy file name=/u01/app/oradata/oradb/sysaux01.dbf RECID=1 STAMP=996504682cataloged datafile copydatafile copy file name=/ u01/app/oradata/oradb/undotbs01.dbf RECID=2 STAMP=996504682cataloged datafile copydatafile copy file name=/u01/app/oradata/oradb/users01.dbf RECID=3 STAMP=996504682datafile 2 switched to datafile copyinput datafile copy RECID= 1 STAMP=996504682 file name=/u01/app/oradata/oradb/sysaux01.dbfdatafile 3 switched to datafile copyinput datafile copy RECID=2 STAMP=996504682 file name=/u01/app/oradata/oradb/undotbs01.dbfdatafile 4 switched to datafile copyinput datafile copy RECID=3 STAMP=996504682 file name=/u01/app/oradata/oradb/users01.dbfcontents of Memory Script:{ Alter clone database open resetlogs;}executing Memory Scriptdatabase openedFinished Duplicate Db at 02-JAN-19< /pre>Finally, verify the success of my rman duplicate in the test library, and query the verification data as follows:
[[email protected] ~] $ sqlplus "/as sysdba"SQL*Plus: Release 11.2.0.4.0 Production on Wed Jan 2 14:55:14 2019Copyright (c) 1982, 2013, Oracle. All rights reserved.Connected to:Oracle Database 11g Enterprise Edition Release 11.2.0.4.0-64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing options[emailprotected]> conn zsd/zsd;Connected.[emailprotected]> select * from t1;NAME------- -------------zsdlzhzjfenjoy! just for fun