10) user-managed backup and recovery

10) user-managed backup and recovery

 

   Terminology

    Whole database backup

       Target database may be open or closed

       Backup of all datafiles and the control file

    Partial database backups<部分备份>

         Tablespace,

                    Data file,

                    Control file

    Consistent backups<一致性备份><下次启动不用恢复>

    Inconsistent backups<不一致备份>

 

1. backup

 

      v$datafile

      v$controlfile

      v$logfile

           dba_data_files

   know file

         SYS>select NAME,STATUS from v$datafile;

         SYS>select name from v$controlfile;

         SYS>select member from v$logfile;

         数据文件对应表空间

         select t.name tablespace,f.name datafile

         from v$tablespace t,v$datafile f

         where t.ts# = f.ts#

         order by t.name

 

   backup methods

      noarchivelog mode closed database

      archivelog mode closed database ,open database

 

   a)noarchiveing mode closed database backup(consisent backup)

     shutdown immediate

     cp files(control files,data files,redo log files,parameter file,password file)

     startup

 

   b)archivelog mode

 

     *closed database backup:

 

       shutdown immediate

       cp files(control files,data files,redo log files)

       startup

 

     *open database backup:

 

        online backup full database:

          alter database begin backup;(full database backup)       

          select * from v$backup;

          cp files(os command)

          alter database end backup;

          select * from v$backup;

 

        online backup tablespace;

          alter tablespace users begin backup;

          select * from v$backup;

          alter tablespace users end backup;

          select * from v$backup;

 

        backup read only tablespace

           create tablespace ts_read_only datafile '/u01/app/oracle/oradata/orcl/ts_rrr01.dbf' size 5m;

           alter tablespace ts_read_only read only;

           cp file

 

        Failure During an Online Tablespace Backup

           During an online tablespace backup, the system may crash,

           a power failure may occur, the database may be shut down, and so on.

 

           alter tablespace users begin backup;

           shutdown abort

           startup

           (select * from v$backup)

           alter database datafile 4 end backup;

       

   c)Backing Up the Control File Manually

 

        alter database backup controlfile to 'control.bkp';

        alter database backup controlfile to trace;

 

           ALTER DATABASE [ADD | DROP] LOGFILE

           ALTER DATABASE [ADD | DROP] LOGFILE MEMBER

           ALTER DATABASE [ADD | DROP ] LOGFILE GROUP

           ALTER DATABASE [ NOARCHIVELOG | ARCHIVELOG ]

           ALTER DATABASE RENAME FILE

           CREATE TABLESPACE

           ALTER TABLESPACE [ADD | RENAME ] DATAFILE

           ALTER TABLESPACE [READ WRITE | READ ONLY ]

           DROP TABLESPACE

 

    d)Backing Up the Server Initialization Parameter File

         create pfile from spfile

         create pfile='file_name from spfile

 

    e)Verifying Backups Using the DBVERIFY Utility

       $dbv file=/home/oracle/oradata/orcl/users01.dbf blocksize=8192

 

 

