用脚本重建控制文件

最新推荐文章于 2024-07-08 21:32:26 发布
最新推荐文章于 2024-07-08 21:32:26 发布
阅读量182 收藏
点赞数
文章标签： 数据库 操作系统

一 描述

本文参考官档资料"How to Recreate the Controlfile [ID 1012929.6]"做如下控制文件重建实验.虽然实验平台为Linux,在windows平台操作过程也一样.引用一下原文 :)

Normal 0 7.8 磅 0 2 false false false EN-US ZH-CN X-NONE

WARNING:

--------

You should only need to recreate your control file under very special

circumstances:

 - All current copies of the control file have been lost or are corrupted.

 - You need to change a "hard" database parameter that was set when the database was first created, such as MAXDATAFILES, MAXLOGFILES,MAXLOGHISTORY, etc.

 - You are restoring a backup in which the control file is corrupted or missing.

 - Oracle Customer Support advises you to do so.

 - If you are moving your database to another machine which is running the same operating system but the location of the datafiles,

logfiles is not the same.

二 操作环境

OS info

$lsb_release -a
LSB Version:    :core-4.0-ia32:core-4.0-noarch:graphics-4.0-ia32:graphics-4.0-noarch:printing-4.0-ia32:printing-4.0-noarch
Distributor ID: EnterpriseEnterpriseServer
Description:    Enterprise Linux Enterprise Linux Server release 5.6 (Carthage)
Release:        5.6
Codename:       Carthage
$uname -a
Linux stu00 2.6.18-238.el5 #1 SMP Tue Jan 4 15:24:05 EST 2011 i686 i686 i386 GNU/Linux
$

DB info

$sqlplus / as sysdba <>
> set lines 150
> COL PRODUCT FORMAT A55
> COL VERSION FORMAT A15
> COL STATUS FORMAT A15
> SELECT * FROM PRODUCT_COMPONENT_VERSION;
> archive log list;
> EOF

SQL*Plus: Release 10.2.0.4.0 - Production on Fri Jun 15 16:13:39 2012

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> SQL> SQL> SQL> SQL> SQL>
PRODUCT                                                 VERSION         STATUS
------------------------------------------------------- --------------- ---------------
NLSRTL                                                  10.2.0.4.0      Production
Oracle Database 10g Enterprise Edition                  10.2.0.4.0      Prod
PL/SQL                                                  10.2.0.4.0      Production
TNS for Linux:                                          10.2.0.4.0      Production

SQL> Database log mode         Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     1
Next log sequence to archive   4
Current log sequence           4
SQL> Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
$

other

三 过程设计

Normal 0 7.8 磅 0 2 false false false EN-US ZH-CN X-NONE I. CREATING A NEW CONTROL FILE FROM THE EXISTING CONTROL FILE

 Normal 0 7.8 磅 0 2 false false false EN-US ZH-CN X-NONE II. CREATING A NEW CONTROL FILE WITHOUT AN EXISTING CONTROL FILE
 (II前提数据库仅有数据文件的冷一致性备份其他参数文件,控制文件不在.此实验再做改变,重新创建参数文件, 重新命名数据库,重新 创建控制文件.)

四 详细步骤操作

I 从已存在的controlfile创建出新建控制文件脚本

1.因为使用的是10g,所以以sysdba身份在sqlplus中运行备份控制文件到trace语句即可:
SQL> alter database backup controlfile to trace;

2.到USER_DUMP_DEST参数目录下打开最新修改时间的trace文件.例如
SQL> col value for a80
SQL> r
  1* select value from v$parameter where name ='user_dump_dest'

VALUE
--------------------------------------------------------------------------------
/u01/app/oracle/product/10.2.0/db_1/admin/testb/udump

SQL> !
$ls -lrt /u01/app/oracle/product/10.2.0/db_1/admin/testb/udump|tail -1
-rw-rw---- 1 oracle oracle   10180 Jun 15 16:30 testb_ora_3926.trc
$

3.显示相应trace文件内容:
$cat /u01/app/oracle/product/10.2.0/db_1/admin/testb/udump/testb_ora_3926.trc
--文件头信息
/u01/app/oracle/product/10.2.0/db_1/admin/testb/udump/testb_ora_3926.trc
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1
System name:    Linux
Node name:      stu00
Release:        2.6.18-238.el5
Version:        #1 SMP Tue Jan 4 15:24:05 EST 2011
Machine:        i686
Instance name: testb
Redo thread mounted by this instance: 1
Oracle process number: 23
Unix process pid: 3926, image: oracle@stu00 (TNS V1-V3)

