Oracle：Backup Controlfile
控制文件的备份通过发起命令“alter database backup controlfile …”实现。
具体描述如下所示：
一、To Trace
SQL*Plus连入数据库，发起备份指令
|
1 2 3 4 5 |
SQL> alter database backup controlfile to trace; Database altered. SQL> |
“alter”指令成功后，alert日志中将会出现如下的信息：
|
1 2 3 4 |
Tue Oct 07 02:03:08 2014 alter database backup controlfile to trace Backup controlfile written to trace file /u01/app/oracle/diag/rdbms/orcl11g/orcl11g/trace/orcl11g_ora_11934.trc Completed: alter database backup controlfile to trace |
查看备份的Trace文件：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 |
[ora11g@rhel5u10 ~]$ file /u01/app/oracle/diag/rdbms/orcl11g/orcl11g/trace/orcl11g_ora_11934.trc /u01/app/oracle/diag/rdbms/orcl11g/orcl11g/trace/orcl11g_ora_11934.trc: ASCII English text [ora11g@rhel5u10 ~]$ cat /u01/app/oracle/diag/rdbms/orcl11g/orcl11g/trace/orcl11g_ora_11934.trc | wc -l 159 [ora11g@rhel5u10 ~]$ [ora11g@rhel5u10 ~]$ cat /u01/app/oracle/diag/rdbms/orcl11g/orcl11g/trace/orcl11g_ora_11934.trc Trace file /u01/app/oracle/diag/rdbms/orcl11g/orcl11g/trace/orcl11g_ora_11934.trc Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options ORACLE_HOME = /u01/app/oracle/product/11/dbhome_1 System name: Linux Node name: rhel5u10 Release: 2.6.18-371.el5 Version: #1 SMP Thu Sep 5 21:21:44 EDT 2013 Machine: x86_64 VM name: VMWare Version: 6 Instance name: orcl11g Redo thread mounted by this instance: 1 Oracle process number: 38 Unix process pid: 11934, image: oracle@rhel5u10 (TNS V1-V3) *** 2014-10-07 02:03:08.648 *** SESSION ID:(64.1895) 2014-10-07 02:03:08.648 *** CLIENT ID:() 2014-10-07 02:03:08.648 *** SERVICE NAME:(SYS$USERS) 2014-10-07 02:03:08.648 *** MODULE NAME:(sqlplus@rhel5u10 (TNS V1-V3)) 2014-10-07 02:03:08.648 *** ACTION NAME:() 2014-10-07 02:03:08.648 -- 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="orcl11g" -- -- 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_1='LOCATION=USE_DB_RECOVERY_FILE_DEST' -- LOG_ARCHIVE_DEST_1='MANDATORY NOREOPEN NODELAY' -- LOG_ARCHIVE_DEST_1='ARCH NOAFFIRM EXPEDITE NOVERIFY SYNC' -- LOG_ARCHIVE_DEST_1='NOREGISTER 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 "ORCL11G" NORESETLOGS ARCHIVELOG MAXLOGFILES 16 MAXLOGMEMBERS 3 MAXDATAFILES 100 MAXINSTANCES 8 MAXLOGHISTORY 292 LOGFILE GROUP 1 '/u01/app/oracle/oradata/orcl11g/redo01.log' SIZE 50M BLOCKSIZE 512, GROUP 2 '/u01/app/oracle/oradata/orcl11g/redo02.log' SIZE 50M BLOCKSIZE 512, GROUP 3 '/u01/app/oracle/oradata/orcl11g/redo03.log' SIZE 50M BLOCKSIZE 512 -- STANDBY LOGFILE DATAFILE '/u01/app/oracle/oradata/orcl11g/system01.dbf', '/u01/app/oracle/oradata/orcl11g/sysaux01.dbf', '/u01/app/oracle/oradata/orcl11g/undotbs01.dbf', '/u01/app/oracle/oradata/orcl11g/users01.dbf', '/u01/app/oracle/oradata/orcl11g/example01.dbf' CHARACTER SET WE8MSWIN1252 ; -- 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 '/u01/app/oracle/fast_recovery_area/ORCL11G/archivelog/2014_10_07/o1_mf_1_1_%u_.arc'; -- ALTER DATABASE REGISTER LOGFILE '/u01/app/oracle/fast_recovery_area/ORCL11G/archivelog/2014_10_07/o1_mf_1_1_%u_.arc'; -- 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 '/u01/app/oracle/oradata/orcl11g/temp01.dbf' SIZE 20971520 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M; -- 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 "ORCL11G" RESETLOGS ARCHIVELOG MAXLOGFILES 16 MAXLOGMEMBERS 3 MAXDATAFILES 100 MAXINSTANCES 8 MAXLOGHISTORY 292 LOGFILE GROUP 1 '/u01/app/oracle/oradata/orcl11g/redo01.log' SIZE 50M BLOCKSIZE 512, GROUP 2 '/u01/app/oracle/oradata/orcl11g/redo02.log' SIZE 50M BLOCKSIZE 512, GROUP 3 '/u01/app/oracle/oradata/orcl11g/redo03.log' SIZE 50M BLOCKSIZE 512 -- STANDBY LOGFILE DATAFILE '/u01/app/oracle/oradata/orcl11g/system01.dbf', '/u01/app/oracle/oradata/orcl11g/sysaux01.dbf', '/u01/app/oracle/oradata/orcl11g/undotbs01.dbf', '/u01/app/oracle/oradata/orcl11g/users01.dbf', '/u01/app/oracle/oradata/orcl11g/example01.dbf' CHARACTER SET WE8MSWIN1252 ; -- 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 '/u01/app/oracle/fast_recovery_area/ORCL11G/archivelog/2014_10_07/o1_mf_1_1_%u_.arc'; -- ALTER DATABASE REGISTER LOGFILE '/u01/app/oracle/fast_recovery_area/ORCL11G/archivelog/2014_10_07/o1_mf_1_1_%u_.arc'; -- 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 -- 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 '/u01/app/oracle/oradata/orcl11g/temp01.dbf' SIZE 20971520 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M; -- End of tempfile additions. -- [ora11g@rhel5u10 ~]$ |
二、To [File_name] SQL*Plus发起备份命令：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
[ora11g@rhel5u10 ~]$ pwd /home/ora11g [ora11g@rhel5u10 ~]$ ll total 0 [ora11g@rhel5u10 ~]$ [ora11g@rhel5u10 ~]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.3.0 Production on Tue Oct 7 02:09:35 2014 Copyright (c) 1982, 2011, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> alter database backup controlfile to '/home/ora11g/cnt_orcl11g.ctl'; Database altered. SQL> |
alert日志的记录：
|
1 2 3 |
Tue Oct 07 02:11:07 2014 alter database backup controlfile to '/home/ora11g/cnt_orcl11g.ctl' Completed: alter database backup controlfile to '/home/ora11g/cnt_orcl11g.ctl' |
查看备份文件：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 |
[ora11g@rhel5u10 ~]$ pwd /home/ora11g [ora11g@rhel5u10 ~]$ ll total 9536 -rw-r----- 1 ora11g oinstall 9748480 Oct 7 02:11 cnt_orcl11g.ctl [ora11g@rhel5u10 ~]$ du -sh * 9.4M cnt_orcl11g.ctl [ora11g@rhel5u10 ~]$ [ora11g@rhel5u10 ~]$ file cnt_orcl11g.ctl cnt_orcl11g.ctl: data [ora11g@rhel5u10 ~]$ [ora11g@rhel5u10 ~]$ strings cnt_orcl11g.ctl }|{z 9ORCL11G TAG20141007T021108 F3ORCL11G \wl- F3ORCL11G \wl- orcl11g orcl11g `wl- hwl- F3/2 `wl- hwl- F3/2 /u01/app/oracle/oradata/orcl11g/redo03.log /u01/app/oracle/oradata/orcl11g/redo02.log /u01/app/oracle/oradata/orcl11g/redo01.log /u01/app/oracle/oradata/orcl11g/users01.dbf /u01/app/oracle/oradata/orcl11g/undotbs01.dbf /u01/app/oracle/oradata/orcl11g/sysaux01.dbf /u01/app/oracle/oradata/orcl11g/system01.dbf /u01/app/oracle/oradata/orcl11g/temp01.dbf /u01/app/oracle/oradata/orcl11g/example01.dbf home/ora11g/cnt_orcl11g.ctl /u01/app/oracle/oradata/orcl11g/redo03.log /u01/app/oracle/oradata/orcl11g/redo02.log /u01/app/oracle/oradata/orcl11g/redo01.log /u01/app/oracle/oradata/orcl11g/users01.dbf /u01/app/oracle/oradata/orcl11g/undotbs01.dbf /u01/app/oracle/oradata/orcl11g/sysaux01.dbf /u01/app/oracle/oradata/orcl11g/system01.dbf /u01/app/oracle/oradata/orcl11g/temp01.dbf /u01/app/oracle/oradata/orcl11g/example01.dbf /home/ora11g/cnt_orcl11g.ctl SYSTEM SYSAUX UNDOTBS1 USERS TEMP EXAMPLE SYSTEM SYSAUX UNDOTBS1 USERS TEMP EXAMPLE F3FR /u01/app/oracle/fast_recovery_area/ORCL11G/archivelog/2014_10_06/o1_mf_1_3_b371fmt1_.arc \wl- u01/app/oracle/oradata/orcl11g/example01.dbf \wl- /u01/app/oracle/oradata/orcl11g/example01.dbf orcl11g orcl11g \wl- \wl- \wl- orcl11g Pg+m dtUNNAMED_INSTANCE_2 UNNAMED_INSTANCE_3 UNNAMED_INSTANCE_4 UNNAMED_INSTANCE_5 UNNAMED_INSTANCE_6 UNNAMED_INSTANCE_7 UNNAMED_INSTANCE_8 orcl11g dtUNNAMED_INSTANCE_2 UNNAMED_INSTANCE_3 UNNAMED_INSTANCE_4 UNNAMED_INSTANCE_5 UNNAMED_INSTANCE_6 UNNAMED_INSTANCE_7 UNNAMED_INSTANCE_8 ACM unit testing operation LSB Database Guard Supplemental Log Data DDL LSB Role Change Support RFS block and kill across RAC RAC-wide SGA ACM unit testing operation LSB Database Guard Supplemental Log Data DDL LSB Role Change Support RFS block and kill across RAC RAC-wide SGA [ora11g@rhel5u10 ~]$ [ora11g@rhel5u10 ~]$ |
——————————————————————
Ending。
