Oracle 12c 数据库备份与恢复

时间：2022-06-06 21:31:18
这是一篇学习笔记，在这篇笔记中，对Oracle数据库和归档日志进行了备份，然后将数据库删掉。之后通过恢复spfile+恢复控制文件+restore数据库+recover数据库的方式进行了恢复。

环境： Oracle 12.2.0.1.0 on LINUX

准备工作： 数据库为归档模式，归档目录为/home/oracle/archlog/orcl。并且已经将归档目录里的文件清空。打开数据库，查询表T1的内容如下：
SQL> archive log list;     
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /home/oracle/archlog/orcl
Oldest online log sequence     8
Next log sequence to archive   10
Current log sequence           10
SQL> select * from t1;

        ID
----------
     10010

[oracle@node01 ~]$ ls -ltrh /home/oracle/archlog/orcl
total 0

1. 备份数据库

这一部分中，先备份了全库，然后往表T1里插入了一条记录，最后备份日志。这样restore只能恢复一条记录，recover之后才能恢复新加的记录

创建一个目录，用于存放备份位置
$ mkdir -p /home/oracle/orcbackup
$ export NLS_DATA_FORMAT='dd-mon-yyyy hh24:mi:ss'
打开RMAN，
$ rman target /
RMAN> set echo on;
RMAN> configure device type disk parallelism 1;

设置数据库备份文件的目录和格式：
RMAN> configure channel 1 device type disk format '/home/oracle/orcbackup/rman1_%U.bk';

设置spfile和control file备份文件的目录和格式：
RMAN> show controlfile autobackup;
RMAN> configure controlfile autobackup format for device type disk to '/home/oracle/orcbackup/rman_ctl_%F.bk';
对数据库进行备份，最后自动备份了spfile和control file：
RMAN> backup incremental level=0 database;

Starting backup at 09-MAR-18
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=269 device type=DISK
channel ORA_DISK_1: starting incremental level 0 datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/home/oracle/app/oracle/oradata/orcl/system01.dbf
input datafile file number=00003 name=/home/oracle/app/oracle/oradata/orcl/sysaux01.dbf
input datafile file number=00004 name=/home/oracle/app/oracle/oradata/orcl/undotbs01.dbf
input datafile file number=00007 name=/home/oracle/app/oracle/oradata/orcl/users01.dbf
channel ORA_DISK_1: starting piece 1 at 09-MAR-18
channel ORA_DISK_1: finished piece 1 at 09-MAR-18
piece handle=/home/oracle/orcbackup/rman1_0pstcmht_1_1.bk tag=TAG20180309T212517 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:46
channel ORA_DISK_1: starting incremental level 0 datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00010 name=/home/oracle/app/oracle/oradata/orcl/orclpdb/sysaux01.dbf
input datafile file number=00009 name=/home/oracle/app/oracle/oradata/orcl/orclpdb/system01.dbf
input datafile file number=00011 name=/home/oracle/app/oracle/oradata/orcl/orclpdb/undotbs01.dbf
input datafile file number=00012 name=/home/oracle/app/oracle/oradata/orcl/orclpdb/users01.dbf
channel ORA_DISK_1: starting piece 1 at 09-MAR-18
channel ORA_DISK_1: finished piece 1 at 09-MAR-18
piece handle=/home/oracle/orcbackup/rman1_0qstcmjb_1_1.bk tag=TAG20180309T212517 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting incremental level 0 datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00006 name=/home/oracle/app/oracle/oradata/orcl/pdbseed/sysaux01.dbf
input datafile file number=00005 name=/home/oracle/app/oracle/oradata/orcl/pdbseed/system01.dbf
input datafile file number=00008 name=/home/oracle/app/oracle/oradata/orcl/pdbseed/undotbs01.dbf
channel ORA_DISK_1: starting piece 1 at 09-MAR-18
channel ORA_DISK_1: finished piece 1 at 09-MAR-18
piece handle=/home/oracle/orcbackup/rman1_0rstcmk5_1_1.bk tag=TAG20180309T212517 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:25
Finished backup at 09-MAR-18

Starting Control File and SPFILE Autobackup at 09-MAR-18
piece handle=/home/oracle/orcbackup/rman_ctl_c-1496578512-20180309-01.bk comment=NONE
Finished Control File and SPFILE Autobackup at 09-MAR-18

