Oracle：获取创建控制文件的指令

Posted on: 2014年11月9日 2014年11月9日
Categories: Oracle Database
Tags: backup, controlfile, create controlfile, TRACE, 控制文件

有时候可能出于好奇或者重建控制文件等需求，需要获取当前数据库的控制文件创建时的语句。
这种时候你需要通过TRACE获取。

以下初步呈现技术细节。
Trace存放路径：

[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 ~]$

[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操作：

[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 ~]$

[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

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文件：

[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 ~]$

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。

Post Views: 455

赏

Adamhuan

Waiting for the moment，热衷数据库技术、Linux/Unix，以及心理学、精神分析等...(etc)，习惯用文字（符号）阐述自己对世界的理解与认知。 ———————— 微信公众号：Leviathan2020 博客：http://www.d-prototype.com

2559

Adamhuan

Leave a Reply Cancel reply