rman duplicate from active 配置级联dataguard
2015/12/7 13:06:14监听配置：
$ pwd
/u01/app/oracle/product/11.2.0/dbhome_1/network/admin
$ cat listener.ora
# listener.ora Network Configuration File: /u01/app/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 = /u01/app/oracle/product/11.2.0/dbhome_1)
(SID_NAME = orcl)
)
(SID_DESC =
(GLOBAL_DBNAME = orcl)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
(SID_NAME = orcl1)
)
(SID_DESC =
(GLOBAL_DBNAME = orcl)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
(SID_NAME = orcl2)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = node1)(PORT = 1521))
)
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
ADR_BASE_LISTENER = /u01/app/oracle
tnsnames.ora 文件配置：
$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
ORCL2 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = node1)(PORT = 1521))
)
(CONNECT_DATA =
(SID = orcl2)
(SERVER = DEDICATED)
)
)
ORCL1 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = node1)(PORT = 1521))
)
(CONNECT_DATA =
(SID = orcl1)
(SERVER = DEDICATED)
)
)
ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = node1)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
$ cat /etc/redhat-release
Red Hat Enterprise Linux Server release 6.4 (Santiago)
$ uname -a
Linux node1 2.6.32-358.el6.x86_64 #1 SMP Tue Jan 29 11:47:41 EST 2013 x86_64 x86_64 x86_64 GNU/Linux
$ sqlplus /nolog
SQL*Plus: Release 11.2.0.4.0 Production on Mon Dec 7 00:06:38 2015
Copyright (c) 1982, 2013, Oracle.All rights reserved.
SQL> connect /as sysdba
Connected.
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE 11.2.0.4.0 Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production
SQL>
SQL>
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
$ lsnrctl status
LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 07-DEC-2015 00:06:58
Copyright (c) 1991, 2013, Oracle.All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=node1)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date 06-DEC-2015 21:22:42
Uptime 0 days 2 hr. 44 min. 16 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/node1/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=node1)(PORT=1521)))
Services Summary...
Service "orcl" has 4 instance(s).
Instance "orcl", status UNKNOWN, has 1 handler(s) for this service...
Instance "orcl", status READY, has 1 handler(s) for this service...
Instance "orcl1", status UNKNOWN, has 1 handler(s) for this service...
Instance "orcl2", status UNKNOWN, has 1 handler(s) for this service...
Service "orclXDB" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
The command completed successfully
$ lsnrctl services
LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 07-DEC-2015 00:07:04
Copyright (c) 1991, 2013, Oracle.All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=node1)(PORT=1521)))
Services Summary...
Service "orcl" has 4 instance(s).
Instance "orcl", status UNKNOWN, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:4 refused:0
LOCAL SERVER
Instance "orcl", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
LOCAL SERVER
Instance "orcl1", status UNKNOWN, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:124 refused:4
LOCAL SERVER
Instance "orcl2", status UNKNOWN, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:98 refused:0
LOCAL SERVER
Service "orclXDB" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
Handler(s):
"D000" established:0 refused:0 current:0 max:1022 state:ready
DISPATCHER <machine: node1, pid: 8110>
(ADDRESS=(PROTOCOL=tcp)(HOST=node1)(PORT=60085))
The command completed successfully
$
$
$
$
$
$
$
orcl 调整数据库参数：
$
$
$ sqlplus /nolog
SQL*Plus: Release 11.2.0.4.0 Production on Mon Dec 7 00:07:33 2015
Copyright (c) 1982, 2013, Oracle.All rights reserved.
SQL> connect /as sysdba
Connected.
SQL>
SQL>
SQL>
SQL> alter database force logging;
Database altered.
SQL> alter system set log_archive_config='dg_config=(orcl,orcl1,orcl2)';
System altered.
SQL> alter system set log_archive_dest_2='service=orcl1 valid_for=(online_logfiles,primary_role) db_unique_name=orcl1';
System altered.
SQL> alter system set standby_file_management=auto;
System altered.
SQL>
SQL>
SQL>
SQL>
SQL>
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 5
Next log sequence to archive 7
Current log sequence 7
开启OMF;
SQL> alter system set db_create_file_dest='/u01/app/oracle/oradata';
System altered.
SQL> show parameter db_create_online_log_dest
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_create_online_log_dest_1 string
db_create_online_log_dest_2 string
db_create_online_log_dest_3 string
db_create_online_log_dest_4 string
db_create_online_log_dest_5 string
SQL>
SQL>
SQL>
SQL> select member from v$logfile;
MEMBER
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/orcl/redo03.log
/u01/app/oracle/oradata/orcl/redo02.log
/u01/app/oracle/oradata/orcl/redo01.log
添加standby logfile;
SQL> ALTER DATABASE ADD STANDBY LOGFILE SIZE 52428800;
Database altered.
SQL> /
Database altered.
SQL> /
Database altered.
SQL> /
Database altered.
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL> exit
创建备库口令文件：
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
$ cd $ORACLE_HOME/dbs
$ ls
hc_orcl1.dathc_orcl.datlkORCL lkORCL2 orapworcl1snapcf_orcl1.fspfileorcl.ora
hc_orcl2.datinit.ora lkORCL1orapworclorapworcl2snapcf_orcl.f
$ ls -ltr
total 19084
-rw-r--r--. 1 oracle oinstall 2851 May 152009 init.ora
-rw-r-----. 1 oracle oinstall 24 Dec6 21:18 lkORCL
-rw-r-----. 1 oracle oinstall 1536 Dec6 22:20 orapworcl1
-rw-r-----. 1 oracle oinstall 24 Dec6 22:20 lkORCL1
-rw-r-----. 1 oracle oinstall 9748480 Dec6 22:21 snapcf_orcl.f
-rw-r-----. 1 oracle oinstall 1536 Dec6 22:57 orapworcl2
-rw-r-----. 1 oracle oinstall 9748480 Dec6 22:57 snapcf_orcl1.f
-rw-r-----. 1 oracle oinstall 24 Dec6 22:57 lkORCL2
-rw-rw----. 1 oracle oinstall 1544 Dec6 23:53 hc_orcl1.dat
-rw-rw----. 1 oracle oinstall 1544 Dec6 23:54 hc_orcl2.dat
-rw-r-----. 1 oracle oinstall 1536 Dec7 00:03 orapworcl
-rw-rw----. 1 oracle oinstall 1544 Dec7 00:04 hc_orcl.dat
-rw-r-----. 1 oracle oinstall 3584 Dec7 00:12 spfileorcl.ora
$ cp orapworcl orapworcl1
$ cp orapworcl orapworcl2
$ ls -ltr
total 19084
-rw-r--r--. 1 oracle oinstall 2851 May 152009 init.ora
-rw-r-----. 1 oracle oinstall 24 Dec6 21:18 lkORCL
-rw-r-----. 1 oracle oinstall 24 Dec6 22:20 lkORCL1
-rw-r-----. 1 oracle oinstall 9748480 Dec6 22:21 snapcf_orcl.f
-rw-r-----. 1 oracle oinstall 9748480 Dec6 22:57 snapcf_orcl1.f
-rw-r-----. 1 oracle oinstall 24 Dec6 22:57 lkORCL2
-rw-rw----. 1 oracle oinstall 1544 Dec6 23:53 hc_orcl1.dat
-rw-rw----. 1 oracle oinstall 1544 Dec6 23:54 hc_orcl2.dat
-rw-r-----. 1 oracle oinstall 1536 Dec7 00:03 orapworcl
-rw-rw----. 1 oracle oinstall 1544 Dec7 00:04 hc_orcl.dat
-rw-r-----. 1 oracle oinstall 3584 Dec7 00:12 spfileorcl.ora
-rw-r-----. 1 oracle oinstall 1536 Dec7 00:16 orapworcl1
-rw-r-----. 1 oracle oinstall 1536 Dec7 00:16 orapworcl2
$
$
$
验证添加完成后的日志文件：
$ sqlplus /nolog
SQL*Plus: Release 11.2.0.4.0 Production on Mon Dec 7 00:16:46 2015
Copyright (c) 1982, 2013, Oracle.All rights reserved.
SQL> connect /as sysdba
Connected.
SQL> select member from v$logfile;
MEMBER
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/orcl/redo03.log
/u01/app/oracle/oradata/orcl/redo02.log
/u01/app/oracle/oradata/orcl/redo01.log
/u01/app/oracle/oradata/ORCL/onlinelog/o1_mf_4_c6b5hjf5_.log
/u01/app/oracle/fast_recovery_area/ORCL/onlinelog/o1_mf_4_c6b5hjjs_.log
/u01/app/oracle/oradata/ORCL/onlinelog/o1_mf_5_c6b5hnd3_.log
/u01/app/oracle/fast_recovery_area/ORCL/onlinelog/o1_mf_5_c6b5hngb_.log
/u01/app/oracle/oradata/ORCL/onlinelog/o1_mf_6_c6b5hqrn_.log
/u01/app/oracle/fast_recovery_area/ORCL/onlinelog/o1_mf_6_c6b5hqto_.log
/u01/app/oracle/oradata/ORCL/onlinelog/o1_mf_7_c6b5hv0s_.log
/u01/app/oracle/fast_recovery_area/ORCL/onlinelog/o1_mf_7_c6b5hv2y_.log
11 rows selected.
SQL> show parameter db_create_file_dest;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest string /u01/app/oracle/oradata
SQL> show parameter db_create_online_log_dest
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_create_online_log_dest_1 string
db_create_online_log_dest_2 string
db_create_online_log_dest_3 string
db_create_online_log_dest_4 string
db_create_online_log_dest_5 string
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
$
$
$
$
$
$
$
创建物理备库相关目录：
$ mkdir -p /u01/app/oracle/oradata/orcl1
$ mkdir -p /u01/app/oracle/oradata/orcl2
$ cd /u01/app/oracle/oradata
$ ls -l
total 16
drwxr-x---. 2 oracle oinstall 4096 Dec7 00:01 orcl
drwxr-x---. 3 oracle oinstall 4096 Dec7 00:15 ORCL
drwxr-xr-x. 2 oracle oinstall 4096 Dec7 00:19 orcl1
drwxr-xr-x. 2 oracle oinstall 4096 Dec7 00:19 orcl2
创建orcl------>orcl1 的物理备库orcl1
$ rman target sys/oracle@orcl1
Recovery Manager: Release 11.2.0.4.0 - Production on Mon Dec 7 00:19:53 2015
Copyright (c) 1982, 2011, Oracle and/or its affiliates.All rights reserved.
connected to target database (not started)
RMAN> startup force nomount
startup failed: ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/initorcl1.ora'
starting Oracle instance without parameter file for retrieval of spfile
Oracle instance started
Total System Global Area 1068937216 bytes
Fixed Size 2260088 bytes
Variable Size 281019272 bytes
Database Buffers 780140544 bytes
Redo Buffers 5517312 bytes
RMAN> exit
Recovery Manager complete.
$ rman target sys/oracle@orcl auxiliary sys/oracle@orcl1
Recovery Manager: Release 11.2.0.4.0 - Production on Mon Dec 7 00:24:02 2015
Copyright (c) 1982, 2011, Oracle and/or its affiliates.All rights reserved.
connected to target database: ORCL (DBID=1425563234)
connected to auxiliary database: DUMMY (not mounted)
RMAN> DUPLICATE DATABASE
FOR STANDBY
FROM ACTIVE DATABASE
DORECOVER
SPFILE
SET db_unique_name='orcl1'
SET DB_CREATE_FILE_DEST='/u01/app/oracle/oradata'
SET 'control_files'='/u01/app/oracle/oradata/orcl1/control01.ctl'
SET FAL_SERVER='orcl'
;2> 3> 4> 5> 6> 7> 8> 9> 10>
Starting Duplicate Db at 07-DEC-2015 00:24:15
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=19 device type=DISK
contents of Memory Script:
{
backup as copy reuse
targetfile'/u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapworcl' auxiliary format
'/u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapworcl1' targetfile
'/u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfileorcl.ora' auxiliary format
'/u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfileorcl1.ora' ;
sql clone "alter system set spfile= ''/u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfileorcl1.ora''";
}
executing Memory Script
Starting backup at 07-DEC-2015 00:24:15
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1 device type=DISK
Finished backup at 07-DEC-2015 00:24:18
sql statement: alter system set spfile= ''/u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfileorcl1.ora''
contents of Memory Script:
{
sql clone "alter system setdb_unique_name =
''orcl1'' comment=
'''' scope=spfile";
sql clone "alter system setDB_CREATE_FILE_DEST =
''/u01/app/oracle/oradata'' comment=
'''' scope=spfile";
sql clone "alter system setcontrol_files =
''/u01/app/oracle/oradata/orcl1/control01.ctl'' comment=
'''' scope=spfile";
sql clone "alter system setFAL_SERVER =
''orcl'' comment=
'''' scope=spfile";
shutdown clone immediate;
startup clone nomount;
}
executing Memory Script
sql statement: alter system setdb_unique_name =''orcl1'' comment= '''' scope=spfile
sql statement: alter system setDB_CREATE_FILE_DEST =''/u01/app/oracle/oradata'' comment= '''' scope=spfile
sql statement: alter system setcontrol_files =''/u01/app/oracle/oradata/orcl1/control01.ctl'' comment= '''' scope=spfile
sql statement: alter system setFAL_SERVER =''orcl'' comment= '''' scope=spfile
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 1603411968 bytes
Fixed Size 2253664 bytes
Variable Size 989858976 bytes
Database Buffers 603979776 bytes
Redo Buffers 7319552 bytes
contents of Memory Script:
{
backup as copy current controlfile for standby auxiliary format'/u01/app/oracle/oradata/orcl1/control01.ctl';
}
executing Memory Script
Starting backup at 07-DEC-2015 00:24:22
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
copying standby control file
output file name=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/snapcf_orcl.f tag=TAG20151207T002422 RECID=3 STAMP=897783862
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 07-DEC-2015 00:24:23
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 clone tempfile1 to new;
switch clone tempfile all;
set newname for clone datafile1 to new;
set newname for clone datafile2 to new;
set newname for clone datafile3 to new;
set newname for clone datafile4 to new;
set newname for clone datafile5 to new;
backup as copy reuse
datafile1 auxiliary format new
datafile2 auxiliary format new
datafile3 auxiliary format new
datafile4 auxiliary format new
datafile5 auxiliary format new
;
sql 'alter system archive log current';
}
executing Memory Script
executing command: SET NEWNAME
renamed tempfile 1 to /u01/app/oracle/oradata/ORCL1/datafile/o1_mf_temp_%u_.tmp in control file
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting backup at 07-DEC-2015 00:24:29
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=/u01/app/oracle/oradata/orcl/system01.dbf
output file name=/u01/app/oracle/oradata/ORCL1/datafile/o1_mf_system_03qo661t_.dbf tag=TAG20151207T002429
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=/u01/app/oracle/oradata/orcl/sysaux01.dbf
output file name=/u01/app/oracle/oradata/ORCL1/datafile/o1_mf_sysaux_04qo662m_.dbf tag=TAG20151207T002429
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting datafile copy
input datafile file number=00005 name=/u01/app/oracle/oradata/orcl/example01.dbf
output file name=/u01/app/oracle/oradata/ORCL1/datafile/o1_mf_example_05qo663f_.dbf tag=TAG20151207T002429
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=/u01/app/oracle/oradata/orcl/undotbs01.dbf
output file name=/u01/app/oracle/oradata/ORCL1/datafile/o1_mf_undotbs1_06qo663u_.dbf tag=TAG20151207T002429
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=/u01/app/oracle/oradata/orcl/users01.dbf
output file name=/u01/app/oracle/oradata/ORCL1/datafile/o1_mf_users_07qo6642_.dbf tag=TAG20151207T002429
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 07-DEC-2015 00:25:39
sql statement: alter system archive log current
contents of Memory Script:
{
backup as copy reuse
archivelog like"/u01/app/oracle/fast_recovery_area/ORCL/archivelog/2015_12_07/o1_mf_1_7_c6b62mcq_.arc" auxiliary format
"/u01/app/oracle/fast_recovery_area/ORCL1/archivelog/2015_12_07/o1_mf_1_7_%u_.arc" ;
catalog clone recovery area;
switch clone datafile all;
}
executing Memory Script
Starting backup at 07-DEC-2015 00:25:39
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log copy
input archived log thread=1 sequence=7 RECID=3 STAMP=897783939
output file name=/u01/app/oracle/fast_recovery_area/ORCL1/archivelog/2015_12_07/o1_mf_1_7_08qo6643_.arc RECID=0 STAMP=0
channel ORA_DISK_1: archived log copy complete, elapsed time: 00:00:01
Finished backup at 07-DEC-2015 00:25:40
searching for all files in the recovery area
List of Files Unknown to the Database
=====================================
File Name: /u01/app/oracle/fast_recovery_area/ORCL1/archivelog/2015_12_06/o1_mf_1_8_c69yw5qt_.arc
File Name: /u01/app/oracle/fast_recovery_area/ORCL1/archivelog/2015_12_06/o1_mf_1_7_09qo5url_.arc
File Name: /u01/app/oracle/fast_recovery_area/ORCL1/archivelog/2015_12_07/o1_mf_1_7_08qo6643_.arc
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: /u01/app/oracle/fast_recovery_area/ORCL1/archivelog/2015_12_06/o1_mf_1_8_c69yw5qt_.arc
File Name: /u01/app/oracle/fast_recovery_area/ORCL1/archivelog/2015_12_06/o1_mf_1_7_09qo5url_.arc
File Name: /u01/app/oracle/fast_recovery_area/ORCL1/archivelog/2015_12_07/o1_mf_1_7_08qo6643_.arc
datafile 1 switched to datafile copy
input datafile copy RECID=3 STAMP=897783940 file name=/u01/app/oracle/oradata/ORCL1/datafile/o1_mf_system_03qo661t_.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=4 STAMP=897783940 file name=/u01/app/oracle/oradata/ORCL1/datafile/o1_mf_sysaux_04qo662m_.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=5 STAMP=897783940 file name=/u01/app/oracle/oradata/ORCL1/datafile/o1_mf_undotbs1_06qo663u_.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=6 STAMP=897783940 file name=/u01/app/oracle/oradata/ORCL1/datafile/o1_mf_users_07qo6642_.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=7 STAMP=897783940 file name=/u01/app/oracle/oradata/ORCL1/datafile/o1_mf_example_05qo663f_.dbf
contents of Memory Script:
{
set until scn993331;
recover
standby
clone database
delete archivelog
;
}
executing Memory Script
executing command: SET until clause
Starting recover at 07-DEC-2015 00:25:40
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=18 device type=DISK
starting media recovery
1_7_08qo6643_.arc
archived log file name=/u01/app/oracle/fast_recovery_area/ORCL1/archivelog/2015_12_07/o1_mf_1_7_08qo6643_.arc thread=1 sequence=7
media recovery complete, elapsed time: 00:00:00
Finished recover at 07-DEC-2015 00:25:41
Finished Duplicate Db at 07-DEC-2015 00:25:52
RMAN> exit
Recovery Manager complete.
$
$
开启物理备库orcl1日志应用：
$ export ORACLE_SID=orcl1
$ sqlplus /nolog
SQL*Plus: Release 11.2.0.4.0 Production on Mon Dec 7 00:26:15 2015
Copyright (c) 1982, 2013, Oracle.All rights reserved.
Connected.
SQL> select member from v$logfile;
MEMBER
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/ORCL1/onlinelog/o1_mf_3_c6b62ryd_.log
/u01/app/oracle/oradata/ORCL1/onlinelog/o1_mf_2_c6b62q4b_.log
/u01/app/oracle/oradata/ORCL1/onlinelog/o1_mf_1_c6b62ojh_.log
/u01/app/oracle/oradata/ORCL1/onlinelog/o1_mf_4_c6b62vdr_.log
/u01/app/oracle/fast_recovery_area/ORCL1/onlinelog/o1_mf_4_c6b62vgb_.log
/u01/app/oracle/oradata/ORCL1/onlinelog/o1_mf_5_c6b62wmo_.log
/u01/app/oracle/fast_recovery_area/ORCL1/onlinelog/o1_mf_5_c6b62wp6_.log
/u01/app/oracle/oradata/ORCL1/onlinelog/o1_mf_6_c6b62xtw_.log
/u01/app/oracle/fast_recovery_area/ORCL1/onlinelog/o1_mf_6_c6b62xw9_.log
/u01/app/oracle/oradata/ORCL1/onlinelog/o1_mf_7_c6b62z31_.log
/u01/app/oracle/fast_recovery_area/ORCL1/onlinelog/o1_mf_7_c6b62z57_.log
MEMBER
--------------------------------------------------------------------------------
/u01/app/oracle/fast_recovery_area/ORCL1/onlinelog/o1_mf_1_c6b62oms_.log
/u01/app/oracle/fast_recovery_area/ORCL1/onlinelog/o1_mf_2_c6b62q6b_.log
/u01/app/oracle/fast_recovery_area/ORCL1/onlinelog/o1_mf_3_c6b62s0y_.log
14 rows selected.
SQL> recover managed standby database using current logfile disconnect;
Media recovery complete.
SQL>
SQL>
SQL>
SQL> exit
切换orcl库日志，检查orcl1 物理备库alert_orcl1.log日志是否正常：
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
$ export ORACLE_SID=orcl
$ sqlplus /nolog
SQL*Plus: Release 11.2.0.4.0 Production on Mon Dec 7 00:29:31 2015
Copyright (c) 1982, 2013, Oracle.All rights reserved.
SQL> connect /as sysdba
Connected.
SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
SQL> exit
配置物理orcl1------>orcl2物理备库orcl2
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
$
$
$ rman target sys/oracle@orcl2
Recovery Manager: Release 11.2.0.4.0 - Production on Mon Dec 7 00:37:03 2015
Copyright (c) 1982, 2011, Oracle and/or its affiliates.All rights reserved.
connected to target database (not started)
RMAN> startup force nomount
startup failed: ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/initorcl2.ora'
starting Oracle instance without parameter file for retrieval of spfile
Oracle instance started
Total System Global Area 1068937216 bytes
Fixed Size 2260088 bytes
Variable Size 281019272 bytes
Database Buffers 780140544 bytes
Redo Buffers 5517312 bytes
RMAN> exit
Recovery Manager complete.
$ rman target sys/oracle@orcl1 auxiliary sys/oracle@orcl2
Recovery Manager: Release 11.2.0.4.0 - Production on Mon Dec 7 00:39:13 2015
Copyright (c) 1982, 2011, Oracle and/or its affiliates.All rights reserved.
connected to target database: ORCL (DBID=1425563234, not open)
connected to auxiliary database: DUMMY (not mounted)
RMAN> DUPLICATE DATABASE
FOR STANDBY
FROM ACTIVE DATABASE
DORECOVER
SPFILE
SET db_unique_name ='orcl2'
SET DB_CREATE_FILE_DEST ='/u01/app/oracle/oradata'
SET control_files='/u01/app/oracle/oradata/orcl2/control01.ctl'
SET FAL_SERVER='orcl1'
;2> 3> 4> 5> 6> 7> 8> 9> 10>
Starting Duplicate Db at 07-DEC-2015 00:39:20
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=19 device type=DISK
contents of Memory Script:
{
backup as copy reuse
targetfile'/u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapworcl1' auxiliary format
'/u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapworcl2' targetfile
'/u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfileorcl1.ora' auxiliary format
'/u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfileorcl2.ora' ;
sql clone "alter system set spfile= ''/u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfileorcl2.ora''";
}
executing Memory Script
Starting backup at 07-DEC-2015 00:39:20
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=32 device type=DISK
Finished backup at 07-DEC-2015 00:39:22
sql statement: alter system set spfile= ''/u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfileorcl2.ora''
contents of Memory Script:
{
sql clone "alter system setdb_unique_name =
''orcl2'' comment=
'''' scope=spfile";
sql clone "alter system setDB_CREATE_FILE_DEST =
''/u01/app/oracle/oradata'' comment=
'''' scope=spfile";
sql clone "alter system setcontrol_files =
''/u01/app/oracle/oradata/orcl2/control01.ctl'' comment=
'''' scope=spfile";
sql clone "alter system setFAL_SERVER =
''orcl1'' comment=
'''' scope=spfile";
shutdown clone immediate;
startup clone nomount;
}
executing Memory Script
sql statement: alter system setdb_unique_name =''orcl2'' comment= '''' scope=spfile
sql statement: alter system setDB_CREATE_FILE_DEST =''/u01/app/oracle/oradata'' comment= '''' scope=spfile
sql statement: alter system setcontrol_files =''/u01/app/oracle/oradata/orcl2/control01.ctl'' comment= '''' scope=spfile
sql statement: alter system setFAL_SERVER =''orcl1'' comment= '''' scope=spfile
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 1603411968 bytes
Fixed Size 2253664 bytes
Variable Size 989858976 bytes
Database Buffers 603979776 bytes
Redo Buffers 7319552 bytes
contents of Memory Script:
{
backup as copy current controlfile for standby auxiliary format'/u01/app/oracle/oradata/orcl2/control01.ctl';
}
executing Memory Script
Starting backup at 07-DEC-2015 00:39:29
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
copying standby control file
output file name=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/snapcf_orcl1.f tag=TAG20151207T003929 RECID=8 STAMP=897784770
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 07-DEC-2015 00:39:30
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 clone tempfile1 to new;
switch clone tempfile all;
set newname for clone datafile1 to new;
set newname for clone datafile2 to new;
set newname for clone datafile3 to new;
set newname for clone datafile4 to new;
set newname for clone datafile5 to new;
backup as copy reuse
datafile1 auxiliary format new
datafile2 auxiliary format new
datafile3 auxiliary format new
datafile4 auxiliary format new
datafile5 auxiliary format new
;
}
executing Memory Script
executing command: SET NEWNAME
renamed tempfile 1 to /u01/app/oracle/oradata/ORCL2/datafile/o1_mf_temp_%u_.tmp in control file
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting backup at 07-DEC-2015 00:39:36
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=/u01/app/oracle/oradata/ORCL1/datafile/o1_mf_system_03qo661t_.dbf
output file name=/u01/app/oracle/oradata/ORCL2/datafile/o1_mf_system_05qo66u8_.dbf tag=TAG20151207T003936
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:35
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=/u01/app/oracle/oradata/ORCL1/datafile/o1_mf_sysaux_04qo662m_.dbf
output file name=/u01/app/oracle/oradata/ORCL2/datafile/o1_mf_sysaux_06qo66vb_.dbf tag=TAG20151207T003936
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:26
channel ORA_DISK_1: starting datafile copy
input datafile file number=00005 name=/u01/app/oracle/oradata/ORCL1/datafile/o1_mf_example_05qo663f_.dbf
output file name=/u01/app/oracle/oradata/ORCL2/datafile/o1_mf_example_07qo6705_.dbf tag=TAG20151207T003936
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=/u01/app/oracle/oradata/ORCL1/datafile/o1_mf_undotbs1_06qo663u_.dbf
output file name=/u01/app/oracle/oradata/ORCL2/datafile/o1_mf_undotbs1_08qo670l_.dbf tag=TAG20151207T003936
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=/u01/app/oracle/oradata/ORCL1/datafile/o1_mf_users_07qo6642_.dbf
output file name=/u01/app/oracle/oradata/ORCL2/datafile/o1_mf_users_09qo670o_.dbf tag=TAG20151207T003936
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 07-DEC-2015 00:40:57
contents of Memory Script:
{
switch clone datafile all;
}
executing Memory Script
datafile 1 switched to datafile copy
input datafile copy RECID=8 STAMP=897784857 file name=/u01/app/oracle/oradata/ORCL2/datafile/o1_mf_system_05qo66u8_.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=9 STAMP=897784857 file name=/u01/app/oracle/oradata/ORCL2/datafile/o1_mf_sysaux_06qo66vb_.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=10 STAMP=897784857 file name=/u01/app/oracle/oradata/ORCL2/datafile/o1_mf_undotbs1_08qo670l_.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=11 STAMP=897784857 file name=/u01/app/oracle/oradata/ORCL2/datafile/o1_mf_users_09qo670o_.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=12 STAMP=897784857 file name=/u01/app/oracle/oradata/ORCL2/datafile/o1_mf_example_07qo6705_.dbf
contents of Memory Script:
{
set until scn994046;
recover
standby
clone database
noredo
delete archivelog
;
}
executing Memory Script
executing command: SET until clause
Starting recover at 07-DEC-2015 00:40:57
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=18 device type=DISK
Finished recover at 07-DEC-2015 00:40:57
Finished Duplicate Db at 07-DEC-2015 00:41:11
RMAN> exit
Recovery Manager complete.
$ export ORACLE_SID=orcl1
$ sqlplus /nolog
SQL*Plus: Release 11.2.0.4.0 Production on Mon Dec 7 00:41:34 2015
Copyright (c) 1982, 2013, Oracle.All rights reserved.
SQL> connect /as sysdba
Connected.
SQL> alter system set log_archive_dest_2='service=orcl2 valid_for=(online_logfiles,primary_role) db_unique_name=orcl2';
System altered.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
$ export ORACLE_SID=orcl2
$ sqlplus /nolog
SQL*Plus: Release 11.2.0.4.0 Production on Mon Dec 7 00:42:59 2015
Copyright (c) 1982, 2013, Oracle.All rights reserved.
SQL> connect /as sysdba
Connected.
SQL> select open_mode from v$database;
OPEN_MODE
--------------------
MOUNTED
SQL> select member from v$logfile;
MEMBER
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/ORCL2/onlinelog/o1_mf_3_c6b6zf0y_.log
/u01/app/oracle/oradata/ORCL2/onlinelog/o1_mf_2_c6b6zc7v_.log
/u01/app/oracle/oradata/ORCL2/onlinelog/o1_mf_1_c6b6z9nd_.log
/u01/app/oracle/oradata/ORCL2/onlinelog/o1_mf_4_c6b6zgyk_.log
/u01/app/oracle/fast_recovery_area/ORCL2/onlinelog/o1_mf_4_c6b6zh0z_.log
/u01/app/oracle/oradata/ORCL2/onlinelog/o1_mf_5_c6b6zjq8_.log
/u01/app/oracle/fast_recovery_area/ORCL2/onlinelog/o1_mf_5_c6b6zjrz_.log
/u01/app/oracle/oradata/ORCL2/onlinelog/o1_mf_6_c6b6zlrx_.log
/u01/app/oracle/fast_recovery_area/ORCL2/onlinelog/o1_mf_6_c6b6zltj_.log
/u01/app/oracle/oradata/ORCL2/onlinelog/o1_mf_7_c6b6zo64_.log
/u01/app/oracle/fast_recovery_area/ORCL2/onlinelog/o1_mf_7_c6b6zo7l_.log
MEMBER
--------------------------------------------------------------------------------
/u01/app/oracle/fast_recovery_area/ORCL2/onlinelog/o1_mf_1_c6b6z9qd_.log
/u01/app/oracle/fast_recovery_area/ORCL2/onlinelog/o1_mf_2_c6b6zc9p_.log
/u01/app/oracle/fast_recovery_area/ORCL2/onlinelog/o1_mf_3_c6b6zf2m_.log
14 rows selected.
开启级联物理备库orcl2日志应用：
SQL> recover managed standby database using current logfile disconnect;
Media recovery complete.
确认orcl,orcl1,orcl2 各个数据库的database_role,并创建数据库对象测试：
SQL> select database_role from v$database;
DATABASE_ROLE
----------------
PHYSICAL STANDBY
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
$ export ORACLE_SID=orcl1
$ sqlplus /nolog
SQL*Plus: Release 11.2.0.4.0 Production on Mon Dec 7 00:44:26 2015
Copyright (c) 1982, 2013, Oracle.All rights reserved.
SQL> connect /as sysdba
Connected.
SQL> select database_role from v$database;
DATABASE_ROLE
----------------
PHYSICAL STANDBY
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
$ export ORACLE_SID=orcl
$ sqlplus /nolog
SQL*Plus: Release 11.2.0.4.0 Production on Mon Dec 7 00:44:49 2015
Copyright (c) 1982, 2013, Oracle.All rights reserved.
SQL> connect /as sysdba
Connected.
SQL> select database_role from v$database;
DATABASE_ROLE
----------------
PRIMARY
SQL>
SQL>
SQL>
SQL>
SQL> alter user scott identified by oracle account unlock;
User altered.
SQL> connect scott/tiger
ERROR:
ORA-01017: invalid username/password; logon denied
Warning: You are no longer connected to ORACLE.
SQL> connect scott/oracle
Connected.
SQL> create table test as select * from user_objects;
Table created.
SQL> commit;
Commit complete.
SQL> select tname from tab;
TNAME
------------------------------
BONUS
DEPT
EMP
SALGRADE
TEST
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
$ export ORACLE_SID=orcl1
$ sqlplus scott/oracle
SQL*Plus: Release 11.2.0.4.0 Production on Mon Dec 7 00:46:27 2015
Copyright (c) 1982, 2013, Oracle.All rights reserved.
ERROR:
ORA-01033: ORACLE initialization or shutdown in progress
Process ID: 0
Session ID: 0 Serial number: 0
Enter user-name: exit
Enter password:
ERROR:
ORA-01005: null password given; logon denied
Enter user-name:
ERROR:
ORA-01017: invalid username/password; logon denied
SP2-0157: unable to CONNECT to ORACLE after 3 attempts, exiting SQL*Plus
$
$
$ export ORACLE_SID=orcl
$ sqlplus /nolog
SQL*Plus: Release 11.2.0.4.0 Production on Mon Dec 7 00:46:56 2015
Copyright (c) 1982, 2013, Oracle.All rights reserved.
SQL> connect /as sysdba
Connected.
SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
$ export ORACLE_SID=orcl1
$ sqlplus /nolog
SQL*Plus: Release 11.2.0.4.0 Production on Mon Dec 7 00:47:32 2015
Copyright (c) 1982, 2013, Oracle.All rights reserved.
SQL> connect /as sysdba
Connected.
SQL> alter database recover managed standby database cancel;
Database altered.
SQL> alter database open;
Database altered.
SQL> select open_mode from v$database;
OPEN_MODE
--------------------
READ ONLY
SQL> select count(*) from scott.test;
COUNT(*)
----------
7
SQL> shudown abort;
SP2-0734: unknown command beginning "shudown ab..." - rest of line ignored.
SQL> connect /as sysdba
Connected.
SQL> alter database recover managed standby database disconnect from session;
Database altered.
SQL> select open_mode from v$database;
OPEN_MODE
--------------------
READ ONLY WITH APPLY
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
$ export ORACLE_SID=orcl2
$ sqlplus /nolog
SQL*Plus: Release 11.2.0.4.0 Production on Mon Dec 7 00:49:52 2015
Copyright (c) 1982, 2013, Oracle.All rights reserved.
SQL> connect /as sysdba
Connected.
SQL> alter database recover managed standby database cancel;
Database altered.
SQL> alter database open;
Database altered.
SQL> select count(*) from scott.test;
COUNT(*)
----------
7
SQL> alter database recover managed standby database disconnect from session;
Database altered.
SQL> select open_mode from v$database;
OPEN_MODE
--------------------
READ ONLY WITH APPLY
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
$
$
$
$
$
$
$
$
2015/12/7 13:51:11
复制脚本备注：
orcl------->orcl1:
DUPLICATE DATABASE
FOR STANDBY
FROM ACTIVE DATABASE
DORECOVER
SPFILE
SET db_unique_name='orcl1'
SET DB_CREATE_FILE_DEST='/u01/app/oracle/oradata'
SET 'control_files'='/u01/app/oracle/oradata/orcl1/control01.ctl'
SET FAL_SERVER='orcl'
;
orcl1---------> orcl2:
DUPLICATE DATABASE
FOR STANDBY
FROM ACTIVE DATABASE
DORECOVER
SPFILE
SET db_unique_name ='orcl2'
SET DB_CREATE_FILE_DEST ='/u01/app/oracle/oradata'
SET control_files='/u01/app/oracle/oradata/orcl2/control01.ctl'
SET FAL_SERVER='orcl1'
;
页:
[1]