{"id":432,"date":"2020-01-25T17:05:08","date_gmt":"2020-01-25T09:05:08","guid":{"rendered":"http:\/\/www.821121.com\/?p=432"},"modified":"2020-12-11T09:59:54","modified_gmt":"2020-12-11T01:59:54","slug":"oracle11g%e5%8d%95%e5%ae%9e%e4%be%8b%e4%b8%80%e4%b8%bb%e4%ba%8c%e5%a4%87","status":"publish","type":"post","link":"http:\/\/www.821121.com\/?p=432","title":{"rendered":"oracle11g\u5355\u5b9e\u4f8b\u4e00\u4e3b\u4e8c\u5907DG\u642d\u5efa"},"content":{"rendered":"
\u4e3b\u5e93\uff1a\nip\uff1a192.168.174.154     \u7cfb\u7edf\uff1alinux6.8   \u6570\u636e\u5e93\uff1aoracle11g\u5355\u5b9e\u4f8b    sys\u5bc6\u7801\uff1a123456  \ndb_name=orcl,\u5373instance_name\u3001db_unique_name \u3001service_name\u90fd\u4e3aorcl\n\n\u5907\u5e931\uff1a\nip\uff1a192.168.174.155     \u7cfb\u7edf\uff1alinux6.8   \u6570\u636e\u5e93\uff1aoracle11g\u5355\u5b9e\u4f8b    sys\u5bc6\u7801\uff1a123456 \n\n\u5907\u5e932\uff1a\nip\uff1a192.168.174.156     \u7cfb\u7edf\uff1alinux6.8   \u6570\u636e\u5e93\uff1aoracle11g\u5355\u5b9e\u4f8b    sys\u5bc6\u7801\uff1a123456 \n\n\u642d\u5efa\u524d\u51c6\u5907\uff1a\n\u9700\u8981\u5728155\u3001156\u4e24\u53f0\u670d\u52a1\u5668\u5148\u884c\u5b89\u88c5\u597doracle11g\u6570\u636e\u5e93\uff0c\u4e14\u90fd\u672a\u521b\u5efa\u5b9e\u4f8b\uff0c\u5907\u5e93\u7684db_name\u4e00\u5b9a\u4e0e\u4e3b\u5e93\u4e00\u81f4\uff0cdb_unique_name\uff0c3\u4e2a\u5e93\u9700\u8981\u4e00\u5b9a\u4e0d\u4e00\u6837\uff0c\u5176\u4f59\u968f\u610f\n<\/code><\/pre>\n
1\u3001\u642d\u5efadataguard<\/h1>\n
\u5224\u65ad\u4e3b\u5e93DG\u662f\u5426\u5df2\u7ecf\u5b89\u88c5\uff1a\n  select * from v$option where parameter = 'Oracle Data Guard';\n  \u5982\u679c\u662ftrue\u8868\u793a\u5df2\u7ecf\u5b89\u88c5\u53ef\u4ee5\u914d\u7f6e\uff0c\u5426\u5219\u9700\u8981\u5b89\u88c5\u76f8\u5e94\u7ec4\u4ef6\u3002\n\u5982\u679c\u4e0d\u77e5\u9053\u4e3b\u5e93sys\u5bc6\u7801\uff0c\u4fee\u6539\u4e3b\u5e93sys\u7528\u6237\u5bc6\u7801\uff1a\nalter user sys identified by 123456;<\/code><\/pre>\n
\u786e\u8ba4\u4e3b\u5e93\u662f\u5426\u5f00\u542farchivelog\u4e0eforce log \nselect log_mode,force_logging from \nv$database; --\u68c0\u67e5\nalter database force logging; --\u5f00\u542f\n\narchive log list;\nalter database archivelog;\n\nselect log_mode,force_logging from v$database; --\u518d\u6b21\u68c0\u67e5<\/code><\/pre>\n
2\u3001\u914d\u7f6e\u4e3b\u3001\u5907\u5e93\u76d1\u542c\u3001tnsmaes\uff08\u4e3b\u5e93\u76d1\u542c\u5c3d\u91cf\u4e0d\u52a8\uff0c\u5907\u5e93\u5fc5\u987b\u662f\u9759\u6001\u76d1\u542c\uff09<\/h1>\n
\u5907\u5e93:\u5fc5\u987b\u662f\u9759\u6001\u76d1\u542c\uff08GLOBAL_DBNAME\u670d\u52a1\u540d\uff0c\u53ef\u81ea\u5b9a\u4e49\uff0ctnsnames.ora\u6587\u4ef6\u4e2d\u7684service_name\u4e0e\u4e4b\u4e00\u81f4\uff1bSID_NAME\u4e0e\u6570\u636e\u5e93\u5b9e\u4f8b\u540d\u4e00\u81f4\uff09\nsu - oracle\ncd $ORACLE_HOME\/network\/admin\nvi listener.ora\n\u5982\u4e0b\u8bed\u53e5\uff1a\n\n\u5907\u5e931\uff1a\n\nLISTENER =\n  (DESCRIPTION_LIST =\n    (DESCRIPTION =\n      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.174.155)(PORT = 1521))\n      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))\n    )\n  )\n\nADR_BASE_LISTENER = \/u01\/app\/oracle\n\nSID_LIST_LISTENER =\n  (SID_LIST =\n    (SID_DESC =\n      (GLOBAL_DBNAME = orcldg1)\n      (ORACLE_HOME = \/u01\/app\/oracle\/product\/11.2.0.4\/db_1)\n      (SID_NAME = orcldg1)\n    )\n  )\n\n\u5907\u5e932\uff1a\n\nLISTENER =\n  (DESCRIPTION_LIST =\n    (DESCRIPTION =\n      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.174.156)(PORT = 1521))\n      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))\n    )\n  )\n\nADR_BASE_LISTENER = \/u01\/app\/oracle\n\nSID_LIST_LISTENER =\n  (SID_LIST =\n    (SID_DESC =\n      (GLOBAL_DBNAME = orcldg2)\n      (ORACLE_HOME = \/u01\/app\/oracle\/product\/11.2.0.4\/db_1)\n      (SID_NAME = orcldg2)\n    )\n  )\n\n\u542f\u52a8\u76d1\u542c\nlsnrctl start\n\u7136\u540e\u518d\u67e5\u770b\u76d1\u542c\u72b6\u6001\uff0c\u201dUNKNOWN\u201d\u8bf4\u660e\u662f\u9759\u6001\u76d1\u542c\u3002\nlsnrctl status<\/code><\/pre>\n
\u4e3b\u5907\u5e93\u90fd\u914d\u7f6e$ORACLE_HOME\/network\/admin\/tnsnames.ora\uff08\u4ee5\u4e0b\u6bcf\u4e2atns\u7684\u7b2c\u4e00\u884c\u4e3atnsnames\u63cf\u8ff0\u7b26\uff09-->\u57fa\u672c\u5efa\u8baetnsnames\u63cf\u8ff0\u7b26\u8bbe\u7f6e\u4e3a\u548cdb_unique_name\u4e00\u81f4\u3002\n\norcl =\n  (DESCRIPTION =\n    (ADDRESS_LIST =\n      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.174.154)(PORT = 1521))\n    )\n    (CONNECT_DATA =\n      (SERVER = DEDICATED)\n      (SERVICE_NAME = orcl)\n    )\n  )\n\norcldg1 =\n  (DESCRIPTION =\n    (ADDRESS_LIST =\n      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.174.155)(PORT = 1521))\n    )\n    (CONNECT_DATA =\n      (SERVER = DEDICATED)\n      (SERVICE_NAME = orcldg1)\n    )\n  )\n\norcldg2 =\n  (DESCRIPTION =\n    (ADDRESS_LIST =\n      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.174.156)(PORT = 1521))\n    )\n    (CONNECT_DATA =\n      (SERVER = DEDICATED)\n      (SERVICE_NAME = orcldg2)\n    )\n  )\n<\/code><\/pre>\n
3.--\u4f7f\u7528\u4e0b\u9762\u7684\u547d\u4ee4\u4fee\u6539\u4e3b\u5e93\u53c2\u6570(\u6b64\u65f6\u4e3b\u5e93\u5e94\u5f53\u4f7f\u7528 spfile \u542f\u52a8\u53c2\u6570),\u4e3b\u5e93\u80fd\u4e0d\u52a8\u5c3d\u91cf\u4e0d\u52a8<\/em><\/strong><\/h1>\n
\u6ce8\u610f\u4ee5\u4e0b\u51e0\u70b9\uff1a\n\nLOG_ARCHIVE_CONFIG='DG_CONFIG(db_unique_name, db_unique_name, ... )' \u4e3b\u5e93\u4e0e\u5907\u5e93\u7aef\u91c7\u7528\u76f8\u540c\u8bbe\u7f6e\u3002\n\nLOG_ARCHIVE_DEST_n ='SERVICE=..... SERIVCE: \u7528\u4e8e\u6307\u5b9a\u5907\u7528\u6570\u636e\u5e93\u7684 TNSNAMES \u63cf\u8ff0\u7b26\n\ndb_file_name_convert\u3001 log_file_name_convert \u53c2\u6570\u503c\u4e3a\u8def\u5f84\uff0c\u53ef\u4ee5\u76f4\u63a5\u5199,db_unique_name\u3002\u5982\u679c\u4f7f\u7528 ASM\uff0c\u53ef\u4ee5\u8bbe\u7f6e\u4e3a*.db_file_name_convert =('+DATA','+RECOVERY')\n\nfal_server\u3001 fal_client \u53c2\u6570\u503c\u4e3a TNSNAMES \u63cf\u8ff0\u7b26\n<\/code><\/pre>\n
\u4ee5\u4e0b\u4e3a\u672c\u6b21\u5b9e\u9a8c\u4e3b\u5e93\u53c2\u6570\u8bbe\u7f6e\uff1a\nalter system set db_unique_name='orcl' scope=spfile sid='*';\nalter system set instance_name='orcl' scope=spfile sid='*';\nalter system set service_names='orcl' scope=spfile sid='*';<\/code><\/pre>\n
alter system set log_archive_config='DG_CONFIG=(orcl,orcldg1,orcldg2)' scope=both sid='*';    \/\/--DG_CONFIG\u586bdb_unique_name\nALTER SYSTEM SET LOG_ARCHIVE_DEST_1='LOCATION=\/u01\/app\/oracle\/oradata\/orcl\/arch  VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=orcl' scope=both sid='*';\nALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=orcldg1 LGWR ASYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcldg1' scope=both sid='*';\nALTER SYSTEM SET LOG_ARCHIVE_DEST_3='SERVICE=orcldg2 LGWR ASYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcldg2' scope=both sid='*';\/\/\u4e00\u4e3b2\u5907\u65f6\u7528\nalter system set log_archive_dest_state_1=enable scope=both sid='*';\nalter system set log_archive_dest_state_2=enable scope=both sid='*';\nalter system set log_archive_dest_state_3=enable scope=both sid='*';\nalter system set log_archive_max_processes=4 scope=both sid='*';\/\/\u53ef\u4e0d\u5199\nalter system set STANDBY_FILE_MANAGEMENT=AUTO scope=both sid='*';\nalter system set  local_listener='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.174.154)(PORT=1521))))' scope=both sid='*';\nalter system set fal_server='orcldg1,orcldg2' scope=both sid='*';\/\/\u5ba2\u7aef\nalter system set fal_client='orcl' scope=both sid='*';\/\/\u4e3b\u7aef\n\nalter system set remote_login_passwordfile='EXCLUSIVE' scope=spfile sid='*';<\/code><\/pre>\n
\/\/\u8fd9\u4e24\u4e2a\u53c2\u6570\u4e3b\u5e93\u53ef\u4ee5\u4e0d\u5199\uff0c\u5177\u4f53\u67e5\u770b\u4e3b\u5907\u5207\u6362\u6587\u6863\nalter system set db_file_name_convert='\/u01\/app\/oracle\/oradata\/orcl\/','\/orcldg1\/datafile\/' scope=spfile sid='*';  \u524d\u4e3b\u540e\u5907,\u4e3b\u8981\u4f5c\u7528\u4f20\u8f93\u6570\u636e\u6587\u4ef6\u5e76\u6539\u540d\nalter system set log_file_name_convert='\/u01\/app\/oracle\/oradata\/orcl\/','\/orcldg1\/logfile\/ scope=spfile sid='*';  \u524d\u4e3b\u540e\u5907\uff0c\u4e3b\u8981\u4f5c\u7528\u4f20\u8f93\u65e5\u5fd7\u6587\u4ef6\u5e76\u6539\u540d<\/code><\/pre>\n
4.\u914d\u7f6e\u4e3b\u5e93standby redo log\uff0c\u5e76\u9a8c\u8bc1\u7ed3\u679c<\/h1>\n
\u67e5\u770b\u662f\u5426\u5df2\u7ecf\u5b58\u5728standby redo log\nselect group#, thread#, sequence#, archived, status from v$standby_log;\n\n\u5b58\u5728\u5c31\u4e0d\u7528\u6309\u7167\u4ee5\u4e0b\u5185\u5bb9\u521b\u5efa\u3002\n\n\u6839\u636e\u4ee5\u4e0bsql\u67e5\u8be2\u7684\u7ed3\u679c\uff0c\u521b\u5efastandby redo log\u3002\nselect GROUP#,THREAD#,BYTES from v$log ;\nselect MEMBER from v$logfile;<\/code><\/pre>\n
\u6839\u636e\u4ee5\u4e0a\u67e5\u8be2\u51fa\u7684\u8def\u5f84\u53cagroup\u4e2a\u6570\uff0c\u521b\u5efastandby redo log\uff0c\u521b\u5efa\u539f\u5219thread\u8981\u4e00\u6837\u591a\uff0c\u540c\u4e2athread\u4e2d\u8981\u6bd4redo\u591a\u4e00\u4e2agroup\uff08standby redo log\u5927\u5c0f\u5fc5\u987b\u4e0e\u4e3b\u5e93\u91cd\u505a\u65e5\u5fd7\u5927\u5c0f\u4e00\u81f4\uff09\nALTER DATABASE ADD STANDBY LOGFILE '\/u01\/app\/oracle\/oradata\/orcl\/stbredo01.log' SIZE 50m;\nALTER DATABASE ADD STANDBY LOGFILE '\/u01\/app\/oracle\/oradata\/orcl\/stbredo02.log' SIZE 50m;\nALTER DATABASE ADD STANDBY LOGFILE '\/u01\/app\/oracle\/oradata\/orcl\/stbredo03.log' SIZE 50m;\nALTER DATABASE ADD STANDBY LOGFILE '\/u01\/app\/oracle\/oradata\/orcl\/stbredo04.log' SIZE 50m;<\/code><\/pre>\n
\u68c0\u67e5\u521b\u5efastandby redo log\u7ed3\u679c\nselect group#, thread#, sequence#, archived, status from v$standby_log;<\/code><\/pre>\n
5.\u5907\u5e93\u7684\u76f8\u5e94\u914d\u7f6e<\/h1>\n
\u5c06\u4e3b\u5e93\u7684\u5bc6\u7801\u6587\u4ef6\u3001pfile\u62f7\u8d1d\u5230\u5907\u5e93\u3002\n\nPfile\u751f\u6210\uff1a\ncreate pfile='\/tmp\/orcl.ora' from spfile;\n\n\u5c06pfile\u6587\u4ef6\u62f7\u8d1d\u5230\u5907\u5e93\uff1a\/tmp\n\u5c06\u5bc6\u7801\u6587\u4ef6\u62f7\u8d1d\u5230\u5907\u5e93\uff1a$ORACLE_HOME\/dbs\n\u5728\u5907\u5e93oracle\u7528\u6237\u6267\u884c\nscp oracle@192.168.174.154:\/tmp\/orcl.ora  \/tmp\nscp oracle@192.168.174.154:$ORACLE_HOME\/dbs\/orapworcl $ORACLE_HOME\/dbs\n\n\u5bc6\u7801\u6587\u4ef6\u62f7\u8d1d\u5230\u5907\u5e93\u540e\uff0c\u9700\u8981\u4fee\u6539\u6587\u4ef6\u540d\u3002\u683c\u5f0f\u4e3aorapw+ORACLE_SID\n\nmv orapworcl orapworcldg1\nmv orapworcl orapworcldg2\n\n\u4e3b\u5907\u5e93\u90fd\u6d4b\u8bd5\u8054\u901a\u6027\uff1a\nsqlplus sys\/123456@orcl as sysdba\nsqlplus sys\/123456@orcldg1 as sysdba\nsqlplus sys\/123456@orcldg2 as sysdba<\/code><\/pre>\n
6.\u5907\u5e93\uff1a\u7f16\u8f91\/tmp\/orcl.ora\uff0c\u4fee\u6539\u4e3a\u4ee5\u4e0b\u5185\u5bb9\uff1a<\/h1>\n
\u4e3b\u5e93\uff1a\norcl.__db_cache_size=486539264\norcl.__java_pool_size=16777216\norcl.__large_pool_size=33554432\norcl.__oracle_base='\/u01\/app\/oracle'#ORACLE_BASE set from environment\norcl.__pga_aggregate_target=486539264\norcl.__sga_target=721420288\norcl.__shared_io_pool_size=0\norcl.__shared_pool_size=167772160\norcl.__streams_pool_size=0\n*.audit_file_dest='\/u01\/app\/oracle\/admin\/orcl\/adump'\n*.audit_trail='db'\n*.compatible='11.2.0.4.0'\n*.control_files='\/u01\/app\/oracle\/oradata\/orcl\/control01.ctl','\/u01\/app\/oracle\/oradata\/orcl\/control02.ctl'\n*.db_block_size=8192\n*.db_domain=''\n*.db_flashback_retention_target=2880\n*.db_name='orcl'\n*.db_recovery_file_dest_size=3221225472\n*.db_recovery_file_dest='\/u01\/database'\n*.db_unique_name='orcl'\n*.diagnostic_dest='\/u01\/app\/oracle'\n*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'\n*.fal_client='orcl'\n*.fal_server='orcldg1,orcldg2'\n*.instance_name='orcl'\n*.local_listener='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.174.154)(PORT=1521))))'\n*.log_archive_config='DG_CONFIG=(orcl,orcldg1,orcldg2)'\n*.log_archive_dest_1='LOCATION=\/u01\/app\/oracle\/oradata\/orcl\/arch  VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=orcl'\n*.log_archive_dest_2='SERVICE=orcldg1 LGWR ASYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcldg1'\n*.log_archive_dest_3='SERVICE=orcldg2 LGWR ASYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcldg2'\n*.log_archive_dest_state_1='ENABLE'\n*.log_archive_dest_state_2='ENABLE'\n*.log_archive_dest_state_3='ENABLE'\n*.log_archive_max_processes=4\n*.memory_target=1202716672\n*.open_cursors=300\n*.processes=150\n*.remote_login_passwordfile='EXCLUSIVE'\n*.service_names='orcl'\n*.standby_file_management='AUTO'\n*.undo_tablespace='UNDOTBS1'<\/code><\/pre>\n
\u6539\u672c\uff1a <\/p>\n
\u5907\u5e931\uff1a\n\n*.__oracle_base='\/u01\/app\/oracle'#ORACLE_BASE set from environment  \n*.audit_file_dest='\/orcldg1\/adump\/'\n*.audit_trail='db'\n*.compatible='11.2.0.4.0'\n*.control_files='\/orcldg1\/controlfile\/control01.ctl','\/orcldg1\/controlfile\/control02.ctl'\n*.db_block_size=8192\n*.db_domain=''\n*.db_flashback_retention_target=2880\n*.db_name='orcl'\n*.db_recovery_file_dest_size=3221225472\n*.db_recovery_file_dest='\/orcldg1\/flashback\/'\n*.db_unique_name='orcldg1'\n*.diagnostic_dest='\/u01\/app\/oracle'\n*.fal_client='orcldg1\n*.fal_server='orcl,orcldg2'\n*.instance_name='orcldg1'\n*.local_listener='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.174.155)(PORT=1521))))'\n*.db_file_name_convert='\/u01\/app\/oracle\/oradata\/orcl\/','\/orcldg1\/datafile\/' \n*.log_file_name_convert='\/u01\/app\/oracle\/oradata\/orcl\/','\/orcldg1\/logfile\/'    \n*.log_archive_config='DG_CONFIG=(orcl,orcldg1,orcldg2)'\n*.log_archive_dest_1='LOCATION=\/orcldg1\/archivelog\/  VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=orcldg1'\n*.log_archive_dest_2='SERVICE=orcl LGWR ASYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcl'\n*.log_archive_dest_3='SERVICE=orcldg2 LGWR ASYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcldg2'\n*.log_archive_dest_state_1='ENABLE'\n*.log_archive_dest_state_2='ENABLE'\n*.log_archive_dest_state_3='ENABLE'\n*.log_archive_max_processes=4\n*.memory_target=1202716672\n*.open_cursors=300\n*.processes=150\n*.remote_login_passwordfile='EXCLUSIVE'\n*.service_names='orcldg1'\n*.standby_file_management='AUTO'\n*.undo_tablespace='UNDOTBS1'\n\nmkdir -p \/orcldg1\/adump                                                                                                                         \nmkdir -p \/orcldg1\/controlfile                                                                                                                   \nmkdir -p \/orcldg1\/datafile                                                                                                                      \nmkdir -p \/orcldg1\/archivelog                                                                                                                    \nmkdir -p \/orcldg1\/logfile\nmkdir -p \/orcldg1\/flashback<\/code><\/pre>\n
\u5907\u5e932\uff1a\n\n*.__oracle_base='\/u01\/app\/oracle'#ORACLE_BASE set from environment  \n*.audit_file_dest='\/orcldg2\/adump\/'\n*.audit_trail='db'\n*.compatible='11.2.0.4.0'\n*.control_files='\/orcldg2\/controlfile\/control01.ctl','\/orcldg2\/controlfile\/control02.ctl'\n*.db_block_size=8192\n*.db_domain=''\n*.db_flashback_retention_target=2880\n*.db_name='orcl'\n*.db_recovery_file_dest_size=3221225472\n*.db_recovery_file_dest='\/orcldg2\/flashback\/'\n*.db_unique_name='orcldg2'\n*.diagnostic_dest='\/u01\/app\/oracle'\n*.fal_client='orcldg2\n*.fal_server='orcl,orcldg1'\n*.instance_name='orcldg2'\n*.local_listener='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.174.156)(PORT=1521))))'\n*.db_file_name_convert='\/u01\/app\/oracle\/oradata\/orcl\/','\/orcldg2\/datafile\/' \n*.log_file_name_convert='\/u01\/app\/oracle\/oradata\/orcl\/','\/orcldg2\/logfile\/'    \n*.log_archive_config='DG_CONFIG=(orcl,orcldg1,orcldg2)'\n*.log_archive_dest_1='LOCATION=\/orcldg2\/archivelog\/  VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=orcldg2'\n*.log_archive_dest_2='SERVICE=orcl LGWR ASYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcl'\n*.log_archive_dest_3='SERVICE=orcldg1 LGWR ASYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcldg1'\n*.log_archive_dest_state_1='ENABLE'\n*.log_archive_dest_state_2='ENABLE'\n*.log_archive_dest_state_3='ENABLE'\n*.log_archive_max_processes=4\n*.memory_target=1202716672\n*.open_cursors=300\n*.processes=150\n*.remote_login_passwordfile='EXCLUSIVE'\n*.service_names='orcldg2'\n*.standby_file_management='AUTO'\n*.undo_tablespace='UNDOTBS1'\n\n mkdir -p \/orcldg2\/adump                                                                                                                         \nmkdir -p \/orcldg2\/controlfile                                                                                                                   \nmkdir -p \/orcldg2\/datafile                                                                                                                      \nmkdir -p \/orcldg2\/archivelog                                                                                                                    \nmkdir -p \/orcldg2\/logfile\nmkdir -p \/orcldg2\/flashback    \n<\/code><\/pre>\n
7.\u5907\u5e93\uff1a\u4ee5pfile\u521b\u5efaspfile\u5e76\u542f\u52a8\u6570\u636e\u5e93\u5230nomount\uff1a<\/h1>\n
SQL> create spfile from pfile='\/tmp\/orcldg1.ora';\nSQL> startup nomount\n\nSQL> create spfile from pfile='\/tmp\/orcldg2.ora';\nSQL> startup nomount  <\/code><\/pre>\n
8.\u590d\u5236\u6570\u636e<\/h1>\n
\u5907\u5e931\uff1a\u65b0\u5efa\u811a\u672cvi \/home\/oracle\/duplicate.sh \uff0c\u52a0\u5165\u4ee5\u4e0b\u5185\u5bb9\uff1a\nrman target sys\/123456@orcl auxiliary sys\/123456@orcldg1 nocatalog > duplicate.log <<EOF\nduplicate target database for standby from active database nofilenamecheck;\nEOF<\/code><\/pre>\n
\u5907\u5e932\uff1a\u65b0\u5efa\u811a\u672cvi \/home\/oracle\/duplicate.sh \uff0c\u52a0\u5165\u4ee5\u4e0b\u5185\u5bb9\uff1a\nrman target sys\/123456@orcl auxiliary sys\/123456@orcldg2 nocatalog > duplicate.log <<EOF\nduplicate target database for standby from active database nofilenamecheck;\nEOF<\/code><\/pre>\n
\u540e\u53f0\u6267\u884c\u811a\u672c\n\ncd \/home\/oracle\nchmod a+x duplicate.sh\nnohup .\/duplicate.sh &\n\u89c2\u5bdf\u65e5\u5fd7duplicate.log\u7b49\u5f85\u5b8c\u6210\u3002<\/code><\/pre>\n
9.\u4f7f\u752811G Data Guard\u65b0\u7279\u6027\uff0cArchive Data Guard<\/h1>\n
\u5728\u5907\u5e93\u8fd0\u884c\uff1a<\/p>\n
\u4ee5\u4e0b\u4e3a\u540c\u6b65\u5f52\u6863\uff0c\u9884\u8ba1\u65f6\u95f4\u6bd4\u8f83\u4e45\nalter database recover managed standby database disconnect from session;\nrecover managed standby database cancel; \nalter database open read only;\nalter database recover managed standby database using current logfile disconnect from session;<\/code><\/pre>\n
10.\u67e5\u770b\u5f52\u6863\u662f\u5426\u540c\u6b65\u5b8c\u6210<\/h1>\n
\u4e3b\u5907\u5e93\u5bf9\u6bd4\u662f\u5426\u4e00\u6837\uff1a\n\u4e3b\u5e93\uff1a\nSQL> select * from ( select sequence#,applied from v$archived_log where dest_id=2 order by first_time  desc) where rownum<=10;\n\n SEQUENCE# APPLIED\n---------- ---------\n        22 YES\n        23 YES\n        24 IN-MEMORY\n\n\u5907\u5e93\uff1a\nSQL> select * from ( select sequence#,applied from v$archived_log where dest_id=1 order by first_time desc) where rownum<=10;\n\n\u5728\u4e3b\u5e93\u8fd0\u884c\uff0c\u786e\u8ba4\u662f\u5426\u6709\u65ad\u5c42\n\nSQL> select STATUS, GAP_STATUS from V$ARCHIVE_DEST_STATUS where DEST_ID = 2;\n\nSTATUS    GAP_STATUS\n--------- ------------------------\nVALID     NO GAP\n\nSQL> select STATUS, GAP_STATUS from V$ARCHIVE_DEST_STATUS where DEST_ID = 3;\n\nSTATUS    GAP_STATUS\n--------- ------------------------\nVALID     NO GAP\n\n\u5907\u5e93\u67e5\u770bMRP\u8fdb\u7a0b\uff1a\nselect inst_id,process,status,thread#,sequence#,block# from gv$managed_standby where PROCESS like 'MRP%';\n\n\u4e3b\u5e93\uff1a\n--\u68c0\u67e5\u4e3b\u5e93\u5df2\u7ecf\u4ea7\u751f\u7684\u65e5\u5fd7sequence# \u53f7\nselect thread#,max(sequence#) "Last Primary Seq Generated" from v$archived_log val,v$database vdb where val.resetlogs_change#=vdb.resetlogs_change# group by thread# order by 1;\n\n\u5907\u5e93\uff1a\n-- \u68c0\u67e5\u5907\u5e93\u5df2\u7ecf\u63a5\u6536\u5230\u7684 sequence# \u53f7\nselect thread#,max(sequence#) "Last Standby Seq Received" from v$archived_log val,v$database vdb where val.resetlogs_change#=vdb.resetlogs_change# group by thread# order by 1;\n-- \u68c0\u67e5\u5907\u5e93\u5df2\u7ecf\u5e94\u7528\u5230\u7684 sequence# \u53f7\nselect thread#,max(sequence#) "Last Standby Seq Applied" from v$archived_log val,v$database vdb where val.resetlogs_change#=vdb.resetlogs_change# and val.applied in ('YES','IN-MEMORY') group by thread# order by 1;<\/code><\/pre>\n
1.11\u4e3b\u5e93\u521b\u5efa\u4e34\u65f6\u8868\u786e\u8ba4\u662f\u5426\u6b63\u5e38\u540c\u6b65<\/h1>\n
\u4e3b\u5e93\uff1a\ncreate table test2020 as select * from dual;\n\n\u5907\u5e93\uff1a\nselect * from test2020;<\/code><\/pre>\n","protected":false},"excerpt":{"rendered":"
\u4e3b\u5e93\uff1a ip\uff1a192.168.174.154 \u7cfb\u7edf<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[18,37,40],"tags":[],"class_list":["post-432","post","type-post","status-publish","format-standard","hentry","category-oracle-11g","category--oracle-11g"],"_links":{"self":[{"href":"http:\/\/www.821121.com\/index.php?rest_route=\/wp\/v2\/posts\/432","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/www.821121.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.821121.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.821121.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/www.821121.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=432"}],"version-history":[{"count":3,"href":"http:\/\/www.821121.com\/index.php?rest_route=\/wp\/v2\/posts\/432\/revisions"}],"predecessor-version":[{"id":460,"href":"http:\/\/www.821121.com\/index.php?rest_route=\/wp\/v2\/posts\/432\/revisions\/460"}],"wp:attachment":[{"href":"http:\/\/www.821121.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=432"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.821121.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=432"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.821121.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=432"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}