用文件系统创建physical standby

一、primary database 的准备工作

1、启用 force loggong

SQL> alter database force logging;

Database altered.

2、创建password file

查看primary database 是否存在passwordfile，没有要进行手动创建。

3、配置standby redo log

确定loggroup的数量及logfile大小

SQL> select group#,bytes from v$log;

    GROUP#        BYTES

---------- ----------

 1   52428800

 2   52428800

 3   52428800

*建议standby redo log group的数量为集群环境中日志组数最大的+1之后再乘以节点的个数

*standby redo log大小要和primary的一样

SQL> alter database add standby logfile group 4

  2  ('/u01/app/oracle/oradata/dg1/redo04.log') size 50m;

Database altered.

SQL> alter database add standby logfile group 5

  2  ('/u01/app/oracle/oradata/dg1/redo05.log') size 50m;

Database altered.

SQL> alter database add standby logfile group 6

  2  ('/u01/app/oracle/oradata/dg1/redo06.log') size 50m;

Database altered.

SQL> alter database add standby logfile group 7

  2  ('/u01/app/oracle/oradata/dg1/redo07.log') size 50m;

4、修改primary database的参数文件。

Primary role initialization parameters

DB_NAME=chicago
DB_UNIQUE_NAME=chicago
LOG_ARCHIVE_CONFIG='DG_CONFIG=(dg1,dg2)'
CONTROL_FILES=LOG_ARCHIVE_DEST_1=
 'LOCATION=/arch1/chicago/
  VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
  DB_UNIQUE_NAME=chicago'
LOG_ARCHIVE_DEST_2=
 'SERVICE=boston LGWR ASYNC
  VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
  DB_UNIQUE_NAME=boston'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
LOG_ARCHIVE_MAX_PROCESSES=30

Standby role initialization parameters

FAL_SERVER=boston
FAL_CLIENT=chicago
DB_FILE_NAME_CONVERT='boston','chicago'
LOG_FILE_NAME_CONVERT=
 '/arch1/boston/','/arch1/chicago/','/arch2/boston/','/arch2/chicago/'
STANDBY_FILE_MANAGEMENT=AUTO
5、启用归档模式

SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT;
SQL> ALTER DATABASE ARCHIVELOG;
SQL> ALTER DATABASE OPEN;
二、创建一个physical standby database

1、为primary database创建一个备份

可以使用rman进行备份

2、为standby database 创建control file

SQL> startup mount;

ORACLE instance started.

Total System Global Area  285212672 bytes

Fixed Size                    1218992 bytes

Variable Size                   96470608 bytes

Database Buffers          184549376 bytes

Redo Buffers                    2973696 bytes

Database mounted.

SQL> alter database create standby controlfile as '/u01/app/tmp/dg2.ctl';

Database altered.

SQL> alter database open;

Database altered.

3、为standby database创建initialization parameter file

在主库上操作

SQL> alter database open;

Database altered.

SQL> show parameter pfile

NAME                                     TYPE         VALUE

------------------------------------ ----------- ------------------------------

spfile                                     string         /u01/app/oracle/product/10.2.0

 .1/db_1/dbs/spfiledg1.ora

SQL> create pfile='/u01/app/oracle/product/10.2.0.1/db_1/dbs/initdg2.ora' from spfile;

File created.

修改physical standby database的参数文件

DB_NAME=chicago
DB_UNIQUE_NAME=boston
LOG_ARCHIVE_CONFIG='DG_CONFIG=(chicago,boston)'
CONTROL_FILES='/arch1/boston/control1.ctl', '/arch2/boston/control2.ctl'
DB_FILE_NAME_CONVERT='chicago','boston'
LOG_FILE_NAME_CONVERT=
 '/arch1/chicago/','/arch1/boston/','/arch2/chicago/','/arch2/boston/'
LOG_ARCHIVE_FORMAT=log%t_%s_%r.arc
LOG_ARCHIVE_DEST_1=
 'LOCATION=/arch1/boston/
  VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
  DB_UNIQUE_NAME=boston'
LOG_ARCHIVE_DEST_2=
 'SERVICE=chicago LGWR ASYNC
  VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
  DB_UNIQUE_NAME=chicago'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
