A. oracle中怎么重建控制文件或是修改控制文件参数
环境:OS:Red Hat Linux As 5DB:10.2.0.4 在全部控制文件丢失或损坏,而且没有备份的情况下,可以使用重建控制文件的办法打开数据库.以下模拟所有的控制文件丢失的情况下重建控制文件. 1.备份控制文件(数据库mount或是open状态)SQL> select status from v$instance;STATUS------------OPENSQL>alter database backup controlfile to trace as '/u01/ftp/bak_controlfile'; 2.删除控制文件[oracle@hxl oracl]$ rm control01.ctlrm: remove regular file `control01.ctl'? y[oracle@hxl oracl]$ rm control02.ctlrm: remove regular file `control02.ctl'? y[oracle@hxl oracl]$ rm control03.ctlrm: remove regular file `control03.ctl'? y 3.关闭数据库后尝试打开数据库SQL> shutdown immediateDatabase closed.Database dismounted.ORACLE instance shut down.SQL> startupORACLE instance started.Total System Global Area 734003200 bytesFixed Size 1221564 bytesVariable Size 218106948 bytesDatabase Buffers 511705088 bytesRedo Buffers 2969600 bytesORA-00205: error in identifying control file, check alert log for more info这个时候数据无法打开,以为我们已经删除了控制文件. 4.查看备份控制文件的内容[oracle@hxl ftp]$ more bak_controlfile-- 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="oracl"---- 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 PERFORMANCESTARTUP NOMOUNTCREATE CONTROLFILE REUSE DATABASE "ORACL" NORESETLOGS ARCHIVELOG MAXLOGFILES 16 MAXLOGMEMBERS 3 MAXDATAFILES 100 MAXINSTANCES 8 MAXLOGHISTORY 292LOGFILE GROUP 1 '/u01/app/oracle/oradata/oracl/redo01.log' SIZE 50M, GROUP 2 '/u01/app/oracle/oradata/oracl/redo02.log' SIZE 50M, GROUP 3 '/u01/app/oracle/oradata/oracl/redo03.log' SIZE 50M-- STANDBY LOGFILEDATAFILE '/u01/app/oracle/oradata/oracl/system01.dbf', '/u01/app/oracle/oradata/oracl/undotbs01.dbf', '/u01/app/oracle/oradata/oracl/sysaux01.dbf', '/u01/app/oracle/oradata/oracl/users01.dbf'CHARACTER SET WE8ISO8859P1;-- 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/flash_recovery_area/ORACL/archivelog/2012_06_12/o1_mf_1_1_%u_.arc';-- ALTER DATABASE REGISTER LOGFILE '/u01/app/oracle/flash_recovery_area/ORACL/archivelog/2012_06_12/o1_mf_1_1_%u_.arc';-- ALTER DATABASE REGISTER LOGFILE '/u01/app/oracle/flash_recovery_area/ORACL/archivelog/2012_06_12/o1_mf_1_1_%u_.arc';-- ALTER DATABASE REGISTER LOGFILE '/u01/app/oracle/flash_recovery_area/ORACL/archivelog/2012_06_12/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/oracl/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 PERFORMANCESTARTUP NOMOUNTCREATE CONTROLFILE REUSE DATABASE "ORACL" RESETLOGS ARCHIVELOG MAXLOGFILES 16 MAXLOGMEMBERS 3 MAXDATAFILES 100 MAXINSTANCES 8 MAXLOGHISTORY 292LOGFILE GROUP 1 '/u01/app/oracle/oradata/oracl/redo01.log' SIZE 50M, GROUP 2 '/u01/app/oracle/oradata/oracl/redo02.log' SIZE 50M, GROUP 3 '/u01/app/oracle/oradata/oracl/redo03.log' SIZE 50M-- STANDBY LOGFILEDATAFILE '/u01/app/oracle/oradata/oracl/system01.dbf', '/u01/app/oracle/oradata/oracl/undotbs01.dbf', '/u01/app/oracle/oradata/oracl/sysaux01.dbf', '/u01/app/oracle/oradata/oracl/users01.dbf'CHARACTER SET WE8ISO8859P1;-- 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/flash_recovery_area/ORACL/archivelog/2012_06_12/o1_mf_1_1_%u_.arc';-- ALTER DATABASE REGISTER LOGFILE '/u01/app/oracle/flash_recovery_area/ORACL/archivelog/2012_06_12/o1_mf_1_1_%u_.arc';-- ALTER DATABASE REGISTER LOGFILE '/u01/app/oracle/flash_recovery_area/ORACL/archivelog/2012_06_12/o1_mf_1_1_%u_.arc';-- ALTER DATABASE REGISTER LOGFILE '/u01/app/oracle/flash_recovery_area/ORACL/archivelog/2012_06_12/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/oracl/temp01.dbf' SIZE 20971520 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;-- End of tempfile additions.--5.从备份控制文件中提取我们需要的部分,这里我们选择RESETLOGS,将如下内容保存文件为create_confile.sql CREATE CONTROLFILE REUSE DATABASE "ORACL" RESETLOGS ARCHIVELOG MAXLOGFILES 16 MAXLOGMEMBERS 3 MAXDATAFILES 100 MAXINSTANCES 8 MAXLOGHISTORY 292LOGFILE GROUP 1 '/u01/app/oracle/oradata/oracl/redo01.log' SIZE 50M, GROUP 2 '/u01/app/oracle/oradata/oracl/redo02.log' SIZE 50M, GROUP 3 '/u01/app/oracle/oradata/oracl/redo03.log' SIZE 50M-- STANDBY LOGFILEDATAFILE '/u01/app/oracle/oradata/oracl/system01.dbf', '/u01/app/oracle/oradata/oracl/undotbs01.dbf', '/u01/app/oracle/oradata/oracl/sysaux01.dbf', '/u01/app/oracle/oradata/oracl/users01.dbf'CHARACTER SET WE8ISO8859P1; 6.执行create_confile.sqlSQL>set sqlblanklines on -- 因为文件中有空行,需要将该选项打开,否则执行的时候报语法错误SQL>@/u01/ftp/create_confile.sql SQL> alter database open resetlogs;Database altered. 说明:重建控制文件后,若备份信息是存储在控制文件的,该信息会丢失.
B. oracle启动命令
启动的阶段和启动的命令如下:
1、nomount:alter database open,此阶段需要参数文件支持;
2、mount:alter database mount,此阶段需要控制文件支持;
3、open :alter database open,此阶段数据库会验证所有的数据文件和redo。
4、也可以一条命令直接起库:startup
具体方法/步骤:
打开命令行窗口界面,可以同时按住“ctrl+R”键,在弹出来的运行窗口中输入cmd。