Oracle RAC主备实验

主库的工作

RAC 主库必须置为归档模式，并且是强制日志模式

SQL> select open_mode,log_mode from v$database;

OPEN_MODE	     LOG_MODE
-------------------- ------------
READ WRITE	     NOARCHIVELOG

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area  839282688 bytes
Fixed Size		    2217992 bytes
Variable Size		  566233080 bytes
Database Buffers	  264241152 bytes
Redo Buffers		    6590464 bytes
Database mounted.

SQL> alter database archivelog;

Database altered.

SQL> select open_mode,log_mode from v$database;

OPEN_MODE	     LOG_MODE
-------------------- ------------
MOUNTED 	     ARCHIVELOG

SQL> shutdown immediate
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  839282688 bytes
Fixed Size		    2217992 bytes
Variable Size		  566233080 bytes
Database Buffers	  264241152 bytes
Redo Buffers		    6590464 bytes
Database mounted.
Database opened.
SQL> select open_mode,log_mode from v$database;

OPEN_MODE	     LOG_MODE
-------------------- ------------
READ WRITE	     ARCHIVELOG

SQL> 
SQL> select name,log_mode,force_logging from gv$database;

NAME	  LOG_MODE     FOR
--------- ------------ ---
DEVDB	  ARCHIVELOG   NO
DEVDB	  ARCHIVELOG   NO

SQL> alter database force logging;

Database altered.

SQL> select name,log_mode,force_logging from gv$database;

NAME	  LOG_MODE     FOR
--------- ------------ ---
DEVDB	  ARCHIVELOG   YES
DEVDB	  ARCHIVELOG   YES

执行一个全备：
提前建好/rman_backup目录，并设置oracle:oinstall组权限

[oracle@node1 trace]$ rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Mon Dec 23 16:53:28 2019

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: DEVDB (DBID=931990411)

run {
allocate channel c1 type disk;
allocate channel c2 type disk;
allocate channel c3 type disk;
allocate channel c4 type disk;
backup database format '/rman_backup/Full_%U.bak';
release channel c1;
release channel c2;
release channel c3;
release channel c4;
}
2> 3> 4> 5> 6> 7> 8> 9> 10> 11> 
using target database control file instead of recovery catalog
allocated channel: c1
channel c1: SID=152 instance=devdb1 device type=DISK

allocated channel: c2
channel c2: SID=29 instance=devdb1 device type=DISK

allocated channel: c3
channel c3: SID=154 instance=devdb1 device type=DISK

allocated channel: c4
channel c4: SID=28 instance=devdb1 device type=DISK

Starting backup at 2019/12/23 16:54:06
channel c1: starting full datafile backup set
channel c1: specifying datafile(s) in backup set
input datafile file number=00001 name=+DATA/devdb/datafile/system.256.1027260085
channel c1: starting piece 1 at 2019/12/23 16:54:10
channel c2: starting full datafile backup set
channel c2: specifying datafile(s) in backup set
input datafile file number=00002 name=+DATA/devdb/datafile/sysaux.257.1027260091
input datafile file number=00004 name=+DATA/devdb/datafile/users.259.1027260095
channel c2: starting piece 1 at 2019/12/23 16:54:11
channel c3: starting full datafile backup set
channel c3: specifying datafile(s) in backup set
input datafile file number=00003 name=+DATA/devdb/datafile/undotbs1.258.1027260095
input datafile file number=00005 name=+DATA/devdb/datafile/undotbs2.264.1027260763
channel c3: starting piece 1 at 2019/12/23 16:54:13
channel c4: starting full datafile backup set
channel c4: specifying datafile(s) in backup set
including current control file in backup set
channel c4: starting piece 1 at 2019/12/23 16:54:30
channel c4: finished piece 1 at 2019/12/23 16:54:40
piece handle=/rman_backup/Full_08uk5k1l_1_1.bak tag=TAG20191223T165408 comment=NONE
channel c4: backup set complete, elapsed time: 00:00:11
channel c4: starting full datafile backup set
channel c4: specifying datafile(s) in backup set
including current SPFILE in backup set
channel c4: starting piece 1 at 2019/12/23 16:54:51
channel c3: finished piece 1 at 2019/12/23 16:54:51
piece handle=/rman_backup/Full_07uk5k1k_1_1.bak tag=TAG20191223T165408 comment=NONE
channel c3: backup set complete, elapsed time: 00:00:38
channel c4: finished piece 1 at 2019/12/23 16:54:57
piece handle=/rman_backup/Full_09uk5k2q_1_1.bak tag=TAG20191223T165408 comment=NONE
channel c4: backup set complete, elapsed time: 00:00:06
channel c2: finished piece 1 at 2019/12/23 16:55:50
piece handle=/rman_backup/Full_06uk5k1j_1_1.bak tag=TAG20191223T165408 comment=NONE
channel c2: backup set complete, elapsed time: 00:01:40
channel c1: finished piece 1 at 2019/12/23 16:56:01
piece handle=/rman_backup/Full_05uk5k1i_1_1.bak tag=TAG20191223T165408 comment=NONE
channel c1: backup set complete, elapsed time: 00:01:51
Finished backup at 2019/12/23 16:56:01

