{"id":3262,"date":"2018-02-04T10:47:43","date_gmt":"2018-02-04T01:47:43","guid":{"rendered":"http:\/\/haisins.epac.to\/wordpress\/?p=3262"},"modified":"2018-02-04T10:47:43","modified_gmt":"2018-02-04T01:47:43","slug":"bcv-%ed%99%98%ea%b2%bd-split-%ec%9d%84-%ec%9d%b4%ec%9a%a9%ed%95%9c-oracle-db-hot-backup-%ec%8a%a4%ed%81%ac%eb%a6%bd%ed%8a%b8","status":"publish","type":"post","link":"http:\/\/haisins.synology.me\/wordpress\/?p=3262","title":{"rendered":"BCV \ud658\uacbd Split \uc744 \uc774\uc6a9\ud55c Oracle DB Hot backup \uc2a4\ud06c\ub9bd\ud2b8"},"content":{"rendered":"<p>\uc2a4\ud1a0\ub9ac\uc9c0 BCV \ud658\uacbd Split \uc744 \uc774\uc6a9\ud55c Oracle DB Hot backup \uc2a4\ud06c\ub9bd\ud2b8 \uc785\ub2c8\ub2e4.<\/p>\n<p>&nbsp;<\/p>\n<p>begin_backup -&gt; BCV, split -&gt; end_backup \uc21c\uc73c\ub85c \uc9c4\ud589 \ud569\ub2c8\ub2e4.<\/p>\n<p>&nbsp;<\/p>\n<p><strong>BEGIN BACKUP \ubd80\ubb38<\/strong><\/p>\n<p>&nbsp;<\/p>\n<p>begin_scr.sql<\/p>\n<pre class=\"lang:plsql decode:true\">set pause off\r\nSET HEADING OFF\r\nSET SERVEROUTPUT OFF\r\nSET LINESIZE 500\r\nSET PAGESIZE 10000\r\nSET SPACE 0 \r\nSET FEEDBACK OFF\r\nSET TRIMSPOOL ON\r\nSET TERM OFF \r\nSET TIMING OFF\r\n\r\nSPOOL \/tmp\/begin_bk_exec.sql\r\n\r\nselect    'alter database backup controlfile to trace;'||chr(10) from dual;\r\nselect    'alter system switch logfile;'||chr(10) from dual ;\r\nselect    'alter system switch logfile;'||chr(10) from dual ;\r\nselect    'alter system switch logfile;'||chr(10) from dual ;\r\n\r\n\r\nSELECT 'ALTER TABLESPACE '||TABLESPACE_NAME||' BEGIN BACKUP;'   FROM DBA_TABLESPACES  WHERE TABLESPACE_NAME NOT LIKE '%TEMP%';\r\n\r\n\r\n\r\nSPOOL OFF\r\n<\/pre>\n<p>&nbsp;<\/p>\n<p>begin_backup.sh<\/p>\n<p>&nbsp;<\/p>\n<pre class=\"lang:plsql decode:true\">#!\/bin\/ksh\r\n###########################################################################\r\n# haisins begin_backup.sh                                                 #\r\n#                                                                         #\r\n# ORACLE LOG SWITCH SYNTAX                                                #\r\n# (*) Last Archived file on BCV devices before DG2(Archived FS) split     # \r\n# Single DB  : alter system switch logfile; \/* 1 Redo log switch *\/       #  \r\n# RAC        : alter system archive log current; \/* 2 Redo log switch *\/  # \r\n#                                                                         #\r\n###########################################################################\r\nexport ORACLE_BASE=\/oracle\r\nexport ORACLE_HOME=$ORACLE_BASE\/product\/102\r\nexport ORA_CRS_HOME=$ORACLE_BASE\/crs\r\nexport ORACLE_TERM=vt100\r\nexport ORACLE_OWNER=oraORADB\r\nexport ORACLE_SID=ORADB1\r\nexport TEMP=$ORACLE_BASE\/tmp \r\nexport TMPDIR=$ORACLE_BASE\/tmp \r\nexport DATA_PUMP_DIR=\/oracle\r\nexport ORA_NLS33=$ORACLE_HOME\/ocommon\/nls\/admin\/data \r\nexport TNS_ADMIN=$ORACLE_HOME\/network\/admin \r\nexport LD_LIBRARY_PATH=$ORACLE_HOME\/lib:$ORACLE_HOME\/lib32:\/usr\/lib\r\nexport LIBPATH=$ORACLE_HOME\/lib:$ORACLE_HOME\/lib32:$ORACLE_HOME\/lib64:$ORACLE_HOME\/network\/lib:\/usr\/lib \r\nexport DISPLAY=1.1.1.1:0.0\r\nexport NLS_LANG=American_america.KO16KSC5601\r\nexport PATH=$ORA_CRS_HOME\/bin:$ORACLE_HOME\/bin:\/bin:\/usr\/bin:\/sbin:\/usr\/ccs\/bin:.:$PATH:$ORACLE_HOME\/OPatch\r\nexport AIXTHREAD_SCOPE=S\r\n\r\n\r\n\r\nexport LOG_DIR=\"\/opt\/emcscripts\/BCV\/log\"\r\nexport DATE_TIME=`date +%Y%m%d%H%M`\r\nexport LOG_FILE=\"${LOG_DIR}\/${DATE_TIME}.oracle.beginbackup.log\"\r\n\r\n# Check for the status of log directory\r\ntouch ${LOG_DIR}\/ok.tmp\r\nif [ $? -ne 0 ]\r\nthen\r\necho \"\\nBegin Backup cannot be started. Check the Directory: ${LOG_DIR}\\n\"\r\nreturn 1\r\nfi\r\n\r\n# Make sure all the tablespace is not in the backup mode\r\nsqlplus -s \"\/ as sysdba\" &lt;&lt;AAA &gt; \/dev\/null\r\nspool ${LOG_DIR}\/bk1.tmp\r\nselect decode(count(*),0,'None','SomeInBackupMode') as BackupInProgress from v\\$backup where status = 'ACTIVE';\r\nspool off\r\nAAA\r\n\r\n# Check if the sqlplus command succeeded\r\nif [ $? -ne 0 ]\r\nthen\r\necho \"\\nERROR: SQLPLUS Failed!!!\\n\"\r\nreturn 1\r\nfi\r\n\r\n# Log error if some tablespaces are already in Backup mode\r\ngrep SomeInBackupMode ${LOG_DIR}\/bk1.tmp &gt; \/dev\/null\r\nif [ $? -eq 0 ]\r\nthen\r\nsqlplus -s \"\/ as sysdba\" &lt;&lt;BBB &gt; \/dev\/null\r\nspool ${LOG_DIR}\/${DATE_TIME}.SomeInBackupMode.err\r\nselect distinct a.tablespace_name, b.file#, b.status, to_char(b.time, 'YYYY\/MM\/DD HH24:MI:SS') as time from dba_data_files a, v\\$backup b where a.file_id = b.file# and b.status = 'ACTIVE';\r\nspool off\r\nBBB\r\necho \"\\nSome tablespaces are already in Backup Mode Before BEGIN BACKUP!!!\\n\"\r\nreturn 1\r\nfi\r\n\r\n# Begin Backup Script\r\nsqlplus -s \"\/ as sysdba\" &lt;&lt;CCC &gt;&gt; ${LOG_FILE}\r\nalter system archive log current;\r\nspool ${LOG_DIR}\/begin_backup.tmp\r\nselect distinct 'alter tablespace '|| a.tablespace_name ||' begin backup;' from dba_data_files a, v\\$backup b where a.file_id = b.file# and b.status='NOT ACTIVE';\r\nspool off\r\n!grep \"^alter\" ${LOG_DIR}\/begin_backup.tmp &gt; ${LOG_DIR}\/begin_backup.sql\r\n@${LOG_DIR}\/begin_backup.sql\r\nCCC\r\n<\/pre>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<hr \/>\n<p><strong>END BACKUP \ubd80\ubb38<\/strong><\/p>\n<p>&nbsp;<\/p>\n<p>end_scr.sql<\/p>\n<p>&nbsp;<\/p>\n<pre class=\"lang:plsql decode:true \">set pause off\r\nSET HEADING OFF\r\nSET SERVEROUTPUT OFF\r\nSET LINESIZE 500\r\nSET PAGESIZE 10000\r\nSET SPACE 0 \r\nSET FEEDBACK OFF\r\nSET TRIMSPOOL ON\r\nSET TERM OFF \r\nSET TIMING OFF\r\n\r\nSPOOL \/tmp\/end_bk_exec.sql\r\n\r\n\r\nSELECT 'ALTER TABLESPACE '||TABLESPACE_NAME||' END BACKUP;'   FROM DBA_TABLESPACES  WHERE TABLESPACE_NAME NOT LIKE '%TEMP%';\r\n\r\nselect    'alter database backup controlfile to trace;'||chr(10) from dual;\r\nselect    'alter system switch logfile;'||chr(10) from dual ;\r\nselect    'alter system switch logfile;'||chr(10) from dual ;\r\nselect    'alter system switch logfile;'||chr(10) from dual ;\r\n\r\nSPOOL OFF\r\n<\/pre>\n<p>&nbsp;<\/p>\n<p>end_backup.sh<\/p>\n<p>&nbsp;<\/p>\n<pre class=\"lang:plsql decode:true \">#!\/bin\/ksh\r\n\r\n###########################################################################\r\n# haisins end_backup.sh                                                   #\r\n#                                                                         #\r\n# ORACLE LOG SWITCH SYNTAX                                                #\r\n# (*) Last Archived file on BCV devices before DG2(Archived FS) split     # \r\n# Single DB  : alter system switch logfile; \/* 1 Redo log switch *\/       #  \r\n# RAC        : alter system archive log current; \/* 2 Redo log switch *\/  # \r\n#                                                                         #\r\n###########################################################################\r\n\r\n\r\nexport ORACLE_BASE=\/oracle\r\nexport ORACLE_HOME=$ORACLE_BASE\/product\/102\r\nexport ORA_CRS_HOME=$ORACLE_BASE\/crs\r\nexport ORACLE_TERM=vt100\r\nexport ORACLE_OWNER=oraORADB\r\nexport ORACLE_SID=ORADB1\r\nexport TEMP=$ORACLE_BASE\/tmp \r\nexport TMPDIR=$ORACLE_BASE\/tmp \r\nexport DATA_PUMP_DIR=\/oracle\r\nexport ORA_NLS33=$ORACLE_HOME\/ocommon\/nls\/admin\/data \r\nexport TNS_ADMIN=$ORACLE_HOME\/network\/admin \r\nexport LD_LIBRARY_PATH=$ORACLE_HOME\/lib:$ORACLE_HOME\/lib32:\/usr\/lib\r\nexport LIBPATH=$ORACLE_HOME\/lib:$ORACLE_HOME\/lib32:$ORACLE_HOME\/lib64:$ORACLE_HOME\/network\/lib:\/usr\/lib \r\nexport DISPLAY=172.18.185.36:0.0\r\nexport NLS_LANG=American_america.KO16KSC5601\r\nexport PATH=$ORA_CRS_HOME\/bin:$ORACLE_HOME\/bin:\/bin:\/usr\/bin:\/sbin:\/usr\/ccs\/bin:.:$PATH:$ORACLE_HOME\/OPatch\r\nexport AIXTHREAD_SCOPE=S\r\n\r\nexport LOG_DIR=\"\/opt\/emcscripts\/BCV\/log\"\r\nexport DATE_TIME=`date +%Y%m%d%H%M`\r\nexport LOG_FILE=\"${LOG_DIR}\/${DATE_TIME}.oracle.endbackup.log\"\r\n\r\nsqlplus -s \"\/ as sysdba\" &lt;&lt;ABC &gt;&gt; ${LOG_FILE}\r\n#alter system switch logfile;\r\nalter system archive log current;\r\nspool ${LOG_DIR}\/end_backup.tmp\r\nselect distinct 'alter tablespace '|| a.tablespace_name ||' end backup;' from dba_data_files a, v\\$backup b where a.file_id = b.file# and b.status='ACTIVE';\r\nspool off\r\n!grep \"^alter\" ${LOG_DIR}\/end_backup.tmp &gt; ${LOG_DIR}\/end_backup.sql\r\n@${LOG_DIR}\/end_backup.sql\r\n\r\nspool ${LOG_DIR}\/bk2.tmp\r\nselect decode(count(*),0,'None','SomeInBackupMode') as BackupInProgress from v\\$backup where status = 'ACTIVE';\r\nspool off\r\n\r\nalter database backup controlfile to trace;\r\nalter database backup controlfile to '${LOG_DIR}\/${DATE_TIME}.control.backup' reuse;\r\n#alter system switch logfile;\r\nalter system archive log current; \r\n\r\nselect to_char(sysdate, 'YYYY\/MM\/DD HH24:MI:SS') from dual;\r\nABC\r\n\r\nif [ $? -ne 0 ]\r\nthen\r\necho \"\\nERROR: SQLPLUS Failed!!!\\n\"\r\nreturn 1\r\nfi\r\n\r\ngrep SomeInBackupMode ${LOG_DIR}\/bk2.tmp &gt; \/dev\/null\r\nif [ $? -eq 0 ]\r\nthen\r\nsqlplus -s \"\/ as sysdba\" &lt;&lt;EOF &gt; \/dev\/null\r\nspool ${LOG_DIR}\/${DATE_TIME}.StillInBackupMode.err\r\nselect distinct a.tablespace_name, b.file#, b.status, to_char(b.time, 'YYYY\/MM\/DD HH24:MI:SS') as time from dba_data_files a, v\\$backup b where a.file_id = b.file# and b.status = 'ACTIVE';\r\nspool off\r\nEOF\r\n\r\necho \"\\nERROR: Some Tablespaces are still in Backup Mode After END BACKUP!!!\\n\"\r\nreturn 1\r\nfi\r\n\r\nrm ${LOG_DIR}\/ok.tmp ${LOG_DIR}\/bk1.tmp ${LOG_DIR}\/bk2.tmp ${LOG_DIR}\/begin_backup.tmp ${LOG_DIR}\/end_backup.tmp ${LOG_DIR}\/begin_backup.sql ${LOG_DIR}\/end_backup.sql\r\n<\/pre>\n<p>&nbsp;<\/p>\n","protected":false},"excerpt":{"rendered":"<p>\uc2a4\ud1a0\ub9ac\uc9c0 BCV \ud658\uacbd Split \uc744 \uc774\uc6a9\ud55c Oracle DB Hot backup \uc2a4\ud06c\ub9bd\ud2b8 \uc785\ub2c8\ub2e4. &nbsp; begin_backup -&gt; BCV, split -&gt; end_backup \uc21c\uc73c\ub85c \uc9c4\ud589 \ud569\ub2c8\ub2e4. &nbsp; BEGIN BACKUP \ubd80\ubb38 &nbsp; begin_scr.sql set pause off SET HEADING OFF SET SERVEROUTPUT OFF SET LINESIZE 500 SET PAGESIZE 10000 SET SPACE 0 SET FEEDBACK OFF SET TRIMSPOOL ON SET TERM OFF [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":2862,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_bbp_topic_count":0,"_bbp_reply_count":0,"_bbp_total_topic_count":0,"_bbp_total_reply_count":0,"_bbp_voice_count":0,"_bbp_anonymous_reply_count":0,"_bbp_topic_count_hidden":0,"_bbp_reply_count_hidden":0,"_bbp_forum_subforum_count":0,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"spay_email":""},"categories":[3],"tags":[971,972],"jetpack_featured_media_url":"https:\/\/i0.wp.com\/haisins.synology.me\/wordpress\/wp-content\/uploads\/2018\/02\/managed-services2.jpg?fit=1200%2C386","amp_enabled":true,"_links":{"self":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/3262"}],"collection":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=3262"}],"version-history":[{"count":1,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/3262\/revisions"}],"predecessor-version":[{"id":3263,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/3262\/revisions\/3263"}],"wp:featuredmedia":[{"embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/media\/2862"}],"wp:attachment":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=3262"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=3262"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=3262"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}