Dataguard架设记录

    前段时间花了一周时间，再加2个熬夜到2点的结果，记录一下。

 

   

1.判断DataGuard是否安装

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

2.网络配置

node1:

192.168.92.6(ole-11g02)-------192.168.92.5(ole-11g03)

3.监听配置

主库

[oracle@node1 ~]$ cd    /u01/app/oracle/product/11.2.0/dbhome_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)

    )

  )

 

 

 

 

 

 

 

 

 

 

 

备库

/u01/app/oracle/product/11.2.0/dbhome_1/network/admin

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

                

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 ASYNCVALID_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/oracle/product/11.2.0/dbhome_1/dbs/initorcl.ora

node2:/u01/app/oracle/product/11.2.0/dbhome_1/dbs/

 

scp –rp  /u01/app/oracle/product/11.2.0/db_1/dbs/initorcl.ora

   standby:/u01/app/oracle/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)

 

scp -rp  /u01/app/oracle/oradata node2: /u01/app/oradata

scp -rp  /u01/app/oracle/oradata     node2: /u01/app/oracle/oradata （）

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/

 

scp  -rp  /tmp/dg.ctl  node2:/u01/app/oracle/oradata/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;

 

 

 

---以上完成dg

 

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;

附：

1.  启动到管理模式

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;