released channel: c1

released channel: c2

released channel: c3

released channel: c4

备份归档日志文件

RMAN> backup archivelog all format '/rman_backup/ARC_%U.bak';

Starting backup at 2019/12/23 16:58:15
current log archived
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=152 instance=devdb1 device type=DISK
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=2 sequence=2 RECID=1 STAMP=1027787411
input archived log thread=1 sequence=6 RECID=2 STAMP=1027789100
channel ORA_DISK_1: starting piece 1 at 2019/12/23 16:58:23
channel ORA_DISK_1: finished piece 1 at 2019/12/23 16:58:24
piece handle=/rman_backup/ARC_0auk5k9e_1_1.bak tag=TAG20191223T165821 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 2019/12/23 16:58:24

RAC主库执行创建物理备库控制文件

RMAN> backup device type disk format '/rman_backup/standby_%U.ctl' current controlfile for standby;

Starting backup at 2019/12/23 16:59:54
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including standby control file in backup set
channel ORA_DISK_1: starting piece 1 at 2019/12/23 16:59:59
channel ORA_DISK_1: finished piece 1 at 2019/12/23 17:00:00
piece handle=/rman_backup/standby_0buk5kcb_1_1.ctl tag=TAG20191223T165954 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 2019/12/23 17:00:00

主库创建物理备库初始化参数文件

SQL> show parameter spfile

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
spfile				     string	 +DATA/devdb/spfiledevdb.ora
SQL> create pfile='/rman_backup/initphydb.ora' from spfile;

File created.

RAC 主库修改口令文件，使双节点 SYS 用户口令一致，这个由于我的双节点密码是一样的，故不用修改。

备库的工作

我的环境是主库为Oracle RAC双节点，备库为单实例Oracle数据库。
把主库/rman_backup相关的目录通过FTP等上传到备库/rman_backup目录，在这里，由于是虚拟机，同一网段，直接使用scp拷贝即可。

[root@node1 ~]# cd /rman_backup/
[root@node1 rman_backup]# ls
ARC_0auk5k9e_1_1.bak   Full_07uk5k1k_1_1.bak  initphydb.ora
Full_05uk5k1i_1_1.bak  Full_08uk5k1l_1_1.bak  standby_0buk5kcb_1_1.ctl
Full_06uk5k1j_1_1.bak  Full_09uk5k2q_1_1.bak
[root@node1 rman_backup]# scp * root@192.168.169.123:/rman_backup

物理备库初始化参数文件修改

[root@phydb dbs]# cp /rman_backup/initphydb.ora /u01/app/oracle/product/11.2.0/db_1/dbs/

[root@phydb dbs]# cat initphydb.ora 
phydb.__db_cache_size=272629760
phydb.__java_pool_size=4194304
phydb.__large_pool_size=4194304
phydb.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
phydb.__pga_aggregate_target=339738624
phydb.__sga_target=503316480
phydb.__shared_io_pool_size=0
phydb.__shared_pool_size=218103808
phydb.__streams_pool_size=0
devdb1.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/phydb/adump'
*.audit_trail='db'
*.cluster_database=false
*.compatible='11.2.0.0.0'
*.control_files='+DATA/phydb/controlfile/cont.ctl'
*.core_dump_dest='/u01/app/oracle/diag/rdbms/phydb/cdump'
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_domain=''
*.db_file_name_convert='+DATA/devdb/','+DATA/phydb/'
*.db_name='devdb'
*.db_recovery_file_dest='+FLASH'
*.db_recovery_file_dest_size=4070572032
*.db_unique_name='phydb'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=devdbXDB)'
*.memory_target=842006528

*.fal_client='phydb'
*.fal_server='devdb1','devdb2'
*.log_archive_config='dg_config=(devdb,phydb)'
*.log_archive_dest_1='location=USE_DB_RECOVERY_FILE_DEST
valid_for=(all_logfiles,all_roles) db_unique_name=phydb'
*.log_archive_dest_2='service=devdb1
valid_for=(online_logfiles,primary_role) db_unique_name=devdb'
*.log_archive_format='ARC_%t_%S_%r.arc'
*.log_file_name_convert='+DATA/devdb/','+DATA/phydb/'


