Lost all control files, Noresetlogs rebuild control file, ALTER DATABASE open

Source: Internet
Author: User

Test 2:
(1) Consistent, fully prepared
sql> shutdown immediate;

$ CP-RF $ORACLE _base/oradata/boss/*.dbf/oradata/bossbak/20140610allbackup
$ CP-RF $ORACLE _base/oradata/boss/*.log/oradata/bossbak/20140610allbackup
$ CP-RF $ORACLE _base/oradata/boss/*.ctl/oradata/bossbak/20140610allbackup
$ cp-rf/oradata/boss/control01.ctl/oradata/bossbak/20140610allbackup
$ CP-RF $ORACLE _home/dbs/spfileboss.ora/oradata/bossbak/20140610allbackup
$ cd/oracle/flash_recovery_area/boss/archivelog/2014_06_10/
$ RM-RF *

(2) View the database information
Sql> select Tablespace_name,status from Dba_tablespaces;

Tablespace_name STATUS
------------------------------ ---------
SYSTEM ONLINE
UNDOTBS1 ONLINE
Sysaux ONLINE
TEMP ONLINE
USERS ONLINE
EXAMPLE ONLINE
TESTTBS01 ONLINE
TESTTBS02 OFFLINE
TESTTBS03 READ only

Sql> Select Table_name,status,tablespace_name from user_tables where tablespace_name like ' testtbs% ';

TABLE_NAME STATUS Tablespace_name
------------------------------ -------- ------------------------------
TEST01 VALID TESTTBS01
Boss_new_test VALID TESTTBS01

Sql> Select
2 Ts.name "Table space name"
3, df.file# "file Number"
4, df.checkpoint_change# "Checkpoint"
5, df.name "file name"
6 from V$tablespace Ts,v$datafile DF
7 where ts.ts#=df.ts#
8 ORDER by df.file#;

Table space name file number checkpoint filename
------------------------------ ---------- ---------- ----------------------------------------
SYSTEM 1 708505/oracle/oradata/boss/system01.dbf
UNDOTBS1 2 708505/oracle/oradata/boss/undotbs01.dbf
Sysaux 3 708505/oracle/oradata/boss/sysaux01.dbf
USERS 4 708505/ORACLE/ORADATA/BOSS/USERS01.DBF
EXAMPLE 5 708505/oracle/oradata/boss/example01.dbf
TESTTBS01 6 708505/ORACLE/ORADATA/BOSS/TESTTBS01_01.DBF
TESTTBS01 7 708505/oracle/oradata/boss/testtbs01_02.dbf
TESTTBS02 8 652783/ORACLE/ORADATA/BOSS/TESTTBS02_01.DBF
TESTTBS03 9 652799/oracle/oradata/boss/testtbs03_01.dbf

(3) Trace files for backup control files
sql> ALTER DATABASE backup Controlfile to trace as '/oradata/bossbak/20140610allbackup/control1.trace ' noresetlogs;
sql> ALTER DATABASE backup Controlfile to trace as '/oradata/bossbak/20140610allbackup/control2.trace ';
sql> ALTER DATABASE backup Controlfile to trace as '/oradata/bossbak/20140610allbackup/control3.trace ' resetlogs;

(4) Create TABLE space Testtbs04, CREATE table test02 in Tablespace testtbs04, and shutdown abort directly
Sql>
Create Tablespace Testtbs04
DataFile '/oracle/oradata/boss/testtbs04_01.dbf ' size 10m
Autoextend on next 1m maxsize Unlimited
Logging
Extent Management Local Autoallocate
BlockSize 8k
Segment Space Management Auto
Flashback on;

Tablespace created.

Sql> CREATE TABLE test02 (ID number, name VARCHAR2 ()) tablespace testtbs04;

sql> INSERT INTO test02 values (1, ' nnnnn ');
sql> INSERT INTO test02 values (2, ' mmmmm ');
Sql> commit;

(5) Delete all control files
$ RM-RF *.ctl

sql> shutdown abort;
ORACLE instance shut down.

sql> startup Open;
Ora-00205:error in identifying control file, check alert log for more info

(6) Edit trace file
$ cp-rf control1.trace control.trace
CREATE controlfile reuse DATABASE "BOSS" noresetlogs  ARCHIVELOG
    maxlogfiles
    maxlogmembers 3
    Maxdatafiles
    maxinstances 8
    maxloghistory 292
LOGFILE
  Group 1 '/oracle/oradata/boss/redo01.log '   SIZE 50M,
  Group 2 '/oracle/oradata/boss/redo02.log '   Size 50M,
  GROUP 3 '/oracle/oradata/boss/redo03.log '   SIZE 50M
datafile
  '/oracle/oradata/ Boss/system01.dbf ',
  '/oracle/oradata/boss/undotbs01.dbf ',
  '/oracle/oradata/boss/sysaux01.dbf ',
  '/oracle/oradata/boss/users01.dbf ',
  '/oracle/oradata/boss/example01.dbf ',
  '/ Oracle/oradata/boss/testtbs01_01.dbf ',
  '/oracle/oradata/boss/testtbs01_02.dbf ',
  '/oracle/ ORADATA/BOSS/TESTTBS04_01.DBF '
CHARACTER SET ZHS16GBK
;

Sql> CREATE controlfile Reuse DATABASE "BOSS" Noresetlogs ARCHIVELOG
2 Maxlogfiles 16
3 Maxlogmembers 3
4 Maxdatafiles 100
5 Maxinstances 8
6 Maxloghistory 292
7 LOGFILE
8 GROUP 1 '/oracle/oradata/boss/redo01.log ' SIZE 50M,
9 GROUP 2 '/oracle/oradata/boss/redo02.log ' SIZE 50M,
Ten GROUP 3 '/oracle/oradata/boss/redo03.log ' SIZE 50M
DataFile
'/oracle/oradata/boss/system01.dbf ',
'/oracle/oradata/boss/undotbs01.dbf ',
'/oracle/oradata/boss/sysaux01.dbf ',
'/oracle/oradata/boss/users01.dbf ',
'/oracle/oradata/boss/example01.dbf ',
'/oracle/oradata/boss/testtbs01_01.dbf ',
'/oracle/oradata/boss/testtbs01_02.dbf ',
'/ORACLE/ORADATA/BOSS/TESTTBS04_01.DBF '
CHARACTER SET ZHS16GBK
21;

Control file created.

Sql> alter system archive log all; # #如果没有执行归档, then don't need recover database

Sql> select status from V$instance;

STATUS
------------
Mounted

sql> Recover database;
Media recovery complete.

sql> ALTER DATABASE open;

Sql> select Tablespace_name,status from Dba_tablespaces;

Tablespace_name STATUS
------------------------------ ---------
SYSTEM ONLINE
UNDOTBS1 ONLINE
Sysaux ONLINE
TEMP ONLINE
USERS ONLINE
EXAMPLE ONLINE
TESTTBS01 ONLINE
TESTTBS02 OFFLINE
TESTTBS03 READ only
TESTTBS04 ONLINE

Sql> select * from test02;

ID NAME
---------- ------------------------------
1 nnnnn
2 Mmmmm

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.