【OracleDatabase】DataGuard（rac-single）
[oracle@king01 ~]$ sqlplus / as sysdba SQL> alter database force logging; Database altered. SQL> col force_logging for a15 SQL> select force_logging from v$database; FORCE_LOGGING --------------- YES SQL> archive log list; Database log mode Archive Mode Automatic archival Enabled Archive destination USE_DB_RECOVERY_FILE_DEST Oldest online log sequence 30 Next log sequence to archive 31 Current log sequence 31 SQL> alter system set db_unique_name='kingmdb' scope=spfile; SQL> alter system set log_archive_config='dg_config=(kingmdb,kingsdb)' scope=spfile; SQL> alter system set log_archive_dest_1='location=USE_DB_RECOVERY_FILE_DEST valid_for=(all_logfiles,all_roles) db_unique_name=kingmdb' scope=spfile; SQL> alter system set log_archive_dest_2='service=kingsdb lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=kingsdb' scope=spfile; SQL> alter system set fal_server='kingsdb' scope=spfile; SQL> alter system set standby_file_management='auto' scope=spfile; SQL> alter system set log_archive_dest_state_2='defer' scope=spfile; SQL> alter system set service_names=kingdb,kingmdb scope=spfile; [oracle@king01 ~]$ mkdir backup [oracle@king01 ~]$ rman target / RMAN> backup device type disk format '/home/oracle/backup/%U' database plus archivelog; RMAN> backup device type disk format '/home/oracle/backup/%U' current controlfile for standby; [oracle@king01 ~]$ scp /home/oracle/backup/* 192.168.1.203:/home/oracle/backup [oracle@king01 ~]$ vi $ORACLE_HOME/network/admin/tnsnames.ora kingmdb = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.201)(PORT = 1521)) (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.202)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = kingdb) ) ) kingsdb = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.203)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = kingdb) ) ) [oracle@king02 ~]$ vi $ORACLE_HOME/network/admin/tnsnames.ora kingmdb = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.201)(PORT = 1521)) (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.202)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = kingdb) ) ) kingsdb = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.203)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = kingdb) ) ) [oracle@king03 ~]$ vi $ORACLE_HOME/network/admin/listener.ora SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = kingdb) (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1) (SID_NAME = kingdb) ) ) LISTENER = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = king03)(PORT = 1521)) ) ADR_BASE_LISTENER = /u01/app/oracle [oracle@king03 ~]$ lsnrctl start [oracle@king03 ~]$ lsnrctl status [oracle@king03 ~]$ vi $ORACLE_HOME/network/admin/tnsnames.ora kingmdb = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.201)(PORT = 1521)) (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.202)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = kingdb) ) ) kingsdb = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.203)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = kingdb) ) ) [oracle@king01 ~]$ tnsping kingsdb Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.203)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = kingdb))) OK (110 msec) [oracle@king03 ~]$ tnsping kingmdb Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.201)(PORT = 1521)) (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.202)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = kingdb))) OK (0 msec) [oracle@king03 ~]$ vi .bash_profile export ORACLE_SID=kingdb export ORACLE_BASE=/u01/app/oracle export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1 export PATH=$ORACLE_HOME/bin:$PATH export DISPLAY=192.168.1.200:0 export NLS_LANG=AMERICAN_AMERICA.UTF8 export NLS_DATE_FORMAT="yyyy-mm-dd hh34:mi:ss" stty erase ^H [oracle@king03 ~]$ source .bash_profile [oracle@king03 ~]$ cd $ORACLE_HOME/dbs [oracle@king03 dbs]$ vi initkingdb.ora *.audit_file_dest='/u01/app/oracle/admin/kingdb/adump' *.audit_trail='db' *.compatible='11.2.0.4.0' *.control_files='/u01/app/oracle/oradata/kingdb/control01.ctl','/u01/app/oracle/fast_recovery_area/kingdb/control02.ctl' *.db_block_size=8192 *.db_domain='' *.db_name='kingdb' *.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area' *.db_recovery_file_dest_size=107374182400 *.db_file_name_convert='+DATAFILE/kingdb/datafile','/u01/app/oracle/oradata/kingdb','+DATAFILE/kingdb/tempfile','/u01/app/oracle/ oradata/kingdb' *.db_unique_name='kingsdb' *.diagnostic_dest='/u01/app/oracle' *.dispatchers='(PROTOCOL=TCP) (SERVICE=kingdbXDB)' *.fal_server='kingmdb' *.log_archive_config='dg_config=(kingmdb,kingsdb)' *.log_archive_dest_1='location=USE_DB_RECOVERY_FILE_DEST valid_for=(all_logfiles,all_roles) db_unique_name=kingsdb' *.log_archive_dest_2='service=kingmdb lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=kingmdb' *.log_file_name_convert='+DATAFILE/kingdb/onlinelog','/u01/app/oracle/oradata/kingdb' *.memory_max_target=1073741824 *.memory_target=1073741824 *.open_cursors=300 *.processes=150 *.remote_login_passwordfile='EXCLUSIVE' *.standby_file_management='auto' *.undo_tablespace='UNDOTBS1' [oracle@king03 dbs]$ sqlplus / as sysdba SQL> create spfile from pfile; File created. [oracle@king03 dbs]$ orapwd file=/u01/app/oracle/product/11.2.0/db_1/dbs/orapwkingdb password=oracle entries=5 force=y [oracle@king03 ~]$ mkdir -p /u01/app/oracle/admin/kingdb/adump [oracle@king03 ~]$ mkdir -p /u01/app/oracle/oradata/kingdb [oracle@king03 ~]$ mkdir -p /u01/app/oracle/fast_recovery_area/kingdb [oracle@king03 ~]$ sqlplus / as sysdba SQL> startup nomount; ORACLE instance started. Total System Global Area 1068937216 bytes Fixed Size 2260088 bytes Variable Size 432014216 bytes Database Buffers 629145600 bytes Redo Buffers 5517312 bytes [oracle@king03 ~]$ rman target sys/oracle@kingmdb auxiliary sys/oracle@kingsdb nocatalog Recovery Manager: Release 11.2.0.4.0 - Production on Wed Nov 14 13:41:26 2018 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. connected to target database: KINGDB (DBID=4127342910) using target database control file instead of recovery catalog connected to auxiliary database: KINGDB (DBID=4127342910) RMAN> duplicate target database for standby dorecover nofilenamecheck; [oracle@king03 ~]$ sqlplus / as sysdba SQL> alter database add standby logfile thread 1 '/u01/app/oracle/oradata/kingdb/standby01.log' size 50m; SQL> alter database add standby logfile thread 1 '/u01/app/oracle/oradata/kingdb/standby02.log' size 50m; SQL> alter database add standby logfile thread 1 '/u01/app/oracle/oradata/kingdb/standby03.log' size 50m; SQL> alter database add standby logfile thread 2 '/u01/app/oracle/oradata/kingdb/standby04.log' size 50m; SQL> alter database add standby logfile thread 2 '/u01/app/oracle/oradata/kingdb/standby05.log' size 50m; SQL> alter database add standby logfile thread 2 '/u01/app/oracle/oradata/kingdb/standby06.log' size 50m; SQL> alter database recover managed standby database disconnect from session using current logfile; [oracle@king01 ~]$ sqlplus / as sysdba SQL> alter system set log_archive_dest_state_2=enable scope=both; SQL> alter system switch logfile; [oracle@king03 ~]$ sqlplus / as sysdba SQL> alter database recover managed standby database cancel; SQL> alter database open; SQL> alter database recover managed standby database disconnect from session using current logfile; [oracle@king01 ~]$ sqlplus / as sysdba SQL> set line 200 SQL> col database_mode for a30 SQL> col protection_mode for a30 SQL> col recovery_mode for a30 SQL> select dest_id , database_mode , recovery_mode , protection_mode from v$archive_dest_status where dest_id=2; DEST_ID DATABASE_MODE RECOVERY_MODE PROTECTION_MODE ---------- ------------------------------ ------------------------------ ------------------------------ 2 OPEN_READ-ONLY MANAGED REAL TIME APPLY MAXIMUM PERFORMANCE SQL> col dest_name for a20 SQL> col destination for a30 SQL> col error for a50 SQL> select dest_id,dest_name,status, destination, error from v$archive_dest where dest_id<=2; DEST_ID DEST_NAME STATUS DESTINATION ERROR ---------- -------------------- --------------------------- ------------------------------ -------------------------------------------------- 1 LOG_ARCHIVE_DEST_1 VALID USE_DB_RECOVERY_FILE_DEST 2 LOG_ARCHIVE_DEST_2 VALID kingsdb SQL> col type for a20 SQL> select dest_name,destination,status,type,archived_seq#,applied_seq# from v$archive_dest_status where dest_id<=2; DEST_NAME DESTINATION STATUS TYPE ARCHIVED_SEQ# APPLIED_SEQ# -------------------- ------------------------------ --------------------------- -------------------- ------------- ------------ LOG_ARCHIVE_DEST_1 VALID LOCAL 30 0 LOG_ARCHIVE_DEST_2 kingsdb VALID PHYSICAL 23 22 SQL> select thread# , sequence# , status from v$log; THREAD# SEQUENCE# STATUS ---------- ---------- ------------------------------------------------ 1 31 CURRENT 1 30 INACTIVE 2 23 INACTIVE 2 24 CURRENT [oracle@king03 ~]$ sqlplus / as sysdba SQL> select thread# , sequence# , archived , status from v$standby_log; THREAD# SEQUENCE# ARCHIVED STATUS ---------- ---------- ---------- ------------------------------ 1 31 YES ACTIVE 1 0 NO UNASSIGNED 1 0 YES UNASSIGNED 2 0 NO UNASSIGNED 2 24 YES ACTIVE 2 0 YES UNASSIGNED SQL> select process , status , thread# , sequence# , block# , blocks from v$managed_standby where process != 'ARCH'; PROCESS STATUS THREAD# SEQUENCE# BLOCK# BLOCKS --------------------------- ------------------------------------ ---------- ---------- ---------- ---------- MRP0 APPLYING_LOG 2 24 20942 102400 RFS IDLE 0 0 0 0 RFS IDLE 0 0 0 0 RFS IDLE 0 0 0 0 RFS IDLE 1 31 49129 1 RFS IDLE 0 0 0 0 RFS IDLE 0 0 0 0 RFS IDLE 0 0 0 0 RFS IDLE 2 24 20942 1
成都创新互联公司成立于2013年，是专业互联网技术服务公司，拥有项目网站设计制作、网站设计网站策划，项目实施与项目整合能力。我们以让每一个梦想脱颖而出为使命，1280元磁县做网站,已为上家服务,为磁县各地企业和个人服务,联系电话:028-86922220
网站题目：【OracleDatabase】DataGuard（rac-single）
当前网址：http://6mz.cn/article/jhgsgs.html