*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='exclusive'
*.service_names='phydb'
*.standby_file_management='auto'
*.thread=1
*.undo_management='auto'
*.undo_tablespace='UNDOTBS1'

添加主库关于备库的连接信息，配置tnsnames.ora

[root@node1 rman_backup]# cat /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

DEVDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = scan-cluster.localdomain)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = devdb)
    )
  )

phydb =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.169.123)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = phydb)
    )
  )

建立相关目录，并设置权限oracle:oinstall

SQL> startup nomount;
ORACLE instance started.

Total System Global Area  839282688 bytes
Fixed Size		    2217992 bytes
Variable Size		  494929912 bytes
Database Buffers	  335544320 bytes
Redo Buffers		    6590464 bytes

查看oracle后台进程正常运行。

[root@phydb phydb]# ps -ef|grep ora_
oracle    4790     1  6 17:58 ?        00:00:00 ora_pmon_phydb
oracle    4792     1  2 17:58 ?        00:00:00 ora_vktm_phydb
oracle    4796     1  3 17:58 ?        00:00:00 ora_gen0_phydb
oracle    4798     1  3 17:58 ?        00:00:00 ora_diag_phydb
oracle    4800     1  3 17:58 ?        00:00:00 ora_dbrm_phydb
oracle    4802     1  2 17:58 ?        00:00:00 ora_psp0_phydb
oracle    4804     1  4 17:58 ?        00:00:00 ora_dia0_phydb
oracle    4806     1 16 17:58 ?        00:00:00 ora_mman_phydb
oracle    4808     1  5 17:58 ?        00:00:00 ora_dbw0_phydb
oracle    4810     1  1 17:58 ?        00:00:00 ora_lgwr_phydb
oracle    4812     1  2 17:58 ?        00:00:00 ora_ckpt_phydb
oracle    4814     1  3 17:58 ?        00:00:00 ora_smon_phydb
oracle    4816     1  2 17:58 ?        00:00:00 ora_reco_phydb
oracle    4818     1  2 17:58 ?        00:00:00 ora_rbal_phydb
oracle    4822     1  3 17:58 ?        00:00:00 ora_mmon_phydb
oracle    4824     1  3 17:58 ?        00:00:00 ora_mmnl_phydb
oracle    4826     1  3 17:58 ?        00:00:00 ora_d000_phydb
oracle    4828     1  3 17:58 ?        00:00:00 ora_s000_phydb
root      4838  4360  0 17:58 pts/3    00:00:00 grep ora_

RMAN恢复备库控制文件
添加ASM，否则restore的时候会报ORA-15001: diskgroup "DATA" does not exist or is not mounted
并在+DATA目录下建立phydb目录

[grid@phydb ~]$ asmcmd -p
ASMCMD [+] > ls
DATA/
FLASH/
GRIDDG/
ASMCMD [+] > cd data      
ASMCMD [+data] > ls
ASMCMD [+data] > pwd
+data
ASMCMD [+data] > mkdir phydb

scp拷贝的所有文件需要设置oracle:oinstall用户组权限

RMAN> restore standby controlfile from '/rman_backup/standby_0buk5kcb_1_1.ctl';

Starting restore at 2019/12/25 21:08:57
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=137 device type=DISK

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:05
output file name=+DATA/phydb/controlfile/cont.ctl
Finished restore at 2019/12/25 21:09:03

mount物理备库

SQL> alter database mount;

Database altered.

rman restore 物理备库，即根据恢复的控制文件去恢复整个数据库，因为控制文件含有数据文件等信息。

[oracle@phydb ~]$ rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Fri Dec 27 17:14:16 2019

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: DEVDB (DBID=931990411, not open)

run {
allocate channel c1 type disk;sk;
allocate channel c2 type disk;sk;
allocate channel c3 type disk;sk;
allocate channel c4 type disk;sk;
restore database;se;
release channel c1;c1;
release channel c2;c2;
release channel c3;
release channel c4;
 }

using target database control file instead of recovery catalog
allocated channel: c1
channel c1: SID=136 device type=DISK

allocated channel: c2
channel c2: SID=141 device type=DISK

allocated channel: c3
channel c3: SID=13 device type=DISK

allocated channel: c4
channel c4: SID=142 device type=DISK

Starting restore at 2019/12/27 17:14:30
Starting implicit crosscheck backup at 2019/12/27 17:14:30
Crosschecked 6 objects
Crosschecked 1 objects
Finished implicit crosscheck backup at 2019/12/27 17:14:34

