Oracle-11G-DataGuard 一主库多备库详细配置

1  说明

基于网上的文档做了补充说明改动形成的文档。

2 判断DataGuard是否安装

select * from v$option where parameter = 'Oracle Data Guard';

3 网络配置

192.168.1.110---------------------------主库-------------实例名----ora11g(ora11g_PD)

192.168.1.111---------------------------备库-------------实例名----ora11g(ora11g_ST)

192.168.1.112---------------------------备库-------------实例名----ora11g(ora11g_SD)

4 监听配置

主库

[oracle@node1~]$ cd    /ora11g/oracle/product/11.2.0/db_1/network/admin

[oracle@node1]$ cat  listener.ora

 

# listener.ora Network Configuration File:/ora11g/oracle/product/11.2.0/db_1/network/admin/listener.ora

# Generated by Oracle configuration tools.

 

LISTENER =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

      (ADDRESS = (PROTOCOL =TCP)(HOST = node1)(PORT = 1521))

      (ADDRESS = (PROTOCOL = IPC)(KEY= EXTPROC1521))

    )

  )

 

ADR_BASE_LISTENER = /ora11g/oracle

SID_LIST_LISTENER =

  (SID_LIST =

    (SID_DESC =

      (GLOBAL_DBNAME = ora11g)

         (ORACLE_HOME =/ora11g/oracle/product/11.2.0/db_1)

          (SID_NAME = ora11g)

   )

 )

[oracle@node1]$  cat   tnsnames.ora

# tnsnames.ora Network Configuration File:/ora11g/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora

# Generated by Oracle configuration tools.

 

ORA11G_PD =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST= 192.168.1.110)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = ora11g)

    )

  )

 

 

ORA11G_ST =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL =TCP)(HOST = 192.168.1.111)(PORT = 1521))

    )

    (CONNECT_DATA =

      (SERVICE_NAME = ora11g)

    )

  )

 

ORA11G_SD =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL =TCP)(HOST = 192.168.1.112)(PORT = 1521))

    )

    (CONNECT_DATA =

      (SERVICE_NAME = ora11g)

    )

  )

 

备库1

[oracle@node2]$ cat  listener.ora

 

 

LISTENER =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

      (ADDRESS = (PROTOCOL =TCP)(HOST = node2)(PORT = 1521))

      (ADDRESS = (PROTOCOL = IPC)(KEY= EXTPROC1521))

    )

  )

 

ADR_BASE_LISTENER = /ora11g/oracle

 

SID_LIST_LISTENER =

 (SID_LIST =

 (SID_DESC =

 (GLOBAL_DBNAME = ora11g)

 (ORACLE_HOME = /ora11g/oracle/product/11.2.0/db_1)

 (SID_NAME = ora11g)

 )

 )

[oracle@node2]$  cat   tnsnames.ora

 

ORA11G_PD =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL =TCP)(HOST = 192.168.1.110)(PORT = 1521))

    )

    (CONNECT_DATA =

      (SERVICE_NAME = ora11g)

    )

  )

 

ORA11G_ST =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL =TCP)(HOST =192.168.1.111)(PORT = 1521))

    )

    (CONNECT_DATA =

      (SERVICE_NAME = ora11g)

    )

  )

 

ORA11G_SD =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL =TCP)(HOST =192.168.1.112)(PORT = 1521))

    )

    (CONNECT_DATA =

      (SERVICE_NAME = ora11g)

    )

  )

 

备库2

[oracle@node3]$ cat  listener.ora

 

 

LISTENER =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

      (ADDRESS = (PROTOCOL =TCP)(HOST = node3)(PORT = 1521))

      (ADDRESS = (PROTOCOL = IPC)(KEY= EXTPROC1521))

    )

  )

 

ADR_BASE_LISTENER = /ora11g/oracle

 

SID_LIST_LISTENER =

 (SID_LIST =

 (SID_DESC =

 (GLOBAL_DBNAME = ora11g)

 (ORACLE_HOME = /ora11g/oracle/product/11.2.0/db_1)

 (SID_NAME = ora11g)

 )

 )

[oracle@node3]$  cat   tnsnames.ora

 

ORA11G_PD =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL =TCP)(HOST = 192.168.1.110)(PORT = 1521))

    )

    (CONNECT_DATA =

      (SERVICE_NAME = ora11g)

    )

  )

 

ORA11G_ST =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL =TCP)(HOST =192.168.1.111)(PORT = 1521))

    )

    (CONNECT_DATA =

      (SERVICE_NAME = ora11g)

    )

  )

 

ORA11G_SD =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL =TCP)(HOST =192.168.1.112)(PORT = 1521))

    )

    (CONNECT_DATA =

      (SERVICE_NAME = ora11g)

    )

  )

 

 

 

