制御ファイルのバックアップ

[Oracle管理]

Oracle_4-100

制御ファイルのバックアップ

障害に備えて制御ファイルをバックアップする手順です。

バックアップする方法は2種類あります。

  1. 1つは既存制御ファイルを複製してバックアップする方法
  2. SQLスクリプトをトレースファイルに書き出す方法

それでは、まず１つ目の手順から行きます。

 

既存制御ファイルを複製するバックアップ

oracleでログインし、sqlplusを起動します。

[oracle@server01:/home/oracle] sqlplus /nolog
  
SQL*Plus: Release 11.2.0.4.0 Production on 金 10月 18 14:30:10 2013
  
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
  
SQL> conn / as sysdba
アイドル・インスタンスに接続しました。
SQL>

既存制御ファイルを、ASMディスクグループからOSファイルシステム上に書き出す場合は、以下のSQLを実行します。

SQL> alter database backup controlfile to '/home/oracle/control.bak';

データベースが変更されました。

SQL>

 

SQLをトレースファイルに書き出すバックアップ

次に、SQLをトレースファイルに書き出す場合は、以下のSQLを実行します。

SQL> alter database backup controlfile to trace;

データベースが変更されました。

SQL>

どのトレースファイルに書き出されたかは、アラートログを見ると書かれています。今回の例ではアラートログにこのように書かれていました。

alter database backup controlfile to trace
Backup controlfile written to trace file /u01/app/oracle/diag/rdbms/db01/trace/db011_ora_13303848.trc
Completed: alter database backup controlfile to trace

 

トレースファイルの中には、制御ファイルを作成するために必要なSQLが書かれていました。
制御ファイルがどのような情報を保持しているかがわかります。

ファイル内容です。

Trace file /u01/app/oracle/diag/rdbms/db01/db011/trace/db011_ora_13303848.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1
System name:	AIX
Node name:	server01
Release:	1
Version:	7
Machine:	00F86E5F4C00
Instance name: db011
Redo thread mounted by this instance: 1
Oracle process number: 54
Unix process pid: 13303848, image: oracle@server01 (TNS V1-V3)


*** 2013-10-25 16:29:29.618
*** SESSION ID:(592.7) 2013-10-25 16:29:29.618
*** CLIENT ID:() 2013-10-25 16:29:29.618
*** SERVICE NAME:(SYS$USERS) 2013-10-25 16:29:29.618
*** MODULE NAME:(sqlplus@server01 (TNS V1-V3)) 2013-10-25 16:29:29.618
*** ACTION NAME:() 2013-10-25 16:29:29.618
 
