Oracle 12c 数据库备份与恢复
发布时间:2020-12-12 14:02:58 所属栏目:百科 来源:网络整理
导读:这是一篇学习笔记,在这篇笔记中,对Oracle数据库和归档日志进行了备份,然后将数据库删掉。之后通过恢复spfile+恢复控制文件+restore数据库+recover数据库的方式进行了恢复。 环境: Oracle 12.2.0.1.0 on LINUX 准备工作: 数据库为归档模式,归档目录为/h
这是一篇学习笔记,在这篇笔记中,对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 sysdbaSQL> select * from t1; ID ---------- 10010 20020 (编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |