Oracle 备份还原数据库练习.

小鱼儿 2022-03-19 12:16 357阅读 0赞

**第一步：确认数据库运行在归档模式。**  
检查：

SQL> archive log list;
    Database log mode           No Archive Mode
    Automatic archival           Disabled
    Archive destination           USE_DB_RECOVERY_FILE_DEST
    Oldest online log sequence     4
    Current log sequence           5

当前运行在非归档模式.  
  
关闭数据库，运行在mount模式

SQL> shutdown immediate; SQL> startup mount;

启用归档模式

SQL> alter database archivelog;

将配置写入spfile

SQL> alter system set log_archive_start=true scope=spfile;

重启数据库 运行在open模式

SQL> shutdown immediate; SQL> startup;

重新检查：已经运行在归档模式。

SQL> archive log list;
    Database log mode           Archive Mode
    Automatic archival           Enabled
    Archive destination           USE_DB_RECOVERY_FILE_DEST
    Oldest online log sequence     4
    Next log sequence to archive   5
    Current log sequence           5

**第二步：备份数据库**

运行rman：

[gateman@~]$ rman target /

将下面语句copy入终端运行：写成脚本都得，注意红色path的写权限

run
    {allocate channel d1 type disk;
    backup as compressed backupset incremental level = 0
    format '/home/gateman/Public/db_bak/db_%d_%s_%p_%t_%T' database;
    sql 'alter system archive log current';
    release channel d1;
    }

如果无error的话， 去该path睇下有无备份出来的备份文件。 备份时我部机卡到阿妈都唔认得

[gateman@Public]$ cd db_bak/
    [gateman@db_bak]$ ls -l
    total 173820
    -rw-r----- 1 oracle dba 176889856 Jul 22 23:07 db_XE_3_1_789346754_20120722
    -rw-r----- 1 oracle dba   1097728 Jul 22 23:07 db_XE_4_1_789347261_20120722

**第三步：一些检验（非必要）**

查看当前的数据文件：

RMAN> report schema;
    
    using target database control file instead of recovery catalog
    Report of database schema for database with db_unique_name XE
    
    List of Permanent Datafiles
    ===========================
    File Size(MB) Tablespace           RB segs Datafile Name
    ---- -------- -------------------- ------- ------------------------
    1    360      SYSTEM               ***     /u01/app/oracle/oradata/XE/system.dbf
    2    640      SYSAUX               ***     /u01/app/oracle/oradata/XE/sysaux.dbf
    3    25       UNDOTBS1             ***     /u01/app/oracle/oradata/XE/undotbs1.dbf
    4    100      USERS                ***     /u01/app/oracle/oradata/XE/users.dbf
    
    List of Temporary Files
    =======================
    File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
    ---- -------- -------------------- ----------- --------------------
    1    20       TEMP                 32767       /u01/app/oracle/oradata/XE/temp.dbf

查看归档日志：

RMAN> list copy of archivelog all;
    
    List of Archived Log Copies for database with db_unique_name XE
    =====================================================================
    
    Key     Thrd Seq     S Low Time 
    ------- ---- ------- - ---------
    1       1    5       A 17-JUL-12
            Name: /u01/app/oracle/fast_recovery_area/XE/archivelog/2012_07_22/o1_mf_1_5_80r5tngg_.arc
    
    2       1    6       A 22-JUL-12
            Name: /u01/app/oracle/fast_recovery_area/XE/archivelog/2012_07_22/o1_mf_1_6_80r7q4y8_.arc

查看还原预览： 可以见到关键信息

RMAN> restore database preview;
    
    Starting restore at 22-JUL-12
    allocated channel: ORA_DISK_1
    channel ORA_DISK_1: SID=51 device type=DISK
    
    
    List of Backup Sets
    ===================
    
    
    BS Key  Type LV Size       Device Type Elapsed Time Completion Time
    ------- ---- -- ---------- ----------- ------------ ---------------
    1       Incr 0  168.69M    DISK        00:08:23     22-JUL-12      
            BP Key: 1   Status: AVAILABLE  Compressed: YES  Tag: TAG20120722T225914
            Piece Name: /home/gateman/Public/db_bak/db_XE_3_1_789346754_20120722
      List of Datafiles in backup set 1
      File LV Type Ckp SCN    Ckp Time  Name
      ---- -- ---- ---------- --------- ----
      1    0  Incr 422228     22-JUL-12 /u01/app/oracle/oradata/XE/system.dbf
      2    0  Incr 422228     22-JUL-12 /u01/app/oracle/oradata/XE/sysaux.dbf
      3    0  Incr 422228     22-JUL-12 /u01/app/oracle/oradata/XE/undotbs1.dbf
      4    0  Incr 422228     22-JUL-12 /u01/app/oracle/oradata/XE/users.dbf
    using channel ORA_DISK_1
    
    List of Archived Log Copies for database with db_unique_name XE
    =====================================================================
    
    Key     Thrd Seq     S Low Time 
    ------- ---- ------- - ---------
    1       1    5       A 17-JUL-12
            Name: /u01/app/oracle/fast_recovery_area/XE/archivelog/2012_07_22/o1_mf_1_5_80r5tngg_.arc
    
    2       1    6       A 22-JUL-12
            Name: /u01/app/oracle/fast_recovery_area/XE/archivelog/2012_07_22/o1_mf_1_6_80r7q4y8_.arc
    
    Media recovery start SCN is 422228
    Recovery must be done beyond SCN 422228 to clear datafile fuzziness
    Finished restore at 22-JUL-12