-- The following are current System-scope REDO Log Archival related
-- parameters and can be included in the database initialization file.
--
-- LOG_ARCHIVE_DEST=''
-- LOG_ARCHIVE_DUPLEX_DEST=''
--
-- LOG_ARCHIVE_FORMAT=%t_%s_%r.dbf
--
-- DB_UNIQUE_NAME="db01"
--
-- LOG_ARCHIVE_CONFIG='SEND, RECEIVE, NODG_CONFIG'
-- LOG_ARCHIVE_MAX_PROCESSES=4
-- STANDBY_FILE_MANAGEMENT=MANUAL
-- STANDBY_ARCHIVE_DEST=?/dbs/arch
-- FAL_CLIENT=''
-- FAL_SERVER=''
--
-- LOG_ARCHIVE_DEST_2='LOCATION=+DATA/db01/archive2'
-- LOG_ARCHIVE_DEST_2='OPTIONAL REOPEN=300 NODELAY'
-- LOG_ARCHIVE_DEST_2='ARCH NOAFFIRM NOEXPEDITE NOVERIFY SYNC'
-- LOG_ARCHIVE_DEST_2='REGISTER NOALTERNATE NODEPENDENCY'
-- LOG_ARCHIVE_DEST_2='NOMAX_FAILURE NOQUOTA_SIZE NOQUOTA_USED NODB_UNIQUE_NAME'
-- LOG_ARCHIVE_DEST_2='VALID_FOR=(PRIMARY_ROLE,ONLINE_LOGFILES)'
-- LOG_ARCHIVE_DEST_STATE_2=ENABLE
--
-- LOG_ARCHIVE_DEST_1='LOCATION=+DATA/db01/archive1'
-- LOG_ARCHIVE_DEST_1='OPTIONAL REOPEN=300 NODELAY'
-- LOG_ARCHIVE_DEST_1='ARCH NOAFFIRM NOEXPEDITE NOVERIFY SYNC'
-- LOG_ARCHIVE_DEST_1='REGISTER NOALTERNATE NODEPENDENCY'
-- LOG_ARCHIVE_DEST_1='NOMAX_FAILURE NOQUOTA_SIZE NOQUOTA_USED NODB_UNIQUE_NAME'
-- LOG_ARCHIVE_DEST_1='VALID_FOR=(PRIMARY_ROLE,ONLINE_LOGFILES)'
-- LOG_ARCHIVE_DEST_STATE_1=ENABLE
--
-- Below are two sets of SQL statements, each of which creates a new
-- control file and uses it to open the database. The first set opens
-- the database with the NORESETLOGS option and should be used only if
-- the current versions of all online logs are available. The second
-- set opens the database with the RESETLOGS option and should be used
-- if online logs are unavailable.
-- The appropriate set of statements can be copied from the trace into
-- a script file, edited as necessary, and executed when there is a
-- need to re-create the control file.
--
--     Set #1. NORESETLOGS case
--
-- The following commands will create a new control file and use it
-- to open the database.
-- Data used by Recovery Manager will be lost.
-- Additional logs may be required for media recovery of offline
-- Use this only if the current versions of all online logs are
-- available.
-- After mounting the created controlfile, the following SQL
-- statement will place the database in the appropriate
-- protection mode:
--  ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "db01" NORESETLOGS  ARCHIVELOG
    MAXLOGFILES 192
    MAXLOGMEMBERS 3
    MAXDATAFILES 1024
    MAXINSTANCES 32
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 (
    '+DG_REDO_1/db01/redo01.log',
    '+DG_REDO_2/db01/redo02.log'
  ) SIZE 500M BLOCKSIZE 512,
  GROUP 2 (
    '+DG_REDO_1/db01/redo03.log',
    '+DG_REDO_2/db01/redo04.log'
  ) SIZE 500M BLOCKSIZE 512,
  GROUP 3 (
    '+DG_REDO_1/db01/redo05.log',
    '+DG_REDO_2/db01/redo06.log'
  ) SIZE 500M BLOCKSIZE 512,
  GROUP 4 (
    '+DG_REDO_1/db01/redo07.log',
    '+DG_REDO_2/db01/redo08.log'
  ) SIZE 500M BLOCKSIZE 512,
  GROUP 5 (
    '+DG_REDO_1/db01/redo09.log',
    '+DG_REDO_2/db01/redo10.log'
  ) SIZE 500M BLOCKSIZE 512,
  GROUP 6 (
    '+DG_REDO_1/db01/redo11.log',
    '+DG_REDO_2/db01/redo12.log'
  ) SIZE 512M BLOCKSIZE 512,
  GROUP 7 (
    '+DG_REDO_1/db01/redo13.log',
    '+DG_REDO_2/db01/redo14.log'
  ) SIZE 512M BLOCKSIZE 512,
  GROUP 8 (
    '+DG_REDO_1/db01/redo15.log',
    '+DG_REDO_2/db01/redo16.log'
  ) SIZE 500M BLOCKSIZE 512,
  GROUP 9 (
    '+DG_REDO_1/db01/redo17.log',
    '+DG_REDO_2/db01/redo18.log'
  ) SIZE 500M BLOCKSIZE 512,
  GROUP 10 (
    '+DG_REDO_1/db01/redo19.log',
    '+DG_REDO_2/db01/redo20.log'
  ) SIZE 500M BLOCKSIZE 512,
  GROUP 11 (
    '+DG_REDO_1/db01/redo21.log',
    '+DG_REDO_2/db01/redo22.log'
  ) SIZE 500M BLOCKSIZE 512,
  GROUP 12 (
    '+DG_REDO_1/db01/redo23.log',
    '+DG_REDO_2/db01/redo24.log'
  ) SIZE 500M BLOCKSIZE 512,
  GROUP 13 (
    '+DG_REDO_1/db01/redo25.log',
    '+DG_REDO_2/db01/redo26.log'
  ) SIZE 500M BLOCKSIZE 512,
  GROUP 14 (
    '+DG_REDO_1/db01/redo27.log',
    '+DG_REDO_2/db01/redo28.log'
  ) SIZE 500M BLOCKSIZE 512,
  GROUP 15 (
    '+DG_REDO_1/db01/redo29.log',
    '+DG_REDO_2/db01/redo30.log'
  ) SIZE 500M BLOCKSIZE 512,
  GROUP 16 (
    '+DG_REDO_1/db01/redo31.log',
    '+DG_REDO_2/db01/redo32.log'
  ) SIZE 500M BLOCKSIZE 512,
  GROUP 17 (
    '+DG_REDO_1/db01/redo33.log',
    '+DG_REDO_2/db01/redo34.log'
  ) SIZE 500M BLOCKSIZE 512,
  GROUP 18 (
    '+DG_REDO_1/db01/redo35.log',
    '+DG_REDO_2/db01/redo36.log'
  ) SIZE 500M BLOCKSIZE 512,
  GROUP 19 (
    '+DG_REDO_1/db01/redo37.log',
    '+DG_REDO_2/db01/redo38.log'
  ) SIZE 500M BLOCKSIZE 512,
  GROUP 20 (
    '+DG_REDO_1/db01/redo39.log',
    '+DG_REDO_2/db01/redo40.log'
  ) SIZE 500M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
  '+DATA/db01/datafile/system.256.829070339',
  '+DATA/db01/datafile/sysaux.257.829070339',
  '+DATA/db01/datafile/undotbs1.258.829070339',
  '+DATA/db01/datafile/users.259.829070339',
  '+DATA/db01/datafile/undotbs2.264.829070483',
  '+DATA/db01/datafile/tsc1_common.329.829505403',
  '+DATA/db01/datafile/tsgk_data.330.829505405',
  '+DATA/db01/datafile/tsgk_index.331.829505405',
  '+DATA/db01/datafile/tshs_ordsup.332.829505407',
  '+DATA/db01/datafile/tsid_buyer.333.829505407',
  '+DATA/db01/datafile/tsid_dwh_1.334.829505409',
  '+DATA/db01/datafile/tsid_dwh_idx_1.335.829505409',
  '+DATA/db01/datafile/tsjh_data.336.829505411',
  '+DATA/db01/datafile/tsjh_index.337.829505411',
  '+DATA/db01/datafile/tsyn_yunyu.338.829505413',
  '+DATA/db01/datafile/tszk_zaiko.339.829505413',
  '+DATA/db01/datafile/tsid_dwh_1.350.829568629'
