oracle11g dataguard安装实施

Oracle DataGuard 实施

1.环境准备
1.1 修改主备机hosts文件
vi /etc/hosts
128.160.11.84    wang
128.160.11.218  dg2

1.2 修改(添加）主备机listener.ora和tnsnames.ora文件

vi $ORACLE_HOME/network/admin/listener.ora
SID_LIST_LISTENER =
        (SID_LIST =
          (SID_DESC =
                (SID_NAME = softdb)
                (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1/)
          )
        )


LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = wang)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

ADR_BASE_LISTENER = /u01/app/oracle

vi $ORACLE_HOME/network/admin/tnsnames.ora
SOFTPRI =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = wang)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = softdb)
    )
  )
SOFTSTD =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = dg2)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = softdb)
    )
  )
  

1.3 确定主备机parameter/control/data/log/archivelog file 的路径

audit_file_dest='/u01/app/oracle/admin/softdb/adump'

1.4 设置主库强制写日志

SQL> select force_logging from v$database;

FOR
---
NO

SQL> alter database force logging;

Database altered.

SQL> select force_logging from v$database;

FOR
---
YES

1.5 设置主库归档模式

SQL> archive log list;
SQL> shutdown immediate;
SQL> startup mount;
SQL> alter database archivelog;
SQL> alter database open;
SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u01/app/oracle/product/11.2.0/db_1//dbs/arch
Oldest online log sequence     175
Next log sequence to archive   177
Current log sequence           177

2. 产生用于建立Standby库的全备份集及控制文件

2.1 创建并修改主库参数文件pfile

SQL> shutdown immediate;
SQL> create pfile from spfile;

修改initsoftdb.ora
vi $ORACLE_HOME/dbs/initsoftdb.ora

DB_NAME=softdb
DB_UNIQUE_NAME=softpri
LOG_ARCHIVE_CONFIG='DG_CONFIG=(softpri,softstd)'
control_files='/oradata/softdb/softdb/control01.ctl','/oradata/softdb/softdb/control02.ctl'
LOG_ARCHIVE_DEST_1=
 'LOCATION=/oradata/softarch/ 
  VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
  DB_UNIQUE_NAME=softpri'
LOG_ARCHIVE_DEST_2=
 'SERVICE=softstd ASYNC
  VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) 
  DB_UNIQUE_NAME=softstd'
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=10

##Standby Role Initialization Parameters
FAL_SERVER=softstd
FAL_CLIENT=softpri
DB_FILE_NAME_CONVERT='softstd','softpri'
LOG_FILE_NAME_CONVERT=
 '/oradata/softdb/softdb/','/oradata/softdb/softdb/' 
STANDBY_FILE_MANAGEMENT=AUTO

生成spfile
SQL> create spfile from pfile;

2.2 在pri库创建std库需要的控制文件

SQL> startup mount;
SQL> alter database create standby controlfile as '/oradata/backup/control01.ctl';
SQL> alter database open;

2.3 在pri库创建standby redo logfile

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

    GROUP#      BYTES    THREAD#
---------- ---------- ----------
         1   52428800          1
         2   52428800          1
         3   52428800          1

SQL> alter database add standby logfile group 4('/oradata/softdb/softdb/slog1.rdo') SIZE 500M;
SQL> alter database add standby logfile group 5('/oradata/softdb/softdb/slog2.rdo') SIZE 500M;
SQL> alter database add standby logfile group 6('/oradata/softdb/softdb/slog3.rdo') SIZE 500M;
SQL> alter database add standby logfile group 7('/oradata/softdb/softdb/slog4.rdo') SIZE 500M;

注：standby redo logfile的group比logfile多1个，单个logfile大小一致。

2.4 生成备份集

$ rman target sys/ora11g@softdb
connected to target database: SOFTDB (DBID=402771454)

run {
allocate channel d1 type disk;
backup database format '/oradata/backup/full_%d_%t_%s.bak'
plus archivelog format '/oradata/backup/arch_%d_%t_%s.log'
delete all input;
release channel d1;
}

或者

run {
allocate channel d1 type disk;
allocate channel d2 type disk;
crosscheck archivelog all;
sql 'alter system switch logfile';
backup full database noexclude filesperset 8 format '/oradata/backup/%d_full_%t_%s_%p.dbf';
sql 'alter system switch logfile';
backup format '/oradata/backup/%d_log_%t_%s_%e.log' archivelog all;
backup current controlfile format '/dbbk/control_%s_%p_%t_%T.ctl';
release channel d1;
release channel d2;
}

rman> list backup;

