dataguard安裝和配置(DG)
1、
配置主庫和備庫資訊
主庫：
作業系統：oracle liunx 5.6
主機名：SZSCPDB
ip地址：192.168.20.10
oracle_sid：szsc
db_unqiue_name：szscpdb
監聽名、埠：listener、1521
service_name:szscpdb
備庫：
作業系統：oracle liunx 5.6
主機名：SZSCSTB
ip地址：192.168.20.11
oracle_sid：szsc
db_unqiue_name：szscstb
監聽名、埠：listener、1521
service_name:szscstb
2、
啟動主庫的監聽：
status
LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 16-MAY-2013 03:42:01
Copyright (c) 1991, 2011, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=SZSCPDB)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.3.0 - Production
Start Date 15-MAY-2013 21:02:28
Uptime 0 days 6 hr. 39 min. 33 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/products/11.2.0/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/SZSCPDB/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=SZSCPDB)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "szscXDB" has 1 instance(s).
Instance "szsc", status READY, has 1 handler(s) for this service...
Service "szscpdb" has 2 instance(s).
Instance "szsc", status UNKNOWN, has 1 handler(s) for this service...
Instance "szsc", status READY, has 1 handler(s) for this service...
The command completed successfully
3、
檢視資料庫是否歸檔:
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /RECO/arch/szsc/
Oldest online log sequence 3
Next log sequence to archive 5
Current log sequence 5
4、
更改主庫為force logging
SQL> alter database force logging;
Database altered.
5、
建立主庫的密碼檔案
cd /u01/app/oracle/products/11.2.0/dbs/
orapwd file=$ORACLE_HOME/dbs/orapwszsc password=oracle entries=30;
6、
修改主庫監聽及tns檔案
# listener.ora Network Configuration File: /u01/app/oracle/products/11.2.0/network/admin/listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = szscpdb)
(ORACLE_HOME = /u01/app/oracle/products/11.2.0)
(SID_NAME = szsc)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = SZSCPDB)(PORT = 1521))
)
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
ADR_BASE_LISTENER = /u01/app/oracle
Vi tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/products/11.2.0/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
SZSCPDB =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = SZSCPDB)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = szscpdb)
)
)
SZSCSTB =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = SZSCSTB)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = szscstb)
)
)
7、
修改主庫的引數檔案
*.audit_file_dest='/u01/app/oracle/admin/szscpdb/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/u01/app/oracle/oradata/szscpdb/control01.ctl','/u01/app/oracle/oradata/szscpdb/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='szscpdb'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=szscXDB)'
*.log_archive_config='dg_config=(szscpdb,szscstb)'
*.log_archive_dest_1='LOCATION=/RECO/arch/szsc/ LGWR VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=szscpdb'
*.log_archive_dest_2='SERVICE=szscstb ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=szscstb'
DB_FILE_NAME_CONVERT='/u01/app/oracle/oradata/szscstb/','/u01/app/oracle/oradata/szscpdb/'
LOG_FILE_NAME_CONVERT='/u01/app/oracle/oradata/szscstb/','/u01/app/oracle/oradata/szscpdb/'
*.log_archive_format='%t_%s_%r.arc'
*.memory_target=422576128
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
*.standby_file_management='AUTO'
*.log_archive_dest_state_1=enable
*.log_archive_dest_state_2=enable
fal_server=szscstb
fal_client=szscpdb
db_unique_name=szscpdb
8、
傳輸引數檔案，密碼檔案,tns檔案到備庫
cd /u01/app/oracle/products/11.2.0/dbs
scp initszsc.ora orapwszsc SZSCSTB:$ORACLE_HOME/dbs
scp orapwszsc SZSCSTB:/u01/app/oracle/products/11.2.0/dbs
cd /u01/app/oracle/products/11.2.0/network/admin/
scp tnsnames.ora SZSCSTB:$ORACLE_HOME/network/admin
9、
修改備庫的引數檔案
*.audit_file_dest='/u01/app/oracle/admin/szscstb/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/u01/app/oracle/oradata/szscstb/control01.ctl','/u01/app/oracle/oradata/szscstb/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='szscpdb'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=szscXDB)'
*.log_archive_config='dg_config=(szscpdb,szscstb)'
*.log_archive_dest_1='LOCATION=/RECO/arch/szsc/ LGWR VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=szscstb'
*.log_archive_dest_2='SERVICE=szscpdb ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=szscpdb'
DB_FILE_NAME_CONVERT='/u01/app/oracle/oradata/szscpdb/','/u01/app/oracle/oradata/szscstb/'
LOG_FILE_NAME_CONVERT='/u01/app/oracle/oradata/szscpdb/','/u01/app/oracle/oradata/szscstb/'
*.log_archive_format='%t_%s_%r.arc'
*.memory_target=422576128
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
*.standby_file_management='AUTO'
*.log_archive_dest_state_1=enable
*.log_archive_dest_state_2=enable
fal_server=szscpdb
fal_client=szscstb
db_unique_name=szscstb
10、
建立引數檔案相關的目錄
audit_file_dest
mkdir -p /u01/app/oracle/admin/szscstb/adump/
control_files
Mkdir -p '/u01/app/oracle/oradata/szscstb
11、
做一個主庫的全備
mkdir -p /u01/BACKUP/
chown -R oracle:oinstall /u01/BACKUP/
su - oracle
rman target /
RMAN>backup device type disk format '/u01/BACKUP/%U' database plus archivelog;
12、
傳輸備份到備庫
scp u01/backup/* SZSCSTB:/u01/BACKUP/
13、
備庫啟動到nomount
su - oracle
sqlplus / as sysdba
SQL> startup nomount;
14、
主庫 duplicate target database for standby;
在主庫上執行
su - oracle
rman target /
Recovery Manager: Release 11.2.0.3.0 - Production on Fri Mar 8 02:41:18 2013
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: SZSCPDB (DBID=850224774)
RMAN> connect auxiliary
connected to auxiliary database: SZSCPDB (not mounted)
RMAN> duplicate target database for standby;
Starting Duplicate Db at 08-MAR-13
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=10 device type=DISK
contents of Memory Script.:
{
restore clone standby controlfile;
}
executing Memory Script
Starting restore at 08-MAR-13
using channel ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: reading from backup piece /BACKUP/0lo3vm4i_1_1
channel ORA_AUX_DISK_1: piece handle=/u01/BACKUP/0lo3vm4i_1_1 tag=TAG20130308T022752
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:02
output file name=/u01/app/oracle/oradata/szscstb/control01.ctl
output file name=/u01/app/oracle/oradata/szscstb/control02.ctl
Finished restore at 08-MAR-13
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/szscstb/temp01.dbf";
switch clone tempfile all;
set newname for datafile 1 to
"/u01/app/oracle/oradata/szscstb/system01.dbf";
set newname for datafile 2 to
"/u01/app/oracle/oradata/szscstb/sysaux01.dbf";
set newname for datafile 3 to
"/u01/app/oracle/oradata/szscstb/undotbs01.dbf";
set newname for datafile 4 to
"/u01/app/oracle/oradata/szscstb/users01.dbf";
restore
clone database
;
}
executing Memory Script
executing command: SET NEWNAME
renamed tempfile 1 to /u01/app/oracle/oradata/szscstb/temp01.dbf in control file
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting restore at 08-MAR-13
using channel ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00001 to //u01/app/oracle/oradata/szscstb/system01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00002 to /u01/app/oracle/oradata/szscstb/sysaux01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/szscstb/undotbs01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/szscstb/users01.dbf
channel ORA_AUX_DISK_1: reading from backup piece /u01/BACKUP/0ko3vm18_1_1
channel ORA_AUX_DISK_1: piece handle=/u01/BACKUP/0ko3vm18_1_1 tag=TAG20130308T022752
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:02:05
Finished restore at 08-MAR-13
contents of Memory Script.:
{
switch clone datafile all;
}
executing Memory Script
datafile 1 switched to datafile copy
input datafile copy RECID=2 STAMP=809491977 file name=/u01/app/oracle/oradata/szscstb/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=3 STAMP=809491977 file name=/u01/app/oracle/oradata/szscstb/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=4 STAMP=809491977 file name=/u01/app/oracle/oradata/szscstb/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=5 STAMP=809491977 file name=/u01/app/oracle/oradata/szscstb/users01.dbf
Finished Duplicate Db at 08-MAR-13
15、
執行備庫恢復模式
SQL> select instance_name,status from v$instance;
INSTANCE_NAME STATUS
---------------- ------------
szsc MOUNTED
SQL> alter database recover managed standby database disconnect from session;
Database altered.
16、
檢視日誌同步情況
SQL> SELECT SEQUENCE#, REGISTRAR, FIRST_TIME, NEXT_TIME, APPLIED
FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
17、
建立STANDBY LOGFILE
主庫：
ALTER DATABASE ADD STANDBY LOGFILE '/u01/app/oracle/oradata/szscpdb/sredo01.log' size 512M;
ALTER DATABASE ADD STANDBY LOGFILE '/u01/app/oracle/oradata/szscpdb/sredo02.log' size 512M;
ALTER DATABASE ADD STANDBY LOGFILE '/u01/app/oracle/oradata/szscpdb/sredo03.log' size 512M;
ALTER DATABASE ADD STANDBY LOGFILE '/u01/app/oracle/oradata/szscpdb/sredo04.log' size 512M;
備庫：
ALTER DATABASE ADD STANDBY LOGFILE '/u01/app/oracle/oradata/szscstb/sredo01.log' size 512M;
ALTER DATABASE ADD STANDBY LOGFILE '/u01/app/oracle/oradata/szscstb/sredo02.log' size 512M;
ALTER DATABASE ADD STANDBY LOGFILE '/u01/app/oracle/oradata/szscstb/sredo03.log' size 512M;
ALTER DATABASE ADD STANDBY LOGFILE '/u01/app/oracle/oradata/szscstb/sredo04.log' size 512M;
當做完上面的新增standby logfile的時候就可以執行下面的（備庫上執行）
alter database recover managed standby database disconnect from session;
現在DG就已經配置完成了，可以檢視兩邊的資料庫情況。
switchover：
1、查詢主庫的switchover狀態
SQL>SELECT SWITCHOVER_STATUS FROM V$DATABASE;
SWITCHOVER_STATUS
--------------------
TO STANDBY
注意：當是to standby或session active的時候可以切換
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY ；
2、關閉szscpdb也就是舊的主庫，啟動到mount狀態
SQL> SHUTDOWN ABORT;
SQL> STARTUP MOUNT;
3、在備庫上執行
alter database recover managed standby database disconnect from session;
然後在執行
SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;
SWITCHOVER_STATUS
--------------------
TO PRIMARY
當查詢的值為TO PRIMARY的時候可以切換
4、在原來的備庫上執行(szscstb)
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY [WITH SESSION SHUTDOWN];
注意：WITH SESSION SHUTDOWN子句可以省略，當之前查詢的結果為TO PRIMARYAD的時候
5、開啟新的主庫也就是原來的備庫（szscstb）
alter database open;
select switchover_status from v$database;
這時候出現的結果應該是to standby
6、現在你在原來的主庫(szscpdb)也就是現在的備庫上查詢
SQL> select DATABASE_ROLE from v$database;
DATABASE_ROLE
----------------
PHYSICAL STANDBY
注意：出現這種情況就代表你切換成功了，就是原來的主庫變成了物理備庫，原來的備庫變成了主庫
7、現在驗證切換成功，看看你新的主庫的日誌是不是能傳到備庫上
insert into t values(2);
commit;
alter system switch logfile; （注意監測兩邊的alert日誌）
現在：
在新的主庫(原來的備庫)上看歸檔日誌序列
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /RECO/arch/szsc/
Oldest online log sequence 56
Next log sequence to archive 58
Current log sequence 58
在新的備庫上(原來的主庫上)看
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /RECO/arch/szsc/
Oldest online log sequence 56
Next log sequence to archive 0
Current log sequence 58
注意：現在已經證明了新主庫上的日誌已經可以傳到新的備庫上了
8、在新的備庫上應用你傳過來的日誌
SQL> alter database recover managed standby database disconnect from session;
Database altered.
或者
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;
9、檢視錶資料
SQL> select * from t;
ID
----------
1
2
成功完成了主備庫的switchover
10、檢視主備庫的角色資訊
在新主庫上：
SQL> select database_role from v$database;
DATABASE_ROLE
----------------
PRIMARY
在新的備庫上：
SQL> select database_role from v$database;
DATABASE_ROLE
----------------
PHYSICAL STANDBY
如果備庫還有沒有應用的日誌，那麼就執行(這句話如果執行了就不用在執行)
alter database recover managed standby database disconnect from session;
檢視備庫的alert日誌檔案，會應用你的歸檔，如果發現它停在某個歸檔上，那麼就是缺失這個歸檔，
這時候你需要將你主庫的歸檔路徑下的歸檔日誌傳到備庫上，備庫會自動應用的
當你的歸檔全部應用完成之後，就可以執行
alter database recover managed standby database disconnect from session;
或者執行：
alter database recover managed standby database using current logfile disconnect from session;
alter database recover managed standby database cancel;
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29107230/viewspace-769012/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 【DG】Oracle11g異構平臺之Linux To Windows DataGuard安裝配置--duplicateOracleLinuxWindows
- Oracle物理DG自動切換——Dataguard Broker配置Oracle
- Dataguard 物理安裝
- DG_安裝三
- DG_安裝二
- DG_安裝一
- zabbix安裝—–nginx安裝和配置Nginx
- oracle 10g dataguard 安裝配置說明及原理Oracle 10g
- 安裝和配置Drupal 8教程，如何安裝和配置Drupal 8？
- 安裝和配置sendmailAI
- Zookeeper 安裝和配置
- 安裝和配置OCFS
- ORACLE資料庫Dataguard dg brokerOracle資料庫
- Flutter的安裝和配置Flutter
- (MAC) PHP安裝和配置MacPHP
- mac MYSQL安裝和配置MacMySql
- Jemeter-安裝和配置
- Oracel bbed安裝和配置
- JDK的安裝和配置JDK
- Git server安裝和配置GitServer
- rsync的安裝 和 配置
- 安裝和配置OPENLDAP(轉)LDA
- 【DataGuard】同一臺主機實現物理Data Guard配置安裝
- 【DATAGUARD】物理dg配置客戶端無縫切換 (八.1)--Data Guard Broker 的配置客戶端
- 【DATAGUARD】物理dg配置客戶端無縫切換 (八.3)--客戶端TAF 配置客戶端
- 【DATAGUARD】物理dg的switchover切換（五）
- Gin 框架 - 安裝和路由配置框架路由
- Java安裝和環境配置Java
- nvm-windows安裝和配置Windows
- vue測試安裝和配置Vue
- Jetty的安裝、配置和使用Jetty
- postfix安裝和簡單配置
- Mac 安裝和配置gradleMacGradle
- Weblogic和eclipse安裝和配置WebEclipse
- 【DATAGUARD】物理dg配置客戶端無縫切換 (八.2)--Fast-Start Failover 的配置客戶端ASTAI
- 11. Oracle for Linux安裝和配置—11.3. Oracle安裝和配置—11.3.1. Oracle軟體安裝OracleLinux
- 11. Oracle for Linux安裝和配置—11.3. Oracle安裝和配置—11.3.3. Oracle net配置OracleLinux
- 【DG】 DataGuard 中處理archive gap的方法Hive