Starting implicit crosscheck copy at 2019/12/27 17:14:34
Finished implicit crosscheck copy at 2019/12/27 17:14:34

searching for all files in the recovery area
cataloging files...
no files cataloged


channel c1: starting datafile backup set restore
channel c1: specifying datafile(s) to restore from backup set
channel c1: restoring datafile 00003 to +DATA/phydb/datafile/undotbs1.258.1027260095
channel c1: restoring datafile 00005 to +DATA/phydb/datafile/undotbs2.264.1027260763
channel c1: reading from backup piece /rman_backup/Full_07uk5k1k_1_1.bak
channel c2: starting datafile backup set restore
channel c2: specifying datafile(s) to restore from backup set
channel c2: restoring datafile 00002 to +DATA/phydb/datafile/sysaux.257.1027260091
channel c2: restoring datafile 00004 to +DATA/phydb/datafile/users.259.1027260095
channel c2: reading from backup piece /rman_backup/Full_06uk5k1j_1_1.bak
channel c3: starting datafile backup set restore
channel c3: specifying datafile(s) to restore from backup set
channel c3: restoring datafile 00001 to +DATA/phydb/datafile/system.256.1027260085
channel c3: reading from backup piece /rman_backup/Full_05uk5k1i_1_1.bak
channel c1: piece handle=/rman_backup/Full_07uk5k1k_1_1.bak tag=TAG20191223T165408
channel c1: restored backup piece 1
channel c1: restore complete, elapsed time: 00:00:24
channel c2: piece handle=/rman_backup/Full_06uk5k1j_1_1.bak tag=TAG20191223T165408
channel c2: restored backup piece 1
channel c2: restore complete, elapsed time: 00:20:55
channel c3: piece handle=/rman_backup/Full_05uk5k1i_1_1.bak tag=TAG20191223T165408
channel c3: restored backup piece 1
channel c3: restore complete, elapsed time: 00:22:12
Finished restore at 2019/12/27 17:36:55

released channel: c1

released channel: c2

released channel: c3

released channel: c4

备库上创建standby logfile，注意大小一定要跟主库的大小一致。

先查看下主库的日志组信息以及大小

SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE	  MEMBERS ARC
---------- ---------- ---------- ---------- ---------- ---------- ---
STATUS		 FIRST_CHANGE# FIRST_TIME	   NEXT_CHANGE#
---------------- ------------- ------------------- ------------
NEXT_TIME
-------------------
	 1	    1	      11   52428800	   512		2 YES
INACTIVE	       1165876 2019/12/24 22:24:22	1203426
2019/12/25 09:00:43

	 2	    1	      12   52428800	   512		2 NO
CURRENT 	       1203426 2019/12/25 09:00:43   2.8147E+14


    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE	  MEMBERS ARC
---------- ---------- ---------- ---------- ---------- ---------- ---
STATUS		 FIRST_CHANGE# FIRST_TIME	   NEXT_CHANGE#
---------------- ------------- ------------------- ------------
NEXT_TIME
-------------------

	 3	    2	       1   52428800	   512		2 YES
INACTIVE		995573 2019/12/17 14:15:07	1042707
2019/12/23 16:14:16

	 4	    2	       2   52428800	   512		2 YES
ACTIVE		       1042707 2019/12/23 16:14:16	1044827

    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE	  MEMBERS ARC
---------- ---------- ---------- ---------- ---------- ---------- ---
STATUS		 FIRST_CHANGE# FIRST_TIME	   NEXT_CHANGE#
---------------- ------------- ------------------- ------------
NEXT_TIME
-------------------
2019/12/23 16:30:03
SQL> select 52428800/1024/1024 as M from dual;

	 M
----------
	50

可以看到已经有1-4的日志组了，并且大小为50M。
在备库上添加两个线程，每个线程3个日志组

SQL> alter database add standby logfile thread 1 group 5 size 50m,group 6 size 50m,group 7 size 50m;
Database altered.

SQL> alter database add standby logfile thread 2 group 8 size 50m,group 9 size 50m,group 10 size 50m;
Database altered.

可以看到6个日志组已创建成功

SQL> select * from v$standby_log;

    GROUP#
----------
DBID
--------------------------------------------------------------------------------
   THREAD#  SEQUENCE#	   BYTES  BLOCKSIZE	  USED ARCHIVED
---------- ---------- ---------- ---------- ---------- ---------
STATUS			       FIRST_CHANGE# FIRST_TIME 	 NEXT_CHANGE#
------------------------------ ------------- ------------------- ------------
NEXT_TIME	    LAST_CHANGE# LAST_TIME
------------------- ------------ -------------------
	 5