--归档相关参数
*** ACTION NAME:() 2012-06-15 16:30:05.588
*** MODULE NAME:(sqlplus@stu00 (TNS V1-V3)) 2012-06-15 16:30:05.588
*** SERVICE NAME:(SYS$USERS) 2012-06-15 16:30:05.588
*** SESSION ID:(145.45) 2012-06-15 16:30:05.588
*** 2012-06-15 16:30:05.588
-- 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="testb"
--
-- 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
--
--controlfile 重建脚本内容(两部分)
--
-- 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.
--
--case 1 以noresetlogs的形式open数据库,此种情况适合所有redolog组都是有效的情况
--     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 "TESTB" NORESETLOGS  ARCHIVELOG
    MAXLOGFILES 20
    MAXLOGMEMBERS 3
    MAXDATAFILES 900
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 (
    '/u01/app/oracle/product/10.2.0/db_1/oradata/testb/redo01a.log',
    '/u01/app/oracle/product/10.2.0/db_1/oradata/testb/redo1.log'
  ) SIZE 50M,
  GROUP 2 (
    '/u01/app/oracle/product/10.2.0/db_1/oradata/testb/redo02b.log',
    '/u01/app/oracle/product/10.2.0/db_1/oradata/testb/redo02.log'
  ) SIZE 50M,
  GROUP 3 (
    '/u01/app/oracle/product/10.2.0/db_1/oradata/testb/redo03.log',
    '/u01/app/oracle/product/10.2.0/db_1/oradata/testb/redo03b.log'
  ) SIZE 50M,
  GROUP 5 (
    '/u01/app/oracle/product/10.2.0/db_1/oradata/testb/redo5.log',
    '/u01/app/oracle/product/10.2.0/db_1/oradata/testb/redo05b.log'
  ) SIZE 60M
-- STANDBY LOGFILE
DATAFILE
  '/u01/app/oracle/product/10.2.0/db_1/oradata/testb/system01.dbf',
  '/u01/app/oracle/product/10.2.0/db_1/oradata/testb/undotbstemp.dbf',
  '/u01/app/oracle/product/10.2.0/db_1/oradata/testb/sysaux01.dbf',
  '/u01/app/oracle/product/10.2.0/db_1/oradata/testb/users01.dbf',
  '/u01/app/oracle/product/10.2.0/db_1/oradata/testb/example01.dbf',
  '/u01/app/oracle/product/10.2.0/db_1/oradata/testb/testb01.dbf',
  '/u01/app/oracle/product/10.2.0/db_1/oradata/testb/testb02'
CHARACTER SET WE8ISO8859P1
;
-- Configure RMAN configuration record 1
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('CONTROLFILE AUTOBACKUP','ON');
-- Configure RMAN configuration record 2
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE','DISK TO ''/home/oracle/ba                 ckup/%F''');
-- Configure RMAN configuration record 3
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('DEVICE TYPE','DISK BACKUP TYPE TO COMPRESSED BACKUPSET PARALLELISM 2');
-- Configure RMAN configuration record 4
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('CHANNEL','DEVICE TYPE DISK FORMAT   ''/home/oracle/backup/%U''');
-- Configure RMAN configuration record 5
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('RETENTION POLICY','TO REDUNDANCY 1');
-- 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 '/home/oracle/backup_testb/TESTB/archivelog/2012_06_15/o1_mf_1_1_%u_.arc';
-- ALTER DATABASE REGISTER LOGFILE '/home/oracle/backup_testb/TESTB/archivelog/2012_06_15/o1_mf_1_1_%u_.arc';
-- ALTER DATABASE REGISTER LOGFILE '/home/oracle/backup_testb/TESTB/archivelog/2012_06_15/o1_mf_1_1_%u_.arc';
-- ALTER DATABASE REGISTER LOGFILE '/home/oracle/backup_testb/TESTB/archivelog/2012_06_15/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/product/10.2.0/db_1/oradata/testb/temp01.dbf'
     SIZE 31457280  REUSE AUTOEXTEND OFF;