查看控制文件备份， 可以看到存在于刚才备份出来的文件中

RMAN> list backup of controlfile;
    
    
    List of Backup Sets
    ===================
    
    
    BS Key  Type LV Size       Device Type Elapsed Time Completion Time
    ------- ---- -- ---------- ----------- ------------ ---------------
    2       Incr 0  1.03M      DISK        00:00:05     22-JUL-12      
            BP Key: 2   Status: AVAILABLE  Compressed: YES  Tag: TAG20120722T225914
            Piece Name: /home/gateman/Public/db_bak/db_XE_4_1_789347261_20120722
      Control File Included: Ckp SCN: 422479       Ckp time: 22-JUL-12
    
    RMAN>

查看spfile备份

RMAN> list backup of spfile;
    
    
    List of Backup Sets
    ===================
    
    
    BS Key  Type LV Size       Device Type Elapsed Time Completion Time
    ------- ---- -- ---------- ----------- ------------ ---------------
    2       Incr 0  1.03M      DISK        00:00:05     22-JUL-12      
            BP Key: 2   Status: AVAILABLE  Compressed: YES  Tag: TAG20120722T225914
            Piece Name: /home/gateman/Public/db_bak/db_XE_4_1_789347261_20120722
      SPFILE Included: Modification time: 22-JUL-12
      SPFILE db_unique_name: XE

**第四步：删除数据库**

你们懂的

RMAN> shutdown immediate;
    RMAN> startup mount;

开启受限模式， 禁止闲杂人等连接。

RMAN> sql 'alter system enable restricted session';
    
    sql statement: alter system enable restricted session

drop 掉数据库..  spfile control file 数据文件都无晒噶啦。

spfile目录：pwd

RMAN> drop database;

退出下先

RMAN> exit

**第五步：还原数据库**

连接rman

[gateman@~]$ rman target /

nomount模式

RMAN> startup nomount;
    
    startup failed: ORA-01078: failure in processing system parameters
    LRM-00109: could not open parameter file '/u01/app/oracle/product/11.2.0/xe/dbs/initXE.ora'
    
    starting Oracle instance without parameter file for retrieval of spfile
    Oracle instance started
    
    Total System Global Area     158662656 bytes
    
    Fixed Size                     2224592 bytes
    Variable Size                 96472624 bytes
    Database Buffers              54525952 bytes
    Redo Buffers

还原spfile

RMAN> restore spfile from '/home/gateman/Public/db_bak/db_XE_4_1_789347261_20120722'
    2> ;
    
    Starting restore at 23-JUL-12
    using target database control file instead of recovery catalog
    allocated channel: ORA_DISK_1
    channel ORA_DISK_1: SID=135 device type=DISK
    
    channel ORA_DISK_1: restoring spfile from AUTOBACKUP /home/gateman/Public/db_bak/db_XE_4_1_789347261_20120722
    channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete
    Finished restore at 23-JUL-12

还原controlfile 控制文件

RMAN> restore controlfile from '/home/gateman/Public/db_bak/db_XE_4_1_789347261_20120722';
    
    Starting restore at 23-JUL-12
    using channel ORA_DISK_1
    
    channel ORA_DISK_1: restoring control file
    channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
    output file name=/u01/app/oracle/product/11.2.0/xe/dbs/cntrlXE.dbf
    Finished restore at 23-JUL-12

注意上面呢个 还原出bug了 叼

下面呢个先系正常的 不然改不了mount模式：话controlfile有问题，  所以path唔正常的命令执行多一次， controlfile 的path有记录在spfile中，对比下一唔一致。

