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
附加两条结论
实验地址 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;
京公网安备 11010802041100号 | 京ICP备19059560号-4 | PHP1.CN 第一PHP社区 版权所有