Table Recovery Using RMAN Backup

 

 Table Recovery Using RMAN Backup

           

Below steps are used to recover the table records by using RMAN backup

 

Step 1:- User creation

 

SQL> create user vignesh identified by vbt default tablespace users temporary tablespace temp profile default account unlock;

User created.

 

Step 2:- Provide grant privileges to user –vignesh

 

SQL> grant create session, create table to vignesh;

Grant succeeded.

SQL> alter user vignesh quota 500M on users;

User altered.

 

Step 3:- Table creation

 

SQL> conn vigneh/vbt

connected

 

SQL>  create  table emp(id number);

 

Table created.

 

Insert 100000 records to the emp table

 

SQL> begin

for i in 1 .. 100000 loop

insert into emp values(i);

end loop;

end;

/  

PL/SQL procedure successfully completed.

 

SQL> commit;

 

Commit complete.

 

SQL> select count(*)from emp;

 

  COUNT(*)

----------

    100000

Get current system date and time

 

SQL> SELECT TO_CHAR (SYSDATE, 'MM-DD-YYYY HH24:MI:SS') "NOW" FROM DUAL;

 

NOW

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

09-08-2021 03:51:13

 

Check archive log mode enabled

 

SQL> archive log list

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence     11

Next log sequence to archive   13

Current log sequence           13

SQL> exit

 

Step -4 Connect rman and backup the full database with archivelogs

 

 [oracle@vignesh dbs]$ rman target/

 

Recovery Manager: Release 19.0.0.0.0 - Production on Wed Sep 8 03:53:19 2021

Version 19.3.0.0.0

 

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

 

connected to target database: LIVEDB (DBID=3038400691)

 

RMAN> backup database plus archivelog;

 

 

Starting backup at 08-SEP-21

current log archived

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=86 device type=DISK

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=5 RECID=1 STAMP=1082689816

input archived log thread=1 sequence=6 RECID=2 STAMP=1082689893

input archived log thread=1 sequence=7 RECID=3 STAMP=1082690477

input archived log thread=1 sequence=8 RECID=4 STAMP=1082690478

input archived log thread=1 sequence=9 RECID=5 STAMP=1082690480

input archived log thread=1 sequence=10 RECID=6 STAMP=1082690866

input archived log thread=1 sequence=11 RECID=7 STAMP=1082690927

input archived log thread=1 sequence=12 RECID=8 STAMP=1082691628

input archived log thread=1 sequence=13 RECID=9 STAMP=1082692404

channel ORA_DISK_1: starting piece 1 at 08-SEP-21

channel ORA_DISK_1: finished piece 1 at 08-SEP-21

piece handle=/u01/app/oracle/fast_recovery_area/LIVEDB/backupset/2021_09_08/o1_mf_annnn_TAG20210908T035324_jmhslx4y_.bkp tag=TAG20210908T035324 comment=NONE

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

Finished backup at 08-SEP-21

 

Starting backup at 08-SEP-21

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=00001 name=/u01/app/oracle/oradata/LIVEDB/datafile/o1_mf_system_jmho76m8_.dbf

input datafile file number=00003 name=/u01/app/oracle/oradata/LIVEDB/datafile/o1_mf_sysaux_jmho8n57_.dbf

input datafile file number=00004 name=/u01/app/oracle/oradata/LIVEDB/datafile/o1_mf_undotbs1_jmho9f8x_.dbf

input datafile file number=00007 name=/u01/app/oracle/oradata/LIVEDB/datafile/o1_mf_users_jmho9gcd_.dbf

channel ORA_DISK_1: starting piece 1 at 08-SEP-21

channel ORA_DISK_1: finished piece 1 at 08-SEP-21

piece handle=/u01/app/oracle/fast_recovery_area/LIVEDB/backupset/2021_09_08/o1_mf_nnndf_TAG20210908T035340_jmhsmf58_.bkp tag=TAG20210908T035340 comment=NONE

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

Finished backup at 08-SEP-21

 

Starting backup at 08-SEP-21

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=14 RECID=10 STAMP=1082692477

channel ORA_DISK_1: starting piece 1 at 08-SEP-21

channel ORA_DISK_1: finished piece 1 at 08-SEP-21

piece handle=/u01/app/oracle/fast_recovery_area/LIVEDB/backupset/2021_09_08/o1_mf_annnn_TAG20210908T035437_jmhso67v_.bkp tag=TAG20210908T035437 comment=NONE

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

