查看: 1670|回复: 0

[Oracle数据库] Oracle数据库容灾解决方案:Oracle DataGuard部署实录

发表于 2018-4-3 08:00:08
  1. SQL> alter database force logging;
  2. Database altered.
  3. SQL> col force_logging for a15
  4. SQL> select force_logging from v$database;
  5. FORCE_LOGGING
  6. ---------------
  7. YES
复制代码


  1. SQL> archive log list;
  2. Database log mode Archive Mode
  3. Automatic archival Enabled
  4. Archive destination USE_DB_RECOVERY_FILE_DEST
  5. Oldest online log sequence 35
  6. Next log sequence to archive 37
  7. Current log sequence 37
复制代码


  1. SQL> alter system set db_unique_name='kingm' scope=spfile;
  2. SQL> alter system set log_archive_config='dg_config=(kingm,kings)' scope=spfile;
  3. SQL> alter system set log_archive_dest_1='location=db_recovery_file_dest valid_for=(all_logfiles,all_roles) db_unique_name=kingm' scope=spfile;
  4. SQL> alter system set log_archive_dest_2='service=kings lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=kings' scope=spfile;
  5. SQL> alter system set fal_server='kings' scope=spfile;
  6. SQL> alter system set fal_client='kingm' scope=spfile;
  7. SQL> alter system set standby_file_management='auto' scope=spfile;
  8. SQL> alter system set log_archive_dest_state_2='defer' scope=spfile;
  9. SQL> alter system set service_names=king,kingm scope=spfile;
复制代码


  1. [oracle@king01 ~]$ cd $ORACLE_HOME/dbs
  2. [oracle@king01 dbs]$ sqlplus / as sysdba
  3. SQL> create pfile from spfile;
  4. File created.
  5. [oracle@king01 dbs]$ scp initking.ora 192.168.1.202:/u01/app/oracle/product/11.2.0/db_1/dbs
  6. [oracle@king01 dbs]$ scp orapwking 192.168.1.202:/u01/app/oracle/product/11.2.0/db_1/dbs