2. recovery

     restore all database file from backup using os commands

     recover file using the sql*plus recover command

       recover database

       recover tablespace tablespace_name

       recover datafile file_name|file#

 

     View V$RECOVER_FILE to determine which datafiles need recovery. <有需要恢复的文件才显示，没有就不显示>

     View V$ARCHIVED_LOG for a list of all archived redo log files for the database.

     View V$RECOVERY_LOG for a list of all archived redo log files required for recovery.

    

 

    a)recovery in noarchivelog mode(close database)

 

     demo 1(lost all files)

        select name from v$datafile;

        select name from v$controlfile;

        select member from v$logfile;

        shutdown immediate

        cp files;<注意文件的权限>

        startup;

        conn scott/tiger

        create table e(id number)

        insert into e values(10);

        commit;

        insert into e values(20);

        rm all file;

        mv file(restore all file)

        sqlplus / as sysdba

        startup

                   原有的目录磁盘损坏时，需要在另外的位置打开oracle

                   修改初始化参数文件create pfile from spfile;modify pfile;create spfile from pfile

                   修改控制文件startup mount,修改文件位置。Strings control01.ctl

                            SYS>select file#,name from v$datafile;

                            SYS>alter database rename file '/u01/app/oracle/oradata/orcl/a.dbf' to '/oradata/a.dbf';

                            SYS>select member from v$logfile;

                            SYS>alter database rename file '/u01/app/oracle/oradata/orcl/a.log' to '/oradata/a.log';

 

         Demo2(without redo log file backups)重建redolog

                   Shutdown immediate

                   Restore datafile,controlfile

                   Startup mount

                   SYS> recover database using backup controlfile until cancel;                                                    

                   ORA-00279: change 2435216 generated at 05/05/2011 08:57:33 needed for thread 1

                   ORA-00289: suggestion : /u01/app/oracle/archivelog/1_53_747081587.dbf

                   ORA-00280: change 2435216 for thread 1 is in sequence #53

                   Specify log: {=suggested | filename | AUTO | CANCEL}

                   cancel                                                                                              

                   Media recovery cancelled.

                   SYS>Alter database open resetlogs

 

    b)recovery in archivelog mode

       complete recovery

       incomplete recovery

 

      *database in mount status

         recover database;

         recover datafile file_name|file#

 

      *database in open

         recover tablespace ts_name

         recover datafile file_name|file#

     

      *complete recovery methods

                   Closed database recovery for

                            System datafiles

                            Undo datafiles

                            Whole database

                   Open database recovery ,with database open

                   Open database recovery,with database closed

                   Data file recovery with no datafile backup

                   对应以上四种情况的四个demo

==recovery for system,undo,whole database

create tablespace app datafile '/u01/app/oracle/oradata/orcl/app_01.dbf' size 100M extent management local uniform segment space management auto;

create user app identified by user1 default tablespace app;

grant connect,resource to user1;

startup mount

alter database archivelog

create table t_1(id int,name char(10));

insert into t_1 values( 0,'a');

shutdown immediate

cp *.dbf /u01/app/oracle/ubackup/cold/  冷备

startup

insert into t_1 values( 1,'b');

alter tablespace app begin backup;

cp app_01.dbf   /u01/app/oracle/ubackup/hot/  热备

alter tablespace app end backup;

insert into t_1 values( 2,'c');

commit

alter system switch logfile;    归档

rm -f *.dbf

shutdown abort

