{"id":543,"date":"2020-03-03T16:17:37","date_gmt":"2020-03-03T08:17:37","guid":{"rendered":"http:\/\/www.821121.com\/?p=543"},"modified":"2021-05-12T16:56:57","modified_gmt":"2021-05-12T08:56:57","slug":"oracle11g%e5%8d%95%e5%ae%9e%e4%be%8bfor%e5%8d%95%e5%ae%9e%e4%be%8bdg%e6%90%ad%e5%bb%ba-2","status":"publish","type":"post","link":"http:\/\/www.821121.com\/?p=543","title":{"rendered":"Oracle11g\u5355\u5b9e\u4f8bfor\u5355\u5b9e\u4f8bDG\u642d\u5efa"},"content":{"rendered":"
\u4e3b\u5e93\uff1a\nip\uff1a10.1.200.66     \u7cfb\u7edf\uff1alinux6.5   \u6570\u636e\u5e93\uff1aoracle11g\u5355\u5b9e\u4f8b    sys\u5bc6\u7801\uff1aoracle\ndb_name=orcl,\u5373instance_name\u3001db_unique_name \u3001service_name\u90fd\u4e3aorcl\n\n\u5907\u5e93\uff1a\nip\uff1a10.1.200.67     \u7cfb\u7edf\uff1alinux6.5   \u6570\u636e\u5e93\uff1aoracle11g\u5355\u5b9e\u4f8b    sys\u5bc6\u7801\uff1aoracle\n\n\u642d\u5efa\u524d\u51c6\u5907\uff1a\n\u9700\u8981\u572867\u8fd9\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\uff0c2\u4e2a\u5e93\u9700\u8981\u4e00\u5b9a\u4e0d\u4e00\u6837\uff0c\u5176\u4f59\u968f\u610f<\/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 oracle;\n1.1\u786e\u8ba4\u4e3b\u5e93\u662f\u5426\u5f00\u542farchivelog\u4e0eforce log \nselect log_mode,force_logging from v$database; --\u68c0\u67e5\nalter database force logging; --\u5f00\u542f\n\narchive log list;\nalter database archivelog;<\/code><\/pre>\n
2\u3001\u914d\u7f6e\u5907\u5e93\u76d1\u542c<\/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\nLISTENER =\n  (DESCRIPTION_LIST =\n    (DESCRIPTION =\n      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.1.200.67)(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 = orcldg)\n      (ORACLE_HOME = \/u01\/app\/oracle\/product\/11.2.0.4\/db_1)\n      (SID_NAME = orcldg)\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\n\n\u4e3b\u5907\u5e93\u90fd\u914d\u7f6e$ORACLE_HOME\/network\/admin\/tnsnames.ora\norcl =\n  (DESCRIPTION =\n    (ADDRESS_LIST =\n      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.1.200.66)(PORT = 1521))\n    )\n    (CONNECT_DATA =\n      (SERVER = DEDICATED)\n      (SERVICE_NAME = orcl)\n    )\n  )\n\norcldg =\n  (DESCRIPTION =\n    (ADDRESS_LIST =\n      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.1.200.67)(PORT = 1521))\n    )\n    (CONNECT_DATA =\n      (SERVER = DEDICATED)\n      (SERVICE_NAME = orcldg)\n    )\n  )<\/code><\/pre>\n
3\u3001\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;\n\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;\n\n\u68c0\u67e5\u521b\u5efastandby redo log\u7ed3\u679c\nselect group#, thread#, sequence#, archived, status from v$standby_log;\n<\/code><\/pre>\n
4\u3001\u914d\u7f6e\u4e3b\u5e93\u521d\u59cb\u5316\u53c2\u6570\u6587\u4ef6<\/h1>\n
\u4e3b\u5e93\uff1a\n\nalter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(orcl,orcldg)' scope=both;  --DG_CONFIG\u586bdb_unique_name\nALTER SYSTEM SET LOG_ARCHIVE_DEST_1='LOCATION=\/u01\/app\/oracle\/product\/11.2.0.4\/db_1\/dbs\/arch  VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=orcl' scope=both;\nALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=orcldg LGWR ASYNC  VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcldg' scope=both;\nALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE scope=both;\nALTER SYSTEM SET FAL_SERVER = orcldg scope=both;  --\u670d\u52a1\u540d\nalter system set STANDBY_FILE_MANAGEMENT=MANUAL scope=both;<\/code><\/pre>\n
5\u3001\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@10.1.200.66:\/tmp\/orcl.ora  \/tmp\nscp oracle@10.1.200.66:$ORACLE_HOME\/dbs\/orapworcl $ORACLE_HOME\/dbs\nmv orapworcl orapworcldg\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\u4e3b\u5907\u5e93\u90fd\u6d4b\u8bd5\u8054\u901a\u6027\uff1a\nsqlplus sys\/oracle@orcl as sysdba\nsqlplus sys\/oracle@orcldg as sysdba\n<\/code><\/pre>\n
\u5907\u5e93\uff1a\u7f16\u8f91\/tmp\/orcl.ora\uff0c\u4fee\u6539\u4e3a\u4ee5\u4e0b\u5185\u5bb9\uff1a\n*.__oracle_base='\/u01\/app\/oracle'#ORACLE_BASE set from environment\n*.audit_file_dest='\/orcldg\/adump'\n*.control_files='\/orcldg\/controlfile\/control01.ctl','\/orcldg\/controlfile\/control02.ctl','\/orcldg\/controlfile\/control03.ctl'#Restore Controlfile\n*.db_file_name_convert='\/u01\/app\/oracle\/oradata\/orcl\/','\/orcldg\/datafile\/'\n*.db_name='orcl'\n*.db_unique_name='orcldg'\n*.diagnostic_dest='\/u01\/app\/oracle'\n*.fal_client='orcldg'\n*.fal_server='orcl'\n*.log_archive_config='DG_CONFIG=(orcldg,orcl)'\n*.log_archive_dest_1='LOCATION=\/orcldg\/archivelog\/  VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=orcldg'\n*.log_archive_dest_2='SERVICE=orcl LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcl'\n*.log_file_name_convert='\/u01\/app\/oracle\/oradata\/orcl\/','\/orcldg\/logfile\/'\n*.compatible='11.2.0.4.0'\n*.standby_file_management='AUTO'\n\n\u4fee\u6539\/tmp\/orcl.ora\u6587\u4ef6\u540d\u4e3a\/tmp\/orcldg.ora\n\u7528oracle\u7528\u6237\u5728\u5907\u5e93\u521b\u5efa\u4e0a\u9762\u6d89\u53ca\u5230\u7684\u8def\u5f84\uff08\u76f8\u5173\u7684\u8def\u5f84\u914d\u7f6e\uff0c\u53ef\u6839\u636e\u5b9e\u9645\u60c5\u51b5\u4fee\u6539\uff09\nmkdir -p \/orcldg\/adump\nmkdir -p \/orcldg\/controlfile\nmkdir -p \/orcldg\/datafile\nmkdir -p \/orcldg\/archivelog\nmkdir -p \/orcldg\/logfile<\/code><\/pre>\n
6\u3001\u5907\u5e93\uff1a\u4ee5pfile\u521b\u5efaspfile\u5e76\u542f\u52a8\u6570\u636e\u5e93\u5230nomount\uff1a<\/h1>\n
SQL> create spfile from pfile='\/tmp\/orcldg.ora'\nSQL> startup nomount <\/code><\/pre>\n
7\u3001\u590d\u5236\u6570\u636e<\/h1>\n
\u5907\u5e93\uff1a\u65b0\u5efa\u811a\u672cvi \/home\/oracle\/duplicate.sh \uff0c\u52a0\u5165\u4ee5\u4e0b\u5185\u5bb9\uff1a\nrman target sys\/oracle@orcl auxiliary sys\/oracle@orcldg nocatalog > duplicate.log <<EOF\nduplicate target database for standby from active database nofilenamecheck;\nEOF\n\n\u540e\u53f0\u6267\u884c\u811a\u672c\ncd \/home\/oracle\nchmod a+x duplicate.sh\nnohup .\/duplicate.sh &\n\u89c2\u5bdf\u65e5\u5fd7duplicate.log\u7b49\u5f85\u5b8c\u6210\u3002\n\n\u4ee5\u4e0b\u4e3a\u540c\u6b65\u5f52\u6863\uff0c\u9884\u8ba1\u65f6\u95f4\u6bd4\u8f83\u4e45\nSQL> alter database recover managed standby database disconnect from session;\n<\/code><\/pre>\n
8\u3001\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;<\/code><\/pre>\n
9\u3001\u4f7f\u752811G Data Guard\u65b0\u7279\u6027\uff0cArchive Data Guard<\/h1>\n
\u5728\u5907\u5e93\u8fd0\u884c\uff1a\nSQL> recover managed standby database cancel; \nMedia recovery complete.\n\nSQL> alter database open read only;\n\nDatabase altered.\n\nSQL> alter database recover managed standby database using current logfile disconnect from session;\n\nDatabase altered.\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<\/code><\/pre>\n
10\u3001\u4e3b\u5e93\u521b\u5efa\u4e34\u65f6\u8868\u786e\u8ba4\u662f\u5426\u6b63\u5e38\u540c\u6b65<\/h1>\n
create table test2016 as select * from dual;\n\nselect * from test2016;<\/code><\/pre>\n","protected":false},"excerpt":{"rendered":"
\u4e3b\u5e93\uff1a ip\uff1a10.1.200.66 \u7cfb\u7edf\uff1alin<\/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-543","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\/543","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=543"}],"version-history":[{"count":2,"href":"http:\/\/www.821121.com\/index.php?rest_route=\/wp\/v2\/posts\/543\/revisions"}],"predecessor-version":[{"id":1536,"href":"http:\/\/www.821121.com\/index.php?rest_route=\/wp\/v2\/posts\/543\/revisions\/1536"}],"wp:attachment":[{"href":"http:\/\/www.821121.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=543"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.821121.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=543"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.821121.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=543"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}