CHARACTER SET JA16SJIS
;
-- Commands to re-create incarnation table
-- Below log names MUST be changed to existing filenames on
-- disk. Any one log file from each branch can be used to
-- re-create incarnation records.
-- ALTER DATABASE REGISTER LOGFILE '+DATA/db01/archive2/1_1_827702973.dbf';
-- ALTER DATABASE REGISTER LOGFILE '+DATA/db01/archive2/1_1_829070404.dbf';
-- Recovery is required if any of the datafiles are restored backups,
-- or if the last shutdown was not normal or immediate.
RECOVER DATABASE
-- All logs need archiving and a log switch is needed.
ALTER SYSTEM ARCHIVE LOG ALL;
-- Database can now be opened normally.
ALTER DATABASE OPEN;
-- Commands to add tempfiles to temporary tablespaces.
-- Online tempfiles have complete space information.
-- Other tempfiles may require adjustment.
ALTER TABLESPACE TEMP ADD TEMPFILE '+DATA/db01/tempfile/temp.263.829070411'
     SIZE 46137344  REUSE AUTOEXTEND ON NEXT 655360  MAXSIZE 32767M;
ALTER TABLESPACE TEMP_REF ADD TEMPFILE '+DATA/db01/tempfile/temp_ref.328.829505391'
     SIZE 3072M REUSE AUTOEXTEND OFF;
