Oracle 12c 使用RMAN搭建物理備庫(RAC to RAC)

531968912發表於2018-06-20
Oracle

一.例項環境說明
主庫環境說明
作業系統: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#。

 

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/25462274/viewspace-2156390/，如需轉載，請註明出處，否則將追究法律責任。

相關文章