Finished backup at 08-SEP-21

 

Starting Control File and SPFILE Autobackup at 08-SEP-21

piece handle=/u01/app/oracle/fast_recovery_area/LIVEDB/autobackup/2021_09_08/o1_mf_s_1082692480_jmhso8gf_.bkp comment=NONE

Finished Control File and SPFILE Autobackup at 08-SEP-21

 

RMAN> exit

 

 

Recovery Manager complete.

 

Once backup completed drop the table under the user for the recovery purpose

 

SQL>  drop table vignesh.emp;

 

Table dropped.

 

Step 5 -Connect rman and recover the table data until mention the time of table record inserted mention the auxiliary destination

 

 [oracle@vignesh ~]$ rman target /

 

 

connected to target database: LIVEDB (DBID=3038400691)

 

RMAN>  recover table vignesh.emp until time "to_date('2021-09-08 03:51:13','yyyy-mm-dd:hh24:mi:ss')" auxiliary destination '/u01/ARC_BKP';

 

Starting recover at 08-SEP-21

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=57 device type=DISK

RMAN-05026: warning: presuming following set of tablespaces applies to specified point-in-time

 

List of tablespaces expected to have UNDO segments

Tablespace SYSTEM

Tablespace UNDOTBS1

 

Creating automatic instance, with SID='hAij'

 

initialization parameters used for automatic instance:

db_name=LIVEDB

db_unique_name=hAij_pitr_LIVEDB

compatible=19.0.0

db_block_size=8192

db_files=200

diagnostic_dest=/u01/app/oracle

_system_trig_enabled=FALSE

sga_target=1648M

processes=200

db_create_file_dest=/u01/ARC_BKP

log_archive_dest_1='location=/u01/ARC_BKP'

#No auxiliary parameter file used

 

 

starting up automatic instance LIVEDB

 

Oracle instance started

 

Total System Global Area    1728050048 bytes

 

Fixed Size                     8897408 bytes

Variable Size                402653184 bytes

Database Buffers            1308622848 bytes

Redo Buffers                   7876608 bytes

Automatic instance created

 

contents of Memory Script:

{

# set requested point in time

set until  time "to_date('2021-09-08 03:51:13','yyyy-mm-dd:hh24:mi:ss')";

# restore the controlfile

restore clone controlfile;

 

# mount the controlfile

sql clone 'alter database mount clone database';

 

# archive current online log

sql 'alter system archive log current';

}

executing Memory Script

 

executing command: SET until clause

 

Starting restore at 08-SEP-21

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: SID=34 device type=DISK

 

channel ORA_AUX_DISK_1: starting datafile backup set restore

channel ORA_AUX_DISK_1: restoring control file

channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/LIVEDB/autobackup/2021_09_08/o1_mf_s_1082690928_jmhr4ryc_.bkp

channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/LIVEDB/autobackup/2021_09_08/o1_mf_s_1082690928_jmhr4ryc_.bkp tag=TAG20210908T032848

channel ORA_AUX_DISK_1: restored backup piece 1

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

output file name=/u01/ARC_BKP/LIVEDB/controlfile/o1_mf_jmhstlpr_.ctl

Finished restore at 08-SEP-21

 

sql statement: alter database mount clone database

 

sql statement: alter system archive log current

 

contents of Memory Script:

{

# set requested point in time

set until  time "to_date('2021-09-08 03:51:13','yyyy-mm-dd:hh24:mi:ss')";

# set destinations for recovery set and auxiliary set datafiles

set newname for clone datafile  1 to new;

set newname for clone datafile  4 to new;

set newname for clone datafile  3 to new;

set newname for clone tempfile  1 to new;

# switch all tempfiles

switch clone tempfile all;

# restore the tablespaces in the recovery set and the auxiliary set

restore clone datafile  1, 4, 3;

 

switch clone datafile all;

}

executing Memory Script

 

executing command: SET until clause

 

executing command: SET NEWNAME

 

executing command: SET NEWNAME

 

executing command: SET NEWNAME

 

executing command: SET NEWNAME

 

renamed tempfile 1 to /u01/ARC_BKP/LIVEDB/datafile/o1_mf_temp_%u_.tmp in control file

 

Starting restore at 08-SEP-21

using channel ORA_AUX_DISK_1

 

channel ORA_AUX_DISK_1: starting datafile backup set restore

channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set

