OracleRAC主备实验

作者：醒不睡睡不醒_269 | 来源：互联网 | 2023-08-27 12:52

主库的工作RAC主库必须置为归档模式，并且是强制日志模式SQLselectopen_mode,log_modefromv$database;OPEN_MODE

主库的工作

RAC 主库必须置为归档模式&＃xff0c;并且是强制日志模式

SQL> select open_mode,log_mode from v$database;OPEN_MODE LOG_MODE -------------------- ------------ READ WRITE NOARCHIVELOGSQL> 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 ARCHIVELOGSQL> shutdown immediate ORA-01109: database not openDatabase 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 ARCHIVELOGSQL> SQL> select name,log_mode,force_logging from gv$database;NAME LOG_MODE FOR --------- ------------ --- DEVDB ARCHIVELOG NO DEVDB ARCHIVELOG NOSQL> 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

执行一个全备&＃xff1a;
提前建好/rman_backup目录&＃xff0c;并设置oracle:oinstall组权限

[oracle&＃64;node1 trace]$ rman target /Recovery Manager: Release 11.2.0.1.0 - Production on Mon Dec 23 16:53:28 2019Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.connected to target database: DEVDB (DBID&＃61;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 &＃39;/rman_backup/Full_%U.bak&＃39;; 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&＃61;152 instance&＃61;devdb1 device type&＃61;DISKallocated channel: c2 channel c2: SID&＃61;29 instance&＃61;devdb1 device type&＃61;DISKallocated channel: c3 channel c3: SID&＃61;154 instance&＃61;devdb1 device type&＃61;DISKallocated channel: c4 channel c4: SID&＃61;28 instance&＃61;devdb1 device type&＃61;DISKStarting 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&＃61;00001 name&＃61;&＃43;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&＃61;00002 name&＃61;&＃43;DATA/devdb/datafile/sysaux.257.1027260091 input datafile file number&＃61;00004 name&＃61;&＃43;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&＃61;00003 name&＃61;&＃43;DATA/devdb/datafile/undotbs1.258.1027260095 input datafile file number&＃61;00005 name&＃61;&＃43;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&＃61;/rman_backup/Full_08uk5k1l_1_1.bak tag&＃61;TAG20191223T165408 comment&＃61;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&＃61;/rman_backup/Full_07uk5k1k_1_1.bak tag&＃61;TAG20191223T165408 comment&＃61;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&＃61;/rman_backup/Full_09uk5k2q_1_1.bak tag&＃61;TAG20191223T165408 comment&＃61;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&＃61;/rman_backup/Full_06uk5k1j_1_1.bak tag&＃61;TAG20191223T165408 comment&＃61;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&＃61;/rman_backup/Full_05uk5k1i_1_1.bak tag&＃61;TAG20191223T165408 comment&＃61;NONE channel c1: backup set complete, elapsed time: 00:01:51 Finished backup at 2019/12/23 16:56:01released channel: c1released channel: c2released channel: c3released channel: c4

备份归档日志文件

RMAN> backup archivelog all format &＃39;/rman_backup/ARC_%U.bak&＃39;;Starting backup at 2019/12/23 16:58:15 current log archived allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID&＃61;152 instance&＃61;devdb1 device type&＃61;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&＃61;2 sequence&＃61;2 RECID&＃61;1 STAMP&＃61;1027787411 input archived log thread&＃61;1 sequence&＃61;6 RECID&＃61;2 STAMP&＃61;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&＃61;/rman_backup/ARC_0auk5k9e_1_1.bak tag&＃61;TAG20191223T165821 comment&＃61;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 &＃39;/rman_backup/standby_%U.ctl&＃39; 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&＃61;/rman_backup/standby_0buk5kcb_1_1.ctl tag&＃61;TAG20191223T165954 comment&＃61;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 spfileNAME TYPE VALUE ------------------------------------ ----------- ------------------------------ spfile string &＃43;DATA/devdb/spfiledevdb.ora SQL> create pfile&＃61;&＃39;/rman_backup/initphydb.ora&＃39; from spfile;File created.

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

备库的工作

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

[root&＃64;node1 ~]# cd /rman_backup/ [root&＃64;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&＃64;node1 rman_backup]# scp * root&＃64;192.168.169.123:/rman_backup

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

[root&＃64;phydb dbs]# cp /rman_backup/initphydb.ora /u01/app/oracle/product/11.2.0/db_1/dbs/

[root&＃64;phydb dbs]# cat initphydb.ora phydb.__db_cache_size&＃61;272629760 phydb.__java_pool_size&＃61;4194304 phydb.__large_pool_size&＃61;4194304 phydb.__oracle_base&＃61;&＃39;/u01/app/oracle&＃39;#ORACLE_BASE set from environment phydb.__pga_aggregate_target&＃61;339738624 phydb.__sga_target&＃61;503316480 phydb.__shared_io_pool_size&＃61;0 phydb.__shared_pool_size&＃61;218103808 phydb.__streams_pool_size&＃61;0 devdb1.__streams_pool_size&＃61;0 *.audit_file_dest&＃61;&＃39;/u01/app/oracle/admin/phydb/adump&＃39; *.audit_trail&＃61;&＃39;db&＃39; *.cluster_database&＃61;false *.compatible&＃61;&＃39;11.2.0.0.0&＃39; *.control_files&＃61;&＃39;&＃43;DATA/phydb/controlfile/cont.ctl&＃39; *.core_dump_dest&＃61;&＃39;/u01/app/oracle/diag/rdbms/phydb/cdump&＃39; *.db_block_size&＃61;8192 *.db_create_file_dest&＃61;&＃39;&＃43;DATA&＃39; *.db_domain&＃61;&＃39;&＃39; *.db_file_name_convert&＃61;&＃39;&＃43;DATA/devdb/&＃39;,&＃39;&＃43;DATA/phydb/&＃39; *.db_name&＃61;&＃39;devdb&＃39; *.db_recovery_file_dest&＃61;&＃39;&＃43;FLASH&＃39; *.db_recovery_file_dest_size&＃61;4070572032 *.db_unique_name&＃61;&＃39;phydb&＃39; *.diagnostic_dest&＃61;&＃39;/u01/app/oracle&＃39; *.dispatchers&＃61;&＃39;(PROTOCOL&＃61;TCP) (SERVICE&＃61;devdbXDB)&＃39; *.memory_target&＃61;842006528*.fal_client&＃61;&＃39;phydb&＃39; *.fal_server&＃61;&＃39;devdb1&＃39;,&＃39;devdb2&＃39; *.log_archive_config&＃61;&＃39;dg_config&＃61;(devdb,phydb)&＃39; *.log_archive_dest_1&＃61;&＃39;location&＃61;USE_DB_RECOVERY_FILE_DEST valid_for&＃61;(all_logfiles,all_roles) db_unique_name&＃61;phydb&＃39; *.log_archive_dest_2&＃61;&＃39;service&＃61;devdb1 valid_for&＃61;(online_logfiles,primary_role) db_unique_name&＃61;devdb&＃39; *.log_archive_format&＃61;&＃39;ARC_%t_%S_%r.arc&＃39; *.log_file_name_convert&＃61;&＃39;&＃43;DATA/devdb/&＃39;,&＃39;&＃43;DATA/phydb/&＃39;*.open_cursors&＃61;300 *.processes&＃61;150 *.remote_login_passwordfile&＃61;&＃39;exclusive&＃39; *.service_names&＃61;&＃39;phydb&＃39; *.standby_file_management&＃61;&＃39;auto&＃39; *.thread&＃61;1 *.undo_management&＃61;&＃39;auto&＃39; *.undo_tablespace&＃61;&＃39;UNDOTBS1&＃39;

添加主库关于备库的连接信息&＃xff0c;配置tnsnames.ora

[root&＃64;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 &＃61;(DESCRIPTION &＃61;(ADDRESS &＃61; (PROTOCOL &＃61; TCP)(HOST &＃61; scan-cluster.localdomain)(PORT &＃61; 1521))(CONNECT_DATA &＃61;(SERVER &＃61; DEDICATED)(SERVICE_NAME &＃61; devdb)))phydb &＃61;(DESCRIPTION &＃61;(ADDRESS &＃61; (PROTOCOL &＃61; TCP)(HOST &＃61; 192.168.169.123)(PORT &＃61; 1521))(CONNECT_DATA &＃61;(SERVER &＃61; DEDICATED)(SERVICE_NAME &＃61; phydb)))

建立相关目录&＃xff0c;并设置权限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&＃64;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&＃xff0c;否则restore的时候会报ORA-15001: diskgroup "DATA" does not exist or is not mounted
并在&＃43;DATA目录下建立phydb目录

[grid&＃64;phydb ~]$ asmcmd -p ASMCMD [&＃43;] > ls DATA/ FLASH/ GRIDDG/ ASMCMD [&＃43;] > cd data ASMCMD [&＃43;data] > ls ASMCMD [&＃43;data] > pwd &＃43;data ASMCMD [&＃43;data] > mkdir phydb

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

RMAN> restore standby controlfile from &＃39;/rman_backup/standby_0buk5kcb_1_1.ctl&＃39;;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&＃61;137 device type&＃61;DISKchannel ORA_DISK_1: restoring control file channel ORA_DISK_1: restore complete, elapsed time: 00:00:05 output file name&＃61;&＃43;DATA/phydb/controlfile/cont.ctl Finished restore at 2019/12/25 21:09:03

mount物理备库

SQL> alter database mount;Database altered.

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

[oracle&＃64;phydb ~]$ rman target /Recovery Manager: Release 11.2.0.1.0 - Production on Fri Dec 27 17:14:16 2019Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.connected to target database: DEVDB (DBID&＃61;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&＃61;136 device type&＃61;DISKallocated channel: c2 channel c2: SID&＃61;141 device type&＃61;DISKallocated channel: c3 channel c3: SID&＃61;13 device type&＃61;DISKallocated channel: c4 channel c4: SID&＃61;142 device type&＃61;DISKStarting 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:34Starting implicit crosscheck copy at 2019/12/27 17:14:34 Finished implicit crosscheck copy at 2019/12/27 17:14:34searching for all files in the recovery area cataloging files... no files catalogedchannel c1: starting datafile backup set restore channel c1: specifying datafile(s) to restore from backup set channel c1: restoring datafile 00003 to &＃43;DATA/phydb/datafile/undotbs1.258.1027260095 channel c1: restoring datafile 00005 to &＃43;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 &＃43;DATA/phydb/datafile/sysaux.257.1027260091 channel c2: restoring datafile 00004 to &＃43;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 &＃43;DATA/phydb/datafile/system.256.1027260085 channel c3: reading from backup piece /rman_backup/Full_05uk5k1i_1_1.bak channel c1: piece handle&＃61;/rman_backup/Full_07uk5k1k_1_1.bak tag&＃61;TAG20191223T165408 channel c1: restored backup piece 1 channel c1: restore complete, elapsed time: 00:00:24 channel c2: piece handle&＃61;/rman_backup/Full_06uk5k1j_1_1.bak tag&＃61;TAG20191223T165408 channel c2: restored backup piece 1 channel c2: restore complete, elapsed time: 00:20:55 channel c3: piece handle&＃61;/rman_backup/Full_05uk5k1i_1_1.bak tag&＃61;TAG20191223T165408 channel c3: restored backup piece 1 channel c3: restore complete, elapsed time: 00:22:12 Finished restore at 2019/12/27 17:36:55released channel: c1released channel: c2released channel: c3released channel: c4

备库上创建standby logfile&＃xff0c;注意大小一定要跟主库的大小一致。

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

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:432 1 12 52428800 512 2 NO CURRENT 1203426 2019/12/25 09:00:43 2.8147E&＃43;14GROUP# 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:164 2 2 52428800 512 2 YES ACTIVE 1042707 2019/12/23 16:14:16 1044827GROUP# 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的日志组了&＃xff0c;并且大小为50M。
在备库上添加两个线程&＃xff0c;每个线程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 UNASSIGNED1 0 52428800 512 512 YESGROUP# ---------- DBID --------------------------------------------------------------------------------THREAD# SEQUENCE# BYTES BLOCKSIZE USED ARCHIVED ---------- ---------- ---------- ---------- ---------- --------- STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# ------------------------------ ------------- ------------------- ------------ NEXT_TIME LAST_CHANGE# LAST_TIME ------------------- ------------ ------------------- UNASSIGNED 0 00GROUP# ---------- DBID --------------------------------------------------------------------------------THREAD# SEQUENCE# BYTES BLOCKSIZE USED ARCHIVED ---------- ---------- ---------- ---------- ---------- --------- STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# ------------------------------ ------------- ------------------- ------------ NEXT_TIME LAST_CHANGE# LAST_TIME ------------------- ------------ -------------------6 UNASSIGNED1 0 52428800 512 512 YESGROUP# ---------- DBID --------------------------------------------------------------------------------THREAD# SEQUENCE# BYTES BLOCKSIZE USED ARCHIVED ---------- ---------- ---------- ---------- ---------- --------- STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# ------------------------------ ------------- ------------------- ------------ NEXT_TIME LAST_CHANGE# LAST_TIME ------------------- ------------ ------------------- UNASSIGNED 0 00GROUP# ---------- DBID --------------------------------------------------------------------------------THREAD# SEQUENCE# BYTES BLOCKSIZE USED ARCHIVED ---------- ---------- ---------- ---------- ---------- --------- STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# ------------------------------ ------------- ------------------- ------------ NEXT_TIME LAST_CHANGE# LAST_TIME ------------------- ------------ -------------------7 UNASSIGNED1 0 52428800 512 512 YESGROUP# ---------- DBID --------------------------------------------------------------------------------THREAD# SEQUENCE# BYTES BLOCKSIZE USED ARCHIVED ---------- ---------- ---------- ---------- ---------- --------- STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# ------------------------------ ------------- ------------------- ------------ NEXT_TIME LAST_CHANGE# LAST_TIME ------------------- ------------ ------------------- UNASSIGNED 0 00GROUP# ---------- DBID --------------------------------------------------------------------------------THREAD# SEQUENCE# BYTES BLOCKSIZE USED ARCHIVED ---------- ---------- ---------- ---------- ---------- --------- STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# ------------------------------ ------------- ------------------- ------------ NEXT_TIME LAST_CHANGE# LAST_TIME ------------------- ------------ -------------------8 UNASSIGNED2 0 52428800 512 512 YESGROUP# ---------- DBID --------------------------------------------------------------------------------THREAD# SEQUENCE# BYTES BLOCKSIZE USED ARCHIVED ---------- ---------- ---------- ---------- ---------- --------- STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# ------------------------------ ------------- ------------------- ------------ NEXT_TIME LAST_CHANGE# LAST_TIME ------------------- ------------ ------------------- UNASSIGNED 0 00GROUP# ---------- DBID --------------------------------------------------------------------------------THREAD# SEQUENCE# BYTES BLOCKSIZE USED ARCHIVED ---------- ---------- ---------- ---------- ---------- --------- STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# ------------------------------ ------------- ------------------- ------------ NEXT_TIME LAST_CHANGE# LAST_TIME ------------------- ------------ -------------------9 UNASSIGNED2 0 52428800 512 512 YESGROUP# ---------- DBID --------------------------------------------------------------------------------THREAD# SEQUENCE# BYTES BLOCKSIZE USED ARCHIVED ---------- ---------- ---------- ---------- ---------- --------- STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# ------------------------------ ------------- ------------------- ------------ NEXT_TIME LAST_CHANGE# LAST_TIME ------------------- ------------ ------------------- UNASSIGNED 0 00GROUP# ---------- DBID --------------------------------------------------------------------------------THREAD# SEQUENCE# BYTES BLOCKSIZE USED ARCHIVED ---------- ---------- ---------- ---------- ---------- --------- STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# ------------------------------ ------------- ------------------- ------------ NEXT_TIME LAST_CHANGE# LAST_TIME ------------------- ------------ -------------------10 UNASSIGNED2 0 52428800 512 512 YESGROUP# ---------- DBID --------------------------------------------------------------------------------THREAD# SEQUENCE# BYTES BLOCKSIZE USED ARCHIVED ---------- ---------- ---------- ---------- ---------- --------- STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# ------------------------------ ------------- ------------------- ------------ NEXT_TIME LAST_CHANGE# LAST_TIME ------------------- ------------ ------------------- UNASSIGNED 0 006 rows selected.

主库参数调整

SQL> show parameter spfile;NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ spfile string &＃43;DATA/devdb/spfiledevdb.ora SQL> show parameter log_archNAME 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 stringNAME 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 stringNAME 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 stringNAME 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 enableNAME 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 enableNAME 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.dbfNAME 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&＃61;&＃39;service&＃61;phydb lgwr sync valid_for&＃61;(online_logfiles,primary_role) db_unique_name&＃61;phydb&＃39; sid&＃61;&＃39;*&＃39;;System altered.

修改log_archive_config和sid参数

SQL> alter system set log_archive_config&＃61;&＃39;dg_config&＃61;(devdb,phydb )&＃39; sid&＃61;&＃39;*&＃39;;System altered.

备库开始应用日志&＃xff1a;

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 &＃43;FLASH/phydb/archivelog/2019_12_30/thread_1_seq_6.266.1028383187 YES8 &＃43;FLASH/phydb/archivelog/2019_12_30/thread_1_seq_8.267.1028383225 NOSEQUENCE# ---------- NAME -------------------------------------------------------------------------------- APPLIED ---------7 &＃43;FLASH/phydb/archivelog/2019_12_30/thread_1_seq_7.268.1028383233 YES9 &＃43;FLASH/phydb/archivelog/2019_12_30/thread_1_seq_9.269.1028383323SEQUENCE# ---------- NAME -------------------------------------------------------------------------------- APPLIED --------- NO10 &＃43;FLASH/phydb/archivelog/2019_12_30/thread_1_seq_10.270.1028383379 NO3SEQUENCE# ---------- NAME -------------------------------------------------------------------------------- APPLIED --------- &＃43;FLASH/phydb/archivelog/2019_12_30/thread_2_seq_3.272.1028383843 NO4 &＃43;FLASH/phydb/archivelog/2019_12_30/thread_2_seq_4.271.1028383843 NO7 rows selected.

正常的话应该有记录&＃xff0c;如果遇到无&＃xff0c;从trace日志中报如下错&＃xff1a;

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 &＃39;active&＃39; 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 &＃39;phydb&＃39; 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 5Current log# 3 seq# 5 mem# 0: &＃43;DATA/devdb/onlinelog/group_3.265.1027260897Current log# 3 seq# 5 mem# 1: &＃43;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 &＃39;phydb&＃39;. Error is 1031.

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

SQL> alter user sys identified by Oracle168;User altered.

并拷贝到备库

[oracle&＃64;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 方式打开&＃xff1a;

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.

从日志中也可以看到&＃xff0c;变成了只读

Dictionary check complete Re-creating tempfile &＃43;DATA/phydb/tempfile/temp.263.1027260471 as &＃43;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

附加两条结论

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

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

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

简单的测试下主备库

查看主库的数据文件&＃xff0c;一共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 ---------------------------------------- ------------------------------ ---------- &＃43;DATA/devdb/datafile/users.259.102726009 USERS 5 5&＃43;DATA/devdb/datafile/undotbs1.258.102726 UNDOTBS1 85 0095&＃43;DATA/devdb/datafile/sysaux.257.10272600 SYSAUX 560 91&＃43;DATA/devdb/datafile/system.256.10272600 SYSTEM 680 85FILE_NAME TABLESPACE_NAME M ---------------------------------------- ------------------------------ ----------&＃43;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 ---------------------------------------- ------------------------------ ---------- &＃43;DATA/phydb/datafile/users.261.102813568 USERS 5 9&＃43;DATA/phydb/datafile/undotbs1.257.102813 UNDOTBS1 85 5679&＃43;DATA/phydb/datafile/sysaux.258.10281356 SYSAUX 540 85&＃43;DATA/phydb/datafile/system.259.10281356 SYSTEM 680 87FILE_NAME TABLESPACE_NAME M ---------------------------------------- ------------------------------ ----------&＃43;DATA/phydb/datafile/undotbs2.260.102813 UNDOTBS2 25 5687

在主库上增加一个&＃xff0c;查看下变成了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 ---------------------------------------- ------------------------------ ---------- &＃43;DATA/devdb/datafile/users.259.102726009 USERS 5 5&＃43;DATA/devdb/datafile/undotbs1.258.102726 UNDOTBS1 85 0095&＃43;DATA/devdb/datafile/sysaux.257.10272600 SYSAUX 560 91&＃43;DATA/devdb/datafile/system.256.10272600 SYSTEM 680 85FILE_NAME TABLESPACE_NAME M ---------------------------------------- ------------------------------ ----------&＃43;DATA/devdb/datafile/undotbs2.264.102726 UNDOTBS2 25 0763&＃43;DATA/devdb/datafile/hbk_test.268.102838 HBK_TEST 5 80096 rows selected.

备库查询下&＃xff0c;也变成了6个

SQL> select file_name,tablespace_name,bytes/1024/1024 M from dba_data_files;FILE_NAME TABLESPACE_NAME M ---------------------------------------- ------------------------------ ---------- &＃43;DATA/phydb/datafile/users.261.102813568 USERS 5 9&＃43;DATA/phydb/datafile/undotbs1.257.102813 UNDOTBS1 85 5679&＃43;DATA/phydb/datafile/sysaux.258.10281356 SYSAUX 560 85&＃43;DATA/phydb/datafile/system.259.10281356 SYSTEM 680 87FILE_NAME TABLESPACE_NAME M ---------------------------------------- ------------------------------ ----------&＃43;DATA/phydb/datafile/undotbs2.260.102813 UNDOTBS2 25 5687&＃43;DATA/phydb/datafile/hbk_test.273.102838 HBK_TEST 5 96476 rows selected.

删除hbk_test表空间&＃xff0c;备库日志也收到了相关信号

SQL> drop tablespace hbk_test including contents and datafiles;Tablespace dropped.

备库日志

Recovery deleting file #6:&＃39;&＃43;DATA/phydb/datafile/hbk_test.273.1028389647&＃39; from controlfile. Deleted Oracle managed file &＃43;DATA/phydb/datafile/hbk_test.273.1028389647 Recovery dropped tablespace &＃39;HBK_TEST&＃39;