RMAN - ORA-19913: unable to decrypt backup ORA-28365: wallet is not open Restoring from a Password-encrypted Backupset

Restoring from a Password-encrypted Backupset

Scenario : 

We first encrypt rman setting then take backup after that restore backup . It will throw error and decrypt  again restore and recover.

Error:

RMAN-03002: failure of restore command at 05/24/2020 12:55:22

ORA-19870: error while restoring backup piece /u01/app/oracle/fast_recovery_area/PLM/backupset/2020_05_24/o1_mf_nnndf_ENCRYPTION_hdn89nxx_.bkp

ORA-19913: unable to decrypt backup

ORA-28365: wallet is not open

Solution:

• Connect to the rman utility

oracle@suse1:/u01/app/oracle/product/12.1.0/dbhome_1/dbs> rman target /

Recovery Manager: Release 12.1.0.2.0 - Production on Sun May 24 12:53:23 2020

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

connected to target database: PLM (DBID=1811128417)

• Enable encryption on and set password

RMAN> SET ENCRYPTION ON IDENTIFIED BY Password ONLY; 

• Backup Tablespace users from rman utility

RMAN> BACKUP TABLESPACE USERS TAG 'ENCRYPTION';

executing command: SET encryption

using target database control file instead of recovery catalog

Starting backup at 24-MAY-20

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=73 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=00006 name=/u01/app/oracle/oradata/plm/users01.dbf

channel ORA_DISK_1: starting piece 1 at 24-MAY-20

channel ORA_DISK_1: finished piece 1 at 24-MAY-20

piece handle=/u01/app/oracle/fast_recovery_area/PLM/backupset/2020_05_24/o1_mf_nnndf_ENCRYPTION_hdn89nxx_.bkp tag=ENCRYPTION comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01

Finished backup at 24-MAY-20

RMAN> ALTER SYSTEM SWITCH LOGFILE;

Statement processed

• Try to restore tablespace while encryption is ON

RMAN>

RESTORE TABLESPACE USERS;

RMAN>

Starting restore at 24-MAY-20

using channel ORA_DISK_1

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 00006 to /u01/app/oracle/oradata/plm/users01.dbf

channel ORA_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/PLM/backupset/2020_05_24/o1_mf_nnndf_ENCRYPTION_hdn89nxx_.bkp

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of restore command at 05/24/2020 12:55:22

ORA-19870: error while restoring backup piece /u01/app/oracle/fast_recovery_area/PLM/backupset/2020_05_24/o1_mf_nnndf_ENCRYPTION_hdn89nxx_.bkp

ORA-19913: unable to decrypt backup

ORA-28365: wallet is not open

• Set decryption on rman setting 

RMAN> SET DECRYPTION IDENTIFIED BY Password;

executing command: SET decryption

• Tablespace  users offline

RMAN> ALTER TABLESPACE USERS OFFLINE;

Statement processed

• Restore tablespace users

RMAN> RESTORE TABLESPACE USERS;

Starting restore at 24-MAY-20

using channel ORA_DISK_1

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 00006 to /u01/app/oracle/oradata/plm/users01.dbf

channel ORA_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/PLM/backupset/2020_05_24/o1_mf_nnndf_ENCRYPTION_hdn89nxx_.bkp

channel ORA_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/PLM/backupset/2020_05_24/o1_mf_nnndf_ENCRYPTION_hdn89nxx_.bkp tag=ENCRYPTION

channel ORA_DISK_1: restored backup piece 1

channel ORA_DISK_1: restore complete, elapsed time: 00:00:01

Finished restore at 24-MAY-20

• Recover tablespace users 

RMAN> RECOVER TABLESPACE USERS;

Starting recover at 24-MAY-20

using channel ORA_DISK_1

starting media recovery

media recovery complete, elapsed time: 00:00:00

Finished recover at 24-MAY-20

• Tablespace online

RMAN> ALTER TABLESPACE USERS ONLINE;

Statement processed

RMAN>

RMAN -Recovery from Loss of the TEMPFILES example

Recovery from Loss of the TEMPFILES 

• Login database  

oracle@suse1:/u01/app/oracle/product/12.1.0/dbhome_1/dbs> sqlplus sys/xxxx123@plm as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Sun May 24 11:47:04 2020

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, Real Application Clusters, OLAP, Advanced Analytics

and Real Application Testing options

SQL> select name,open_mode,database_role from v$database;

NAME      OPEN_MODE            DATABASE_ROLE

--------- -------------------- ----------------

PLM       READ WRITE           PRIMARY

• Check Tempfile location

SQL> SELECT NAME FROM V$TEMPFILE;

NAME

--------------------------------------------------------------------------------

/u01/app/oracle/oradata/plm/temp01.dbf

SQL>

• Remove tempfile at OS level

host rm -f /u01/app/oracle/oradata/plm/temp01.dbf

• Create global temporary table it will through error

SQL> CREATE GLOBAL TEMPORARY TABLE test ON COMMIT PRESERVE ROWS AS SELECT * FROM DBA_TABLES ;

CREATE GLOBAL TEMPORARY TABLE test ON COMMIT PRESERVE ROWS AS SELECT * FROM DBA_TABLES

                                                                                               *

ERROR at line 1:

ORA-01116: error in opening database file 201

ORA-01110: data file 201: '/u01/app/oracle/oradata/plm/temp01.dbf'

ORA-27041: unable to open file

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

SQL> select * from dba_temp_files;

FILE_NAME

--------------------------------------------------------------------------------

   FILE_ID TABLESPACE_NAME                     BYTES     BLOCKS STATUS

---------- ------------------------------ ---------- ---------- -------

RELATIVE_FNO AUT   MAXBYTES  MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS

------------ --- ---------- ---------- ------------ ---------- -----------

/u01/app/oracle/oradata/plm/temp01.dbf

         1 TEMP                            206569472      25216 ONLINE

           1 YES 3.4360E+10    4194302           80  205520896       25088

• Add new datafile on TEMP tablespace 

SQL> ALTER TABLESPACE temp ADD TEMPFILE '/u01/app/oracle/oradata/plm/temp02.dbf' size 1g;

Tablespace altered.

SQL>

• Still it will through same error 

SQL> CREATE GLOBAL TEMPORARY TABLE test ON COMMIT PRESERVE ROWS AS SELECT * FROM DBA_TABLES;

CREATE GLOBAL TEMPORARY TABLE test ON COMMIT PRESERVE ROWS AS SELECT * FROM DBA_TABLES

                                                                                     *

ERROR at line 1:

ORA-01116: error in opening database file 201

ORA-01110: data file 201: '/u01/app/oracle/oradata/plm/temp01.dbf'

ORA-27041: unable to open file

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

• OFFLINE tempfile datafile which thow an error.

SQL> ALTER DATABASE TEMPFILE '/u01/app/oracle/oradata/plm/temp01.dbf' OFFLINE;

Database altered.

• Check the datafile on temp tablespace.

SQL> select name from v$tempfile;

NAME

--------------------------------------------------------------------------------

/u01/app/oracle/oradata/plm/temp01.dbf

/u01/app/oracle/oradata/plm/temp02.dbf

• Create Global temporary table again this time it will create successfully

SQL>  CREATE GLOBAL TEMPORARY TABLE test ON COMMIT PRESERVE ROWS AS SELECT * FROM DBA_TABLES;

Table created.

SQL>

RMAN - Inactive Redo Log Group recovery during Database Startup

Recovery from Loss of an Inactive Redo Log Group on Database Startup

Run the following query and select a GROUP# which its status is INACTIVE. 

• Check the inactive group of file

SQL> SELECT G.GROUP#, G.STATUS FROM V$LOG G ORDER BY 1;

    GROUP# STATUS

---------- ----------------

         1 INACTIVE

         2 CURRENT

         3 INACTIVE

• Check the location of file group 3 and remove database at OS level.

SQL> col MEMBER for a150

SELECT MEMBER FROM V$LOGFILE WHERE GROUP#=3;

SQL>

MEMBER

----------------------------------------------------------------------------------------------------

/u01/app/oracle/oradata/plm/redo03.log

• Shutdown database

SQL> SHUTDOWN IMMEDIATE ;

Database closed.

Database dismounted.

ORACLE instance shut down.

• Startup , It will through an error.

SQL> STARTUP

ORACLE instance started.

Total System Global Area 2717908992 bytes

Fixed Size                  2928104 bytes

Variable Size             704643608 bytes

Database Buffers         1996488704 bytes

Redo Buffers               13848576 bytes

Database mounted.

ORA-03113: end-of-file on communication channel

Process ID: 8227

Session ID: 1 Serial number: 51969

SQL> exit

Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, Real Application Clusters, OLAP, Advanced Analytics

and Real Application Testing options

oracle@suse1:~> sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Thu May 21 18:17:08 2020

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Connected to an idle instance.

• Start database in mount stage

SQL> STARTUP MOUNT;

ORACLE instance started.

Total System Global Area 2717908992 bytes

Fixed Size                  2928104 bytes

Variable Size             704643608 bytes

Database Buffers         1996488704 bytes

Redo Buffers               13848576 bytes

Database mounted.

• Clear logfile group3 

SQL> ALTER DATABASE CLEAR LOGFILE GROUP 3;

Database altered.

• Open the database

SQL> ALTER DATABASE OPEN;

Database altered.

SQL> @display_logs

NAME                                                            Size MB    Used MB

------------------------------------------------------------ ---------- ----------

/u01/app/oracle/fast_recovery_area                                 4560       1379

SQL> ALTER SYSTEM SWITCH LOGFILE;

System altered.

SQL>

SQL> col MEMBER for a30

SELECT G.GROUP#, G.STATUS G_STATUS, M.STATUS M_STATUS,    SUBSTR( MEMBER, INSTR(MEMBER,'o1'))  MEMBER FROM V$LOG G, V$LOGFILE M WHERE G.GROUP#=M.GROUP# ORDER BY 1;SQL>

    GROUP# G_STATUS         M_STATU MEMBER

---------- ---------------- ------- ------------------------------

         1 INACTIVE                 /u01/app/oracle/oradata/plm/re

                                    do01.log

         2 ACTIVE                   /u01/app/oracle/oradata/plm/re

                                    do02.log

         3 CURRENT                  /u01/app/oracle/oradata/plm/re

                                    do03.log

SQL> exit