oracle 12c r2 rman备份恢复

访问量:1471 创建时间:2021-04-12

查看归档模式

[oracle@esbqasdb ~]$ sqlplus /  as  sysdba
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

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

在非归档模式下rman 只能备份mount状态下的数据库

####非归档模式，直接备份失败
[oracle@esbqasdb ~]$ rman target /

Recovery Manager: Release 12.2.0.1.0 - Production on Sun Apr 11 21:11:58 2021

Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ESBQAS (DBID=1221469753)

RMAN> BACKUP DATABASE;

Starting backup at 11-APR-21
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=68 device type=DISK
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of backup command at 04/11/2021 21:12:09
RMAN-06149: cannot BACKUP DATABASE in NOARCHIVELOG mode

启动数据库在非归档的mount状态

SQL> startup  mount;
ORACLE instance started.

Total System Global Area 1577058304 bytes
Fixed Size                  8793208 bytes
Variable Size             905970568 bytes
Database Buffers          654311424 bytes
Redo Buffers                7983104 bytes
Database mounted.
SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       MOUNTED
         3 ESBQASPDB                      MOUNTED
         4 APPTEST                        MOUNTED
####通过rman备份全库，成功
[oracle@esbqasdb ~]$ rman target /
RMAN> BACKUP DATABASE;

Starting backup at 11-APR-21
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=34 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/oracle/u01/app/oracle/oradata/esbqas/system01.dbf
input datafile file number=00003 name=/oracle/u01/app/oracle/oradata/esbqas/sysaux01.dbf
input datafile file number=00004 name=/oracle/u01/app/oracle/oradata/esbqas/undotbs01.dbf
input datafile file number=00007 name=/oracle/u01/app/oracle/oradata/esbqas/users01.dbf
channel ORA_DISK_1: starting piece 1 at 11-APR-21
channel ORA_DISK_1: finished piece 1 at 11-APR-21
piece handle=/oracle/u01/app/oracle/fast_recovery_area/esbqas/ESBQAS/backupset/2021_04_11/o1_mf_nnndf_TAG20210411T212303_j7787rv7_.bkp tag=TAG20210411T212303 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:48
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00010 name=/oracle/u01/app/oracle/oradata/esbqas/esbqaspdb/sysaux01.dbf
input datafile file number=00009 name=/oracle/u01/app/oracle/oradata/esbqas/esbqaspdb/system01.dbf
input datafile file number=00011 name=/oracle/u01/app/oracle/oradata/esbqas/esbqaspdb/undotbs01.dbf
input datafile file number=00012 name=/oracle/u01/app/oracle/oradata/esbqas/esbqaspdb/users01.dbf
channel ORA_DISK_1: starting piece 1 at 11-APR-21
channel ORA_DISK_1: finished piece 1 at 11-APR-21
piece handle=/oracle/u01/app/oracle/fast_recovery_area/esbqas/ESBQAS/BF6DEF21E4AC4674E0538034A8C0A46B/backupset/2021_04_11/o1_mf_nnndf_TAG20210411T212303_j7789cfd_.bkp tag=TAG20210411T212303 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:26
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00014 name=/oracle/u01/app/oracle/oradata/esbqas/apptest/ESBQAS/BF878367FEF65E41E0538034A8C0C455/datafile/o1_mf_sysaux_j706gljq_.dbf
input datafile file number=00013 name=/oracle/u01/app/oracle/oradata/esbqas/apptest/ESBQAS/BF878367FEF65E41E0538034A8C0C455/datafile/o1_mf_system_j706gjoc_.dbf
input datafile file number=00015 name=/oracle/u01/app/oracle/oradata/esbqas/apptest/ESBQAS/BF878367FEF65E41E0538034A8C0C455/datafile/o1_mf_undotbs1_j706gljv_.dbf
channel ORA_DISK_1: starting piece 1 at 11-APR-21
channel ORA_DISK_1: finished piece 1 at 11-APR-21
piece handle=/oracle/u01/app/oracle/fast_recovery_area/esbqas/ESBQAS/BF878367FEF65E41E0538034A8C0C455/backupset/2021_04_11/o1_mf_nnndf_TAG20210411T212303_j778b507_.bkp tag=TAG20210411T212303 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:26
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00006 name=/oracle/u01/app/oracle/oradata/esbqas/pdbseed/sysaux01.dbf
input datafile file number=00005 name=/oracle/u01/app/oracle/oradata/esbqas/pdbseed/system01.dbf
input datafile file number=00008 name=/oracle/u01/app/oracle/oradata/esbqas/pdbseed/undotbs01.dbf
channel ORA_DISK_1: starting piece 1 at 11-APR-21
channel ORA_DISK_1: finished piece 1 at 11-APR-21
piece handle=/oracle/u01/app/oracle/fast_recovery_area/esbqas/ESBQAS/BF6DCFCE60263FA3E0538034A8C03C03/backupset/2021_04_11/o1_mf_nnndf_TAG20210411T212303_j778byfl_.bkp tag=TAG20210411T212303 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:16
Finished backup at 11-APR-21