-- End of tempfile additions.
--
--case 2 当redolog组被破坏后,使用resetlogs打开数据库.
--     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 "TESTB" RESETLOGS  ARCHIVELOG
    MAXLOGFILES 20
    MAXLOGMEMBERS 3
    MAXDATAFILES 900
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 (
    '/u01/app/oracle/product/10.2.0/db_1/oradata/testb/redo01a.log',
    '/u01/app/oracle/product/10.2.0/db_1/oradata/testb/redo1.log'
  ) SIZE 50M,
  GROUP 2 (
    '/u01/app/oracle/product/10.2.0/db_1/oradata/testb/redo02b.log',
    '/u01/app/oracle/product/10.2.0/db_1/oradata/testb/redo02.log'
  ) SIZE 50M,
  GROUP 3 (
    '/u01/app/oracle/product/10.2.0/db_1/oradata/testb/redo03.log',
    '/u01/app/oracle/product/10.2.0/db_1/oradata/testb/redo03b.log'
  ) SIZE 50M,
  GROUP 5 (
    '/u01/app/oracle/product/10.2.0/db_1/oradata/testb/redo5.log',
    '/u01/app/oracle/product/10.2.0/db_1/oradata/testb/redo05b.log'
  ) SIZE 60M
-- STANDBY LOGFILE
DATAFILE
  '/u01/app/oracle/product/10.2.0/db_1/oradata/testb/system01.dbf',
  '/u01/app/oracle/product/10.2.0/db_1/oradata/testb/undotbstemp.dbf',
  '/u01/app/oracle/product/10.2.0/db_1/oradata/testb/sysaux01.dbf',
  '/u01/app/oracle/product/10.2.0/db_1/oradata/testb/users01.dbf',
  '/u01/app/oracle/product/10.2.0/db_1/oradata/testb/example01.dbf',
  '/u01/app/oracle/product/10.2.0/db_1/oradata/testb/testb01.dbf',
  '/u01/app/oracle/product/10.2.0/db_1/oradata/testb/testb02'
CHARACTER SET WE8ISO8859P1
;
-- Configure RMAN configuration record 1
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('CONTROLFILE AUTOBACKUP','ON');
-- Configure RMAN configuration record 2
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE','DISK TO ''/home/oracle/ba                 ckup/%F''');
-- Configure RMAN configuration record 3
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('DEVICE TYPE','DISK BACKUP TYPE TO COMPRESSED BACKUPSET PARALLELISM 2');
-- Configure RMAN configuration record 4
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('CHANNEL','DEVICE TYPE DISK FORMAT   ''/home/oracle/backup/%U''');
-- Configure RMAN configuration record 5
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('RETENTION POLICY','TO REDUNDANCY 1');
-- 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 '/home/oracle/backup_testb/TESTB/archivelog/2012_06_15/o1_mf_1_1_%u_.arc';
-- ALTER DATABASE REGISTER LOGFILE '/home/oracle/backup_testb/TESTB/archivelog/2012_06_15/o1_mf_1_1_%u_.arc';
-- ALTER DATABASE REGISTER LOGFILE '/home/oracle/backup_testb/TESTB/archivelog/2012_06_15/o1_mf_1_1_%u_.arc';
-- ALTER DATABASE REGISTER LOGFILE '/home/oracle/backup_testb/TESTB/archivelog/2012_06_15/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/product/10.2.0/db_1/oradata/testb/temp01.dbf'
     SIZE 31457280  REUSE AUTOEXTEND OFF;
-- End of tempfile additions.
--
$

4.在原数据库的基础上,直接运行" Set #1. NORESETLOGS case"部分的脚本new_control.sql(将 "Set #1. NORESETLOGS case"的内容另存为脚本new_control.sql ),重建控制文件.
$cat new_control.sql
--     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 "TESTB" NORESETLOGS  ARCHIVELOG
    MAXLOGFILES 20
    MAXLOGMEMBERS 3
    MAXDATAFILES 900
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 (
    '/u01/app/oracle/product/10.2.0/db_1/oradata/testb/redo01a.log',
    '/u01/app/oracle/product/10.2.0/db_1/oradata/testb/redo1.log'
  ) SIZE 50M,
  GROUP 2 (
    '/u01/app/oracle/product/10.2.0/db_1/oradata/testb/redo02b.log',
    '/u01/app/oracle/product/10.2.0/db_1/oradata/testb/redo02.log'
  ) SIZE 50M,
  GROUP 3 (
    '/u01/app/oracle/product/10.2.0/db_1/oradata/testb/redo03.log',
    '/u01/app/oracle/product/10.2.0/db_1/oradata/testb/redo03b.log'
  ) SIZE 50M,
  GROUP 5 (
    '/u01/app/oracle/product/10.2.0/db_1/oradata/testb/redo5.log',
    '/u01/app/oracle/product/10.2.0/db_1/oradata/testb/redo05b.log'
  ) SIZE 60M