channel ORA_AUX_DISK_1: restoring datafile 00001 to /u01/ARC_BKP/LIVEDB/datafile/o1_mf_system_%u_.dbf

channel ORA_AUX_DISK_1: restoring datafile 00004 to /u01/ARC_BKP/LIVEDB/datafile/o1_mf_undotbs1_%u_.dbf

channel ORA_AUX_DISK_1: restoring datafile 00003 to /u01/ARC_BKP/LIVEDB/datafile/o1_mf_sysaux_%u_.dbf

channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/LIVEDB/backupset/2021_09_08/o1_mf_nnndf_TAG20210908T032750_jmhr2yv3_.bkp

channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/LIVEDB/backupset/2021_09_08/o1_mf_nnndf_TAG20210908T032750_jmhr2yv3_.bkp tag=TAG20210908T032750

channel ORA_AUX_DISK_1: restored backup piece 1

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:01:06

Finished restore at 08-SEP-21

 

datafile 1 switched to datafile copy

input datafile copy RECID=4 STAMP=1082692724 file name=/u01/ARC_BKP/LIVEDB/datafile/o1_mf_system_jmhsttmb_.dbf

datafile 4 switched to datafile copy

input datafile copy RECID=5 STAMP=1082692724 file name=/u01/ARC_BKP/LIVEDB/datafile/o1_mf_undotbs1_jmhsttog_.dbf

datafile 3 switched to datafile copy

input datafile copy RECID=6 STAMP=1082692724 file name=/u01/ARC_BKP/LIVEDB/datafile/o1_mf_sysaux_jmhsttob_.dbf

 

contents of Memory Script:

{

# set requested point in time

set until  time "to_date('2021-09-08 03:51:13','yyyy-mm-dd:hh24:mi:ss')";

# online the datafiles restored or switched

sql clone "alter database datafile  1 online";

sql clone "alter database datafile  4 online";

sql clone "alter database datafile  3 online";

# recover and open database read only

recover clone database tablespace  "SYSTEM", "UNDOTBS1", "SYSAUX";

sql clone 'alter database open read only';

}

executing Memory Script

 

executing command: SET until clause

 

sql statement: alter database datafile  1 online

 

sql statement: alter database datafile  4 online

 

sql statement: alter database datafile  3 online

 

Starting recover at 08-SEP-21

using channel ORA_AUX_DISK_1

 

starting media recovery

 

archived log for thread 1 with sequence 11 is already on disk as file /u01/app/oracle/fast_recovery_area/LIVEDB/archivelog/2021_09_08/o1_mf_1_11_jmhr4q32_.arc

archived log for thread 1 with sequence 12 is already on disk as file /u01/app/oracle/fast_recovery_area/LIVEDB/archivelog/2021_09_08/o1_mf_1_12_jmhrtn65_.arc

archived log for thread 1 with sequence 13 is already on disk as file /u01/app/oracle/fast_recovery_area/LIVEDB/archivelog/2021_09_08/o1_mf_1_13_jmhslv5x_.arc

archived log file name=/u01/app/oracle/fast_recovery_area/LIVEDB/archivelog/2021_09_08/o1_mf_1_11_jmhr4q32_.arc thread=1 sequence=11

archived log file name=/u01/app/oracle/fast_recovery_area/LIVEDB/archivelog/2021_09_08/o1_mf_1_12_jmhrtn65_.arc thread=1 sequence=12

archived log file name=/u01/app/oracle/fast_recovery_area/LIVEDB/archivelog/2021_09_08/o1_mf_1_13_jmhslv5x_.arc thread=1 sequence=13

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

Finished recover at 08-SEP-21

 

sql statement: alter database open read only

 

contents of Memory Script:

{

   sql clone "create spfile from memory";

   shutdown clone immediate;

   startup clone nomount;

   sql clone "alter system set  control_files =

  ''/u01/ARC_BKP/LIVEDB/controlfile/o1_mf_jmhstlpr_.ctl'' comment=

 ''RMAN set'' scope=spfile";

   shutdown clone immediate;

   startup clone nomount;

# mount database

sql clone 'alter database mount clone database';

}

executing Memory Script

 

sql statement: create spfile from memory

 

database closed

database dismounted

Oracle instance shut down

 

connected to auxiliary database (not started)

Oracle instance started

 

Total System Global Area    1728050048 bytes

 

Fixed Size                     8897408 bytes

Variable Size                402653184 bytes