UNASSIGNED
	 1	    0	52428800	512	   512 YES

    GROUP#
----------
DBID
--------------------------------------------------------------------------------
   THREAD#  SEQUENCE#	   BYTES  BLOCKSIZE	  USED ARCHIVED
---------- ---------- ---------- ---------- ---------- ---------
STATUS			       FIRST_CHANGE# FIRST_TIME 	 NEXT_CHANGE#
------------------------------ ------------- ------------------- ------------
NEXT_TIME	    LAST_CHANGE# LAST_TIME
------------------- ------------ -------------------
UNASSIGNED				   0				    0
			       0


    GROUP#
----------
DBID
--------------------------------------------------------------------------------
   THREAD#  SEQUENCE#	   BYTES  BLOCKSIZE	  USED ARCHIVED
---------- ---------- ---------- ---------- ---------- ---------
STATUS			       FIRST_CHANGE# FIRST_TIME 	 NEXT_CHANGE#
------------------------------ ------------- ------------------- ------------
NEXT_TIME	    LAST_CHANGE# LAST_TIME
------------------- ------------ -------------------
	 6
UNASSIGNED
	 1	    0	52428800	512	   512 YES

    GROUP#
----------
DBID
--------------------------------------------------------------------------------
   THREAD#  SEQUENCE#	   BYTES  BLOCKSIZE	  USED ARCHIVED
---------- ---------- ---------- ---------- ---------- ---------
STATUS			       FIRST_CHANGE# FIRST_TIME 	 NEXT_CHANGE#
------------------------------ ------------- ------------------- ------------
NEXT_TIME	    LAST_CHANGE# LAST_TIME
------------------- ------------ -------------------
UNASSIGNED				   0				    0
			       0


    GROUP#
----------
DBID
--------------------------------------------------------------------------------
   THREAD#  SEQUENCE#	   BYTES  BLOCKSIZE	  USED ARCHIVED
---------- ---------- ---------- ---------- ---------- ---------
STATUS			       FIRST_CHANGE# FIRST_TIME 	 NEXT_CHANGE#
------------------------------ ------------- ------------------- ------------
NEXT_TIME	    LAST_CHANGE# LAST_TIME
------------------- ------------ -------------------
	 7
UNASSIGNED
	 1	    0	52428800	512	   512 YES

    GROUP#
----------
DBID
--------------------------------------------------------------------------------
   THREAD#  SEQUENCE#	   BYTES  BLOCKSIZE	  USED ARCHIVED
---------- ---------- ---------- ---------- ---------- ---------
STATUS			       FIRST_CHANGE# FIRST_TIME 	 NEXT_CHANGE#
------------------------------ ------------- ------------------- ------------
NEXT_TIME	    LAST_CHANGE# LAST_TIME
------------------- ------------ -------------------
UNASSIGNED				   0				    0
			       0


    GROUP#
----------
DBID
--------------------------------------------------------------------------------
   THREAD#  SEQUENCE#	   BYTES  BLOCKSIZE	  USED ARCHIVED
---------- ---------- ---------- ---------- ---------- ---------
STATUS			       FIRST_CHANGE# FIRST_TIME 	 NEXT_CHANGE#
------------------------------ ------------- ------------------- ------------
NEXT_TIME	    LAST_CHANGE# LAST_TIME
------------------- ------------ -------------------
	 8
UNASSIGNED
	 2	    0	52428800	512	   512 YES

    GROUP#
----------
DBID
--------------------------------------------------------------------------------
   THREAD#  SEQUENCE#	   BYTES  BLOCKSIZE	  USED ARCHIVED
---------- ---------- ---------- ---------- ---------- ---------
STATUS			       FIRST_CHANGE# FIRST_TIME 	 NEXT_CHANGE#
------------------------------ ------------- ------------------- ------------
NEXT_TIME	    LAST_CHANGE# LAST_TIME
------------------- ------------ -------------------
UNASSIGNED				   0				    0
			       0


    GROUP#
----------
DBID
--------------------------------------------------------------------------------
   THREAD#  SEQUENCE#	   BYTES  BLOCKSIZE	  USED ARCHIVED
---------- ---------- ---------- ---------- ---------- ---------
STATUS			       FIRST_CHANGE# FIRST_TIME 	 NEXT_CHANGE#
------------------------------ ------------- ------------------- ------------
NEXT_TIME	    LAST_CHANGE# LAST_TIME
------------------- ------------ -------------------
	 9
UNASSIGNED
	 2	    0	52428800	512	   512 YES

    GROUP#
