Creating a Physical Standby Database

一、在备机上创建备库

通过冷备份数据库文件拷贝的方式，在备机上创建一个与原库数据库结构一致的数据库，具体方法如下：

将数据库所必需的文件，如数据文件、初始化参数文件、redo日志文件、密码文件等拷贝到备库相应的目录中，目录需要和初始化参数文件中指定的路径保持一致。其中控制文件需要使用standby控制文件，生成命令如下：

> alter database create standbycontrolfile as '/tmp/controlfile.standby';

将此控制文件拷贝到初始化参数文件指定目录中，并替换掉之前的控制文件。

至此，数据库备库创建完毕。

二、修改主备库的监听文件，确保二者的数据库能够互相访问以获取对方的日志文件

修改主备库的listener.ora文件和tnsnames.ora文件，保证listener.ora同时监听本机的库和备机的库。

具体事例如下（仅供参考）：

主（备）库lintener.ora文件：

LISTENER=

 (DESCRIPTION=

   (ADDRESS_LIST=

     (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.97)(PORT=1521))))

SID_LIST_LISTENER=

 (SID_LIST=

   (SID_DESC=

     (GLOBAL_DBNAME=standby)

     (ORACLE_HOME=/apps/oracle/product/10.2.0/)

     (SID_NAME=credit))

   (SID_DESC=

     (GLOBAL_DBNAME=primary)

     (ORACLE_HOME=/apps/oracle/product/10.2.0/)

     (SID_NAME=credit))

)

主（备）库tnsnames.ora文件：

credit_52 =

 (DESCRIPTION =

   (ADDRESS_LIST =

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

    )

   (CONNECT_DATA =

     (SERVICE_NAME = standby)

     (SERVER=dedicated)

    )

  )

 

credit_97 =

 (DESCRIPTION =

   (ADDRESS_LIST =

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

    )

   (CONNECT_DATA =

     (SERVICE_NAME = primary)

     (SERVER=dedicated)

    )

  )

 

三、修改初始化参数文件，这里采用修改pfile的方式

修改主备库的初始化参数文件，主要增加如下几个参数：

DB_UNIQUE_NAMELOG_ARCHIVE_CONFIGLOG_ARCHIVE_DEST_1LOG_ARCHIVE_DEST_2FAL_SERVERFAL_CLIENTDB_FILE_NAME_CONVERTLOG_FILE_NAME_CONVERTSTANDBY_FILE_MANAGEMENT参考事例：主库DB_UNIQUE_NAME=primaryLOG_ARCHIVE_CONFIG='DG_CONFIG=(primary,standby)'log_archive_dest_1='LOCATION=/data/archivelog VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=primary'log_archive_dest_2='SERVICE=credit_52 LGWR SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=standby'FAL_SERVER=standbyFAL_CLIENT=primarySTANDBY_FILE_MANAGEMENT=AUTO备库DB_UNIQUE_NAME=standbyLOG_ARCHIVE_CONFIG='DG_CONFIG=(standby,primary)'log_archive_dest_1='LOCATION=/data/archivelog VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=standby'log_archive_dest_2='SERVICE=credit_97 LGWR SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=primary'FAL_SERVER=primaryFAL_CLIENT=standbySTANDBY_FILE_MANAGEMENT=AUTOdb_file_name_convert='/data/oradata/credit','/data/oradata/credit'log_file_name_convert='/data/oradata/credit','/data/oradata/credit' 四、在主、备库上增加standby redolog file

在主备库上增加比原redo日志数量增加一个的standby redo日志文件，具体命令如下：

>alter database add standby logfilegroup 4 '/u01/app/oracle/oradata/stand/redo04.log' size 50m;

注意group#不要与之前的冲突

五、开库验证DG

以上配置完成之后，对DG进行验证：

首先将主库启动到open状态，主库需要打开force logging状态，备库启动到mount状态，在主库执行

> alter system switch logfile;

并打开备库的告警日志文件，查看是否传输过去，如下即为搭建成功：

Fri Aug 29 18:53:51 CST 2014

Primary database is in MAXIMUM PERFORMANCEmode

RFS[2]: Successfully opened standby log 4:'/data/oradata/credit/redo04.log'

Fri Aug 29 18:53:52 CST 2014

Media Recovery Log/data/archivelog/1_14_856871410.dbf

Media Recovery Waiting for thread 1sequence 15 (in transit)

由于此DG采用了最大可用模式，因此如果需要实时在备库上应用日志，则需要执行以下命令：

> alter database recover managed standbydatabase disconnect from session;

 

PS: upgrading the protection mode,

Step 1   If you are upgrading the protection mode, perform this step.

Perform this step only if you are upgrading the protection mode (for example, from maximum performance to maximum availability mode). Otherwise, go to Step 3.

Assume this example is upgrading the Data Guard configuration from the maximum performance mode to the maximum availability mode. Shut down the primary database and restart it in mounted mode:

SQL> SHUTDOWN IMMEDIATE; SQL> STARTUP MOUNT;

For a Real Application Clusters database, shut down all of the primary instances but start and mount only one primary instance.

Step 2   Set the data protection mode.

To specify a data protection mode, issue the SQL ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE {PROTECTION | AVAILABILITY | PERFORMANCE} statement on the primary database. For example, the following statement specifies the maximum availability mode:

SQL> ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE AVAILABILITY;

Step 3   Open the primary database.

If you performed Step 1 to upgrade the protection mode, open the database:

SQL> ALTER DATABASE OPEN;

If you are downgrading the protection mode, the database will already be open.

Step 4   Configure the LOG_ARCHIVE_DEST_n parameters on standby databases.

On the standby databases, configure the LOG_ARCHIVE_DEST_n parameter attributes so the configuration can continue to operate in the new protection mode after a switchover.

For example:

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=boston 2> OPTIONAL LGWR SYNC AFFIRM 3> VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) 4> DB_UNIQUE_NAME=boston';

Step 5   Confirm the configuration is operating in the new protection mode.

Query the V$DATABASE view to confirm the Data Guard configuration is operating in the new protection mode. For example:

SQL> SELECT PROTECTION_MODE, PROTECTION_LEVEL FROM V$DATABASE; PROTECTION_MODE PROTECTION_LEVEL --------------------- --------------------- MAXIMUM AVAILABILITY MAXIMUM AVAILABILITY