DataGuard配置说明-优快云博客

本文详细介绍了Oracle物理备用数据库的配置步骤，包括设置强制日志、创建密码文件、配置备用重做日志等关键环节，并提供了数据库初始化参数的示例。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

1. Enable Forced Logging 强制日志主数据库
force logging(强制日志)模式alter database force logging来使得Oracle无论什么操作都进行redo的写入,通过select force_logging from v$database可以看到当前数据库强制日志模式的状态
SQL> ALTER DATABASE FORCE LOGGING;(ALTER DATABASE no FORCE LOGGING;)

2. Create a Password File

orapwd file=/u01/app/oracle/product/10.2.0/db_1/dbs/orapworcl password=oracle entries=30 force=y;

3. Configure a Standby Redo Log（创建多一个日子）

SQL>ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 '/u01/app/oracle/oradata/primary/redo4.log') SIZE 5M;

SQL>ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 '/u01/app/oracle/oradata/primary/redo5.log') SIZE 5M;

SQL>ALTER DATABASE ADD STANDBY LOGFILE GROUP 6 '/u01/app/oracle/oradata/primary/redo6.log') SIZE 5M;

SQL>ALTER DATABASE ADD STANDBY LOGFILE GROUP 7 '/u01/app/oracle/oradata/primary/redo7.log') SIZE 5M;

SELECT GROUP#,THREAD#,SEQUENCE#,ARCHIVED,STATUS FROM V$STANDBY_LOG;
SQL> select group#,type,member from v$logfile;

4. Set Primary Database Initialization Parameters

DB_NAME=orcl

DB_UNIQUE_NAME=primary 注意：加唯一名字

LOG_ARCHIVE_CONFIG='DG_CONFIG=(primary,standby)'

LOG_ARCHIVE_DEST_1='LOCATION=/u01/app/oracle/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=primary'

LOG_ARCHIVE_DEST_2='SERVICE=standby LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=standby' 网络服务名

LOG_ARCHIVE_DEST_STATE_1=ENABLE

LOG_ARCHIVE_DEST_STATE_2=ENABLE

LOG_ARCHIVE_FORMAT=%t_%s_%r.arc

FAL_SERVER=standby

FAL_CLIENT=primary

DB_FILE_NAME_CONVERT='standby','primary' 如果路径一样就不需要这个参数了

LOG_FILE_NAME_CONVERT='/u01/app/oracle/arch/','/u01/app/oracle/arch/' 如果路径一样就不需要这个参数了

STANDBY_FILE_MANAGEMENT=AUTO

5. Enable Archiving

SQL> SHUTDOWN IMMEDIATE;

SQL> STARTUP MOUNT;

SQL> ALTER DATABASE ARCHIVELOG;

SQL> ALTER DATABASE OPEN;

6. Create a Backup Copy of the Primary Database Datafiles

SQL>SHUTDOWN IMMEDIATE

scp datafile

7. Create a Control File for the Standby Database

SQL> STARTUP MOUNT;

SQL> ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/tmp/standby.ctl';

SQL> ALTER DATABASE OPEN;

在备用数据库下作

8. Prepare an Initialization Parameter File for the Standby Database 修改参数文件

DB_NAME=orcl

DB_UNIQUE_NAME=standby

CONTROL_FILE='/../../standby.ctl' LOG_ARCHIVE_CONFIG='DG_CONFIG=(primary,standby)'

LOG_ARCHIVE_DEST_1='LOCATION=/u01/app/oracle/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=standby' 倒过来了

LOG_ARCHIVE_DEST_2='SERVICE=primary LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=primary'

LOG_ARCHIVE_DEST_STATE_1=ENABLE

LOG_ARCHIVE_DEST_STATE_2=ENABLE

LOG_ARCHIVE_FORMAT=%t_%s_%r.arc

FAL_SERVER=standby

FAL_CLIENT=primary

DB_FILE_NAME_CONVERT='standby','primary'倒过来了

LOG_FILE_NAME_CONVERT='/u01/app/oracle/arch/','/u01/app/oracle/arch/'

STANDBY_FILE_MANAGEMENT=AUTO

9. Copy Files from the Primary System to the Standby System
包括所有目录结构~~~主库生成的standby files等等

10. Configure listeners for the primary and standby databases

primary 数据库
vi listener.ora
SID_LIST_LISTENER =
(SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = orcl)
      (ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1)
      (SID_NAME = ORCL)
    )
)

LISTENER =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = stu134.uplooking.com)(PORT = 1521))
)

vi tnsnames.ora
standby =
(DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = stu195.uplooking.com)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
)

standby 数据库

vi listener.ora
SID_LIST_LISTENER =
(SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = orcl)
      (ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1)
      (SID_NAME = orcl)
    )
)