Database Buffers            1308622848 bytes

Redo Buffers                   7876608 bytes

 

sql statement: alter system set  control_files =   ''/u01/ARC_BKP/LIVEDB/controlfile/o1_mf_jmhstlpr_.ctl'' comment= ''RMAN set'' scope=spfile

 

Oracle instance shut down

 

connected to auxiliary database (not started)

Oracle instance started

 

Total System Global Area    1728050048 bytes

 

Fixed Size                     8897408 bytes

Variable Size                402653184 bytes

Database Buffers            1308622848 bytes

Redo Buffers                   7876608 bytes

 

sql statement: alter database mount clone database

 

contents of Memory Script:

{

# set requested point in time

set until  time "to_date('2021-09-08 03:51:13','yyyy-mm-dd:hh24:mi:ss')";

# set destinations for recovery set and auxiliary set datafiles

set newname for datafile  7 to new;

# restore the tablespaces in the recovery set and the auxiliary set

restore clone datafile  7;

 

switch clone datafile all;

}

executing Memory Script

 

executing command: SET until clause

 

executing command: SET NEWNAME

 

Starting restore at 08-SEP-21

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: SID=34 device type=DISK

 

channel ORA_AUX_DISK_1: starting datafile backup set restore

channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set

channel ORA_AUX_DISK_1: restoring datafile 00007 to /u01/ARC_BKP/HAIJ_PITR_LIVEDB/datafile/o1_mf_users_%u_.dbf

channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/LIVEDB/backupset/2021_09_08/o1_mf_nnndf_TAG20210908T032750_jmhr2yv3_.bkp

channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/LIVEDB/backupset/2021_09_08/o1_mf_nnndf_TAG20210908T032750_jmhr2yv3_.bkp tag=TAG20210908T032750

channel ORA_AUX_DISK_1: restored backup piece 1

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

Finished restore at 08-SEP-21

 

datafile 7 switched to datafile copy

input datafile copy RECID=8 STAMP=1082692811 file name=/u01/ARC_BKP/HAIJ_PITR_LIVEDB/datafile/o1_mf_users_jmhszlhd_.dbf

 

contents of Memory Script:

{

# set requested point in time

set until  time "to_date('2021-09-08 03:51:13','yyyy-mm-dd:hh24:mi:ss')";

# online the datafiles restored or switched

sql clone "alter database datafile  7 online";

# recover and open resetlogs

recover clone database tablespace  "USERS", "SYSTEM", "UNDOTBS1", "SYSAUX" delete archivelog;

alter clone database open resetlogs;

}

executing Memory Script

 

executing command: SET until clause

 

sql statement: alter database datafile  7 online

 

Starting recover at 08-SEP-21

using channel ORA_AUX_DISK_1

 

starting media recovery

 

archived log for thread 1 with sequence 11 is already on disk as file /u01/app/oracle/fast_recovery_area/LIVEDB/archivelog/2021_09_08/o1_mf_1_11_jmhr4q32_.arc

archived log for thread 1 with sequence 12 is already on disk as file /u01/app/oracle/fast_recovery_area/LIVEDB/archivelog/2021_09_08/o1_mf_1_12_jmhrtn65_.arc

archived log for thread 1 with sequence 13 is already on disk as file /u01/app/oracle/fast_recovery_area/LIVEDB/archivelog/2021_09_08/o1_mf_1_13_jmhslv5x_.arc

archived log file name=/u01/app/oracle/fast_recovery_area/LIVEDB/archivelog/2021_09_08/o1_mf_1_11_jmhr4q32_.arc thread=1 sequence=11

archived log file name=/u01/app/oracle/fast_recovery_area/LIVEDB/archivelog/2021_09_08/o1_mf_1_12_jmhrtn65_.arc thread=1 sequence=12

archived log file name=/u01/app/oracle/fast_recovery_area/LIVEDB/archivelog/2021_09_08/o1_mf_1_13_jmhslv5x_.arc thread=1 sequence=13

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

Finished recover at 08-SEP-21

 

database opened

 

contents of Memory Script:

{

# create directory for datapump import

sql "create or replace directory TSPITR_DIROBJ_DPDIR as ''

/u01/ARC_BKP''";

# create directory for datapump export

sql clone "create or replace directory TSPITR_DIROBJ_DPDIR as ''

/u01/ARC_BKP''";

}

executing Memory Script

 

sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/u01/ARC_BKP''

 

sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/u01/ARC_BKP''

 

Performing export of tables...

   EXPDP> Starting "SYS"."TSPITR_EXP_hAij_tuvv":

   EXPDP> Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

   EXPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

   EXPDP> Processing object type TABLE_EXPORT/TABLE/TABLE

   EXPDP> . . exported "VIGNESH"."EMP"                             873.1 KB  100000 rows

   EXPDP> Master table "SYS"."TSPITR_EXP_hAij_tuvv" successfully loaded/unloaded

   EXPDP> ******************************************************************************

   EXPDP> Dump file set for SYS.TSPITR_EXP_hAij_tuvv is:

   EXPDP>   /u01/ARC_BKP/tspitr_hAij_54955.dmp

   EXPDP> Job "SYS"."TSPITR_EXP_hAij_tuvv" successfully completed at Wed Sep 8 04:01:35 2021 elapsed 0 00:00:37

Export completed

 

 

contents of Memory Script:

{

# shutdown clone before import

shutdown clone abort

}

executing Memory Script

 

Oracle instance shut down

 

Performing import of tables...

   IMPDP> Master table "SYS"."TSPITR_IMP_hAij_erbn" successfully loaded/unloaded

   IMPDP> Starting "SYS"."TSPITR_IMP_hAij_erbn":

   IMPDP> Processing object type TABLE_EXPORT/TABLE/TABLE

   IMPDP> Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

   IMPDP> . . imported "VIGNESH"."EMP"                             873.1 KB  100000 rows

   IMPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

   IMPDP> Job "SYS"."TSPITR_IMP_hAij_erbn" successfully completed at Wed Sep 8 04:01:44 2021 elapsed 0 00:00:03

Import completed

 

 

Removing automatic instance

Automatic instance removed

auxiliary instance file /u01/ARC_BKP/LIVEDB/datafile/o1_mf_temp_jmhsx8cg_.tmp deleted

auxiliary instance file /u01/ARC_BKP/HAIJ_PITR_LIVEDB/onlinelog/o1_mf_3_jmhszzv5_.log deleted

auxiliary instance file /u01/ARC_BKP/HAIJ_PITR_LIVEDB/onlinelog/o1_mf_2_jmhszobv_.log deleted

auxiliary instance file /u01/ARC_BKP/HAIJ_PITR_LIVEDB/onlinelog/o1_mf_1_jmhszo49_.log deleted

auxiliary instance file /u01/ARC_BKP/HAIJ_PITR_LIVEDB/datafile/o1_mf_users_jmhszlhd_.dbf deleted

auxiliary instance file /u01/ARC_BKP/LIVEDB/datafile/o1_mf_sysaux_jmhsttob_.dbf deleted

auxiliary instance file /u01/ARC_BKP/LIVEDB/datafile/o1_mf_undotbs1_jmhsttog_.dbf deleted

auxiliary instance file /u01/ARC_BKP/LIVEDB/datafile/o1_mf_system_jmhsttmb_.dbf deleted

auxiliary instance file /u01/ARC_BKP/LIVEDB/controlfile/o1_mf_jmhstlpr_.ctl deleted

auxiliary instance file tspitr_hAij_54955.dmp deleted

Finished recover at 08-SEP-21

 

RMAN> exit

 

step 6- connect database and the user to check the table can be recoverd

 

 [oracle@vignesh ~]$ export ORACLE_SID=livedb

[oracle@vignesh ~]$ sqlplus / as sysdba

 

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Sep 8 04:01:56 2021

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> select count(*)from vignesh.emp;

 

  COUNT(*)

----------

    100000

 

SQL>

Control File recovery Using RMAN backup

 

Control File recovery Using RMAN backup

 

Step 1- view the control file location

 

SQL> select name from v$controlfile;

 

NAME

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

/u01/app/oracle/oradata/ORCL/controlfile/o1_mf_jkfb1xvw_.ctl

/u01/app/oracle/fast_recovery_area/ORCL/controlfile/o1_mf_jkfb1y1l_.ctl

 

Step 2- copy the control file to other location

 

 [oracle@sample ~]$ cp /u01/app/oracle/oradata/ORCL/controlfile/o1_mf_jkfb1xvw_.ctl /backup/rman

 

 

Step 3- connect and shut down the database

 

 [oracle@sample ~]$ . .orcl.env

[oracle@sample ~]$ sqlplus / as sysdba

 

Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.3.0.0.0

 