Starting Control File and SPFILE Autobackup at 11-APR-21
piece handle=/oracle/u01/app/oracle/fast_recovery_area/esbqas/ESBQAS/autobackup/2021_04_11/o1_mf_s_1069622369_j778cjqo_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 11-APR-21

通常不会关闭数据库备份，开启归档模式后，不需要在mount模式下备份，可以直接使用rman备份，

开启归档模式

开启归档参考：https://www.linuxsre.cn/wiki/dbserver/21

[oracle@esbqasdb ~]$ sqlplus /  as  sysdba
SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       MOUNTED
         3 ESBQASPDB                      MOUNTED
         4 APPTEST                        MOUNTED
SQL> alter database archivelog;
Database altered.

SQL> alter database open;
Database altered.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 ESBQASPDB                      READ WRITE NO
         4 APPTEST                        MOUNTED
SQL> alter  pluggable database APPTEST open;
Pluggable database altered.
###查看归档日志存储目录，下面显示使用USE_DB_RECOVERY_FILE_DEST存储
SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     3
Next log sequence to archive   5
Current log sequence           5

创建归档目录

[root@esbqasdb ~]# mkdir -pv /oracle/u01/archive
mkdir: created directory ‘/oracle/u01/archive’
[root@esbqasdb ~]# chown oracle:oinstall -R /oracle/u01/archive/

修改oracle配置

####查看归档目录配置
SQL> show parameter  log_archive
SQL> alter system set log_archive_dest_1='location=/oracle/u01/archive/'  scope=both;

System altered.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 ESBQASPDB                      READ WRITE NO
         4 APPTEST                        MOUNTED
###因为APPTEST没有配置触发器随cdb启动，我这里需要单独open一下
SQL> alter pluggable database APPTEST open;
###查看归档目录
SQL> show parameter  log_archive

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_config                   string
log_archive_dest                     string
log_archive_dest_1                   string      location=/oracle/u01/archive/
SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /oracle/u01/archive/
Oldest online log sequence     3
Next log sequence to archive   5
Current log sequence           5

演示：手动切换归档日志：

[root@esbqasdb ~]# ll /oracle/u01/archive
total 0
###手动切换
SQL> alter system switch logfile;
System altered.
[root@esbqasdb ~]# ll /oracle/u01/archive
total 19424
-rw-r----- 1 oracle oinstall 19889664 Apr 11 22:15 1_5_1069280892.dbf

归档模式下rman备份

用户建立参考：https://www.linuxsre.cn/wiki/dbserver/172

SQL> conn / as sysdba
SQL> alter session set container=ESBQASPDB;
Session altered.
###授权
SQL> alter user testss quota unlimited  on users;

User altered.

SQL> grant  resource to testss;

Grant succeeded.
#切换用户
SQL> conn testss/123456@192.168.52.128:1521/ESBQASPDB
Connected.
#查看当前用户表空间
SQL> select  username,default_tablespace   from user_users;

USERNAME                                 DEFAULT_TABLESPACE
---------------------------------------- ------------------------------
TESTSS                                   USERS

###建表
create table student(
       sno   varchar2(3) not null,
       sname varchar2(9) not null,
       ssex  varchar2(3) not null,
       sbirthday date,
       sclass varchar2(5),
       constraint pk_student primary key(sno)
);
comment on column student.sno is '学号（主键）';
comment on column student.sname is '学生姓名';
comment on column student.ssex is '学生性别';
comment on column student.sbirthday is '学生出生年月日';
comment on column student.sclass is '学生所在班级';
####插入数据
SQL> insert into student(sno,sname,ssex,sbirthday,sclass) values(108,'zh','nan',to_date('1977-09-01','yyyy-mm-dd'),95033);
1 row created.

SQL> insert into student(sno,sname,ssex,sbirthday,sclass) values(105,'lisi','nan',to_date('1975-10-02','yyyy-mm-dd'),95031);
1 row created.

测试备份全部的数据库(包括CDB)

rman target /
BACKUP DATABASE;

报错处理:ORA-19809: limit exceeded for recovery files

#查看闪回去大小设置
SQL> show parameter db_recover;
SQL> alter system set db_recovery_file_dest_size=10g scope=both;

System altered.
###然后使用rman重新BACKUP DATABASE;

删除数据然后rman恢复到特定时间

SQL> conn testss/123456@192.168.52.128:1521/ESBQASPDB
Connected.

SQL> select * from student;