-- End of tempfile additions.
--
--     Set #2. RESETLOGS case
--
-- The following commands will create a new control file and use it
-- to open the database.
-- Data used by Recovery Manager will be lost.
-- The contents of online logs will be lost and all backups will
-- be invalidated. Use this only if online logs are damaged.
-- After mounting the created controlfile, the following SQL
-- statement will place the database in the appropriate
-- protection mode:
--  ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "db01" RESETLOGS  ARCHIVELOG
    MAXLOGFILES 192
    MAXLOGMEMBERS 3
    MAXDATAFILES 1024
    MAXINSTANCES 32
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 (
    '+DG_REDO_1/db01/redo01.log',
    '+DG_REDO_2/db01/redo02.log'
  ) SIZE 500M BLOCKSIZE 512,
  GROUP 2 (
    '+DG_REDO_1/db01/redo03.log',
    '+DG_REDO_2/db01/redo04.log'
  ) SIZE 500M BLOCKSIZE 512,
  GROUP 3 (
    '+DG_REDO_1/db01/redo05.log',
    '+DG_REDO_2/db01/redo06.log'
  ) SIZE 500M BLOCKSIZE 512,
  GROUP 4 (
    '+DG_REDO_1/db01/redo07.log',
    '+DG_REDO_2/db01/redo08.log'
  ) SIZE 500M BLOCKSIZE 512,
  GROUP 5 (
    '+DG_REDO_1/db01/redo09.log',
    '+DG_REDO_2/db01/redo10.log'
  ) SIZE 500M BLOCKSIZE 512,
  GROUP 6 (
    '+DG_REDO_1/db01/redo11.log',
    '+DG_REDO_2/db01/redo12.log'
  ) SIZE 512M BLOCKSIZE 512,
  GROUP 7 (
    '+DG_REDO_1/db01/redo13.log',
    '+DG_REDO_2/db01/redo14.log'
  ) SIZE 512M BLOCKSIZE 512,
  GROUP 8 (
    '+DG_REDO_1/db01/redo15.log',
    '+DG_REDO_2/db01/redo16.log'
  ) SIZE 500M BLOCKSIZE 512,
  GROUP 9 (
    '+DG_REDO_1/db01/redo17.log',
    '+DG_REDO_2/db01/redo18.log'
  ) SIZE 500M BLOCKSIZE 512,
  GROUP 10 (
    '+DG_REDO_1/db01/redo19.log',
    '+DG_REDO_2/db01/redo20.log'
  ) SIZE 500M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
  '+DATA/db01/datafile/system.256.829070339',
  '+DATA/db01/datafile/sysaux.257.829070339',
  '+DATA/db01/datafile/undotbs1.258.829070339',
  '+DATA/db01/datafile/users.259.829070339',
  '+DATA/db01/datafile/undotbs2.264.829070483',
  '+DATA/db01/datafile/tsc1_common.329.829505403',
  '+DATA/db01/datafile/tsgk_data.330.829505405',
  '+DATA/db01/datafile/tsgk_index.331.829505405',
  '+DATA/db01/datafile/tshs_ordsup.332.829505407',
  '+DATA/db01/datafile/tsid_buyer.333.829505407',
  '+DATA/db01/datafile/tsid_dwh_1.334.829505409',
  '+DATA/db01/datafile/tsid_dwh_idx_1.335.829505409',
  '+DATA/db01/datafile/tsjh_data.336.829505411',
  '+DATA/db01/datafile/tsjh_index.337.829505411',
  '+DATA/db01/datafile/tsyn_yunyu.338.829505413',
  '+DATA/db01/datafile/tszk_zaiko.339.829505413',
  '+DATA/db01/datafile/tsid_dwh_1.350.829568629'