----------
DBID
--------------------------------------------------------------------------------
   THREAD#  SEQUENCE#	   BYTES  BLOCKSIZE	  USED ARCHIVED
---------- ---------- ---------- ---------- ---------- ---------
STATUS			       FIRST_CHANGE# FIRST_TIME 	 NEXT_CHANGE#
------------------------------ ------------- ------------------- ------------
NEXT_TIME	    LAST_CHANGE# LAST_TIME
------------------- ------------ -------------------
UNASSIGNED				   0				    0
			       0


    GROUP#
----------
DBID
--------------------------------------------------------------------------------
   THREAD#  SEQUENCE#	   BYTES  BLOCKSIZE	  USED ARCHIVED
---------- ---------- ---------- ---------- ---------- ---------
STATUS			       FIRST_CHANGE# FIRST_TIME 	 NEXT_CHANGE#
------------------------------ ------------- ------------------- ------------
NEXT_TIME	    LAST_CHANGE# LAST_TIME
------------------- ------------ -------------------
	10
UNASSIGNED
	 2	    0	52428800	512	   512 YES

    GROUP#
----------
DBID
--------------------------------------------------------------------------------
   THREAD#  SEQUENCE#	   BYTES  BLOCKSIZE	  USED ARCHIVED
---------- ---------- ---------- ---------- ---------- ---------
STATUS			       FIRST_CHANGE# FIRST_TIME 	 NEXT_CHANGE#
------------------------------ ------------- ------------------- ------------
NEXT_TIME	    LAST_CHANGE# LAST_TIME
------------------- ------------ -------------------
UNASSIGNED				   0				    0
			       0


6 rows selected.

主库参数调整

SQL> show parameter spfile;

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
spfile				     string	 +DATA/devdb/spfiledevdb.ora
SQL> show parameter log_arch

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
log_archive_config		     string
log_archive_dest		     string
log_archive_dest_1		     string
log_archive_dest_10		     string
log_archive_dest_11		     string
log_archive_dest_12		     string
log_archive_dest_13		     string
log_archive_dest_14		     string
log_archive_dest_15		     string
log_archive_dest_16		     string
log_archive_dest_17		     string

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_18		     string
log_archive_dest_19		     string
log_archive_dest_2		     string
log_archive_dest_20		     string
log_archive_dest_21		     string
log_archive_dest_22		     string
log_archive_dest_23		     string
log_archive_dest_24		     string
log_archive_dest_25		     string
log_archive_dest_26		     string
log_archive_dest_27		     string

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_28		     string
log_archive_dest_29		     string
log_archive_dest_3		     string
log_archive_dest_30		     string
log_archive_dest_31		     string
log_archive_dest_4		     string
log_archive_dest_5		     string
log_archive_dest_6		     string
log_archive_dest_7		     string
log_archive_dest_8		     string
log_archive_dest_9		     string

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_state_1	     string	 enable
log_archive_dest_state_10	     string	 enable
log_archive_dest_state_11	     string	 enable
log_archive_dest_state_12	     string	 enable
log_archive_dest_state_13	     string	 enable
log_archive_dest_state_14	     string	 enable
log_archive_dest_state_15	     string	 enable
log_archive_dest_state_16	     string	 enable
log_archive_dest_state_17	     string	 enable
log_archive_dest_state_18	     string	 enable
log_archive_dest_state_19	     string	 enable

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_state_2	     string	 enable
log_archive_dest_state_20	     string	 enable
log_archive_dest_state_21	     string	 enable
log_archive_dest_state_22	     string	 enable
log_archive_dest_state_23	     string	 enable
log_archive_dest_state_24	     string	 enable
log_archive_dest_state_25	     string	 enable
log_archive_dest_state_26	     string	 enable
log_archive_dest_state_27	     string	 enable
log_archive_dest_state_28	     string	 enable
log_archive_dest_state_29	     string	 enable

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_state_3	     string	 enable
log_archive_dest_state_30	     string	 enable
log_archive_dest_state_31	     string	 enable
log_archive_dest_state_4	     string	 enable
log_archive_dest_state_5	     string	 enable
log_archive_dest_state_6	     string	 enable
log_archive_dest_state_7	     string	 enable
log_archive_dest_state_8	     string	 enable
log_archive_dest_state_9	     string	 enable
log_archive_duplex_dest 	     string
log_archive_format		     string	 %t_%s_%r.dbf

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
log_archive_local_first 	     boolean	 TRUE
log_archive_max_processes	     integer	 4
log_archive_min_succeed_dest	     integer	 1
log_archive_start		     boolean	 FALSE
log_archive_trace		     integer	 0

主要修改log_archive_dest_2和sid的值

