【DG】搭建（二）及相關測試
搭建DG:
1. 準備工作
|
主備 |
虛擬機器名稱 |
主機名 |
IP地址 |
db_name |
db_unique_name |
備註 |
|
主庫 |
wxb |
wang |
192.168.10.2 |
ORA11GR2 |
ORA11GR2 |
主庫 |
|
備庫 |
www |
bing |
192.168.10.3 |
ORA11GR2 |
SBDB |
備庫，Oracle資料庫只安裝了軟體 |
[oracle@wang ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Mon Oct 24 10:21:52 2016
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
SYS@ORA11GR2>select flashback_on from v$database;
FLASHBACK_ON
------------------
NO
SYS@ORA11GR2>show parameter local_listener
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
local_listener string
SYS@ORA11GR2>
注:如果主庫有外部表，必須將外部表刪除；
2.主庫資料庫歸檔並強制生成日誌
SYS@ORA11GR2>archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 12
Next log sequence to archive 14
Current log sequence 14
SYS@ORA11GR2>show parameter recover
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string /u01/app/FRA
db_recovery_file_dest_size big integer 3G
db_unrecoverable_scn_tracking boolean TRUE
recovery_parallelism integer 0
SYS@ORA11GR2>
SYS@ORA11GR2>alter database force logging;
Database altered.
SYS@ORA11GR2>select force_logging from v$database;
FOR
---
YES
3.主庫建立standby logfile
SYS@ORA11GR2>select group#,member from v$logfile order by 1;
GROUP# MEMBER
--------------------------------------------------------------------------------
1 /u01/app/oracle/oradata/ORA11GR2/redo01.log
2 /u01/app/oracle/oradata/ORA11GR2/redo02.log
3 /u01/app/oracle/oradata/ORA11GR2/redo03.log
SYS@ORA11GR2>select group#,bytes/1024/1024 m from v$log;
GROUP# M
---------- ----------
1 50
2 50
3 50
SYS@ORA11GR2>alter database add standby logfile group 4 ('/u01/app/oracle/oradata/ORA11GR2/standby401.log','/u01/app/oracle/oradata/ORA11GR2/standby402.log') size 50m;
Database altered.
SYS@ORA11GR2>alter database add standby logfile group 5 ('/u01/app/oracle/oradata/ORA11GR2/standby501.log','/u01/app/oracle/oradata/ORA11GR2/standby502.log') size 50m;
Database altered.
SYS@ORA11GR2>alter database add standby logfile group 6 ('/u01/app/oracle/oradata/ORA11GR2/standby6501.log','/u01/app/oracle/oradata/ORA11GR2/standby602.log') size 50m;
Database altered.
SYS@ORA11GR2>alter database add standby logfile group 7('/u01/app/oracle/oradata/ORA11GR2/standby701.log','/u01/app/oracle/oradata/ORA11GR2/standby702.log') size 50m;
Database altered.
SYS@ORA11GR2>select group#,thread#,sequence#,bytes/1024/1024 m,status from v$standby_log;
GROUP# THREAD# SEQUENCE# M STATUS
---------- ---------- ---------- ---------- ----------
4 0 0 50 UNASSIGNED
5 0 0 50 UNASSIGNED
6 0 0 50 UNASSIGNED
7 0 0 50 UNASSIGNED
SYS@ORA11GR2>col member for a55
SYS@ORA11GR2>col type for a10
SYS@ORA11GR2>select group#,type,member from v$logfile order by 1;
GROUP# TYPE MEMBER
---------- ---------- -------------------------------------------------------
1 ONLINE /u01/app/oracle/oradata/ORA11GR2/redo01.log
2 ONLINE /u01/app/oracle/oradata/ORA11GR2/redo02.log
3 ONLINE /u01/app/oracle/oradata/ORA11GR2/redo03.log
4 STANDBY /u01/app/oracle/oradata/ORA11GR2/standby401.log
4 STANDBY /u01/app/oracle/oradata/ORA11GR2/standby402.log
5 STANDBY /u01/app/oracle/oradata/ORA11GR2/standby501.log
5 STANDBY /u01/app/oracle/oradata/ORA11GR2/standby502.log
6 STANDBY /u01/app/oracle/oradata/ORA11GR2/standby6501.log
6 STANDBY /u01/app/oracle/oradata/ORA11GR2/standby602.log
7 STANDBY /u01/app/oracle/oradata/ORA11GR2/standby701.log
7 STANDBY /u01/app/oracle/oradata/ORA11GR2/standby702.log
11 rows selected.
SYS@ORA11GR2>select group#,type,member from v$logfile order by 1;
GROUP# TYPE MEMBER
---------- ---------- -------------------------------------------------------
1 ONLINE /u01/app/oracle/oradata/ORA11GR2/redo01.log
2 ONLINE /u01/app/oracle/oradata/ORA11GR2/redo02.log
3 ONLINE /u01/app/oracle/oradata/ORA11GR2/redo03.log
4 STANDBY /u01/app/oracle/oradata/ORA11GR2/standby401.log
4 STANDBY /u01/app/oracle/oradata/ORA11GR2/standby402.log
5 STANDBY /u01/app/oracle/oradata/ORA11GR2/standby501.log
5 STANDBY /u01/app/oracle/oradata/ORA11GR2/standby502.log
6 STANDBY /u01/app/oracle/oradata/ORA11GR2/standby6501.log
6 STANDBY /u01/app/oracle/oradata/ORA11GR2/standby602.log
7 STANDBY /u01/app/oracle/oradata/ORA11GR2/standby701.log
7 STANDBY /u01/app/oracle/oradata/ORA11GR2/standby702.log
11 rows selected.
4. 主庫配置靜態監聽及作為客戶端的tnsnames
——主庫配置靜態監聽：
[oracle@wang ~]$ cd /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/
[oracle@wang admin]$ ls
listener.ora samples shrept.lst tnsnames.ora
[oracle@wang admin]$ vi 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 = wang)(PORT = 1521)))
)
sid_list_listener=
(sid_list=
(sid_desc=
(global_dbname=ORA11GR2)
(ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1)
(sid_name=ORA11GR2))
)
~
[oracle@wang admin]$ lsnrctl reload
LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 24-OCT-2016 10:36:58
Copyright (c) 1991, 2013, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=wang)(PORT=1521)))
The command completed successfully
[oracle@wang admin]$ lsnrctl status
LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 24-OCT-2016 10:37:06
Copyright (c) 1991, 2013, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=wang)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date 30-AUG-2016 20:38:58
Uptime 54 days 13 hr. 58 min. 9 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/wang/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=wang)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "ORA11GR2" has 2 instance(s).
Instance "ORA11GR2", status UNKNOWN, has 1 handler(s) for this service...
Instance "ORA11GR2", status READY, has 1 handler(s) for this service...
Service "ORA11GR2XDB" has 1 instance(s).
Instance "ORA11GR2", status READY, has 1 handler(s) for this service...
The command completed successfully
[oracle@wang admin]$
——主庫配置tns：
[oracle@wang admin]$ vi 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.
ORA11GR2 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = wang)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ORA11GR2)
)
)
SBDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.10.3)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = SBDB)
)
)
~
"tnsnames.ora" 20L, 507C written
[oracle@wang admin]$ tnsping sbdb
TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 24-OCT-2016 10:41:03
Copyright (c) 1997, 2013, Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.10.3)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = SBDB)))
OK (0 msec)
[oracle@wang admin]$
5.備庫配置靜態監聽及作為客戶端的tnsnames
——備庫配置靜態監聽：
[oracle@bing ~]$ echo $ORACLE_SID
PROD
[oracle@bing ~]$ export ORACLE_SID=SBDB
[oracle@bing ~]$ echo $ORACLE_SID
SBDB
[oracle@bing ~]$
[oracle@bing ~]$ cd /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/
[oracle@bing admin]$ ls
listener.ora samples shrept.lst tnsnames.ora
[oracle@bing admin]$ vi 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 = bing)(PORT = 1521))
)
)
ADR_BASE_LISTENER = /u01/app/oracle
sid_list_listener=
(sid_list=
(sid_desc=
(global_dbname=SBDB)
(ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1)
(sid_name=SBDB))
)
~
~
"listener.ora" 19L, 477C written
[oracle@bing admin]$ lsnrctl reload
LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 24-OCT-2016 10:49:14
Copyright (c) 1991, 2013, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=bing)(PORT=1521)))
The command completed successfully
[oracle@bing admin]$
[oracle@bing admin]$ lsnrctl status
LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 24-OCT-2016 10:49:24
Copyright (c) 1991, 2013, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=bing)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date 05-SEP-2016 16:44:16
Uptime 48 days 18 hr. 5 min. 8 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/bing/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=bing)(PORT=1521)))
Services Summary...
Service "SBDB" has 1 instance(s).
Instance "SBDB", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
[oracle@bing admin]$
——配置備庫的tns：
[oracle@bing admin]$ vi 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.
12 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.10.3)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = PROD)
)
)
ORA11GR2 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.10.2)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ORA11GR2)
)
)
~
"tnsnames.ora" 21L, 518C written
[oracle@bing admin]$ tnsping ora11gr2
TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 24-OCT-2016 10:52:04
Copyright (c) 1997, 2013, Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.10.2)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = ORA11GR2)))
OK (0 msec)
[oracle@bing admin]$
6.主庫修改引數增加DG相關的屬性引數：
——根據spfile建立pfile：
SYS@ORA11GR2>create pfile from spfile;
File created.
SYS@ORA11GR2>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@wang ~]$ cd $ORACLE_HOME/dbs
[oracle@wang dbs]$ ls
hc_ORA11GR2.dat initORA11GR2.ora orapwORA11GR2
init.ora lkORA11GR2 spfileORA11GR2.ora
——修改pfile，增加DG屬性引數：
[oracle@wang dbs]$ vi initORA11GR2.ora
ORA11GR2.__db_cache_size=373293056
ORA11GR2.__java_pool_size=4194304
ORA11GR2.__large_pool_size=8388608
ORA11GR2.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
ORA11GR2.__pga_aggregate_target=289406976
ORA11GR2.__sga_target=545259520
ORA11GR2.__shared_io_pool_size=0
ORA11GR2.__shared_pool_size=146800640
ORA11GR2.__streams_pool_size=4194304
*.audit_file_dest='/u01/app/oracle/admin/ORA11GR2/adump'
*.audit_trail='db'
*.compatible='11.2.0.4.0'
*.control_files='/u01/app/oracle/oradata/ORA11GR2/control01.ctl','/u01/app/oracle/oradata/ORA11GR2/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='ORA11GR2'
*.db_recovery_file_dest_size=3221225472
*.db_recovery_file_dest='/u01/app/FRA'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=ORA11GR2XDB)'
*.memory_target=833617920
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
DB_UNIQUE_NAME=ORA11GR2
LOG_ARCHIVE_CONFIG='DG_CONFIG=(ORA11GR2,SBDB)'
LOG_ARCHIVE_DEST_1=
'LOCATION=/u01/arch1/ORA11GR2/
VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
DB_UNIQUE_NAME=ORA11GR2'
LOG_ARCHIVE_DEST_2=
'SERVICE=SBDB ASYNC
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
DB_UNIQUE_NAME=SBDB'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
LOG_ARCHIVE_MAX_PROCESSES=30
FAL_SERVER=SBDB
DB_FILE_NAME_CONVERT='SBDB','ORA11GR2'
LOG_FILE_NAME_CONVERT='SBDB','ORA11GR2'
STANDBY_FILE_MANAGEMENT=AUTO
[oracle@wang dbs]$
——根據引數檔案建立不存在的目錄：
[oracle@wang dbs]$ mkdir -p /u01/arch1/ORA11GR2/
——透過pfile再重新生成spfile驗證已經修改的引數檔案：
[oracle@wang dbs]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Mon Oct 24 11:07:27 2016
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
SYS@ORA11GR2>shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@ORA11GR2>
SYS@ORA11GR2>create spfile from pfile;
File created.
SYS@ORA11GR2>startup
ORACLE instance started.
Total System Global Area 830930944 bytes
Fixed Size 2257800 bytes
Variable Size 503319672 bytes
Database Buffers 322961408 bytes
Redo Buffers 2392064 bytes
Database mounted.
Database opened.
SYS@ORA11GR2>
SYS@ORA11GR2>show parameter spfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /u01/app/oracle/product/11.2.0
/dbhome_1/dbs/spfileORA11GR2.ora
7. 複製相關檔案至備庫（即引數檔案、密碼檔案）
[oracle@wang dbs]$ pwd
/u01/app/oracle/product/11.2.0/dbhome_1/dbs
[oracle@wang dbs]$ ls
hc_ORA11GR2.dat initORA11GR2.ora orapwORA11GR2
init.ora lkORA11GR2 spfileORA11GR2.ora
[oracle@wang dbs]$ scp initORA11GR2.ora 192.168.10.3:$ORACLE_HOME/dbs/initSBDB.ora
The authenticity of host '192.168.10.3 (192.168.10.3)' can't be established.
RSA key fingerprint is 46:2d:74:b7:54:f9:5f:4b:13:7d:e0:0f:5a:a4:92:2f.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '192.168.10.3' (RSA) to the list of known hosts.
oracle@192.168.10.3's password:
initORA11GR2.ora 100% 1514 1.5KB/s 00:00
[oracle@wang dbs]$
[oracle@wang dbs]$ scp orapwORA11GR2 192.168.10.3:$ORACLE_HOME/dbs/orapwSBDB
oracle@192.168.10.3's password:
orapwORA11GR2 100% 1536 1.5KB/s 00:00
[oracle@wang dbs]$
8.備庫引數修改增加DG相關的屬性引數
[oracle@bing dbs]$ pwd
/u01/app/oracle/product/11.2.0/dbhome_1/dbs
[oracle@bing dbs]$ ls initSBDB.ora orapwSBDB
initSBDB.ora orapwSBDB
[oracle@bing dbs]$
——修改備庫引數檔案：(全域性修改，唯一不變的是db_name=ORA11GR2)
[oracle@bing dbs]$ cat initSBDB.ora
SBDB.__db_cache_size=373293056
SBDB.__java_pool_size=4194304
SBDB.__large_pool_size=8388608
SBDB.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
SBDB.__pga_aggregate_target=289406976
SBDB.__sga_target=545259520
SBDB.__shared_io_pool_size=0
SBDB.__shared_pool_size=146800640
SBDB.__streams_pool_size=4194304
*.audit_file_dest='/u01/app/oracle/admin/SBDB/adump'
*.audit_trail='db'
*.compatible='11.2.0.4.0'
*.control_files='/u01/app/oracle/oradata/SBDB/control01.ctl','/u01/app/oracle/oradata/SBDB/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='ORA11GR2'
*.db_recovery_file_dest_size=3221225472
*.db_recovery_file_dest='/u01/app/FRA'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=SBDBXDB)'
*.memory_target=833617920
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
DB_UNIQUE_NAME=SBDB
LOG_ARCHIVE_CONFIG='DG_CONFIG=(SBDB,ORA11GR2)'
LOG_ARCHIVE_DEST_1=
'LOCATION=/u01/arch1/SBDB/
VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
DB_UNIQUE_NAME=SBDB'
LOG_ARCHIVE_DEST_2=
'SERVICE=ORA11GR2 ASYNC
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
DB_UNIQUE_NAME=ORA11GR2'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
LOG_ARCHIVE_MAX_PROCESSES=30
FAL_SERVER=ORA11GR2
DB_FILE_NAME_CONVERT='ORA11GR2','SBDB'
LOG_FILE_NAME_CONVERT='ORA11GR2','SBDB'
STANDBY_FILE_MANAGEMENT=AUTO
[oracle@bing dbs]$
——根據引數檔案建立不存在的目錄：
[oracle@bing dbs]$ mkdir -p /u01/app/oracle/admin/SBDB/adump
[oracle@bing dbs]$ mkdir -p /u01/app/oracle/oradata/SBDB/
[oracle@bing dbs]$ mkdir -p /u01/app/FRA
[oracle@bing dbs]$ mkdir -p /u01/arch1/SBDB/
——備庫透過pfile生成spfile，並啟動到nomount下，驗證已經修改的引數檔案：
[oracle@bing dbs]$ echo $ORACLE_SID
SBDB
[oracle@bing dbs]$
[oracle@bing dbs]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Mon Oct 24 11:32:23 2016
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to an idle instance.
SQL> create spfile from pfile;
File created.
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 830930944 bytes
Fixed Size 2257800 bytes
Variable Size 503319672 bytes
Database Buffers 322961408 bytes
Redo Buffers 2392064 bytes
SQL>
SQL> show parameter spfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /u01/app/oracle/product/11.2.0
/dbhome_1/dbs/spfileSBDB.ora
9. 使用RMAN auxiliary恢復資料庫
主庫為open狀態，備庫為nomount，監聽為啟動狀態
[oracle@wang dbs]$ rman target / auxiliary sys/oracle@sbdb
Recovery Manager: Release 11.2.0.4.0 - Production on Mon Oct 24 11:34:57 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORA11GR2 (DBID=237843809)
connected to auxiliary database: ORA11GR2 (not mounted)
RMAN>
RMAN> duplicate target database for standby from active database;
Starting Duplicate Db at 24-OCT-16
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=20 device type=DISK
contents of Memory Script:
{
backup as copy reuse
targetfile '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwORA11GR2' auxiliary format
'/u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwSBDB' ;
}
executing Memory Script
Starting backup at 24-OCT-16
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=79 device type=DISK
Finished backup at 24-OCT-16
contents of Memory Script:
{
backup as copy current controlfile for standby auxiliary format '/u01/app/oracle/oradata/SBDB/control01.ctl';
restore clone controlfile to '/u01/app/oracle/oradata/SBDB/control02.ctl' from
'/u01/app/oracle/oradata/SBDB/control01.ctl';
}
executing Memory Script
Starting backup at 24-OCT-16
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_ORA11GR2.f tag=TAG20161024T113659 RECID=2 STAMP=926077019
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 24-OCT-16
Starting restore at 24-OCT-16
using channel ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: copied control file copy
Finished restore at 24-OCT-16
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/SBDB/temp01.dbf";
switch clone tempfile all;
set newname for datafile 1 to
"/u01/app/oracle/oradata/SBDB/system01.dbf";
set newname for datafile 2 to
"/u01/app/oracle/oradata/SBDB/sysaux01.dbf";
set newname for datafile 3 to
"/u01/app/oracle/oradata/SBDB/undotbs01.dbf";
set newname for datafile 4 to
"/u01/app/oracle/oradata/SBDB/users01.dbf";
set newname for datafile 5 to
"/u01/app/oracle/oradata/SBDB/example01.dbf";
backup as copy reuse
datafile 1 auxiliary format
"/u01/app/oracle/oradata/SBDB/system01.dbf" datafile
2 auxiliary format
"/u01/app/oracle/oradata/SBDB/sysaux01.dbf" datafile
3 auxiliary format
"/u01/app/oracle/oradata/SBDB/undotbs01.dbf" datafile
4 auxiliary format
"/u01/app/oracle/oradata/SBDB/users01.dbf" datafile
5 auxiliary format
"/u01/app/oracle/oradata/SBDB/example01.dbf" ;
sql 'alter system archive log current';
}
executing Memory Script
executing command: SET NEWNAME
renamed tempfile 1 to /u01/app/oracle/oradata/SBDB/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 24-OCT-16
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=/u01/app/oracle/oradata/ORA11GR2/system01.dbf
output file name=/u01/app/oracle/oradata/SBDB/system01.dbf tag=TAG20161024T113707
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=/u01/app/oracle/oradata/ORA11GR2/sysaux01.dbf
output file name=/u01/app/oracle/oradata/SBDB/sysaux01.dbf tag=TAG20161024T113707
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile file number=00005 name=/u01/app/oracle/oradata/ORA11GR2/example01.dbf
output file name=/u01/app/oracle/oradata/SBDB/example01.dbf tag=TAG20161024T113707
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=/u01/app/oracle/oradata/ORA11GR2/undotbs01.dbf
output file name=/u01/app/oracle/oradata/SBDB/undotbs01.dbf tag=TAG20161024T113707
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=/u01/app/oracle/oradata/ORA11GR2/users01.dbf
output file name=/u01/app/oracle/oradata/SBDB/users01.dbf tag=TAG20161024T113707
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:02
Finished backup at 24-OCT-16
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=926077069 file name=/u01/app/oracle/oradata/SBDB/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=3 STAMP=926077069 file name=/u01/app/oracle/oradata/SBDB/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=4 STAMP=926077069 file name=/u01/app/oracle/oradata/SBDB/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=5 STAMP=926077069 file name=/u01/app/oracle/oradata/SBDB/users01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=6 STAMP=926077069 file name=/u01/app/oracle/oradata/SBDB/example01.dbf
Finished Duplicate Db at 24-OCT-16
完成dg搭建！！！！！！！！！！！
——檢視主備狀態：
SYS@ORA11GR2>select name,database_role,switchover_status,open_mode from v$database;
NAME DATABASE_ROLE SWITCHOVER_STATUS OPEN_MODE
--------- ---------------- -------------------- --------------------
ORA11GR2 PRIMARY FAILED DESTINATION READ WRITE
SQL> select name,database_role,switchover_status,open_mode from v$database;
NAME DATABASE_ROLE SWITCHOVER_STATUS OPEN_MODE
--------- ---------------- -------------------- --------------------
ORA11GR2 PHYSICAL STANDBY SESSIONS ACTIVE MOUNTED
SQL> alter database open;
Database altered.
SQL> select name,database_role,switchover_status,open_mode from v$database;
NAME DATABASE_ROLE SWITCHOVER_STATUS OPEN_MODE
--------- ---------------- -------------------- --------------------
ORA11GR2 PHYSICAL STANDBY NOT ALLOWED READ ONLY
——備庫應用日誌：
SQL> recover managed standby database using current logfile disconnect from session;
Media recovery complete.
——過會再檢視主備狀態：
SYS@ORA11GR2>select name,database_role,switchover_status,open_mode from v$database;
NAME DATABASE_ROLE SWITCHOVER_STATUS OPEN_MODE
--------- ---------------- -------------------- --------------------
ORA11GR2 PRIMARY SESSIONS ACTIVE READ WRITE
SYS@ORA11GR2>select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
--------------
19
SQL> select name,database_role,switchover_status,open_mode from v$database;
NAME DATABASE_ROLE SWITCHOVER_STATUS OPEN_MODE
--------- ---------------- -------------------- --------------------
ORA11GR2 PHYSICAL STANDBY NOT ALLOWED READ ONLY WITH APPLY
SQL> select sequence#,applied from v$archived_log;
SEQUENCE# APPLIED
---------- ---------
17 YES
18 YES
19 IN-MEMORY
10.切換保護模式
10.1最大效能轉最大可用（預設dg搭建完是最大效能模式）
——確認主備資料庫狀態：
SYS@ORA11GR2>select name,protection_mode,database_role,switchover_status from v$database;
NAME PROTECTION_MODE DATABASE_ROLE SWITCHOVER_STATUS
--------- -------------------- ---------------- --------------------
ORA11GR2 MAXIMUM PERFORMANCE PRIMARY SESSIONS ACTIVE
SQL> select name,protection_mode,database_role,switchover_status from v$database;
NAME PROTECTION_MODE DATABASE_ROLE SWITCHOVER_STATUS
--------- -------------------- ---------------- --------------------
ORA11GR2 MAXIMUM PERFORMANCE PHYSICAL STANDBY NOT ALLOWED
——主庫重啟到mount下，修改引數：
SYS@ORA11GR2>shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@ORA11GR2>
SYS@ORA11GR2>startup mount;
ORACLE instance started.
Total System Global Area 830930944 bytes
Fixed Size 2257800 bytes
Variable Size 503319672 bytes
Database Buffers 322961408 bytes
Redo Buffers 2392064 bytes
Database mounted.
SYS@ORA11GR2>
SYS@ORA11GR2>alter system set LOG_ARCHIVE_DEST_2='SERVICE=SBDB LGWR SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=SBDB';
System altered.
——執行模式轉換操作：
SYS@ORA11GR2>alter database set standby database to maximize availability;
Database altered.
——開啟主庫驗證：
SYS@ORA11GR2>alter database open;
Database altered.
SYS@ORA11GR2>select name,protection_mode,database_role,switchover_status from v$database;
NAME PROTECTION_MODE DATABASE_ROLE SWITCHOVER_STATUS
--------- -------------------- ---------------- --------------------
ORA11GR2 MAXIMUM AVAILABILITY PRIMARY SESSIONS ACTIVE
——備庫確認:
SQL> select name,protection_mode,database_role,switchover_status from v$database;
NAME PROTECTION_MODE DATABASE_ROLE SWITCHOVER_STATUS
--------- -------------------- ---------------- --------------------
ORA11GR2 MAXIMUM AVAILABILITY PHYSICAL STANDBY NOT ALLOWED
——最好把備庫也改一下，以便在主備切換時，依舊是最大可用：
SQL> alter system set LOG_ARCHIVE_DEST_2='SERVICE=ORA11GR2 LGWR SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ORA11GR2';
System altered.
SQL>
10.2最大可用模式轉換為最大保護：
（因為最大可用模式和最大保護模式對應的引數都是LGWR和SYNC,所以不需要再改引數了）
——主庫操作：
SYS@ORA11GR2>alter database set standby database to maximize protection;
Database altered.
SYS@ORA11GR2>select name,protection_mode,database_role,switchover_status from v$database;
NAME PROTECTION_MODE DATABASE_ROLE SWITCHOVER_STATUS
--------- -------------------- ---------------- --------------------
ORA11GR2 MAXIMUM PROTECTION PRIMARY SESSIONS ACTIVE
——檢視備庫：
SQL> select name,protection_mode,database_role,switchover_status from v$database;
NAME PROTECTION_MODE DATABASE_ROLE SWITCHOVER_STATUS
--------- -------------------- ---------------- --------------------
ORA11GR2 MAXIMUM PROTECTION PHYSICAL STANDBY NOT ALLOWED
注: 備庫修改引數，與主庫相同，上面已經修改過了；
10.3最大保護轉最大效能：
（最大效能引數為LGWR、SYNC或者ASYNC或者ARCH、SYNC，因為上面已經改為LGWR、SYNC，所以不需要再設定了，直接轉換）
——主庫操作：
SYS@ORA11GR2>alter database set standby database to maximize performance;
Database altered.
SYS@ORA11GR2>select name,protection_mode,database_role,switchover_status from v$database;
NAME PROTECTION_MODE DATABASE_ROLE SWITCHOVER_STATUS
--------- -------------------- ---------------- --------------------
ORA11GR2 MAXIMUM PERFORMANCE PRIMARY SESSIONS ACTIVE
——檢視備庫狀態：
select name,protection_mode,database_role,switchover_status from v$database;
NAME PROTECTION_MODE DATABASE_ROLE SWITCHOVER_STATUS
--------- -------------------- ---------------- --------------------
ORA11GR2 MAXIMUM PERFORMANCE PHYSICAL STANDBY NOT ALLOWED
注：最好把備庫也改一下，以便在主備切換時，依舊是最大效能，上面引數已經修改過了。
11. DG備庫斷檔問題
1）前提備庫停止應用日誌：
SQL> recover managed standby database cancel;
Media recovery complete.
2）主庫操作：
SYS@ORA11GR2>alter system switch logfile;
System altered.
SYS@ORA11GR2>/
System altered.
SYS@ORA11GR2>select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
--------------
38
3）檢視備庫日誌應用情況：
（因為備庫停止了應用日誌，所以35 、36 、37 、 38 號日誌未應用）
SQL> select sequence#,applied from v$archived_log;
SEQUENCE# APPLIED
---------- ---------
17 YES
18 YES
19 YES
20 YES
21 YES
22 YES
23 YES
24 YES
25 YES
26 YES
27 YES
SEQUENCE# APPLIED
---------- ---------
28 YES
29 YES
30 YES
31 YES
32 YES
33 YES
34 YES
35 NO
36 NO
37 NO
38 NO
SEQUENCE# APPLIED
---------- ---------
39 NO
23 rows selected.
4）模擬故障：
——主庫操作：
[oracle@wang dbs]$ cd /u01/arch1/ORA11GR2/
[oracle@wang ORA11GR2]$ ls
1_15_921272292.arc 1_25_921272292.arc 1_35_921272292.arc
1_16_921272292.arc 1_26_921272292.arc 1_36_921272292.arc
1_17_921272292.arc 1_27_921272292.arc 1_37_921272292.arc
1_18_921272292.arc 1_28_921272292.arc 1_38_921272292.arc
1_19_921272292.arc 1_29_921272292.arc 1_39_921272292.arc
1_20_921272292.arc 1_30_921272292.arc 1_40_921272292.arc
1_21_921272292.arc 1_31_921272292.arc 1_41_921272292.arc
1_22_921272292.arc 1_32_921272292.arc 1_42_921272292.arc
1_23_921272292.arc 1_33_921272292.arc
1_24_921272292.arc 1_34_921272292.arc
[oracle@wang ORA11GR2]$ mv 1_35_921272292.arc 1_35_921272292.arc.bak
[oracle@wang ORA11GR2]$ ls
1_15_921272292.arc 1_25_921272292.arc 1_35_921272292.arc.bak
1_16_921272292.arc 1_26_921272292.arc 1_36_921272292.arc
1_17_921272292.arc 1_27_921272292.arc 1_37_921272292.arc
1_18_921272292.arc 1_28_921272292.arc 1_38_921272292.arc
1_19_921272292.arc 1_29_921272292.arc 1_39_921272292.arc
1_20_921272292.arc 1_30_921272292.arc 1_40_921272292.arc
1_21_921272292.arc 1_31_921272292.arc 1_41_921272292.arc
1_22_921272292.arc 1_32_921272292.arc 1_42_921272292.arc
1_23_921272292.arc 1_33_921272292.arc 1_43_921272292.arc
1_24_921272292.arc 1_34_921272292.arc
——備庫操作：
[oracle@bing ~]$ cd /u01/arch1/SBDB/
[oracle@bing SBDB]$ ls
1_17_921272292.arc 1_27_921272292.arc 1_37_921272292.arc
1_18_921272292.arc 1_28_921272292.arc 1_38_921272292.arc
1_19_921272292.arc 1_29_921272292.arc 1_39_921272292.arc
1_20_921272292.arc 1_30_921272292.arc 1_40_921272292.arc
1_21_921272292.arc 1_31_921272292.arc 1_41_921272292.arc
1_22_921272292.arc 1_32_921272292.arc 1_42_921272292.arc
1_23_921272292.arc 1_33_921272292.arc 1_43_921272292.arc
1_24_921272292.arc 1_34_921272292.arc 1_44_921272292.arc
1_25_921272292.arc 1_35_921272292.arc
1_26_921272292.arc 1_36_921272292.arc
[oracle@bing SBDB]$ mv 1_35_921272292.arc 1_35_921272292.arc.bak
[oracle@bing SBDB]$ ls
1_17_921272292.arc 1_27_921272292.arc 1_37_921272292.arc
1_18_921272292.arc 1_28_921272292.arc 1_38_921272292.arc
1_19_921272292.arc 1_29_921272292.arc 1_39_921272292.arc
1_20_921272292.arc 1_30_921272292.arc 1_40_921272292.arc
1_21_921272292.arc 1_31_921272292.arc 1_41_921272292.arc
1_22_921272292.arc 1_32_921272292.arc 1_42_921272292.arc
1_23_921272292.arc 1_33_921272292.arc 1_43_921272292.arc
1_24_921272292.arc 1_34_921272292.arc 1_44_921272292.arc
1_25_921272292.arc 1_35_921272292.arc.bak 1_45_921272292.arc
1_26_921272292.arc 1_36_921272292.arc
[oracle@bing SBDB]$
5）再次在備庫操作，即在備庫應用日誌，發生斷檔問題了：
SQL> recover managed standby database using current logfile disconnect from session;
Media recovery complete.
SQL>
SQL> select sequence#,applied from v$archived_log;
SEQUENCE# APPLIED
---------- ---------
17 YES
18 YES
19 YES
20 YES
21 YES
22 YES
23 YES
24 YES
25 YES
26 YES
27 YES
SEQUENCE# APPLIED
---------- ---------
28 YES
29 YES
30 YES
31 YES
32 YES
33 YES
34 YES
35 NO
36 NO
37 NO
38 NO
SEQUENCE# APPLIED
---------- ---------
39 NO
40 NO
41 NO
42 NO
43 NO
44 NO
45 NO
46 NO
47 NO
31 rows selected.
6）解決斷檔：（備庫操作）
[oracle@bing SBDB]$ pwd
/u01/arch1/SBDB
[oracle@bing SBDB]$ ls
1_17_921272292.arc 1_28_921272292.arc 1_39_921272292.arc
1_18_921272292.arc 1_29_921272292.arc 1_40_921272292.arc
1_19_921272292.arc 1_30_921272292.arc 1_41_921272292.arc
1_20_921272292.arc 1_31_921272292.arc 1_42_921272292.arc
1_21_921272292.arc 1_32_921272292.arc 1_43_921272292.arc
1_22_921272292.arc 1_33_921272292.arc 1_44_921272292.arc
1_23_921272292.arc 1_34_921272292.arc 1_45_921272292.arc
1_24_921272292.arc 1_35_921272292.arc.bak 1_46_921272292.arc
1_25_921272292.arc 1_36_921272292.arc 1_47_921272292.arc
1_26_921272292.arc 1_37_921272292.arc 1_48_921272292.arc
1_27_921272292.arc 1_38_921272292.arc 1_49_921272292.arc
[oracle@bing SBDB]$ mv 1_35_921272292.arc.bak 1_35_921272292.arc
[oracle@bing SBDB]$ ls
1_17_921272292.arc 1_28_921272292.arc 1_39_921272292.arc
1_18_921272292.arc 1_29_921272292.arc 1_40_921272292.arc
1_19_921272292.arc 1_30_921272292.arc 1_41_921272292.arc
1_20_921272292.arc 1_31_921272292.arc 1_42_921272292.arc
1_21_921272292.arc 1_32_921272292.arc 1_43_921272292.arc
1_22_921272292.arc 1_33_921272292.arc 1_44_921272292.arc
1_23_921272292.arc 1_34_921272292.arc 1_45_921272292.arc
1_24_921272292.arc 1_35_921272292.arc 1_46_921272292.arc
1_25_921272292.arc 1_36_921272292.arc 1_47_921272292.arc
1_26_921272292.arc 1_37_921272292.arc 1_48_921272292.arc
1_27_921272292.arc 1_38_921272292.arc 1_49_921272292.arc
7）.備庫操作重新註冊一下剛剛找回來的歸檔日誌：
SQL> alter database register physical logfile '/u01/arch1/SBDB/1_35_921272292.arc';
Database altered.
8）.再次在備庫重新應用日誌：（因為前面操作，應用日誌沒有關閉，過會再次檢視日誌應用狀況）
SQL> select sequence#,applied from v$archived_log;
SEQUENCE# APPLIED
---------- ---------
17 YES
18 YES
19 YES
20 YES
21 YES
22 YES
23 YES
24 YES
25 YES
26 YES
27 YES
SEQUENCE# APPLIED
---------- ---------
28 YES
29 YES
30 YES
31 YES
32 YES
33 YES
34 YES
35 YES
36 YES
37 YES
38 YES
SEQUENCE# APPLIED
---------- ---------
39 YES
40 YES
41 YES
42 YES
43 YES
44 YES
45 YES
46 YES
47 YES
48 YES
49 YES
SEQUENCE# APPLIED
---------- ---------
50 YES
51 YES
52 YES
35 YES
53 YES
54 YES
55 YES
56 IN-MEMORY
41 rows selected.
斷檔問題解決了！！！！！！！！！！！！！！！
12.Switchover主備切換操作
12.1（主切備，備切主）
1）主切備：
SYS@ORA11GR2>select database_role,switchover_status from v$database;
DATABASE_ROLE SWITCHOVER_STATUS
---------------- --------------------
PRIMARY SESSIONS ACTIVE
——執行切換操作（主切備後，後臺程式關閉了資料庫）
SYS@ORA11GR2>alter database commit to switchover to physical standby with session shutdown;
ERROR:
ORA-01034: ORACLE not available
Process ID: 6114
Session ID: 1 Serial number: 5
Database altered.
SYS@ORA11GR2>conn / as sysdba
Connected to an idle instance.
SYS@ORA11GR2>startup mount;
ORACLE instance started.
Total System Global Area 830930944 bytes
Fixed Size 2257800 bytes
Variable Size 503319672 bytes
Database Buffers 322961408 bytes
Redo Buffers 2392064 bytes
Database mounted.
——再次檢視狀態（需進行應用日誌操作）
SYS@ORA11GR2>select database_role,switchover_status from v$database;
DATABASE_ROLE SWITCHOVER_STATUS
---------------- --------------------
PHYSICAL STANDBY RECOVERY NEEDED
SYS@ORA11GR2>recover managed standby database using current logfile disconnect from session;
Media recovery complete.
SYS@ORA11GR2>
SYS@ORA11GR2>select database_role,switchover_status from v$database;
DATABASE_ROLE SWITCHOVER_STATUS
---------------- --------------------
PHYSICAL STANDBY TO PRIMARY
——啟庫：
SYS@ORA11GR2>alter database open;
alter database open
*
ERROR at line 1:
ORA-10456: cannot open standby database; media recovery session may be in
Progress
——關閉日誌應用：
SYS@ORA11GR2>recover managed standby database cancel;
Media recovery complete.
SYS@ORA11GR2>alter database open;
Database altered.
——檢視狀態：
SYS@ORA11GR2>select database_role,switchover_status,open_mode from v$database;
DATABASE_ROLE SWITCHOVER_STATUS OPEN_MODE
---------------- -------------------- --------------------
PHYSICAL STANDBY SESSIONS ACTIVE READ ONLY
2）備切主：（TO PRIMARY 狀態，為可以切換為主庫的正常狀態）
SQL> select database_role,switchover_status,open_mode from v$database;
DATABASE_ROLE SWITCHOVER_STATUS OPEN_MODE
---------------- -------------------- --------------------
PHYSICAL STANDBY TO PRIMARY READ ONLY WITH APPLY
——進行備切主操作：（備切主後，後臺程式直接關庫，再將庫啟動到mount下）
SQL> alter database commit to switchover to primary with session shutdown;
Database altered.
SQL> select database_role,switchover_status,open_mode from v$database;
DATABASE_ROLE SWITCHOVER_STATUS OPEN_MODE
---------------- -------------------- --------------------
PRIMARY NOT ALLOWED MOUNTED
——啟庫，再檢視資料庫狀態：
SQL> alter database open;
Database altered.
SQL> select database_role,switchover_status,open_mode from v$database;
DATABASE_ROLE SWITCHOVER_STATUS OPEN_MODE
---------------- -------------------- --------------------
PRIMARY SESSIONS ACTIVE READ WRITE
3）再次檢視轉換後的備庫狀態：
SYS@ORA11GR2>select database_role,switchover_status,open_mode from v$database;
DATABASE_ROLE SWITCHOVER_STATUS OPEN_MODE
---------------- -------------------- --------------------
PHYSICAL STANDBY RECOVERY NEEDED READ ONLY
——應用日誌：
SYS@ORA11GR2>recover managed standby database using current logfile disconnect from session;
Media recovery complete.
SYS@ORA11GR2>
SYS@ORA11GR2>select database_role,switchover_status,open_mode from v$database;
DATABASE_ROLE SWITCHOVER_STATUS OPEN_MODE
---------------- -------------------- --------------------
PHYSICAL STANDBY NOT ALLOWED READ ONLY WITH APPLY
12.2反切（備切主、主切備，即將切換後的備庫切換回主庫，切換後的主庫切換回備庫）
1）主庫操作：
SQL> select database_role,switchover_status,open_mode from v$database;
DATABASE_ROLE SWITCHOVER_STATUS OPEN_MODE
---------------- -------------------- --------------------
PRIMARY SESSIONS ACTIVE READ WRITE
——執行切換（後臺自動關庫）
SQL> alter database commit to switchover to physical standby with session shutdown;
Database altered.
SQL> conn / as sysdba
Connected to an idle instance.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 830930944 bytes
Fixed Size 2257800 bytes
Variable Size 503319672 bytes
Database Buffers 322961408 bytes
Redo Buffers 2392064 bytes
Database mounted.
——查詢狀態：（需應用日誌）
SQL> select database_role,switchover_status,open_mode from v$database;
DATABASE_ROLE SWITCHOVER_STATUS OPEN_MODE
---------------- -------------------- --------------------
PHYSICAL STANDBY RECOVERY NEEDED MOUNTED
——應用日誌：
SQL> recover managed standby database using current logfile disconnect from session;
Media recovery complete.
——再次檢視資料庫狀態：
SQL> select database_role,switchover_status,open_mode from v$database;
DATABASE_ROLE SWITCHOVER_STATUS OPEN_MODE
---------------- -------------------- --------------------
PHYSICAL STANDBY TO PRIMARY MOUNTED
——結束日誌應用，然後啟庫：
SQL> recover managed standby database cancel;
Media recovery complete.
SQL>
SQL> alter database open;
Database altered.
SQL> select database_role,switchover_status,open_mode from v$database;
DATABASE_ROLE SWITCHOVER_STATUS OPEN_MODE
---------------- -------------------- --------------------
PHYSICAL STANDBY TO PRIMARY READ ONLY
——再次應用日誌：
SQL> recover managed standby database using current logfile disconnect from session;
Media recovery complete.
SQL> select database_role,switchover_status,open_mode from v$database;
DATABASE_ROLE SWITCHOVER_STATUS OPEN_MODE
---------------- -------------------- --------------------
PHYSICAL STANDBY TO PRIMARY READ ONLY WITH APPLY
2）備庫操作：
SYS@ORA11GR2>select database_role,switchover_status,open_mode from v$database;
DATABASE_ROLE SWITCHOVER_STATUS OPEN_MODE
---------------- -------------------- --------------------
PHYSICAL STANDBY SESSIONS ACTIVE READ ONLY WITH APPLY
——切換操作（備切主後，後臺程式直接關庫，再將庫啟動到mount下）
SYS@ORA11GR2>alter database commit to switchover to primary with session shutdown;
Database altered.
SYS@ORA11GR2>select database_role,switchover_status,open_mode from v$database;
DATABASE_ROLE SWITCHOVER_STATUS OPEN_MODE
---------------- -------------------- --------------------
PRIMARY NOT ALLOWED MOUNTED
SYS@ORA11GR2>alter database open;
Database altered.
SYS@ORA11GR2>select database_role,switchover_status,open_mode from v$database;
DATABASE_ROLE SWITCHOVER_STATUS OPEN_MODE
---------------- -------------------- --------------------
PRIMARY SESSIONS ACTIVE READ WRITE
3）再次檢視切換後的備庫狀態：（not allowed為正常狀態，如果為to primary則為不正常狀態，因為已經有了主庫）
SQL> select database_role,switchover_status,open_mode from v$database;
DATABASE_ROLE SWITCHOVER_STATUS OPEN_MODE
---------------- -------------------- --------------------
PHYSICAL STANDBY NOT ALLOWED READ ONLY WITH APPLY
13. 開啟dg broker
前提：1.很重要，搭建dg broker 之前一定一定要使備庫日誌應用與主庫一致；
2.在搭建dg broker之前，一定一定要將主備庫的tnsnames.ora裡的連線字串改成與db_unique_name的名字一樣，同時修改引數檔案裡的log_archive_dest_2='service的值；
搭建dg broker 需在主備庫上都進行設定，步驟如下：
1. 在監聽檔案listener.ora中的靜態監聽內的global_dbname引數值加_DGMGRL。
2. Reload 監聽；
3. 查引數dg_broker_start,預設是false。修改：alter system set dg_broker_start = true;
1)配置主庫：
[oracle@wang ~]$ cd /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/
[oracle@wang admin]$ ls
listener.ora samples shrept.lst tnsnames.ora
[oracle@wang admin]$ vi 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 = wang)(PORT = 1521)))
)
sid_list_listener=
(sid_list=
(sid_desc=
(global_dbname=ORA11GR2_DGMGRL)
(ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1)
(sid_name=ORA11GR2))
)
~
"listener.ora" 19L, 460C written
[oracle@wang admin]$
[oracle@wang admin]$ lsnrctl reload
LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 24-OCT-2016 20:20:15
Copyright (c) 1991, 2013, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=wang)(PORT=1521)))
The command completed successfully
[oracle@wang admin]$
[oracle@wang admin]$ lsnrctl status
LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 24-OCT-2016 20:20:30
Copyright (c) 1991, 2013, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=wang)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date 30-AUG-2016 20:38:58
Uptime 54 days 23 hr. 41 min. 32 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/wang/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=wang)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "ORA11GR2" has 1 instance(s).
Instance "ORA11GR2", status READY, has 1 handler(s) for this service...
Service "ORA11GR2XDB" has 1 instance(s).
Instance "ORA11GR2", status READY, has 1 handler(s) for this service...
Service "ORA11GR2_DGMGRL" has 1 instance(s).
Instance "ORA11GR2", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
[oracle@wang admin]$
SYS@ORA11GR2>alter system set dg_broker_start=true;
System altered.
SYS@ORA11GR2>show parameter dg_broker_start
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
dg_broker_start boolean TRUE
2)配置備庫：
[oracle@bing ~]$ export ORACLE_SID=SBDB
[oracle@bing ~]$ echo $ORACLE_SID
SBDB
[oracle@bing ~]$ cd /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/
[oracle@bing admin]$ ls
listener.ora samples shrept.lst tnsnames.ora
[oracle@bing admin]$ vi 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 = bing)(PORT = 1521))
)
)
ADR_BASE_LISTENER = /u01/app/oracle
sid_list_listener=
(sid_list=
(sid_desc=
(global_dbname=SBDB_DGMGRL)
(ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1)
(sid_name=SBDB))
)
~
"listener.ora" 19L, 484C written
[oracle@bing admin]$ lsnrctl reload
LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 24-OCT-2016 20:26:16
Copyright (c) 1991, 2013, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=bing)(PORT=1521)))
The command completed successfully
[oracle@bing admin]$
[oracle@bing admin]$ lsnrctl status
LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 24-OCT-2016 20:26:26
Copyright (c) 1991, 2013, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=bing)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date 05-SEP-2016 16:44:16
Uptime 49 days 3 hr. 42 min. 10 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/bing/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=bing)(PORT=1521)))
Services Summary...
Service "SBDB" has 1 instance(s).
Instance "SBDB", status READY, has 1 handler(s) for this service...
Service "SBDBXDB" has 1 instance(s).
Instance "SBDB", status READY, has 1 handler(s) for this service...
Service "SBDB_DGMGRL" has 1 instance(s).
Instance "SBDB", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
[oracle@bing admin]$
SQL> alter system set dg_broker_start=true;
System altered.
SQL> show parameter dg_broker_start
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
dg_broker_start boolean TRUE
14. dg broker維護DG
1）配置dg broker:(在主庫操作)
[oracle@wang admin]$ dgmgrl
DGMGRL for Linux: Version 11.2.0.4.0 - 64bit Production
Copyright (c) 2000, 2009, Oracle. All rights reserved.
Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys/oracle
Connected.
DGMGRL> create configuration 'ORA11GR2' as primary database is 'ORA11GR2' connect identifier is ORA11GR2;
Configuration "ORA11GR2" created with primary database "ORA11GR2"
DGMGRL>
DGMGRL> show configuration;
Configuration - ORA11GR2
Protection Mode: MaxPerformance
Databases:
ORA11GR2 - Primary database
Fast-Start Failover: DISABLED
Configuration Status:
DISABLED
DGMGRL> add database 'SBDB' as connect identifier is SBDB;
Database "SBDB" added
DGMGRL>
DGMGRL> show configuration;
Configuration - ORA11GR2
Protection Mode: MaxPerformance
Databases:
ORA11GR2 - Primary database
SBDB - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
DISABLED
DGMGRL> show database verbose 'SBDB';
Database - SBDB
Role: PHYSICAL STANDBY
Intended State: OFFLINE
Transport Lag: (unknown)
Apply Lag: (unknown)
Apply Rate: (unknown)
Real Time Query: OFF
Instance(s):
SBDB
Properties:
DGConnectIdentifier = 'sbdb'
ObserverConnectIdentifier = ''
LogXptMode = 'SYNC'
DelayMins = '0'
Binding = 'OPTIONAL'
MaxFailure = '0'
MaxConnections = '1'
ReopenSecs = '300'
NetTimeout = '30'
RedoCompression = 'DISABLE'
LogShipping = 'ON'
PreferredApplyInstance = ''
ApplyInstanceTimeout = '0'
ApplyParallel = 'AUTO'
StandbyFileManagement = 'AUTO'
ArchiveLagTarget = '0'
LogArchiveMaxProcesses = '30'
LogArchiveMinSucceedDest = '1'
DbFileNameConvert = 'ORA11GR2, SBDB'
LogFileNameConvert = 'ORA11GR2, SBDB'
FastStartFailoverTarget = ''
InconsistentProperties = '(monitor)'
InconsistentLogXptProps = '(monitor)'
SendQEntries = '(monitor)'
LogXptStatus = '(monitor)'
RecvQEntries = '(monitor)'
ApplyLagThreshold = '0'
TransportLagThreshold = '0'
TransportDisconnectedThreshold = '30'
SidName = 'SBDB'
StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=bing)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=SBDB_DGMGRL)(INSTANCE_NAME=SBDB)(SERVER=DEDICATED)))'
StandbyArchiveLocation = '/u01/arch1/SBDB/'
AlternateLocation = ''
LogArchiveTrace = '0'
LogArchiveFormat = '%t_%s_%r.arc'
TopWaitEvents = '(monitor)'
Database Status:
DISABLED
DGMGRL> enable configuration;
Enabled.
DGMGRL> show configuration
Configuration - ORA11GR2
Protection Mode: MaxPerformance
Databases:
ORA11GR2 - Primary database
SBDB - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS
DGMGRL> enable database 'SBDB';
Enabled.
DGMGRL> show database 'SBDB';
Database - SBDB
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds (computed 1 second ago)
Apply Lag: 0 seconds (computed 1 second ago)
Apply Rate: 0 Byte/s
Real Time Query: ON
Instance(s):
SBDB
Database Status:
SUCCESS
2). fast start failover功能開啟:
主庫與備庫執行如下命令並並且在備庫開啟應用日誌程式：
——主庫：
SYS@ORA11GR2>alter system set undo_retention=3600;
System altered.
SYS@ORA11GR2>alter system set db_flashback_retention_target=4320;
System altered.
SYS@ORA11GR2>alter database flashback on;
Database altered.
當然，還需要快速恢復區大小及位置，本實驗先前已經設定好了快速恢復區
——備庫：
SQL> alter system set undo_retention=3600;
System altered.
SQL> alter system set db_flashback_retention_target=4320;
System altered.
SQL> alter database flashback on;
alter database flashback on
*
ERROR at line 1:
ORA-01153: an incompatible media recovery is active
SQL> recover managed standby database cancel;
Media recovery complete.
SQL> alter database flashback on;
Database altered.
SQL> recover managed standby database using current logfile disconnect from session;
Media recovery complete.
(當然，還需要快速恢復區大小及位置，本實驗先前已經設定好了快速恢復區)
——主庫在DGMGRL工具下操作：
DGMGRL> edit database 'ORA11GR2' set property faststartfailovertarget='SBDB';
Property "faststartfailovertarget" updated
DGMGRL>
DGMGRL> edit database 'SBDB' set property faststartfailovertarget='ORA11GR2';
Property "faststartfailovertarget" updated
DGMGRL>
DGMGRL> start observer;
Observer started
[oracle@wang ~]$ dgmgrl
DGMGRL for Linux: Version 11.2.0.4.0 - 64bit Production
Copyright (c) 2000, 2009, Oracle. All rights reserved.
Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys/oracle
Connected.
DGMGRL>
DGMGRL> enable fast_start failover;
Error: ORA-16651: requirements not met for enabling fast-start failover
Failed.
——檢視報警日誌資訊：
[oracle@wang trace]$tail -100f drcORA11GR2.log
=========================================================================
ENABLE FAST_START FAILOVER
ORA-16651, MaxPerformance FSFO requires LogXptMode=ASYNC for primary and target standby
ORA-16651, failed to enable FSFO
Command ENABLE FAST_START FAILOVER completed
=============================================================================
——解決：
DGMGRL> edit database 'SBDB' set property 'LogXptMode'='SYNC';
Property "LogXptMode" updated
DGMGRL> edit database 'ORA11GR2' set property 'LogXptMode'='SYNC';
Property "LogXptMode" updated
——轉換到最大可用模式：
DGMGRL> edit configuration set protection mode as maxavailability;
Succeeded.
DGMGRL> show configuration;
Configuration - ORA11GR2
Protection Mode: MaxAvailability
Databases:
ORA11GR2 - Primary database
SBDB - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS
——使fast_start failover功能生效：
DGMGRL> enable fast_start failover;
Enabled.
——驗證：
DGMGRL> show configuration;
Configuration - ORA11GR2
Protection Mode: MaxAvailability
Databases:
ORA11GR2 - Primary database
SBDB - (*) Physical standby database
Fast-Start Failover: ENABLED
Configuration Status:
SUCCESS
——驗證dg broker搭建成功：
SYS@ORA11GR2>col FS_FAILOVER_OBSERVER_HOST for a20
SYS@ORA11GR2>select fs_failover_observer_present,fs_failover_observer_host,fs_failover_threshold from v$database;
FS_FAIL FS_FAILOVER_OBSERVER FS_FAILOVER_THRESHOLD
------- -------------------- ---------------------
YES wang 30
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/31397003/viewspace-2126992/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- c# stream類相關測試續(二）C#
- Java異常及相關呼叫效能測試Java
- oracle10 data guard(dg)__主庫添刪表空間及資料檔案相關測試Oracle
- DG相關指令碼指令碼
- (原)發動機油指標及相關測試指標
- iOS 應用效能測試的相關方法、工具及技巧iOS
- 【測試】Android Studio 相關下載及引數Android
- rman duplicate搭建第二個 dg
- 壓力測試相關指標指標
- oracle鎖級別相關測試Oracle
- griffin環境搭建及功能測試
- [20170324]dg相關程式.txt
- 關於測試平臺的搭建 (我們要不要搭建測試平臺)
- DG搭建
- 軟體測試相關簡要記錄
- 半導體測試行業的相關術語行業
- 大資料測試 - 相關性評估大資料
- Python容器相關簡單效能測試Python
- Oracle DB 壓力測試相關效能指標及達到指標的方法Oracle指標
- 物理DG刪除歸檔測試
- DG學習筆記(12)_相關檢視筆記
- 軟體測試相關概念以及原則（一）
- 學習 java 做自動化測試相關Java
- Oracle SCN相關問題學習與測試Oracle
- 【DG】搭建（一）
- lustre檔案系統環境搭建及測試
- 自己做oracle試驗的相關總結之二Oracle
- oracle11g同義詞synonym建立及授權且刪除相關測試Oracle
- oracle例項、資料庫及相關資料庫狀態的理解和測試Oracle資料庫
- 軟體測試相關理論知識有哪些?
- 列表及相關操作
- 字典及相關操作
- 【轉】 Ubuntu samba伺服器搭建及測試--不錯UbuntuSamba伺服器
- Docker 搭建 PHP 環境及相關軟體入門到了解DockerPHP
- Docker搭建官方私有倉庫registry及相關配置（推薦registry）Docker
- DG搭建配置方案
- 進位制與二進位制及相關轉換
- thinkphp，onethink都沒有測試相關的內容PHP