Oracle 12c 使用RMAN搭建物理备库(RAC to RAC)
短信预约 -IT技能 免费直播动态提醒
一.实例环境说明
主库环境说明
操作系统:Oracle Linux 7.1
数据库版本:12.2.0.1
IP:10.10.10.190/10.10.10.191
主机名:cs1/cs2
数据库名:cs
Db_unique_name:cs
实例名(SID)cs1/cs2
备库环境说明
操作系统:Oracle Linux 7.1
数据库版本:12.2.0.1
IP:10.10.10.171/172
主机名:jytest1/jytest2
数据库名:cs
Db_unique_nmae:cs_dg
实例名(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>
现在主库没有启用归档，执行下面的命令来对主库启用归档
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
这里主库已经启用了归档
启用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 给备库创建密码文件
这里通过复制主库的密码文件来创建备库密码文件
[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.给备库创建参数文件
使用主库的参数文件进行创建
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为主库和备库配置监听
主库是rac已经配置了监听，备库也是RAC需要对其配置监听
备库:节点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)
)
)
备库:节点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为主库和备库创建Oracle Net服务名
主库:节点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)
)
)
主库:节点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)
)
)
备库:节点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)
)
)
备库:节点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)
)
)
使用备份创建备库
[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.
对物理备库创建备重做日志文件,查询主库的联机重做日志文件，备重做日志文件的大小应该与主库联机重做日志文件的大小相同，备重做日志文件组的数量应该比主库联机重做日志文件组多一组，计算公式为(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.
设置主库相关初始化参数
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
主库以备库角色运行时需要额外设置的参数。这些参数当主库被转换为备库角色运行时生效:
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.
给主库创建备重做日志文件，备重做日志文件组比联机重做日志文件多一组，主库是RAC数据库，在创建备重做日志文件时需要指定thread号:
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在备库的另一节点注册备库和备库实例
[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
添加实例
[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
在备库的单个节点上执行实时重做应用
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
在主库创建表空间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'
从上面信息可以看到在备库创建的表空间test的数据文件名，下面从备库进行查询来验证
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
验证主库
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
主库:节点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
备库:节点1
SQL> select count(*) from c##test.t1;
COUNT(*)
----------
73390
主库:节点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
备库: 节点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
验证物理备库是否执行正确
在创建物理备库后并且设置重做传输服务，可能想要验证主库的数据库修改是否会成功的传输到备库。对于备库可以查询v$managed_standby视图来验证重做是否被从主库传输到备库并应用。
[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
上面的查询对于使用CLIENT_PROCESS为LGWR的主库会显示一行记录，它指示重做传输工作正常并且主重做线程将会被发送到备库。 如果主库是RAC数据库，那么对于使用CLIENT_PROCESS为LGWR的当前活动的每个主库实例都会显示一行记录。上面的查询对于MRP也行显示一行。如果MRP的状态显示为APPLYING_LOG并且SEQUENCE#等于主库当前正被发送的日志
序列号，那么备库已经解决了所有的日志差异并且当前处于实时应用日志模式。
查询主库当前正被发送日志的序列号为139与上面的MRP进程所显示的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进程可能显示的sequence#比主库当前被发送的日志序列号小，那么这就表示正在应用的归档重做日志文件与发送的日志文件之间存在差异并且它并没有赶上。一旦所有差异被解决，相同的查询将显示MRP正在应用当前sequence#。
免责声明：
① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的，并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据，供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。
② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341
![[mysql]mysql8修改root密码](https://static.528045.com/imgs/24.jpg?imageMogr2/format/webp/blur/1x0/quality/35)
