Oracle 11g:Data Guard(物理)
Oracle Data Guard的配置确实不难,但是比较繁琐。
下面呈现关于Oracle 11g Data Guard物理备库的技术细节。
主库:192.168.124.128
备库:192.168.124.129
1 2 3 4 5 6 7 8 9 10 |
[root@dg1 ~]# cat /etc/hosts # File: /etc/hosts, Changed by: Adamhuan # Changed on: Sat Oct 25 22:37:04 PDT 2014 # Localhost 127.0.0.1 localhost # Public: eth0 192.168.124.128 dg1 192.168.124.129 dg2 [root@dg1 ~]# |
我的环境中:
主库的实例名为:yayu
备库的实例名为:standby_yayu
首先,打开主库的归档模式:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 |
[root@dg1 ~]# su - oracle [oracle@dg1 ~]$ [oracle@dg1 ~]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.4.0 Production on Sun Oct 26 00:14:37 2014 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> startup mount; ORACLE instance started. Total System Global Area 417546240 bytes Fixed Size 2253824 bytes Variable Size 318770176 bytes Database Buffers 92274688 bytes Redo Buffers 4247552 bytes Database mounted. SQL> archive log list; Database log mode No Archive Mode Automatic archival Enabled Archive destination /u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch Oldest online log sequence 1 Current log sequence 3 SQL> alter database archivelog; Database altered. SQL> archive log list; Database log mode Archive Mode Automatic archival Enabled Archive destination /u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch Oldest online log sequence 1 Next log sequence to archive 3 Current log sequence 3 SQL> SQL> alter database open; Database altered. SQL> select instance_name,status from v$instance; INSTANCE_NAME STATUS ---------------- ------------ yayu OPEN SQL> |
打开主库的强制日志功能,确保用户无法执行诸如NOLOGGING这样的操作:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
SQL> select name,force_logging from v$database; NAME FOR --------- --- YAYU NO SQL> alter database force logging; Database altered. SQL> select name,force_logging from v$database; NAME FOR --------- --- YAYU YES SQL> |
设置standby文件的自动管理,这样在主库添加或者删除数据文件的时候,在备库这些文件也会被自动的添加与删除:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
SQL> show parameter standby_file_management NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ standby_file_management string MANUAL SQL> alter system set standby_file_management='AUTO'; System altered. SQL> show parameter standby_file_management NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ standby_file_management string AUTO SQL> |
确认主库有standby log file。
备库使用standby log file保存从主库接收的重做日志。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 |
SQL> desc v$logfile; Name Null? Type ----------------------------------------- -------- ---------------------------- GROUP# NUMBER STATUS VARCHAR2(7) TYPE VARCHAR2(7) MEMBER VARCHAR2(513) IS_RECOVERY_DEST_FILE VARCHAR2(3) SQL> col member for a60 SQL> select member from v$logfile; MEMBER -------------------------------------------------------------------------------- /u01/app/oracle/oradata/yayu/redo03.log /u01/app/oracle/oradata/yayu/redo02.log /u01/app/oracle/oradata/yayu/redo01.log SQL> alter database add standby logfile group 4 ('/u01/app/oracle/oradata/yayu/standby_redo04.log') size 50M; Database altered. SQL> select member from v$logfile; MEMBER -------------------------------------------------------------------------------- /u01/app/oracle/oradata/yayu/redo03.log /u01/app/oracle/oradata/yayu/redo02.log /u01/app/oracle/oradata/yayu/redo01.log /u01/app/oracle/oradata/yayu/standby_redo04.log SQL> SQL> desc v$standby_log Name Null? Type ----------------------------------------- -------- ---------------------------- GROUP# NUMBER DBID VARCHAR2(40) THREAD# NUMBER SEQUENCE# NUMBER BYTES NUMBER BLOCKSIZE NUMBER USED NUMBER ARCHIVED VARCHAR2(3) STATUS VARCHAR2(10) FIRST_CHANGE# NUMBER FIRST_TIME DATE NEXT_CHANGE# NUMBER NEXT_TIME DATE LAST_CHANGE# NUMBER LAST_TIME DATE SQL> select count(*) from v$standby_log; COUNT(*) ---------- 1 SQL> select group#,thread#,bytes/1024 "Mb",status from v$standby_log; GROUP# THREAD# Mb STATUS ---------- ---------- ---------- ---------- 4 0 51200 UNASSIGNED SQL> |
密码文件的设置:
初始化参数)
1 2 3 4 5 6 |
SQL> show parameter remote_login_passwordfile NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ remote_login_passwordfile string EXCLUSIVE SQL> |
密码文件)
1 2 3 4 |
[oracle@dg1 ~]$ cd $ORACLE_HOME/dbs [oracle@dg1 dbs]$ ls | grep --color orapw orapwyayu [oracle@dg1 dbs]$ |
初始化参数:db_unique_name。
1 2 3 4 5 6 |
SQL> show parameter db_unique_name NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_unique_name string yayu SQL> |
开启数据库闪回:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 |
[oracle@dg1 dbs]$ cd /u01/ [oracle@dg1 u01]$ ls app [oracle@dg1 u01]$ mkdir flash_recovery [oracle@dg1 u01]$ ll total 8 drwxr-xr-x 6 oracle oinstall 4096 Oct 25 22:57 app drwxr-xr-x 2 oracle oinstall 4096 Oct 26 01:11 flash_recovery [oracle@dg1 u01]$ [oracle@dg1 u01]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.4.0 Production on Sun Oct 26 01:11:37 2014 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> show parameter db_recovery_file_dest NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_recovery_file_dest string db_recovery_file_dest_size big integer 0 SQL> SQL> alter system set db_recovery_file_dest_size=1G scope=both; System altered. SQL> alter system set db_recovery_file_dest='/u01/flash_recovery' scope=both; System altered. SQL> SQL> show parameter db_recovery_file_dest NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_recovery_file_dest string /u01/flash_recovery db_recovery_file_dest_size big integer 1G SQL> SQL> select flashback_on from v$database; FLASHBACK_ON ------------------ NO SQL> alter database flashback on; Database altered. SQL> select flashback_on from v$database; FLASHBACK_ON ------------------ YES SQL> |
配置SQL*Net:
主库:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 |
[oracle@dg1 admin]$ pwd /u01/app/oracle/product/11.2.0/dbhome_1/network/admin [oracle@dg1 admin]$ cat tnsnames.ora # tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora # Generated by Oracle configuration tools. YAYU = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.124.128)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = yayu) ) ) standby_yayu = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.124.129)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = standby_yayu) ) ) [oracle@dg1 admin]$ cat listener.ora # listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora # Generated by Oracle configuration tools. LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = dg1)(PORT = 1521)) (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521)) ) ) ADR_BASE_LISTENER = /u01/app/oracle SID_LIST_LISTENER = (sid_list = (sid_desc = (GLOBAL_DBNAME = YAYU) (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1) (SID_NAME = YAYU) ) ) [oracle@dg1 admin]$ |
备库:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 |
[oracle@dg2 admin]$ pwd /u01/app/oracle/product/11.2.0/dbhome_1/network/admin [oracle@dg2 admin]$ cat tnsnames.ora YAYU = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.124.128)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = yayu) ) ) standby_yayu = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.124.129)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = standby_yayu) ) ) [oracle@dg2 admin]$ cat listener.ora # listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora # Generated by Oracle configuration tools. LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = dg2)(PORT = 1521)) (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521)) ) ) ADR_BASE_LISTENER = /u01/app/oracle SID_LIST_LISTENER = (sid_list = (sid_desc = (GLOBAL_DBNAME = standby_yayu) (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1) (SID_NAME = standby_yayu) ) ) [oracle@dg2 admin]$ |
重做日志的传输的配置:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 |
SQL> show parameter log_archive_dest_1 NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ 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 SQL> SQL> alter system set log_archive_dest_1='location=use_db_recovery_file_dest valid_for=(all_logfiles,all_roles) db_unique_name=YAYU'; System altered. SQL> show parameter log_archive_dest_2 NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ 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 NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ log_archive_dest_27 string log_archive_dest_28 string log_archive_dest_29 string SQL> SQL> alter system set log_archive_dest_2='service=standby_yayu async valid_for=(online_logfile,primary_role) db_unique_name=standby_yayu'; System altered. SQL> show parameter log_archive_dest_1 NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ log_archive_dest_1 string location=use_db_recovery_file_ dest valid_for=(all_logfiles,a ll_roles) db_unique_name=YAYU 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 log_archive_dest_18 string log_archive_dest_19 string SQL> show parameter log_archive_dest_2 NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ log_archive_dest_2 string service=standby_yayu async val id_for=(online_logfile,primary _role) db_unique_name=standby_ yayu 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 log_archive_dest_28 string log_archive_dest_29 string SQL> SQL> show parameter fal_ NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ fal_client string fal_server string SQL> SQL> alter system set fal_server='STANDBY_YAYU'; System altered. SQL> show parameter fal_ NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ fal_client string fal_server string STANDBY_YAYU SQL> SQL> show parameter log_archive_config NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ log_archive_config string SQL> alter system set log_archive_config='dg_config=(YAYU,STANDBY_YAYU)'; System altered. SQL> show parameter log_archive_config; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ log_archive_config string dg_config=(YAYU,STANDBY_YAYU) SQL> SQL> create pfile from spfile; File created. SQL> |
至此,主库的环境基本配置好了。
(备库的环境)
主库)
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
[oracle@dg1 dbs]$ pwd /u01/app/oracle/product/11.2.0/dbhome_1/dbs [oracle@dg1 dbs]$ ll total 24 -rw-rw---- 1 oracle oinstall 1544 Oct 26 00:15 hc_yayu.dat -rw-r--r-- 1 oracle oinstall 2851 May 15 2009 init.ora -rw-r--r-- 1 oracle oinstall 1221 Oct 26 03:26 inityayu.ora -rw-r----- 1 oracle oinstall 24 Oct 26 00:09 lkYAYU -rw-r----- 1 oracle oinstall 1536 Oct 26 00:12 orapwyayu -rw-r----- 1 oracle oinstall 3584 Oct 26 03:26 spfileyayu.ora [oracle@dg1 dbs]$ scp orapwyayu dg2:/u01/app/oracle/product/11.2.0/dbhome_1/dbs The authenticity of host 'dg2 (192.168.124.129)' can't be established. RSA key fingerprint is 95:6f:ee:ae:42:52:18:53:29:05:01:8b:64:e2:a2:8e. Are you sure you want to continue connecting (yes/no)? yes Warning: Permanently added 'dg2,192.168.124.129' (RSA) to the list of known hosts. oracle@dg2's password: orapwyayu 100% 1536 1.5KB/s 00:00 [oracle@dg1 dbs]$ scp inityayu.ora dg2:/u01/app/oracle/product/11.2.0/dbhome_1/dbs oracle@dg2's password: inityayu.ora 100% 1221 1.2KB/s 00:00 [oracle@dg1 dbs]$ |
备库:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
[oracle@dg2 dbs]$ pwd /u01/app/oracle/product/11.2.0/dbhome_1/dbs [oracle@dg2 dbs]$ ls -ltr total 12 -rw-r--r-- 1 oracle oinstall 2851 May 15 2009 init.ora -rw-r----- 1 oracle oinstall 1536 Oct 26 03:31 orapwyayu -rw-r--r-- 1 oracle oinstall 1221 Oct 26 03:32 inityayu.ora [oracle@dg2 dbs]$ mv orapwyayu orapwstandby_yayu [oracle@dg2 dbs]$ mv inityayu.ora initstandby_yayu.ora [oracle@dg2 dbs]$ ls -ltr total 12 -rw-r--r-- 1 oracle oinstall 2851 May 15 2009 init.ora -rw-r----- 1 oracle oinstall 1536 Oct 26 03:31 orapwstandby_yayu -rw-r--r-- 1 oracle oinstall 1221 Oct 26 03:32 initstandby_yayu.ora [oracle@dg2 dbs]$ |
修改PFILE:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 |
[oracle@dg2 dbs]$ vi initstandby_yayu.ora [oracle@dg2 dbs]$ [oracle@dg2 dbs]$ cat initstandby_yayu.ora yayu.__db_cache_size=121634816 yayu.__java_pool_size=4194304 yayu.__large_pool_size=8388608 yayu.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment yayu.__pga_aggregate_target=171966464 yayu.__sga_target=247463936 yayu.__shared_io_pool_size=0 yayu.__shared_pool_size=104857600 yayu.__streams_pool_size=0 *.audit_file_dest='/u01/app/oracle/admin/standby_yayu/adump' *.audit_trail='db' *.compatible='11.2.0.4.0' *.control_files='/u01/app/oracle/oradata/standby_yayu/control01.ctl','/u01/app/oracle/oradata/standby_yayu/control02.ctl' *.db_block_size=8192 *.db_domain='' *.db_name='yayu' *.db_recovery_file_dest_size=1073741824 *.db_recovery_file_dest='/u01/flash_recovery' *.diagnostic_dest='/u01/app/oracle' *.dispatchers='(PROTOCOL=TCP) (SERVICE=yayuXDB)' *.fal_server='STANDBY_YAYU' *.log_archive_config='dg_config=(YAYU,STANDBY_YAYU)' *.log_archive_dest_1='location=use_db_recovery_file_dest valid_for=(all_logfiles,all_roles) db_unique_name=STANDBY_YAYU' *.log_archive_dest_2='service=standby_yayu async valid_for=(online_logfile,primary_role) db_unique_name=YAYU' *.memory_target=417333248 *.open_cursors=300 *.processes=150 *.remote_login_passwordfile='EXCLUSIVE' *.standby_file_management='AUTO' *.undo_tablespace='UNDOTBS1' *.db_unique_name=standby_yayu db_file_name_convert='/u01/app/oracle/oradata/yayu/','/u01/app/oracle/oradata/standby_yayu/' log_file_name_convert='/u01/app/oracle/oradata/yayu/','/u01/app/oracle/oradata/standby_yayu/' [oracle@dg2 dbs]$ |
在备库上,创建基本的目录结构:
1 2 3 4 |
[root@dg2 ~]# su - oracle [oracle@dg2 ~]$ mkdir -p /u01/app/oracle/admin/standby_yayu/{adump,bdump,pfile} [oracle@dg2 ~]$ mkdir -p /u01/app/oracle/oradata/standby_yayu/ [oracle@dg2 ~]$ |
启动备库到nomount阶段:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 |
[oracle@dg2 dbs]$ env | grep SID ORACLE_SID=edendb1 [oracle@dg2 dbs]$ export ORACLE_SID=standby_yayu [oracle@dg2 dbs]$ env | grep SID ORACLE_SID=standby_yayu [oracle@dg2 dbs]$ [oracle@dg2 dbs]$ ps -ef | grep ora_ oracle 17115 16252 0 03:42 pts/1 00:00:00 grep ora_ [oracle@dg2 dbs]$ [oracle@dg2 dbs]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.4.0 Production on Sun Oct 26 03:42:33 2014 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to an idle instance. SQL> startup nomount pfile=initstandby_yayu.ora ORACLE instance started. Total System Global Area 417546240 bytes Fixed Size 2253824 bytes Variable Size 322964480 bytes Database Buffers 88080384 bytes Redo Buffers 4247552 bytes SQL> create spfile from pfile; File created. SQL> shutdown immediate; ORA-01507: database not mounted ORACLE instance shut down. SQL> startup nomount; ORACLE instance started. Total System Global Area 417546240 bytes Fixed Size 2253824 bytes Variable Size 322964480 bytes Database Buffers 88080384 bytes Redo Buffers 4247552 bytes SQL> show parameter spfile NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ spfile string /u01/app/oracle/product/11.2.0 /dbhome_1/dbs/spfilestandby_ya yu.ora SQL> exit Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options [oracle@dg2 dbs]$ ps -ef | grep ora_ oracle 17167 1 0 03:43 ? 00:00:00 ora_pmon_standby_yayu oracle 17169 1 0 03:43 ? 00:00:00 ora_psp0_standby_yayu oracle 17171 1 0 03:43 ? 00:00:00 ora_vktm_standby_yayu oracle 17175 1 0 03:43 ? 00:00:00 ora_gen0_standby_yayu oracle 17177 1 0 03:43 ? 00:00:00 ora_diag_standby_yayu oracle 17179 1 0 03:43 ? 00:00:00 ora_dbrm_standby_yayu oracle 17181 1 0 03:43 ? 00:00:00 ora_dia0_standby_yayu oracle 17183 1 0 03:43 ? 00:00:00 ora_mman_standby_yayu oracle 17185 1 0 03:43 ? 00:00:00 ora_dbw0_standby_yayu oracle 17187 1 0 03:43 ? 00:00:00 ora_lgwr_standby_yayu oracle 17189 1 0 03:43 ? 00:00:00 ora_ckpt_standby_yayu oracle 17191 1 0 03:43 ? 00:00:00 ora_smon_standby_yayu oracle 17193 1 0 03:43 ? 00:00:00 ora_reco_standby_yayu oracle 17195 1 0 03:43 ? 00:00:00 ora_mmon_standby_yayu oracle 17197 1 0 03:43 ? 00:00:00 ora_mmnl_standby_yayu oracle 17199 1 0 03:43 ? 00:00:00 ora_d000_standby_yayu oracle 17201 1 0 03:43 ? 00:00:00 ora_s000_standby_yayu oracle 17208 16252 0 03:44 pts/1 00:00:00 grep ora_ [oracle@dg2 dbs]$ ls -ltr total 24 -rw-r--r-- 1 oracle oinstall 2851 May 15 2009 init.ora -rw-r----- 1 oracle oinstall 1536 Oct 26 03:31 orapwstandby_yayu -rw-r--r-- 1 oracle oinstall 1462 Oct 26 03:39 initstandby_yayu.ora -rw-r----- 1 oracle oinstall 4608 Oct 26 03:43 spfilestandby_yayu.ora -rw-rw---- 1 oracle oinstall 1544 Oct 26 03:43 hc_standby_yayu.dat [oracle@dg2 dbs]$ |
创建备库:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 |
[oracle@dg2 dbs]$ cat $ORACLE_HOME/network/admin/tnsnames.ora YAYU = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.124.128)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = yayu) ) ) STANDBY_YAYU = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.124.129)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = standby_yayu) ) ) [oracle@dg2 dbs]$ tnsping yayu TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 26-OCT-2014 03:45:37 Copyright (c) 1997, 2013, Oracle. All rights reserved. Used parameter files: Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.124.128)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = yayu))) OK (10 msec) [oracle@dg2 dbs]$ tnsping standby_yayu TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 26-OCT-2014 03:45:54 Copyright (c) 1997, 2013, Oracle. All rights reserved. Used parameter files: Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.124.129)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = standby_yayu))) OK (10 msec) [oracle@dg2 dbs]$ [oracle@dg2 dbs]$ rman Recovery Manager: Release 11.2.0.4.0 - Production on Sun Oct 26 03:47:32 2014 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. RMAN> connect target sys@yayu target database Password: connected to target database: YAYU (DBID=4229443176) RMAN> connect auxiliary sys@standby_yayu auxiliary database Password: connected to auxiliary database: YAYU (not mounted) RMAN> duplicate target database for standby from active database; Starting Duplicate Db at 26-OCT-2014 03:48:32 using target database control file instead of recovery catalog allocated channel: ORA_AUX_DISK_1 channel ORA_AUX_DISK_1: SID=19 device type=DISK contents of Memory Script: { backup as copy reuse targetfile '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwyayu' auxiliary format '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwstandby_yayu' ; } executing Memory Script Starting backup at 26-OCT-2014 03:48:33 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=40 device type=DISK Finished backup at 26-OCT-2014 03:48:34 contents of Memory Script: { backup as copy current controlfile for standby auxiliary format '/u01/app/oracle/oradata/standby_yayu/control01.ctl'; restore clone controlfile to '/u01/app/oracle/oradata/standby_yayu/control02.ctl' from '/u01/app/oracle/oradata/standby_yayu/control01.ctl'; } executing Memory Script Starting backup at 26-OCT-2014 03:48:34 using channel ORA_DISK_1 channel ORA_DISK_1: starting datafile copy copying standby control file output file name=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/snapcf_yayu.f tag=TAG20141026T034834 RECID=2 STAMP=861940115 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07 Finished backup at 26-OCT-2014 03:48:41 Starting restore at 26-OCT-2014 03:48:41 using channel ORA_AUX_DISK_1 channel ORA_AUX_DISK_1: copied control file copy Finished restore at 26-OCT-2014 03:48:42 contents of Memory Script: { sql clone 'alter database mount standby database'; } executing Memory Script sql statement: alter database mount standby database contents of Memory Script: { set newname for tempfile 1 to "/u01/app/oracle/oradata/standby_yayu/temp01.dbf"; switch clone tempfile all; set newname for datafile 1 to "/u01/app/oracle/oradata/standby_yayu/system01.dbf"; set newname for datafile 2 to "/u01/app/oracle/oradata/standby_yayu/sysaux01.dbf"; set newname for datafile 3 to "/u01/app/oracle/oradata/standby_yayu/undotbs01.dbf"; set newname for datafile 4 to "/u01/app/oracle/oradata/standby_yayu/users01.dbf"; set newname for datafile 5 to "/u01/app/oracle/oradata/standby_yayu/example01.dbf"; backup as copy reuse datafile 1 auxiliary format "/u01/app/oracle/oradata/standby_yayu/system01.dbf" datafile 2 auxiliary format "/u01/app/oracle/oradata/standby_yayu/sysaux01.dbf" datafile 3 auxiliary format "/u01/app/oracle/oradata/standby_yayu/undotbs01.dbf" datafile 4 auxiliary format "/u01/app/oracle/oradata/standby_yayu/users01.dbf" datafile 5 auxiliary format "/u01/app/oracle/oradata/standby_yayu/example01.dbf" ; sql 'alter system archive log current'; } executing Memory Script executing command: SET NEWNAME renamed tempfile 1 to /u01/app/oracle/oradata/standby_yayu/temp01.dbf in control file executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME Starting backup at 26-OCT-2014 03:48:48 using channel ORA_DISK_1 channel ORA_DISK_1: starting datafile copy input datafile file number=00001 name=/u01/app/oracle/oradata/yayu/system01.dbf output file name=/u01/app/oracle/oradata/standby_yayu/system01.dbf tag=TAG20141026T034848 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:35 channel ORA_DISK_1: starting datafile copy input datafile file number=00002 name=/u01/app/oracle/oradata/yayu/sysaux01.dbf output file name=/u01/app/oracle/oradata/standby_yayu/sysaux01.dbf tag=TAG20141026T034848 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25 channel ORA_DISK_1: starting datafile copy input datafile file number=00005 name=/u01/app/oracle/oradata/yayu/example01.dbf output file name=/u01/app/oracle/oradata/standby_yayu/example01.dbf tag=TAG20141026T034848 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15 channel ORA_DISK_1: starting datafile copy input datafile file number=00003 name=/u01/app/oracle/oradata/yayu/undotbs01.dbf output file name=/u01/app/oracle/oradata/standby_yayu/undotbs01.dbf tag=TAG20141026T034848 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07 channel ORA_DISK_1: starting datafile copy input datafile file number=00004 name=/u01/app/oracle/oradata/yayu/users01.dbf output file name=/u01/app/oracle/oradata/standby_yayu/users01.dbf tag=TAG20141026T034848 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03 Finished backup at 26-OCT-2014 03:50:14 sql statement: alter system archive log current contents of Memory Script: { switch clone datafile all; } executing Memory Script datafile 1 switched to datafile copy input datafile copy RECID=2 STAMP=861940216 file name=/u01/app/oracle/oradata/standby_yayu/system01.dbf datafile 2 switched to datafile copy input datafile copy RECID=3 STAMP=861940216 file name=/u01/app/oracle/oradata/standby_yayu/sysaux01.dbf datafile 3 switched to datafile copy input datafile copy RECID=4 STAMP=861940216 file name=/u01/app/oracle/oradata/standby_yayu/undotbs01.dbf datafile 4 switched to datafile copy input datafile copy RECID=5 STAMP=861940216 file name=/u01/app/oracle/oradata/standby_yayu/users01.dbf datafile 5 switched to datafile copy input datafile copy RECID=6 STAMP=861940216 file name=/u01/app/oracle/oradata/standby_yayu/example01.dbf Finished Duplicate Db at 26-OCT-2014 03:50:20 RMAN> exit Recovery Manager complete. [oracle@dg2 dbs]$ |
备库的ALERT日志:
1 2 3 4 5 6 |
[oracle@dg2 standby_yayu]$ pwd /u01/app/oracle/diag/rdbms/standby_yayu/standby_yayu [oracle@dg2 standby_yayu]$ ls alert cdump hm incident incpkg ir lck metadata metadata_dgif metadata_pv stage sweep trace [oracle@dg2 standby_yayu]$ tail -f trace/alert_standby_yayu.log (... ...) |
确认日志应用是否正常:
主库:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
SQL> col destination for a28 SQL> select DEST_ID, STATUS, DESTINATION, ERROR from V$ARCHIVE_DEST where DEST_ID<=2; DEST_ID STATUS DESTINATION ERROR ---------- --------- ---------------------------- -------- 1 VALID USE_DB_RECOVERY_FILE_DEST 2 VALID standby_yayu SQL> SQL> col name for a80 SQL> set linesize 400 SQL> select SEQUENCE#, FIRST_TIME, NEXT_TIME, APPLIED, ARCHIVED,name from v$ARCHIVED_LOG order by FIRST_TIME; SEQUENCE# FIRST_TIM NEXT_TIME APPLIED ARC NAME ---------- --------- --------- --------- --- -------------------------------------------------------------------------------- 3 26-OCT-14 26-OCT-14 NO YES /u01/flash_recovery/YAYU/archivelog/2014_10_26/o1_mf_1_3_b4slvfgh_.arc 4 26-OCT-14 26-OCT-14 NO YES /u01/flash_recovery/YAYU/archivelog/2014_10_26/o1_mf_1_4_b4slvjfo_.arc 5 26-OCT-14 26-OCT-14 NO YES /u01/flash_recovery/YAYU/archivelog/2014_10_26/o1_mf_1_5_b4snh795_.arc 5 26-OCT-14 26-OCT-14 YES YES standby_yayu 6 26-OCT-14 26-OCT-14 NO YES /u01/flash_recovery/YAYU/archivelog/2014_10_26/o1_mf_1_6_b4snj1f3_.arc 6 26-OCT-14 26-OCT-14 YES YES standby_yayu 6 rows selected. SQL> |
(如上所示,到备库的日志的APPLIED与ARCHIVED应该都是YES。)
备库:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
SQL> select DEST_ID, STATUS, DESTINATION, ERROR from V$ARCHIVE_DEST where DEST_ID<=2; DEST_ID STATUS DESTINATION ERROR ---------- --------- ---------------------------- -------- 1 VALID USE_DB_RECOVERY_FILE_DEST 2 VALID standby_yayu SQL> select SEQUENCE#, FIRST_TIME, NEXT_TIME, APPLIED, ARCHIVED,name from v$ARCHIVED_LOG; SEQUENCE# FIRST_TIM NEXT_TIME APPLIED ARC NAME ---------- --------- --------- --------- --- ------------------------------------------------------------------------------ 5 26-OCT-14 26-OCT-14 YES YES /u01/flash_recovery/STANDBY_YAYU/archivelog/2014_10_26/o1_mf_1_5_b4snhso6_.arc 6 26-OCT-14 26-OCT-14 YES YES /u01/flash_recovery/STANDBY_YAYU/archivelog/2014_10_26/o1_mf_1_6_b4snj1jo_.arc SQL> |
如果发现有日志没有被应用,那么有可能是重做日志的连续性被打破了。
一般来说FAL_SERVER设置正确,是不会出现这样的问题的。
一下SQL可以帮助你检查你的DG是否存在重做日志的缺口问题,该SQL应该在主库上执行。
1 2 3 4 5 6 7 |
SQL> select STATUS, GAP_STATUS from V$ARCHIVE_DEST_STATUS where DEST_ID = 2; STATUS GAP_STATUS --------- ------------------------ VALID NO GAP SQL> |
一切正常的时候,如上所示,返回VALID和NO GAP。
如果需要查询你的Data Guard的状态(发生过什么),那么除了可以通过查看ALERT日志确认外,还可以通过以下方法查看:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 |
SQL> col message for a85 SQL> select * from V$DATAGUARD_STATUS order by TIMESTAMP; FACILITY SEVERITY DEST_ID MESSAGE_NUM ERROR_CODE CAL TIMESTAMP MESSAGE ------------------------ ------------- ---------- ----------- ---------- --- --------- ------------------------------------------------------------------------------------- Log Transport Services Informational 0 1 0 NO 26-OCT-14 ARC0: Archival started Log Transport Services Informational 0 2 0 NO 26-OCT-14 ARC1: Archival started Log Transport Services Informational 0 3 0 NO 26-OCT-14 ARC2: Archival started Log Transport Services Informational 0 4 0 NO 26-OCT-14 ARC1: Becoming the 'no FAL' ARCH Log Transport Services Informational 0 5 0 NO 26-OCT-14 ARC1: Becoming the 'no SRL' ARCH Log Transport Services Informational 0 6 0 NO 26-OCT-14 ARC2: Becoming the heartbeat ARCH Log Transport Services Informational 0 7 0 NO 26-OCT-14 ARC3: Archival started Log Transport Services Informational 0 8 0 NO 26-OCT-14 ARC4: Archival started Log Transport Services Informational 0 9 0 NO 26-OCT-14 ARCH shutting down Log Transport Services Informational 0 10 0 NO 26-OCT-14 ARC4: Archival stopped Log Transport Services Control 0 11 0 YES 26-OCT-14 ARC0: Beginning to archive thread 1 sequence 3 (960812-971313) Log Transport Services Control 0 12 0 YES 26-OCT-14 ARC0: Completed archiving thread 1 sequence 3 (960812-971313) Log Transport Services Error 0 13 12514 YES 26-OCT-14 Error 12514 received logging on to the standby Fetch Archive Log Error 2 14 12514 YES 26-OCT-14 FAL[server, ARC3]: Error 12514 creating remote archivelog file 'standby_yayu' Log Transport Services Control 0 15 0 YES 26-OCT-14 ARC0: Beginning to archive thread 1 sequence 4 (971313-971316) Log Transport Services Control 0 16 0 YES 26-OCT-14 ARC0: Completed archiving thread 1 sequence 4 (971313-971316) Log Transport Services Error 0 17 12514 YES 26-OCT-14 Error 12514 received logging on to the standby Log Transport Services Error 2 18 12514 YES 26-OCT-14 PING[ARC2]: Heartbeat failed to connect to standby 'standby_yayu'. Error is 12514. Log Transport Services Error 0 19 12514 YES 26-OCT-14 Error 12514 received logging on to the standby Log Transport Services Error 2 20 12514 YES 26-OCT-14 PING[ARC2]: Heartbeat failed to connect to standby 'standby_yayu'. Error is 12514. Log Transport Services Error 0 21 12514 YES 26-OCT-14 Error 12514 received logging on to the standby Log Transport Services Error 2 22 12514 YES 26-OCT-14 PING[ARC2]: Heartbeat failed to connect to standby 'standby_yayu'. Error is 12514. Log Transport Services Error 0 23 12514 YES 26-OCT-14 Error 12514 received logging on to the standby Log Transport Services Error 2 24 12514 YES 26-OCT-14 PING[ARC2]: Heartbeat failed to connect to standby 'standby_yayu'. Error is 12514. Log Transport Services Error 0 25 12514 YES 26-OCT-14 Error 12514 received logging on to the standby Log Transport Services Error 2 26 12514 YES 26-OCT-14 PING[ARC2]: Heartbeat failed to connect to standby 'standby_yayu'. Error is 12514. Log Transport Services Error 0 27 12514 YES 26-OCT-14 Error 12514 received logging on to the standby Log Transport Services Error 2 28 12514 YES 26-OCT-14 PING[ARC2]: Heartbeat failed to connect to standby 'standby_yayu'. Error is 12514. Log Transport Services Error 0 29 12514 YES 26-OCT-14 Error 12514 received logging on to the standby Log Transport Services Error 2 30 12514 YES 26-OCT-14 PING[ARC2]: Heartbeat failed to connect to standby 'standby_yayu'. Error is 12514. Log Transport Services Error 0 31 12514 YES 26-OCT-14 Error 12514 received logging on to the standby Log Transport Services Error 2 32 12514 YES 26-OCT-14 PING[ARC2]: Heartbeat failed to connect to standby 'standby_yayu'. Error is 12514. Log Transport Services Error 0 33 12514 YES 26-OCT-14 Error 12514 received logging on to the standby Log Transport Services Error 2 34 12514 YES 26-OCT-14 PING[ARC2]: Heartbeat failed to connect to standby 'standby_yayu'. Error is 12514. Log Transport Services Error 0 35 12514 YES 26-OCT-14 Error 12514 received logging on to the standby Log Transport Services Error 2 36 12514 YES 26-OCT-14 PING[ARC2]: Heartbeat failed to connect to standby 'standby_yayu'. Error is 12514. Log Transport Services Error 0 37 12514 YES 26-OCT-14 Error 12514 received logging on to the standby Log Transport Services Error 2 38 12514 YES 26-OCT-14 PING[ARC2]: Heartbeat failed to connect to standby 'standby_yayu'. Error is 12514. Log Transport Services Error 0 39 12514 YES 26-OCT-14 Error 12514 received logging on to the standby Log Transport Services Error 2 40 12514 YES 26-OCT-14 PING[ARC2]: Heartbeat failed to connect to standby 'standby_yayu'. Error is 12514. Log Transport Services Error 0 41 12514 YES 26-OCT-14 Error 12514 received logging on to the standby Log Transport Services Error 2 42 12514 YES 26-OCT-14 PING[ARC2]: Heartbeat failed to connect to standby 'standby_yayu'. Error is 12514. Log Transport Services Error 0 43 12514 YES 26-OCT-14 Error 12514 received logging on to the standby Log Transport Services Error 2 44 12514 YES 26-OCT-14 PING[ARC2]: Heartbeat failed to connect to standby 'standby_yayu'. Error is 12514. Log Transport Services Error 0 45 12514 YES 26-OCT-14 Error 12514 received logging on to the standby Log Transport Services Error 2 46 12514 YES 26-OCT-14 PING[ARC2]: Heartbeat failed to connect to standby 'standby_yayu'. Error is 12514. Log Transport Services Error 0 47 12514 YES 26-OCT-14 Error 12514 received logging on to the standby Log Transport Services Error 2 48 12514 YES 26-OCT-14 PING[ARC2]: Heartbeat failed to connect to standby 'standby_yayu'. Error is 12514. Log Transport Services Error 0 49 12528 YES 26-OCT-14 Error 12528 received logging on to the standby Log Transport Services Error 2 50 12528 YES 26-OCT-14 PING[ARC2]: Heartbeat failed to connect to standby 'standby_yayu'. Error is 12528. Log Transport Services Error 0 51 12528 YES 26-OCT-14 Error 12528 received logging on to the standby Log Transport Services Error 2 52 12528 YES 26-OCT-14 PING[ARC2]: Heartbeat failed to connect to standby 'standby_yayu'. Error is 12528. Log Transport Services Error 0 53 12528 YES 26-OCT-14 Error 12528 received logging on to the standby Log Transport Services Error 2 54 12528 YES 26-OCT-14 PING[ARC2]: Heartbeat failed to connect to standby 'standby_yayu'. Error is 12528. Log Transport Services Error 2 55 16058 YES 26-OCT-14 PING[ARC2]: Heartbeat failed to connect to standby 'standby_yayu'. Error is 16058. Log Transport Services Error 2 56 16058 YES 26-OCT-14 PING[ARC2]: Heartbeat failed to connect to standby 'standby_yayu'. Error is 16058. Log Transport Services Error 2 57 16058 YES 26-OCT-14 PING[ARC2]: Heartbeat failed to connect to standby 'standby_yayu'. Error is 16058. Log Transport Services Control 0 58 0 YES 26-OCT-14 ARCH: Beginning to archive thread 1 sequence 5 (971316-973156) Log Transport Services Control 0 59 0 YES 26-OCT-14 ARCH: Completed archiving thread 1 sequence 5 (971316-973156) Log Transport Services Warning 1 60 0 NO 26-OCT-14 ARC0: Standby redo logfile selected for thread 1 sequence 5 for destination LOG_ARCHI VE_DEST_2 Log Transport Services Control 0 61 0 YES 26-OCT-14 ARC3: Beginning to archive thread 1 sequence 6 (973156-973191) Log Transport Services Control 0 62 0 YES 26-OCT-14 ARC3: Completed archiving thread 1 sequence 6 (973156-973191) Log Transport Services Warning 1 63 0 NO 26-OCT-14 LNS: Standby redo logfile selected for thread 1 sequence 7 for destination LOG_ARCHIV E_DEST_2 Log Transport Services Control 0 64 0 YES 26-OCT-14 LNS: Beginning to archive log 1 thread 1 sequence 7 64 rows selected. SQL> |
————————————————————————————————
Ending。