-- STANDBY LOGFILE
DATAFILE
  '/u01/app/oracle/product/10.2.0/db_1/oradata/testb/system01.dbf',
  '/u01/app/oracle/product/10.2.0/db_1/oradata/testb/undotbstemp.dbf',
  '/u01/app/oracle/product/10.2.0/db_1/oradata/testb/sysaux01.dbf',
  '/u01/app/oracle/product/10.2.0/db_1/oradata/testb/users01.dbf',
  '/u01/app/oracle/product/10.2.0/db_1/oradata/testb/example01.dbf',
  '/u01/app/oracle/product/10.2.0/db_1/oradata/testb/testb01.dbf',
  '/u01/app/oracle/product/10.2.0/db_1/oradata/testb/testb02'
CHARACTER SET WE8ISO8859P1
;
-- Configure RMAN configuration record 1
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('CONTROLFILE AUTOBACKUP','ON');
-- Configure RMAN configuration record 2
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE','DISK TO ''/home/oracle/backup/%F''');
-- Configure RMAN configuration record 3
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('DEVICE TYPE','DISK BACKUP TYPE TO COMPRESSED BACKUPSET PARALLELISM 2');
-- Configure RMAN configuration record 4
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('CHANNEL','DEVICE TYPE DISK FORMAT   ''/home/oracle/backup/%U''');
-- Configure RMAN configuration record 5
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('RETENTION POLICY','TO REDUNDANCY 1');
-- 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 '/home/oracle/backup_testb/TESTB/archivelog/2012_06_15/o1_mf_1_1_%u_.arc';
-- ALTER DATABASE REGISTER LOGFILE '/home/oracle/backup_testb/TESTB/archivelog/2012_06_15/o1_mf_1_1_%u_.arc';
-- ALTER DATABASE REGISTER LOGFILE '/home/oracle/backup_testb/TESTB/archivelog/2012_06_15/o1_mf_1_1_%u_.arc';
-- ALTER DATABASE REGISTER LOGFILE '/home/oracle/backup_testb/TESTB/archivelog/2012_06_15/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/product/10.2.0/db_1/oradata/testb/temp01.dbf'
     SIZE 31457280  REUSE AUTOEXTEND OFF;
-- End of tempfile additions.

$sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Fri Jun 15 23:52:12 2012

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

Connected to an idle instance.

SQL> @new_control.sql
ORACLE instance started.

Total System Global Area  285212672 bytes
Fixed Size                  1267068 bytes
Variable Size             125831812 bytes
Database Buffers          155189248 bytes
Redo Buffers                2924544 bytes

Control file created.


PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.

Media recovery complete.

System altered.


Database altered.


Tablespace altered.

SQL>

5. 因为控制文件是新创建的,以前的rman备份信息内容都已经清空.建议此时做一个全库备份.

II 目前只有数据库的冷备,而控制文件又丢失,需要自己手写参数文件,手写份控制文件重建脚本.

控制文件创建语法如下:

CREATE CONTROLFILE [REUSE] 
   DATABASE name  
   [LOGFILE filespec [, filespec] ...]   
    RESETLOGS | NORESETLOGS    
   [MAXLOGFILES integer]     
   [DATAFILE filespec [, filespec] ...]      
   [MAXDATAFILES integer]       
   [MAXINSTANCES integer]        
   [ARCHIVELOG | NOARCHIVELOG]         
   [SHARED | EXCLUSIVE]

更详细的创建控制文件语法内容参考 http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_5003.htm#i2061000