RMAN> restore controlfile from '/home/gateman/Public/db_bak/db_XE_4_1_789347261_20120722';
    
    Starting restore at 23-JUL-12
    using channel ORA_DISK_1
    
    channel ORA_DISK_1: restoring control file
    channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
    output file name=/u01/app/oracle/oradata/XE/control.dbf
    Finished restore at 23-JUL-12

改成mount 模式

RMAN> alter database mount;
    
    database mounted
    released channel: ORA_DISK_1

check下backup文件

RMAN> crosscheck backup;
    
    Starting implicit crosscheck backup at 23-JUL-12
    allocated channel: ORA_DISK_1
    channel ORA_DISK_1: SID=5 device type=DISK
    Crosschecked 1 objects
    Finished implicit crosscheck backup at 23-JUL-12
    
    Starting implicit crosscheck copy at 23-JUL-12
    using channel ORA_DISK_1
    Finished implicit crosscheck copy at 23-JUL-12
    
    searching for all files in the recovery area
    cataloging files...
    cataloging done
    
    List of Cataloged Files
    =======================
    File Name: /u01/app/oracle/fast_recovery_area/XE/archivelog/2012_07_22/o1_mf_1_6_80r7q4y8_.arc
    File Name: /u01/app/oracle/fast_recovery_area/XE/archivelog/2012_07_22/o1_mf_1_5_80r5tngg_.arc
    
    using channel ORA_DISK_1
    crosschecked backup piece: found to be 'AVAILABLE'
    backup piece handle=/home/gateman/Public/db_bak/db_XE_3_1_789346754_20120722 RECID=1 STAMP=789346754
    Crosschecked 1 objects

再check下预览 因为恢复了controlfile 预览应该会正常。

RMAN> restore database preview;

开始真正还原数据文件

RMAN> restore database;
    
    Starting restore at 23-JUL-12
    using channel ORA_DISK_1
    
    channel ORA_DISK_1: starting datafile backup set restore
    channel ORA_DISK_1: specifying datafile(s) to restore from backup set
    channel ORA_DISK_1: restoring datafile 00001 to /u01/app/oracle/oradata/XE/system.dbf
    channel ORA_DISK_1: restoring datafile 00002 to /u01/app/oracle/oradata/XE/sysaux.dbf
    channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/XE/undotbs1.dbf
    channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/XE/users.dbf
    channel ORA_DISK_1: reading from backup piece /home/gateman/Public/db_bak/db_XE_3_1_789346754_20120722
    channel ORA_DISK_1: piece handle=/home/gateman/Public/db_bak/db_XE_3_1_789346754_20120722 tag=TAG20120722T225914
    channel ORA_DISK_1: restored backup piece 1
    channel ORA_DISK_1: restore complete, elapsed time: 00:01:45
    Finished restore at 23-JUL-12

貌似成功了

**第六步：检验完整性**

有问题

RMAN> alter database open resetlogs;
    
    RMAN-00571: ===========================================================
    RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
    RMAN-00571: ===========================================================
    RMAN-03002: failure of alter db command at 07/23/2012 00:57:12
    ORA-01152: file 1 was not restored from a sufficiently old backup 
    ORA-01110: data file 1: '/u01/app/oracle/oradata/XE/system.dbf'

recover 虽然报错话找不到归档日志， 因为drop database 时被删除了。 但是 执行这个命令后就可以open
     RMAN> recover database;
    
    Starting recover at 23-JUL-12
    using channel ORA_DISK_1
    
    starting media recovery
    
    archived log for thread 1 with sequence 5 is already on disk as file /u01/app/oracle/fast_recovery_area/XE/archivelog/2012_07_22/o1_mf_1_5_80r5tngg_.arc
    archived log for thread 1 with sequence 6 is already on disk as file /u01/app/oracle/fast_recovery_area/XE/archivelog/2012_07_22/o1_mf_1_6_80r7q4y8_.arc
    archived log file name=/u01/app/oracle/fast_recovery_area/XE/archivelog/2012_07_22/o1_mf_1_5_80r5tngg_.arc thread=1 sequence=5
    archived log file name=/u01/app/oracle/fast_recovery_area/XE/archivelog/2012_07_22/o1_mf_1_6_80r7q4y8_.arc thread=1 sequence=6
    unable to find archived log
    archived log thread=1 sequence=7
    RMAN-00571: ===========================================================
    RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
    RMAN-00571: ===========================================================
    RMAN-03002: failure of recover command at 07/23/2012 00:57:50
    RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 7 and starting SCN of 442570

呢次终于可以open了

RMAN> alter database open resetlogs;
    
    database opened

最后， 禁止返受限模式，其他人又可以连接了

RMAN> sql 'alter system disable restricted session'; 
      sql statement: alter system disable restricted session