1．判断DataGuard是否安装
select * from v$option where parameter = 'Oracle Data Guard';
2. 网络配置
192.168.1.10（orcl）------------------------------------192.168.1.20(dg)
3.监听配置
主库
[oracle@node1 ~]$ cd /u01/app/product/11.2.0/db_1/network/admin
[oracle@node1 admin]$ cat listener.ora
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = node1)(PORT = 1521))
)
)
[oracle@node1 admin]$ cat tnsnames.ora
ORCL =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = node1)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = orcl)
)
)
DG =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = node2)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = dg)
)
)
备库
[oracle@node1 admin]$ cat listener.ora
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = node2)(PORT = 1521))
)
)
[oracle@node1 admin]$ cat tnsnames.ora
ORCL =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = node1)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = orcl)
)
)
DG =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = node2)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = dg)
)
)
4.主库前期准备
设置强制写日志
SQL> select FORCE_LOGGING from v$database;
NO
SQL> alter database force logging;
SQL> select FORCE_LOGGING from v$database;
YES
5. 创建口令文件
orapwd file=$ORACLE_HOME/dbs/orapwdb1 password=oracle entries=5
6.修改主库初始化参数
创建主库pfile
sql > create pfile from spfile;
修改pfile
DB_UNIQUE_NAME=orcl
LOG_ARCHIVE_CONFIG='DG_CONFIG=(orcl,dg)'
LOG_ARCHIVE_DEST_1= 'LOCATION=/u01/app/archive VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=orcl'
LOG_ARCHIVE_DEST_2= 'SERVICE=dg LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=dg'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
FAL_SERVER=dg
FAL_CLIENT=orcl
STANDBY_FILE_MANAGEMENT=AUTO
Pfile 拷贝到备库上
scp –rp /u01/app/product/11.2.0/db_1/dbs/initorcl.ora node2:/u01/app/product/11.2.0/db_1/dbs/
7.修改数据库运行在归档模式下
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE ARCHIVELOG;
ALTER DATABASE OPEN;
8. 创建备份库需要的控制文件
创建控制文件
Shutdown immediate
STARTUP MOUNT;
ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/tmp/orcl.ctl';
ALTER DATABASE OPEN;
创建主库pfile
Shutdown immediate
startup pfile='//u01/app/oracle/product/11.2.0/dbhome_1/dbs/20011.ora'
sql> create spfile from pfile='//u01/app/oracle/product/11.2.0/dbhome_1/dbs/20011.ora'
;
(先把原来的干掉)
shutdown immediate;
startup
9. 备份生产数据库
scp -rp /u01/oradata/ORCL node2:/u01/oradata/
scp –rp /u01/app/admin/orcl node2:/u01/app/admin(记得在备库创建admin)
10.修改备库pfile
DB_UNIQUE_NAME=orcl
LOG_ARCHIVE_CONFIG='DG_CONFIG=(orcl,dg)'
LOG_ARCHIVE_FORMAT=log%t_%s_%r.arc
LOG_ARCHIVE_DEST_1=
'LOCATION=/u01/app/archive
VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
DB_UNIQUE_NAME=dg'
LOG_ARCHIVE_DEST_2=
'SERVICE=orcl ASYNC
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
DB_UNIQUE_NAME=orcl'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
FAL_SERVER=orcl
FAL_CLIENT=dg
STANDBY_FILE_MANAGEMENT=AUTO
11.将控制文件
scp -rp /tmp/dg.ctl node2:/u01/oradata/ORCL/controlfile/
scp -rp /tmp/dg.ctl node2:/u01/flash_recovery_area/orcl/controlfile/
12.在备库上创建口令文件
orapwd file=$ORACLE_HOME/dbs/orapwdb1 password=oracle entries=5
13.在备库上创建spfile
Shutdown immediate
startup pfile='//u01/app/oracle/product/11.2.0/dbhome_1/dbs/20011.ora'
CREATE SPFILE FROM PFILE;
14.启动物理备用数据库
STARTUP MOUNT;
15．配置Standby Redo Log
在两边都配置standby redo log
在主库查看日志组的数量和每个日志文件的大小
SQL> SELECT GROUP#, BYTES FROM V$LOG;
在备库库查看日志组的数量和每个日志文件的大小
SQL> SELECT GROUP#, BYTES FROM V$STANDBY_LOG;
创建日志组和redo log文件
SQL> ALTER DATABASE ADD STANDBY LOGFILE group 4('/oracle/dbs/slog1.rdo') SIZE 50M;
SQL> ALTER DATABASE ADD STANDBY LOGFILE group 5 ('/oracle/dbs/slog2.rdo') SIZE 50M;
SQL>ALTER DATABASE ADD STANDBY LOGFILE group 6 ('/oracle/dbs/slog3.rdo') SIZE 50M;
SQL> ALTER DATABASE ADD STANDBY LOGFILE group 7('/oracle/dbs/slog4.rdo') SIZE 50M;
16. Start Redo Apply
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;
查看哪些归档日志被APPLY了
在备库
SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
在主库强制日志切换到当前的online redo log file.
ALTER SYSTEM ARCHIVE LOG CURRENT;
在备库查看新的被归档的redo data
SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
在备库查看接收到的被应用的redo
SQL> SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
查看数据库的角色
select database_role,protection_mode,protection_level from v$database;
l 主备库切换
1.查看主库的状态
SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;
2.将主库切换至备用模式
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH -
> SESSION SHUTDOWN;
3.关闭、装载主数据库
SQL> SHUTDOWN ABORT;
SQL> STARTUP MOUNT;
4.查看备库准备向主库模式切换
SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;
SWITCHOVER_STATUS
-----------------
TO_PRIMARY
1 row selected
5.切换备库至主库模式
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN;
6.打开新的主数据库
SQL> ALTER DATABASE OPEN;
7. 在新的备库服务器上启动 REDO apply。
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE -
> DISCONNECT FROM SESSION;
l 灾难恢复（failover）
Step 1 Flush any unsent redo from the primary database to the target standby
database
SQL> ALTER SYSTEM FLUSH REDO TO target_db_name;
Step 2 Verify that the standby database has the most recently archived redo log
file for each primary database redo thread.
SQL> SELECT UNIQUE THREAD# AS THREAD, MAX(SEQUENCE#) -
> OVER (PARTITION BY thread#) AS LAST from V$ARCHIVED_LOG;
SQL> ALTER DATABASE REGISTER PHYSICAL LOGFILE 'filespec1';
Step 3 Identify and resolve any archived redo log gaps.
SQL> SELECT THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE# FROM V$ARCHIVE_GAP;
SQL> ALTER DATABASE REGISTER PHYSICAL LOGFILE 'filespec1';
Step 4 Repeat Step 3 until all gaps are resolved.
Step 5 Stop Redo Apply.
Issue the following SQL statement on the target standby database:
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
Step 6 Finish applying all received redo data.
Issue the following SQL statement on the target standby database:
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH;
Step 7 Verify that the target standby database is ready to become a primary
database.
Step 8 Switch the physical standby database to the primary role.
Issue the following SQL statement on the target standby database:
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN;
Step 9 Open the new primary database.
SQL> ALTER DATABASE OPEN;
Step 10 Back up the new primary database.
Oracle recommends that a full backup be taken of the new primary database.
Step 11 Restart Redo Apply if it has stopped at any of the other physical standby
databases in your Data Guard configuration.
For example:
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE -
> DISCONNECT FROM SESSION;
附：
- 启动到管理模式
SQL>shutdown immediate;
SQL>startup nomount;
SQL>alter database mount standby database;
SQL>alter database recover managed standby database disconnect from session;
2.启动到只读方式
SQL>shutdown immediate;
SQL>startup nomount;
SQL>alter database mount standby database;
SQL>alter database open read only;
3如果在管理恢复模式下到只读模式
SQL> recover managed standby database cancel;
SQL> alter database open read only;