STANDBY_FILE_MANAGEMENT=AUTO
FAL_SERVER=chicago
FAL_CLIENT=boston

4、将数据文件拷贝到standby database

5、创建standby database的环境

创建口令文件

[oracle@dg1 dbs]$ orapwd file=orapwdg2 password=zhou689689 entries=5

创建spfile（备库）

SQL> create spfile from pfile;

将备库启动到mount状态

SQL> startup mount;

ORACLE instance started.

Total System Global Area  285212672 bytes

Fixed Size                    1218992 bytes

Variable Size                   92276304 bytes

Database Buffers          188743680 bytes

Redo Buffers                    2973696 bytes

Database mounted.

启用日志应用

SQL> alter database recover managed standby database disconnect from session;

停止日志应用

SQL> alter database recover managed standby database cancel;

Database altered.

此时依然从主库接收日志，但是暂不应用。

将备库以read only方式打开

SQL> alter database open read only;

Database altered.

查看备库日志应用情况

SQL>  select  sequence#,applied from v$archived_log;

 SEQUENCE# APP

---------- ---

 5 YES

 7 YES

 8 YES

 4 YES

 9 YES

10 YES

 1 YES

 3 YES

 6 YES

 2 YES

11 YES

 SEQUENCE# APP

---------- ---

12 YES

13 YES

SQL> select NAME,OPEN_MODE,DATABASE_ROLE,SWITCHOVER_STATUS,DB_UNIQUE_NAME,PROTECTION_MODE from v$database;

NAME          OPEN_MODE  DATABASE_ROLE    SWITCHOVER_STATUS    DB_UNIQUE_NAME                  PROTECTION_MODE

--------- ---------- ---------------- -------------------- ------------------------------ --------------------

DG1          MOUNTED    PHYSICAL STANDBY NOT ALLOWED           dg2                                  UNPROTECTED

6、监视log file 的归档信息

主库：

SQL> select thread#,sequence#,archived,status from v$log;

   THREAD#  SEQUENCE# ARC STATUS

---------- ---------- --- ----------------

 1           13 YES INACTIVE

 1           14 YES ACTIVE

 1           15 NO  CURRENT

SQL> select max(sequence#) from v$archived_log;

MAX(SEQUENCE#)

--------------

    14

检查那个归档是最近传送到standby的

SQL> select destination,status,archived_thread#,archived_seq# from v$archive_dest_status

  2  where status <> 'DEFERRED' and status <> 'INACTIVE';

DESTINATION             STATUS    ARCHIVED_THREAD# ARCHIVED_SEQ#

-------------------- --------- ---------------- -------------

/arch1/dg1/             VALID                      1            14

dg2                     VALID                      1            13

检查是否存在归档不一致的日志

SQL> select local.thread#,local.sequence# from (select thread#,sequence# from v$archived_log where dest_id=2) local where local.sequence# not in (select sequence# from v$archived_log where dest_id=1 and thread#=local.thread#);

   THREAD#  SEQUENCE#

---------- ----------

 1           15

7、从maximun performance 升级到 maximun availability  模式

7、1关闭主库，启动到mount状态

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount;

ORACLE instance started.

Total System Global Area  285212672 bytes

Fixed Size                    1218992 bytes

Variable Size                   79693392 bytes

Database Buffers          201326592 bytes

Redo Buffers                    2973696 bytes

Database mounted.

7.2更改保护级别

SQL> alter database set standby database to maximize availability;

Database altered.

7.3打开主库（如果你是在向下调保护级别，此时数据库已经是打开状态）

SQL> alter database open;

Database altered.

7.4在备库上更改参数log_archive_dest_n以保证新的保护级别在进行了switchover后还能生效。

SQL> alter system set log_archive_dest_2='service=dg1 optional lgwr sync affirm valid_for=(online_logfiles,primary_role) db_unique_name=dg1';

System altered.

7.5在主库验证保护级别

SQL> SELECT PROTECTION_MODE, PROTECTION_LEVEL FROM V$DATABASE;

PROTECTION_MODE      PROTECTION_LEVEL

-------------------- --------------------

MAXIMUM AVAILABILITY MAXIMUM AVAILABILITY

建议在主库更改log_archive_dest_2参数 符合maximum availability模式。