SQL> alter system set log_archive_dest_2='service=phydb lgwr sync valid_for=(online_logfiles,primary_role) db_unique_name=phydb' sid='*';

System altered.

修改log_archive_config和sid参数

SQL> alter system set log_archive_config='dg_config=(devdb,phydb )' sid='*';

System altered.

备库开始应用日志：

SQL> alter database recover managed standby database using current logfile disconnect from session;

查询下备库是否接受到主库的归档日志

SQL> select sequence#,name, applied from v$archived_log;

 SEQUENCE#
----------
NAME
--------------------------------------------------------------------------------
APPLIED
---------
	 6
+FLASH/phydb/archivelog/2019_12_30/thread_1_seq_6.266.1028383187
YES

	 8
+FLASH/phydb/archivelog/2019_12_30/thread_1_seq_8.267.1028383225
NO

 SEQUENCE#
----------
NAME
--------------------------------------------------------------------------------
APPLIED
---------

	 7
+FLASH/phydb/archivelog/2019_12_30/thread_1_seq_7.268.1028383233
YES

	 9
+FLASH/phydb/archivelog/2019_12_30/thread_1_seq_9.269.1028383323

 SEQUENCE#
----------
NAME
--------------------------------------------------------------------------------
APPLIED
---------
NO

	10
+FLASH/phydb/archivelog/2019_12_30/thread_1_seq_10.270.1028383379
NO

	 3

 SEQUENCE#
----------
NAME
--------------------------------------------------------------------------------
APPLIED
---------
+FLASH/phydb/archivelog/2019_12_30/thread_2_seq_3.272.1028383843
NO

	 4
+FLASH/phydb/archivelog/2019_12_30/thread_2_seq_4.271.1028383843
NO


7 rows selected.

正常的话应该有记录，如果遇到无，从trace日志中报如下错：

Check that the primary and standby are using a password file
and remote_login_passwordfile is set to SHARED or EXCLUSIVE,
and that the SYS password is same in the password files.
returning error ORA-16191
******************************************************************
LGWR: Setting 'active' archival for destination LOG_ARCHIVE_DEST_2
******************************************************************
Mon Dec 30 13:38:17 2019
Error 1031 received logging on to the standby
Errors in file /u01/app/oracle/diag/rdbms/devdb/devdb2/trace/devdb2_lgwr_8677.trc:
ORA-01031: insufficient privileges
Error 1031 for archive log file 3 to 'phydb'
LGWR: Failed to archive log 3 thread 2 sequence 5 (1031)
Thread 2 advanced to log sequence 5 (thread open)
Thread 2 opened at log sequence 5
  Current log# 3 seq# 5 mem# 0: +DATA/devdb/onlinelog/group_3.265.1027260897
  Current log# 3 seq# 5 mem# 1: +FLASH/devdb/onlinelog/group_3.259.1027260897
Successful open of redo thread 2
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Mon Dec 30 13:38:19 2019
Error 1031 received logging on to the standby
Errors in file /u01/app/oracle/diag/rdbms/devdb/devdb2/trace/devdb2_arc2_8886.trc:
ORA-01031: insufficient privileges
PING[ARC2]: Heartbeat failed to connect to standby 'phydb'. Error is 1031.

是因为sys口令不一致导致。双节点执行

SQL> alter user sys identified by Oracle168;

User altered.

并拷贝到备库

[oracle@node2 ~]$ scp /u01/app/oracle/product/11.2.0/db_1/dbs/orapwdevdb2 192.168.169.123:/u01/app/oracle/product/11.2.0/db_1/dbs/orapwphydb

备库以 READ ONLY 方式打开：

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-10456: cannot open standby database; media recovery session may be
in progress
SQL> alter database recover managed standby database cancel;
Database altered.
SQL> alter database open;
Database altered.

从日志中也可以看到，变成了只读

Dictionary check complete
Re-creating tempfile +DATA/phydb/tempfile/temp.263.1027260471 as +DATA/phydb/tempfile/temp.272.1028384035
Database Characterset is AL32UTF8
No Resource Manager plan active
replication_dependency_tracking turned off (no async multimaster replication found)
Mon Dec 30 14:14:00 2019
Physical standby database opened for read only access.

SQL> select dbid,name,switchover_status,db_unique_name,database_role,open_mode,current_scn from v$database;

      DBID NAME      SWITCHOVER_STATUS	  DB_UNIQUE_NAME
---------- --------- -------------------- ------------------------------
DATABASE_ROLE	 OPEN_MODE	      CURRENT_SCN
---------------- -------------------- -----------
 931990411 DEVDB     RECOVERY NEEDED	  phydb
