ORACLE10G DG配置下Physical Standby Database的管理
1.啟動和關閉Physical Standby Database
1)啟動Physical Standby Database
SQL> startup mount;
ORACLE instance started.
Total System Global Area 281018368 bytes
Fixed Size 2020160 bytes
Variable Size 88083648 bytes
Database Buffers 184549376 bytes
Redo Buffers 6365184 bytes
Database mounted.
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION; --啟用redo Apply
Database altered.
SQL> select name,database_role from v$database; --檢視資料庫角色
NAME DATABASE_ROLE
--------- ----------------
MYORCL PHYSICAL STANDBY
在作業系統下，檢視redo應用是否啟用，如下：
[oracle@xiaoru ~]$ ps -ef |grep ora_mrp
oracle 2155 1 0 13:28 ? 00:00:00 ora_mrp0_orcl --有該程式表示standby啟用了redo應用
oracle 2163 1808 0 13:29 pts/2 00:00:00 grep ora_mrp
在SQL命令列下，檢視redo應用是否啟用，如下：
SQL> select process,status from v$managed_standby;
PROCESS STATUS
--------- ------------
ARCH CONNECTED
ARCH CONNECTED
MRP0 WAIT_FOR_LOG --有MRP0程式在WAIT_FOR_LOG狀態時表示啟動了Redo應用
SQL> select distinct recovery_mode,protection_mode from v$archive_dest_status
2 where database_mode='MOUNTED-STANDBY';
RECOVERY_MODE PROTECTION_MODE
----------------------- --------------------
MANAGED MAXIMUM PERFORMANCE
批註：recovery_mode為 MANAGED | MANAGED REAL TIME APPLY 就表示啟用了Redo應用; 為 IDLE 時表示沒有啟用Redo應用。
SQL> alter database recover managed standby database cancel; --取消redoApply接收
Database altered.
SQL> alter database open;
Database altered.
批註：alter database open會直接開啟到read only 模式，如果要再切換會redo apply模式，直接執行
alter database recover managed standby database disconnect from session即可。
2)關閉physical standby Database
SQL> SELECT PROCESS, STATUS FROM V$MANAGED_STANDBY; --備庫查詢是否在接收redo 應用
PROCESS STATUS
--------- ------------
ARCH CONNECTED
ARCH CONNECTED
MRP0 WAIT_FOR_LOG
SQL> alter system switch logfile; --關閉備庫之前先在主庫切換日誌
System altered.
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL; --取消redo應用
Database altered.
SQL> SHUTDOWN IMMEDIATE; --關閉資料庫
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
2.影響物理standby的主庫事件
多數情況下，Primary資料庫的修改會隨著REDO資料傳播到物理Standby資料庫端並被應用，不需要在物理Standby端做額外的操作，不過根據實際配置的不同，也會有例外，有些操作不是沒有被傳播到Standby端，而是傳播過去了，但不能正確執行，其中最常見的就是對錶空間和日誌檔案的管理操作，下面透過例項逐一進行說明。
1)建立表空間或資料檔案
初始化引數standby_file_management用來控制是否自動將primary資料庫增加表空間或資料檔案的改動，傳播到物理物理standby資料庫。該引數有兩個值：
AUTO：如果該引數值設定為AUTO，則primary database執行的表空間操作也會傳播到physical standby database上執行；
MANUAL：如果設定為MANUAL或未設定任何值(預設值是MANUAL)，需要手工複製新建立的資料檔案到physical standby database伺服器。
批註：STANDBY_FILE_MANAGEMENT引數特指Primary database端的表空間或資料檔案建立，如果資料檔案是從其他資料庫複製而來(比如透過TTS傳輸表空間)，則不管STANDBY_FILE_MANAGEMENT引數值如何設定，都必須同時手工複製到Standby database，並建立physical standby database的控制檔案。對於檔案系統，我們可以將初始化引數STANDBY_FILE_MANAGMENT設定為AUTO，但是對於裸裝置，只能將該引數設定為MANUAL。
示例1：新增一個資料檔案
SQL>startup --主庫開啟資料庫
SQL> show parameter STANDBY_FILE_MANAGEMENT ; --主庫查詢操作
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
standby_file_management string AUTO
SQL> startup mount; --備庫啟動到mount狀態
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION; --開啟redo應用
Database altered.
SQL> show parameter STANDBY_FILE_MANAGEMENT ; --備庫查詢操作
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
standby_file_management string MANUAL
SQL> show parameter STANDBY_FILE_MANAGEMENT ; --備庫修改STANDBY_FILE_MANAGEMENT的值
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
standby_file_management string AUTO
在主資料庫建立一個表空間如下：
SQL> CREATE TABLESPACE new_ts DATAFILE '/u01/app/oradata/myorcl/new_ts.dbf' SIZE 1m AUTOEXTEND ON MAXSIZE UNLIMITED;
Tablespace created.
SQL> ALTER SYSTEM ARCHIVE LOG CURRENT; --將當前日誌歸檔
System altered.
SQL> SELECT NAME FROM V$DATAFILE; --主庫檢視建立的表空間指定的資料檔案
NAME
--------------------------------------------------------------------------------
/u01/app/oradata/myorcl/system01.dbf
/u01/app/oradata/myorcl/undotbs01.dbf
/u01/app/oradata/myorcl/sysaux01.dbf
/u01/app/oradata/myorcl/users01.dbf
/u01/app/oradata/myorcl/example01.dbf
/u01/app/oradata/myorcl/new_ts.dbf
6 rows selected.
在備庫操作如下：
SQL> SELECT NAME FROM V$DATAFILE;
NAME
--------------------------------------------------------------------------------
/u01/app/oradata/myorcl/system01.dbf
/u01/app/oradata/myorcl/undotbs01.dbf
/u01/app/oradata/myorcl/sysaux01.dbf
/u01/app/oradata/myorcl/users01.dbf
/u01/app/oradata/myorcl/example01.dbf
/u01/app/oradata/myorcl/new_ts.dbf
6 rows selected.
3)刪除表空間
SQL> show parameter STANDBY_FILE_MANAGEMENT ; --主庫查詢操作
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
standby_file_management string AUTO
SQL> show parameter STANDBY_FILE_MANAGEMENT ; --備庫查詢操作
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
standby_file_management string AUTO
SQL> drop tablespace new_ts; --主庫操作，刪除名為new_ts的表空間
Tablespace dropped.
SQL> alter system switch logfile; --切換日誌，使其歸檔
System altered.
SQL> SELECT PROCESS, STATUS FROM V$MANAGED_STANDBY; --備庫操作，確定redo 應用實時執行
PROCESS STATUS
--------- ------------
ARCH CONNECTED
ARCH CONNECTED
MRP0 WAIT_FOR_LOG
RFS IDLE
RFS IDLE
SQL> SELECT NAME FROM V$DATAFILE; --主庫檢視錶空間是否刪除
NAME
--------------------------------------------------------------------------------
/u01/app/oradata/myorcl/system01.dbf
/u01/app/oradata/myorcl/undotbs01.dbf
/u01/app/oradata/myorcl/sysaux01.dbf
/u01/app/oradata/myorcl/users01.dbf
/u01/app/oradata/myorcl/example01.dbf
SQL> SELECT NAME FROM V$DATAFILE; --備庫查詢的刪除的表空間已經同步
NAME
--------------------------------------------------------------------------------
/u01/app/oradata/myorcl/system01.dbf
/u01/app/oradata/myorcl/undotbs01.dbf
/u01/app/oradata/myorcl/sysaux01.dbf
/u01/app/oradata/myorcl/users01.dbf
/u01/app/oradata/myorcl/example01.dbf
在redo應用之後，刪除主、備庫系統上的資料檔案，如下操作：
[oracle@xiaoru myorcl]$ ls
example01.dbf new_ts.dbf sysaux01.dbf system01.dbf temp01.dbf undotbs01.dbf users01.dbf
[oracle@xiaoru myorcl]$ rm -rf new_ts.dbf --備庫操作
[oracle@xiaoru myorcl]$ pwd
/u01/app/oradata/myorcl
[oracle@dayong myorcl]$ ls
control01.ctl control03.ctl new_ts.dbf redo02.log sysaux01.dbf temp01.dbf users01.dbf
control02.ctl example01.dbf redo01.log redo03.log system01.dbf undotbs01.dbf
[oracle@dayong myorcl]$ rm -rf new_ts.dbf --主庫操作
[oracle@dayong myorcl]$ pwd
/u01/app/oradata/myorcl
4)重新命名資料檔案
如果Primary資料庫重新命名了一個或多個資料檔案，該項修改並不會自動傳輸到Standby Database。就算設定了初始化引數STANDBY_FILE_MANAGEMENT等於AUTO也不行，要讓Standby的資料檔案與Primary 保持一致，只能手工操作。
示例如下：
SQL> CREATE TABLESPACE new_ts DATAFILE '/u01/app/oradata/myorcl/new_ts.dbf' SIZE 1m AUTOEXTEND ON MAXSIZE UNLIMITED;
Tablespace created. --首先在主資料庫建立一個實驗表空間
SQL> alter tablespace new_ts offline; --主資料庫操作，將要重新命名的表空間設定離線
Tablespace altered.
[oracle@dayong myorcl]$ ls
control01.ctl control03.ctl new_ts.dbf redo02.log sysaux01.dbf temp01.dbf users01.dbf
control02.ctl example01.dbf redo01.log redo03.log system01.dbf undotbs01.dbf
[oracle@dayong myorcl]$ mv new_ts.dbf new.dbf --主資料庫操作，手工修改資料檔名字
[oracle@dayong myorcl]$ pwd
/u01/app/oradata/myorcl
[oracle@dayong myorcl]$ ls
control01.ctl control03.ctl new.dbf redo02.log sysaux01.dbf temp01.dbf users01.dbf
control02.ctl example01.dbf redo01.log redo03.log system01.dbf undotbs01.dbf
SQL> ALTER TABLESPACE new_ts RENAME DATAFILE '/u01/app/oradata/myorcl/new_ts.dbf' TO '/u01/app/oradata/myorcl/new.dbf';
Tablespace altered. --主資料庫操作，透過命令修改資料字典中表空間路徑
SQL> ALTER TABLESPACE new_ts ONLINE; --主資料庫操作，將表空間設定為online
Tablespace altered.
SQL> select name,status from v$datafile; --主資料庫查詢當前資料庫表空間資訊
NAME STATUS
-------------------------------------------------- -------
/u01/app/oradata/myorcl/system01.dbf SYSTEM
/u01/app/oradata/myorcl/undotbs01.dbf ONLINE
/u01/app/oradata/myorcl/sysaux01.dbf ONLINE
/u01/app/oradata/myorcl/users01.dbf ONLINE
/u01/app/oradata/myorcl/example01.dbf ONLINE
/u01/app/oradata/myorcl/new.dbf ONLINE
6 rows selected.
SQL> ALTER SYSTEM SWITCH LOGFILE; --主資料庫切換日誌
System altered.
SQL> select name,status from v$datafile; --備用資料庫檢視當前資料庫下的資料檔案，可知主資料庫對資料檔案的修改並沒有同步
NAME STATUS
-------------------------------------------------- -------
/u01/app/oradata/myorcl/system01.dbf SYSTEM
/u01/app/oradata/myorcl/undotbs01.dbf ONLINE
/u01/app/oradata/myorcl/sysaux01.dbf ONLINE
/u01/app/oradata/myorcl/users01.dbf ONLINE
/u01/app/oradata/myorcl/example01.dbf ONLINE
/u01/app/oradata/myorcl/new_ts.dbf ONLINE
6 rows selected.
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL; -備用資料庫操作，暫停redo apply
Database altered.
SQL> shutdown immediate --關閉備庫
[oracle@xiaoru myorcl]$ ls
example01.dbf new_ts.dbf sysaux01.dbf system01.dbf temp01.dbf undotbs01.dbf users01.dbf
[oracle@xiaoru myorcl]$ mv new_ts.dbf new.dbf --備庫操作，手動修改資料檔案的名字
[oracle@xiaoru myorcl]$ ls
example01.dbf new.dbf sysaux01.dbf system01.dbf temp01.dbf undotbs01.dbf users01.dbf
[oracle@xiaoru myorcl]$ pwd
/u01/app/oradata/myorcl
SQL> STARTUP MOUNT; --啟動備庫至mount狀態
SQL> show parameter STANDBY_FILE_MANAGEMENT;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
standby_file_management string AUTO
SQL> alter system set STANDBY_FILE_MANAGEMENT=MANUAL; --需要將STANDBY_FILE_MANAGEMENT引數值設定為MANUAL
System altered.
SQL> show parameter STANDBY_FILE_MANAGEMENT;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
standby_file_management string MANUAL
SQL> alter database rename file '/u01/app/oradata/myorcl/new_ts.dbf' to '/u01/app/oradata/myorcl/new.dbf';
Database altered. --備庫操作，透過命令修改資料字典中的表空間路徑
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION; -設定備庫重新接收redo應用
Database altered.
SQL> select name,status from v$datafile; --備庫查詢操作
NAME STATUS
-------------------------------------------------- -------
/u01/app/oradata/myorcl/system01.dbf SYSTEM
/u01/app/oradata/myorcl/undotbs01.dbf ONLINE
/u01/app/oradata/myorcl/sysaux01.dbf ONLINE
/u01/app/oradata/myorcl/users01.dbf ONLINE
/u01/app/oradata/myorcl/example01.dbf ONLINE
/u01/app/oradata/myorcl/new.dbf ONLINE
6 rows selected.
5)新增或刪除線上日誌檔案
SQL> alter database recover managed standby database cancel; --備庫操作，取消redo apply
Database altered.
SQL> show parameter STANDBY_FILE_MANAGEMENT;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
standby_file_management string MANUAL
SQL> col member for a50;
SQL> select group#,member from v$logfile; --主庫操作，檢視當前資料庫的日誌檔案
GROUP# MEMBER
---------- --------------------------------------------------
3 /u01/app/oradata/myorcl/redo03.log
2 /u01/app/oradata/myorcl/redo02.log
1 /u01/app/oradata/myorcl/redo01.log
4 /u01/app/oradata/orcl/standbyrd01.log
5 /u01/app/oradata/orcl/standbyrd02.log
6 /u01/app/oradata/orcl/standbyrd03.log
6 rows selected.
SQL> ALTER DATABASE ADD LOGFILE group 7 '/u01/app/oradata/myorcl/redo04.log' size 50m; --新增主庫日誌檔案
Database altered.
SQL> alter database add standby logfile group 8 '/u01/app/oradata/orcl/standbyrd04.log' size 50m; --新增備庫日誌檔案
Database altered.
SQL> select group#,member from v$logfile;
GROUP# MEMBER
---------- --------------------------------------------------
3 /u01/app/oradata/myorcl/redo03.log
2 /u01/app/oradata/myorcl/redo02.log
1 /u01/app/oradata/myorcl/redo01.log
4 /u01/app/oradata/orcl/standbyrd01.log
5 /u01/app/oradata/orcl/standbyrd02.log
6 /u01/app/oradata/orcl/standbyrd03.log
7 /u01/app/oradata/myorcl/redo04.log
8 /u01/app/oradata/orcl/standbyrd04.log
8 rows selected.
SQL> col member for a50;
SQL> select group#,member from v$logfile; --備庫操作，檢視可知並沒有實現與主庫建立的日誌檔案實時同步，需要手動新增
GROUP# MEMBER
---------- --------------------------------------------------
3 /u01/app/oradata/myorcl/redo03.log
2 /u01/app/oradata/myorcl/redo02.log
1 /u01/app/oradata/myorcl/redo01.log
SQL> ALTER DATABASE ADD LOGFILE group 4 '/u01/app/oradata/myorcl/redo04.log' size 50m; --備庫操作
Database altered.
SQL> alter database add standby logfile group 5 '/u01/app/oradata/orcl/standbyrd04.log' size 50m; --備庫操作
Database altered.
SQL> alter database recover managed standby database using current logfile disconnect from session; --備庫接收redo apply
Database altered.
SQL> select group#,member from v$logfile; --備庫查詢
GROUP# MEMBER
---------- --------------------------------------------------
3 /u01/app/oradata/myorcl/redo03.log
2 /u01/app/oradata/myorcl/redo02.log
1 /u01/app/oradata/myorcl/redo01.log
4 /u01/app/oradata/myorcl/redo04.log
5 /u01/app/oradata/orcl/standbyrd04.log
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29634949/viewspace-1174134/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- ORACLE10g DataGuard 配置Physical Standby DatabaseOracleDatabase
- Oracle DG建立Physical Standby DatabaseOracleDatabase
- Oracle10G Physical Standby Database setupOracleDatabase
- oracle10g data guard(dg)__flashback_physical databaseOracleDatabase
- Convert a Physical Standby Database into a Snapshot Standby DatabaseDatabase
- 【DG】Data Guard搭建（physical standby）
- Physical Standby Database 切換到 Snapshot Standby DatabaseDatabase
- Creating a Physical Standby DatabaseDatabase
- Performing a Failover to a Physical Standby DatabaseORMAIDatabase
- Oracle DG Standby Database型別OracleDatabase型別
- Performing a Switchover to a Physical Standby Database and failoverORMDatabaseAI
- Oracle DG建立Logical Standby DatabaseOracleDatabase
- 同事總結的 ： 用RMAN建立Physical Standby DatabaseDatabase
- oracle 10g physical standby database creationOracle 10gDatabase
- Recover physical standby database after loss of archive log(2)DatabaseHive
- 配置oracle 9i physical standby database時，duplicate命令的執行記錄OracleDatabase
- 配置Oracle11g的Dataguard測試，建立物理備庫(Physical Standby Database)OracleDatabase
- oracle9204(physical dg)配置_指南Oracle
- oracle 9i physical standby database 中v$database switchover_status的含義OracleDatabase
- oracle 9i physical standby database狀態查詢OracleDatabase
- Using RMAN Incremental Backups to Roll Forward a Physical Standby DatabaseREMForwardDatabase
- Physical Standby上開啟flashback database實驗日誌Database
- Brief description of Oracle physical standby database configuration and managementOracleDatabase
- Brief description of Oracle physical standby database configuration and managemeOracleDatabase
- Recover physical standby database after loss of archive log – roll forward(轉)DatabaseHiveForward
- oracle 9i physical standby database 上的v$archived_logOracleDatabaseHive
- Oracle physical standbyOracle
- Creating a Physical Standby using RMAN DUPLICATE FROM ACTIVE DATABASEDatabase
- Oracle10g RAC配置standbyOracle
- 在Oracle DG Standby庫上啟用flashback database功能OracleDatabase
- Logical Standby Database的配置步驟.Database
- Step By Step Guide To Create Physical Standby Database Using RMAN [ID 469493.1]GUIIDEDatabase
- Oracle11g的Dataguard測試，建立物理備庫(Physical Standby Database)OracleDatabase
- DataGuard:Physical Standby Switchover
- Data Guard - Snapshot Standby Database配置Database
- Physical Database LimitsDatabaseMIT
- 【DataGuarad】ORA-1153 trying to turn on Flashback for Physical Standby DatabaseDatabase
- Changing Primary DB Character Set without Recreate DG Physical Standby_1124165.1