1.按照过程设计的步骤,在$ORACLE_HOME/dbs目录下创建一个新pfile出来,文件名为inittestbcb.ora. (原库pfile名字为inittestb., 新pfile名字为 testb cold backup 缩写)
列出inittestbcb.ora参数文件内容

 $cat inittestbcb.ora
*.compatible='10.2.0.1.0'
*.control_files='/u01/app/oracle/product/10.2.0/db_1/oradata/testbcb/control01.ctl','/u01/app/oracle/product/10.2.0/db_1/oradata/testbcb/control02.ctl','/u01/app/oracle/product/10.2.0/db_1/oradata/testbcb/control03.ctl'
*.db_block_size=8192
*.db_name='testbcb'
*.undo_management='AUTO'
*.undo_tablespace='UNDOTEMP'
$

(注意相应的目录需要创建出来)
参数文件最小化参考文档:http://www.msgde.net/category/oracle_pfile_simple_10g.html

2.列出冷备数据文件(控制文件已经丢失,或者由于我们的实验目的而不存在了)
$ls /u01/app/oracle/product/10.2.0/db_1/oradata/testbcb/
example01.dbf  redo02b.log  redo03b.log  redo05b.log  redo5.log     system01.dbf  testb01.dbf  undotbstemp.dbf
redo01a.log    redo02.log   redo03.log   redo1.log    sysaux01.dbf  temp01.dbf    testb02      users01.dbf
$

3.编写重建控制文件脚本 (new_control.sql)
$cat new_control.sql
CREATE CONTROLFILE REUSE SET DATABASE "TESTBCB" RESETLOGS  ARCHIVELOG
    MAXLOGFILES 20
    MAXLOGMEMBERS 3
    MAXDATAFILES 900
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 (
    '/u01/app/oracle/product/10.2.0/db_1/oradata/testbcb/redo01a.log',
    '/u01/app/oracle/product/10.2.0/db_1/oradata/testbcb/redo1.log'
  ) SIZE 50M,
  GROUP 2 (
    '/u01/app/oracle/product/10.2.0/db_1/oradata/testbcb/redo02b.log',
    '/u01/app/oracle/product/10.2.0/db_1/oradata/testbcb/redo02.log'
  ) SIZE 50M,
  GROUP 3 (
    '/u01/app/oracle/product/10.2.0/db_1/oradata/testbcb/redo03.log',
    '/u01/app/oracle/product/10.2.0/db_1/oradata/testbcb/redo03b.log'
  ) SIZE 50M,
  GROUP 5 (
    '/u01/app/oracle/product/10.2.0/db_1/oradata/testbcb/redo5.log',
    '/u01/app/oracle/product/10.2.0/db_1/oradata/testbcb/redo05b.log'
  ) SIZE 60M
DATAFILE
'/u01/app/oracle/product/10.2.0/db_1/oradata/testbcb/example01.dbf',
'/u01/app/oracle/product/10.2.0/db_1/oradata/testbcb/sysaux01.dbf',
'/u01/app/oracle/product/10.2.0/db_1/oradata/testbcb/system01.dbf',
'/u01/app/oracle/product/10.2.0/db_1/oradata/testbcb/testb01.dbf',
'/u01/app/oracle/product/10.2.0/db_1/oradata/testbcb/testb02',
'/u01/app/oracle/product/10.2.0/db_1/oradata/testbcb/undotbstemp.dbf',
'/u01/app/oracle/product/10.2.0/db_1/oradata/testbcb/users01.dbf';

(加粗部分为要重要变动:重命令数据库需要set,同时resetlogs.数据文件中不需要temp临时文件)

4.启动数据库到nomount状态
$export ORACLE_SID=testbcb
$sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Sat Jun 16 01:57:09 2012

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

Connected to an idle instance.

SQL> startup nomount;
ORACLE instance started.

Total System Global Area  113246208 bytes
Fixed Size                  1266080 bytes
Variable Size              58723936 bytes
Database Buffers           50331648 bytes
Redo Buffers                2924544 bytes
SQL>

5.运行创建控制文件脚本.
SQL> @new_control.sql

Control file created.

SQL>

6.打开数据库(控制文件创建成功后数据库自动到mount状态)

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open


SQL> alter database open resetlogs;



Database altered.

SQL>