复制代码


  1. [oracle@king01 ~]$ mkdir -p /u01/app/oracle/backup
  2. [oracle@king02 ~]$ mkdir -p /u01/app/oracle/backup
  3. [oracle@king01 ~]$ rman target /
  4. RMAN> backup device type disk format '/u01/app/oracle/backup/%U' database plus archivelog;
  5. RMAN> backup device type disk format '/u01/app/oracle/backup/%U' current controlfile for standby;
  6. [oracle@king01 ~]$ scp /u01/app/oracle/backup/* 192.168.1.202:/u01/app/oracle/backup
复制代码

  1. [oracle@king01 ~]$ vi /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora
  2. KINGS =
  3. (DESCRIPTION =
  4. (ADDRESS_LIST =
  5. (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.202)(PORT = 1521))
  6. )
  7. (CONNECT_DATA =
  8. (SERVICE_NAME = king)
  9. )
  10. )
  11. KINGM =
  12. (DESCRIPTION =
  13. (ADDRESS_LIST =
  14. (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.201)(PORT = 1521))
  15. )
  16. (CONNECT_DATA =
  17. (SERVICE_NAME = king)
  18. )
  19. )
复制代码


  1. [oracle@king02 ~]$ vi /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
  2. SID_LIST_LISTENER =
  3. (SID_LIST =
  4. (SID_DESC =
  5. (GLOBAL_DBNAME = king)
  6. (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
  7. (SID_NAME = king)
  8. )
  9. )
  10. LISTENER =
  11. (DESCRIPTION =
  12. (ADDRESS = (PROTOCOL = TCP)(HOST = king02)(PORT = 1521))
  13. )
  14. ADR_BASE_LISTENER = /u01/app/oracle
  15. [oracle@king02 ~]$ lsnrctl start
  16. [oracle@king02 ~]$ lsnrctl status
复制代码


  1. [oracle@king02 ~]$ vi /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora
  2. KINGS =
  3. (DESCRIPTION =
  4. (ADDRESS_LIST =
  5. (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.202)(PORT = 1521))
  6. )
  7. (CONNECT_DATA =
  8. (SERVICE_NAME = king)
  9. )
  10. )
  11. KINGM =
  12. (DESCRIPTION =
  13. (ADDRESS_LIST =
  14. (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.201)(PORT = 1521))
  15. )
  16. (CONNECT_DATA =
  17. (SERVICE_NAME = king)
  18. )
  19. )
复制代码


  1. [oracle@king01 ~]$ tnsping kings
  2. Used TNSNAMES adapter to resolve the alias
  3. Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.202)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME =
  4. king)))
  5. OK (40 msec)
  6. [oracle@king02 ~]$ tnsping kingm
  7. Used TNSNAMES adapter to resolve the alias
  8. Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.201)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME =
  9. king)))
  10. OK (30 msec)
复制代码


  1. [oracle@king02 ~]$ cd $ORACLE_HOME/dbs
  2. [oracle@king02 dbs]$ vi initking.ora
  3. *.audit_file_dest='/u01/app/oracle/admin/king/adump'
  4. *.audit_trail='db'
  5. *.compatible='11.2.0.4.0'
  6. *.control_files='/u01/app/oracle/oradata/king/control01.ctl','/u01/app/oracle/fast_recovery_area/king/control02.ctl'
  7. *.db_block_size=8192
  8. *.db_domain=''
  9. *.db_name='king'
  10. *.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'
  11. *.db_recovery_file_dest_size=107374182400
  12. *.db_unique_name='kings'
  13. *.diagnostic_dest='/u01/app/oracle'
  14. *.dispatchers='(PROTOCOL=TCP) (SERVICE=kingXDB)'
  15. *.fal_client='kings'
  16. *.fal_server='kingm'
  17. *.log_archive_config='dg_config=(kingm,kings)'
  18. *.log_archive_dest_1='location=db_recovery_file_dest valid_for=(all_logfiles,all_roles) db_unique_name=kings'
  19. *.log_archive_dest_2='service=kings lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=kingm'
  20. *.memory_max_target=1073741824
  21. *.memory_target=1073741824
  22. *.open_cursors=300
  23. *.processes=150
  24. *.remote_login_passwordfile='EXCLUSIVE'
  25. *.service_names='KING','KINGS'
  26. *.standby_file_management='auto'
  27. *.undo_tablespace='UNDOTBS1'
  28. [oracle@king02 dbs]$ sqlplus / as sysdba
  29. SQL> create spfile from pfile;
  30. File created.
复制代码


  1. [oracle@king02 ~]$ mkdir -p /u01/app/oracle/admin/king/adump
  2. [oracle@king02 ~]$ mkdir -p /u01/app/oracle/admin/king/dpdump
  3. [oracle@king02 ~]$ mkdir -p /u01/app/oracle/admin/king/pfile
  4. [oracle@king02 ~]$ mkdir -p /u01/app/oracle/oradata/king
  5. [oracle@king02 ~]$ mkdir -p /u01/app/oracle/fast_recovery_area/king
复制代码


  1. [oracle@king02 ~]$ sqlplus / as sysdba
  2. SQL> startup nomount;
  3. ORACLE instance started.
  4. Total System Global Area 1068937216 bytes
  5. Fixed Size 2260088 bytes
  6. Variable Size 432014216 bytes
  7. Database Buffers 629145600 bytes
  8. Redo Buffers 5517312 bytes
复制代码


  1. [oracle@king02 ~]$ rman target sys/oracle@kingm auxiliary sys/oracle@kings nocatalog
  2. Recovery Manager: Release 11.2.0.4.0 - Production on Sat Mar 31 14:25:26 2018
  3. Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
  4. connected to target database: KING (DBID=1072562510)
  5. using target database control file instead of recovery catalog
  6. connected to auxiliary database: KING (not mounted)
  7. RMAN> duplicate target database for standby dorecover nofilenamecheck;
复制代码


  1. [oracle@king02 ~]$ sqlplus / as sysdba
  2. SQL> alter database add standby logfile '/u01/app/oracle/oradata/king/standby01.log' size 50m;
  3. SQL> alter database add standby logfile '/u01/app/oracle/oradata/king/standby02.log' size 50m;
  4. SQL> alter database add standby logfile '/u01/app/oracle/oradata/king/standby03.log' size 50m;
  5. SQL> alter database add standby logfile '/u01/app/oracle/oradata/king/standby04.log' size 50m;
  6. SQL> alter database recover managed standby database disconnect from session using current logfile;
复制代码


  1. [oracle@king01 ~]$ sqlplus / as sysdba
  2. SQL> alter system set log_archive_dest_state_2=enable scope=both;
  3. SQL> alter system switch logfile;
复制代码


  1. [oracle@king02 ~]$ sqlplus / as sysdba
  2. SQL> alter database recover managed standby database cancel;
  3. SQL> alter database open;
  4. SQL> alter database recover managed standby database disconnect from session using current logfile;
复制代码


  1. [oracle@king01 ~]$ sqlplus / as sysdba
  2. SQL> set line 200
  3. SQL> col DATABASE_MODE for a30
  4. SQL> col PROTECTION_MODE for a30
  5. SQL> col RECOVERY_MODE for a30
  6. SQL> select DEST_ID , DATABASE_MODE , RECOVERY_MODE , PROTECTION_MODE from v$archive_dest_status where DEST_ID=2;
  7. DEST_ID DATABASE_MODE RECOVERY_MODE PROTECTION_MODE
  8. ---------- ------------------------------ ------------------------------ ------------------------------
  9. 2 OPEN_READ-ONLY MANAGED REAL TIME APPLY MAXIMUM PERFORMANCE
  10. SQL> col DEST_NAME for a20
  11. SQL> col DESTINATION for a30
  12. SQL> col ERROR for a50
  13. SQL> select DEST_ID,DEST_NAME,STATUS, DESTINATION, ERROR from V$ARCHIVE_DEST where DEST_ID<=2;
  14. DEST_ID DEST_NAME STATUS DESTINATION ERROR
  15. ---------- -------------------- --------------------------- ------------------------------ --------------------------------------------------
  16. 1 LOG_ARCHIVE_DEST_1 VALID db_recovery_file_dest
  17. 2 LOG_ARCHIVE_DEST_2 VALID kings
  18. SQL> col TYPE for a20
  19. SQL> select DEST_NAME,DESTINATION,STATUS,TYPE,ARCHIVED_SEQ#,APPLIED_SEQ# from v$archive_dest_status where DEST_ID<=2;
  20. DEST_NAME DESTINATION STATUS TYPE ARCHIVED_SEQ# APPLIED_SEQ#
  21. -------------------- ------------------------------ --------------------------- -------------------- ------------- ------------
  22. LOG_ARCHIVE_DEST_1 db_recovery_file_dest VALID LOCAL 50 0
  23. LOG_ARCHIVE_DEST_2 kings VALID PHYSICAL 50 49
  24. SQL> select THREAD# , SEQUENCE# , STATUS from v$log;
  25. THREAD# SEQUENCE# STATUS
  26. ---------- ---------- ------------------------------------------------
  27. 1 49 INACTIVE
  28. 1 50 INACTIVE
  29. 1 51 CURRENT
复制代码


  1. [oracle@king02 ~]$ sqlplus / as sysdba
  2. SQL> set line 200
  3. col ARCHIVED for a10
  4. SQL> select THREAD# , SEQUENCE# , ARCHIVED , STATUS from v$standby_log;
  5. THREAD# SEQUENCE# ARCHIVED STATUS
  6. ---------- ---------- ---------- ------------------------------
  7. 1 51 YES ACTIVE
  8. 1 0 NO UNASSIGNED
  9. 0 0 YES UNASSIGNED
  10. 0 0 YES UNASSIGNED
  11. SQL> select PROCESS , STATUS , THREAD# , SEQUENCE# , BLOCK# , BLOCKS from v$managed_standby where process != 'ARCH';
  12. PROCESS STATUS THREAD# SEQUENCE# BLOCK# BLOCKS
  13. --------------------------- ------------------------------------ ---------- ---------- ---------- ----------
  14. RFS IDLE 0 0 0 0
  15. RFS IDLE 0 0 0 0
  16. RFS IDLE 1 51 4381 1
  17. MRP0 APPLYING_LOG 1 51 4381 102400
复制代码




回复

使用道具 举报