ORACLE資料庫Dataguard dg broker
ORACLE資料庫Dataguard dg broker
主機名：
host:ocm1 ip:192.168.88.101 sid=pmdb db_name=pmdb db_unique_name=pmdb
host:ocm2 ip:192.168.88.102 sid=pmdbdg db_name=pmdb db_unique_name=pmdbdg
maximum performance mode
oracle dg 主備庫的db_name(sid)是一樣的，db_unique_name不一樣
主庫的操作：
1.sql>alter database force logging;
2.sql>select * from v$log;
3.sql>select member from v$logfile;
4.
sql>ALTER DATABASE ADD STANDBY LOGFILE GROUP 4
('/u01/oracle/oradata/PMDB/redo04.log') SIZE 50m;
sql>ALTER DATABASE ADD STANDBY LOGFILE GROUP 5
('/u01/oracle/oradata/PMDB/redo05.log') SIZE 50m;
sql>ALTER DATABASE ADD STANDBY LOGFILE GROUP 6
('/u01/oracle/oradata/PMDB/redo06.log') SIZE 50m;
配置引數檔案：
DB_UNIQUE_NAME=pmdb
LOG_ARCHIVE_CONFIG='DG_CONFIG=(pmdb,pmdbdg)'
LOG_ARCHIVE_DEST_1='LOCATION=/home/oracle/arch/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=pmdb'
LOG_ARCHIVE_DEST_2='SERVICE=pmdbdg LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=pmdbdg'
FAL_SERVER=pmdbdg
FAL_CLIENT=pmdb
DB_FILE_NAME_CONVERT='/u01/oracle/oradata/pmdbdg/','/u01/oracle/oradata/pmdb/'
LOG_FILE_NAME_CONVERT= '/u01/oracle/oradata/pmdbdg/','/u01/oracle/oradata/pmdb/'
STANDBY_FILE_MANAGEMENT=AUTO
sql>shutdown immediate
cd $ORACLE_BASE/oradata
tar -vcf pmdb.tar pmdb
gzip pmdb.tar
主庫：scp pmdb.tar.gz ocm2:/u01/oralce/oradata->備庫：cd /u01/oracle/oradata/ tar -vxzf pmdb.tar.gz
sql>startup mount
sql>alter database create standby controlfile as '/tmp/control.stdby';
scp /tmp/control.stdby ocm2:/u01/oracle/oradata/pmdbdg
cd $ORACLE_HOME/dbs
scp initpmdb.ora ocm2:/tmp
配置主庫的tns:
cd $ORACLE_HOME/network/admin
vim tnsnames.ora
vim tnsnames.ora
新增如下程式碼：
PMDBDG =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = ocm2)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = pmdbdg)
)
)
tail -f alter_pmdb.log
備庫
cd $ORACLE_HOME/dbs
orapwd file=orapwpmdbdg password=oracle entries=5 force=y
cd $ORACLE_BASE/oradata/pmdbdg
rm *.ctl
mv control.stdby control01.ctl
cp /tmp/initpmdb.ora $ORACLE_HOME/dbs
cd $ORACLE_HOME/dbs/
mv initpmdb.ora initpmdbdg.ora
vim initpmdbdg.ora
DB_UNIQUE_NAME=pmdbdg
LOG_ARCHIVE_CONFIG='DG_CONFIG=(pmdb,pmdbdg)'
LOG_ARCHIVE_DEST_1='LOCATION=/home/oracle/arch/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=pmdbdg'
LOG_ARCHIVE_DEST_2='SERVICE=pmdb LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=pmdb'
FAL_SERVER=pmdb
FAL_CLIENT=pmdbdg
DB_FILE_NAME_CONVERT='/u01/oracle/oradata/pmdb/','/u01/oracle/oradata/pmdbdg/'
LOG_FILE_NAME_CONVERT= '/u01/oracle/oradata/pmdb/','/u01/oracle/oradata/pmdbdg/'
STANDBY_FILE_MANAGEMENT=AUTO
mkdir -p /u01/oracle/admin/pmdbdg/udump
mkdir -p /u01/oracle/admin/pmdbdg/cdump
mkdir -p /u01/oracle/admin/pmdbdg/bdump
mkdir -p /u01/oracle/admin/pmdbdg/adump
注意修改initpmdbdg.ora檔案中的control檔案路徑。
配置備庫的tns:
cd $ORACLE_HOME/network/admin
vim tnsnames.ora
新增如下程式碼：
PMDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = ocm1)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = pmdb)
)
)
測試連通性：
sqlplus 'sys/oracle1@pmdb as sysdba'
啟動例項：
export ORACLE_SID=pmdbdg
sqlplus '/as sysdba'
sql>startup mount
啟動監聽程式：
lsnrctl start
lsnrctl status
show parameters local
show parameters dump
tail -f alter_pmdbdg.log
啟動順序：
先備庫：
此時備庫之前已經是mount狀態：
執行sql>alter database recover managed standby database disconnect from session;
然後檢視後臺alter日誌。
日誌中會顯示Clearing online redo logfile;
media recovery waiting for thread 1 sequence 32
再主庫：
sql>alter database open;
然後檢視日誌：
日誌中會顯示：
lgwr: setting 'active' archival for destination log_archive_dest_2
此時檢視備庫日誌：
日誌中會顯示：
primary database is in maximum performance mode
media recovery log /home/oracle/arch/1_32_.log
在主庫嘗試切換幾組日誌：
sql>alter system switch logfile;
sql>alter system switch logfile;
sql>alter system switch logfile;
然後到備庫中檢視日誌：
media recovery log
再主庫嘗試建立表空間看備庫是否也有相應表空間：
sql>create tablespace test datafile '/u01/app/oracle/oradata/prod/disk2/test01.dbf' size 10m;
sql>alter system switch logfile(切換完日誌備庫中才會傳過去剛建立的表空間)。
角色切換(switchover)：
主庫：
sql>select switchover_status from v$database;
switchover_status
------------------------
SESSIONS_ACTIVE(不會顯示to standby狀態)
sql> alter database commit to switchover to physical standby with session shutdown;
檢視後臺alter 日誌
然後檢視備庫後臺alter日誌：
日誌中會有End-of-REDO標識。（主庫和備庫都會有End-of-REDO標識）
備庫：
sql>alter database commit to switchover to primary;
此時原來的主庫變成備庫，備庫變成主庫。
啟動的時候先備庫再主庫：
備庫（原來的主庫）：
sql>shutdown
sql>startup mount
sql>alter database recover managed standby database disconnect from session;
主庫（原來的備庫）：
sql>alter database open;
然後再切換回原來的角色：
主庫（原來的備庫）
sql> alter database commit to switchover to physical standby with session shutdown;
備庫（原來的主庫）
sql> alter database commit to switchover to primary;
sql>alter database open;
主庫(原來的備庫)
sql>shutdown
sql>startup
升級到最大保護模式：
主庫：
sql>shutdown immediate
備庫：
sql>shutdown immediate
主庫：
修改主庫pfile檔案：
原來：
LOG_ARCHIVE_DEST_2='SERVICE=pmdbdg LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=pmdbdg'
修改為：
LOG_ARCHIVE_DEST_2='SERVICE=pmdbdg LGWR SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=pmdbdg'
備庫：
修改備庫pfile檔案：
原來：
LOG_ARCHIVE_DEST_2='SERVICE=pmdb LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=pmdb'
修改為：
LOG_ARCHIVE_DEST_2='SERVICE=pmdb LGWR SYNC affirm VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=pmdb'
升級到最大保護模式（需要完全同步）
1.先做一次主備庫切換
先主庫：
sql>alter database commit to switchover to physical standby with session shutdown;
再備庫：
sql>alter database commit to switchover to primary;
然後在原來的主庫上：
sql>shutdown
sql>startup mount
sql>alter database recover managed standby database disconnect from session;
然後在原來的備庫庫上：
sql>alter database set standby database to maximize protection;
sql>alter database open;
在原來的備庫上建立測試表：
SQL> create table test_lzq(id number,name varchar2(20));
Table created.
SQL> insert into test_lzq values(1,'lzq');
1 row created.
SQL> insert into test_lzq values(1,'lzq');
1 row created.
SQL> insert into test_lzq values(1,'lzq');
1 row created.
SQL> insert into test_lzq values(1,'lzq');
1 row created.
SQL> insert into test_lzq values(1,'lzq');
1 row created.
SQL> commit;
Commit complete.
SQL> select count(*) from test_lzq;
COUNT(*)
----------
5
sql>shutdown immediate
再在原來的主庫上：
sql>shutdown immediate
sql>startup
SQL> select count(*) from test_lzq;
COUNT(*)
----------
5
最後變回原來的模式：
先在原來的主庫上：
sql>shutdown abort
sql>startup mount
再在原來的備庫上：
sql>startup mount
最後在原來的主庫上：
sql>alter database recover managed standby database disconnect from session;
最後在原來的備庫上：
sql>alter database set standby database to maximize protection;
sql>alter database open;
升級最高可用模式：
此時保證主備庫都是mount狀態：
sql>startup mount
再在原來的備庫上：
sql>startup mount
最後在原來的主庫上：
sql>alter database recover managed standby database disconnect from session;
最後在原來的備庫上：
sql>alter database set standby database to maximize availability;
sql>alter database open;
dg broker
DG broker配置及應用測試
一．配置broker的先決條件
1.資料庫版本：主庫和備庫版本必須10g r2 或者更高，企業版
2.引數檔案：必須使用spfile
SQL> show parameter spfile
NAME TYPE VALUE
------------ ----------- ---------------
spfile string /u01/oracle/10g/dbs/spfilepmdb.ora
3.確定主備庫的compatibe值相同
4.主備庫dg_broker_start的值必須為：true
SQL> alter system set dg_broker_start=true scope=both;
System altered.
SQL> show parameter dg_broker
NAME TYPE VALUE
--------------------------- -------- ------------------------------
dg_broker_config_file1 string /u01/oracle/10g/dbs/dr1pmdb.dat
dg_broker_config_file2 string /u01/oracle/10g/dbs/dr2pmdb.dat
dg_broker_start boolean TRUE
5.必須在監聽裡設定global_name的值
主庫：
SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC=
(SID_NAME=pmdb)
(GLOBAL_DBNAME=pmdb_DGMGRL)
(ORACLE_HOME=/u01/oracle/10g)))
備庫：
SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC=
(SID_NAME=pmdbdg)
(GLOBAL_DBNAME=pmdbdg_DGMGRL)
(ORACLE_HOME=/u01/oracle/10g)))
6.在主備庫開啟閃回資料庫功能
SQL>SHUTDOWN IMMEDIATE;
SQL>STARTUP MOUNT;
SQL>ALTER DATABASE FLASHBACK ON;
SQL>ALTER DATABASE OPEN;
注：如果是rac模式，需要在每個節點上設定dg_broker_config_filen引數
ALTER SYSTEM SET DG_BROKER_CONFIG_FILE1 = '+DG/DIRECTORY/DR1.DAT' SCOPE=BOTH;
ALTER SYSTEM SET DG_BROKER_CONFIG_FILE2 = '+DG/DIRECTORY/DR2.DAT' SCOPE=BOTH;
如果使用了非預設埠(1521),必須設定local_lisener以便所有成員都能訪問
二．broker配置
在從庫上配置
1.執行命令, 連線到主庫：
dgmgrl
connect sys/oracle@pmdbdg
2.建立broker配置
[oracle@ocm2 ]$ dgmgrl
DGMGRL for Linux: Version 10.2.0.1.0 - Production
Copyright (c) 2000, 2005, Oracle. All rights reserved.
Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys/oracle@pmdb
Connected.
DGMGRL> create configuration 'pmdbsoulution' as primary database is 'pmdb' connect identifier is 'pmdb';
Configuration "pordbsoulution" created with primary database "pmdb"
DGMGRL> show configuration
Configuration
Name: pordbsoulution
Enabled: NO
Protection Mode: MaxAvailability
Fast-Start Failover: DISABLED
Databases:
pordb - Primary database
Current status for "pmdbsoulution":
DISABLED
新增備庫到broker配置:
DGMGRL> add database 'pmdbdg' as connect identifier is pmdbdg maintained as physical;
Database "pmdbdg" added
DGMGRL> show configuration
Configuration
Name: pordbsoulution
Enabled: NO
Protection Mode: MaxAvailability
Fast-Start Failover: DISABLED
Databases:
pmdb - Primary database
pmdbdg - Physical standby database
Current status for "pmdbdgsoulution":
DISABLED
3.使broker配置生效
DGMGRL> enable configuration;
Enabled.
DGMGRL> show configuration
Configuration
Name: pordbsoulution
Enabled: YES
Protection Mode: MaxAvailability
Fast-Start Failover: DISABLED
Databases:
pmdb - Primary database
pmdbdg - Physical standby database
Current status for "pordbsoulution":
SUCCESS
可以看到當前狀態改變了
設定資料庫狀態模式:
設定 FastStartFailoverTarget 值.
DGMGRL> edit database 'pmdb' set property 'logxptmode'='sync';
Property "logxptmode" updated
DGMGRL> edit database 'pmdbdg' set property 'logxptmode'='sync';
Property "logxptmode" updated
使 Fast-Start Failover 生效
DGMGRL> ENABLE FAST_START FAILOVER;
Enabled.
開啟 Observer
DGMGRL> start observer
注：執行開啟 Observer後，介面不會自動退出，如果要執行其它dgmgrl命令需重新開一個視窗
重新開一個視窗，進入dgmgrl,檢視fast-start failover 配置
DGMGRL> show configuration;
DGMGRL> show configuration verbose;
4.驗證fast-start failover 配置
主庫：
SQL> col FS_FAILOVER_OBSERVER_HOST for a30
SQL>select fs_failover_observer_present,fs_failover_observer_host,fs_failover_threshold
from v$database;
FS_FAIL FS_FAILOVER_OBSERVER_HOST FS_FAILOVER_THRESHOLD
------- ------------------------------ ---------------------
YES slindb 30
備庫：
SQL> col FS_FAILOVER_OBSERVER_HOST for a30
SQL>select fs_failover_observer_present,fs_failover_observer_host,fs_failover_threshold
from v$database;
FS_FAIL FS_FAILOVER_OBSERVER_HOST FS_FAILOVER_THRESHOLD
------- ------------------------------ ---------------------
YES slindb 30
修改fsfailoverthreshold值為什麼120，預設為30
DGMGRL> edit configuration set property faststartfailoverthreshold=120;
Property "faststartfailoverthreshold" updated
測試broker
檢視主庫和備庫配置資訊和狀態:
DGMGRL> show database verbose pmdb;
Database
Name: pmdb
Role: PRIMARY
Enabled: YES
Intended State: ONLINE
Instance(s):
pmdb
Properties:
InitialConnectIdentifier = 'pmdb'
LogXptMode = 'sync'
Dependency = ''
DelayMins = '0'
Binding = 'OPTIONAL'
MaxFailure = '0'
MaxConnections = '1'
ReopenSecs = '300'
NetTimeout = '180'
LogShipping = 'ON'
PreferredApplyInstance = ''
ApplyInstanceTimeout = '0'
ApplyParallel = 'AUTO'
StandbyFileManagement = 'AUTO'
ArchiveLagTarget = '0'
LogArchiveMaxProcesses = '2'
LogArchiveMinSucceedDest = '1'
DbFileNameConvert = '/u01/oracle/oradata/pmdbdg, /oracle/oradata/pmdb'
LogFileNameConvert = '/u01/oracle/oradata/pmdbdg, /oracle/oradata/pmdb'
FastStartFailoverTarget = 'pmdbdg'
StatusReport = '(monitor)'
InconsistentProperties = '(monitor)'
InconsistentLogXptProps = '(monitor)'
SendQEntries = '(monitor)'
LogXptStatus = '(monitor)'
RecvQEntries = '(monitor)'
HostName = 'plindb'
SidName = 'ocm1'
LocalListenerAddress = '(ADDRESS=(PROTOCOL=tcp)(HOST=ocm1)(PORT=1521))'
StandbyArchiveLocation = '/home/oracle/arch'
AlternateLocation = ''
LogArchiveTrace = '0'
LogArchiveFormat = '%t_%s_%r.dbf'
LatestLog = '(monitor)'
TopWaitEvents = '(monitor)'
Current status for "pmdb":
SUCCESS
DGMGRL> show database verbose pmdbdg;
Database
Name: pmdbdg
Role: PHYSICAL STANDBY
Enabled: YES
Intended State: ONLINE
Instance(s):
pmdbdg
Properties:
InitialConnectIdentifier = 'pmdbdg'
LogXptMode = 'sync'
Dependency = ''
DelayMins = '0'
Binding = 'OPTIONAL'
MaxFailure = '0'
MaxConnections = '1'
ReopenSecs = '300'
NetTimeout = '180'
LogShipping = 'ON'
PreferredApplyInstance = ''
ApplyInstanceTimeout = '0'
ApplyParallel = 'AUTO'
StandbyFileManagement = 'AUTO'
ArchiveLagTarget = '0'
LogArchiveMaxProcesses = '2'
LogArchiveMinSucceedDest = '1'
DbFileNameConvert = '/u01/oracle/oradata/pmdb, /u01/oracle/oradata/pmdbdg'
LogFileNameConvert = '/u01/oracle/oradata/pmdb, /u01/oracle/oradata/pmdbdg'
FastStartFailoverTarget = 'pordb'
StatusReport = '(monitor)'
InconsistentProperties = '(monitor)'
InconsistentLogXptProps = '(monitor)'
SendQEntries = '(monitor)'
LogXptStatus = '(monitor)'
RecvQEntries = '(monitor)'
HostName = 'ocm2'
SidName = 'pmdbdg'
LocalListenerAddress = '(ADDRESS=(PROTOCOL=tcp)(HOST=ocm2)(PORT=1521))'
StandbyArchiveLocation = '/home/oracle/arch/'
AlternateLocation = ''
LogArchiveTrace = '0'
LogArchiveFormat = '%t_%s_%r.dbf'
LatestLog = '(monitor)'
TopWaitEvents = '(monitor)'
Current status for "pmdbdg":
SUCCESS
三．驗證和測試Broker
1.主備切換測試：switchover
檢視主庫和備庫狀態和角色
主庫：
SQL> select database_role,switchover_status from v$database;
DATABASE_ROLE SWITCHOVER_STATUS
---------------- --------------------
PRIMARY SESSIONS ACTIVE
備庫：
SQL> select database_role,switchover_status from v$database;
DATABASE_ROLE SWITCHOVER_STATUS
---------------- --------------------
PHYSICAL STANDBY SESSIONS ACTIVE
[oracle@ocm2 ~]$ dgmgrl
DGMGRL for Linux: Version 10.2.0.1.0 - Production
Copyright (c) 2000, 2005, Oracle. All rights reserved.
Welcome to DGMGRL, type "help" for information.
DGMGRL>
DGMGRL> connect sys/oracle@pmdb
Connected.
DGMGRL> show configuration
Configuration
Name: pmdbsoulution
Enabled: YES
Protection Mode: MaxAvailability
Fast-Start Failover: ENABLED
Databases:
pmdb - Primary database
pmdbdg - Physical standby database
- Fast-Start Failover target
Current status for "pmdbsoulution":
SUCCESS
將主庫切換到sordb上，切換時同時觀察主庫，務庫，dgmgrl的告警日誌資訊：
tail -f /oracle/admin/pmdb/bdump/alter*.log
tail -f /oracle/admin/pmdbdg/bdump/alter*.log
tail -f /oracle/admin/pmdbdg/bdump/dr*.log
形如切換：
DGMGRL> switchover to sordb;
Performing switchover NOW, please wait...
Operation requires shutdown of instance "pmdb" on database "pmdb"
Shutting down instance "pmdb"...
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
Operation requires shutdown of instance "pmdbdg" on database "pmdbdg"
Shutting down instance "ordb"...
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance "pmdb" on database "pmdb"
Starting instance "ordb"...
ORACLE instance started.
Database mounted.
Operation requires startup of instance "pmdbdg" on database "pmdbdg"
Starting instance "pmdbdg"...
ORACLE instance started.
Database mounted.
Switchover succeeded, new primary is "pmdbdg"
切換成功後，檢視新的主庫和備庫的狀態和角色：
SQL> select database_role,switchover_status from v$database;
SQL> select status from v$instance;
DGMGRL> show configuration;
Configuration
Name: pordbsoulution
Enabled: YES
Protection Mode: MaxAvailability
Fast-Start Failover: ENABLED
Databases:
pmdbdg - Primary database
pmdb - Physical standby database
- Fast-Start Failover target
Current status for "pordbsoulution":
SUCCESS
2.failover測試
手動測試：
DGMGRL> failover to pmdbdg;
Performing failover NOW. Please wait...
Operation requires shutdown of instance "pmdbdg " on database
"sordb".
Shutting down instance "sordb"...
database not mounted
ORACLE instance shut down.
Operation requires startup of instance "pmdbdg" on database "pmdbdg".
Starting instance "sordb"...
ORACLE instance started.
Database mounted.
Failover succeeded. New primary is "pmdbdg"
切換成功後，主庫自動變為pmdbdg
自動測試：
將主庫shutdown abort
SQL> shutdown abort
ORACLE instance shut down.
此時在observer控制檯上看到：
DGMGRL> start observer
Observer started
22:26:38.10 Friday, December 09, 2011
Initiating fast-start failover to database "pmdbdg"...
Performing failover NOW, please wait...
Failover succeeded, new primary is "pmdbdg"
22:26:52.27 Friday, December 09, 2011
表示在主庫pmdb出現故障時，自動切換，新的主庫為pmdbdg
檢視failover後的新主庫sordb角色和狀態：
SQL> select database_role,switchover_status from v$database;
DATABASE_ROLE SWITCHOVER_STATUS
---------------- --------------------
PRIMARY SESSIONS ACTIVE
SQL> select status from v$instance;
STATUS
------------
OPEN
切換成功。
如果此時再將pordb啟動，啟動後會自動轉化為備庫：
此時在observer控制檯上看到：
22:31:04.46 Friday, December 09, 2011
Initiating reinstatement for database "pmdb"...
Reinstating database "pordb", please wait...
Operation requires shutdown of instance "pmdbdg" on database "pmdbdg"
Shutting down instance "pmdbdg"...
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance "pmdb" on database "pmdb"
Starting instance "pmdb"...
ORACLE instance started.
Database mounted.
Continuing to reinstate database "pmdb" ...
Reinstatement of database "pordb" succeeded
22:31:59.61 Friday, December 09, 2011
檢視pordb角色和狀態：
SQL> select database_role,switchover_status from v$database;
DATABASE_ROLE SWITCHOVER_STATUS
---------------- --------------------
PHYSICAL STANDBY SESSIONS ACTIVE
SQL> select status from v$instance;
STATUS
------------
MOUNTED
檢視sordb角色和狀態：
SQL> select database_role,switchover_status from v$database;
DATABASE_ROLE SWITCHOVER_STATUS
---------------- --------------------
PRIMARY SESSIONS ACTIVE
SQL> select status from v$instance;
STATUS
------------
OPEN
檢視主庫和備庫歸檔日誌及應用資訊：
select sequence#,first_time,next_time,applied from v$archived_log order by sequence#;
總結：在實驗中還是遇到一些問題，不過最後都一一的順利解決，最容易遇到的以下問題：
1. ORA-01031: insufficient privileges
PING[ARC0]: Heartbeat failed to connect to standby 'sordb'. Error is 1031.
這各情況有兩個可能，一個是密碼檔案沒有拷到備庫上，另一個原因是TNS設定不正確或者監聽有問題，重新設定這兩個地方就可以解決。
2.備庫接收不到備庫的日誌
這個主要還是需要看告警日誌，主要原因還是網路和許可權的問題，當然也有可能是由其它原因導致網路和許可權問題，比如備庫的環境變數設定，我就在實驗時不小心備庫sid設定的問題引起權
限問題，導致備庫接收不到日誌
3. 資料庫檔案是使用OMF管理的，那麼使用rman建立的備庫的資料檔名已經與主庫的檔名不一樣了，此 時需將引數檔案裡的控制檔案的路徑和名稱修改為備庫上的實際路徑和
名稱，否則啟動資料庫會報錯。
Warning: ORA-16610: command 'EDIT DATABASE billdb SET PROPERTY' in progress
是正常的，說明操作正在進行中，等待一段時間後再檢查，直到最後出現SUCCESS。
主機名：
host:ocm1 ip:192.168.88.101 sid=pmdb db_name=pmdb db_unique_name=pmdb
host:ocm2 ip:192.168.88.102 sid=pmdbdg db_name=pmdb db_unique_name=pmdbdg
maximum performance mode
oracle dg 主備庫的db_name(sid)是一樣的，db_unique_name不一樣
主庫的操作：
1.sql>alter database force logging;
2.sql>select * from v$log;
3.sql>select member from v$logfile;
4.
sql>ALTER DATABASE ADD STANDBY LOGFILE GROUP 4
('/u01/oracle/oradata/PMDB/redo04.log') SIZE 50m;
sql>ALTER DATABASE ADD STANDBY LOGFILE GROUP 5
('/u01/oracle/oradata/PMDB/redo05.log') SIZE 50m;
sql>ALTER DATABASE ADD STANDBY LOGFILE GROUP 6
('/u01/oracle/oradata/PMDB/redo06.log') SIZE 50m;
配置引數檔案：
DB_UNIQUE_NAME=pmdb
LOG_ARCHIVE_CONFIG='DG_CONFIG=(pmdb,pmdbdg)'
LOG_ARCHIVE_DEST_1='LOCATION=/home/oracle/arch/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=pmdb'
LOG_ARCHIVE_DEST_2='SERVICE=pmdbdg LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=pmdbdg'
FAL_SERVER=pmdbdg
FAL_CLIENT=pmdb
DB_FILE_NAME_CONVERT='/u01/oracle/oradata/pmdbdg/','/u01/oracle/oradata/pmdb/'
LOG_FILE_NAME_CONVERT= '/u01/oracle/oradata/pmdbdg/','/u01/oracle/oradata/pmdb/'
STANDBY_FILE_MANAGEMENT=AUTO
sql>shutdown immediate
cd $ORACLE_BASE/oradata
tar -vcf pmdb.tar pmdb
gzip pmdb.tar
主庫：scp pmdb.tar.gz ocm2:/u01/oralce/oradata->備庫：cd /u01/oracle/oradata/ tar -vxzf pmdb.tar.gz
sql>startup mount
sql>alter database create standby controlfile as '/tmp/control.stdby';
scp /tmp/control.stdby ocm2:/u01/oracle/oradata/pmdbdg
cd $ORACLE_HOME/dbs
scp initpmdb.ora ocm2:/tmp
配置主庫的tns:
cd $ORACLE_HOME/network/admin
vim tnsnames.ora
vim tnsnames.ora
新增如下程式碼：
PMDBDG =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = ocm2)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = pmdbdg)
)
)
tail -f alter_pmdb.log
備庫
cd $ORACLE_HOME/dbs
orapwd file=orapwpmdbdg password=oracle entries=5 force=y
cd $ORACLE_BASE/oradata/pmdbdg
rm *.ctl
mv control.stdby control01.ctl
cp /tmp/initpmdb.ora $ORACLE_HOME/dbs
cd $ORACLE_HOME/dbs/
mv initpmdb.ora initpmdbdg.ora
vim initpmdbdg.ora
DB_UNIQUE_NAME=pmdbdg
LOG_ARCHIVE_CONFIG='DG_CONFIG=(pmdb,pmdbdg)'
LOG_ARCHIVE_DEST_1='LOCATION=/home/oracle/arch/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=pmdbdg'
LOG_ARCHIVE_DEST_2='SERVICE=pmdb LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=pmdb'
FAL_SERVER=pmdb
FAL_CLIENT=pmdbdg
DB_FILE_NAME_CONVERT='/u01/oracle/oradata/pmdb/','/u01/oracle/oradata/pmdbdg/'
LOG_FILE_NAME_CONVERT= '/u01/oracle/oradata/pmdb/','/u01/oracle/oradata/pmdbdg/'
STANDBY_FILE_MANAGEMENT=AUTO
mkdir -p /u01/oracle/admin/pmdbdg/udump
mkdir -p /u01/oracle/admin/pmdbdg/cdump
mkdir -p /u01/oracle/admin/pmdbdg/bdump
mkdir -p /u01/oracle/admin/pmdbdg/adump
注意修改initpmdbdg.ora檔案中的control檔案路徑。
配置備庫的tns:
cd $ORACLE_HOME/network/admin
vim tnsnames.ora
新增如下程式碼：
PMDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = ocm1)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = pmdb)
)
)
測試連通性：
sqlplus 'sys/oracle1@pmdb as sysdba'
啟動例項：
export ORACLE_SID=pmdbdg
sqlplus '/as sysdba'
sql>startup mount
啟動監聽程式：
lsnrctl start
lsnrctl status
show parameters local
show parameters dump
tail -f alter_pmdbdg.log
啟動順序：
先備庫：
此時備庫之前已經是mount狀態：
執行sql>alter database recover managed standby database disconnect from session;
然後檢視後臺alter日誌。
日誌中會顯示Clearing online redo logfile;
media recovery waiting for thread 1 sequence 32
再主庫：
sql>alter database open;
然後檢視日誌：
日誌中會顯示：
lgwr: setting 'active' archival for destination log_archive_dest_2
此時檢視備庫日誌：
日誌中會顯示：
primary database is in maximum performance mode
media recovery log /home/oracle/arch/1_32_.log
在主庫嘗試切換幾組日誌：
sql>alter system switch logfile;
sql>alter system switch logfile;
sql>alter system switch logfile;
然後到備庫中檢視日誌：
media recovery log
再主庫嘗試建立表空間看備庫是否也有相應表空間：
sql>create tablespace test datafile '/u01/app/oracle/oradata/prod/disk2/test01.dbf' size 10m;
sql>alter system switch logfile(切換完日誌備庫中才會傳過去剛建立的表空間)。
角色切換(switchover)：
主庫：
sql>select switchover_status from v$database;
switchover_status
------------------------
SESSIONS_ACTIVE(不會顯示to standby狀態)
sql> alter database commit to switchover to physical standby with session shutdown;
檢視後臺alter 日誌
然後檢視備庫後臺alter日誌：
日誌中會有End-of-REDO標識。（主庫和備庫都會有End-of-REDO標識）
備庫：
sql>alter database commit to switchover to primary;
此時原來的主庫變成備庫，備庫變成主庫。
啟動的時候先備庫再主庫：
備庫（原來的主庫）：
sql>shutdown
sql>startup mount
sql>alter database recover managed standby database disconnect from session;
主庫（原來的備庫）：
sql>alter database open;
然後再切換回原來的角色：
主庫（原來的備庫）
sql> alter database commit to switchover to physical standby with session shutdown;
備庫（原來的主庫）
sql> alter database commit to switchover to primary;
sql>alter database open;
主庫(原來的備庫)
sql>shutdown
sql>startup
升級到最大保護模式：
主庫：
sql>shutdown immediate
備庫：
sql>shutdown immediate
主庫：
修改主庫pfile檔案：
原來：
LOG_ARCHIVE_DEST_2='SERVICE=pmdbdg LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=pmdbdg'
修改為：
LOG_ARCHIVE_DEST_2='SERVICE=pmdbdg LGWR SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=pmdbdg'
備庫：
修改備庫pfile檔案：
原來：
LOG_ARCHIVE_DEST_2='SERVICE=pmdb LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=pmdb'
修改為：
LOG_ARCHIVE_DEST_2='SERVICE=pmdb LGWR SYNC affirm VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=pmdb'
升級到最大保護模式（需要完全同步）
1.先做一次主備庫切換
先主庫：
sql>alter database commit to switchover to physical standby with session shutdown;
再備庫：
sql>alter database commit to switchover to primary;
然後在原來的主庫上：
sql>shutdown
sql>startup mount
sql>alter database recover managed standby database disconnect from session;
然後在原來的備庫庫上：
sql>alter database set standby database to maximize protection;
sql>alter database open;
在原來的備庫上建立測試表：
SQL> create table test_lzq(id number,name varchar2(20));
Table created.
SQL> insert into test_lzq values(1,'lzq');
1 row created.
SQL> insert into test_lzq values(1,'lzq');
1 row created.
SQL> insert into test_lzq values(1,'lzq');
1 row created.
SQL> insert into test_lzq values(1,'lzq');
1 row created.
SQL> insert into test_lzq values(1,'lzq');
1 row created.
SQL> commit;
Commit complete.
SQL> select count(*) from test_lzq;
COUNT(*)
----------
5
sql>shutdown immediate
再在原來的主庫上：
sql>shutdown immediate
sql>startup
SQL> select count(*) from test_lzq;
COUNT(*)
----------
5
最後變回原來的模式：
先在原來的主庫上：
sql>shutdown abort
sql>startup mount
再在原來的備庫上：
sql>startup mount
最後在原來的主庫上：
sql>alter database recover managed standby database disconnect from session;
最後在原來的備庫上：
sql>alter database set standby database to maximize protection;
sql>alter database open;
升級最高可用模式：
此時保證主備庫都是mount狀態：
sql>startup mount
再在原來的備庫上：
sql>startup mount
最後在原來的主庫上：
sql>alter database recover managed standby database disconnect from session;
最後在原來的備庫上：
sql>alter database set standby database to maximize availability;
sql>alter database open;
dg broker
DG broker配置及應用測試
一．配置broker的先決條件
1.資料庫版本：主庫和備庫版本必須10g r2 或者更高，企業版
2.引數檔案：必須使用spfile
SQL> show parameter spfile
NAME TYPE VALUE
------------ ----------- ---------------
spfile string /u01/oracle/10g/dbs/spfilepmdb.ora
3.確定主備庫的compatibe值相同
4.主備庫dg_broker_start的值必須為：true
SQL> alter system set dg_broker_start=true scope=both;
System altered.
SQL> show parameter dg_broker
NAME TYPE VALUE
--------------------------- -------- ------------------------------
dg_broker_config_file1 string /u01/oracle/10g/dbs/dr1pmdb.dat
dg_broker_config_file2 string /u01/oracle/10g/dbs/dr2pmdb.dat
dg_broker_start boolean TRUE
5.必須在監聽裡設定global_name的值
主庫：
SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC=
(SID_NAME=pmdb)
(GLOBAL_DBNAME=pmdb_DGMGRL)
(ORACLE_HOME=/u01/oracle/10g)))
備庫：
SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC=
(SID_NAME=pmdbdg)
(GLOBAL_DBNAME=pmdbdg_DGMGRL)
(ORACLE_HOME=/u01/oracle/10g)))
6.在主備庫開啟閃回資料庫功能
SQL>SHUTDOWN IMMEDIATE;
SQL>STARTUP MOUNT;
SQL>ALTER DATABASE FLASHBACK ON;
SQL>ALTER DATABASE OPEN;
注：如果是rac模式，需要在每個節點上設定dg_broker_config_filen引數
ALTER SYSTEM SET DG_BROKER_CONFIG_FILE1 = '+DG/DIRECTORY/DR1.DAT' SCOPE=BOTH;
ALTER SYSTEM SET DG_BROKER_CONFIG_FILE2 = '+DG/DIRECTORY/DR2.DAT' SCOPE=BOTH;
如果使用了非預設埠(1521),必須設定local_lisener以便所有成員都能訪問
二．broker配置
在從庫上配置
1.執行命令, 連線到主庫：
dgmgrl
connect sys/oracle@pmdbdg
2.建立broker配置
[oracle@ocm2 ]$ dgmgrl
DGMGRL for Linux: Version 10.2.0.1.0 - Production
Copyright (c) 2000, 2005, Oracle. All rights reserved.
Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys/oracle@pmdb
Connected.
DGMGRL> create configuration 'pmdbsoulution' as primary database is 'pmdb' connect identifier is 'pmdb';
Configuration "pordbsoulution" created with primary database "pmdb"
DGMGRL> show configuration
Configuration
Name: pordbsoulution
Enabled: NO
Protection Mode: MaxAvailability
Fast-Start Failover: DISABLED
Databases:
pordb - Primary database
Current status for "pmdbsoulution":
DISABLED
新增備庫到broker配置:
DGMGRL> add database 'pmdbdg' as connect identifier is pmdbdg maintained as physical;
Database "pmdbdg" added
DGMGRL> show configuration
Configuration
Name: pordbsoulution
Enabled: NO
Protection Mode: MaxAvailability
Fast-Start Failover: DISABLED
Databases:
pmdb - Primary database
pmdbdg - Physical standby database
Current status for "pmdbdgsoulution":
DISABLED
3.使broker配置生效
DGMGRL> enable configuration;
Enabled.
DGMGRL> show configuration
Configuration
Name: pordbsoulution
Enabled: YES
Protection Mode: MaxAvailability
Fast-Start Failover: DISABLED
Databases:
pmdb - Primary database
pmdbdg - Physical standby database
Current status for "pordbsoulution":
SUCCESS
可以看到當前狀態改變了
設定資料庫狀態模式:
設定 FastStartFailoverTarget 值.
DGMGRL> edit database 'pmdb' set property 'logxptmode'='sync';
Property "logxptmode" updated
DGMGRL> edit database 'pmdbdg' set property 'logxptmode'='sync';
Property "logxptmode" updated
使 Fast-Start Failover 生效
DGMGRL> ENABLE FAST_START FAILOVER;
Enabled.
開啟 Observer
DGMGRL> start observer
注：執行開啟 Observer後，介面不會自動退出，如果要執行其它dgmgrl命令需重新開一個視窗
重新開一個視窗，進入dgmgrl,檢視fast-start failover 配置
DGMGRL> show configuration;
DGMGRL> show configuration verbose;
4.驗證fast-start failover 配置
主庫：
SQL> col FS_FAILOVER_OBSERVER_HOST for a30
SQL>select fs_failover_observer_present,fs_failover_observer_host,fs_failover_threshold
from v$database;
FS_FAIL FS_FAILOVER_OBSERVER_HOST FS_FAILOVER_THRESHOLD
------- ------------------------------ ---------------------
YES slindb 30
備庫：
SQL> col FS_FAILOVER_OBSERVER_HOST for a30
SQL>select fs_failover_observer_present,fs_failover_observer_host,fs_failover_threshold
from v$database;
FS_FAIL FS_FAILOVER_OBSERVER_HOST FS_FAILOVER_THRESHOLD
------- ------------------------------ ---------------------
YES slindb 30
修改fsfailoverthreshold值為什麼120，預設為30
DGMGRL> edit configuration set property faststartfailoverthreshold=120;
Property "faststartfailoverthreshold" updated
測試broker
檢視主庫和備庫配置資訊和狀態:
DGMGRL> show database verbose pmdb;
Database
Name: pmdb
Role: PRIMARY
Enabled: YES
Intended State: ONLINE
Instance(s):
pmdb
Properties:
InitialConnectIdentifier = 'pmdb'
LogXptMode = 'sync'
Dependency = ''
DelayMins = '0'
Binding = 'OPTIONAL'
MaxFailure = '0'
MaxConnections = '1'
ReopenSecs = '300'
NetTimeout = '180'
LogShipping = 'ON'
PreferredApplyInstance = ''
ApplyInstanceTimeout = '0'
ApplyParallel = 'AUTO'
StandbyFileManagement = 'AUTO'
ArchiveLagTarget = '0'
LogArchiveMaxProcesses = '2'
LogArchiveMinSucceedDest = '1'
DbFileNameConvert = '/u01/oracle/oradata/pmdbdg, /oracle/oradata/pmdb'
LogFileNameConvert = '/u01/oracle/oradata/pmdbdg, /oracle/oradata/pmdb'
FastStartFailoverTarget = 'pmdbdg'
StatusReport = '(monitor)'
InconsistentProperties = '(monitor)'
InconsistentLogXptProps = '(monitor)'
SendQEntries = '(monitor)'
LogXptStatus = '(monitor)'
RecvQEntries = '(monitor)'
HostName = 'plindb'
SidName = 'ocm1'
LocalListenerAddress = '(ADDRESS=(PROTOCOL=tcp)(HOST=ocm1)(PORT=1521))'
StandbyArchiveLocation = '/home/oracle/arch'
AlternateLocation = ''
LogArchiveTrace = '0'
LogArchiveFormat = '%t_%s_%r.dbf'
LatestLog = '(monitor)'
TopWaitEvents = '(monitor)'
Current status for "pmdb":
SUCCESS
DGMGRL> show database verbose pmdbdg;
Database
Name: pmdbdg
Role: PHYSICAL STANDBY
Enabled: YES
Intended State: ONLINE
Instance(s):
pmdbdg
Properties:
InitialConnectIdentifier = 'pmdbdg'
LogXptMode = 'sync'
Dependency = ''
DelayMins = '0'
Binding = 'OPTIONAL'
MaxFailure = '0'
MaxConnections = '1'
ReopenSecs = '300'
NetTimeout = '180'
LogShipping = 'ON'
PreferredApplyInstance = ''
ApplyInstanceTimeout = '0'
ApplyParallel = 'AUTO'
StandbyFileManagement = 'AUTO'
ArchiveLagTarget = '0'
LogArchiveMaxProcesses = '2'
LogArchiveMinSucceedDest = '1'
DbFileNameConvert = '/u01/oracle/oradata/pmdb, /u01/oracle/oradata/pmdbdg'
LogFileNameConvert = '/u01/oracle/oradata/pmdb, /u01/oracle/oradata/pmdbdg'
FastStartFailoverTarget = 'pordb'
StatusReport = '(monitor)'
InconsistentProperties = '(monitor)'
InconsistentLogXptProps = '(monitor)'
SendQEntries = '(monitor)'
LogXptStatus = '(monitor)'
RecvQEntries = '(monitor)'
HostName = 'ocm2'
SidName = 'pmdbdg'
LocalListenerAddress = '(ADDRESS=(PROTOCOL=tcp)(HOST=ocm2)(PORT=1521))'
StandbyArchiveLocation = '/home/oracle/arch/'
AlternateLocation = ''
LogArchiveTrace = '0'
LogArchiveFormat = '%t_%s_%r.dbf'
LatestLog = '(monitor)'
TopWaitEvents = '(monitor)'
Current status for "pmdbdg":
SUCCESS
三．驗證和測試Broker
1.主備切換測試：switchover
檢視主庫和備庫狀態和角色
主庫：
SQL> select database_role,switchover_status from v$database;
DATABASE_ROLE SWITCHOVER_STATUS
---------------- --------------------
PRIMARY SESSIONS ACTIVE
備庫：
SQL> select database_role,switchover_status from v$database;
DATABASE_ROLE SWITCHOVER_STATUS
---------------- --------------------
PHYSICAL STANDBY SESSIONS ACTIVE
[oracle@ocm2 ~]$ dgmgrl
DGMGRL for Linux: Version 10.2.0.1.0 - Production
Copyright (c) 2000, 2005, Oracle. All rights reserved.
Welcome to DGMGRL, type "help" for information.
DGMGRL>
DGMGRL> connect sys/oracle@pmdb
Connected.
DGMGRL> show configuration
Configuration
Name: pmdbsoulution
Enabled: YES
Protection Mode: MaxAvailability
Fast-Start Failover: ENABLED
Databases:
pmdb - Primary database
pmdbdg - Physical standby database
- Fast-Start Failover target
Current status for "pmdbsoulution":
SUCCESS
將主庫切換到sordb上，切換時同時觀察主庫，務庫，dgmgrl的告警日誌資訊：
tail -f /oracle/admin/pmdb/bdump/alter*.log
tail -f /oracle/admin/pmdbdg/bdump/alter*.log
tail -f /oracle/admin/pmdbdg/bdump/dr*.log
形如切換：
DGMGRL> switchover to sordb;
Performing switchover NOW, please wait...
Operation requires shutdown of instance "pmdb" on database "pmdb"
Shutting down instance "pmdb"...
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
Operation requires shutdown of instance "pmdbdg" on database "pmdbdg"
Shutting down instance "ordb"...
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance "pmdb" on database "pmdb"
Starting instance "ordb"...
ORACLE instance started.
Database mounted.
Operation requires startup of instance "pmdbdg" on database "pmdbdg"
Starting instance "pmdbdg"...
ORACLE instance started.
Database mounted.
Switchover succeeded, new primary is "pmdbdg"
切換成功後，檢視新的主庫和備庫的狀態和角色：
SQL> select database_role,switchover_status from v$database;
SQL> select status from v$instance;
DGMGRL> show configuration;
Configuration
Name: pordbsoulution
Enabled: YES
Protection Mode: MaxAvailability
Fast-Start Failover: ENABLED
Databases:
pmdbdg - Primary database
pmdb - Physical standby database
- Fast-Start Failover target
Current status for "pordbsoulution":
SUCCESS
2.failover測試
手動測試：
DGMGRL> failover to pmdbdg;
Performing failover NOW. Please wait...
Operation requires shutdown of instance "pmdbdg " on database
"sordb".
Shutting down instance "sordb"...
database not mounted
ORACLE instance shut down.
Operation requires startup of instance "pmdbdg" on database "pmdbdg".
Starting instance "sordb"...
ORACLE instance started.
Database mounted.
Failover succeeded. New primary is "pmdbdg"
切換成功後，主庫自動變為pmdbdg
自動測試：
將主庫shutdown abort
SQL> shutdown abort
ORACLE instance shut down.
此時在observer控制檯上看到：
DGMGRL> start observer
Observer started
22:26:38.10 Friday, December 09, 2011
Initiating fast-start failover to database "pmdbdg"...
Performing failover NOW, please wait...
Failover succeeded, new primary is "pmdbdg"
22:26:52.27 Friday, December 09, 2011
表示在主庫pmdb出現故障時，自動切換，新的主庫為pmdbdg
檢視failover後的新主庫sordb角色和狀態：
SQL> select database_role,switchover_status from v$database;
DATABASE_ROLE SWITCHOVER_STATUS
---------------- --------------------
PRIMARY SESSIONS ACTIVE
SQL> select status from v$instance;
STATUS
------------
OPEN
切換成功。
如果此時再將pordb啟動，啟動後會自動轉化為備庫：
此時在observer控制檯上看到：
22:31:04.46 Friday, December 09, 2011
Initiating reinstatement for database "pmdb"...
Reinstating database "pordb", please wait...
Operation requires shutdown of instance "pmdbdg" on database "pmdbdg"
Shutting down instance "pmdbdg"...
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance "pmdb" on database "pmdb"
Starting instance "pmdb"...
ORACLE instance started.
Database mounted.
Continuing to reinstate database "pmdb" ...
Reinstatement of database "pordb" succeeded
22:31:59.61 Friday, December 09, 2011
檢視pordb角色和狀態：
SQL> select database_role,switchover_status from v$database;
DATABASE_ROLE SWITCHOVER_STATUS
---------------- --------------------
PHYSICAL STANDBY SESSIONS ACTIVE
SQL> select status from v$instance;
STATUS
------------
MOUNTED
檢視sordb角色和狀態：
SQL> select database_role,switchover_status from v$database;
DATABASE_ROLE SWITCHOVER_STATUS
---------------- --------------------
PRIMARY SESSIONS ACTIVE
SQL> select status from v$instance;
STATUS
------------
OPEN
檢視主庫和備庫歸檔日誌及應用資訊：
select sequence#,first_time,next_time,applied from v$archived_log order by sequence#;
總結：在實驗中還是遇到一些問題，不過最後都一一的順利解決，最容易遇到的以下問題：
1. ORA-01031: insufficient privileges
PING[ARC0]: Heartbeat failed to connect to standby 'sordb'. Error is 1031.
這各情況有兩個可能，一個是密碼檔案沒有拷到備庫上，另一個原因是TNS設定不正確或者監聽有問題，重新設定這兩個地方就可以解決。
2.備庫接收不到備庫的日誌
這個主要還是需要看告警日誌，主要原因還是網路和許可權的問題，當然也有可能是由其它原因導致網路和許可權問題，比如備庫的環境變數設定，我就在實驗時不小心備庫sid設定的問題引起權
限問題，導致備庫接收不到日誌
3. 資料庫檔案是使用OMF管理的，那麼使用rman建立的備庫的資料檔名已經與主庫的檔名不一樣了，此 時需將引數檔案裡的控制檔案的路徑和名稱修改為備庫上的實際路徑和
名稱，否則啟動資料庫會報錯。
Warning: ORA-16610: command 'EDIT DATABASE billdb SET PROPERTY' in progress
是正常的，說明操作正在進行中，等待一段時間後再檢查，直到最後出現SUCCESS。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29429468/viewspace-1085489/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Oracle物理DG自動切換——Dataguard Broker配置Oracle
- oracle dataguard broker 配置Oracle
- Oracle DG管理Broker配置Oracle
- DataGuard broker之一：DataGuard broker簡介
- Oracle DG Broker配置的管理週期Oracle
- ORACLE 11.2.0.4 DG(Broker) for linux 部署OracleLinux
- 【DATAGUARD】DG系列之RACtoONE快照備用資料庫的搭建資料庫
- 【DATAGUARD】Oracle19c Data Guard BrokerOracle
- Oracle DG管理資料庫屬性Oracle資料庫
- ORACLE DATAGUARD 資料庫---建立物理備用資料庫Oracle資料庫
- oracle_DG+broker+Keepalived的部署文件Oracle
- Oracle DG資料庫狀態轉換Oracle資料庫
- Oracle資料庫（DataGuard）遷移方案(上)Oracle資料庫
- Oracle資料庫（DataGuard）遷移方案（中）Oracle資料庫
- Oracle資料庫（DataGuard）遷移方案（下）Oracle資料庫
- oracle 11g dg broker開啟和配置Oracle
- Oracle 11g dg broker自動failoverOracleAI
- Oracle DG 管理Broker配置成員的狀態Oracle
- oracle dg庫資料檔案空間不足Oracle
- 使用DG_broker工具管理DG之switchover
- ORACLE RAC資料庫配置Dataguard環境(3)Oracle資料庫
- ORACLE RAC資料庫配置Dataguard環境(2)Oracle資料庫
- ORACLE RAC資料庫配置Dataguard環境(1)Oracle資料庫
- dg和ogg的區別--oracle資料庫Oracle資料庫
- oracle DG資料庫切換步驟筆記Oracle資料庫筆記
- Linux下建立Oracle 10g DG和Broker配置LinuxOracle 10g
- Linux 下建立Oracle 10g DG和Broker配置LinuxOracle 10g
- 【DATAGUARD】物理dg配置客戶端無縫切換 (八.1)--Data Guard Broker 的配置客戶端
- 使用Broker實現DG切換
- Oracle DG備庫手動管理新增資料檔案Oracle
- 【DATAGUARD】DG系列之RACtoONE物理備庫的搭建
- 【DataGuard】Oracle DataGuard 資料保護模式切換Oracle模式
- Oracle資料庫由dataguard備庫引起的log file sync等待Oracle資料庫
- ORACLE資料庫dataguard配置,rman移機,dx鎖處Oracle資料庫
- dg broker配置的問題及分析
- DataGuard 搭建 uweb 分行資料庫Web資料庫
- GoldenGate12.2從DataGuard備庫同步資料到其他Oracle資料庫GoOracle資料庫
- Oracle Dataguard + Goldengate資料同步OracleGo