SNO SNAME     SSE SBIRTHDAY SCLAS
--- --------- --- --------- -----
108 zh        nan 01-SEP-77 95033
105 lisi      nan 02-OCT-75 95031

SQL> delete from student where SNAME='zh';

1 row deleted.

SQL> select * from student;

SNO SNAME     SSE SBIRTHDAY SCLAS
--- --------- --- --------- -----
105 lisi      nan 02-OCT-75 95031
SQL> alter session set NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS';
SQL> select sysdate from dual;

SYSDATE
-------------------
2021-04-11 23:17:29
####删除所有数据
SQL> truncate table student;

Table truncated.

以下命令在rman中运行

[oracle@esbqasdb ~]$ rman target /
run {
shutdown immediate;
startup mount;
allocate channel c1 type disk;
allocate channel c2 type disk;
sql "alter session set nls_date_format=''yyyy-mm-dd hh24:mi:ss''";
set until time '2021-04-11 23:17:29';
restore database;
recover database;
alter database open resetlogs;
}

查看数据：

[oracle@esbqasdb ~]$ sqlplus /  as  sysdba
SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 ESBQASPDB                      READ WRITE NO
         4 APPTEST                        MOUNTED
SQL> conn testss/123456@192.168.52.128:1521/ESBQASPDB
Connected.
SQL>  select * from student;

SNO SNAME     SSE SBIRTHDAY SCLAS
--- --------- --- --------- -----
105 lisi      nan 02-OCT-75 95031

以上演示基本完成，下面是rman备份恢复命令：

备份类型	命令
备份全部的数据库(包括CDB)	rman target / BACKUP DATABASE;
只备份CDB	rman target / BACKUP DATABASE ROOT;
只备份单个pdb	rman target=sys/oracle@pdb1 BACKUP DATABASE;
只备份单个pdb	rman target / BACKUP PLUGGABLE DATABASE pdb1;
备份多个pdb [在CDB根（root）使用BACKUP PLUGGABLE DATABASE命令备份一个或多个PDB数据库。]	rman target / BACKUP PLUGGABLE DATABASE pdb1,pdb2;
备份其中的某一个表空间	rman target=sys/oracle@pdb1 BACKUP TABLESPACE users;

还原	命令
全部数据库还原	rman target / startup mount; restore database; recover database; alter database open;
只还原CDB	rman target / startup mount; restore database root; recover database root; alter database open;
只还原 pdb (一个或者多个)	rman target / alter pluggable database pdb1, pdb2 close; restore pluggable database pdb1, pdb2; recover pluggable database pdb1, pdb2; alterpluggable database pdb1, pdb2 open;

注意： rman备份时默认备份到闪回区，闪回区有大小限制，backup命令可以跟format参数设置备份的目录: BACKUP PLUGGABLE DATABASE ESBQASPDB format '/oracle/backup/ESB_ful_%U';

备份时查看是否备份配置文件：

[oracle@ ~]$ rman target /

Recovery Manager: Release 12.2.0.1.0 - Production on Thu May 20 08:06:25 2021

Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.

connected to target database: **** (DBID=2566827203)

RMAN> show all;

using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name ESBPRD are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1;
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
#####CONTROLFILE AUTOBACKUP ON 表示自动备份配置文件
CONFIGURE CONTROLFILE AUTOBACKUP ON; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE RMAN OUTPUT TO KEEP FOR 7 DAYS; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/snapcf_esbprd.f'; # default
#############ORACLE_ful_%U.bak 是备份文件命名规则
RMAN> BACKUP DATABASE format '/oracle/backup20210520/ORACLE_ful_%U.bak' ;

备份时还可以开启多个channel 加快速度。

查看备份信息

####查看数据库数据的备份
RMAN> list backup of database;
####查看控制文件的备份位置（前提CONFIGURE CONTROLFILE AUTOBACKUP ON; # default）
RMAN> list backup of controlfile;
###list backup;与list backupset相同
RMAN> list backupset;

删除备份

[oracle@esbprddb oracle]$ rman target /
##查看备份
RMAN> list backup of database;
#报告过期备份
RMAN> report obsolete;
#删除无效备份。首先执行CROSSCHECK命令核对备份集，如果发现备份无效（比如备份对应的数据文件损坏或丢失），RMAN会将该备份集标记为 EXPIRED状态。要删除相应的备份记录，可以执行DELETE EXPIRED BACKUP命令：
RMAN> DELETE OBSOLETE;
#删除特定备份片，如下所示：
RMAN> delete backuppiece '/oracle/backup20210520/ESB_ful_07vvab8h_1_1.bak';
#删除所有备份集，如下所示：
RMAN> DELETE BACKUP; 
#手工删除备份集，之后用如下命令同步
RMAN>crosscheck backup;
RMAN>delete expired backup;

<< 上一章节

下一章节 >>

登陆评论: 使用GITHUB登陆