Backup and restore tablespace by using the RMAN ENCRYPTION Method in Oracle 19c
Posted by Mir Sayeed Hassan on December 7th, 2022
Backup and restore tablespace by using the RMAN ENCRYPTION Method in Oracle 19c
This scenario is to test the encryption method while performing the backup & restore operation.
Connect to Database
[oracle@testdb ~]$ sqlplus / as sysdba SQL*Plus: Release 19.0.0.0.0 - Production on Tue Nov 8 04:03:38 2022 Version 19.3.0.0.0 Copyright (c) 1982, 2019, Oracle. All rights reserved. Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.3.0.0.0 SQL> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ---------------------------------------------------- 2 PDB$SEED READ ONLY NO 3 TESTPDB READ WRITE NO 4 PDB1 READ WRITE NO
SQL> alter session set container=TESTPDB; Session altered.
SQL> select * from v$tablespace; TS# NAME INC BIG FLA ENC CON_ID ---------- ----------------------------------- 0 SYSTEM YES NO YES 3 1 SYSAUX YES NO YES 3 2 UNDOTBS1 YES NO YES 3 3 TEMP NO NO YES 3 5 USERS YES NO YES 3
Create a tablespace in TESTPDB
SQL> create tablespace testing1 datafile size 100m; Tablespace created.
SQL> select * from v$tablespace; TS# NAME INC BIG FLA ENC CON_ID ---------- ------------------------------------------ 0 SYSTEM YES NO YES 3 1 SYSAUX YES NO YES 3 2 UNDOTBS1 YES NO YES 3 3 TEMP NO NO YES 3 5 USERS YES NO YES 3 7 TESTING1 YES NO YES 3
Connect to RMAN to enable the Encryption & take a backup
[oracle@testdb ~]$ rman target sys@TESTPDB Recovery Manager: Release 19.0.0.0.0 - Production on Tue Nov 8 07:10:05 2022 Version 19.3.0.0.0 Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved. target database Password: connected to target database: TESTDB:TESTPDB (DBID=4132390638) RMAN> CONFIGURE ENCRYPTION FOR DATABASE ON; old RMAN configuration parameters: CONFIGURE ENCRYPTION FOR DATABASE ON; new RMAN configuration parameters: CONFIGURE ENCRYPTION FOR DATABASE ON; new RMAN configuration parameters are successfully stored
Verify the encryption method
RMAN> show all; RMAN configuration parameters for database with db_unique_name TESTDB are: CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default CONFIGURE BACKUP OPTIMIZATION OFF; # default CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default 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 ON; 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 '/u01/app/oracle/product/11.2.0/db_1/dbs/snapcf_testdb.f'; # default
Before taking the backup, set the encryption parameter
RMAN> set encryption on identified by password1 only; executing command: SET encryption
Take the backup of tablespace “TESTING1”
RMAN> backup tablespace testing1 format '/u01/testing1.bkp'; Starting backup at 09-NOV-22 using channel ORA_DISK_1 channel ORA_DISK_1: starting full datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set input datafile file number=00030 name=+DATA/TESTDB/E15539AB196953DFE0537B00140AD296/DATAFILE/testing1.323.1120201681 channel ORA_DISK_1: starting piece 1 at 09-NOV-22 channel ORA_DISK_1: finished piece 1 at 09-NOV-22 piece handle=/u01/testing1.bkp tag=TAG20221109T032323 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03 Finished backup at 09-NOV-22
Drop the tablespace after backup
SQL> drop tablespace testing1 including contents and datafiles; Tablespace dropped.
Now restore the dropped tablespace by using the decryption
Connect to TESTPDB Pluggable database, set the decryption & restore the tablespace
[oracle@testdb ~]$ rman target sys@TESTPDB Recovery Manager: Release 19.0.0.0.0 - Production on Tue Nov 8 07:21:31 2022 Version 19.3.0.0.0 Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved. target database Password: connected to target database: TESTDB:TESTPDB (DBID=4132390638) RMAN> set decryption identified by password1; executing command: SET decryption using target database control file instead of recovery catalog
Start restore & recover of tablespace
RMAN> restore tablespace testing1; Starting restore at 08-NOV-22 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=636 device type=DISK channel ORA_DISK_1: starting datafile backup set restore channel ORA_DISK_1: specifying datafile(s) to restore from backup set channel ORA_DISK_1: restoring datafile 00030 to +DATA/TESTDB/E15539AB196953DFE0537B00140AD296/DATAFILE/testing1.323.1120201681 channel ORA_DISK_1: reading from backup piece +DATA/TESTDB/E15539AB196953DFE0537B00140AD296/BACKUPSET/2022_11_08/nnndf0_tag20221108t071201_0.324.1120201923 channel ORA_DISK_1: piece handle=+DATA/TESTDB/E15539AB196953DFE0537B00140AD296/BACKUPSET/2022_11_08/nnndf0_tag20221108t071201_0.324.1120201923 tag=TAG20221108T071201 channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00:00:01 Finished restore at 08-NOV-22
RMAN> recover tablespace testing1; Starting recover at 08-NOV-22 using channel ORA_DISK_1 starting media recovery media recovery complete, elapsed time: 00:00:01 Finished recover at 08-NOV-22