SQL> shut immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> exit

Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.3.0.0.0

 

Step 4- connect rman and start the database to spectify the copied control file location

[oracle@sample ~]$ rman target/

 

Recovery Manager: Release 19.0.0.0.0 - Production on Sun Sep 5 23:27:39 2021

Version 19.3.0.0.0

 

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

 

connected to target database (not started)

 

Step -5

 

set database id

 

RMAN> set dbid 1608294549;

 

executing command: SET DBID

 

start the database nomount stage

 

RMAN>  STARTUP NOMOUNT;

 

Oracle instance started

 

Total System Global Area    2147481656 bytes

 

Fixed Size                     8898616 bytes

Variable Size               1694498816 bytes

Database Buffers             436207616 bytes

Redo Buffers                   7876608 bytes

 

Restore the database mention the control file location

 

RMAN> RESTORE CONTROLFILE FROM "/backup/rman/o1_mf_jkfb1xvw_.ctl";

 

Starting restore at 05-SEP-21

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=38 device type=DISK

 

channel ORA_DISK_1: copied control file copy

output file name=/u01/app/oracle/oradata/ORCL/controlfile/o1_mf_jkfb1xvw_.ctl

output file name=/u01/app/oracle/fast_recovery_area/ORCL/controlfile/o1_mf_jkfb1y1l_.ctl

Finished restore at 05-SEP-21

 

Finally mount and open the database

 

RMAN>  alter database mount;

 

released channel: ORA_DISK_1

Statement processed

 

 

Spfile recovery using RMAN backup

 

 

Spfile recovery using RMAN backup

 

Connect database and check db is up spfile or not

 

[oracle@sample ~]$ . .orcl.env

[oracle@sample ~]$ sqlplus / as sysdba

 

SQL*Plus: Release 19.0.0.0.0 - Production on Sun Sep 5 22:20:00 2021

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 parameter pfile;

 

NAME                                 TYPE        VALUE

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

spfile                               string      /u01/app/oracle/product/19.0.0

                                                 /dbhome_1/dbs/spfileorcl.ora

 

Connect rman and backup the spfile

 

[oracle@sample ~]$ rman target/

 

Recovery Manager: Release 19.0.0.0.0 - Production on Sun Sep 5 22:22:51 2021

Version 19.3.0.0.0

 

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

 

connected to target database: ORCL (DBID=1608294549)

 

RMAN> backup spfile;

 

Starting backup at 05-SEP-21

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=83 device type=DISK

channel ORA_DISK_1: starting full datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

including current SPFILE in backup set

channel ORA_DISK_1: starting piece 1 at 05-SEP-21

channel ORA_DISK_1: finished piece 1 at 05-SEP-21

piece handle=/backup/rman/full_2b08b8m6_75_1 tag=TAG20210905T222302 comment=NONE

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

Finished backup at 05-SEP-21

 

Starting Control File and SPFILE Autobackup at 05-SEP-21

piece handle=/u01/app/oracle/fast_recovery_area/ORCL/autobackup/2021_09_05/o1_mf_s_1082499783_jm9xhjjd_.bkp comment=NONE

Finished Control File and SPFILE Autobackup at 05-SEP-21

 

RMAN> exit

 

 

Shutdown the database

 

SQL> shut immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> exit

 

Move the spfile to backup file

 

[oracle@sample ~]$ cd $ORACLE_HOME/dbs

[oracle@sample dbs]$ ls

Spfileorcl.ora  initorcl.ora

 

[oracle@sample dbs]$ mv spfileorcl.ora spfileorcl.ora_bkp

 

[oracle@sample dbs]$ ls

initorcl.ora   Spfileorcl.ora_bkp

 

connect rman and recover the spfile

 

[oracle@sample ~]$ rman target/

 

Recovery Manager: Release 19.0.0.0.0 - Production on Sun Sep 5 22:29:28 2021

Version 19.3.0.0.0

 

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

 

connected to target database (not started)

 

set database id

 

RMAN> set dbid 1608294549

 

executing command: SET DBID

 

start the database in force option with nomount stage

 

RMAN>  startup force nomount;

 

Oracle instance started

 

Total System Global Area    2147481656 bytes

 

Fixed Size                     8898616 bytes

Variable Size               1677721600 bytes

Database Buffers             452984832 bytes

Redo Buffers                   7876608 bytes

 

Restore the spfile from autobackup locatiom

 