2.5 将主机softdb的密码文件、参数文件、备份文件拷贝到备机

scp -rp $ORACLE_HOME/dbs/orapwsoftdb oracle@128.160.11.218:$ORACLE_HOME/dbs/
scp -rp $ORACLE_HOME/dbs/initsoftdb.ora oracle@128.160.11.218:$ORACLE_HOME/dbs/initsoftdb.ora
scp -rp /oradata/backup/* oracle@128.160.11.218:/oradata/backup/

3. 备机恢复数据库

3.1 修改standby库pfile

vi $ORACLE_HOME/dbs/initsoftdb.ora

DB_NAME=softdb
DB_UNIQUE_NAME=softstd
LOG_ARCHIVE_CONFIG='DG_CONFIG=(softpri,softstd)'
control_files='/oradata/softdb/softdb/control01.ctl','/oradata/softdb/softdb/control02.ctl'
LOG_ARCHIVE_DEST_1=
 'LOCATION=/oradata/softarch/ 
  VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
  DB_UNIQUE_NAME=softstd'
LOG_ARCHIVE_DEST_2=
 'SERVICE=softpri ASYNC
  VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) 
  DB_UNIQUE_NAME=softpri'
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=10
FAL_SERVER=softpri
FAL_CLIENT=softstd
DB_FILE_NAME_CONVERT='softpri','softstd'
LOG_FILE_NAME_CONVERT=
 '/oradata/softdb/softdb/','/oradata/softdb/softdb/' 
STANDBY_FILE_MANAGEMENT=AUTO

3.2 在standby执行rman的恢复primary库的全库备份

3.2.1 修改后的pfile将standby库启动到nomount

sqlplus /nolog
conn sys/@softstd as sysdba
startup nomount pfile='$ORACLE_HOME/dbs/initsoftdb.ora';

3.2.2 RMAN恢复standby库
rman> connect target sys@softstd
rman> set DBID 402771454
rman> restore controlfile from '/oradata/backup/full_SOFTDB_863944285_11.bak';
rman> alter database mount;
rman> restore database;
rman> recover database;

3.3 建立主备库standby关系
3.3.1 备库修改后spfile启动
SQL> shutdown immediate;
SQL> create spfile from pfile='$ORACLE_HOME/dbs/initsoftdb.ora';
SQL> startup nomount;

3.3.2 恢复standby controlfile
rman> restore controlfile from '/oradata/backup/control01.ctl';

3.3.3 在standby数据库上添加standby redo log
SQL> alter database mount standby database;
SQL> alter database add standby logfile group 4('/oradata/softdb/softdb/slog1.rdo') SIZE 500M;
SQL> alter database add standby logfile group 5('/oradata/softdb/softdb/slog2.rdo') SIZE 500M;
SQL> alter database add standby logfile group 6('/oradata/softdb/softdb/slog3.rdo') SIZE 500M;
SQL> alter database add standby logfile group 7('/oradata/softdb/softdb/slog4.rdo') SIZE 500M;

3.3.4 把standby数据库启动为恢复模式
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;

4. 确保standby库正常接收redolog和Applying

4.1 standby database, query the V$ARCHIVED_LOG 确认存在的archived logfile

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

SEQUENCE# FIRST_TIME          NEXT_TIME
---------- ------------------- -------------------
      184 2014-11-17 22:00:31 2014-11-18 00:12:53
      185 2014-11-18 00:12:53 2014-11-18 06:55:59
      186 2014-11-18 06:55:59 2014-11-18 08:30:56
      187 2014-11-18 08:30:56 2014-11-18 08:31:27
      188 2014-11-18 08:31:27 2014-11-18 13:58:10

4.2  Force a log switch on primary database （to archive current logfile)

SQL> ALTER SYSTEM SWITCH LOGFILE;

4.3 确保standby库已经Archive and Apply新redolog

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

 SEQUENCE# FIRST_TIME          NEXT_TIME           APPLIED
---------- ------------------- ------------------- ------------------
       184 2014-11-17 22:00:31 2014-11-18 00:12:53 YES
       185 2014-11-18 00:12:53 2014-11-18 06:55:59 YES
       186 2014-11-18 06:55:59 2014-11-18 08:30:56 YES
       187 2014-11-18 08:30:56 2014-11-18 08:31:27 YES
       188 2014-11-18 08:31:27 2014-11-18 13:58:10 YES
       189 2014-11-18 13:58:10 2014-11-18 14:08:33 IN-MEMORY
       

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

转载于:http://blog.itpub.net/30025058/viewspace-1455336/