data guard搭建

搭建data guard

主库：hostname（TESTDB2)       db_name （ DG2）   db_unique_name （DG2) 

备库：hostname（TESTDB1)      db_name（DG1)       db_unique_name（DG1)

 tnsnames.ora内容，主库和备库一样

DG2 =

  (DESCRIPTION =

    (ADDRESS_LIST =

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

    )

    (CONNECT_DATA =

      (SERVICE_NAME = DG2)

    )

  )

DG1 =

  (DESCRIPTION =

    (ADDRESS_LIST =

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

    )

    (CONNECT_DATA =

      (SERVICE_NAME = DG1)

    )

  )

监听各自配置各自的

备库

LISTENER =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

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

    )

  )

主库

LISTENER =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

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

    )

  )

确认主库是归档模式

SQL> archive log list;

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence     72

Next log sequence to archive   74

Current log sequence           74

如果不是归档模式使用以下步骤开启归档

SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT;
SQL> ALTER DATABASE ARCHIVELOG;
SQL> ALTER DATABASE OPEN;

确认是否启用force logging

SQL> select force_logging from v$database;

FOR
---
YES

没有启用的话启用

SQL> alter database force logging;

关闭主数据库，起到mount状态添加standby logfile，添加的大小要与主库的logfile大小保持一致，数量要比主库至少多一组

注：查看主库logfile日志大小

SQL> select group#,BYTES/1024/1024 "M",members from v$log;

    GROUP#          M    MEMBERS
---------- ---------- ----------
         1         50          1
         2         50          1
         3         50          1

我这里没有添加standby logfile，添加的话按照下面的语句添加进至少四组

SQL>alter database add logfile group 4('/u01/app/oracle/oradata/DG2/disk1/redo04.log')  size 50m;

SQL>alter database add logfile group 5('/u01/app/oracle/oradata/DG2/disk1/redo05.log')  size 50m;

SQL>alter database add logfile group 6('/u01/app/oracle/oradata/DG2/disk1/redo06.log')  size 50m;

SQL>alter database add logfile group 7('/u01/app/oracle/oradata/DG2/disk1/redo07.log')  size 50m;

主库生成pfile参数文件

SQL>create pfile from spfile;

修改pfile文件，其余内容不变增加如下内容

DB_NAME=DG2

DB_UNIQUE_NAME=DG2

LOG_ARCHIVE_CONFIG='DG_CONFIG=(DG2,DG1)'

LOG_ARCHIVE_DEST_1=

 'LOCATION=USE_DB_RECOVERY_FILE_DEST 

  VALID_FOR=(ALL_LOGFILES,ALL_ROLES)

  DB_UNIQUE_NAME=DG2'

LOG_ARCHIVE_DEST_2=

 'SERVICE=DG1 ASYNC

  VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) 

  DB_UNIQUE_NAME=DG1'

LOG_ARCHIVE_DEST_STATE_1=ENABLE

LOG_ARCHIVE_DEST_STATE_2=ENABLE

REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE

LOG_ARCHIVE_FORMAT=%t_%s_%r.arc

FAL_SERVER=DG1

DB_FILE_NAME_CONVERT='DG1','DG2'

LOG_FILE_NAME_CONVERT='DG1','DG2'

STANDBY_FILE_MANAGEMENT=AUTO

关闭数据库生成spfile

SQL>create spfile from pfile;

将主库pfile和orapwDG2口令文件传到备库

关闭主库，将数据文件tar包，然后传到备库解压到DG1目录下，然后删除controlfile

启动主数据到mount生成standby controlfile

SQL>alter database create standby controlfile as '/home/oracle/control01.ctl';

将controlfile传到备库相应的控制文件位置

[oracle@TESTDB2 ~]$ scp control01.ctl oracle@192.168.80.131:/u01/app/oracle/oradata/DG1/control01.ctl

[oracle@TESTDB2 ~]$ scp control01.ctl oracle@192.168.80.131:/u01/app/oracle/fast_recovery_area/DG1/control02.ctl

SQL>alter database open;

修改备库的参数文件，要建立相应的路径

将DG2改为DG1，db_name不要改

DG1.__db_cache_size=339738624

DG1.__java_pool_size=4194304

DG1.__large_pool_size=4194304

DG1.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment

DG1.__pga_aggregate_target=343932928

DG1.__sga_target=507510784

DG1.__shared_io_pool_size=0

DG1.__shared_pool_size=146800640

DG1.__streams_pool_size=4194304

*.audit_file_dest='/u01/app/oracle/admin/DG1/adump'

*.audit_trail='db'

*.compatible='11.2.0.0.0'

*.control_files='/u01/app/oracle/oradata/DG1/control01.ctl','/u01/app/oracle/fast_recovery_area/DG1/control02.ctl'

*.db_block_size=8192

*.db_domain=''

*.db_name='DG1'（可以不要的，留着不影响）

*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'

*.db_recovery_file_dest_size=5218762752

*.diagnostic_dest='/u01/app/oracle'

*.dispatchers='(PROTOCOL=TCP) (SERVICE=DG1XDB)'

*.memory_target=848297984

*.open_cursors=300

*.processes=150

*.remote_login_passwordfile='EXCLUSIVE'

*.undo_tablespace='UNDOTBS1'

DB_NAME=DG2（不要改的）

DB_UNIQUE_NAME=DG1

LOG_ARCHIVE_CONFIG='DG_CONFIG=(DG1,DG2)'

LOG_ARCHIVE_DEST_1=

 'LOCATION=USE_DB_RECOVERY_FILE_DEST 

  VALID_FOR=(ALL_LOGFILES,ALL_ROLES)

  DB_UNIQUE_NAME=DG1'

LOG_ARCHIVE_DEST_2=

 'SERVICE=DG2 ASYNC

  VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) 

  DB_UNIQUE_NAME=DG2'

LOG_ARCHIVE_DEST_STATE_1=ENABLE

LOG_ARCHIVE_DEST_STATE_2=ENABLE

REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE

LOG_ARCHIVE_FORMAT=%t_%s_%r.arc

FAL_SERVER=DG2

DB_FILE_NAME_CONVERT='DG2','DG1'

LOG_FILE_NAME_CONVERT='DG2','DG1'

STANDBY_FILE_MANAGEMENT=AUTO

启动备库

[oracle@TESTDB1 dbs]$ export ORACLE_SID=DG1

SQL> startup mount;

SQL> alter database mount standby database ;

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

（主库）SQL> select SEQUENCE#,ARCHIVED from v$archived_log;

 SEQUENCE# ARC

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

        68 YES

        69 YES

        70 YES

        70 YES

        71 YES

        71 YES

（备库）SQL> select SEQUENCE#,ARCHIVED from v$archived_log;

SEQUENCE# ARC

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

        70 YES

        71 YES

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

转载于:http://blog.itpub.net/30166973/viewspace-1694068/