7.收尾 :)
将前6步需要补充的添上.如: pfile中内容很单一,数据库临时表空间没有包含在重建控制文件脚本中,各后台trace目录位置信息,归档位置,/etc/oratab中的数据库相关信息,compatible参数设置... ...
SQL> create spfile from pfile;

File created.

SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/app/oracle/product/10.2.0/db_1/oradata/testbcb/temp01.dbf'
     SIZE 5M  REUSE AUTOEXTEND ON;  2

Tablespace altered.

SQL>
.... ... 各设置这里不一一设置了,想到什么补什么上来 :)

8.使用spfile重启数据库,然后做一个全备出来.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  113246208 bytes
Fixed Size                  1266080 bytes
Variable Size              58723936 bytes
Database Buffers           50331648 bytes
Redo Buffers                2924544 bytes
Database mounted.
Database opened.
SQL>

使用rman备份数据库
$rman target /

Recovery Manager: Release 10.2.0.4.0 - Production on Sat Jun 16 03:11:47 2012

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

ORACLE error from target database:
ORA-04031: unable to allocate 4112 bytes of shared memory ("shared pool","DBMS_RCVMAN","PL/SQL MPCODE","BAMIMA: Bam Buffer")
ORA-06508: PL/SQL: could not find program unit being called: "SYS.DBMS_RCVMAN"

error executing package DBMS_RCVMAN in TARGET database
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00554: initialization of internal recovery manager package failed
RMAN-06429: TARGET database is not compatible with this version of RMAN
$

查看官档 RMAN-06429的错误原因:The relevant message in this case is RMAN-06438 because package DBMS_RCVMAN   in target database is invalid.
解决,数据库重新编译相应包
 
ORA-04031 sharepool内存不足了. 关库,改参数:

SQL> shutdown immediate;

Package body created.

SQL>

Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> SQL> SQL>
SQL>
SQL>
SQL> startup nomount;
ORACLE instance started.

Total System Global Area  113246208 bytes
Fixed Size                  1266080 bytes
Variable Size              58723936 bytes
Database Buffers           50331648 bytes
Redo Buffers                2924544 bytes
SQL> show parameter sga

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
lock_sga                             boolean     FALSE
pre_page_sga                         boolean     FALSE
sga_max_size                         big integer 108M
sga_target                           big integer 0
SQL> alter system set sga_max_size=800M scope=spfile;

System altered.

SQL> shutdown abort
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  838860800 bytes
Fixed Size                  1270268 bytes
Variable Size             784338436 bytes
Database Buffers           50331648 bytes
Redo Buffers                2920448 bytes
Database mounted.
Database opened.
SQL> alter system set sga_target =500M;

System altered.

SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
$rman target /

Recovery Manager: Release 10.2.0.4.0 - Production on Sat Jun 16 07:29:17 2012

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

connected to target database: TESTBCB (DBID=298517861)

RMAN>

五 个人总结

I 对知识点涉及到recover database与recover database using backup controlfile的区别,alter database open与alter database open resetlogs的区别.

II 内容相对操作很多,在仅有数据文件冷一致性备份的前提下,创建一个全新的pfile,创建全新的控制文件,还将数据库重命名.

这样两个简单实验,折腾出这么多的参考文档.每一次操作都不能小视.

做事一定要做到底,如果仅是把库打开为目的,没有后面的rman备库实验,那数据库参数还是在那放着,要是真到了生产运营数据库上,也够人憋一会的了.

六 资料参考引用

How to Recreate the Controlfile [ID 1012929.6]

Oracle® Database SQL Reference 10g Release 2 (10.2) Part Number B14200-02  --CREATE CONTROLFILE

http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_5003.htm#i2061000

Oracle10g 用pfile 启动最少的参数配置

http://www.msgde.net/category/oracle_pfile_simple_10g.html


RMAN-06429 TARGET Database is not Compatible with this Version of RMAN [ID 245698.1]

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/11780477/viewspace-733064/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/11780477/viewspace-733064/

