溫馨提示×
您好，登錄后才能下訂單哦！
點(diǎn)擊 登錄注冊(cè) 即表示同意《億速云用戶服務(wù)條款》
您好，登錄后才能下訂單哦！
一.實(shí)例環(huán)境說明
主庫環(huán)境說明
操作系統(tǒng):Oracle Linux 7.1
數(shù)據(jù)庫版本:12.2.0.1
IP:10.10.10.190/10.10.10.191
主機(jī)名:cs1/cs2
數(shù)據(jù)庫名:cs
Db_unique_name:cs
實(shí)例名(SID)cs1/cs2
備庫環(huán)境說明
操作系統(tǒng):Oracle Linux 7.1
數(shù)據(jù)庫版本:12.2.0.1
IP:10.10.10.171/172
主機(jī)名:jytest1/jytest2
數(shù)據(jù)庫名:cs
Db_unique_nmae:cs_dg
實(shí)例名(SID)cs1/cs2
二.配置過程
2.1修改主庫啟用歸檔與force logging
首先檢查主庫是否啟用歸檔
[oracle@cs1 ~]$ sqlplus /nolog SQL*Plus: Release 12.2.0.1.0 Production on Fri May 11 12:54:07 2018 Copyright (c) 1982, 2016, Oracle. All rights reserved. SQL> conn sys/abcd@cs as sysdba Connected. SQL> archive log list Database log mode No Archive Mode Automatic archival Disabled Archive destination /u01/app/oracle/product/12.2.0/db/dbs/arch Oldest online log sequence 135 Current log sequence 136 SQL> [oracle@cs2 ~]$ sqlplus /nolog SQL*Plus: Release 12.2.0.1.0 Production on Fri May 11 12:54:22 2018 Copyright (c) 1982, 2016, Oracle. All rights reserved. SQL> conn sys/abcd@cs as sysdba Connected. SQL> archive log list Database log mode No Archive Mode Automatic archival Disabled Archive destination /u01/app/oracle/product/12.2.0/db/dbs/arch Oldest online log sequence 116 Current log sequence 117 SQL>
現(xiàn)在主庫沒有啟用歸檔，執(zhí)行下面的命令來對(duì)主庫啟用歸檔
SQL> alter system set log_archive_dest_1='location=+data/arch/' scope=both sid='*'; System altered. SQL> show parameter log_archive_for NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ log_archive_format string %t_%s_%r.dbf SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> alter system set log_archive_dest_1='location=+data/arch/' scope=both sid='*'; System altered. SQL> show parameter log_archive_for NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ log_archive_format string %t_%s_%r.dbf SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> startup mount ORACLE instance started. Total System Global Area 2147483648 bytes Fixed Size 8794848 bytes Variable Size 671092000 bytes Database Buffers 1459617792 bytes Redo Buffers 7979008 bytes Database mounted. SQL> alter database archivelog; Database altered. SQL> alter database open; Database altered. SQL> archive log list Database log mode Archive Mode Automatic archival Enabled Archive destination +DATA/arch/ Oldest online log sequence 136 Next log sequence to archive 137 Current log sequence 137
這里主庫已經(jīng)啟用了歸檔
啟用force logging
SQL> alter database force logging; Database altered. SQL> select force_logging from v$database; FORCE_LOGGING --------------------------------------- YES SQL> select force_logging from v$database; FORCE_LOGGING --------------------------------------- YES
2.2 給備庫創(chuàng)建密碼文件
這里通過復(fù)制主庫的密碼文件來創(chuàng)建備庫密碼文件
[oracle@jytest1 dbs]$ scp oracle@10.10.10.190:/u01/app/oracle/product/12.2.0/db/dbs/orapwcs1 /u01/app/oracle/product/12.2.0/db/dbs/orapwcs2 The authenticity of host '10.10.10.190 (10.10.10.190)' can't be established. ECDSA key fingerprint is 7f:1f:9a:0f:8b:d1:e0:17:32:08:12:73:d8:1d:9c:da. Are you sure you want to continue connecting (yes/no)? yes Warning: Permanently added '10.10.10.190' (ECDSA) to the list of known hosts. oracle@10.10.10.190's password: orapwcs1 100% 3584 3.5KB/s 00:00 [oracle@jytest2 dbs]$ ls -lrt [oracle@jytest2 dbs]$ scp oracle@10.10.10.190:/u01/app/oracle/product/12.2.0/db/dbs/orapwcs1 /u01/app/oracle/product/12.2.0/db/dbs/orapwcs2 The authenticity of host '10.10.10.190 (10.10.10.190)' can't be established. ECDSA key fingerprint is 7f:1f:9a:0f:8b:d1:e0:17:32:08:12:73:d8:1d:9c:da. Are you sure you want to continue connecting (yes/no)? yes Warning: Permanently added '10.10.10.190' (ECDSA) to the list of known hosts. oracle@10.10.10.190's password: orapwcs1 100% 3584 3.5KB/s 00:00 [oracle@jytest2 dbs]$ ls -lrt
2.3.給備庫創(chuàng)建參數(shù)文件
使用主庫的參數(shù)文件進(jìn)行創(chuàng)建
SQL> create pfile='/u01/app/oracle/product/12.2.0/db/dbs/initcs.ora' from spfile='+DATA/CS/PARAMETERFILE/spfile.287.970602765'; File created. [oracle@jytest1 dbs]$ cat initcs1_temp.ora *.audit_file_dest='/u01/app/oracle/admin/cs/adump' *.audit_trail='db' *.cluster_database=true *.compatible='12.2.0' *.control_files='+FRA/CS_DG/CONTROLFILE/control01.ctl' *.db_block_size=8192 *.db_name='cs' *.db_unique_name='cs_dg' *.db_create_file_dest='+FRA' *.diagnostic_dest='/u01/app/oracle' *.dispatchers='(PROTOCOL=TCP) (SERVICE=csXDB)' *.enable_pluggable_database=true family:dw_helper.instance_mode='read-only' log_archive_config='DG_CONFIG=(cs,cs_dg)' cs2.instance_number=2 cs1.instance_number=1 *.log_archive_format='%t_%s_%r.dbf' *.log_archive_max_processes=30 *.log_archive_dest_1='LOCATION=+test/arch/cs_dg/ valid_for=(ALL_LOGFILES,ALL_ROLES) db_unique_name=cs_dg' *.log_archive_dest_2='service=cs LGWR ASYNC valid_for=(online_logfiles,primary_role) db_unique_name=cs ' *.nls_language='AMERICAN' *.nls_territory='AMERICA' *.open_cursors=300 *.pga_aggregate_target=1g *.processes=320 *.remote_login_passwordfile='exclusive' *.sga_target=2g cs2.thread=2 cs1.thread=1 cs2.undo_tablespace='UNDOTBS2' cs1.undo_tablespace='UNDOTBS1' *.db_file_name_convert= '+DATA/CS/','+FRA/CS_DG/' *.fal_client='cs_dg' *.fal_server='cs' *.log_file_name_convert= '+DATA/CS/','+FRA/CS_DG/' *.standby_file_management='auto'
SQL> create spfile='+fra/cs/parameterfile/spfilecs.ora' from pfile='/u01/app/oracle/product/12.2.0/db/dbs/initcs1_temp.ora'; File created. [oracle@jytest1 dbs]$ vi initcs1.ora spfile='+fra/cs/parameterfile/spfilecs.ora' [oracle@jytest2 dbs]$ vi initcs2.ora spfile='+fra/cs/parameterfile/spfilecs.ora'
2.4為主庫和備庫配置監(jiān)聽
主庫是rac已經(jīng)配置了監(jiān)聽，備庫也是RAC需要對(duì)其配置監(jiān)聽
備庫:節(jié)點(diǎn)1
[grid@jytest1 admin]$ vi listener.ora LISTENER_SCAN2=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN2)))) # line added by Agent MGMTLSNR=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=MGMTLSNR)))) # line added by Agent LISTENER_SCAN3=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN3)))) # line added by Agent LISTENER=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))) # line added by Agent LISTENER_SCAN1=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN1)))) # line added by Agent ASMNET1LSNR_ASM=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=ASMNET1LSNR_ASM)))) # line added by Agent ENABLE_GLOBAL_DYNAMIC_ENDPOINT_ASMNET1LSNR_ASM=ON # line added by Agent VALID_NODE_CHECKING_REGISTRATION_ASMNET1LSNR_ASM=SUBNET # line added by Agent ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN1=ON # line added by Agent VALID_NODE_CHECKING_REGISTRATION_LISTENER_SCAN1=OFF # line added by Agent ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON # line added by Agent VALID_NODE_CHECKING_REGISTRATION_LISTENER=SUBNET # line added by Agent ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN3=ON # line added by Agent VALID_NODE_CHECKING_REGISTRATION_LISTENER_SCAN3=OFF # line added by Agent ENABLE_GLOBAL_DYNAMIC_ENDPOINT_MGMTLSNR=ON # line added by Agent VALID_NODE_CHECKING_REGISTRATION_MGMTLSNR=SUBNET # line added by Agent ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN2=ON # line added by Agent VALID_NODE_CHECKING_REGISTRATION_LISTENER_SCAN2=OFF # line added by Agent SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (SID_NAME = cs2) (ORACLE_HOME =/u01/app/oracle/product/12.2.0/db) (GLOBAL_DBNAME=cs_dg) ) )
備庫:節(jié)點(diǎn)2
[grid@jytest2 admin]$ vi listener.ora LISTENER_SCAN2=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN2)))) # line added by Agent MGMTLSNR=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=MGMTLSNR)))) # line added by Agent LISTENER_SCAN3=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN3)))) # line added by Agent LISTENER=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))) # line added by Agent LISTENER_SCAN1=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN1)))) # line added by Agent ASMNET1LSNR_ASM=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=ASMNET1LSNR_ASM)))) # line added by Agent ENABLE_GLOBAL_DYNAMIC_ENDPOINT_ASMNET1LSNR_ASM=ON # line added by Agent VALID_NODE_CHECKING_REGISTRATION_ASMNET1LSNR_ASM=SUBNET # line added by Agent ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN1=ON # line added by Agent VALID_NODE_CHECKING_REGISTRATION_LISTENER_SCAN1=OFF # line added by Agent ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON # line added by Agent VALID_NODE_CHECKING_REGISTRATION_LISTENER=SUBNET # line added by Agent ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN3=ON # line added by Agent VALID_NODE_CHECKING_REGISTRATION_LISTENER_SCAN3=OFF # line added by Agent ENABLE_GLOBAL_DYNAMIC_ENDPOINT_MGMTLSNR=ON # line added by Agent VALID_NODE_CHECKING_REGISTRATION_MGMTLSNR=SUBNET # line added by Agent ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN2=ON # line added by Agent VALID_NODE_CHECKING_REGISTRATION_LISTENER_SCAN2=OFF # line added by Agent SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (SID_NAME = cs2) (ORACLE_HOME =/u01/app/oracle/product/12.2.0/db) (GLOBAL_DBNAME=cs_dg) ) )
[grid@jytest1 admin]$ srvctl stop listener -n jytest1 [grid@jytest1 admin]$ srvctl stop listener -n jytest2 [grid@jytest1 admin]$ srvctl start listener -n jytest1 [grid@jytest1 admin]$ srvctl start listener -n jytest2 [grid@jytest1 admin]$ lsnrctl status LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 14-MAY-2018 22:33:06 Copyright (c) 1991, 2016, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER))) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 12.2.0.1.0 - Production Start Date 14-MAY-2018 22:32:08 Uptime 0 days 0 hr. 0 min. 57 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /u01/app/product/12.2.0/crs/network/admin/listener.ora Listener Log File /u01/app/grid/diag/tnslsnr/jytest1/listener/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER))) (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.10.10.171)(PORT=1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.10.10.175)(PORT=1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=jytest1.jydba.net)(PORT=5500))(Security=(my_wallet_directory=/u01/app/oracle/product/12.2.0/db/admin/jy/xdb_wallet))(Presentation=HTTP)(Session=RAW)) Services Summary... Service "+ASM" has 1 instance(s). Instance "+ASM1", status READY, has 1 handler(s) for this service... Service "+ASM_CRS" has 1 instance(s). Instance "+ASM1", status READY, has 1 handler(s) for this service... Service "+ASM_DATA" has 1 instance(s). Instance "+ASM1", status READY, has 1 handler(s) for this service... Service "+ASM_FRA" has 1 instance(s). Instance "+ASM1", status READY, has 1 handler(s) for this service... Service "+ASM_TEST" has 1 instance(s). Instance "+ASM1", status READY, has 1 handler(s) for this service... Service "5f9ac6865e87549fe053ab828a0ade94" has 1 instance(s). Instance "jy1", status READY, has 1 handler(s) for this service... Service "cs_dg" has 1 instance(s). Instance "cs1", status UNKNOWN, has 1 handler(s) for this service... Service "jy" has 1 instance(s). Instance "jy1", status READY, has 1 handler(s) for this service... Service "jyXDB" has 1 instance(s). Instance "jy1", status READY, has 1 handler(s) for this service... Service "jy_srv" has 1 instance(s). Instance "jy1", status READY, has 1 handler(s) for this service... Service "jypdb" has 1 instance(s). Instance "jy1", status READY, has 1 handler(s) for this service... The command completed successfully [grid@jytest2 ~]$ lsnrctl status LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 14-MAY-2018 22:33:10 Copyright (c) 1991, 2016, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER))) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 12.2.0.1.0 - Production Start Date 14-MAY-2018 22:32:16 Uptime 0 days 0 hr. 0 min. 54 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /u01/app/product/12.2.0/crs/network/admin/listener.ora Listener Log File /u01/app/grid/diag/tnslsnr/jytest2/listener/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER))) (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.10.10.172)(PORT=1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.10.10.176)(PORT=1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=jytest2.jydba.net)(PORT=5500))(Security=(my_wallet_directory=/u01/app/oracle/product/12.2.0/db/admin/jy/xdb_wallet))(Presentation=HTTP)(Session=RAW)) Services Summary... Service "+ASM" has 1 instance(s). Instance "+ASM2", status READY, has 1 handler(s) for this service... Service "+ASM_CRS" has 1 instance(s). Instance "+ASM2", status READY, has 1 handler(s) for this service... Service "+ASM_DATA" has 1 instance(s). Instance "+ASM2", status READY, has 1 handler(s) for this service... Service "+ASM_FRA" has 1 instance(s). Instance "+ASM2", status READY, has 1 handler(s) for this service... Service "+ASM_TEST" has 1 instance(s). Instance "+ASM2", status READY, has 1 handler(s) for this service... Service "5f9ac6865e87549fe053ab828a0ade94" has 1 instance(s). Instance "jy2", status READY, has 1 handler(s) for this service... Service "cs_dg" has 1 instance(s). Instance "cs2", status UNKNOWN, has 1 handler(s) for this service... Service "jy" has 1 instance(s). Instance "jy2", status READY, has 1 handler(s) for this service... Service "jyXDB" has 1 instance(s). Instance "jy2", status READY, has 1 handler(s) for this service... Service "jypdb" has 1 instance(s). Instance "jy2", status READY, has 1 handler(s) for this service... The command completed successfully
2.5為主庫和備庫創(chuàng)建Oracle Net服務(wù)名
主庫:節(jié)點(diǎn)1
[oracle@cs1 admin]$ vi tnsnames.ora # tnsnames.ora Network Configuration File: /u01/app/oracle/product/12.2.0/db/network/admin/tnsnames.ora # Generated by Oracle configuration tools. CS = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST =10.10.10.149)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = cs) ) ) CS_DG = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST =10.10.10.175)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = cs_dg) (UR=A) ) )
主庫:節(jié)點(diǎn)2
[oracle@cs2 admin]$ vi tnsnames.ora # tnsnames.ora Network Configuration File: /u01/app/oracle/product/12.2.0/db/network/admin/tnsnames.ora # Generated by Oracle configuration tools. CS = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST =10.10.10.134)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = cs) ) ) CS_DG = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST =10.10.10.176)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = cs_dg) (UR=A) ) )
備庫:節(jié)點(diǎn)1
[oracle@jytest1 admin]$ vi tnsnames.ora # tnsnames.ora.jytest1 Network Configuration File: /u01/app/oracle/product/12.2.0/db/network/admin/tnsnames.ora.jytest1 # Generated by Oracle configuration tools. CS = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST =10.10.10.149)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = cs) ) )
備庫:節(jié)點(diǎn)2
[oracle@jytest2 admin]$ vi tnsnames.ora # tnsnames.ora.jytest2 Network Configuration File: /u01/app/oracle/product/12.2.0/db/network/admin/tnsnames.ora.jytest2 # Generated by Oracle configuration tools. CS = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST =10.10.10.134)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = cs) ) )
使用備份創(chuàng)建備庫
[oracle@jytest1 admin]$ export ORACLE_SID=cs1 [oracle@jytest1 admin]$ sqlplus / as sysdba SQL*Plus: Release 12.2.0.1.0 Production on Tue May 15 00:06:43 2018 Copyright (c) 1982, 2016, Oracle. All rights reserved. Connected to an idle instance. SQL> startup nomount ORACLE instance started. Total System Global Area 2147483648 bytes Fixed Size 8794848 bytes Variable Size 721423648 bytes Database Buffers 1409286144 bytes Redo Buffers 7979008 bytes
[oracle@cs1 admin]$ rman target sys/abcd@cs auxiliary sys/abcd@cs_dg
Recovery Manager: Release 12.2.0.1.0 - Production on Mon May 14 19:43:21 2018
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
connected to target database: CS (DBID=1386528187)
connected to auxiliary database: CS (not mounted)
RMAN> duplicate target database for standby from active database nofilenamecheck;
Starting Duplicate Db at 14-MAY-18
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=197 instance=cs1 device type=DISK
contents of Memory Script:
{
backup as copy reuse
targetfile '+DATA/CS/PASSWORD/pwdcs.271.970601731' auxiliary format
'/u01/app/oracle/product/12.2.0/db/dbs/orapwcs1' ;
}
executing Memory Script
Starting backup at 14-MAY-18
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=291 instance=cs1 device type=DISK
Finished backup at 14-MAY-18
contents of Memory Script:
{
restore clone from service 'cs' standby controlfile;
}
executing Memory Script
Starting restore at 14-MAY-18
using channel ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service cs
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03
output file name=+FRA/CS_DG/CONTROLFILE/control01.ctl
Finished restore at 14-MAY-18
contents of Memory Script:
{
sql clone 'alter database mount standby database';
}
executing Memory Script
sql statement: alter database mount standby database
RMAN-05529: warning: DB_FILE_NAME_CONVERT resulted in invalid ASM names; names changed to disk group only.
contents of Memory Script:
{
set newname for tempfile 1 to
"+FRA";
set newname for tempfile 2 to
"+FRA";
switch clone tempfile all;
set newname for datafile 1 to
"+FRA";
set newname for datafile 3 to
"+FRA";
set newname for datafile 4 to
"+FRA";
set newname for datafile 5 to
"+FRA";
set newname for datafile 6 to
"+FRA";
set newname for datafile 7 to
"+FRA";
set newname for datafile 8 to
"+FRA";
set newname for datafile 9 to
"+FRA";
restore
from nonsparse from service
'cs' clone database
;
sql 'alter system archive log current';
}
executing Memory Script
executing command: SET NEWNAME
executing command: SET NEWNAME
renamed tempfile 1 to +FRA in control file
renamed tempfile 2 to +FRA in control file
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting restore at 14-MAY-18
using channel ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service cs
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00001 to +FRA
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:16
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service cs
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00003 to +FRA
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:45
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service cs
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00004 to +FRA
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:04
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service cs
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00005 to +FRA
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:08
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service cs
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00006 to +FRA
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:07
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service cs
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00007 to +FRA
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:02
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service cs
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00008 to +FRA
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:04
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service cs
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00009 to +FRA
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03
Finished restore at 14-MAY-18
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=12 STAMP=976150821 file name=+FRA/CS_DG/DATAFILE/system.342.976150731
datafile 3 switched to datafile copy
input datafile copy RECID=13 STAMP=976150821 file name=+FRA/CS_DG/DATAFILE/sysaux.341.976150747
datafile 4 switched to datafile copy
input datafile copy RECID=14 STAMP=976150821 file name=+FRA/CS_DG/DATAFILE/undotbs1.340.976150793
datafile 5 switched to datafile copy
input datafile copy RECID=15 STAMP=976150821 file name=+FRA/CS_DG/67369AA1C9AA3E71E053BE828A0A8262/DATAFILE/system.347.976150795
datafile 6 switched to datafile copy
input datafile copy RECID=16 STAMP=976150822 file name=+FRA/CS_DG/67369AA1C9AA3E71E053BE828A0A8262/DATAFILE/sysaux.351.976150803
datafile 7 switched to datafile copy
input datafile copy RECID=17 STAMP=976150822 file name=+FRA/CS_DG/DATAFILE/users.350.976150811
datafile 8 switched to datafile copy
input datafile copy RECID=18 STAMP=976150823 file name=+FRA/CS_DG/67369AA1C9AA3E71E053BE828A0A8262/DATAFILE/undotbs1.348.976150813
datafile 9 switched to datafile copy
input datafile copy RECID=19 STAMP=976150823 file name=+FRA/CS_DG/DATAFILE/undotbs2.345.976150817
Finished Duplicate Db at 14-MAY-18
[oracle@jytest1 admin]$ sqlplus / as sysdba SQL*Plus: Release 12.2.0.1.0 Production on Tue May 15 01:05:35 2018 Copyright (c) 1982, 2016, Oracle. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production SQL> select instance_name,status from v$instance; INSTANCE_NAME STATUS ---------------- ------------ cs1 MOUNTED SQL> select name from v$datafile; NAME -------------------------------------------------------------------------------- +FRA/CS_DG/DATAFILE/system.342.976150731 +FRA/CS_DG/DATAFILE/sysaux.341.976150747 +FRA/CS_DG/DATAFILE/undotbs1.340.976150793 +FRA/CS_DG/67369AA1C9AA3E71E053BE828A0A8262/DATAFILE/system.347.976150795 +FRA/CS_DG/67369AA1C9AA3E71E053BE828A0A8262/DATAFILE/sysaux.351.976150803 +FRA/CS_DG/DATAFILE/users.350.976150811 +FRA/CS_DG/67369AA1C9AA3E71E053BE828A0A8262/DATAFILE/undotbs1.348.976150813 +FRA/CS_DG/DATAFILE/undotbs2.345.976150817 8 rows selected.
對(duì)物理備庫創(chuàng)建備重做日志文件,查詢主庫的聯(lián)機(jī)重做日志文件，備重做日志文件的大小應(yīng)該與主庫聯(lián)機(jī)重做日志文件的大小相同，備重做日志文件組的數(shù)量應(yīng)該比主庫聯(lián)機(jī)重做日志文件組多一組，計(jì)算公式為(maximum # of logfiles +1) * maximum # of threads
SQL> select member from v$logfile;
MEMBER
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+DATA/CS/ONLINELOG/group_2.277.970601985
+DATA/CS/ONLINELOG/group_1.278.970601985
+DATA/CS/ONLINELOG/group_3.285.970602759
+DATA/CS/ONLINELOG/group_4.286.970602761
SQL> select group#,thread#,bytes/1024/1024 from v$log;
GROUP# THREAD# BYTES/1024/1024
---------- ---------- ---------------
1 1 200
2 1 200
3 2 200
4 2 200
SQL> select member from v$logfile;
MEMBER
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+FRA/CS_DG/ONLINELOG/group_2.346.976150835
+FRA/CS_DG/ONLINELOG/group_1.349.976150825
+FRA/CS_DG/ONLINELOG/group_3.344.976150843
+FRA/CS_DG/ONLINELOG/group_4.352.976150855
SQL> select group#,thread#,bytes/1024/1024 from v$log;
GROUP# THREAD# BYTES/1024/1024
---------- ---------- ---------------
1 1 200
2 1 200
3 2 200
4 2 200
SQL> alter database add standby logfile thread 1 group 5('+FRA/CS_DG/ONLINELOG/redo05.log') size 200M;
Database altered.
SQL> alter database add standby logfile thread 1 group 6('+FRA/CS_DG/ONLINELOG/redo06.log') size 200M;
Database altered.
SQL> alter database add standby logfile thread 1 group 7('+FRA/CS_DG/ONLINELOG/redo07.log') size 200M;
Database altered.
SQL> alter database add standby logfile thread 2 group 8('+FRA/CS_DG/ONLINELOG/redo08.log') size 200M;
Database altered.
SQL> alter database add standby logfile thread 2 group 9('+FRA/CS_DG/ONLINELOG/redo09.log') size 200M;
Database altered.
SQL> alter database add standby logfile thread 2 group 10('+FRA/CS_DG/ONLINELOG/redo10.log') size 200M;
Database altered.
設(shè)置主庫相關(guān)初始化參數(shù)
log_archive_config='DG_CONFIG=(cs,cs_dg)' log_archive_dest_1='LOCATION=+data/arch/ valid_for=(ALL_LOGFILES,ALL_ROLES) db_unique_name=cs' log_archive_dest_2='SERVICE=cs_dg LGWR ASYNC valid_for=(ONLINE_LOGFILES,PRIMARY_ROLE) db_unique_name=cs_dg' log_archive_dest_state_1=enable log_archive_dest_state_2=enable remote_login_passwordfile=exclusive log_archive_format= %t_%s_%r.dbf log_archive_max_processes=30
主庫以備庫角色運(yùn)行時(shí)需要額外設(shè)置的參數(shù)。這些參數(shù)當(dāng)主庫被轉(zhuǎn)換為備庫角色運(yùn)行時(shí)生效:
fal_server='cs_dg' fal_client='cs' db_file_name_convert='+FRA/CS_DG/', '+DATA/CS/' log_file_name_convert='+FRA/CS_DG/', '+DATA/CS/' standby_file_management='auto'
SQL> alter system set log_archive_dest_1='LOCATION=+data/arch/ valid_for=(ALL_LOGFILES,ALL_ROLES) db_unique_name=cs' scope=both sid='*'; System altered. SQL> alter system set log_archive_dest_2='SERVICE=cs_dg LGWR ASYNC valid_for=(ONLINE_LOGFILES,PRIMARY_ROLE) db_unique_name=cs_dg' scope=both sid='*'; System altered. SQL> alter system set log_archive_dest_state_1=enable scope=both sid='*'; System altered. SQL> alter system set log_archive_dest_state_2=enable scope=both sid='*'; System altered. SQL> alter system set log_archive_max_processes=30 scope=both sid='*'; System altered. SQL> alter system set log_archive_config='DG_CONFIG=(cs,cs_dg)' scope=both sid='*'; System altered. SQL> alter system set fal_server='cs_dg' scope=both sid='*'; System altered. SQL> alter system set fal_client='cs' scope=both sid='*'; System altered. SQL> alter system set db_file_name_convert='+FRA/CS_DG/', '+DATA/CS/' scope=spfile sid='*'; System altered. SQL> alter system set log_file_name_convert='+FRA/CS_DG/', '+DATA/CS/' scope=spfile sid='*'; System altered. SQL> alter system set standby_file_management='auto' scope=both sid='*'; System altered.
給主庫創(chuàng)建備重做日志文件，備重做日志文件組比聯(lián)機(jī)重做日志文件多一組，主庫是RAC數(shù)據(jù)庫，在創(chuàng)建備重做日志文件時(shí)需要指定thread號(hào):
SQL> alter database add standby logfile thread 1 group 5('+DATA/CS/ONLINELOG/redo05.log') size 200M;
Database altered.
SQL> alter database add standby logfile thread 1 group 6('+DATA/CS/ONLINELOG/redo06.log') size 200M;
Database altered.
SQL> alter database add standby logfile thread 1 group 7('+DATA/CS/ONLINELOG/redo07.log') size 200M;
Database altered.
SQL> alter database add standby logfile thread 2 group 8('+DATA/CS/ONLINELOG/redo08.log') size 200M;
Database altered.
SQL> alter database add standby logfile thread 2 group 9('+DATA/CS/ONLINELOG/redo09.log') size 200M;
Database altered.
SQL> alter database add standby logfile thread 2 group 10('+DATA/CS/ONLINELOG/redo10.log') size 200M;
Database altered.
Oracle用戶使用srvctl在備庫的另一節(jié)點(diǎn)注冊(cè)備庫和備庫實(shí)例
[oracle@jytest1 dbs]$ srvctl add database -db cs_dg -oraclehome /u01/app/oracle/product/12.2.0/db/ -dbtype RAC -spfile +FRA/CS_DG/PARAMETERFILE/spfilecs.ora -pwfile +FRA/CS_DG/PASSWORD/pwdcs -role physical_standby -startoption open -stopoption immediate -dbname cs -policy automatic -diskgroup data,fra,test [oracle@jytest1 dbs]$ srvctl config database -db cs_dg Database unique name: cs_dg Database name: cs Oracle home: /u01/app/oracle/product/12.2.0/db/ Oracle user: oracle Spfile: +FRA/CS_DG/PARAMETERFILE/spfilecs.ora Password file: +FRA/CS_DG/PASSWORD/pwdcs Domain: Start options: open Stop options: immediate Database role: PHYSICAL_STANDBY Management policy: AUTOMATIC Server pools: Disk Groups: DATA,FRA,TEST Mount point paths: Services: Type: RAC Start concurrency: Stop concurrency: OSDBA group: dba OSOPER group: oper Database instances: Configured nodes: CSS critical: no CPU count: 0 Memory target: 0 Maximum memory: 0 Default network number for database services: Database is administrator managed
添加實(shí)例
[oracle@jytest1 dbs]$ srvctl add instance -db cs_dg -instance cs1 -node jytest1 [oracle@jytest1 dbs]$ srvctl add instance -db cs_dg -instance cs2 -node jytest2 [oracle@jytest1 dbs]$ srvctl config database -db cs_dg Database unique name: cs_dg Database name: cs Oracle home: /u01/app/oracle/product/12.2.0/db/ Oracle user: oracle Spfile: +FRA/CS_DG/PARAMETERFILE/spfilecs.ora Password file: +FRA/CS_DG/PASSWORD/pwdcs Domain: Start options: open Stop options: immediate Database role: PHYSICAL_STANDBY Management policy: AUTOMATIC Server pools: Disk Groups: DATA,FRA,TEST Mount point paths: Services: Type: RAC Start concurrency: Stop concurrency: OSDBA group: dba OSOPER group: oper Database instances: cs1,cs2 Configured nodes: jytest1,jytest2 CSS critical: no CPU count: 0 Memory target: 0 Maximum memory: 0 Default network number for database services: Database is administrator managed [grid@jytest1 ~]$ srvctl start database -db cs_dg [grid@jytest1 ~]$ crsctl stat res -t -------------------------------------------------------------------------------- Name Target State Server State details -------------------------------------------------------------------------------- Local Resources -------------------------------------------------------------------------------- ora.ASMNET1LSNR_ASM.lsnr ONLINE ONLINE jytest1 STABLE ONLINE ONLINE jytest2 STABLE ora.CRS.dg ONLINE ONLINE jytest1 STABLE ONLINE ONLINE jytest2 STABLE ora.DATA.dg ONLINE ONLINE jytest1 STABLE ONLINE ONLINE jytest2 STABLE ora.FRA.dg ONLINE ONLINE jytest1 STABLE ONLINE ONLINE jytest2 STABLE ora.LISTENER.lsnr ONLINE ONLINE jytest1 STABLE ONLINE ONLINE jytest2 STABLE ora.TEST.dg ONLINE ONLINE jytest1 STABLE ONLINE ONLINE jytest2 STABLE ora.chad ONLINE ONLINE jytest1 STABLE ONLINE ONLINE jytest2 STABLE ora.net1.network ONLINE ONLINE jytest1 STABLE ONLINE ONLINE jytest2 STABLE ora.ons ONLINE ONLINE jytest1 STABLE ONLINE ONLINE jytest2 STABLE ora.proxy_advm OFFLINE OFFLINE jytest1 STABLE OFFLINE OFFLINE jytest2 STABLE -------------------------------------------------------------------------------- Cluster Resources -------------------------------------------------------------------------------- ora.LISTENER_SCAN1.lsnr 1 ONLINE ONLINE jytest2 STABLE ora.LISTENER_SCAN2.lsnr 1 ONLINE ONLINE jytest1 STABLE ora.LISTENER_SCAN3.lsnr 1 ONLINE ONLINE jytest1 STABLE ora.MGMTLSNR 1 ONLINE ONLINE jytest1 169.254.123.145 88.8 8.88.1,STABLE ora.asm 1 ONLINE ONLINE jytest1 Started,STABLE 2 ONLINE ONLINE jytest2 Started,STABLE 3 ONLINE OFFLINE STABLE ora.cs_dg.db 1 ONLINE ONLINE jytest1 Open,Readonly,HOME=/ u01/app/oracle/produ ct/12.2.0/db/,STABLE 2 ONLINE ONLINE jytest2 Open,Readonly,HOME=/ u01/app/oracle/produ ct/12.2.0/db/,STABLE ora.cvu 1 ONLINE ONLINE jytest1 STABLE ora.jy.jy_srv.svc 1 ONLINE ONLINE jytest1 STABLE ora.jytest1.vip 1 ONLINE ONLINE jytest1 STABLE ora.jytest2.vip 1 ONLINE ONLINE jytest2 STABLE ora.mgmtdb 1 ONLINE ONLINE jytest1 Open,STABLE ora.qosmserver 1 ONLINE ONLINE jytest1 STABLE ora.scan1.vip 1 ONLINE ONLINE jytest2 STABLE ora.scan2.vip 1 ONLINE ONLINE jytest1 STABLE ora.scan3.vip 1 ONLINE ONLINE jytest1 STABLE --------------------------------------------------------------------------------
[oracle@jytest1 ~]$ export ORACLE_SID=cs1 [oracle@jytest1 ~]$ sqlplus / as sysdba SQL*Plus: Release 12.2.0.1.0 Production on Tue May 15 23:02:41 2018 Copyright (c) 1982, 2016, Oracle. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production SQL> set line 120 SQL> select name, database_role, open_mode from gv$database; NAME DATABASE_ROLE OPEN_MODE --------- ---------------- -------------------- CS PHYSICAL STANDBY READ ONLY CS PHYSICAL STANDBY READ ONLY [oracle@jytest2 dbs]$ export ORACLE_SID=cs2 [oracle@jytest2 dbs]$ sqlplus / as sysdba SQL*Plus: Release 12.2.0.1.0 Production on Tue May 15 23:02:47 2018 Copyright (c) 1982, 2016, Oracle. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production SQL> set line 120 SQL> select name, database_role, open_mode from gv$database; NAME DATABASE_ROLE OPEN_MODE --------- ---------------- -------------------- CS PHYSICAL STANDBY READ ONLY CS PHYSICAL STANDBY READ ONLY
在備庫的單個(gè)節(jié)點(diǎn)上執(zhí)行實(shí)時(shí)重做應(yīng)用
SQL> alter database recover managed standby database using current logfile disconnect from session; Database altered.
alert日志信息如下
Media Recovery Log +TEST/arch/cs_dg/2_129_970601983.dbf 2018-05-15T23:04:44.911711+08:00 Completed: alter database recover managed standby database using current logfile disconnect from session 2018-05-15T23:04:45.116759+08:00 Media Recovery Log +TEST/arch/cs_dg/1_145_970601983.dbf 2018-05-15T23:04:45.676517+08:00 Media Recovery Log +TEST/arch/cs_dg/2_130_970601983.dbf 2018-05-15T23:05:31.642093+08:00 Media Recovery Log +TEST/arch/cs_dg/2_131_970601983.dbf
在主庫創(chuàng)建表空間test
SQL> create tablespace test; Tablespace created. SQL> select tablespace_name from dba_tablespaces; TABLESPACE_NAME ------------------------------ SYSTEM SYSAUX UNDOTBS1 TEMP USERS UNDOTBS2 TEST 7 rows selected. SQL> select con_id,ts#,name from v$tablespace; CON_ID TS# NAME ---------- ---------- ------------------------------ 1 1 SYSAUX 1 0 SYSTEM 1 2 UNDOTBS1 1 4 USERS 1 3 TEMP 2 0 SYSTEM 2 1 SYSAUX 2 2 UNDOTBS1 2 3 TEMP 1 5 UNDOTBS2 1 6 TEST SQL> select ts#,name from v$datafile where ts#=6 and con_id=1; TS# NAME ---------- -------------------------------------------------------------------------------- 6 +DATA/CS/DATAFILE/test.326.976211663
[oracle@jytest1 trace]$ tail -f alert_cs1.log 2018-05-15T23:04:44.736977+08:00 NOTE: dependency between database cs_dg and diskgroup resource ora.TEST.dg is established 2018-05-15T23:04:44.842580+08:00 Media Recovery Log +TEST/arch/cs_dg/2_129_970601983.dbf 2018-05-15T23:04:44.911711+08:00 Completed: alter database recover managed standby database using current logfile disconnect from session 2018-05-15T23:04:45.116759+08:00 Media Recovery Log +TEST/arch/cs_dg/1_145_970601983.dbf 2018-05-15T23:04:45.676517+08:00 Media Recovery Log +TEST/arch/cs_dg/2_130_970601983.dbf 2018-05-15T23:05:31.642093+08:00 Media Recovery Log +TEST/arch/cs_dg/2_131_970601983.dbf 2018-05-15T23:06:11.885256+08:00 Media Recovery Log +TEST/arch/cs_dg/1_146_970601983.dbf 2018-05-15T23:06:26.490187+08:00 Media Recovery Log +TEST/arch/cs_dg/2_132_970601983.dbf 2018-05-15T23:06:36.761337+08:00 Media Recovery Log +TEST/arch/cs_dg/2_133_970601983.dbf 2018-05-15T23:06:38.014959+08:00 Media Recovery Log +TEST/arch/cs_dg/1_147_970601983.dbf 2018-05-15T23:06:38.932380+08:00 Media Recovery Log +TEST/arch/cs_dg/1_148_970601983.dbf 2018-05-15T23:06:40.372178+08:00 Media Recovery Log +TEST/arch/cs_dg/2_134_970601983.dbf 2018-05-15T23:06:40.994801+08:00 Media Recovery Log +TEST/arch/cs_dg/1_149_970601983.dbf 2018-05-15T23:06:41.656032+08:00 Media Recovery Log +TEST/arch/cs_dg/2_135_970601983.dbf 2018-05-15T23:06:47.456319+08:00 Media Recovery Waiting for thread 2 sequence 136 (in transit) 2018-05-15T23:06:47.474190+08:00 Recovery of Online Redo Log: Thread 2 Group 9 Seq 136 Reading mem 0 Mem# 0: +FRA/CS_DG/ONLINELOG/redo09.log 2018-05-15T23:06:52.286510+08:00 Media Recovery Waiting for thread 1 sequence 150 (in transit) 2018-05-15T23:06:52.338688+08:00 Recovery of Online Redo Log: Thread 1 Group 6 Seq 150 Reading mem 0 Mem# 0: +FRA/CS_DG/ONLINELOG/redo06.log 2018-05-15T23:08:48.743309+08:00 Successfully added datafile 10 to media recovery Datafile #10: '+FRA/CS_DG/DATAFILE/test.327.976230527'
從上面信息可以看到在備庫創(chuàng)建的表空間test的數(shù)據(jù)文件名，下面從備庫進(jìn)行查詢來驗(yàn)證
SQL> select con_id,ts#,name from v$tablespace; CON_ID TS# NAME ---------- ---------- ------------------------------ 1 1 SYSAUX 1 0 SYSTEM 1 2 UNDOTBS1 1 4 USERS 1 3 TEMP 2 0 SYSTEM 2 1 SYSAUX 2 2 UNDOTBS1 2 3 TEMP 1 5 UNDOTBS2 1 6 TEST 11 rows selected. SQL> select ts#,name from v$datafile where ts#=6 and con_id=1; TS# NAME ---------- ------------------------------------------------------------------------------------------------------------------------ 6 +FRA/CS_DG/DATAFILE/test.327.976230527
驗(yàn)證主庫
SQL> create user c##test identified by "test" default tablespace users temporary tablespace temp; User created. SQL> grant dba,connect,resource to c##test; Grant succeeded. SQL> create table c##test.t1 as select * from dba_objects where 1=2; Table created.
備庫
SQL> desc c##test.t1 Name Null? Type ----------------------------------------------------------------- -------- -------------------------------------------- OWNER VARCHAR2(128) OBJECT_NAME VARCHAR2(128) SUBOBJECT_NAME VARCHAR2(128) OBJECT_ID NUMBER DATA_OBJECT_ID NUMBER OBJECT_TYPE VARCHAR2(23) CREATED DATE LAST_DDL_TIME DATE TIMESTAMP VARCHAR2(19) STATUS VARCHAR2(7) TEMPORARY VARCHAR2(1) GENERATED VARCHAR2(1) SECONDARY VARCHAR2(1) NAMESPACE NUMBER EDITION_NAME VARCHAR2(128) SHARING VARCHAR2(18) EDITIONABLE VARCHAR2(1) ORACLE_MAINTAINED VARCHAR2(1) APPLICATION VARCHAR2(1) DEFAULT_COLLATION VARCHAR2(100) DUPLICATED VARCHAR2(1) SHARDED VARCHAR2(1) CREATED_APPID NUMBER CREATED_VSNID NUMBER MODIFIED_APPID NUMBER MODIFIED_VSNID NUMBER
主庫:節(jié)點(diǎn)1
SQL> insert into c##test.t1 select * from dba_objects; 73390 rows created. SQL> commit; Commit complete. SQL> select count(*) from c##test.t1; COUNT(*) ---------- 73390
備庫:節(jié)點(diǎn)1
SQL> select count(*) from c##test.t1; COUNT(*) ---------- 73390
主庫:節(jié)點(diǎn)2
SQL> insert into c##test.t1 select * from c##test.t1; 73390 rows created. SQL> commit; Commit complete. SQL> select count(*) from c##test.t1; COUNT(*) ---------- 146780
備庫: 節(jié)點(diǎn)2
SQL> select count(*) from c##test.t1; COUNT(*) ---------- 146780
主庫:
SQL> truncate table c##test.t1; Table truncated. SQL> select count(*) from c##test.t1; COUNT(*) ---------- 0
備庫:
SQL> select count(*) from c##test.t1; COUNT(*) ---------- 0
驗(yàn)證物理備庫是否執(zhí)行正確
在創(chuàng)建物理備庫后并且設(shè)置重做傳輸服務(wù)，可能想要驗(yàn)證主庫的數(shù)據(jù)庫修改是否會(huì)成功的傳輸?shù)絺鋷臁?duì)于備庫可以查詢v$managed_standby視圖來驗(yàn)證重做是否被從主庫傳輸?shù)絺鋷觳?yīng)用。
[oracle@jytest1 ~]$ export ORACLE_SID=cs1 [oracle@jytest1 ~]$ sqlplus / as sysdba SQL*Plus: Release 12.2.0.1.0 Production on Wed May 16 22:41:10 2018 Copyright (c) 1982, 2016, Oracle. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production SQL> select client_process,process,thread#,sequence#,status from v$managed_standby where client_process='LGWR' or process='MRP0'; CLIENT_P PROCESS THREAD# SEQUENCE# STATUS -------- --------- ---------- ---------- ------------ LGWR RFS 1 151 IDLE N/A MRP0 2 139 APPLYING_LOG
上面的查詢對(duì)于使用CLIENT_PROCESS為L(zhǎng)GWR的主庫會(huì)顯示一行記錄，它指示重做傳輸工作正常并且主重做線程將會(huì)被發(fā)送到備庫。 如果主庫是RAC數(shù)據(jù)庫，那么對(duì)于使用CLIENT_PROCESS為L(zhǎng)GWR的當(dāng)前活動(dòng)的每個(gè)主庫實(shí)例都會(huì)顯示一行記錄。上面的查詢對(duì)于MRP也行顯示一行。如果MRP的狀態(tài)顯示為APPLYING_LOG并且SEQUENCE#等于主庫當(dāng)前正被發(fā)送的日志
序列號(hào)，那么備庫已經(jīng)解決了所有的日志差異并且當(dāng)前處于實(shí)時(shí)應(yīng)用日志模式。
查詢主庫當(dāng)前正被發(fā)送日志的序列號(hào)為139與上面的MRP進(jìn)程所顯示的sequence#(139)相同
SQL> select group#,thread#,sequence#,status from v$log; GROUP# THREAD# SEQUENCE# STATUS ---------- ---------- ---------- ---------------- 1 1 151 CURRENT 2 1 150 INACTIVE 3 2 139 CURRENT 4 2 138 INACTIVE
注意MRP進(jìn)程可能顯示的sequence#比主庫當(dāng)前被發(fā)送的日志序列號(hào)小，那么這就表示正在應(yīng)用的歸檔重做日志文件與發(fā)送的日志文件之間存在差異并且它并沒有趕上。一旦所有差異被解決，相同的查詢將顯示MRP正在應(yīng)用當(dāng)前sequence#。
免責(zé)聲明：本站發(fā)布的內(nèi)容（圖片、視頻和文字）以原創(chuàng)、轉(zhuǎn)載和分享為主，文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如果涉及侵權(quán)請(qǐng)聯(lián)系站長(zhǎng)郵箱：is@yisu.com進(jìn)行舉報(bào)，并提供相關(guān)證據(jù)，一經(jīng)查實(shí)，將立刻刪除涉嫌侵權(quán)內(nèi)容。
億速云公眾號(hào)
手機(jī)網(wǎng)站二維碼