1、备份到文件
SQL> ALTER DATABASE BACKUP CONTROLFILE TO '/opt/oracle/jack/crontrolbak.ctl'; Database altered. SQL> ! ls -lh /opt/oracle/jack total 6.8M -rw-r----- 1 oracle oinstall 6.8M Jun 24 18:53 crontrolbak.ctl -rwxrwxr-x 1 oracle oinstall 64 Jun 24 18:51 jack.sh SQL>
2、备份到trace
SQL> ALTER DATABASE BACKUP CONTROLFILE TO '/opt/oracle/jack/crontrolbak.ctl'; Database altered. SQL> ! ls -lh /opt/oracle/jack total 6.8M -rw-r----- 1 oracle oinstall 6.8M Jun 24 18:53 crontrolbak.ctl -rwxrwxr-x 1 oracle oinstall 64 Jun 24 18:51 jack.sh SQL>
备份后的控制文件内容一览：
SQL> ! more /opt/oracle/admin/ocm/udump/ocm_ora_5472.trc /opt/oracle/admin/ocm/udump/ocm_ora_5472.trc Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production With the Partitioning, OLAP and Data Mining options ORACLE_HOME = /opt/oracle/product System name: Linux Node name: ocmserver.com Release: 2.6.18-164.el5 Version: #1 SMP Tue Aug 18 15:51:54 EDT 2009 Machine: i686 Instance name: ocm Redo thread mounted by this instance: 1 Oracle process number: 22 Unix process pid: 5472, image: oracle@ocmserver.com (TNS V1-V3) *** ACTION NAME:() 2013-06-24 19:05:04.963 *** MODULE NAME:(SQL*Plus) 2013-06-24 19:05:04.963 *** SERVICE NAME:(SYS$USERS) 2013-06-24 19:05:04.963 *** SESSION ID:(140.345) 2013-06-24 19:05:04.963 *** 2013-06-24 19:05:04.963 -- 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="ocm" -- -- LOG_ARCHIVE_CONFIG='SEND, RECEIVE, NODG_CONFIG' -- LOG_ARCHIVE_MAX_PROCESSES=2 -- STANDBY_FILE_MANAGEMENT=MANUAL -- STANDBY_ARCHIVE_DEST=?/dbs/arch -- FAL_CLIENT='' -- FAL_SERVER='' -- -- LOG_ARCHIVE_DEST_10='LOCATION=USE_DB_RECOVERY_FILE_DEST' -- LOG_ARCHIVE_DEST_10='OPTIONAL REOPEN=300 NODELAY' -- LOG_ARCHIVE_DEST_10='ARCH NOAFFIRM NOEXPEDITE NOVERIFY SYNC' -- LOG_ARCHIVE_DEST_10='REGISTER NOALTERNATE NODEPENDENCY' -- LOG_ARCHIVE_DEST_10='NOMAX_FAILURE NOQUOTA_SIZE NOQUOTA_USED NODB_UNIQUE_NAME' -- LOG_ARCHIVE_DEST_10='VALID_FOR=(PRIMARY_ROLE,ONLINE_LOGFILES)' -- LOG_ARCHIVE_DEST_STATE_10=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 "OCM" NORESETLOGS NOARCHIVELOG MAXLOGFILES 16 MAXLOGMEMBERS 3 MAXDATAFILES 100 MAXINSTANCES 8 MAXLOGHISTORY 292 LOGFILE GROUP 1 '/opt/oracle/oradata/ocm/redo01.log' SIZE 50M, GROUP 2 '/opt/oracle/oradata/ocm/redo02.log' SIZE 50M, GROUP 3 '/opt/oracle/oradata/ocm/redo03.log' SIZE 50M -- STANDBY LOGFILE DATAFILE '/opt/oracle/oradata/ocm/system01.dbf', '/opt/oracle/oradata/ocm/undotbs01.dbf', '/opt/oracle/oradata/ocm/sysaux01.dbf', '/opt/oracle/oradata/ocm/users01.dbf', '/opt/oracle/oradata/ocm/example01.dbf' CHARACTER SET AL32UTF8 ; -- 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 '/opt/oracle/flash_recovery_area/OCM/archivelog/2013_06_24/o1_mf_1_1_%u_.arc'; -- ALTER DATABASE REGISTER LOGFILE '/opt/oracle/flash_recovery_area/OCM/archivelog/2013_06_24/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 -- 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 '/opt/oracle/oradata/ocm/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 "OCM" RESETLOGS NOARCHIVELOG MAXLOGFILES 16 MAXLOGMEMBERS 3 MAXDATAFILES 100 MAXINSTANCES 8 MAXLOGHISTORY 292 LOGFILE GROUP 1 '/opt/oracle/oradata/ocm/redo01.log' SIZE 50M, GROUP 2 '/opt/oracle/oradata/ocm/redo02.log' SIZE 50M, GROUP 3 '/opt/oracle/oradata/ocm/redo03.log' SIZE 50M -- STANDBY LOGFILE DATAFILE '/opt/oracle/oradata/ocm/system01.dbf', '/opt/oracle/oradata/ocm/undotbs01.dbf', '/opt/oracle/oradata/ocm/sysaux01.dbf', '/opt/oracle/oradata/ocm/users01.dbf', '/opt/oracle/oradata/ocm/example01.dbf' CHARACTER SET AL32UTF8 ; -- 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 '/opt/oracle/flash_recovery_area/OCM/archivelog/2013_06_24/o1_mf_1_1_%u_.arc'; -- ALTER DATABASE REGISTER LOGFILE '/opt/oracle/flash_recovery_area/OCM/archivelog/2013_06_24/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 '/opt/oracle/oradata/ocm/temp01.dbf' SIZE 20971520 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M; -- End of tempfile additions. -- *** 2013-06-24 19:06:31.608 -- 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="ocm" -- -- LOG_ARCHIVE_CONFIG='SEND, RECEIVE, NODG_CONFIG' -- LOG_ARCHIVE_MAX_PROCESSES=2 -- STANDBY_FILE_MANAGEMENT=MANUAL -- STANDBY_ARCHIVE_DEST=?/dbs/arch -- FAL_CLIENT='' -- FAL_SERVER='' -- -- LOG_ARCHIVE_DEST_10='LOCATION=USE_DB_RECOVERY_FILE_DEST' -- LOG_ARCHIVE_DEST_10='OPTIONAL REOPEN=300 NODELAY' -- LOG_ARCHIVE_DEST_10='ARCH NOAFFIRM NOEXPEDITE NOVERIFY SYNC' -- LOG_ARCHIVE_DEST_10='REGISTER NOALTERNATE NODEPENDENCY' -- LOG_ARCHIVE_DEST_10='NOMAX_FAILURE NOQUOTA_SIZE NOQUOTA_USED NODB_UNIQUE_NAME' -- LOG_ARCHIVE_DEST_10='VALID_FOR=(PRIMARY_ROLE,ONLINE_LOGFILES)' -- LOG_ARCHIVE_DEST_STATE_10=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 "OCM" NORESETLOGS NOARCHIVELOG MAXLOGFILES 16 MAXLOGMEMBERS 3 MAXDATAFILES 100 MAXINSTANCES 8 MAXLOGHISTORY 292 LOGFILE GROUP 1 '/opt/oracle/oradata/ocm/redo01.log' SIZE 50M, GROUP 2 '/opt/oracle/oradata/ocm/redo02.log' SIZE 50M, GROUP 3 '/opt/oracle/oradata/ocm/redo03.log' SIZE 50M -- STANDBY LOGFILE DATAFILE '/opt/oracle/oradata/ocm/system01.dbf', '/opt/oracle/oradata/ocm/undotbs01.dbf', '/opt/oracle/oradata/ocm/sysaux01.dbf', '/opt/oracle/oradata/ocm/users01.dbf', '/opt/oracle/oradata/ocm/example01.dbf' CHARACTER SET AL32UTF8 ; -- 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 '/opt/oracle/flash_recovery_area/OCM/archivelog/2013_06_24/o1_mf_1_1_%u_.arc'; -- ALTER DATABASE REGISTER LOGFILE '/opt/oracle/flash_recovery_area/OCM/archivelog/2013_06_24/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 -- 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 '/opt/oracle/oradata/ocm/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 "OCM" RESETLOGS NOARCHIVELOG MAXLOGFILES 16 MAXLOGMEMBERS 3 MAXDATAFILES 100 MAXINSTANCES 8 MAXLOGHISTORY 292 LOGFILE GROUP 1 '/opt/oracle/oradata/ocm/redo01.log' SIZE 50M, GROUP 2 '/opt/oracle/oradata/ocm/redo02.log' SIZE 50M, GROUP 3 '/opt/oracle/oradata/ocm/redo03.log' SIZE 50M -- STANDBY LOGFILE DATAFILE '/opt/oracle/oradata/ocm/system01.dbf', '/opt/oracle/oradata/ocm/undotbs01.dbf', '/opt/oracle/oradata/ocm/sysaux01.dbf', '/opt/oracle/oradata/ocm/users01.dbf', '/opt/oracle/oradata/ocm/example01.dbf' CHARACTER SET AL32UTF8 ; -- 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 '/opt/oracle/flash_recovery_area/OCM/archivelog/2013_06_24/o1_mf_1_1_%u_.arc'; -- ALTER DATABASE REGISTER LOGFILE '/opt/oracle/flash_recovery_area/OCM/archivelog/2013_06_24/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 '/opt/oracle/oradata/ocm/temp01.dbf' SIZE 20971520 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M; -- End of tempfile additions. -- SQL>
3、getrace.sql脚本
SELECT d.VALUE || '/' || LOWER (RTRIM (i.INSTANCE, CHR (0))) || '_ora_' || p.spid || '.trc' trace_file_name FROM (SELECT p.spid FROM v$mystat m, v$session s, v$process p WHERE m.statistic# = 1 AND s.SID = m.SID AND p.addr = s.paddr) p, (SELECT t.INSTANCE FROM v$thread t, v$parameter v WHERE v.NAME = 'thread' AND (v.VALUE = 0 OR t.thread# = TO_NUMBER (v.VALUE))) i, (SELECT VALUE FROM v$parameter WHERE NAME = 'user_dump_dest') d /
脚本版本2：
SELECT a.VALUE || b.symbol || c.instance_name || '_ora_' || d.spid || '.trc' trace_file FROM (SELECT VALUE FROM v$parameter WHERE NAME = 'user_dump_dest') a, (SELECT SUBSTR (VALUE, -6, 1) symbol FROM v$parameter WHERE NAME = 'user_dump_dest') b, (SELECT instance_name FROM v$instance) c, (SELECT spid FROM v$session s, v$process p, v$mystat m WHERE s.paddr = p.addr AND s.SID = m.SID AND m.statistic# = 0) d
(第二个脚本未验证）
4、rman备份控制文件
[oracle@ocmserver ~]$ rman target / Recovery Manager: Release 10.2.0.1.0 - Production on Mon Jun 24 20:35:52 2013 Copyright (c) 1982, 2005, Oracle. All rights reserved. connected to target database: OCM (DBID=2201575123) RMAN> BACKUP CURRENT CONTROLFILE; Starting backup at 24-JUN-13 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: sid=147 devtype=DISK channel ORA_DISK_1: starting full datafile backupset channel ORA_DISK_1: specifying datafile(s) in backupset including current control file in backupset channel ORA_DISK_1: starting piece 1 at 24-JUN-13 channel ORA_DISK_1: finished piece 1 at 24-JUN-13 piece handle=/opt/oracle/flash_recovery_area/OCM/backupset/2013_06_24/o1_mf_ncnnf_TAG20130624T203605_8wjh9s39_.bkp tag=TAG20130624T203605 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:07 Finished backup at 24-JUN-13 RMAN> BACKUP DATABASE INCLUDE CURRENT CONTROLFILE; Starting backup at 24-JUN-13 using channel ORA_DISK_1 channel ORA_DISK_1: starting full datafile backupset channel ORA_DISK_1: specifying datafile(s) in backupset RMAN-03009: failure of backup command on ORA_DISK_1 channel at 06/24/2013 20:36:48 ORA-19602: cannot backup or copy active file in NOARCHIVELOG mode continuing other job steps, job failed will not be re-run channel ORA_DISK_1: starting full datafile backupset channel ORA_DISK_1: specifying datafile(s) in backupset including current control file in backupset including current SPFILE in backupset channel ORA_DISK_1: starting piece 1 at 24-JUN-13 channel ORA_DISK_1: finished piece 1 at 24-JUN-13 piece handle=/opt/oracle/flash_recovery_area/OCM/backupset/2013_06_24/o1_mf_ncsnf_TAG20130624T203648_8wjhc18w_.bkp tag=TAG20130624T203648 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02 RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03009: failure of backup command on ORA_DISK_1 channel at 06/24/2013 20:36:48 ORA-19602: cannot backup or copy active file in NOARCHIVELOG mode RMAN> RMAN> list backup of controlfile; ----列出备份的控制文件 List of Backup Sets =================== BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 1 Full 6.77M DISK 00:00:04 24-JUN-13 BP Key: 1 Status: AVAILABLE Compressed: NO Tag: TAG20130624T203605 Piece Name: /opt/oracle/flash_recovery_area/OCM/backupset/2013_06_24/o1_mf_ncnnf_TAG20130624T203605_8wjh9s39_.bkp Control File Included: Ckp SCN: 503485 Ckp time: 24-JUN-13 BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 2 Full 6.80M DISK 00:00:01 24-JUN-13 BP Key: 2 Status: AVAILABLE Compressed: NO Tag: TAG20130624T203648 Piece Name: /opt/oracle/flash_recovery_area/OCM/backupset/2013_06_24/o1_mf_ncsnf_TAG20130624T203648_8wjhc18w_.bkp Control File Included: Ckp SCN: 503508 Ckp time: 24-JUN-13 RMAN>CONFIGURE CONTROLFILE AUTOBACKUP ON ----修改为自动备份，以后有变动都会修改更新自动备份控制文件。
5、eygle的一个实验：获取
http://www.eygle.com/archives/2004/10/backup_and_recreate_controlfile.html