CHARACTER SET JA16SJIS
;
-- Commands to re-create incarnation table
-- Below log names MUST be changed to existing filenames on
-- disk. Any one log file from each branch can be used to
-- re-create incarnation records.
-- ALTER DATABASE REGISTER LOGFILE '+DATA/db01/archive2/1_1_827702973.dbf';
-- ALTER DATABASE REGISTER LOGFILE '+DATA/db01/archive2/1_1_829070404.dbf';
-- Recovery is required if any of the datafiles are restored backups,
-- or if the last shutdown was not normal or immediate.
RECOVER DATABASE USING BACKUP CONTROLFILE
-- Create log files for threads other than thread one.
ALTER DATABASE ADD LOGFILE THREAD 2
  GROUP 11 (
    '+DG_REDO_1/db01/redo21.log',
    '+DG_REDO_2/db01/redo22.log'
  ) SIZE 500M BLOCKSIZE 512 REUSE,
  GROUP 12 (
    '+DG_REDO_1/db01/redo23.log',
    '+DG_REDO_2/db01/redo24.log'
  ) SIZE 500M BLOCKSIZE 512 REUSE,
  GROUP 13 (
    '+DG_REDO_1/db01/redo25.log',
    '+DG_REDO_2/db01/redo26.log'
  ) SIZE 500M BLOCKSIZE 512 REUSE,
  GROUP 14 (
    '+DG_REDO_1/db01/redo27.log',
    '+DG_REDO_2/db01/redo28.log'
  ) SIZE 500M BLOCKSIZE 512 REUSE,
  GROUP 15 (
    '+DG_REDO_1/db01/redo29.log',
    '+DG_REDO_2/db01/redo30.log'
  ) SIZE 500M BLOCKSIZE 512 REUSE,
  GROUP 16 (
    '+DG_REDO_1/db01/redo31.log',
    '+DG_REDO_2/db01/redo32.log'
  ) SIZE 500M BLOCKSIZE 512 REUSE,
  GROUP 17 (
    '+DG_REDO_1/db01/redo33.log',
    '+DG_REDO_2/db01/redo34.log'
  ) SIZE 500M BLOCKSIZE 512 REUSE,
  GROUP 18 (
    '+DG_REDO_1/db01/redo35.log',
    '+DG_REDO_2/db01/redo36.log'
  ) SIZE 500M BLOCKSIZE 512 REUSE,
  GROUP 19 (
    '+DG_REDO_1/db01/redo37.log',
    '+DG_REDO_2/db01/redo38.log'
  ) SIZE 500M BLOCKSIZE 512 REUSE,
  GROUP 20 (
    '+DG_REDO_1/db01/redo39.log',
    '+DG_REDO_2/db01/redo40.log'
  ) SIZE 500M BLOCKSIZE 512 REUSE;
-- Database can now be opened zeroing the online logs.
ALTER DATABASE OPEN RESETLOGS;
-- Commands to add tempfiles to temporary tablespaces.
-- Online tempfiles have complete space information.
-- Other tempfiles may require adjustment.
ALTER TABLESPACE TEMP ADD TEMPFILE '+DATA/db01/tempfile/temp.263.829070411'
     SIZE 46137344  REUSE AUTOEXTEND ON NEXT 655360  MAXSIZE 32767M;
ALTER TABLESPACE TEMP_REF ADD TEMPFILE '+DATA/db01/tempfile/temp_ref.328.829505391'
     SIZE 3072M REUSE AUTOEXTEND OFF;
-- End of tempfile additions.
--

 

 

 

END

このエントリーをはてなブックマークに追加

タグ：, ,

サブコンテンツ

Copyright (C) 2025 オラエーリックスマンの呟き All Rights Reserved.

このページの先頭へ


Warning: Use of undefined constant XML - assumed 'XML' (this will throw an Error in a future version of PHP) in /home/luck777er/it-memo.info/public_html/wp-content/plugins/wp-syntaxhighlighter/wp-syntaxhighlighter.php on line 1048