LISTENER =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = stu195.uplooking.com)(PORT = 1521))
)

vi tnsnames.ora
primary =
(DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = stu134.uplooking.com)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
)
)

11. Start the Physical Standby Database启动物理备用数据库到mount

SQL> STARTUP MOUNT;

12. Start Redo Apply

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION; 变为后台进程DISCONNECT (开始接收主库传输redo数据)

over

13. Verify the Physical Standby Database Is Performing Properly

Step 1 Identify the existing archived redo log files in standby database

SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

Step 2 Force a log switch to archive the current online redo log file in primary database

SQL> ALTER SYSTEM SWITCH LOGFILE;

Step 3 Verify the new redo data was archived on the standby database.

SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME
FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

Step 4 Verify new archived redo log files were applied on the standby database.

SQL> SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG
ORDER BY SEQUENCE#;

standby 查看命令
====================================================
= startup nomount
= alter database mount standby database;
= select name,database_role from v$database;
= alter database recover managed standby database disconnect from session; 接收日志
= alter system archive log current;
= alter database recover managed standby database cancel; 取消接收日志
= alter database open read only;
= select sequence#,first_time,next_time from v$archived_log order by sequence#;
= select sequence#,applied from v$archived_log order by sequence#;
=
====================================================
switchover

in primary database

1 select switchover_status from v$database;

2 to_standby

alter database commit to switchover to physical standby;

session active

alter database commit to switchover to physical standby with session shutdown;

3 shutdown immediate

startup mount

in standby database

1 select switchover_status from v$database;

2 alter database commit to switchover to primary;

alter database commit to switchover to primary with session shutdown;

3 alter database open

shutdown immediate

startup
主动切换后以前的主库变为从库,状态为需要恢复,要恢复之后才能接收现在主库的日志

保护数据库通过日志的传输
物理备用：按快恢复的（起恢复进程）应用日志必须在mount下，不作恢复可以在open read only，可以备份
逻辑备用：在日志中挖掘出的sql恢复的在备用数据库上执行的叫逻辑备用
需要有数据库角色管理
角色的切换（不是自动的）
三中保护模式

Maximum protection最大保护模式：不会丢失数据

数据库的版本必须一致，一定要归档模式，
RFS远程文件服务器
MRP管理恢复进程
LSP逻辑服务器进程
FAL让日志连续起来可以手动拷贝
日志文件数量必须>=主数据库的日志
测试结果：
在primary数据库查询结果如下：是双份的才算配置成功
SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
SEQUENCE# FIRST_TIM NEXT_TIME
---------- --------- ---------
         1 15-APR-11 15-APR-11
         1 15-APR-11 15-APR-11
         2 15-APR-11 15-APR-11
         2 15-APR-11 15-APR-11
         3 15-APR-11 15-APR-11
         3 15-APR-11 15-APR-11
         4 15-APR-11 15-APR-11
         4 15-APR-11 15-APR-11
         5 15-APR-11 15-APR-11
         5 15-APR-11 15-APR-11
         6 15-APR-11 15-APR-11

SEQUENCE# FIRST_TIM NEXT_TIME
---------- --------- ---------
         6 15-APR-11 15-APR-11
         7 15-APR-11 15-APR-11
         7 15-APR-11 15-APR-11
         8 15-APR-11 15-APR-11
         8 15-APR-11 15-APR-11
         9 15-APR-11 15-APR-11
         9 15-APR-11 15-APR-11
        10 15-APR-11 15-APR-11
        10 15-APR-11 15-APR-11
        11 15-APR-11 15-APR-11
        11 15-APR-11 15-APR-11

SEQUENCE# FIRST_TIM NEXT_TIME
---------- --------- ---------
        12 15-APR-11 15-APR-11
        12 15-APR-11 15-APR-11
        13 15-APR-11 15-APR-11
        13 15-APR-11 15-APR-11

26 rows selected.

在standby库查询结果如下
SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

SEQUENCE# FIRST_TIM NEXT_TIME
---------- --------- ---------
         1 15-APR-11 15-APR-11
         2 15-APR-11 15-APR-11
         3 15-APR-11 15-APR-11
         4 15-APR-11 15-APR-11
         5 15-APR-11 15-APR-11
         6 15-APR-11 15-APR-11
         7 15-APR-11 15-APR-11
         8 15-APR-11 15-APR-11
         9 15-APR-11 15-APR-11
        10 15-APR-11 15-APR-11
        11 15-APR-11 15-APR-11

SEQUENCE# FIRST_TIM NEXT_TIME
---------- --------- ---------
12 15-APR-11 15-APR-11
13 15-APR-11 15-APR-11

13 rows selected.

SQL>

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/24531354/viewspace-696893/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/24531354/viewspace-696893/