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