oracle12c+数据库备份,Oracle 12c 数据库备份与恢复

这是一篇学习笔记，在这篇笔记中，对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 dbRMAN>

recover database;

RMAN>

alter database open resetlogs;

3.5 验证[oracle@node01 dbs]$

sqlplus / as sysdba

SQL>

select * from t1;         ID ----------      10010      20020