Oracle 11g：Dataguard的初步配置
如图所示，本文将描述Oracle 11g中Dataguard的配置。
环境说明
Primary·主库:
192.168.223.129
DB_NAME=orcl
Standby·备库:
192.168.223.130
DB_NAME=orcl
归档路径：
/u01/archivelog
主库配置
1.开启归档模式
(Primary)
|
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 |
[root@dg1 software]# su - oracle [oracle@dg1 ~]$ env | grep SID ORACLE_SID=orcl [oracle@dg1 ~]$ ps -ef | grep pmon oracle 20912 1 0 Oct14 ? 00:00:00 ora_pmon_orcl oracle 25400 25372 0 00:54 pts/2 00:00:00 grep pmon [oracle@dg1 ~]$ [oracle@dg1 ~]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.3.0 Production on Wed Oct 15 00:54:39 2014 Copyright (c) 1982, 2011, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> archive log list; Database log mode Archive Mode Automatic archival Enabled Archive destination USE_DB_RECOVERY_FILE_DEST Oldest online log sequence 2 Next log sequence to archive 4 Current log sequence 4 SQL> |
2.设置force logging
(Primary)
|
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 --------- --- ORCL NO SQL> alter database force logging; Database altered. SQL> select name,force_logging from v$database; NAME FOR --------- --- ORCL YES SQL> |
3.配置Oracle Net
(Primary)
|
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 admin]$ pwd /u01/app/oracle/product/11.2.0/dbhome_1/network/admin [oracle@dg1 admin]$ ll total 16 -rw-r--r-- 1 oracle oinstall 588 Oct 15 01:02 listener.ora drwxr-xr-x 2 oracle oinstall 4096 Oct 14 19:44 samples -rw-r--r-- 1 oracle oinstall 205 May 11 2011 shrept.lst -rw-r----- 1 oracle oinstall 595 Oct 15 01:05 tnsnames.ora [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 # Static Register Instance to Listener SID_LIST_LISTENER = ( SID_LIST = ( SID_DESC = (GLOBAL_DBNAME = orcl) (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1) (SID_NAME = orcl) ) ) [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. # Oracle 11g Dataguard: Primary ORCL_primary = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.223.129)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl) ) ) # Oracle 11g Dataguard: Standby ORCL_standby = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.223.130)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl) ) ) [oracle@dg1 admin]$ |
(Standby)
|
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 |
[oracle@dg2 admin]$ pwd /u01/app/oracle/product/11.2.0/dbhome_1/network/admin [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 # Static Register Instance to Listener SID_LIST_LISTENER = ( SID_LIST = ( SID_DESC = (GLOBAL_DBNAME = orcl) (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1) (SID_NAME = orcl) ) ) [oracle@dg2 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. # Oracle 11g Dataguard: Primary ORCL_primary = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.223.129)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl) ) ) # Oracle 11g Dataguard: Standby ORCL_standby = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.223.130)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl) ) ) [oracle@dg2 admin]$ |
修改“listener.ora”与“tnsnames.ora”之后，需要重启LISTENER，以便生效。
|
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 |
[oracle@dg1 admin]$ lsnrctl stop LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 15-OCT-2014 01:10:04 Copyright (c) 1991, 2011, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=dg1)(PORT=1521))) The command completed successfully [oracle@dg1 admin]$ lsnrctl start LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 15-OCT-2014 01:10:13 Copyright (c) 1991, 2011, Oracle. All rights reserved. Starting /u01/app/oracle/product/11.2.0/dbhome_1/bin/tnslsnr: please wait... TNSLSNR for Linux: Version 11.2.0.3.0 - Production System parameter file is /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora Log messages written to /u01/app/oracle/diag/tnslsnr/dg1/listener/alert/log.xml Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dg1)(PORT=1521))) Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521))) Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=dg1)(PORT=1521))) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 11.2.0.3.0 - Production Start Date 15-OCT-2014 01:10:13 Uptime 0 days 0 hr. 0 min. 0 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora Listener Log File /u01/app/oracle/diag/tnslsnr/dg1/listener/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dg1)(PORT=1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521))) Services Summary... Service "orcl" has 1 instance(s). Instance "orcl", status UNKNOWN, has 1 handler(s) for this service... The command completed successfully [oracle@dg1 admin]$ |
4.配置Dataguard参数：
(Primary)
|
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@dg1 admin]$ cd $ORACLE_HOME/dbs [oracle@dg1 dbs]$ ls -ltr total 20 -rw-r--r-- 1 oracle oinstall 2851 May 15 2009 init.ora -rw-r----- 1 oracle oinstall 24 Oct 14 20:29 lkORCL -rw-r----- 1 oracle oinstall 1536 Oct 14 20:32 orapworcl -rw-rw---- 1 oracle oinstall 1544 Oct 14 20:34 hc_orcl.dat -rw-r----- 1 oracle oinstall 2560 Oct 14 22:01 spfileorcl.ora [oracle@dg1 dbs]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.3.0 Production on Wed Oct 15 01:12:35 2014 Copyright (c) 1982, 2011, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> show parameter db_name NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_name string orcl SQL> SQL> create pfile from spfile; File created. SQL> exit Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options [oracle@dg1 dbs]$ ls -ltr total 24 -rw-r--r-- 1 oracle oinstall 2851 May 15 2009 init.ora -rw-r----- 1 oracle oinstall 24 Oct 14 20:29 lkORCL -rw-r----- 1 oracle oinstall 1536 Oct 14 20:32 orapworcl -rw-rw---- 1 oracle oinstall 1544 Oct 14 20:34 hc_orcl.dat -rw-r----- 1 oracle oinstall 2560 Oct 14 22:01 spfileorcl.ora -rw-r--r-- 1 oracle oinstall 927 Oct 15 01:12 initorcl.ora [oracle@dg1 dbs]$ vi initorcl.ora [oracle@dg1 dbs]$ cat initorcl.ora orcl.__db_cache_size=109051904 orcl.__java_pool_size=4194304 orcl.__large_pool_size=4194304 orcl.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment orcl.__pga_aggregate_target=171966464 orcl.__sga_target=247463936 orcl.__shared_io_pool_size=0 orcl.__shared_pool_size=121634816 orcl.__streams_pool_size=0 *.audit_file_dest='/u01/app/oracle/admin/orcl/adump' *.audit_trail='db' *.compatible='11.2.0.0.0' *.control_files='/u01/app/oracle/oradata/orcl/control01.ctl','/u01/app/oracle/oradata/orcl/control02.ctl' *.db_block_size=8192 *.db_domain='' *.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area' *.db_recovery_file_dest_size=4322230272 *.diagnostic_dest='/u01/app/oracle' *.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)' *.log_archive_format='%t_%s_%r.dbf' *.memory_target=417333248 *.open_cursors=300 *.processes=150 *.remote_login_passwordfile='EXCLUSIVE' *.undo_tablespace='UNDOTBS1' *.db_name='orcl' *.db_unique_name='orcl_primary' *.log_archive_config='dg_config=(orcl_primary,orcl_standby)' *.log_archive_dest_1='location=/u01/archivelog valid_for=(all_logfiles,all_roles) db_unique_name=orcl_primary' *.log_archive_dest_2='service=orcl_standby reopen=120 lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=orcl_standby' *.log_archive_dest_state_1=enable *.log_archive_dest_state_2=enable *.standby_file_management='auto' *.fal_server='orcl_standby' *.fal_client='orcl_primary' [oracle@dg1 dbs]$ |
在11g Dataguard中，参数standby_archive_dest已过期。
5.复制主库的REDO到备库
|
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 |
SQL> select member from v$logfile; MEMBER -------------------------------------------------------------------------------- /u01/app/oracle/oradata/orcl/redo03.log /u01/app/oracle/oradata/orcl/redo02.log /u01/app/oracle/oradata/orcl/redo01.log SQL> select instance_name,status from v$instance; INSTANCE_NAME STATUS ---------------- ------------ orcl OPEN SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> exit Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options [oracle@dg1 dbs]$ [oracle@dg1 dbs]$ du -sh /u01/app/oracle/oradata/orcl/*.log 51M /u01/app/oracle/oradata/orcl/redo01.log 51M /u01/app/oracle/oradata/orcl/redo02.log 51M /u01/app/oracle/oradata/orcl/redo03.log [oracle@dg1 dbs]$ [oracle@dg1 dbs]$ scp /u01/app/oracle/oradata/orcl/*.log 192.168.223.130:/u01/app/oracle/oradata/orcl_standby/ The authenticity of host '192.168.223.130 (192.168.223.130)' can't be established. RSA key fingerprint is 66:0c:61:38:18:1f:27:c8:9c:cb:1e:85:6e:e5:90:88. Are you sure you want to continue connecting (yes/no)? yes Warning: Permanently added '192.168.223.130' (RSA) to the list of known hosts. oracle@192.168.223.130's password: redo01.log 100% 50MB 50.0MB/s 00:01 redo02.log 100% 50MB 50.0MB/s 00:01 redo03.log 100% 50MB 12.5MB/s 00:04 [oracle@dg1 dbs]$ |
6.通过新的初始化参数启动主库
(Primary)
|
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 |
[oracle@dg1 dbs]$ ps -ef | grep ora_ oracle 20912 1 0 Oct14 ? 00:00:00 ora_pmon_orcl oracle 20914 1 0 Oct14 ? 00:00:00 ora_psp0_orcl oracle 20916 1 0 Oct14 ? 00:00:03 ora_vktm_orcl oracle 20920 1 0 Oct14 ? 00:00:00 ora_gen0_orcl oracle 20922 1 0 Oct14 ? 00:00:00 ora_diag_orcl oracle 20924 1 0 Oct14 ? 00:00:00 ora_dbrm_orcl oracle 20926 1 0 Oct14 ? 00:00:05 ora_dia0_orcl oracle 20928 1 0 Oct14 ? 00:00:00 ora_mman_orcl oracle 20930 1 0 Oct14 ? 00:00:00 ora_dbw0_orcl oracle 20932 1 0 Oct14 ? 00:00:01 ora_lgwr_orcl oracle 20934 1 0 Oct14 ? 00:00:03 ora_ckpt_orcl oracle 20936 1 0 Oct14 ? 00:00:01 ora_smon_orcl oracle 20938 1 0 Oct14 ? 00:00:00 ora_reco_orcl oracle 20940 1 0 Oct14 ? 00:00:03 ora_mmon_orcl oracle 20942 1 0 Oct14 ? 00:00:03 ora_mmnl_orcl oracle 20944 1 0 Oct14 ? 00:00:00 ora_d000_orcl oracle 20946 1 0 Oct14 ? 00:00:00 ora_s000_orcl oracle 20987 1 0 Oct14 ? 00:00:00 ora_arc0_orcl oracle 20989 1 0 Oct14 ? 00:00:00 ora_arc1_orcl oracle 20991 1 0 Oct14 ? 00:00:00 ora_arc2_orcl oracle 20993 1 0 Oct14 ? 00:00:00 ora_arc3_orcl oracle 20995 1 0 Oct14 ? 00:00:00 ora_qmnc_orcl oracle 20997 1 0 Oct14 ? 00:00:00 ora_q000_orcl oracle 20999 1 0 Oct14 ? 00:00:00 ora_q001_orcl oracle 21014 1 0 Oct14 ? 00:00:03 ora_cjq0_orcl oracle 21163 1 0 Oct14 ? 00:00:00 ora_smco_orcl oracle 22477 1 0 Oct14 ? 00:00:00 ora_vkrm_orcl oracle 25915 1 0 01:21 ? 00:00:00 ora_w000_orcl oracle 25928 25372 0 01:22 pts/2 00:00:00 grep ora_ [oracle@dg1 dbs]$ env | grep SID ORACLE_SID=orcl [oracle@dg1 dbs]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.3.0 Production on Wed Oct 15 01:22:40 2014 Copyright (c) 1982, 2011, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> select instance_name,status from v$instance; INSTANCE_NAME STATUS ---------------- ------------ orcl OPEN SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> !ps -ef | grep pmon oracle 25941 25934 0 01:23 pts/2 00:00:00 /bin/bash -c ps -ef | grep pmon SQL> SQL> !pwd /u01/app/oracle/product/11.2.0/dbhome_1/dbs SQL> !ls -ltr total 24 -rw-r--r-- 1 oracle oinstall 2851 May 15 2009 init.ora -rw-r----- 1 oracle oinstall 24 Oct 14 20:29 lkORCL -rw-r----- 1 oracle oinstall 1536 Oct 14 20:32 orapworcl -rw-r----- 1 oracle oinstall 2560 Oct 14 22:01 spfileorcl.ora -rw-r--r-- 1 oracle oinstall 1424 Oct 15 01:19 initorcl.ora -rw-rw---- 1 oracle oinstall 1544 Oct 15 01:22 hc_orcl.dat SQL> !mv spfileorcl.ora spfileorcl.ora_orig_20141015-1624 SQL> !ls -ltr total 24 -rw-r--r-- 1 oracle oinstall 2851 May 15 2009 init.ora -rw-r----- 1 oracle oinstall 24 Oct 14 20:29 lkORCL -rw-r----- 1 oracle oinstall 1536 Oct 14 20:32 orapworcl -rw-r----- 1 oracle oinstall 2560 Oct 14 22:01 spfileorcl.ora_orig_20141015-1624 -rw-r--r-- 1 oracle oinstall 1424 Oct 15 01:19 initorcl.ora -rw-rw---- 1 oracle oinstall 1544 Oct 15 01:22 hc_orcl.dat SQL> create spfile from pfile='initorcl.ora'; File created. SQL> !ls -ltr total 28 -rw-r--r-- 1 oracle oinstall 2851 May 15 2009 init.ora -rw-r----- 1 oracle oinstall 24 Oct 14 20:29 lkORCL -rw-r----- 1 oracle oinstall 1536 Oct 14 20:32 orapworcl -rw-r----- 1 oracle oinstall 2560 Oct 14 22:01 spfileorcl.ora_orig_20141015-1624 -rw-r--r-- 1 oracle oinstall 1424 Oct 15 01:19 initorcl.ora -rw-rw---- 1 oracle oinstall 1544 Oct 15 01:22 hc_orcl.dat -rw-r----- 1 oracle oinstall 3584 Oct 15 01:24 spfileorcl.ora SQL> startup ORACLE instance started. Total System Global Area 417546240 bytes Fixed Size 2228944 bytes Variable Size 301993264 bytes Database Buffers 109051904 bytes Redo Buffers 4272128 bytes Database mounted. Database opened. SQL> !ps -ef | grep -v grep | grep ora_ | wc -l 27 SQL> select instance_name,status from v$instance; INSTANCE_NAME STATUS ---------------- ------------ orcl OPEN SQL> |
备库配置
1.创建相关的目录结构
日志部分：
|
1 2 3 |
[oracle@dg2 ~]$ cd $ORACLE_BASE [oracle@dg2 dbs]$ mkdir -p /u01/app/oracle/admin/orcl_standby/{adump,dpdump,pfile} [oracle@dg2 dbs]$ |
数据部分（oradata）：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
[oracle@dg2 oracle]$ pwd /u01/app/oracle [oracle@dg2 oracle]$ ll total 20 drwxr-xr-x 3 oracle oinstall 4096 Oct 15 01:33 admin drwxr-xr-x 3 oracle oinstall 4096 Oct 14 20:17 cfgtoollogs drwxr-xr-x 2 oracle oinstall 4096 Oct 14 20:14 checkpoints drwxrwxr-x 11 oracle oinstall 4096 Oct 14 20:09 diag drwxr-xr-x 3 oracle oinstall 4096 Oct 14 18:48 product [oracle@dg2 oracle]$ mkdir -p oradata/orcl_standby/ [oracle@dg2 oracle]$ ll total 24 drwxr-xr-x 3 oracle oinstall 4096 Oct 15 01:33 admin drwxr-xr-x 3 oracle oinstall 4096 Oct 14 20:17 cfgtoollogs drwxr-xr-x 2 oracle oinstall 4096 Oct 14 20:14 checkpoints drwxrwxr-x 11 oracle oinstall 4096 Oct 14 20:09 diag drwxr-xr-x 3 oracle oinstall 4096 Oct 15 01:35 oradata drwxr-xr-x 3 oracle oinstall 4096 Oct 14 18:48 product [oracle@dg2 oracle]$ tree oradata/ oradata/ `-- orcl_standby 1 directory, 0 files [oracle@dg2 oracle]$ |
关于数据目录：
主库中为：$ORACLE_BASE/oradata/orcl
备库中为：$ORACLE_BASE/oradata/orcl_standby
这一部分的差异会在稍后的参数文件中有所体现。
快速恢复区：
|
1 2 |
[oracle@dg2 dbs]$ mkdir -p /u01/app/oracle/fast_recovery_area [oracle@dg2 dbs]$ |
2.创建STANDBY库的口令文件
|
1 2 3 4 5 6 7 8 9 10 |
[oracle@dg2 ~]$ cd $ORACLE_HOME/dbs [oracle@dg2 dbs]$ ll total 4 -rw-r--r-- 1 oracle oinstall 2851 May 15 2009 init.ora [oracle@dg2 dbs]$ orapwd file=orapworcl password=oracle entries=10 [oracle@dg2 dbs]$ ll total 8 -rw-r--r-- 1 oracle oinstall 2851 May 15 2009 init.ora -rw-r----- 1 oracle oinstall 2560 Oct 15 01:39 orapworcl [oracle@dg2 dbs]$ |
3.配置STANDBY库的初始化参数
|
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@dg2 dbs]$ pwd /u01/app/oracle/product/11.2.0/dbhome_1/dbs [oracle@dg2 dbs]$ ll total 12 -rw-r--r-- 1 oracle oinstall 2851 May 15 2009 init.ora -rw-r--r-- 1 oracle oinstall 1636 Oct 15 01:47 initorcl.ora -rw-r----- 1 oracle oinstall 2560 Oct 15 01:39 orapworcl [oracle@dg2 dbs]$ cat initorcl.ora orcl.__db_cache_size=109051904 orcl.__java_pool_size=4194304 orcl.__large_pool_size=4194304 orcl.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment orcl.__pga_aggregate_target=171966464 orcl.__sga_target=247463936 orcl.__shared_io_pool_size=0 orcl.__shared_pool_size=121634816 orcl.__streams_pool_size=0 *.audit_file_dest='/u01/app/oracle/admin/orcl/adump' *.audit_trail='db' *.compatible='11.2.0.0.0' *.control_files='/u01/app/oracle/oradata/orcl/control01.ctl','/u01/app/oracle/oradata/orcl/control02.ctl' *.db_block_size=8192 *.db_domain='' *.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area' *.db_recovery_file_dest_size=4322230272 *.diagnostic_dest='/u01/app/oracle' *.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)' *.log_archive_format='%t_%s_%r.dbf' *.memory_target=417333248 *.open_cursors=300 *.processes=150 *.remote_login_passwordfile='EXCLUSIVE' *.undo_tablespace='UNDOTBS1' *.db_name='orcl' *.db_unique_name='orcl_standby' *.log_archive_config='dg_config=(orcl_primary,orcl_standby)' *.log_archive_dest_1='location=/u01/archivelog valid_for=(all_logfiles,all_roles) db_unique_name=orcl_standby' *.log_archive_dest_2='service=orcl_primary reopen=120 lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=orcl_primary' *.log_archive_dest_state_1=enable *.log_archive_dest_state_2=enable *.standby_file_management='auto' *.fal_server='orcl_primary' *.fal_client='orcl_standby' *.log_file_name_convert='/u01/app/oracle/oradata/orcl','/u01/app/oracle/oradata/orcl_standby' *.db_file_name_convert='/u01/app/oracle/oradata/orcl','/u01/app/oracle/oradata/orcl_standby' [oracle@dg2 dbs]$ |
4.启动STANDBY库到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 |
[oracle@dg2 dbs]$ pwd /u01/app/oracle/product/11.2.0/dbhome_1/dbs [oracle@dg2 dbs]$ ll total 12 -rw-r--r-- 1 oracle oinstall 2851 May 15 2009 init.ora -rw-r--r-- 1 oracle oinstall 1636 Oct 15 01:47 initorcl.ora -rw-r----- 1 oracle oinstall 2560 Oct 15 01:39 orapworcl [oracle@dg2 dbs]$ ps -ef | grep ora_ oracle 25445 24824 0 01:52 pts/2 00:00:00 grep ora_ [oracle@dg2 dbs]$ [oracle@dg2 dbs]$ export ORACLE_SID=orcl [oracle@dg2 dbs]$ [oracle@dg2 dbs]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.3.0 Production on Wed Oct 15 01:52:46 2014 Copyright (c) 1982, 2011, Oracle. All rights reserved. Connected to an idle instance. SQL> create spfile from pfile='initorcl.ora'; File created. SQL> !pwd /u01/app/oracle/product/11.2.0/dbhome_1/dbs SQL> !ls -ltr total 20 -rw-r--r-- 1 oracle oinstall 2851 May 15 2009 init.ora -rw-r----- 1 oracle oinstall 2560 Oct 15 01:39 orapworcl -rw-r--r-- 1 oracle oinstall 1636 Oct 15 01:47 initorcl.ora -rw-r----- 1 oracle oinstall 4608 Oct 15 01:52 spfileorcl.ora SQL> SQL> startup nomount; ORACLE instance started. Total System Global Area 417546240 bytes Fixed Size 2228944 bytes Variable Size 301993264 bytes Database Buffers 109051904 bytes Redo Buffers 4272128 bytes SQL> |
查看备库的数据目录的当前状态：
|
1 2 3 4 5 6 |
[oracle@dg2 orcl_standby]$ ll total 153780 -rw-r----- 1 oracle oinstall 52429312 Oct 15 02:37 redo01.log -rw-r----- 1 oracle oinstall 52429312 Oct 15 02:37 redo02.log -rw-r----- 1 oracle oinstall 52429312 Oct 15 02:37 redo03.log [oracle@dg2 orcl_standby]$ |
通过RMAN，做DUPLICATE
|
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 |
[oracle@dg2 dbs]$ tnsping orcl_primary TNS Ping Utility for Linux: Version 11.2.0.3.0 - Production on 15-OCT-2014 02:00:37 Copyright (c) 1997, 2011, Oracle. All rights reserved. Used parameter files: Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.223.129)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl))) OK (0 msec) [oracle@dg2 dbs]$ tnsping orcl_standby TNS Ping Utility for Linux: Version 11.2.0.3.0 - Production on 15-OCT-2014 02:00:42 Copyright (c) 1997, 2011, Oracle. All rights reserved. Used parameter files: Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.223.130)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl))) OK (0 msec) [oracle@dg2 dbs]$ [oracle@dg2 dbs]$ rman target sys/oracle@orcl_primary auxiliary sys/oracle@orcl_standby Recovery Manager: Release 11.2.0.3.0 - Production on Wed Oct 15 02:01:08 2014 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. connected to target database: ORCL (DBID=1388744098) connected to auxiliary database: ORCL (not mounted) RMAN> RMAN> duplicate target database for standby from active database; Starting Duplicate Db at 15-OCT-2014 02:28:07 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/orapworcl' auxiliary format '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapworcl' ; } executing Memory Script Starting backup at 15-OCT-2014 02:28:08 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=38 device type=DISK Finished backup at 15-OCT-2014 02:28:09 contents of Memory Script: { backup as copy current controlfile for standby auxiliary format '/u01/app/oracle/oradata/orcl/control01.ctl'; restore clone controlfile to '/u01/app/oracle/oradata/orcl/control02.ctl' from '/u01/app/oracle/oradata/orcl/control01.ctl'; } executing Memory Script Starting backup at 15-OCT-2014 02:28:09 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_orcl.f tag=TAG20141015T022809 RECID=4 STAMP=860984891 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07 Finished backup at 15-OCT-2014 02:28:16 Starting restore at 15-OCT-2014 02:28:16 using channel ORA_AUX_DISK_1 channel ORA_AUX_DISK_1: copied control file copy Finished restore at 15-OCT-2014 02:28:17 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/orcl_standby/temp01.dbf"; switch clone tempfile all; set newname for datafile 1 to "/u01/app/oracle/oradata/orcl_standby/system01.dbf"; set newname for datafile 2 to "/u01/app/oracle/oradata/orcl_standby/sysaux01.dbf"; set newname for datafile 3 to "/u01/app/oracle/oradata/orcl_standby/undotbs01.dbf"; set newname for datafile 4 to "/u01/app/oracle/oradata/orcl_standby/users01.dbf"; set newname for datafile 5 to "/u01/app/oracle/oradata/orcl_standby/example01.dbf"; backup as copy reuse datafile 1 auxiliary format "/u01/app/oracle/oradata/orcl_standby/system01.dbf" datafile 2 auxiliary format "/u01/app/oracle/oradata/orcl_standby/sysaux01.dbf" datafile 3 auxiliary format "/u01/app/oracle/oradata/orcl_standby/undotbs01.dbf" datafile 4 auxiliary format "/u01/app/oracle/oradata/orcl_standby/users01.dbf" datafile 5 auxiliary format "/u01/app/oracle/oradata/orcl_standby/example01.dbf" ; sql 'alter system archive log current'; } executing Memory Script executing command: SET NEWNAME renamed tempfile 1 to /u01/app/oracle/oradata/orcl_standby/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 15-OCT-2014 02:28:23 using channel ORA_DISK_1 channel ORA_DISK_1: starting datafile copy input datafile file number=00001 name=/u01/app/oracle/oradata/orcl/system01.dbf output file name=/u01/app/oracle/oradata/orcl_standby/system01.dbf tag=TAG20141015T022823 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:45 channel ORA_DISK_1: starting datafile copy input datafile file number=00002 name=/u01/app/oracle/oradata/orcl/sysaux01.dbf output file name=/u01/app/oracle/oradata/orcl_standby/sysaux01.dbf tag=TAG20141015T022823 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:46 channel ORA_DISK_1: starting datafile copy input datafile file number=00005 name=/u01/app/oracle/oradata/orcl/example01.dbf output file name=/u01/app/oracle/oradata/orcl_standby/example01.dbf tag=TAG20141015T022823 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:35 channel ORA_DISK_1: starting datafile copy input datafile file number=00003 name=/u01/app/oracle/oradata/orcl/undotbs01.dbf output file name=/u01/app/oracle/oradata/orcl_standby/undotbs01.dbf tag=TAG20141015T022823 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15 channel ORA_DISK_1: starting datafile copy input datafile file number=00004 name=/u01/app/oracle/oradata/orcl/users01.dbf output file name=/u01/app/oracle/oradata/orcl_standby/users01.dbf tag=TAG20141015T022823 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01 Finished backup at 15-OCT-2014 02:30:46 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=4 STAMP=860985048 file name=/u01/app/oracle/oradata/orcl_standby/system01.dbf datafile 2 switched to datafile copy input datafile copy RECID=5 STAMP=860985048 file name=/u01/app/oracle/oradata/orcl_standby/sysaux01.dbf datafile 3 switched to datafile copy input datafile copy RECID=6 STAMP=860985048 file name=/u01/app/oracle/oradata/orcl_standby/undotbs01.dbf datafile 4 switched to datafile copy input datafile copy RECID=7 STAMP=860985048 file name=/u01/app/oracle/oradata/orcl_standby/users01.dbf datafile 5 switched to datafile copy input datafile copy RECID=8 STAMP=860985048 file name=/u01/app/oracle/oradata/orcl_standby/example01.dbf Finished Duplicate Db at 15-OCT-2014 02:30:57 RMAN> |
在做DUPLICATE的时候，可以通过查看数据库实例的ALERT日志，确定数据库当前的状态：
tail -f /u01/app/oracle/diag/rdbms/orcl_standby/orcl/trace/alert_orcl.log
在DUPLICATE过程中后台日志输出信息如下：
|
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 |
Wed Oct 15 02:39:06 2014 RFS connections have been disallowed alter database mount standby database Set as converted control file due to db_unique_name mismatch Changing di2dbun from orcl_primary to orcl_standby ARCH: STARTING ARCH PROCESSES Wed Oct 15 02:39:10 2014 ARC0 started with pid=22, OS id=27849 ARC0: Archival started ARCH: STARTING ARCH PROCESSES COMPLETE ARC0: STARTING ARCH PROCESSES Wed Oct 15 02:39:11 2014 ARC1 started with pid=23, OS id=27851 Wed Oct 15 02:39:11 2014 Successful mount of redo thread 1, with mount id 1388733770 Physical Standby Database mounted. Lost write protection disabled Wed Oct 15 02:39:11 2014 ARC2 started with pid=24, OS id=27853 ARC1: Archival started ARC2: Archival started ARC1: Becoming the 'no FAL' ARCH ARC2: Becoming the heartbeat ARCH ARC2: Becoming the active heartbeat ARCH Wed Oct 15 02:39:11 2014 ARC3 started with pid=25, OS id=27855 Completed: alter database mount standby database Wed Oct 15 02:39:11 2014 Using STANDBY_ARCHIVE_DEST parameter default value as /u01/archivelog ARC3: Archival started ARC0: STARTING ARCH PROCESSES COMPLETE Wed Oct 15 02:41:30 2014 Switch of datafile 1 complete to datafile copy checkpoint is 1063531 Switch of datafile 2 complete to datafile copy checkpoint is 1063566 Switch of datafile 3 complete to datafile copy checkpoint is 1063631 Switch of datafile 4 complete to datafile copy checkpoint is 1063638 Switch of datafile 5 complete to datafile copy checkpoint is 1063601 alter database clear logfile group 1 Clearing online log 1 of thread 1 sequence number 19 Completed: alter database clear logfile group 1 alter database clear logfile group 2 Clearing online log 2 of thread 1 sequence number 17 Completed: alter database clear logfile group 2 alter database clear logfile group 3 Clearing online log 3 of thread 1 sequence number 18 Wed Oct 15 02:41:43 2014 Completed: alter database clear logfile group 3 RFS connections are allowed Wed Oct 15 02:41:45 2014 RFS[1]: Assigned to RFS process 29096 RFS[1]: Opened log for thread 1 sequence 20 dbid 1388744098 branch 860963365 Wed Oct 15 02:41:45 2014 RFS[2]: Assigned to RFS process 29099 RFS[2]: Opened log for thread 1 sequence 19 dbid 1388744098 branch 860963365 Wed Oct 15 02:41:50 2014 RFS[3]: Assigned to RFS process 29069 RFS[3]: Opened log for thread 1 sequence 21 dbid 1388744098 branch 860963365 Archived Log entry 1 added for thread 1 sequence 19 rlc 860963365 ID 0x52c73aa2 dest 2: Archived Log entry 2 added for thread 1 sequence 20 rlc 860963365 ID 0x52c73aa2 dest 2: Archived Log entry 3 added for thread 1 sequence 21 rlc 860963365 ID 0x52c73aa2 dest 2: Wed Oct 15 02:41:50 2014 Primary database is in MAXIMUM PERFORMANCE mode RFS[4]: Assigned to RFS process 29146 RFS[4]: No standby redo logfiles created RFS[4]: Opened log for thread 1 sequence 22 dbid 1388744098 branch 860963365 |
通过日志可以看到，当前Dataguard处于：最大性能模式。
备库的状态：
|
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 |
[oracle@dg2 orcl_standby]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.3.0 Production on Wed Oct 15 02:48:05 2014 Copyright (c) 1982, 2011, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> select instance_name,status from v$instance; INSTANCE_NAME STATUS ---------------- ------------ orcl MOUNTED SQL> archive log list; Database log mode Archive Mode Automatic archival Enabled Archive destination /u01/archivelog Oldest online log sequence 21 Next log sequence to archive 0 Current log sequence 22 SQL> SQL> exit Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options [oracle@dg2 orcl_standby]$ [oracle@dg2 orcl_standby]$ ll /u01/archivelog/ total 1108 -rw-r----- 1 oracle oinstall 30208 Oct 15 02:16 1_10_860963365.dbf -rw-r----- 1 oracle oinstall 125952 Oct 15 02:31 1_13_860963365.dbf -rw-r----- 1 oracle oinstall 59904 Oct 15 02:31 1_14_860963365.dbf -rw-r----- 1 oracle oinstall 14336 Oct 15 02:31 1_15_860963365.dbf -rw-r----- 1 oracle oinstall 90624 Oct 15 02:41 1_19_860963365.dbf -rw-r----- 1 oracle oinstall 54272 Oct 15 02:41 1_20_860963365.dbf -rw-r----- 1 oracle oinstall 10240 Oct 15 02:41 1_21_860963365.dbf -rw-r----- 1 oracle oinstall 52429312 Oct 15 02:47 1_22_860963365.dbf -rw-r----- 1 oracle oinstall 271872 Oct 15 02:16 1_8_860963365.dbf -rw-r----- 1 oracle oinstall 249856 Oct 15 02:16 1_9_860963365.dbf [oracle@dg2 orcl_standby]$ |
至此，Dataguard的初步配置已经完成。
————————————————————————————
Ending。