PHYSICAL STANDBY READ ONLY		  1204047

附加两条结论

结论一：对于修改SYS用户密码而言，在主备之间数据库实例状态未改变的情况下，备库能正常接受主库的日志，但是当主备任何实例重启或者主库的归档远程进程重置，新建立的连接都会导致备库无法接受主库的归档

结论二：在明确知道sys用户密码修改后，通过alter user sys identified by 重置原密码，但是操作系统层面口令文件OrapwSID.ora文件不一致，也是白瞎，只能通过scp主库操作系统层面sys密码达到想要的效果

实验地址 https://www.cnblogs.com/lvcha001/p/9359710.html

简单的测试下主备库

查看主库的数据文件，一共5个数据文件

SQL> col file_name for a40
SQL> select file_name,tablespace_name,bytes/1024/1024 M from dba_data_files;

FILE_NAME				 TABLESPACE_NAME			 M
---------------------------------------- ------------------------------ ----------
+DATA/devdb/datafile/users.259.102726009 USERS					 5
5

+DATA/devdb/datafile/undotbs1.258.102726 UNDOTBS1				85
0095

+DATA/devdb/datafile/sysaux.257.10272600 SYSAUX 			       560
91

+DATA/devdb/datafile/system.256.10272600 SYSTEM 			       680
85

FILE_NAME				 TABLESPACE_NAME			 M
---------------------------------------- ------------------------------ ----------

+DATA/devdb/datafile/undotbs2.264.102726 UNDOTBS2				25
0763

备库也是5个数据文件

SQL> col file_name for a40
SQL> select file_name,tablespace_name,bytes/1024/1024 M from dba_data_files;

FILE_NAME				 TABLESPACE_NAME			 M
---------------------------------------- ------------------------------ ----------
+DATA/phydb/datafile/users.261.102813568 USERS					 5
9

+DATA/phydb/datafile/undotbs1.257.102813 UNDOTBS1				85
5679

+DATA/phydb/datafile/sysaux.258.10281356 SYSAUX 			       540
85

+DATA/phydb/datafile/system.259.10281356 SYSTEM 			       680
87

FILE_NAME				 TABLESPACE_NAME			 M
---------------------------------------- ------------------------------ ----------

+DATA/phydb/datafile/undotbs2.260.102813 UNDOTBS2				25
5687

在主库上增加一个，查看下变成了6个

SQL> create tablespace hbk_test datafile size 5m;

Tablespace created.

SQL> select file_name,tablespace_name,bytes/1024/1024 M from dba_data_files;

FILE_NAME				 TABLESPACE_NAME			 M
---------------------------------------- ------------------------------ ----------
+DATA/devdb/datafile/users.259.102726009 USERS					 5
5

+DATA/devdb/datafile/undotbs1.258.102726 UNDOTBS1				85
0095

+DATA/devdb/datafile/sysaux.257.10272600 SYSAUX 			       560
91

+DATA/devdb/datafile/system.256.10272600 SYSTEM 			       680
85

FILE_NAME				 TABLESPACE_NAME			 M
---------------------------------------- ------------------------------ ----------

+DATA/devdb/datafile/undotbs2.264.102726 UNDOTBS2				25
0763

+DATA/devdb/datafile/hbk_test.268.102838 HBK_TEST				 5
8009


6 rows selected.

备库查询下，也变成了6个

SQL> select file_name,tablespace_name,bytes/1024/1024 M from dba_data_files;

FILE_NAME				 TABLESPACE_NAME			 M
---------------------------------------- ------------------------------ ----------
+DATA/phydb/datafile/users.261.102813568 USERS					 5
9

+DATA/phydb/datafile/undotbs1.257.102813 UNDOTBS1				85
5679

+DATA/phydb/datafile/sysaux.258.10281356 SYSAUX 			       560
85

+DATA/phydb/datafile/system.259.10281356 SYSTEM 			       680
87

FILE_NAME				 TABLESPACE_NAME			 M
---------------------------------------- ------------------------------ ----------

+DATA/phydb/datafile/undotbs2.260.102813 UNDOTBS2				25
5687

+DATA/phydb/datafile/hbk_test.273.102838 HBK_TEST				 5
9647


6 rows selected.

删除hbk_test表空间，备库日志也收到了相关信号

SQL> drop tablespace hbk_test including contents and datafiles;

Tablespace dropped.

备库日志

Recovery deleting file #6:'+DATA/phydb/datafile/hbk_test.273.1028389647' from controlfile.
Deleted Oracle managed file +DATA/phydb/datafile/hbk_test.273.1028389647
Recovery dropped tablespace 'HBK_TEST'