5 主库前期准备

设置强制写日志

SQL>  select  FORCE_LOGGING  from  v$database;

NO

SQL> alter  database  force  logging;

SQL> select  FORCE_LOGGING  from  v$database;

YES           

6 创建口令文件

orapwd  file=$ORACLE_HOME/dbs/orapwora11g password=test  entries=5

7 修改主库初始化参数 

 创建主库pfile

 sql > create  pfile   from  spfile;

修改pfile

DB_UNIQUE_NAME='ORA11G_PD'

LOG_ARCHIVE_CONFIG='DG_CONFIG=(ORA11G_PD,ORA11G_ST,ORA11G_SD)'

LOG_ARCHIVE_DEST_1= 'LOCATION=/ora11g/oracle/archiveVALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=ORA11G_PD'

LOG_ARCHIVE_DEST_2= 'SERVICE=ORA11G_ST  LGWR SYNCVALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ORA11G_ST'

LOG_ARCHIVE_DEST_3= 'SERVICE=ORA11G_SD  LGWR SYNCVALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ORA11G_SD'

LOG_ARCHIVE_DEST_STATE_1=ENABLE

LOG_ARCHIVE_DEST_STATE_2=ENABLE

LOG_ARCHIVE_DEST_STATE_3=ENABLE

LOG_ARCHIVE_FORMAT=%t_%s_%r.arc

FAL_SERVER='ora11g_st,ora11g_sd'

FAL_CLIENT='ora11g_pd'

STANDBY_FILE_MANAGEMENT=AUTO

Pfile 拷贝到备库1、2上

[node1]$scp -rp /ora11g/oracle/product/11.2.0/db_1/dbs/initorcl.oranode2:/ora11g/oracle/product/11.2.0/db_1/dbs/

[node1]$scp -rp /ora11g/oracle/product/11.2.0/db_1/dbs/initorcl.oranode2:/ora11g/oracle/product/11.2.0/db_1/dbs/

 

8 修改数据库运行在归档模式下

SHUTDOWN  IMMEDIATE;

STARTUP  MOUNT;

ALTER  DATABASE  ARCHIVELOG;

ALTER  DATABASE OPEN;

9 创建备份库需要的控制文件

创建控制文件

Shutdown immediate

STARTUP  MOUNT;

ALTER  DATABASE  CREATE  STANDBY  CONTROLFILE  AS  '/ora11g/oracle/ora11g01.ctl';

ALTER  DATABASE OPEN;

创建主库spfile

Shutdown immediate

startuppfile='/ora11g/oracle/oracle/product/11.2.0/db_1/dbs/initora11g.ora'

sql>create spfile from  pfile='/ora11g/oracle/product/11.2.0/db_1/dbs/initora11g.ora'

;

(先把原来的干掉)

shutdown immediate;

startup

 

10 备份生产数据库

 scp -rp /ora11g/oracle/oradata/ORA11G node2:/u01/oradata/

scp -rp /ora11g/oracle/admin/ORA11G  node2:/ora11g/oracle/admin(记得在备库1、2创建admin)

11 修改备库1的pfile

 

DB_UNIQUE_NAME='ORA11G_ST'

LOG_ARCHIVE_CONFIG='DG_CONFIG=(ORA11G_PD,ORA11G_ST)'

LOG_ARCHIVE_DEST_1= 'LOCATION=/ora11g/oracle/archiveVALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=ORA11G_ST'

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

LOG_ARCHIVE_DEST_STATE_1=ENABLE

LOG_ARCHIVE_DEST_STATE_2=ENABLE

LOG_ARCHIVE_FORMAT=%t_%s_%r.arc

FAL_SERVER=ORA11G_PD

FAL_CLIENT=ORA11G_ST

STANDBY_FILE_MANAGEMENT=AUTO

 

12 修改备库2的pfile

DB_UNIQUE_NAME='ORA11G_SD'

LOG_ARCHIVE_CONFIG='DG_CONFIG=(ORA11G_PD,ORA11G_SD)'

LOG_ARCHIVE_DEST_1= 'LOCATION=/ora11g/oracle/archiveVALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=ORA11G_SD'

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

LOG_ARCHIVE_DEST_STATE_1=ENABLE

LOG_ARCHIVE_DEST_STATE_2=ENABLE

LOG_ARCHIVE_FORMAT=%t_%s_%r.arc

FAL_SERVER=ORA11G_PD

FAL_CLIENT=ORA11G_SD

STANDBY_FILE_MANAGEMENT=AUTO

13 将控制文件拷贝到备库1、2

[node1]$scp -rp /ora11g/oracle/ora11g01.ctlnode2:/ora11g/oracle/oradata/ORA11G/

