How to Recover a Table from Oracle 12c via RMAN Backup ?
on same server . Table recovery failed with RMAN-05057: Table OWNER.TABLE_NAME not found (Doc ID 2764271.1)table being recovered resides in a pluggable database, include the keywords "OF PLUGGABLE DATABASE <pdb_name>" in the script/command as below: RECOVER TABLE OWNER.TABLE_NAME UNTIL TIME "to_date('08032021 1540', 'ddmmyyyy hh24mi')" AUXILIARY DESTINATION '/<path>' REMAP TABLE OWNER.<table name>:<new table name>; ========= RMAN> RECOVER TABLE VIHAANX.TEST_RESTORE05 OF PLUGGABLE DATABASE PDB9 UNTIL TIME "to_date('05/10/2022 07:15:40','dd/mm/yyyy hh24:mi:ss')" AUXILIARY DESTINATION '/u01/app/oracle/aux' REMAP TABLE 'VIHAANX'.'TEST_RESTORE05':'TEST_RESTORE55'; Starting recover at 05-10-2022 08:34:46 using channel ORA_DISK_1 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 PDB9:SYSTEM Tablespace UNDOTBS1 Tablespace PDB9:UNDOTBS1 Tablespace UNDOTBS2 Tablespace PDB9:PDB9_UNDOTBS1 Creating automatic instance, with SID='wlan' initialization parameters used for automatic instance: db_name=VIHCDBD8 db_unique_name=wlan_pitr_PDB9_VIHCDBD8 compatible=12.2.0 db_block_size=8192 db_files=200 diagnostic_dest=/u01/app/oracle _system_trig_enabled=FALSE sga_target=21696M processes=200 db_create_file_dest=/u01/app/oracle/aux log_archive_dest_1='location=/u01/app/oracle/aux' enable_pluggable_database=true _clone_one_pdb_recovery=true #No auxiliary parameter file used starting up automatic instance VIHCDBD8 Oracle instance started Total System Global Area 22749904896 bytes Fixed Size 19421832 bytes Variable Size 3019900280 bytes Database Buffers 19662897152 bytes Redo Buffers 47685632 bytes Automatic instance created contents of Memory Script: { # set requested point in time set until time "to_date('05/10/2022 07:15:40','dd/mm/yyyy 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 05-10-2022 08:35:57 allocated channel: ORA_AUX_DISK_1 channel ORA_AUX_DISK_1: SID=318 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/VIHAAN8/cf_c-3962735431-20221005-00 channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/VIHAAN8/cf_c-3962735431-20221005-00 tag=TAG20221005T071537 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/app/oracle/aux/VIHCDBD8/controlfile/o1_mf_kmtykgqc_.ctl Finished restore at 05-10-2022 08:35:59 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('05/10/2022 07:15:40','dd/mm/yyyy 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 5 to new; set newname for clone datafile 4 to new; set newname for clone datafile 8 to new; set newname for clone datafile 13 to new; set newname for clone datafile 21 to new; set newname for clone datafile 3 to new; set newname for clone datafile 6 to new; set newname for clone tempfile 1 to new; set newname for clone tempfile 6 to new; set newname for clone tempfile 8 to new; # switch all tempfiles switch clone tempfile all; # restore the tablespaces in the recovery set and the auxiliary set restore clone datafile 1, 5, 4, 8, 13, 21, 3, 6; 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 executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME renamed tempfile 1 to /u01/app/oracle/aux/VIHCDBD8/datafile/o1_mf_temp_tes_%u_.tmp in control file renamed tempfile 6 to /u01/app/oracle/aux/VIHCDBD8/datafile/o1_mf_temp_%u_.tmp in control file renamed tempfile 8 to /u01/app/oracle/aux/VIHCDBD8/A3144982890B1187E05324F56A40A1C1/datafile/o1_mf_temp_%u_.tmp in control file Starting restore at 05-10-2022 08:36:06 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 00005 to /u01/app/oracle/aux/VIHCDBD8/A3144982890B1187E05324F56A40A1C1/datafile/o1_mf_system_%u_.dbf channel ORA_AUX_DISK_1: restoring datafile 00008 to /u01/app/oracle/aux/VIHCDBD8/A3144982890B1187E05324F56A40A1C1/datafile/o1_mf_undotbs1_%u_.dbf channel ORA_AUX_DISK_1: restoring datafile 00021 to /u01/app/oracle/aux/VIHCDBD8/A3144982890B1187E05324F56A40A1C1/datafile/o1_mf_pdb9_und_%u_.dbf channel ORA_AUX_DISK_1: restoring datafile 00006 to /u01/app/oracle/aux/VIHCDBD8/A3144982890B1187E05324F56A40A1C1/datafile/o1_mf_sysaux_%u_.dbf channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/VIHAAN8/20221004_vihcdbd8_2493_1_1117186896 channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/VIHAAN8/20221004_vihcdbd8_2493_1_1117186896 tag=TAG20221004T094135 channel ORA_AUX_DISK_1: restored backup piece 1 channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:11:15 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/app/oracle/aux/VIHCDBD8/datafile/o1_mf_system_%u_.dbf channel ORA_AUX_DISK_1: restoring datafile 00004 to /u01/app/oracle/aux/VIHCDBD8/datafile/o1_mf_undotbs1_%u_.dbf channel ORA_AUX_DISK_1: restoring datafile 00013 to /u01/app/oracle/aux/VIHCDBD8/datafile/o1_mf_undotbs2_%u_.dbf channel ORA_AUX_DISK_1: restoring datafile 00003 to /u01/app/oracle/aux/VIHCDBD8/datafile/o1_mf_sysaux_%u_.dbf channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/VIHAAN8/20221004_vihcdbd8_2496_1_1117189672 channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/VIHAAN8/20221004_vihcdbd8_2496_1_1117189672 tag=TAG20221004T094135 channel ORA_AUX_DISK_1: restored backup piece 1 channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:02:35 Finished restore at 05-10-2022 08:50:05 datafile 1 switched to datafile copy input datafile copy RECID=144 STAMP=1117270205 file name=/u01/app/oracle/aux/VIHCDBD8/datafile/o1_mf_system_kmtz72fj_.dbf datafile 5 switched to datafile copy input datafile copy RECID=145 STAMP=1117270205 file name=/u01/app/oracle/aux/VIHCDBD8/A3144982890B1187E05324F56A40A1C1/datafile/o1_mf_system_kmtykz50_.dbf datafile 4 switched to datafile copy input datafile copy RECID=146 STAMP=1117270206 file name=/u01/app/oracle/aux/VIHCDBD8/datafile/o1_mf_undotbs1_kmtz72jl_.dbf datafile 8 switched to datafile copy input datafile copy RECID=147 STAMP=1117270206 file name=/u01/app/oracle/aux/VIHCDBD8/A3144982890B1187E05324F56A40A1C1/datafile/o1_mf_undotbs1_kmtykz5y_.dbf datafile 13 switched to datafile copy input datafile copy RECID=148 STAMP=1117270206 file name=/u01/app/oracle/aux/VIHCDBD8/datafile/o1_mf_undotbs2_kmtz72gq_.dbf datafile 21 switched to datafile copy input datafile copy RECID=149 STAMP=1117270206 file name=/u01/app/oracle/aux/VIHCDBD8/A3144982890B1187E05324F56A40A1C1/datafile/o1_mf_pdb9_und_kmtykz4l_.dbf datafile 3 switched to datafile copy input datafile copy RECID=150 STAMP=1117270207 file name=/u01/app/oracle/aux/VIHCDBD8/datafile/o1_mf_sysaux_kmtz72ck_.dbf datafile 6 switched to datafile copy input datafile copy RECID=151 STAMP=1117270207 file name=/u01/app/oracle/aux/VIHCDBD8/A3144982890B1187E05324F56A40A1C1/datafile/o1_mf_sysaux_kmtykz6w_.dbf contents of Memory Script: { # set requested point in time set until time "to_date('05/10/2022 07:15:40','dd/mm/yyyy hh24:mi:ss')"; # online the datafiles restored or switched sql clone "alter database datafile 1 online"; sql clone 'PDB9' "alter database datafile 5 online"; sql clone "alter database datafile 4 online"; sql clone 'PDB9' "alter database datafile 8 online"; sql clone "alter database datafile 13 online"; sql clone 'PDB9' "alter database datafile 21 online"; sql clone "alter database datafile 3 online"; sql clone 'PDB9' "alter database datafile 6 online"; # recover and open database read only recover clone database tablespace "SYSTEM", "PDB9":"SYSTEM", "UNDOTBS1", "PDB9":"UNDOTBS1", "UNDOTBS2", "PDB9":"PDB9_UNDOTBS1", "SYSAUX", "PDB9":"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 5 online sql statement: alter database datafile 4 online sql statement: alter database datafile 8 online sql statement: alter database datafile 13 online sql statement: alter database datafile 21 online sql statement: alter database datafile 3 online sql statement: alter database datafile 6 online Starting recover at 05-10-2022 08:50:14 using channel ORA_AUX_DISK_1 starting media recovery archived log for thread 1 with sequence 2964 is already on disk as file +DATA/VIHCDBD8/ARCHIVELOG/2022_10_05/thread_1_seq_2964.4150.1117264719 channel ORA_AUX_DISK_1: starting archived log restore to default destination channel ORA_AUX_DISK_1: restoring archived log archived log thread=1 sequence=2953 channel ORA_AUX_DISK_1: restoring archived log archived log thread=1 sequence=2954 channel ORA_AUX_DISK_1: restoring archived log archived log thread=1 sequence=2955 channel ORA_AUX_DISK_1: restoring archived log archived log thread=1 sequence=2956 channel ORA_AUX_DISK_1: restoring archived log archived log thread=1 sequence=2957 channel ORA_AUX_DISK_1: restoring archived log archived log thread=1 sequence=2958 channel ORA_AUX_DISK_1: restoring archived log archived log thread=1 sequence=2959 channel ORA_AUX_DISK_1: restoring archived log archived log thread=1 sequence=2960 channel ORA_AUX_DISK_1: restoring archived log archived log thread=1 sequence=2961 channel ORA_AUX_DISK_1: restoring archived log archived log thread=1 sequence=2962 channel ORA_AUX_DISK_1: restoring archived log archived log thread=1 sequence=2963 channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/VIHAAN8/20221005_vihcdbd8_2503_1_1117264491 channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/VIHAAN8/20221005_vihcdbd8_2503_1_1117264491 tag=TAG20221005T071451 channel ORA_AUX_DISK_1: restored backup piece 1 channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:55 archived log file name=/u01/app/oracle/aux/1_2953_1085481127.dbf thread=1 sequence=2953 archived log file name=/u01/app/oracle/aux/1_2954_1085481127.dbf thread=1 sequence=2954 archived log file name=/u01/app/oracle/aux/1_2955_1085481127.dbf thread=1 sequence=2955 archived log file name=/u01/app/oracle/aux/1_2956_1085481127.dbf thread=1 sequence=2956 archived log file name=/u01/app/oracle/aux/1_2957_1085481127.dbf thread=1 sequence=2957 archived log file name=/u01/app/oracle/aux/1_2958_1085481127.dbf thread=1 sequence=2958 archived log file name=/u01/app/oracle/aux/1_2959_1085481127.dbf thread=1 sequence=2959 archived log file name=/u01/app/oracle/aux/1_2960_1085481127.dbf thread=1 sequence=2960 archived log file name=/u01/app/oracle/aux/1_2961_1085481127.dbf thread=1 sequence=2961 archived log file name=/u01/app/oracle/aux/1_2962_1085481127.dbf thread=1 sequence=2962 archived log file name=/u01/app/oracle/aux/1_2963_1085481127.dbf thread=1 sequence=2963 archived log file name=+DATA/VIHCDBD8/ARCHIVELOG/2022_10_05/thread_1_seq_2964.4150.1117264719 thread=1 sequence=2964 media recovery complete, elapsed time: 00:06:22 Finished recover at 05-10-2022 08:57:42 sql statement: alter database open read only contents of Memory Script: { sql clone 'alter pluggable database PDB9 open read only'; } executing Memory Script sql statement: alter pluggable database PDB9 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/app/oracle/aux/VIHCDBD8/controlfile/o1_mf_kmtykgqc_.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 22749904896 bytes Fixed Size 19421832 bytes Variable Size 3019900280 bytes Database Buffers 19662897152 bytes Redo Buffers 47685632 bytes sql statement: alter system set control_files = ''/u01/app/oracle/aux/VIHCDBD8/controlfile/o1_mf_kmtykgqc_.ctl'' comment= ''RMAN set'' scope=spfile Oracle instance shut down connected to auxiliary database (not started) Oracle instance started Total System Global Area 22749904896 bytes Fixed Size 19421832 bytes Variable Size 3019900280 bytes Database Buffers 19662897152 bytes Redo Buffers 47685632 bytes sql statement: alter database mount clone database contents of Memory Script: { # set requested point in time set until time "to_date('05/10/2022 07:15:40','dd/mm/yyyy hh24:mi:ss')"; # set destinations for recovery set and auxiliary set datafiles set newname for datafile 73 to new; # restore the tablespaces in the recovery set and the auxiliary set restore clone datafile 73; switch clone datafile all; } executing Memory Script executing command: SET until clause executing command: SET NEWNAME Starting restore at 05-10-2022 08:59:39 allocated channel: ORA_AUX_DISK_1 channel ORA_AUX_DISK_1: SID=190 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 00073 to /u01/app/oracle/aux/WLAN_PITR_PDB9_VIHCDBD8/A3144982890B1187E05324F56A40A1C1/datafile/o1_mf_testx_%u_.dbf channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/VIHAAN8/20221004_vihcdbd8_2493_1_1117186896 channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/VIHAAN8/20221004_vihcdbd8_2493_1_1117186896 tag=TAG20221004T094135 channel ORA_AUX_DISK_1: restored backup piece 1 channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:25 Finished restore at 05-10-2022 09:00:07 datafile 73 switched to datafile copy input datafile copy RECID=153 STAMP=1117270808 file name=/u01/app/oracle/aux/WLAN_PITR_PDB9_VIHCDBD8/A3144982890B1187E05324F56A40A1C1/datafile/o1_mf_testx_kmtzxyso_.dbf contents of Memory Script: { # set requested point in time set until time "to_date('05/10/2022 07:15:40','dd/mm/yyyy hh24:mi:ss')"; # online the datafiles restored or switched sql clone 'PDB9' "alter database datafile 73 online"; # recover and open resetlogs recover clone database tablespace "PDB9":"TESTX", "SYSTEM", "PDB9":"SYSTEM", "UNDOTBS1", "PDB9":"UNDOTBS1", "UNDOTBS2", "PDB9":"PDB9_UNDOTBS1", "SYSAUX", "PDB9":"SYSAUX" delete archivelog; alter clone database open resetlogs; } executing Memory Script executing command: SET until clause sql statement: alter database datafile 73 online Starting recover at 05-10-2022 09:00:08 using channel ORA_AUX_DISK_1 starting media recovery archived log for thread 1 with sequence 2964 is already on disk as file +DATA/VIHCDBD8/ARCHIVELOG/2022_10_05/thread_1_seq_2964.4150.1117264719 channel ORA_AUX_DISK_1: starting archived log restore to default destination channel ORA_AUX_DISK_1: restoring archived log archived log thread=1 sequence=2953 channel ORA_AUX_DISK_1: restoring archived log archived log thread=1 sequence=2954 channel ORA_AUX_DISK_1: restoring archived log archived log thread=1 sequence=2955 channel ORA_AUX_DISK_1: restoring archived log archived log thread=1 sequence=2956 channel ORA_AUX_DISK_1: restoring archived log archived log thread=1 sequence=2957 channel ORA_AUX_DISK_1: restoring archived log archived log thread=1 sequence=2958 channel ORA_AUX_DISK_1: restoring archived log archived log thread=1 sequence=2959 channel ORA_AUX_DISK_1: restoring archived log archived log thread=1 sequence=2960 channel ORA_AUX_DISK_1: restoring archived log archived log thread=1 sequence=2961 channel ORA_AUX_DISK_1: restoring archived log archived log thread=1 sequence=2962 channel ORA_AUX_DISK_1: restoring archived log archived log thread=1 sequence=2963 channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/VIHAAN8/20221005_vihcdbd8_2503_1_1117264491 channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/VIHAAN8/20221005_vihcdbd8_2503_1_1117264491 tag=TAG20221005T071451 channel ORA_AUX_DISK_1: restored backup piece 1 channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:56 archived log file name=/u01/app/oracle/aux/1_2953_1085481127.dbf thread=1 sequence=2953 channel clone_default: deleting archived log(s) archived log file name=/u01/app/oracle/aux/1_2953_1085481127.dbf RECID=4550 STAMP=1117270831 archived log file name=/u01/app/oracle/aux/1_2954_1085481127.dbf thread=1 sequence=2954 channel clone_default: deleting archived log(s) archived log file name=/u01/app/oracle/aux/1_2954_1085481127.dbf RECID=4558 STAMP=1117270870 archived log file name=/u01/app/oracle/aux/1_2955_1085481127.dbf thread=1 sequence=2955 channel clone_default: deleting archived log(s) archived log file name=/u01/app/oracle/aux/1_2955_1085481127.dbf RECID=4555 STAMP=1117270866 archived log file name=/u01/app/oracle/aux/1_2956_1085481127.dbf thread=1 sequence=2956 channel clone_default: deleting archived log(s) archived log file name=/u01/app/oracle/aux/1_2956_1085481127.dbf RECID=4559 STAMP=1117270871 archived log file name=/u01/app/oracle/aux/1_2957_1085481127.dbf thread=1 sequence=2957 channel clone_default: deleting archived log(s) archived log file name=/u01/app/oracle/aux/1_2957_1085481127.dbf RECID=4557 STAMP=1117270869 archived log file name=/u01/app/oracle/aux/1_2958_1085481127.dbf thread=1 sequence=2958 channel clone_default: deleting archived log(s) archived log file name=/u01/app/oracle/aux/1_2958_1085481127.dbf RECID=4556 STAMP=1117270869 archived log file name=/u01/app/oracle/aux/1_2959_1085481127.dbf thread=1 sequence=2959 channel clone_default: deleting archived log(s) archived log file name=/u01/app/oracle/aux/1_2959_1085481127.dbf RECID=4549 STAMP=1117270828 archived log file name=/u01/app/oracle/aux/1_2960_1085481127.dbf thread=1 sequence=2960 channel clone_default: deleting archived log(s) archived log file name=/u01/app/oracle/aux/1_2960_1085481127.dbf RECID=4554 STAMP=1117270865 archived log file name=/u01/app/oracle/aux/1_2961_1085481127.dbf thread=1 sequence=2961 channel clone_default: deleting archived log(s) archived log file name=/u01/app/oracle/aux/1_2961_1085481127.dbf RECID=4551 STAMP=1117270838 archived log file name=/u01/app/oracle/aux/1_2962_1085481127.dbf thread=1 sequence=2962 channel clone_default: deleting archived log(s) archived log file name=/u01/app/oracle/aux/1_2962_1085481127.dbf RECID=4553 STAMP=1117270839 archived log file name=/u01/app/oracle/aux/1_2963_1085481127.dbf thread=1 sequence=2963 channel clone_default: deleting archived log(s) archived log file name=/u01/app/oracle/aux/1_2963_1085481127.dbf RECID=4552 STAMP=1117270838 archived log file name=+DATA/VIHCDBD8/ARCHIVELOG/2022_10_05/thread_1_seq_2964.4150.1117264719 thread=1 sequence=2964 media recovery complete, elapsed time: 00:00:05 Finished recover at 05-10-2022 09:01:23 database opened contents of Memory Script: { sql clone 'alter pluggable database PDB9 open'; } executing Memory Script sql statement: alter pluggable database PDB9 open contents of Memory Script: { # create directory for datapump import sql 'PDB9' "create or replace directory TSPITR_DIROBJ_DPDIR as '' /u01/app/oracle/aux''"; # create directory for datapump export sql clone 'PDB9' "create or replace directory TSPITR_DIROBJ_DPDIR as '' /u01/app/oracle/aux''"; } executing Memory Script sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/u01/app/oracle/aux'' sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/u01/app/oracle/aux'' Performing export of tables... EXPDP> Starting "SYS"."TSPITR_EXP_wlan_Aguy": 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 "VIHAANX"."TEST_RESTORE05" 5.062 KB 1 rows EXPDP> Master table "SYS"."TSPITR_EXP_wlan_Aguy" successfully loaded/unloaded EXPDP> ****************************************************************************** EXPDP> Dump file set for SYS.TSPITR_EXP_wlan_Aguy is: EXPDP> /u01/app/oracle/aux/tspitr_wlan_64020.dmp EXPDP> Job "SYS"."TSPITR_EXP_wlan_Aguy" successfully completed at Wed Oct 5 09:05:47 2022 elapsed 0 00:00:45 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_wlan_viim" successfully loaded/unloaded IMPDP> Starting "SYS"."TSPITR_IMP_wlan_viim": IMPDP> Processing object type TABLE_EXPORT/TABLE/TABLE IMPDP> Processing object type TABLE_EXPORT/TABLE/TABLE_DATA IMPDP> . . imported "VIHAANX"."TEST_RESTORE55" 5.062 KB 1 rows IMPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS IMPDP> Job "SYS"."TSPITR_IMP_wlan_viim" successfully completed at Wed Oct 5 09:06:08 2022 elapsed 0 00:00:11 Import completed Removing automatic instance Automatic instance removed auxiliary instance file /u01/app/oracle/aux/VIHCDBD8/A3144982890B1187E05324F56A40A1C1/datafile/o1_mf_temp_kmtztktj_.tmp deleted auxiliary instance file /u01/app/oracle/aux/VIHCDBD8/datafile/o1_mf_temp_kmtzt8dm_.tmp deleted auxiliary instance file /u01/app/oracle/aux/VIHCDBD8/datafile/o1_mf_temp_tes_kmtzt8np_.tmp deleted auxiliary instance file /u01/app/oracle/aux/WLAN_PITR_PDB9_VIHCDBD8/onlinelog/o1_mf_6_kmv0143r_.log deleted auxiliary instance file /u01/app/oracle/aux/WLAN_PITR_PDB9_VIHCDBD8/onlinelog/o1_mf_5_kmv01430_.log deleted auxiliary instance file /u01/app/oracle/aux/WLAN_PITR_PDB9_VIHCDBD8/onlinelog/o1_mf_4_kmv0142y_.log deleted auxiliary instance file /u01/app/oracle/aux/WLAN_PITR_PDB9_VIHCDBD8/onlinelog/o1_mf_1_kmv01422_.log deleted auxiliary instance file /u01/app/oracle/aux/WLAN_PITR_PDB9_VIHCDBD8/A3144982890B1187E05324F56A40A1C1/datafile/o1_mf_testx_kmtzxyso_.dbf deleted auxiliary instance file /u01/app/oracle/aux/VIHCDBD8/A3144982890B1187E05324F56A40A1C1/datafile/o1_mf_sysaux_kmtykz6w_.dbf deleted auxiliary instance file /u01/app/oracle/aux/VIHCDBD8/datafile/o1_mf_sysaux_kmtz72ck_.dbf deleted auxiliary instance file /u01/app/oracle/aux/VIHCDBD8/A3144982890B1187E05324F56A40A1C1/datafile/o1_mf_pdb9_und_kmtykz4l_.dbf deleted auxiliary instance file /u01/app/oracle/aux/VIHCDBD8/datafile/o1_mf_undotbs2_kmtz72gq_.dbf deleted auxiliary instance file /u01/app/oracle/aux/VIHCDBD8/A3144982890B1187E05324F56A40A1C1/datafile/o1_mf_undotbs1_kmtykz5y_.dbf deleted auxiliary instance file /u01/app/oracle/aux/VIHCDBD8/datafile/o1_mf_undotbs1_kmtz72jl_.dbf deleted auxiliary instance file /u01/app/oracle/aux/VIHCDBD8/A3144982890B1187E05324F56A40A1C1/datafile/o1_mf_system_kmtykz50_.dbf deleted auxiliary instance file /u01/app/oracle/aux/VIHCDBD8/datafile/o1_mf_system_kmtz72fj_.dbf deleted auxiliary instance file /u01/app/oracle/aux/VIHCDBD8/controlfile/o1_mf_kmtykgqc_.ctl deleted auxiliary instance file tspitr_wlan_64020.dmp deleted Finished recover at 05-10-2022 09:06:11 ==== alter session set container=PDB9 ; Session altered. select count(*) from VIHAANX.TEST_RESTORE55 ; COUNT(*) ---------- 1
Wednesday, 5 October 2022
How to Recover a Table from Oracle 12c via RMAN Backup ?
How to Recover a Table from an Oracle 12c RMAN Backup
Monday, 19 September 2022
Oracle >18c Rman Duplication of a PDB To another CDB
Oracle >18c Rman Duplication of a PDB To another CDB ..
Oracle >18c Rman Duplication of a PDB To another CDB
PDB Duplication
source = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.211)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (sid = orclcdb) ) )
dest = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.209)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (sid = orclcdb) ) )
#rman target sys/oracle@source auxiliary sys/oracle@dest
tnsping dest
TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 19-SEP-2022 03:50:51
Copyright (c) 1997, 2019, Oracle. All rights reserved.
Used parameter files:
/u01/app/oracle/product/version/db_1/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.209)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (sid = orclcdb)))
OK (10 msec)
[oracle@Vihaan admin]$ tnsping source
TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 19-SEP-2022 03:50:59
Copyright (c) 1997, 2019, Oracle. All rights reserved.
Used parameter files:
/u01/app/oracle/product/version/db_1/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.211)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (sid = orclcdb)))
OK (340 msec)
===========================================
check below
sqlplus 'sys/oracle@source as sysdba'
sqlplus 'sys/oracle@dest as sysdba'
on 192.168.1.211
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 ORCL READ WRITE NO
4 ORCL1 READ WRITE NO >>>>> create duplicate to ORCLD on 192.168.1.209
on 192.168.1.209 i.e dest
alter system set remote_recovery_file_dest='/u01/app/oracle/Remote_Recovery' scope=both;
Create directory for new PDB:
mkdir -p /u01/app/oracle/oradata/ORCLCDB/orcld
rman
Recovery Manager: Release 19.0.0.0.0 - Production on Mon Sep 19 04:01:27 2022
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
RMAN> connect target sys/oracle@source
connected to target database: ORCLCDB (DBID=2780785463)
RMAN> connect auxiliary sys/oracle@dest
connected to auxiliary database: ORCLCDB (DBID=2780785463)
RMAN>
RMAN> connect target sys/oracle@source
RMAN> connect auxiliary sys/oracle@dest
DUPLICATE PLUGGABLE DATABASE ORCL1 as ORCLD TO orclcdb DB_FILE_NAME_CONVERT('orcl1','orcld') FROM ACTIVE DATABASE SECTION SIZE 10M;
[Instance i.e dest]
DUPLICATE PLUGGABLE DATABASE ORCL1 as ORCLD TO orclcdb DB_FILE_NAME_CONVERT('orcl1','orcld') FROM ACTIVE DATABASE SECTION SIZE 10M;
RMAN>
RMAN>
Starting Duplicate PDB at 19-SEP-22
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=22 device type=DISK
current log archived
duplicating Online logs to Oracle Managed File (OMF) location
contents of Memory Script:
{
set newname for datafile 16 to
"/u01/app/oracle/oradata/ORCLCDB/orcld/system01.dbf";
set newname for datafile 17 to
"/u01/app/oracle/oradata/ORCLCDB/orcld/sysaux01.dbf";
set newname for datafile 18 to
"/u01/app/oracle/oradata/ORCLCDB/orcld/undotbs01.dbf";
set newname for datafile 19 to
"/u01/app/oracle/oradata/ORCLCDB/orcld/orcld.dbf";
restore
from nonsparse section size
10 m clone foreign pluggable database
"ORCL1"
from service 'source' ;
}
executing Memory Script
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting restore at 19-SEP-22
using channel ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service source
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring section 1 of 27
channel ORA_AUX_DISK_1: restoring foreign file 16 to /u01/app/oracle/oradata/ORCLCDB/orcld/system01.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:07
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service source
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring section 2 of 27
channel ORA_AUX_DISK_1: restoring foreign file 16 to /u01/app/oracle/oradata/ORCLCDB/orcld/system01.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service source
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring section 3 of 27
channel ORA_AUX_DISK_1: restoring foreign file 16 to /u01/app/oracle/oradata/ORCLCDB/orcld/system01.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:08
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service source
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring section 4 of 27
channel ORA_AUX_DISK_1: restoring foreign file 16 to /u01/app/oracle/oradata/ORCLCDB/orcld/system01.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:07
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service source
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring section 5 of 27
.
.
.
.
channel ORA_AUX_DISK_1: restoring section 21 of 25
channel ORA_AUX_DISK_1: restoring foreign file 19 to /u01/app/oracle/oradata/ORCLCDB/orcld/orcld.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:02
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service source
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring section 22 of 25
channel ORA_AUX_DISK_1: restoring foreign file 19 to /u01/app/oracle/oradata/ORCLCDB/orcld/orcld.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service source
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring section 23 of 25
channel ORA_AUX_DISK_1: restoring foreign file 19 to /u01/app/oracle/oradata/ORCLCDB/orcld/orcld.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service source
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring section 24 of 25
channel ORA_AUX_DISK_1: restoring foreign file 19 to /u01/app/oracle/oradata/ORCLCDB/orcld/orcld.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service source
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring section 25 of 25
channel ORA_AUX_DISK_1: restoring foreign file 19 to /u01/app/oracle/oradata/ORCLCDB/orcld/orcld.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:02
Finished restore at 19-SEP-22
current log archived
contents of Memory Script:
{
set archivelog destination to '/u01/app/oracle/Remote_Recovery';
restore clone force from service 'source'
foreign archivelog from scn 3145371;
}
executing Memory Script
executing command: SET ARCHIVELOG DESTINATION
Starting restore at 19-SEP-22
using channel ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: starting archived log restore to user-specified destination
archived log destination=/u01/app/oracle/Remote_Recovery
channel ORA_AUX_DISK_1: using network backup set from service source
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=26
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:02
channel ORA_AUX_DISK_1: starting archived log restore to user-specified destination
archived log destination=/u01/app/oracle/Remote_Recovery
channel ORA_AUX_DISK_1: using network backup set from service source
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=27
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:04
Finished restore at 19-SEP-22
Performing import of metadata...
Finished Duplicate PDB at 19-SEP-22
===============================================================
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 ORCL READ WRITE NO
4 ORCLD READ WRITE NO
set linesize 300
col NAME for a70
select con_id,name from v$datafile where con_id=4;
CON_ID NAME
---------- ----------------------------------------------------------------------
4 /u01/app/oracle/oradata/ORCLCDB/orcld/system01.dbf
4 /u01/app/oracle/oradata/ORCLCDB/orcld/sysaux01.dbf
4 /u01/app/oracle/oradata/ORCLCDB/orcld/undotbs01.dbf
4 /u01/app/oracle/oradata/ORCLCDB/orcld/orcld.dbf
set linesize 300
COLUMN time FORMAT A30
COLUMN name FORMAT A30
COLUMN cause FORMAT A30
COLUMN message FORMAT A30
col ACTION for a70
select name, cause, type, status,action,message,time from pdb_plug_in_violations;
NAME CAUSE TYPE STATUS ACTION MESSAGE TIME
------------------------------ ------------------------------ --------- --------- ---------------------------------------------------------------------- ------------------------------ ------------------------------
PDB$SEED SQL Patch ERROR RESOLVED Call datapatch to install in the PDB or the CDB '19.3.0.0.0 Release_Update 190 31-MAY-19 03.17.50.323190 PM
4101227' is installed in the C
DB but no release updates are
installed in the PDB
Friday, 16 September 2022
RMAN JOBs in Detail and monitoring
RMAN JOBs in Detail and monitoring
define 1='14-09-20 15:19:57' --- Change based on your requirement
-- open_time >sysdate -1 --- current status
-- ''''||s.sid ||','|| s.serial#||',@'||s.inst_id ||'''' kill
alter session set nls_date_format='dd-mm-yy hh24:mi:ss';
set lines 1500
set pages 100
col CLI_INFO format a10
col spid format a5
col ch format a20
col seconds format 999999.99
col filename format a70
col bfc format 9
col "% Complete" format 999.99
col event format a40
col kill for a15
set numwidth 10
select sysdate from dual;
REM gv$session_longops (channel level)
prompt
prompt Channel progress - gv$session_longops:
prompt
select ''''||s.sid ||','|| s.serial#||',@'||s.inst_id ||'''' kill, CLIENT_INFO ch, context, sofar, totalwork, round(sofar/totalwork*100,2) "% Complete"
FROM gv$session_longops o, gv$session s
WHERE opname LIKE 'RMAN%'
AND opname NOT LIKE '%aggregate%'
AND o.sid=s.sid
AND totalwork != 0
AND sofar <> totalwork;
REM Check wait events (RMAN sessions) - this is for CURRENT waits only
REM use the following for 11G+
prompt
prompt Session progess - CURRENT wait events and time in wait so far:
prompt
select ''''||sid ||','|| serial#||',@'||inst_id ||'''' kill, CLIENT_INFO ch, seq#, event, state, wait_time_micro/1000000 seconds from gv$session
where program like '%rman%'
and wait_time = 0
and not action is null;
REM gv$backup_async_io
prompt
prompt Disk (file and backuppiece) progress - includes tape backuppiece
prompt if backup_tape_io_slaves=TRUE:
prompt
select ''''||s.sid ||','|| s.serial#||',@'||s.inst_id ||'''' kill, CLIENT_INFO Ch, a.STATUS,
open_time, round(BYTES/1024/1024,2) "SOFAR Mb" , round(total_bytes/1024/1024,2)
TotMb, io_count,
round(BYTES/TOTAL_BYTES*100,2) "% Complete" , a.type, filename
from gv$backup_async_io a, gv$session s
where not a.STATUS in ('UNKNOWN')
and a.sid=s.sid
--and open_time > to_date('&1', 'dd-mon-rr hh24:mi:ss')
and open_time >sysdate -1
order by 2,7;
REM gv$backup_sync_io
prompt
prompt Tape backuppiece progress (only if backup_tape_io_slaves=FALSE):
prompt
col FILENAME for a50
set line 400 pagesize 100
select ''''||s.sid ||','|| s.serial#||',@'||s.inst_id ||'''' kill, CLIENT_INFO Ch, filename, a.type, a.status, buffer_size bsz, buffer_count bfc,
open_time open, io_count
from gv$backup_sync_io a, gv$session s
where
a.sid=s.sid
--and open_time > to_date('&1', 'dd-mon-rr hh24:mi:ss')
and open_time >sysdate -1
;
Wednesday, 14 September 2022
dbms_xplan.display_cursor with gather_plan_statistics hint
dbms_xplan.display_cursor with gather_plan_statistics hint
gather_plan_statistics hint-- Table Create ---- create table teststats as select * from all_objects; select /*+ gather_plan_statistics ANUJTEST */ count(*) from teststats where object_id between 100 and 200; col sql_text for a100 wrap select inst_id,sql_id,child_number,sql_fulltext FROM gv$sql WHERE 1=1 and sql_text LIKE '%ANUJTEST%' and SQL_TEXT not like '%from gv$sql%'; INST_ID SQL_ID CHILD_NUMBER SQL_FULLTEXT ---------- ------------- ------------ ---------------------------------------------------------------------------------------------------- 1 5jv62xqkdd1tz 0 SELECT x.* FROM v$sql s, TABLE(DBMS_XPLAN.DISPLAY_CURSOR(s.sql_id, s.child_num 1 c69d8vn5pdn8t 0 SELECT x.* FROM v$sql s, TABLE(DBMS_XPLAN.DISPLAY_CURSOR(s.sql_id, s.ch 1 6wbxyxvnhqfsh 0 SELECT x.* FROM v$sql s, TABLE(DBMS_XPLAN.DISPLAY_CURSOR(s.sql_id, s.ch 1 2197z7183vsgc 0 select /*+ gather_plan_statistics ANUJTEST */ count(*) from teststats where obje <<<<<<< this one !!! SELECT x.* FROM gv$sql s, TABLE(DBMS_XPLAN.DISPLAY_CURSOR(s.sql_id, s.child_number)) x WHERE 1=1 and s.sql_text LIKE '%ANUJTEST%' and SQL_TEXT not like '%from gv$sql'; set line 300 pagesize 300 col PLAN_TABLE_OUTPUT for a150 SELECT * FROM TABLE(dbms_xplan.display_cursor(sql_id => '2197z7183vsgc',cursor_child_no => 0, FORMAT => 'ADVANCED +ROWS +BYTES +COST +PARALLEL +PARTITION +IOSTATS +MEMSTATS +ALIAS +PEEKED_BINDS +OUTLINE +PREDICATE +PROJECTION +REMOTE +NOTE')); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------ SQL_ID 2197z7183vsgc, child number 0 ------------------------------------- select /*+ gather_plan_statistics ANUJTEST */ count(*) from teststats where object_id between 100 and 200 Plan hash value: 1317213715 ----------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | Reads | ----------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | | 392 (100)| | 1 |00:00:00.11 | 1443 | 1111 | | 1 | SORT AGGREGATE | | 1 | 1 | 13 | | | 1 |00:00:00.11 | 1443 | 1111 | |* 2 | TABLE ACCESS FULL| TESTSTATS | 1 | 16 | 208 | 392 (1)| 00:00:01 | 96 |00:00:00.01 | 1443 | 1111 | ----------------------------------------------------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$1 2 - SEL$1 / TESTSTATS@SEL$1 Outline Data ------------- /*+ BEGIN_OUTLINE_DATA IGNORE_OPTIM_EMBEDDED_HINTS OPTIMIZER_FEATURES_ENABLE('12.2.0.1') DB_VERSION('12.2.0.1') ALL_ROWS OUTLINE_LEAF(@"SEL$1") FULL(@"SEL$1" "TESTSTATS"@"SEL$1") END_OUTLINE_DATA */ Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter(("OBJECT_ID">=100 AND "OBJECT_ID"<=200)) Column Projection Information (identified by operation id): ----------------------------------------------------------- 1 - (#keys=0) COUNT(*)[22] 2 - (rowset=1019) Note ----- - dynamic statistics used: dynamic sampling (level=2) 50 rows selected. =========================================================================================== or set linesize 300 pagesize 300 alter session set statistics_level='ALL'; col PLAN_TABLE_OUTPUT for a150 select * from table(dbms_xplan.display_cursor(sql_id => '2197z7183vsgc', cursor_child_no => 0,FORMAT =>'allstats')); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------ SQL_ID 2197z7183vsgc, child number 0 ------------------------------------- select /*+ gather_plan_statistics ANUJTEST */ count(*) from teststats where object_id between 100 and 200 Plan hash value: 1317213715 --------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | --------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.11 | 1443 | 1111 | | 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.11 | 1443 | 1111 | |* 2 | TABLE ACCESS FULL| TESTSTATS | 1 | 16 | 96 |00:00:00.01 | 1443 | 1111 | --------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter(("OBJECT_ID">=100 AND "OBJECT_ID"<=200)) Note ----- - dynamic statistics used: dynamic sampling (level=2) 24 rows selected.
====================
select inst_id,sql_id,child_number from gv$sql
WHERE sql_id = '&sql_id'
;
INST_ID SQL_ID CHILD_NUMBER
---------- ------------- ------------
1 74b220rfs400g 0
SELECT
t.plan_table_output
FROM
gv$sql v,
TABLE(DBMS_XPLAN.DISPLAY('gv$sql_plan_statistics_all'
, NULL
, 'ADVANCED ALLSTATS LAST +COST +PARALLEL +PARTITION +IOSTATS'
, 'inst_id = '||v.inst_id||'
AND sql_id = '''||v.sql_id||''' AND child_number = '||v.child_number )
) t
WHERE
v.sql_id = '&sql_id'
AND v.loaded_versions > 0
;
SELECT * FROM TABLE(dbms_xplan.display_cursor(sql_id => '&&sql_id', cursor_child_no => 0, FORMAT => 'TYPICAL -ROWS -BYTES +COST +PARALLEL +PARTITION +IOSTATS +MEMSTATS +ALIAS +PEEKED_BINDS +OUTLINE +PREDICATE -PROJECTION +REMOTE +NOTE'));
PRO
PRO GV$SQL_PLAN_STATISTICS_ALL LAST (ordered by inst_id and child_number)
PRO ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
COL inst_child FOR A21
BREAK ON inst_child SKIP 2;
SET PAGES 0;
WITH v AS (
SELECT /*+ MATERIALIZE */
DISTINCT sql_id, inst_id, child_number
FROM gv$sql
WHERE sql_id = '&&sql_id.'
AND loaded_versions > 0
ORDER BY 1, 2, 3 )
SELECT /*+ ORDERED USE_NL(t) */
RPAD('Inst: '||v.inst_id, 9)||' '||RPAD('Child: '||v.child_number, 11) inst_child, t.plan_table_output FROM v, TABLE(DBMS_XPLAN.DISPLAY('gv$sql_plan_statistics_all', NULL, 'ADVANCED ALLSTATS LAST',
'inst_id = '||v.inst_id||' AND sql_id = '''||v.sql_id||''' AND child_number = '||v.child_number)) t
/
define sql_id='74b220rfs400g'
set linesize 300 pagesize 300
col PLAN_TABLE_OUTPUT for a180
COL inst_child FOR A21
BREAK ON inst_child SKIP 2
SELECT RPAD('Inst: '||v.inst_id, 9)||' '||RPAD('Child: '||v.child_number, 11) inst_child, t.plan_table_output
FROM gv$sql v,
TABLE(DBMS_XPLAN.DISPLAY('gv$sql_plan_statistics_all', NULL, 'ADVANCED ALLSTATS LAST', 'inst_id = '||v.inst_id||' AND sql_id = '''||v.sql_id||''' AND child_number = '||v.child_number)) t
WHERE v.sql_id = '&&sql_id.'
AND v.loaded_versions > 0
;
set linesize 300 pagesize 300
WITH v AS (
SELECT /*+ MATERIALIZE */
DISTINCT sql_id, inst_id, child_number
FROM gv$sql
WHERE sql_id = '&sql_id' and child_number = '&child_number'
AND loaded_versions > 0
ORDER BY 1, 2, 3 )
,u
as (
SELECT /*+ ORDERED USE_NL(t) */
rank() over(order by v.inst_id) rn,
RPAD('Inst: '||v.inst_id, 9)||' '||RPAD('Child: '||v.child_number, 11) inst_child,
t.plan_table_output
FROM v, TABLE(DBMS_XPLAN.DISPLAY('gv$sql_plan_statistics_all', NULL, 'ALL ALLSTATS LAST -PROJECTION', --'ALLSTATS LAST alias partition cost',
'inst_id = '||v.inst_id||' AND sql_id = '''||v.sql_id||''' AND child_number = '||v.child_number)) t
)
select u.plan_table_output
from u
where
rn = 1 -- to avoid multiple occurences per instance
set linesize 300 pagesize 300
WITH v AS (
SELECT /*+ MATERIALIZE */
DISTINCT sql_id, inst_id, child_number
FROM gv$sql
WHERE sql_id = '&sql_id' and child_number = '&child_number'
AND loaded_versions > 0
ORDER BY 1, 2, 3 )
,u
as (
SELECT /*+ ORDERED USE_NL(t) */
rank() over(order by v.inst_id) rn,
RPAD('Inst: '||v.inst_id, 9)||' '||RPAD('Child: '||v.child_number, 11) inst_child,
t.plan_table_output
FROM v, TABLE(DBMS_XPLAN.DISPLAY('gv$sql_plan_statistics_all', NULL, 'ADVANCED ALLSTATS LAST',
'inst_id = '||v.inst_id||' AND sql_id = '''||v.sql_id||''' AND child_number = '||v.child_number)) t
)
select u.plan_table_output
from u
where
rn = 1 -- to avoid multiple occurences per instance
/
set linesize 500 pagesize 500
select * from table( dbms_xplan.display_awr('&sql_id', plan_hash_value => '&plan_hash_value', format => 'ADVANCED ALLSTATS LAST') ) --'ADVANCED +PEEKED_BINDS +ALLSTATS LAST +MEMSTATS LAST partition cost') )
/
Tuesday, 13 September 2022
Recover Pluggable Database
Recover Pluggable Database ..
Recover Pluggable Database Performing Point-In-Time Recovery for a Pluggable Database Restoring and recovering pluggable database using point-in-time recovery
on
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
full_backup.sh
export DATE=$(date +%y-%m-%d_%H%M%S)
rman target / log=/u01/app/Rman/proddb_${DATE}.log << EOF
run
{
allocate channel ch1 device type disk format '/u01/app/Rman/proddb_full_bkp_%u';
allocate channel ch2 device type disk format '/u01/app/Rman/proddb_full_bkp_%u';
crosscheck backup;
delete noprompt obsolete;
backup database;
backup archivelog all delete input;
release channel ch1;
release channel ch2;
}
EOF
=====
[oracle@DESKTOP-BAS028D ~]$ export NLS_DATE_FORMAT='DD/MM/YYYY HH24:MI:SS'
[oracle@DESKTOP-BAS028D ~]$ rman target /
Recovery Manager: Release 19.0.0.0.0 - Production on Tue Sep 13 14:36:09 2022
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCLCDB (DBID=2780785463)
RMAN> LIST BACKUP OF PLUGGABLE DATABASE orcl;
using target database control file instead of recovery catalog
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
2 Full 1008.70M DISK 00:00:23 13/09/2022 13:27:54
BP Key: 2 Status: AVAILABLE Compressed: NO Tag: TAG20220913T132730
Piece Name: /u01/app/Rman/proddb_full_bkp_0117k7q3
List of Datafiles in backup set 2
Container ID: 3, PDB Name: ORCL
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- ------------------- ----------- ------ ----
10 Full 2826715 13/09/2022 13:27:31 NO /u01/app/oracle/oradata/ORCLCDB/orcl/sysaux01.dbf
13 Full 2826715 13/09/2022 13:27:31 NO /u01/app/oracle/oradata/ORCLCDB/orcl/APEX_1291597703607401.dbf
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
3 Full 470.40M DISK 00:00:12 13/09/2022 13:28:09
BP Key: 3 Status: AVAILABLE Compressed: NO Tag: TAG20220913T132730
Piece Name: /u01/app/Rman/proddb_full_bkp_0317k7qt
List of Datafiles in backup set 3
Container ID: 3, PDB Name: ORCL
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- ------------------- ----------- ------ ----
9 Full 2826726 13/09/2022 13:27:57 NO /u01/app/oracle/oradata/ORCLCDB/orcl/system01.dbf
12 Full 2826726 13/09/2022 13:27:57 NO /u01/app/oracle/oradata/ORCLCDB/orcl/users01.dbf
15 Full 2826726 13/09/2022 13:27:57 NO /u01/app/oracle/oradata/ORCLCDB/orcl/undotbs2.dbf
====before start check below
If the FRA is not configured, then AUXILIARY DESTINATION must be specified ....
show parameter DB_RECOVERY
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string /u01/app/oracle/fast_recovery_
area
db_recovery_file_dest_size big integer 12918M
https://docs.oracle.com/en/database/oracle/oracle-database/19/ntdbi/about-fast-recovery-area-and-fast-recovery-area-disk-group.html
RUN {
ALTER PLUGGABLE DATABASE orcl CLOSE;
SET UNTIL TIME "TO_DATE('13-SEP-2022 13:27:57','DD-MON-YYYY HH24:MI:SS')";
RESTORE PLUGGABLE DATABASE orcl;
RECOVER PLUGGABLE DATABASE orcl;
ALTER PLUGGABLE DATABASE orcl OPEN RESETLOGS;
}=====================
RMAN> RESTORE pluggable DATABASE orcl UNTIL TIME "TO_DATE('13.09.2022 13:27:57','DD.MM.YYYY HH24:MI:SS')" PREVIEW;
Starting restore at 13/09/2022 13:53:00
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=7 device type=DISK
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
3 Full 470.40M DISK 00:00:12 13/09/2022 13:28:09
BP Key: 3 Status: AVAILABLE Compressed: NO Tag: TAG20220913T132730
Piece Name: /u01/app/Rman/proddb_full_bkp_0317k7qt
List of Datafiles in backup set 3
Container ID: 3, PDB Name: ORCL
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- ------------------- ----------- ------ ----
9 Full 2826726 13/09/2022 13:27:57 NO /u01/app/oracle/oradata/ORCLCDB/orcl/system01.dbf
12 Full 2826726 13/09/2022 13:27:57 NO /u01/app/oracle/oradata/ORCLCDB/orcl/users01.dbf
15 Full 2826726 13/09/2022 13:27:57 NO /u01/app/oracle/oradata/ORCLCDB/orcl/undotbs2.dbf
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
2 Full 1008.70M DISK 00:00:23 13/09/2022 13:27:54
BP Key: 2 Status: AVAILABLE Compressed: NO Tag: TAG20220913T132730
Piece Name: /u01/app/Rman/proddb_full_bkp_0117k7q3
List of Datafiles in backup set 2
Container ID: 3, PDB Name: ORCL
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- ------------------- ----------- ------ ----
10 Full 2826715 13/09/2022 13:27:31 NO /u01/app/oracle/oradata/ORCLCDB/orcl/sysaux01.dbf
13 Full 2826715 13/09/2022 13:27:31 NO /u01/app/oracle/oradata/ORCLCDB/orcl/APEX_1291597703607401.dbf
using channel ORA_DISK_1
List of Backup Sets
===================
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ -------------------
7 451.00K DISK 00:00:00 13/09/2022 13:28:26
BP Key: 7 Status: AVAILABLE Compressed: NO Tag: TAG20220913T132825
Piece Name: /u01/app/Rman/proddb_full_bkp_0817k7rq
List of Archived Logs in backup set 7
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- ------------------- ---------- ---------
1 24 2825244 13/09/2022 13:06:57 2826756 13/09/2022 13:28:24
recovery will be done up to SCN 2826726
Media recovery start SCN is 2826715
Recovery must be done beyond SCN 2826726 to clear datafile fuzziness <<<<<<<<<<<<<+1 i.e. 2826727 scn no
Finished restore at 13/09/2022 13:53:04
RMAN>
RUN {
SET UNTIL scn 2826727;
RESTORE PLUGGABLE DATABASE orcl;
RECOVER PLUGGABLE DATABASE orcl;
ALTER PLUGGABLE DATABASE orcl OPEN RESETLOGS;
}
RMAN> 2> 3> 4> 5> 6>
executing command: SET until clause
Starting restore at 13/09/2022 14:48:35
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=13 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 00010 to /u01/app/oracle/oradata/ORCLCDB/orcl/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00013 to /u01/app/oracle/oradata/ORCLCDB/orcl/APEX_1291597703607401.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/Rman/proddb_full_bkp_0117k7q3
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 09/13/2022 14:48:39
ORA-19870: error while restoring backup piece /u01/app/Rman/proddb_full_bkp_0117k7q3
ORA-19573: cannot obtain exclusive enqueue for datafile 10 <<<<<<<<<<<<<<<<<<<<<<<
RMAN>
RMAN>
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 ORCL READ WRITE NO SQL> alter session set container=orcl; Session altered. SQL> shutdown immediate ; Pluggable Database closed.
**********************************************
started again after shutdown the pluggable database !!!!!!! RMAN> RUN { SET UNTIL scn 2826727; RESTORE PLUGGABLE DATABASE orcl; RECOVER PLUGGABLE DATABASE orcl; ALTER PLUGGABLE DATABASE orcl OPEN RESETLOGS; } RMAN> executing command: SET until clause Starting restore at 13/09/2022 14:55:37 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 00010 to /u01/app/oracle/oradata/ORCLCDB/orcl/sysaux01.dbf channel ORA_DISK_1: restoring datafile 00013 to /u01/app/oracle/oradata/ORCLCDB/orcl/APEX_1291597703607401.dbf channel ORA_DISK_1: reading from backup piece /u01/app/Rman/proddb_full_bkp_0117k7q3 channel ORA_DISK_1: piece handle=/u01/app/Rman/proddb_full_bkp_0117k7q3 tag=TAG20220913T132730 channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00:00:15 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 00009 to /u01/app/oracle/oradata/ORCLCDB/orcl/system01.dbf channel ORA_DISK_1: restoring datafile 00012 to /u01/app/oracle/oradata/ORCLCDB/orcl/users01.dbf channel ORA_DISK_1: restoring datafile 00015 to /u01/app/oracle/oradata/ORCLCDB/orcl/undotbs2.dbf channel ORA_DISK_1: reading from backup piece /u01/app/Rman/proddb_full_bkp_0317k7qt channel ORA_DISK_1: piece handle=/u01/app/Rman/proddb_full_bkp_0317k7qt tag=TAG20220913T132730 channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00:00:07 Finished restore at 13/09/2022 14:56:00 Starting recover at 13/09/2022 14:56:01 using channel ORA_DISK_1 starting media recovery media recovery complete, elapsed time: 00:00:01 Finished recover at 13/09/2022 14:56:03 Statement processed =================================================================================== SQL> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 ORCL READ WRITE NO
Thursday, 1 September 2022
ORA-65049: Creation of local user or role is not allowed in this container.
ORA-65049: Creation of local user or role is not allowed in this container
grant dba to test identified by teast123 * ERROR at line 1: ORA-65049: Creation of local user or role is not allowed in this container. ################################################################################################ alter session set "_ORACLE_SCRIPT"=true; grant dba to test identified by teast123; Grant succeeded. <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< Created col USERNAME for a20 set linesize 300 pagesize 300 select USERNAME,COMMON from dba_users ; USERNAME COM ------------------------------ --- TEST YES Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.14.0.0.0 SQL> create user c##test identified by test123 container=all; otherwise create c## user User created. USERNAME COM -------------------- --- C##TEST YES
Wednesday, 31 August 2022
How to purge a Sql from shared pool?
How to purge a Sql from shared pool ?
define sql_id='62kmmu728kgu6' ---- Change the sql Id!!!! select sql_id,ADDRESS, HASH_VALUE from gV$SQLAREA where SQL_Id='&sql_id'; SQL_ID ADDRESS HASH_VALUE ------------- ---------------- ---------- 62kmmu728kgu6 00000000CE4417E0 3297328966
for purge command!!!!! set linesize 300 col pool_purge for a100 select 'exec DBMS_SHARED_POOL.PURGE ('''||ADDRESS||','|| HASH_VALUE||''''||','||'''C'''|| ');' pool_purge ,SQL_Id from gV$SQLAREA where SQL_Id='&sql_id'; POOL_PURGE SQL_ID ---------------------------------------------------------------------------------------------------- ------------- exec DBMS_SHARED_POOL.PURGE ('00000000CE4417E0,3297328966','C'); 62kmmu728kgu6 exec DBMS_SHARED_POOL.PURGE ('00000000CCB180F0,3297328966','C'); 62kmmu728kgu6 SQL> exec DBMS_SHARED_POOL.PURGE ('00000000CE4417E0,3297328966','C'); PL/SQL procedure successfully completed. select sql_id,HASH_VALUE, PLAN_HASH_VALUE, CHILD_ADDRESS, CHILD_NUMBER from gv$sql_plan where sql_id='&sql_id';
select sql_id,address,hash_value,users_executing,sql_text from gv$sqlarea where sql_id='cbr6yy2vwr6s0'; exec for c in (select address,hash_value,users_executing,sql_text from v$sqlarea where sql_id='cbr6yy2vwr6s0') loop sys.dbms_shared_pool.purge(c.address||','||c.hash_value,'...'); end loop; select sql_id,address,hash_value,users_executing,sql_text from gv$sqlarea where sql_id='cbr6yy2vwr6s0';
Subscribe to:
Posts (Atom)
