Oracle:获取创建控制文件的指令
有时候可能出于好奇或者重建控制文件等需求,需要获取当前数据库的控制文件创建时的语句。
这种时候你需要通过TRACE获取。
以下初步呈现技术细节。
Trace存放路径:
1 2 3 4 5 6 |
[oracle@ora12c ~]$ pwd /home/oracle [oracle@ora12c ~]$ ls -ltr total 8 -rw-r--r-- 1 oracle oinstall 4781 Nov 2 21:40 db_logic.xml [oracle@ora12c ~]$ |
SQL*Plus操作:
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 |
[oracle@ora12c ~]$ ps -ef | grep pmon oracle 39387 1 0 13:01 ? 00:00:00 ora_pmon_atlas oracle 39561 39474 0 13:27 pts/2 00:00:00 grep pmon [oracle@ora12c ~]$ env | grep SID ORACLE_SID=atlas [oracle@ora12c ~]$ [oracle@ora12c ~]$ sqlplus / as sysdba SQL*Plus: Release 12.1.0.2.0 Production on Sun Nov 9 13:27:50 2014 Copyright (c) 1982, 2014, Oracle. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options SQL> alter database backup controlfile to trace as '/home/oracle/atlas_controlfile.sql' noresetlogs; Database altered. SQL> exit Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options [oracle@ora12c ~]$ |
注意,上面的“alter database backup controlfile …”最后跟的参数是“noresetlogs”。
它的意义是不重置联机重做日志。
如果需要重置联机重做日志,可以将“noresetlogs”修改为“resetlogs”。
默认情况下,如果不跟“noresetlogs”或“resetlogs”,在生成的TRACE文件中会包含两条“Create controlfile”的语句,分别对应以上两个选项。
在选择的时候需要小心甄别。
查看生成的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 |
[oracle@ora12c ~]$ pwd /home/oracle [oracle@ora12c ~]$ ls -ltr total 16 -rw-r--r-- 1 oracle oinstall 4781 Nov 2 21:40 db_logic.xml -rw-r--r-- 1 oracle oinstall 4234 Nov 9 13:28 atlas_controlfile.sql [oracle@ora12c ~]$ [oracle@ora12c ~]$ cat atlas_controlfile.sql -- 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="atlas" -- -- 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 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 -- -- 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 "ATLAS" NORESETLOGS NOARCHIVELOG MAXLOGFILES 16 MAXLOGMEMBERS 3 MAXDATAFILES 1024 MAXINSTANCES 8 MAXLOGHISTORY 292 LOGFILE GROUP 1 ( '/oradata/ATLAS/onlinelog/o1_mf_1_b5cyjr0s_.log', '/oradata/fast_recovery_area/ATLAS/onlinelog/o1_mf_1_b5cyjrgf_.log' ) SIZE 50M BLOCKSIZE 512, GROUP 2 ( '/oradata/ATLAS/onlinelog/o1_mf_2_b5cyjs6t_.log', '/oradata/fast_recovery_area/ATLAS/onlinelog/o1_mf_2_b5cyjsoj_.log' ) SIZE 50M BLOCKSIZE 512, GROUP 3 ( '/oradata/ATLAS/onlinelog/o1_mf_3_b5cyjt8v_.log', '/oradata/fast_recovery_area/ATLAS/onlinelog/o1_mf_3_b5cyjvkg_.log' ) SIZE 50M BLOCKSIZE 512 -- STANDBY LOGFILE DATAFILE '/oradata/ATLAS/datafile/o1_mf_system_b5cyjwrl_.dbf', '/oradata/ATLAS/06DDE7A616EE107BE053827CA8C0CC0F/datafile/o1_mf_system_b5cyk7cv_.dbf', '/oradata/ATLAS/datafile/o1_mf_sysaux_b5cykjk4_.dbf', '/oradata/ATLAS/06DDE7A616EE107BE053827CA8C0CC0F/datafile/o1_mf_sysaux_b5cykscz_.dbf', '/oradata/ATLAS/datafile/o1_mf_undotbs1_b5cykxrb_.dbf', '/oradata/ATLAS/datafile/o1_mf_users_b5cym96v_.dbf', '/oradata/ATLAS/06E0B924516E1CA4E053827CA8C0A438/datafile/o1_mf_system_b5dcogxz_.dbf', '/oradata/ATLAS/06E0B924516E1CA4E053827CA8C0A438/datafile/o1_mf_sysaux_b5dcogy5_.dbf', '/oradata/ATLAS/06E0B924516C1CA4E053827CA8C0A438/datafile/o1_mf_system_b5dbcb48_.dbf', '/oradata/ATLAS/06E0B924516C1CA4E053827CA8C0A438/datafile/o1_mf_sysaux_b5dbcb4w_.dbf' CHARACTER SET ZHS16GBK ; -- 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 '/oradata/fast_recovery_area/ATLAS/archivelog/2014_11_09/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; -- Open all the PDBs. ALTER PLUGGABLE DATABASE ALL OPEN; -- Commands to add tempfiles to temporary tablespaces. -- Online tempfiles have complete space information. -- Other tempfiles may require adjustment. ALTER TABLESPACE TEMP ADD TEMPFILE '/oradata/ATLAS/datafile/o1_mf_temp_b5cyl0l1_.tmp' REUSE; ALTER SESSION SET CONTAINER = PDB$SEED; ALTER TABLESPACE TEMP ADD TEMPFILE '/oradata/ATLAS/06DDE7A616EE107BE053827CA8C0CC0F/datafile/o1_mf_temp_b5cyl0pt_.tmp' REUSE; ALTER SESSION SET CONTAINER = DB_LOGIC_1; ALTER TABLESPACE TEMP ADD TEMPFILE '/oradata/ATLAS/06E0B924516C1CA4E053827CA8C0A438/datafile/o1_mf_temp_b5dbcb4w_.dbf' REUSE; ALTER SESSION SET CONTAINER = ALLAH; ALTER TABLESPACE TEMP ADD TEMPFILE '/oradata/ATLAS/06E0B924516E1CA4E053827CA8C0A438/datafile/o1_mf_temp_b5dcogy6_.dbf' REUSE; ALTER SESSION SET CONTAINER = CDB$ROOT; -- End of tempfile additions. -- [oracle@ora12c ~]$ |
——————————————
Finished。