RMAN> restore spfile  from autobackup;

 

Starting restore at 05-SEP-21

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=38 device type=DISK

 

recovery area destination: /u01/app/oracle/fast_recovery_area

database name (or database unique name) used for search: ORCL

channel ORA_DISK_1: AUTOBACKUP /u01/app/oracle/fast_recovery_area/ORCL/autobackup/2021_09_05/o1_mf_s_1082499783_jm9xhjjd_.bkp found in the recovery area

channel ORA_DISK_1: looking for AUTOBACKUP on day: 20210905

channel ORA_DISK_1: restoring spfile from AUTOBACKUP /u01/app/oracle/fast_recovery_area/ORCL/autobackup/2021_09_05/o1_mf_s_1082499783_jm9xhjjd_.bkp

channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete

Finished restore at 05-SEP-21

 

 

Recovery Manager complete.

 

Once RMAN recovery completed connect the database it is in mounted stage

 

 [oracle@sample ~]$ . .orcl.env

[oracle@sample ~]$ sqlplus / as sysdba

 

SQL*Plus: Release 19.0.0.0.0 - Production on Sun Sep 5 22:32:18 2021

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> select name,open_mode from v$database;

 

NAME      OPEN_MODE

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

ORCL      MOUNTED

 

Using alter command open the database

 

SQL> alter database open;

 

Database altered.

 

 

SQL>  select name,open_mode from v$database;

 

NAME      OPEN_MODE

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

ORCL      READ WRITE

Pin a query in shared pool

 

                                                 pin a query in shared pool

Pinning database objects in Oracle's shared pool can improve database performance. It is recommended that all ESRI stored procedures and sequences be pinned. Once objects reside in Oracle's shared pool, they do not need to be parsed, which saves considerable resources.

Pinning objects in the shared pool uses available memory that might otherwise be used for other processes that share the pool. Allocate the additional memory to the shared pool as needed. In general, do not pin objects that are rarely used; this could have the adverse affect on database performance.

Procedure

1.      Before pinning database objects, create the SYS package that pins the objects in the shared pool. As the SYS user in SQL*Plus, execute the dbmspool.sql script located under the $ORACLE_HOME/rdbms/admin directory.

Optionally, it is possible to grant execute privileges to the new DBMS_SHARED_POOL package to users who will need to pin objects. For example, to grant these privileges to the SDE user:

Code:

GRANT execute ON dbms_shared_pool TO sde; 

2.      Begin pinning objects in the shared pool.

Code:

EXECUTE dbms_shared_pool.keep ('STANDARD', 'P'); As a general rule, always pin the following 

packages owned by SYS:

   STANDARD 

   DBMS_STANDARD 

   DBMS_UTILITY 

   DBMS_DESCRIBE 

   DBMS_OUTPUT 

Pinning other SYS packages that are often used, such as DBMS_LOCK and DBMS_ALERT, 

may be desired.

3.      As the SDE user, pin the objects that the application frequently uses.
Code:
EXECUTE dbms_shared_pool.keep ('VERSION_UTIL', 'P');

Code:

 EXECUTE dbms_shared_pool.keep ('LOCK_UTIL', 'P'); 

Database objects are not limited to stored procedures. Database sequences, triggers, and cursors

 can all be pinned in the shared pool. ESRI recommends pinning frequently used sequences as well.

Code:

EXECUTE dbms_shared_pool.keep ('SDE.CONNECTION_ID_GENERATOR', 'Q'); 

SQL> SELECT 'Example TEST' FROM dual;

'EXAMPLETEST

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

Example TEST

SQL> select substr(sql_text,1,15) Text,address,hash_value,KEPT_VERSIONS

from v$sql where sql_text like '%Example%';  2 

SQL> @?/rdbms/admin/dbmspool.sql

Session altered.

Package created.

Grant succeeded.

Session altered.

SQL> exec DBMS_SHARED_POOL.PURGE ('000000006189AC28,2929179549','c');

PL/SQL procedure successfully completed.

SQL> select sid,serial#,prev_sql_id from v$session where audsid=userenv('sessionid');

       SID    SERIAL# PREV_SQL_ID

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

            79        57117 5rsr6qt29hu5s

SQL> select ADDRESS, HASH_VALUE from V$SQLAREA where SQL_Id='5rsr6qt29hu5s';

ADDRESS      HASH_VALUE

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

0000000061A2DE58 1150838968