完成后指定的备份目录内容如下:
[oracle@node01 ~]$ ls -ltrh /home/oracle/orcbackup
total 2.1G
-rw-r-----. 1 oracle oinstall 1.1G Mar  9 21:25 rman1_0pstcmht_1_1.bk
-rw-r-----. 1 oracle oinstall 524M Mar  9 21:26 rman1_0qstcmjb_1_1.bk
-rw-r-----. 1 oracle oinstall 513M Mar  9 21:26 rman1_0rstcmk5_1_1.bk
-rw-r-----. 1 oracle oinstall  18M Mar  9 21:26 rman_ctl_c-1496578512-20180309-01.bk

往T1中插入一条记录：
SQL> insert into t1 values(20020);
SQL> commit;

备份归档日志：
RMAN> crosscheck archivelog all;
RMAN> backup archivelog all;

Starting backup at 09-MAR-18
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=2 RECID=1 STAMP=968881650
input archived log thread=1 sequence=10 RECID=9 STAMP=970349297
input archived log thread=1 sequence=11 RECID=10 STAMP=970349345
channel ORA_DISK_1: starting piece 1 at 09-MAR-18
channel ORA_DISK_1: finished piece 1 at 09-MAR-18
piece handle=/home/oracle/orcbackup/rman1_0tstcmp1_1_1.bk tag=TAG20180309T212905 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:07
Finished backup at 09-MAR-18

Starting Control File and SPFILE Autobackup at 09-MAR-18
piece handle=/home/oracle/orcbackup/rman_ctl_c-1496578512-20180309-02.bk comment=NONE
Finished Control File and SPFILE Autobackup at 09-MAR-18

再次查看备份目录内容如下
[oracle@node01 ~]$ ls -ltrh /home/oracle/orcbackup
total 2.3G
-rw-r-----. 1 oracle oinstall 1.1G Mar  9 21:25 rman1_0pstcmht_1_1.bk
-rw-r-----. 1 oracle oinstall 524M Mar  9 21:26 rman1_0qstcmjb_1_1.bk
-rw-r-----. 1 oracle oinstall 513M Mar  9 21:26 rman1_0rstcmk5_1_1.bk
-rw-r-----. 1 oracle oinstall  18M Mar  9 21:26 rman_ctl_c-1496578512-20180309-01.bk
-rw-r-----. 1 oracle oinstall 192M Mar  9 21:29 rman1_0tstcmp1_1_1.bk
-rw-r-----. 1 oracle oinstall  18M Mar  9 21:29 rman_ctl_c-1496578512-20180309-02.bk

2. 删除数据库

SQL> shutdown immediate; 
SQL> startup force mount;
SQL> ALTER SYSTEM ENABLE RESTRICTED SESSION;
SQL> drop database;

3. 恢复数据库

3.1 恢复spfile

先造一个假的pfile，随便起个名叫pfile_tmp.ora，目地是设置数据库的名子，使数据库启动到nomount状态，因此这个文件只包含一条记录即可
[oracle@node01 ~]$ export ORACLE_SID=orcl
[oracle@node01 ~]$ echo "db_name=orcl" >> $ORACLE_HOME/dbs/pfile_tmp.ora

RMAN> startup nomount pfile='$ORACLE_HOME/dbs/pfile_tmp.ora';

再从备份中恢复出真正的spfile，由于两次备份操作都备份了spfile和control file，选择最后一个即可:
RMAN> restore spfile from '/home/oracle/orcbackup/rman_ctl_c-1496578512-20180309-02.bk';
RMAN> shutdown immediate;
RMAN> startup nomount;

3.2 恢复控制文件

从备份中恢复控制文件
RMAN> restore controlfile from '/home/oracle/orcbackup/rman_ctl_c-1496578512-20180309-02.bk';
RMAN> alter database mount;

3.3 restore db 

由于都是在本机，备份片的位置没有变化，都记录在上一步的控制文件里，所以这一步比较简单：
RMAN> restore database;

3.4 recover db

RMAN> recover database;
RMAN> alter database open resetlogs;

3.5 验证

[oracle@node01 dbs]$ sqlplus / as sysdba
SQL> select * from t1;
        ID
----------
     10010
     20020
标签：

相关文章