Oracle 备份恢复 31个实验 13-18

备份恢复实验之十三

      当前控制文件损坏_完全恢复_用控制文件脚本_不需备份(什么叫控制文件脚本?脚本嘛,就是你看得懂的文件,往下做你就知道了。注意,实验十二需要RESETLOGS,而本实验却不用RESETLOGS,这点区别要注意(PS:所以爱用脚本备份的控制文件就是这个原因)

SQL> conn / assysdba

Connected to anidle instance.

SQL> startup

ORACLE instancestarted.

 

Total SystemGlobal Area 452984832 bytes

Fixed Size                 1219952 bytes

Variable Size            134218384 bytes

DatabaseBuffers         314572800 bytes

Redo Buffers               2973696 bytes

Database mounted.

Database opened.

SQL>alter database backup controlfile to trace;

 

Database altered.

它会把控制文件的脚本备份到UDUMP底下,如何找到这个备份呢?

方法一:

SQL> selectdistinct sid from v$mystat;

 

      SID

----------

      159

SQL> selectp.spid

 from v$process p,v$session s

 where s.paddr=p.addr and s.sid=159;

 

SPID

------------

2598

等会去UDUMP底下找带有数字2598.TRC文件。

方法二:(不推荐,很可能不准确)

[oracle@ocpdbudump]$ pwd

/home/oracle/app/oracle/admin/orcl/udump

[oracle@ocpdb udump]$ ls –ltr

-rw-r----- 1oracle oinstall   6167 Jan 14 14:34orcl_ora_874.trc

-rw-r----- 1oracle oinstall    617 Jan 14 15:05orcl_ora_2554.trc

-rw-r----- 1oracle oinstall    674 Jan 14 15:05orcl_ora_2584.trc

-rw-r----- 1oracle oinstall   7947 Jan 14 15:08orcl_ora_2598.trc

(最下面的,最新的那个就是了)

[oracle@ocpdbudump]$ cp -p orcl_ora_2598.trc /home/oracle/c1.ctl

(先把它拷到其他地方去,好习惯而已,没有为什么)

好了,准备工作(也就是脚本)做完了,我为什么要先备份?其实不备也行,但是你等会就知道了,那前提是你得写得出来啊,大哥。

SQL> conn hr/hr

Connected.

SQL> createtable test(a varchar2(10));

 

Table created.

 

SQL> insertinto test values('liujie');

 

1 row created.

 

SQL> commit;

 

Commit complete.

 

SQL> select *from test;

 

A

----------

liujie

select * fromv$controlfile;

STATUS    NAME        IS_RECOVE   BLOCK_SIZE FILE_SIZE_BLKS

   /home/oracle/app/oracle/oradata/ORCL/controlfile/o1_mf_6lzo7cmw_.ctl      NO      16384  430

   /home/oracle/app/oracle/flash_recovery_area/ORCL/controlfile/o1_mf_6lzo7cvq_.ctl  YES    16384  430

[oracle@ocpdbbackup]$ rm -rf/home/oracle/app/oracle/oradata/ORCL/controlfile/o1_mf_6lzo7cmw_.ctl

[oracle@ocpdbbackup]$ rm -rf/home/oracle/app/oracle/flash_recovery_area/ORCL/controlfile/o1_mf_6lzo7cvq_.ctl

SQL> shutdownabort

ORACLE instanceshut down.

SQL> startup

ORACLE instancestarted.

 

Total SystemGlobal Area 452984832 bytes

Fixed Size                 1219952 bytes

Variable Size            138412688 bytes

DatabaseBuffers         310378496 bytes

Redo Buffers               2973696 bytes

ORA-00205: errorin identifying control file, check alert log for more info

接下来,要注意了。

还记得我们刚才的控制文件的备份吗?

[oracle@ocpdb ~]$vi c1.ctl

总共有两部分,一部分是SET #1 NORESETLOGS,另一部分是SET #2 RESETLOGS

--

--    Set #1. NORESETLOGS case

以下略

删到只能剩如下的内容

CREATE CONTROLFILEREUSE DATABASE "ORCL" NORESETLOGS ARCHIVELOG

   MAXLOGFILES 16

   MAXLOGMEMBERS 3

   MAXDATAFILES 100

   MAXINSTANCES 8

   MAXLOGHISTORY 292

LOGFILE

 GROUP 1 (

   '/home/oracle/app/oracle/oradata/ORCL/onlinelog/o1_mf_1_6lzo7hdn_.log',

   '/home/oracle/app/oracle/flash_recovery_area/ORCL/onlinelog/o1_mf_1_6lzo7k3y_.log'

 ) SIZE 50M,

 GROUP 2 (

   '/home/oracle/app/oracle/oradata/ORCL/onlinelog/o1_mf_2_6lzo7lpr_.log',

   '/home/oracle/app/oracle/flash_recovery_area/ORCL/onlinelog/o1_mf_2_6lzo7nb8_.log'

 ) SIZE 50M,

 GROUP 3 (

   '/home/oracle/app/oracle/oradata/ORCL/onlinelog/o1_mf_3_6lzo7p06_.log',

   '/home/oracle/app/oracle/flash_recovery_area/ORCL/onlinelog/o1_mf_3_6lzo7qjo_.log'

 ) SIZE 50M

-- STANDBY LOGFILE

DATAFILE

 '/home/oracle/app/oracle/oradata/ORCL/datafile/o1_mf_system_6lzo5fdn_.dbf',

 '/home/oracle/app/oracle/oradata/ORCL/datafile/o1_mf_undotbs1_6lzo5fk5_.dbf',

 '/home/oracle/app/oracle/oradata/ORCL/datafile/o1_mf_sysaux_6lzo5ffx_.dbf',

 '/home/oracle/app/oracle/oradata/ORCL/datafile/o1_mf_users_6lzo5fm3_.dbf',

 '/home/oracle/app/oracle/oradata/ORCL/datafile/o1_mf_example_6lzo869k_.dbf'

CHARACTER SETZHS16GBK

;(我说的删到剩如下的内容指的是本实验!

我们等会会用C1.CTL这个脚本来创建控制文件,它会将控制文件创建在哪里呢?你的SPFILE里面的控制文件的路径是什么,它就会建在那里。

NOMOUNT的时候

SQL>@/home/oracle/c1.ctl

CREATE CONTROLFILEREUSE DATABASE "ORCL" NORESETLOGS ARCHIVELOG

*

ERROR at line 1:

ORA-01503: CREATECONTROLFILE failed

ORA-01276: Cannotadd file

/home/oracle/app/oracle/oradata/ORCL/controlfile/o1_mf_6lzo7cmw_.ctl. File has

an Oracle ManagedFiles file name.

(为什么会创建失败?因为我DBCA建库选的是OMF格式的,你要转换一下SPFILE(或者说PFILE)里面的控制文件的路径格式。)

SQL> shutdownabort

ORACLE instanceshut down.

SQL> createpfile from spfile;

 

File created.

去找新创建的PFILE文件,修改一下CONTROLFILES对应得路径名称。

[oracle@ocpdbdbs]$ pwd

/home/oracle/app/oracle/product/10.2.0/db_1/dbs

[oracle@ocpdbdbs]$ ls

alert_orcl.log initdw.ora initorcl.ora     lkORCL    snapcf_orcl.f

hc_orcl.dat    init.ora   initorcl.ora.bak orapworcl spfileorcl.ora

[oracle@ocpdb dbs]$ vi initorcl.ora

*.control_files='/home/oracle/app/oracle/oradata/ORCL/controlfile/o1_mf_6lzo7cmw_.ctl','/home/oracle/app/oracle/flash_recovery_area/ORCL/controlfile/o1_mf_6lzo7cvq_.ctl'

(以上这一行改成(看下面),这里为了简便,只建一个控制文件。)

对了,大哥,其它行就不要动了,不变。

*.control_files='/home/oracle/control01.ctl'

SQL> createspfile from pfile;

 

File created.

SQL> startupnomount

ORACLE instancestarted.

 

Total SystemGlobal Area 452984832 bytes

Fixed Size                 1219952 bytes

Variable Size            138412688 bytes

DatabaseBuffers         310378496 bytes

Redo Buffers               2973696 bytes

SQL>@/home/oracle/c1.ctl

 

Control filecreated.

 

SQL> selectopen_mode from v$database;

 

OPEN_MODE

----------

MOUNTED

(这就是我前面说的,用脚本创建完控制文件后,它自动就会帮你MOUNT了。)

SQL> recoverdatabase;

Media recoverycomplete.

SQL> alterdatabase open;

 

Database altered.

 

SQL> select *from hr.test;

 

A

----------

liujie

(同学们,用控制文件脚本是如此的便利,如此的顺利,推荐使用!)

接下来,你要问你自己,INCARNATION有没换?

[oracle@ocpdb ~]$rman target /

 

Recovery Manager:Release 10.2.0.1.0 - Production on Mon Jan 17 23:35:22 2011

 

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

 

connected totarget database: ORCL (DBID=1268485194)

 

RMAN> listincarnation of database;

 

using targetdatabase control file instead of recovery catalog

 

List of DatabaseIncarnations

DB Key Inc Key DB Name DB ID            STATUS  Reset SCN Reset Time

------- --------------- ---------------- --- ---------- ----------

1      1      ORCL    1268485194      CURRENT 446075     17-JAN-11

(没换INCARNATION,还看出什么了?之前不是还有一个PARENT0的吗,现在脚本建控制文件后,它认为446075就是现在的那个“0”,听不懂算了,往下吧)

 

备份恢复实验之十四

      当前控制文件损坏_下线user表空间不完全恢复_用控制文件二进制备份_数据不丢_不需备份(也就是说在USERS表空间下线时备份控制文件,之后损坏控制文件,再恢复,你也可以试试在USERS表空间ONLINE时备份控制文件,之后USERSOFFLINE。)

SQL> conn /as sysdba

Connected.

SQL> altertablespace users offline;

 

Tablespacealtered.

RMAN> backupcurrent controlfile;

 

Starting backup at18-JAN-11

using channelORA_DISK_1

channelORA_DISK_1: starting full datafile backupset

channel ORA_DISK_1:specifying datafile(s) in backupset

including currentcontrol file in backupset

channelORA_DISK_1: starting piece 1 at 18-JAN-11

channelORA_DISK_1: finished piece 1 at 18-JAN-11

piecehandle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2011_01_18/o1_mf_ncnnf_TAG20110118T000021_6m8swowl_.bkptag=TAG20110118T000021 comment=NONE

channelORA_DISK_1: backup set complete, elapsed time: 00:00:01

Finished backup at18-JAN-11

[oracle@ocpdborcl]$ rm -rf c*

[oracle@ocpdborcl]$ ls

example01.dbf redo02.log sysaux01.dbf temp01.dbf    users01.dbf

redo01.log    redo03.log system01.dbf undotbs01.dbf

(删除控制文件)

SQL> shutdownabort

ORACLE instanceshut down.

SQL> startup

ORACLE instancestarted.

 

Total SystemGlobal Area 452984832 bytes

Fixed Size                 1219952 bytes

Variable Size            134218384 bytes

DatabaseBuffers         314572800 bytes

Redo Buffers               2973696 bytes

ORA-00205: errorin identifying control file, check alert log for more info

[oracle@ocpdborcl]$ rman target /

 

Recovery Manager:Release 10.2.0.1.0 - Production on Tue Jan 18 00:28:55 2011

 

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

 

connected totarget database: orcl (not mounted)

 

RMAN> restorecontrolfile from '/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2011_01_18/o1_mf_ncnnf_TAG20110118T001835_6m8tywof_.bkp';

 

Starting restoreat 18-JAN-11

using targetdatabase control file instead of recovery catalog

allocated channel:ORA_DISK_1

channelORA_DISK_1: sid=155 devtype=DISK

 

channelORA_DISK_1: restoring control file

channelORA_DISK_1: restore complete, elapsed time: 00:00:04

outputfilename=/home/oracle/app/oracle/oradata/orcl/control01.ctl

outputfilename=/home/oracle/app/oracle/oradata/orcl/control02.ctl

output filename=/home/oracle/app/oracle/oradata/orcl/control03.ctl

Finished restoreat 18-JAN-11

RMAN> alterdatabase mount;

 

database mounted

released channel:ORA_DISK_1

RMAN> recoverdatabase;

 

Starting recoverat 18-JAN-11

Starting implicitcrosscheck backup at 18-JAN-11

allocated channel:ORA_DISK_1

channelORA_DISK_1: sid=155 devtype=DISK

Finished implicitcrosscheck backup at 18-JAN-11

 

Starting implicitcrosscheck copy at 18-JAN-11

using channelORA_DISK_1

Finished implicitcrosscheck copy at 18-JAN-11

 

searching for allfiles in the recovery area

catalogingfiles...

cataloging done

 

List of CatalogedFiles

=======================

File Name:/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2011_01_18/o1_mf_ncnnf_TAG20110118T001835_6m8tywof_.bkp

 

using channelORA_DISK_1

datafile 4 notprocessed because file is offline

 

starting mediarecovery

 

archive log thread1 sequence 2 is already on disk as file/home/oracle/app/oracle/oradata/orcl/redo01.log

archive logfilename=/home/oracle/app/oracle/oradata/orcl/redo01.log thread=1 sequence=2

media recoverycomplete, elapsed time: 00:00:04

Finished recoverat 18-JAN-11

RMAN> alterdatabase open resetlogs;

 

database opened

SQL> selectcount(*) from hr.employees;(你之前最好建个表,插点值进去,测试一下,我这里前面漏做了,你自己实验一下)

 

 COUNT(*)

----------

      107

SQL> selectstatus from dba_tablespaces where tablespace_name='USERS';

 

STATUS

------------------

OFFLINE

SQL> altertablespace users online;

 

Tablespacealtered.

RMAN> listincarnation of database;

 

 

List of DatabaseIncarnations

DB Key Inc Key DB Name DB ID            STATUS  Reset SCN Reset Time

------- --------------- ---------------- --- ---------- ----------

1      1      ORCL    1268488725      PARENT 1          30-JUN-05

2      2      ORCL    1268488725      PARENT 446075     18-JAN-11

3      3      ORCL    1268488725      CURRENT 478743     18-JAN-11

 

备份恢复实验之十五

      当前控制文件损坏_下线user表空间完全恢复_用控制文件脚本_不需备份(前面解释很多了,这个不解释,看吧)--这个为了跟之前一个实验区分,我换一个方法做,我在它USERS表空间ONLINE的时候备份控制文件之后才OFFLINE。)

SQL> conn /as sysdba

Connected.

SQL> alterdatabase backup controlfile to trace;

 

Database altered.

[oracle@ocpdbudump]$ cp orcl_ora_18821.trc /home/oracle/c1.sql

SQL> conn hr/hr

Connected.

SQL> createtable test(a varchar2(10));

 

Table created.

 

SQL> insertinto test values('empoli');

 

1 row created.

 

SQL> commit;

 

Commit complete.

 

SQL> select *from test;

 

A

----------

empoli

SQL> altertablespace users offline;

 

Tablespacealtered.

[oracle@ocpdborcl]$ rm -rf c*

[oracle@ocpdborcl]$ ls

example01.dbf redo02.log sysaux01.dbf temp01.dbf    users01.dbf

redo01.log    redo03.log system01.dbf undotbs01.dbf

SQL> shutdownabort

ORACLE instanceshut down.

SQL> startup

ORACLE instancestarted.

 

Total SystemGlobal Area 452984832 bytes

Fixed Size                 1219952 bytes

Variable Size            130024080 bytes

DatabaseBuffers         318767104 bytes

Redo Buffers               2973696 bytes

ORA-00205: errorin identifying control file, check alert log for more info

[oracle@ocpdb ~]$vi c1.sql

CREATE CONTROLFILEREUSE DATABASE "ORCL" NORESETLOGS ARCHIVELOG

   MAXLOGFILES 16

   MAXLOGMEMBERS 3

   MAXDATAFILES 100

   MAXINSTANCES 8

   MAXLOGHISTORY 292

LOGFILE

 GROUP 1'/home/oracle/app/oracle/oradata/orcl/redo01.log' SIZE 50M,

 GROUP 2 '/home/oracle/app/oracle/oradata/orcl/redo02.log' SIZE 50M,

 GROUP 3'/home/oracle/app/oracle/oradata/orcl/redo03.log' SIZE 50M

-- STANDBY LOGFILE

DATAFILE

 '/home/oracle/app/oracle/oradata/orcl/system01.dbf',

 '/home/oracle/app/oracle/oradata/orcl/undotbs01.dbf',

 '/home/oracle/app/oracle/oradata/orcl/sysaux01.dbf',

 '/home/oracle/app/oracle/oradata/orcl/users01.dbf',

 '/home/oracle/app/oracle/oradata/orcl/example01.dbf'

CHARACTER SETZHS16GBK

;

(你可以做一下实验,如果你是OFFLINE后才备份的控制文件脚本,红线处就会没掉,不信?自己试试。因为它认为你OFFLINE的表空间不用参与恢复,明白?)

SQL> @/home/oracle/c1.sql

 

Control filecreated.

SQL> selectopen_mode from v$database;

 

OPEN_MODE

----------

MOUNTED

SQL> recoverdatabase;

Media recoverycomplete.

SQL> alterdatabase open;

 

Database altered.

(哎呀,用脚本恢复,就一个字,“爽!”)

但是别高兴的太早,看:

SQL> select *from hr.test;

select * fromhr.test

                *

ERROR at line 1:

ORA-00376: file 4cannot be read at this time

ORA-01110: datafile 4: '/home/oracle/app/oracle/oradata/orcl/users01.dbf'

(想想为什么)

因为,你之前备份控制文件的时候USERSONLINE的,可是你之后把USERS OFFLINE了,它已经一清二楚的记在了日志和数据字典表中,所以…….

SQL> altertablespace users online;

 

Tablespacealtered.

 

SQL> select *from hr.test;

 

A

----------

empoli

(可以了,OK

RMAN> listincarnation of database;

 

using targetdatabase control file instead of recovery catalog

 

List of DatabaseIncarnations

DB Key Inc Key DB Name DB ID            STATUS  Reset SCN Reset Time

------- --------------- ---------------- --- ---------- ----------

1      1      ORCL    1268490595      CURRENT 446075     18-JAN-11

(没有RESETLOGS,自然INCARNATION也不会换)

 

备份恢复实验之十六

当前控制文件损坏_只读user表空间不完全恢复_用控制文件二进制备份_数据不丢_不需备份(继续不解释,看实验)

(我们这次再做一个跟实验十三不一样的过程,相当于是把各种不同的情况都囊括进去了,明白?我们在USERS表空间只读之前,先备份二进制的控制文件)

RMAN> backupcurrent controlfile;

 

Starting backup at18-JAN-11

using targetdatabase control file instead of recovery catalog

allocated channel:ORA_DISK_1

channelORA_DISK_1: sid=141 devtype=DISK

channelORA_DISK_1: starting full datafile backupset

channelORA_DISK_1: specifying datafile(s) in backupset

including currentcontrol file in backupset

channel ORA_DISK_1:starting piece 1 at 18-JAN-11

channelORA_DISK_1: finished piece 1 at 18-JAN-11

piecehandle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2011_01_18/o1_mf_ncnnf_TAG20110118T010936_6m8xyllx_.bkptag=TAG20110118T010936 comment=NONE

channel ORA_DISK_1:backup set complete, elapsed time: 00:00:03

Finished backup at18-JAN-11

SQL> alter userhr default tablespace users;

User altered.(对了,HR默认的是EXAMPLE表空间,没事,你只要知道是这么回事就行了,明白?)

SQL> conn hr/hr

Connected.

SQL> createtable test(a varchar2(30));

 

Table created.

 

SQL> insertinto test values('i love oracle');

 

1 row created.

 

SQL> commit;

 

Commit complete.

 

SQL> select *from test;

 

A

------------------------------

i love oracle

SQL> conn /as sysdba

Connected.

SQL> alter tablespace users read only;

 

Tablespacealtered.

SQL> insertinto hr.test values('1');

insert intohr.test values('1')

              *

ERROR at line 1:

ORA-00372: file 4cannot be modified at this time

ORA-01110: datafile 4: '/home/oracle/app/oracle/oradata/orcl/users01.dbf'

(当然啊,你表空间已经是READ ONLY了)

[oracle@ocpdborcl]$ rm -rf c*

[oracle@ocpdborcl]$ ls

example01.dbf redo02.log sysaux01.dbf temp01.dbf    users01.dbf

redo01.log    redo03.log system01.dbf undotbs01.dbf

SQL> shutdownabort

ORACLE instanceshut down.

SQL> startup

ORACLE instancestarted.

 

Total SystemGlobal Area 452984832 bytes

Fixed Size                 1219952 bytes

Variable Size            134218384 bytes

DatabaseBuffers         314572800 bytes

Redo Buffers               2973696 bytes

ORA-00205: errorin identifying control file, check alert log for more info

RMAN> restorecontrolfile from'/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2011_01_18/o1_mf_ncnnf_TAG20110118T010936_6m8xyllx_.bkp';

 

Starting restoreat 18-JAN-11

using target databasecontrol file instead of recovery catalog

allocated channel:ORA_DISK_1

channelORA_DISK_1: sid=155 devtype=DISK

 

channelORA_DISK_1: restoring control file

channelORA_DISK_1: restore complete, elapsed time: 00:00:04

outputfilename=/home/oracle/app/oracle/oradata/orcl/control01.ctl

outputfilename=/home/oracle/app/oracle/oradata/orcl/control02.ctl

outputfilename=/home/oracle/app/oracle/oradata/orcl/control03.ctl

Finished restoreat 18-JAN-11

RMAN> alterdatabase mount;

 

database mounted

released channel:ORA_DISK_1

RMAN> recoverdatabase;

 

Starting recoverat 18-JAN-11

Starting implicitcrosscheck backup at 18-JAN-11

allocated channel:ORA_DISK_1

channelORA_DISK_1: sid=155 devtype=DISK

Finished implicitcrosscheck backup at 18-JAN-11

 

Starting implicitcrosscheck copy at 18-JAN-11

using channelORA_DISK_1

Finished implicitcrosscheck copy at 18-JAN-11

 

searching for allfiles in the recovery area

catalogingfiles...

cataloging done

 

List of CatalogedFiles

=======================

File Name: /home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2011_01_18/o1_mf_ncnnf_TAG20110118T010936_6m8xyllx_.bkp

 

using channelORA_DISK_1

 

starting mediarecovery

 

archive log thread1 sequence 2 is already on disk as file /home/oracle/app/oracle/oradata/orcl/redo01.log

archive logfilename=/home/oracle/app/oracle/oradata/orcl/redo01.log thread=1 sequence=2

media recoverycomplete, elapsed time: 00:00:03

Finished recoverat 18-JAN-11

RMAN> alterdatabase open;

 

RMAN-00571:===========================================================

RMAN-00569:=============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571:===========================================================

RMAN-03002:failure of alter db command at 01/18/2011 01:19:45

ORA-01589: mustuse RESETLOGS or NORESETLOGS option for database open

 

RMAN> alterdatabase open resetlogs;

 

database opened

RMAN> listincarnation of database;

 

 

List of DatabaseIncarnations

DB Key Inc Key DB Name DB ID            STATUS  Reset SCN Reset Time

------- ------- ------------------------ --- ---------- ----------

1      1      ORCL    1268491800      PARENT 1          30-JUN-05

2      2      ORCL    1268491800      PARENT 446075     18-JAN-11

3      3      ORCL    1268491800      CURRENT 478667     18-JAN-11

SQL> select *from hr.test;

 

A

------------------------------

i love oracle

(数据还在)

 

备份恢复实验之十七

     当前控制文件损坏_只读user表空间完全恢复_用控制文件脚本_不需备份

(广告词:用脚本就是爽!)这个实验,我们也要跟之前的要有区别,我在它只读后再备份控制文件的脚本。

SQL> alter userhr default tablespace users;

 

User altered.

SQL> conn hr/hr

Connected.

SQL> createtable test(a varchar2(10));

 

Table created.

 

SQL> insertinto test values('empoli');

 

1 row created.

 

SQL> commit;

 

Commit complete.

 

SQL> select *from test;

 

A

----------

empoli

SQL> conn /as sysdba

Connected.

SQL> altertablespace users read only;

 

Tablespacealtered.

SQL> alter databasebackup controlfile to trace;

 

Database altered.

[oracle@ocpdbudump]$ pwd

/home/oracle/app/oracle/admin/orcl/udump

[oracle@ocpdb udump]$ ls –ltr

[oracle@ocpdbudump]$ cp orcl_ora_16091.trc /home/oracle/c1.sql

[oracle@ocpdb ~]$vi c1.sql

CREATE CONTROLFILEREUSE DATABASE "ORCL" NORESETLOGS ARCHIVELOG

   MAXLOGFILES 16

   MAXLOGMEMBERS 3

   MAXDATAFILES 100

   MAXINSTANCES 8

   MAXLOGHISTORY 292

LOGFILE

 GROUP 1'/home/oracle/app/oracle/oradata/orcl/redo01.log' SIZE 50M,

 GROUP 2 '/home/oracle/app/oracle/oradata/orcl/redo02.log' SIZE 50M,

 GROUP 3'/home/oracle/app/oracle/oradata/orcl/redo03.log' SIZE 50M

-- STANDBY LOGFILE

DATAFILE

 '/home/oracle/app/oracle/oradata/orcl/system01.dbf',

 '/home/oracle/app/oracle/oradata/orcl/undotbs01.dbf',

 '/home/oracle/app/oracle/oradata/orcl/sysaux01.dbf',

 '/home/oracle/app/oracle/oradata/orcl/example01.dbf'

CHARACTER SETZHS16GBK

;

[oracle@ocpdborcl]$ pwd

/home/oracle/app/oracle/oradata/orcl

[oracle@ocpdborcl]$ rm -rf c*(再次提醒,这是测试环境!!!)

[oracle@ocpdborcl]$ ls

example01.dbf redo02.log sysaux01.dbf temp01.dbf    users01.dbf

redo01.log    redo03.log system01.dbf undotbs01.dbf

SQL> shutdownabort

ORACLE instanceshut down.

SQL> startup

ORACLE instancestarted.

 

Total SystemGlobal Area 452984832 bytes

Fixed Size                 1219952 bytes

Variable Size            130024080 bytes

DatabaseBuffers         318767104 bytes

Redo Buffers               2973696 bytes

ORA-00205: errorin identifying control file, check alert log for more info

SQL>@/home/oracle/c1.sql

 

Control filecreated.

SQL> recoverdatabase;

Media recoverycomplete.

SQL> alterdatabase open;

 

Database altered.

SQL> select *from hr.test;

select * fromhr.test

                *

ERROR at line 1:

ORA-00376: file 4cannot be read at this time

ORA-01111: namefor data file 4 is unknown - rename to correct file

ORA-01110: datafile 4:

'/home/oracle/app/oracle/product/10.2.0/db_1/dbs/MISSING00004'

(你注意看下CREATE CONTROLFILE那里,没有USERS这个表空间,去看看。为什么?因为它认为你READ ONLY后就无需恢复,没有在控制文件的脚本中加入,但是数据字典表中却有,所以它矛盾了,不满了,给你报个错:

'/home/oracle/app/oracle/product/10.2.0/db_1/dbs/MISSING00004'

SQL> alterdatabase rename file '/home/oracle/app/oracle/product/10.2.0/db_1/dbs/MISSING00004'to '/home/oracle/app/oracle/oradata/orcl/users01.dbf';

 

Database altered.

SQL> select *from hr.test;

select * fromhr.test

                *

ERROR at line 1:

ORA-00376: file 4cannot be read at this time

ORA-01110: datafile 4: '/home/oracle/app/oracle/oradata/orcl/users01.dbf'

SQL> selectstatus from dba_tablespaces where tablespace_name='USERS';

 

STATUS

---------

READ ONLY

(为什么READ ONLY却无法SELECT,不会吧)

SQL> altertablespace users online;

 

Tablespacealtered.

 

SQL> select *from hr.test;

 

A

----------

empoli

(可能是恢复完之后,只读是只读,还要手动给它ONLINE一下,让它反应一下,明白?或者说,它就认为你是OFFLINE的,所以你给它ONLINE下。)

OK了。

 

 

备份恢复实验之十八

      当前控制文件损坏_备份时下线user表空间不完全恢复_用控制文件二进制备份_数据不丢_不需备份(我做到这里才发现,已经把这种情况考虑进去了)你再看看实验十四,我已经做过备份时下线的情况了,那么我现在来做USERS表空间ONLINE时备份控制文件二进制)

[oracle@ocpdb ~]$rman target /

 

Recovery Manager:Release 10.2.0.1.0 - Production on Tue Jan 18 14:08:12 2011

 

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

 

connected totarget database: ORCL (DBID=1268538488)

 

RMAN> backupcurrent controlfile;

 

Starting backup at18-JAN-11

using targetdatabase control file instead of recovery catalog

allocated channel:ORA_DISK_1

channelORA_DISK_1: sid=141 devtype=DISK

channelORA_DISK_1: starting full datafile backupset

channelORA_DISK_1: specifying datafile(s) in backupset

including currentcontrol file in backupset

channelORA_DISK_1: starting piece 1 at 18-JAN-11

channelORA_DISK_1: finished piece 1 at 18-JAN-11

piecehandle=/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2011_01_18/o1_mf_ncnnf_TAG20110118T140820_6mbclnwf_.bkptag=TAG20110118T140820 comment=NONE

channelORA_DISK_1: backup set complete, elapsed time: 00:00:01

Finished backup at18-JAN-11

SQL> conn / assysdba

Connected.

SQL> alter userhr default tablespace users;

 

User altered.

SQL> conn hr/hr

Connected.

SQL> createtable test(a varchar2(10));

 

Table created.

 

SQL> insertinto test values('empoli');

 

1 row created.

 

SQL> commit;

 

Commit complete.

 

SQL> select *from test;

 

A

----------

empoli

SQL> conn / as sysdba

Connected.

SQL> altertablespace users offline;

 

Tablespacealtered.

[oracle@ocpdborcl]$ rm -rf c*

[oracle@ocpdborcl]$ ls

example01.dbf redo02.log sysaux01.dbf temp01.dbf    users01.dbf

redo01.log    redo03.log system01.dbf undotbs01.dbf

SQL> shutdownabort

ORACLE instanceshut down.

SQL> startup

ORACLE instancestarted.

 

Total SystemGlobal Area 452984832 bytes

Fixed Size                 1219952 bytes

Variable Size            134218384 bytes

DatabaseBuffers         314572800 bytes

Redo Buffers               2973696 bytes

ORA-00205: errorin identifying control file, check alert log for more info

RMAN> restorecontrolfile from '/home/oracle/app/oracle/flash_recovery_area/ORCL/backupset/2011_01_18/o1_mf_ncnnf_TAG20110118T140820_6mbclnwf_.bkp';

 

Starting restoreat 18-JAN-11

using targetdatabase control file instead of recovery catalog

allocated channel:ORA_DISK_1

channelORA_DISK_1: sid=155 devtype=DISK

 

channelORA_DISK_1: restoring control file

channelORA_DISK_1: restore complete, elapsed time: 00:00:06

outputfilename=/home/oracle/app/oracle/oradata/orcl/control01.ctl

outputfilename=/home/oracle/app/oracle/oradata/orcl/control02.ctl

output filename=/home/oracle/app/oracle/oradata/orcl/control03.ctl

Finished restoreat 18-JAN-11

RMAN> alterdatabase mount;

database mounted

released channel:ORA_DISK_1

RMAN> recoverdatabase;

RMAN> alterdatabase open;

RMAN-00571:===========================================================

RMAN-00569:=============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571:===========================================================

RMAN-03002:failure of alter db command at 01/18/2011 14:18:02

ORA-01589: mustuse RESETLOGS or NORESETLOGS option for database open

控制文件二进制一定要RESETLOGS,没办法。

RMAN> alterdatabase open resetlogs;

 

database opened

SQL> select *from hr.test;

select * fromhr.test

                *

ERROR at line 1:

ORA-00376: file 4cannot be read at this time

ORA-01110: datafile 4: '/home/oracle/app/oracle/oradata/orcl/users01.dbf'

SQL> altertablespace users online;

 

Tablespacealtered.

 

SQL> select *from hr.test;

 

A

----------

empoli

RMAN> listincarnation of database;

List of DatabaseIncarnations

DB Key Inc Key DB Name DB ID            STATUS  Reset SCN Reset Time

------- --------------- ---------------- --- ---------- ----------

1      1      ORCL    1268538488      PARENT 1          30-JUN-05

2      2      ORCL    1268538488      PARENT 446075     18-JAN-11

3      3      ORCL    1268538488      CURRENT 478725     18-JAN-11

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

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

抵扣说明:

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

余额充值