oracle rman 구성 (12.1~)

1. 백업

1.1 CDB/PDB 모두 백업

rman target=/

BACKUP DATABASE PLUS ARCHIVELOG;

1.2 CDB 백업

rman target=/

BACKUP DATABASE ROOT;

BACKUP TABLESPACE pdb1:system, pdb1:sysaux, pdb1:users, pdb2:system;

1.3 PDB 백업

rman target=/

BACKUP PLUGGABLE DATABASE pdb1, pdb2;

# 데이터베이스

rman target=sys@pdb1

BACKUP DATABASE;

# 테이블스페이스

BACKUP TABLESPACE system, sysaux, users;

# 데이터파일

BACKUP DATAFILE 8, 9, 10;

2. 복구

2.1 전체(CDB/PDB) 복구

rman target=/

RUN {
  SHUTDOWN IMMEDIATE; # use abort if this fails
  STARTUP MOUNT;
  RESTORE DATABASE;
  RECOVER DATABASE;
  ALTER DATABASE OPEN;
}

2.2 CDB 복구

rman target=/

RUN {
  SHUTDOWN IMMEDIATE; # use abort if this fails
  STARTUP MOUNT;
  RESTORE DATABASE ROOT;
  RECOVER DATABASE ROOT;
  # Consider recovering PDBs before opening.
  ALTER DATABASE OPEN;
}

2.3 PDB 복구

rman target=/

RUN {
  ALTER PLUGGABLE DATABASE pdb1, pdb2 CLOSE;
  RESTORE PLUGGABLE DATABASE pdb1, pdb2;
  RECOVER PLUGGABLE DATABASE pdb1, pdb2;
  ALTER PLUGGABLE DATABASE pdb1, pdb2 OPEN;
}

sqlplus sys@pdb1 as sysdba

CREATE USER admin_user IDENTIFIED BY admin_user;
GRANT CREATE SESSION, PDB_DBA, SYSDBA TO admin_user;
EXIT;

rman target=admin_user@pdb1

SHUTDOWN IMMEDIATE;
RESTORE DATABASE;
RECOVER DATABASE;
STARTUP;

rman target=sys@pdb1

RUN {
  ALTER TABLESPACE users OFFLINE;
  RESTORE TABLESPACE users;
  RECOVER TABLESPACE users;
  ALTER TABLESPACE users ONLINE;
}

rman target=sys@pdb1

RUN {
  ALTER DATABASE DATAFILE 10 OFFLINE;
  RESTORE DATAFILE 10;
  RECOVER DATAFILE 10;
  ALTER DATABASE DATAFILE 10 ONLINE;
}

# Point in Time Recovery (CDB/PDB)

rman target=/

RUN {
  SHUTDOWN IMMEDIATE; # use abort if this fails
  STARTUP MOUNT;
  SET UNTIL TIME "TO_DATE('23-DEC-2013 12:00:00','DD-MON-YYYY HH24:MI:SS')";
  RESTORE DATABASE;
  RECOVER DATABASE;
  # Should probably open read-only and check it out first.
  ALTER DATABASE OPEN RESETLOGS;
}

# Point in Time Recovery (PDB)

rman target=/

RUN {
  ALTER PLUGGABLE DATABASE pdb1 CLOSE;
  SET UNTIL TIME "TO_DATE('23-DEC-2013 12:00:00','DD-MON-YYYY HH24:MI:SS')";
  RESTORE PLUGGABLE DATABASE pdb1;
  RECOVER PLUGGABLE DATABASE pdb1;
  ALTER PLUGGABLE DATABASE pdb1 OPEN RESETLOGS;
}

# Point in Time Recovery for table in CDB

SELECT DBMS_FLASHBACK.get_system_change_number FROM dual;

Auxiliary 위치로 DB를 복구후 해당 테이블 추출하여 t1_prev로 데이터 import

rman target=/

RECOVER TABLE 'TEST'.'T1'
  UNTIL SCN 1853267
  AUXILIARY DESTINATION '/u01/aux'  
  REMAP TABLE 'TEST'.'T1':'T1_PREV';       

RECOVER TABLE 'TEST'.'T1'
  UNTIL SCN 1853267
  AUXILIARY DESTINATION '/u01/aux'  
  REMAP TABLE 'TEST'.'T1':'T1_PREV'
  REMAP TABLESPACE 'USERS':'EXAMPLES';

# 12.2 User Remap 포함 

RECOVER TABLE 'TEST'.'T1'
  UNTIL SCN 1853267
  AUXILIARY DESTINATION '/u01/aux'  
  REMAP TABLE 'TEST'.'T1':'TEST2'.'T1_PREV'
  REMAP TABLESPACE 'USERS':'EXAMPLES';

복구하여 test_t1_prev.dmp 파일 생성

RECOVER TABLE 'TEST'.'T1'
  UNTIL SCN 1853267
  AUXILIARY DESTINATION '/u01/aux'
  DATAPUMP DESTINATION '/u01/export'
  DUMP FILE 'test_t1_prev.dmp'
  NOTABLEIMPORT;

# Point in Time Recovery for table in PDB

rman target=/

RECOVER TABLE 'TEST'.'T1' OF PLUGGABLE DATABASE pdb1
  UNTIL SCN 5695703
  AUXILIARY DESTINATION '/u01/aux'  
  REMAP TABLE 'TEST'.'T1':'T1_PREV';

RECOVER TABLE 'TEST'.'T1' OF PLUGGABLE DATABASE pdb1
  UNTIL SCN 5695703
  AUXILIARY DESTINATION '/u01/aux'
  DATAPUMP DESTINATION '/u01/export'
  DUMP FILE 'test_t1_prev.dmp'
  NOTABLEIMPORT;