cnk45946
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
如何获得创建控制文件的脚本并重建控制文件
01-25 938
作者：eygle链接：http://www.eygle.com/archives/2004/10/backup_and_recreate_controlfile.html控制文件对于数据库来说是非常重要的数据结构，在进行数据恢复时通常是必不可少的.Oracle提供两种方式备份控制文件:1.生成可以重建控制文件的脚本2.备份二进制的控制文件我们看一下如何获得可以重建控制文件的脚本
重建控制文件脚本
cuidi3636的博客
05-10 96
执行alter database backup controlfile to trace;在%oracle_base%oradata%oracle_sid%udump下找到刚刚创建的trc文件，打开它。可以看到里面包含了如下两段...
Oracle 11g重建控制文件——如何获取创建控制文件脚本
congjiumi6955的博客
05-30 278
控制文件对于数据库来说是非常重要，在进行数据恢复时通常是必不可少的；丢失控制文件并不是致命的，但是会使恢复变得困难很多。因此，dba除了多路保存控制文件外，还需要备份控制文件。 备份控制文件也有多种办法，...
使用RESETLOGS重建控制文件恢复数据库（二）
cuiwu7783的博客
11-22 125
控制文件丢失，但数据文件及联机日志文件还在，并且数据库是正常关闭。 -- 模拟交易产生 22:20:46 SYS@wailon> select group#,sequence#,status fro...
Spring执行sql脚本文件的方法
08-26
本文将详细介绍如何使用Spring来执行SQL脚本文件，以及一个具体的解决方案来处理多测试文件间的数据库状态隔离问题。 首先，Spring Boot在启动时会自动查找并执行`classpath:schema.sql`或`classpath:data.sql`这样...
mysql脚本文件_自定义脚本文件
06-01
4. **版本控制**：将这些脚本文件纳入版本控制系统（如Git），可以追踪数据库结构的变化，便于团队协作和回滚。 5. **自动化部署**：在持续集成/持续部署（CI/CD）流程中，这些脚本可以自动执行，确保新环境与旧...
导出max模型脚本文件
02-25
导出的脚本文件可能包含一系列的MaxScript函数调用，用于重建原始模型。 在描述中提到的插件可能是一个名为“mMesh10beta”的工具，它可能是专门为3ds Max设计的，用于帮助用户将Max模型导出为脚本格式。这类插件...
【脚本】Oracle控制文件的备份与恢复
08-13
4. **RESTORE FROM REDO LOGS**：在某些情况下，可以利用redo logs中的信息重建控制文件。但这需要数据库处于归档模式，并且有足够的redo日志信息。 在执行控制文件的备份和恢复操作时，必须谨慎处理，因为错误的...
使用ant执行sql脚本重建oracle数据库
03-06
总结一下，使用Ant执行SQL脚本重建Oracle数据库涉及以下步骤： 1. 设置Ant构建文件，定义`sql`任务，并提供数据库连接参数。 2. 指定SQL脚本路径，这些脚本应包含创建数据库架构和填充数据的语句。 3. 确保引入...
如何重建RAC的控制文件？
sqlora的专栏
05-11 635
在下面的一些情况下，可能需要重建控制文件： o 所有控制文件都已损坏或丢失 o 没有针对控制文件的备份或者备份已损坏 o 或者在一些情况下，Oracle Support建议这么做 下面是针对RAC环境下重建控制文件的具体过程， 包括两个例子。一个是以noresetlogs模式来重建控制文件，一个是以resetlogs模式来重建控制文件。如果redo logs都存在而且没有被损坏，那么可以采用noresetlogs。 使用resetlogs会将所有redo log清空而且重置log sequence为1. 在
Oracle RMAN-06429: TARGET database is not compatible with this version of RMAN
JANE_ZDr的博客
08-02 844
备份遇到错误： RMAN-06429: TARGET database is not compatible with this version of RMAN 检查软件版本 opatch lspatches 实例数据字典中版本 SQL> select * from v$version; SQL> set lines 200 pages 1000 SQL> col COMMENTS for a40 SQL> select ACTION_TIME, ACTION, COMME
手工建库】（二）在原有数据库的基础上再建立一个数据库
w58215821的博客
08-04 968
数据库 congjiu2607 2016-10-23 10:33:38 32 收藏 在原有数据库的基础上再建立一个数据库 （先决条件：当当前虚拟内存不够用时，将现有数据库关闭；如果不关库，则需要增加虚拟内存） 建库开始： 1.生成pfile文件： [oracle@book oracle]$ cd $ORACLE_HOME [oracle@book dbhome_1]$ cd dbs oracle@book dbs]$ ls hc_PROD.dat init.ora initPROD.ora l
RMAN.DBMS_RCVCAT 版本错误处理
java3344520的专栏
01-09 2235
[oracle@xml-oms ~]$ rman target sys/oracle1@emdb catalog rman/rman@emdb Recovery Manager:Release 10.2.0.5.0 - Production on Wed Jan 9 10:37:49 2013 Copyright (c)1982, 2007, Oracle.  All rights reser
Oracle重建控制文件
zongzizz的博客
01-11 4297
重建控制文件前言一、重建原因二、重建步骤 前言 以下只是记录一次重建控制文件的过程 一、重建原因 在rman恢复数据库的过程中，碰到恢复结束时，做不完全恢复后，无法resetlogs打开数据库的情况，报错如下： 这种情况不能rename日志文件，也不能添加新的redo后再把旧的redo删除，所以只能选择重建控制文件了。 二、重建步骤 在mount状态下备份控制文件到/u01/control12.trc alter database backup controlfile to trace as '/u
ORA-04063: package body "SYS.DBMS_RCVMAN" has errors
Enjoy Perfect
02-13 7737
rman备份报错： Recovery Manager: Release 11.2.0.3.0 - Production on Thu Feb 13 10:06:46 2014 Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved. ORACLE error from targe
初始C++
最新发布
2302_81016149的博客
07-08 785
在C语言我们学习函数的时候，我们学习过生命周期这个概念。那么，什么是生命周期呢？生命周期通常指的是一个对象或变量从创建到销毁所经历的时间段。那么，命名空间是什么？咱们可以这样理解：在这个空间的生命周期内，对这个空间内的全部标识符起一个名字，可对其进行引用，可类似与C语言中的结构体。#include<stdlib.h>//此处不包此头文件代码可正常运行，那该如何解决此处情况呢？int main()//此处编译不通过原因为：在不包头文件前为变量，包含后为函数。return 0;
解决分布式环境下session共享问题
shenxiaomo1688的博客
07-06 2360
在分布式环境下，session会存在两个问题第一个问题:不同域名下，浏览器存储的jsessionid是没有存储的。比如登录时认证服务auth.gulimall.com存储了session，但是搜索服务search.gulimall.com是没有这个session的；
JDBC (基础)
weixin_51644244的博客
07-07 389
思考: java和sql是两种不同的编程语言。翻译程序---每个数据库厂商都提高了翻译软件-- -打包jar。---我们的java代码引入jar就可完成与数据库的沟通。Java ：OOP 面向对象的编程对象。SQL： 结构化查询语言。
oracle重建控制文件
10-29
当Oracle数据库的控制文件遭到损坏或丢失时，我们需要重建控制文件。下面是重建控制文件的步骤： 1. 首先，我们需要确保数据库处于关闭状态。如果数据库正在运行，我们需要停止所有相关进程，包括实例和监听器。 2. 找到数据库实例的初始化参数文件（通常是spfile或init.ora文件），此文件记录了数据库的配置信息和启动参数。确保备份此文件，以防止意外情况发生。 3. 打开一个编辑器，并创建一个新的SQL脚本文件，例如控制文件重建.sql，用于重建控制文件。 4. 在SQL脚本文件中，编写以下命令： ALTER DATABASE BACKUP CONTROLFILE TO TRACE; 这个命令将生成一个包含重建控制文件所需的语句的跟踪文件。 5. 关闭SQL脚本文件，并将其放置在$ORACLE_HOME/rdbms/admin目录中。 6. 启动数据库实例，但不要打开任何数据库。 7. 使用SQL*Plus以SYSDBA权限登录到数据库。 8. 运行以下命令加载步骤4中生成的跟踪文件： @?/rdbms/admin/控制文件重建.sql 这将执行所需的SQL语句，以重建控制文件。 9. 当脚本执行完成后，检查控制文件是否已经成功重建。你可以通过查询V$CONTROLFILE视图来检查。 10. 确认控制文件的完整性和正确性后，关闭数据库实例。 11. 最后，备份新生成的控制文件，以便将来遇到类似问题时可以快速恢复。 总结起来，重建Oracle控制文件涉及到备份初始化参数文件、创建包含重建语句的SQL脚本、加载脚本以重建控制文件，最后确认重建结果并备份新的控制文件。这些步骤将帮助我们成功重建控制文件，使数据库恢复正常运行。

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值