cp /u01/app/oracle/ubackup/cold/*  /u01/app/oracle/oradata/orcl/

startup -----> mount

select * from v$recover_file;  数据文件和控制文件不一致查看需要恢复的

set autorecovery off/on

recover datafile 1;

select * from v$recover_file;  少了一条

reover database;

select * from v$recover_file;  空了说明没有需要恢复的。

alter database open

 

==database open

rm -f app_01.dbf

app>insert into

app>commit    写入日志中。

sys>select * from v$recover_file  无

sys>alter system checkpoint 写入数据文件

tail -f /u01/app/oracle/admin/orcl/bdump/alert_orcl.log 

ORA-01110: data file 6: '/u01/app/oracle/oradata/orcl/app_01.dbf'

sys>select * from v$recover_file  有

alter database datafile 6 offline

SYS>recover datafile 6;

[c^c   恢复时终端被关闭了。

ORA-01013: user requested cancel of current operation

sys>

SYS>recover datafile 6;                                                                       

ORA-00283: recovery session canceled due to errors

ORA-01124: cannot recover data file 6 - file is in use or recovery

ORA-01110: data file 6: '/u01/app/oracle/oradata/orcl/app_01.dbf'

ORA-00275: media recovery has already been started

shutdown abort

startup mount

recover datafile 6;

alter database open;]

alter database datafile 6 online;

 

==database close硬件错误数据库被关闭

由于业务要求，需要打开再恢复

mount

offline

open

restore<类似以上，做了一下变通>

online

v$tablespace

v$datafile

 

==recover file without a backup

cannot be system tablespace because necessary redo is not available

control file not be recreated

all archived log file are avaiable;

missing datafile is still in data dictionary and control file;

step by step

offline

recreate datafile

recover

online

SYS>create tablespace app1 datafile '/u01/app/oracle/oradata/orcl/app1_01.dbf' size 50M;

APP>create table t_b (id int) tablespace app1;

SYS>select * from v$recover_file; 无

SYS>alter database datafile 7 offline;    

SYS>alter database create datafile '/u01/app/oracle/oradata/orcl/app1_01.dbf';

或者

SYS>alter database create datafile '/u01/app/oracle/oradata/orcl/app1_01.dbf' as '/u01/app1_01.dbf'

SYS>recover datafile 7 ;

SYS>alter database datafile 7 online;

 

 

       demo 1(lost a file database open)

         SCOTT>create table t(id number) tablespace USERS;

         SCOTT>insert into t values(1);

         SCOTT>commit;

         $rm users01.dbf

         SYS>alter database datafile file# offline immediate;

           or

              alter tablespace users offline immediate;

              select * from v$recover_file;

              select NAME,CHECKPOINT_CHANGE#,LAST_CHANGE# from v$datafile;

              select SEQUENCE#,ARCHIVE_NAME from v$recovery_log;

         $restore data file (cp)

         SYS>recover datafile file#

         SYS>alter database datafile file# online;

 

       demo 2(closed database for system tablespace datafile,undo segment datafile ,whole database)

         SYS>alter tablespace system begin backup;

         $cp system01.dbf(backup tablespace)

         SYS>alter tablespace system end backup;

 

         $rm system01.dbf

         SYS>shutdown abort

         $restore file

         SYS>startup mount

         sys>recover tablespace system;

         sys>alter database open;

 

       demo 3(restoring datafiles to a new location)

 

        *(closed database)

 

         SYS>shutdown abort

         $cp (restore the file to the new location)

         SYS>startup mount

         SYS>alter database rename file 'old_filename' to 'new_filename';

         SYS>recover database ; or SYS>recover datafile file#;

         SYS>alter database open;

 

        *(open database)

 

         SYS>alter tablespace ts_name offline;

         $cp (restore the file to the new location)

         SYS>alter tablespace ts_name rename datafile 'old_filename' to 'new_filename';

         SYS>recover tablespace ts_name;

         SYS>alter tablespace ts_name online;

 

      demo 4(Recovery of a Datafile Without a Backup )

         Media or user failure has resulted in the loss of a datafile that was never backed up.

         All archived logs exist since the file was created.

         The affected files do not belong to the system or undo segment tablespace.

 

         sys>create tablespace ts_name datafile '/u01/app/oracle/oradata/orcl/ts_name01.dbf' size 5m;

         scott>create table c(c number) tablespace ts_name;

         scott>insert into c values(1);

               commit;

               insert into c values(2);

         oracle$rm ts_name

         sys>alter tablespace ts_name offline;

         sys>alter database create datafile '/u01/app/oracle/oradata/orcl/ts_name01.dbf';

           or alter database create datafile '/u01/app/oracle/oradata/orcl/ts_name01.dbf'

              as 'new_file_name';

         sys>recover tablespace ts_name;

         sys>alter tablespace ts_name online;

       

       demo 5(read only tablespace recovery)

 

       1.read only(backup)->read only

 

         SCOTT>create table tt(c number) tablespace ts_name;

         SYS>alter tablespace ts_name read only;

         $cp (backup datafile in tablespace ts_name)

         $rm datafile;

         $restore datafile (cp backup datafile in tablespace ts_name)

 

        2.read only(backup)->read write

 

         SYS>alter tablespace ts_name read write;

         SCOTT>create table c(c number) tablesapce ts_name;

         SCOTT>insert into c values(1);

         SCOTT>commit;

         SCOTT>insert into c values(2);

         $rm file

         $restore file;

         SYS>alter tablespace ts_name offline;

         SYS>recover tablespace ts_name;

         SYS>alter tablespace ts_name online;

 

        3.read write(backup)->read only

 

         SYS>alter tablespace ts_name begin backup;

         $cp file;

         SYS>alter tablespace ts_name end backup;

         SCOTT>insert into c values(2);

         SCOTT>commit;

         SYS>alter tablespace ts_name read only;

         $rm file

         $restore file

         SYS>recover tablespce ts_name;

         有可能需要重启数据库才能作成最后一步??

    

 

loss of control files

1.all control file are lost

2.rename database

3.the current setting in the control file neet to be changed

 

control files behavior

1.write all in initialization parameter control_files

2.read only the first file listed in the control_files,

3.any of control file error,the instance becomes inoperable and abort;

 

SCN

controlfile scn < datafile checkpoint scn; "controlfile too old";restore a new cf

controlfile scn > datafile checkpoint scn; "media recovery required"

controlfile scn = datafile checkpoint scn; startup normally

从文件头读取

SYS>select file#,CHECKPOINT_CHANGE# from v$datafile_header;

从控制文件读取

SYS>select file#,CHECKPOINT_CHANGE# from v$datafile;

 

recovery control files

1.loss one

2.loss all

 

rm control01.ctl

shutdown immediate

startup

tail -f /u01/app/oracle/admin/orcl/bdump/alert_orcl.log

shutdown abort

1.modified initparameter rm a ctl

or

2.cp cf

 

 

all lost

SYS>alter database backup controlfile to trace as '/u01/app/oracle/admin/orcl/udump/c.trc';

rm *.ctl

shutdown abort

[c.trc 解释

-- 注释

有2个SQL语句；

1.noresetlog, redo.log 都是完好的,完全恢复。

2.resetlog 不完全恢复]

vi .trc

删除不需要的和注释

SYS>@/u01/app/oracle/admin/orcl/udump/noresetlog.sql   

 

all lost(backup cf,but phpical have be change,recover it)

1.backup cf

2.add datafile改变了物理结构

3.rm cf

4.recover

alter database backup controlfile to '/tmp/cf.bk';

select TS#,name from v$tablespace;

create tablespace app2 datafile '/u01/app/oracle/oradata/orcl/app2_01.dbf' size 10M;

APP>create table t_c (name char(10)) tablespace app2;

APP>insert into t_c values('jack');   

APP>commit;

rm *.ctl

shutdown abort

cp  /tmp/cf.bk  /u01/app/oracle/oradata/orcl/control01.ctl  02ctl,02ctl

startup mount

SYS>select FILE#,CHECKPOINT_CHANGE# from v$datafile_header;  文件头SCN

SYS>select CHECKPOINT_CHANGE#,FILE# from v$datafile; 控制文件SCN

SYS>RECOVER DATABASE USING BACKUP CONTROLFILE;

提示查找一个不存在的归档文件，说明数据在redo.log中

SYS>/u01/app/oracle/oradata/orcl/redo01.log

[SYS>RECOVER DATABASE USING BACKUP CONTROLFILE;

SYS>/u01/app/oracle/oradata/orcl/redo02.log 尝试直到找到为止]

提示ORA-01110: data file 8: '/u01/app/oracle/oradata/orcl/app2_01.dbf'

select  file#,name from v$datafile; 查看数据文件位置

alter database  rename file '/u01/app/oracle/product/10.2.0/db_1/dbs/UNNAMED00008' to '/u01/app/oracle/oradata/orcl/app2_01.dbf';

如何发现是app2_01.dbf？根据alert_orcl.log,或者前面的提示

SYS>recover database using backup controlfile until cancel;

SYS>/u01/app/oracle/oradata/orcl/redo02.log  和上次测试正确的是同一个

Media recovery complete.

SYS>alter database open resetlogs;

 

incomplete media recovery or recovery using a backup control file,resetting the redo log

1.archive the curent online redo logs and erase the contents of eth online redo logs

and reset the log sequence number to 1.

2.create eth online redo logs if it dont exist.

3.reinitializes the control file metadata about online redo logs  and redo threads

4.update all current datafiles and online redo logs and all subsequent archived redo logs with a new reset logs SCN and time stamp;

 

incarnation 化身

resetlogs,log sequence from 1 to ...

log_archive_format

%r

 

read only tablespace recovery

1.read-only,readonly

2.readonly,readwrite

3.readwrite,readonly

backup1,backup2,recovery

specail:

1.recreate a cf

2.rename datafiles

3.using a backup control file.

 

SYS>select ts#,name from v$tablespace

SYS>alter tablespace app2 read only; 

SYS>alter database backup controlfile to trace as '/tmp/c2.sql';

查看与c1.sql恢复的差别。