[node1]$scp -rp /ora11g/oracle/ora11g01.ctlnode2:/ora11g/oracle/fast_recovery_area/orcl/controlfile/

 

[node1]$scp –rp  /ora11g/oracle/ora11g01.ctl node3:/ora11g/oracle/oradata/ORA11G/

[node1]$scp -rp /ora11g/oracle/ora11g01.ctlnode3:/ora11g/oracle/fast_recovery_area/orcl/controlfile/

 

14 在备库1、2上创建口令文件

orapwd  file=$ORACLE_HOME/dbs/orapwora11g password=oracle entries=5

15 在备库1、2上创建spfile 

SQL>Shutdown immediate;

SQL>startup;

SQL>pfile='/ora11g/oracle/oracle/product/11.2.0/db_1/dbs/initora11g.ora';

SQL>CREATE  SPFILE  FROM  PFILE;

16 启动物理备用数据库1、2

STARTUPMOUNT;

17 配置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 ('/ora11g/oracle/oracle/oradata/highfly/stdby_redo04.log') size50m,

   group 5  ('/ora11g/oracle/oracle/oradata/highfly/stdby_redo05.log')size 50m,

   group 6  ('/ora11g/oracle/oracle/oradata/highfly/stdby_redo06.log')size 50m,

   group 7 ('/ora11g/oracle/oracle/oradata/highfly/stdby_redo07.log') size50m;

18 Start Redo Apply

在备库

SQL>ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USINGCURRENT LOGFILE DISCONNECT FROM SESSION;

SQL>ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

SQL>alter user scott account lock;

查看哪些归档日志被APPLY了

在备库

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

在主库强制日志切换到当前的online redo log file.

SQL>ALTER  SYSTEM  ARCHIVE  LOG  CURRENT;

在备库查看新的被归档的redo data

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

在备库查看接收到的被应用的redo

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

查看数据库的角色

SQL>select database_role,protection_mode,protection_levelfrom v$database;

19 主备库切换

 

1.查看主库的状态

SQL> SELECTSWITCHOVER_STATUS FROM V$DATABASE;

2.将主库切换至备用模式

 

SQL> ALTERDATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH -

> SESSION SHUTDOWN;

 

3.关闭、装载主数据库

SQL> SHUTDOWNABORT;

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 SESSIONSHUTDOWN;

 

6.打开新的主数据库

 

SQL> ALTER DATABASEOPEN;

 

7. 在新的备库服务器上启动 REDO apply。

 SQL> ALTER DATABASE RECOVER MANAGED STANDBYDATABASE USING CURRENT LOGFILE -

> DISCONNECT FROMSESSION;

 

20 灾难恢复（failover）

 

Step 1

Flushany unsent redo from the primary database to the target standby

database

 

SQL> ALTER SYSTEMFLUSH REDO TO target_db_name;

 

Step 2

 Verify that the standby database has the mostrecently archived redo log

filefor each primary database redo thread.

 

SQL>SELECT UNIQUE THREAD# AS THREAD, MAX(SEQUENCE#) -

> OVER (PARTITION BYthread#) AS LAST from V$ARCHIVED_LOG;

 

SQL> ALTER DATABASEREGISTER PHYSICAL LOGFILE 'filespec1';

 

Step 3

Identifyand resolve any archived redo log gaps.

 

SQL> SELECT THREAD#,LOW_SEQUENCE#, HIGH_SEQUENCE# FROM V$ARCHIVE_GAP;

 

SQL> ALTER DATABASEREGISTER PHYSICAL LOGFILE 'filespec1';

 

Step 4

RepeatStep 3 until all gaps are resolved.

 

Step 5

StopRedo Apply.

Issuethe following SQL statement on the target standby database:

SQL> ALTER DATABASERECOVER MANAGED STANDBY DATABASE CANCEL;

 

Step 6

Finishapplying all received redo data.

Issuethe following SQL statement on the target standby database:

SQL> ALTER DATABASERECOVER MANAGED STANDBY DATABASE FINISH;

 

Step 7

Verifythat the target standby database is ready to become a primary

database.

 

Step 8

Switchthe physical standby database to the primary role.

Issuethe following SQL statement on the target standby database:

SQL> ALTER DATABASECOMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN;

 

Step 9

Openthe new primary database.

SQL> ALTER DATABASEOPEN;

 

Step10

Backup the new primary database.

Oracle recommendsthat a full backup be taken of the new primary database.

 

Step11

RestartRedo Apply if it has stopped at any of the other physical standby

databasesin your Data Guard configuration.

Forexample:

SQL> ALTERDATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE -

> DISCONNECT FROMSESSION;

附：

1. 启动到管理模式

SQL>shutdown immediate;

SQL>startup nomount;

SQL>alter database mount standby database;

SQL>alter database recover managed standbydatabase 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;