Oracle How to Restore Of Single PDB via RMAN

		 
Oracle How to Restore Of Single PDB via RMAN 
Rman to Restore Of Single PDB in Multitenant	



Aim to restore only ORCL database ONLY via rman !!!!
		
SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 ORCL                           READ WRITE NO   <<<< only this database !!!
         4 ORCLD                          READ WRITE NO

 
		 
=========		 

		 
RMAN> restore controlfile from '/home/oracle/RmanBackup/ORCLCDB_T20221230_cf15_p1_t1124819364';

Starting restore at 31-12-2022 02:43:09
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=424 device type=DISK

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/u01/app/oracle/oradata/ORCLCDB/control01.ctl
Finished restore at 31-12-2022 02:43:12

RMAN> alter database mount ;


released channel: ORA_DISK_1
Statement processed



from another session !!!

set linesize 300 pagesize 100
col inst_id                      for 9999999                 heading 'Instance #'
col file_nr                      for 9999999                 heading 'File #'
col file_name                    for A70                     heading 'File name'
col checkpoint_change_nr         for 99999999999999          heading 'Checkpoint #'
col checkpoint_change_time       for A20                     heading 'Checkpoint time'
col last_change_nr               for 99999999999999          heading 'Last change #'
col SCNStatus for a15
SELECT
 fe.inst_id,
fe.CON_ID,  ---- for >12c
 fe.fenum file_nr,
 fn.fnnam file_name,
 TO_NUMBER (fe.fecps) checkpoint_change_nr,
 fe.fecpt checkpoint_change_time,
fe.fests last_change_nr,
NVL2(fe.fecps, '<-Good', 'Recover to this scn') SCNStatus,  ---- Recover to max 'scn'
 DECODE (
 fe.fetsn,
 0, DECODE (BITAND (fe.festa, 2), 0, 'SYSOFF', 'SYSTEM'),
 DECODE (BITAND (fe.festa, 18),
         0, 'OFFLINE',
         2, 'ONLINE',
            'RECOVER')
 ) status
FROM x$kccfe fe, x$kccfn fn
WHERE ( (fe.fepax != 65535 AND fe.fepax != 0 )  OR (fe.fepax = 65535 OR fe.fepax = 0) )
 AND fn.fnfno = fe.fenum
 AND fe.fefnh = fn.fnnum
 AND fe.fedup != 0
 AND fn.fntyp = 4
 AND fn.fnnam IS NOT NULL
 AND BITAND (fn.fnflg, 4) != 4
ORDER BY fe.fenum
;
 
		 
		 Instance #     CON_ID   File # File name                                                                 Checkpoint # Checkpoint time      Last change # SCNSTATUS       STATUS
---------- ---------- -------- ---------------------------------------------------------------------- --------------- -------------------- -------------------- --------------- -------
         1          1        1 /u01/app/oracle/oradata/ORCLCDB/system01.dbf                                   4079968 12/30/2022 17:49:19<-Good          SYSTEM
         1          1        3 /u01/app/oracle/oradata/ORCLCDB/sysaux01.dbf                                   4079968 12/30/2022 17:49:19<-Good          ONLINE
         1          2        5 /u01/app/oracle/oradata/ORCLCDB/pdbseed/system01.dbf                           2163739 05/31/2019 15:32:31<-Good          SYSOFF
         1          2        6 /u01/app/oracle/oradata/ORCLCDB/pdbseed/sysaux01.dbf                           2163739 05/31/2019 15:32:31<-Good          OFFLINE
         1          1        7 /u01/app/oracle/oradata/ORCLCDB/users01.dbf                                    4079968 12/30/2022 17:49:19<-Good          ONLINE
         1          2        8 /u01/app/oracle/oradata/ORCLCDB/pdbseed/undotbs01.dbf                          2163739 05/31/2019 15:32:31<-Good          OFFLINE
         1          3        9 /u01/app/oracle/oradata/ORCLCDB/orcl/system01.dbf                              4079968 12/30/2022 17:49:19<-Good          SYSTEM
         1          3       10 /u01/app/oracle/oradata/ORCLCDB/orcl/sysaux01.dbf                              4079968 12/30/2022 17:49:19<-Good          ONLINE
         1          3       12 /u01/app/oracle/oradata/ORCLCDB/orcl/users01.dbf                               4079968 12/30/2022 17:49:19<-Good          ONLINE
         1          3       13 /u01/app/oracle/oradata/ORCLCDB/orcl/APEX_1291597703607401.dbf                 4079968 12/30/2022 17:49:19<-Good          ONLINE
         1          1       14 /u01/app/oracle/oradata/ORCLCDB/undotbs2.dbf                                   4079968 12/30/2022 17:49:19<-Good          ONLINE
         1          3       15 /u01/app/oracle/oradata/ORCLCDB/orcl/undotbs2.dbf                              4079968 12/30/2022 17:49:19<-Good          ONLINE
         1          4       16 /u01/app/oracle/oradata/ORCLCDB/orcld/system01.dbf                             4078427 12/30/2022 17:42:42<-Good          SYSOFF
         1          4       17 /u01/app/oracle/oradata/ORCLCDB/orcld/sysaux01.dbf                             4078427 12/30/2022 17:42:42<-Good          OFFLINE
         1          4       18 /u01/app/oracle/oradata/ORCLCDB/orcld/undotbs01.dbf                            4078427 12/30/2022 17:42:42<-Good          OFFLINE
         1          4       19 /u01/app/oracle/oradata/ORCLCDB/orcld/orcld.dbf                                4078427 12/30/2022 17:42:42<-Good          OFFLINE

16 rows selected.


Find out the scn from above SQL



run { set until scn 4079968;
            restore database root ;
            restore database "PDB$SEED";
            restore database ORCL;
                        }


Starting restore at 31-12-2022 02:50:50
Starting implicit crosscheck backup at 31-12-2022 02:50:50
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=414 device type=DISK
Crosschecked 14 objects
Finished implicit crosscheck backup at 31-12-2022 02:50:51

Starting implicit crosscheck copy at 31-12-2022 02:50:51
using channel ORA_DISK_1
Crosschecked 4 objects
Finished implicit crosscheck copy at 31-12-2022 02:50:51

searching for all files in the recovery area
cataloging files...
no files cataloged

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 00001 to /u01/app/oracle/oradata/ORCLCDB/system01.dbf
channel ORA_DISK_1: restoring datafile 00007 to /u01/app/oracle/oradata/ORCLCDB/users01.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/RmanBackup/ORCLCDB_T20221230_db_s3_p1_t1124819247
channel ORA_DISK_1: piece handle=/home/oracle/RmanBackup/ORCLCDB_T20221230_db_s3_p1_t1124819247 tag=RMANFULL
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:25
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 00003 to /u01/app/oracle/oradata/ORCLCDB/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00014 to /u01/app/oracle/oradata/ORCLCDB/undotbs2.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/RmanBackup/ORCLCDB_T20221230_db_s5_p1_t1124819293
channel ORA_DISK_1: piece handle=/home/oracle/RmanBackup/ORCLCDB_T20221230_db_s5_p1_t1124819293 tag=RMANFULL
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:15
Finished restore at 31-12-2022 02:51:33

Starting restore at 31-12-2022 02:51:33
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 00005 to /u01/app/oracle/oradata/ORCLCDB/pdbseed/system01.dbf
channel ORA_DISK_1: restoring datafile 00008 to /u01/app/oracle/oradata/ORCLCDB/pdbseed/undotbs01.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/RmanBackup/ORCLCDB_T20221230_db_s7_p1_t1124819319
channel ORA_DISK_1: piece handle=/home/oracle/RmanBackup/ORCLCDB_T20221230_db_s7_p1_t1124819319 tag=RMANFULL
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 00006 to /u01/app/oracle/oradata/ORCLCDB/pdbseed/sysaux01.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/RmanBackup/ORCLCDB_T20221230_db_s9_p1_t1124819336
channel ORA_DISK_1: piece handle=/home/oracle/RmanBackup/ORCLCDB_T20221230_db_s9_p1_t1124819336 tag=RMANFULL
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:15
Finished restore at 31-12-2022 02:52:03

Starting restore at 31-12-2022 02:52:03
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 /home/oracle/RmanBackup/ORCLCDB_T20221230_db_s2_p1_t1124819247
channel ORA_DISK_1: piece handle=/home/oracle/RmanBackup/ORCLCDB_T20221230_db_s2_p1_t1124819247 tag=RMANFULL
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:35
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 /home/oracle/RmanBackup/ORCLCDB_T20221230_db_s4_p1_t1124819293
channel ORA_DISK_1: piece handle=/home/oracle/RmanBackup/ORCLCDB_T20221230_db_s4_p1_t1124819293 tag=RMANFULL
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:25
Finished restore at 31-12-2022 02:53:04






RMAN> recover database ;

Starting recover at 31-12-2022 03:27:16
using channel ORA_DISK_1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 12/31/2022 03:27:16
RMAN-07551: data file 16 must be restored or preplugin recovery must be completed


******************************************************************************************************************
How to use Rman to Restore Of Single PDB in Multitenant to Alternate Server (Doc ID 2142675.1)
Skip the PDB's database not required during the recovery stage by using 'Skip forever tablespace >'

*******************************************************************************************************************




RMAN> report schema ;

RMAN-06139: warning: control file is not current for REPORT SCHEMA
Report of database schema for database with db_unique_name ORCLCDB

List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    910      SYSTEM               ***     /u01/app/oracle/oradata/ORCLCDB/system01.dbf
3    570      SYSAUX               ***     /u01/app/oracle/oradata/ORCLCDB/sysaux01.dbf
5    270      PDB$SEED:SYSTEM      ***     /u01/app/oracle/oradata/ORCLCDB/pdbseed/system01.dbf
6    330      PDB$SEED:SYSAUX      ***     /u01/app/oracle/oradata/ORCLCDB/pdbseed/sysaux01.dbf
7    5        USERS                ***     /u01/app/oracle/oradata/ORCLCDB/users01.dbf
8    100      PDB$SEED:UNDOTBS1    ***     /u01/app/oracle/oradata/ORCLCDB/pdbseed/undotbs01.dbf
9    410      ORCL:SYSTEM          ***     /u01/app/oracle/oradata/ORCLCDB/orcl/system01.dbf
10   1200     ORCL:SYSAUX          ***     /u01/app/oracle/oradata/ORCLCDB/orcl/sysaux01.dbf
12   401      ORCL:USERS           ***     /u01/app/oracle/oradata/ORCLCDB/orcl/users01.dbf
13   2        ORCL:APEX_1291597703607401 ***     /u01/app/oracle/oradata/ORCLCDB/orcl/APEX_1291597703607401.dbf
14   50       UNDOTBS2             ***     /u01/app/oracle/oradata/ORCLCDB/undotbs2.dbf
15   50       ORCL:UNDOTBS2        ***     /u01/app/oracle/oradata/ORCLCDB/orcl/undotbs2.dbf
16   0        ORCLD:SYSTEM         ***     /u01/app/oracle/oradata/ORCLCDB/orcld/system01.dbf
17   0        ORCLD:SYSAUX         ***     /u01/app/oracle/oradata/ORCLCDB/orcld/sysaux01.dbf
18   0        ORCLD:UNDOTBS1       ***     /u01/app/oracle/oradata/ORCLCDB/orcld/undotbs01.dbf
19   0        ORCLD:ORCL1_USERS    ***     /u01/app/oracle/oradata/ORCLCDB/orcld/orcld.dbf

List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1    20       TEMP                 32767       /u01/app/oracle/oradata/ORCLCDB/temp01.dbf
2    36       PDB$SEED:TEMP        32767       /u01/app/oracle/oradata/ORCLCDB/pdbseed/temp012019-05-31_15-03-14-330-PM.dbf
3    36       ORCL:TEMP            32767       /u01/app/oracle/oradata/ORCLCDB/orcl/temp01.dbf
4    36       ORCLD:TEMP           32767       /u01/app/oracle/oradata/ORCLCDB/orcld/cl_ixantemp012019-05-31_15-03-14-330-PM.dbf




Now skip ORCLD pluggable and datafiles!!!


RMAN> recover database skip forever tablespace ORCLD:SYSTEM,ORCLD:ORCL1_USERS,ORCLD:SYSAUX,ORCLD:UNDOTBS1 ;


Starting recover at 31-12-2022 03:39:38
using channel ORA_DISK_1

Executing: alter database datafile 16 offline drop
Executing: alter database datafile 19 offline drop
Executing: alter database datafile 17 offline drop
Executing: alter database datafile 18 offline drop
starting media recovery

channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=33
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=34
channel ORA_DISK_1: reading from backup piece /home/oracle/RmanBackup/ORCLCDB_T20221230_arch_s13_p1_t1124819360
channel ORA_DISK_1: piece handle=/home/oracle/RmanBackup/ORCLCDB_T20221230_arch_s13_p1_t1124819360 tag=RMANFULL
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
archived log file name=/u01/app/oracle/fast_recovery_area/ORCLCDB/archivelog/2022_12_31/o1_mf_1_33_ktzxbcob_.arc thread=1 sequence=33
channel default: deleting archived log(s)
archived log file name=/u01/app/oracle/fast_recovery_area/ORCLCDB/archivelog/2022_12_31/o1_mf_1_33_ktzxbcob_.arc RECID=6 STAMP=1124854779
archived log file name=/u01/app/oracle/fast_recovery_area/ORCLCDB/archivelog/2022_12_31/o1_mf_1_34_ktzxbcoy_.arc thread=1 sequence=34
channel default: deleting archived log(s)
archived log file name=/u01/app/oracle/fast_recovery_area/ORCLCDB/archivelog/2022_12_31/o1_mf_1_34_ktzxbcoy_.arc RECID=5 STAMP=1124854779
channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=35
channel ORA_DISK_1: reading from backup piece /home/oracle/RmanBackup/ORCLCDB_T20221230_arch_s14_p1_t1124819361
channel ORA_DISK_1: piece handle=/home/oracle/RmanBackup/ORCLCDB_T20221230_arch_s14_p1_t1124819361 tag=RMANFULL
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
archived log file name=/u01/app/oracle/fast_recovery_area/ORCLCDB/archivelog/2022_12_31/o1_mf_1_35_ktzxbf4z_.arc thread=1 sequence=35
channel default: deleting archived log(s)
archived log file name=/u01/app/oracle/fast_recovery_area/ORCLCDB/archivelog/2022_12_31/o1_mf_1_35_ktzxbf4z_.arc RECID=7 STAMP=1124854781
unable to find archived log
archived log thread=1 sequence=36
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 12/31/2022 03:39:42
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 36 and starting SCN of 4079968

RMAN>

RMAN> alter database open resetlogs;
Statement processed

Recovey completed ....




SQL> select open_mode,current_scn,checkpoint_change#,archive_change#,controlfile_change# from v$database;

OPEN_MODE            CURRENT_SCN CHECKPOINT_CHANGE# ARCHIVE_CHANGE# CONTROLFILE_CHANGE#
-------------------- ----------- ------------------ --------------- -------------------
READ WRITE               4092115            4079972               0             4092072




set linesize 300
col NAME for a70
col ERROR for a15
 select * from (select file#,con_id,tablespace_name,status,error ,recover ,fuzzy ,checkpoint_time checkpoint from v$datafile_header)
natural join (select hxfil file#, fhsta, fhscn, fhrba_seq, fhafs from x$kcvfhall);





    FILE#     CON_ID TABLESPACE_NAME                STATUS  ERROR           REC FUZ CHECKPOIN      FHSTA FHSCN                 FHRBA_SEQ FHAFS
---------- ---------- ------------------------------ ------- --------------- --- --- --------- ---------- -------------------- ---------- --------------------
         1          1 SYSTEM                         ONLINE                  NO  YES 31-DEC-22       8196 4079972                       1 0
         3          1 SYSAUX                         ONLINE                  NO  YES 31-DEC-22          4 4079972                       1 0
         5          2 SYSTEM                         ONLINE                      NO  31-MAY-19       8192 2163739                       7 0
         6          2 SYSAUX                         ONLINE                      NO  31-MAY-19          0 2163739                       7 0
         7          1 USERS                          ONLINE                  NO  YES 31-DEC-22          4 4079972                       1 0
         8          2 UNDOTBS1                       ONLINE                      NO  31-MAY-19          0 2163739                       7 0
         9          3 SYSTEM                         ONLINE                  NO  YES 31-DEC-22       8196 4080979                       1 0
        10          3 SYSAUX                         ONLINE                  NO  YES 31-DEC-22          4 4080979                       1 0
        12          3 USERS                          ONLINE                  NO  YES 31-DEC-22          4 4080979                       1 0
        13          3 APEX_1291597703607401          ONLINE                  NO  YES 31-DEC-22          4 4080979                       1 0
        14          1 UNDOTBS2                       ONLINE                  NO  YES 31-DEC-22          4 4079972                       1 0
        15          3 UNDOTBS2                       ONLINE                  NO  YES 31-DEC-22          4 4080979                       1 0
        16          4                                OFFLINE FILE NOT FOUND                             0 0                             0 0
        17          4                                OFFLINE FILE NOT FOUND                             0 0                             0 0
        18          4                                OFFLINE FILE NOT FOUND                             0 0                             0 0
        19          4                                OFFLINE FILE NOT FOUND                             0 0                             0 0

16 rows selected.

SQL>



SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 ORCL                           READ WRITE NO
         4 ORCLD                          MOUNTED  <<<<<
SQL>


      

Oracle Recover from a loss of the SYSTEM tablespace datafile

recover system datafile  lost

Recover from a loss of the SYSTEM tablespace datafile 



Error on alert log
ORA-01110: data file 1: '/u01/app/oracle/Datafile/data_D-ORCL_TS-SYSTEM_FNO-1'
ORA-01565: error in identifying file '/u01/app/oracle/Datafile/data_D-ORCL_TS-SYSTEM_FNO-1'   ------<<<<<<<
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
 


We should have valid backup to recover system datafile  ...


export NLS_DATE_FORMAT='dd-mm-yyyy hh:mi:ss'
rman target / | tee rman.log
run {
  allocate channel ch1 device type DISK;
  allocate channel ch2 device type DISK;
  backup incremental level=0 tag "RMANFULL" format '/u01/app/oracle/RmanBackup/%d_T%T_db_s%s_p%p_t%t' database filesperset 4 plus archivelog tag "RMANFULL";
 sql "alter system archive log current";
  backup format '/u01/app/oracle/RmanBackup/%d_T%T_arch_s%s_p%p_t%t' archivelog all filesperset 4 tag "RMANFULL";
  backup format '/u01/app/oracle/RmanBackup/%d_T%T_cf%s_p%p_t%t' current controlfile tag "RMANFULL";
sql "alter system archive log current";
restore database preview;
}



Simulate the failure - by moving the system datafile:


[oracle@wcp12cr2 Datafile]$ mv data_D-ORCL_TS-SYSTEM_FNO-1 data_D-ORCL_TS-SYSTEM_FNO-1-ORIG



SQL> shutdown immediate ;
ORA-01116: error in opening database file 1
ORA-01110: data file 1: '/u01/app/oracle/Datafile/data_D-ORCL_TS-SYSTEM_FNO-1'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
SQL>






from alert log 

2022-12-29T13:34:41.592711-08:00
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_m000_10344.trc:
ORA-01110: data file 1: '/u01/app/oracle/Datafile/data_D-ORCL_TS-SYSTEM_FNO-1'
ORA-01565: error in identifying file '/u01/app/oracle/Datafile/data_D-ORCL_TS-SYSTEM_FNO-1'   ------<<<<<<<
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 7
Checker run found 1 new persistent data failures
2022-12-29T13:35:25.549197-08:00
OS process OFSD (ospid 3088) idle for 30 seconds, exiting



--Check the status of datafiles 

set pagesize 300 linesize 200
col NAME for a70
col recover for a12
col , error for a20
select file#,NAME,checkpoint_change#, status, recover, error from v$datafile_header;


SQL> SQL> SQL>
     FILE# NAME                                                                   CHECKPOINT_CHANGE# STATUS  REC
---------- ---------------------------------------------------------------------- ------------------ ------- ---
         1                                                                                         0 ONLINE        <<<<< lost file 
         2 /u01/app/oracle/Datafile/data_D-ORCL_TS-WCPVM_IAS_OPSS_FNO-2                      3361521 ONLINE  NO
         3 /u01/app/oracle/Datafile/data_D-ORCL_TS-SYSAUX_FNO-3                              3361521 ONLINE  NO
         4 /u01/app/oracle/Datafile/data_D-ORCL_TS-UNDOTBS1_FNO-4                            3361521 ONLINE  NO
         5 /u01/app/oracle/Datafile/data_D-ORCL_TS-WCPVM_IAS_PORTLET_FNO-5                   3361521 ONLINE  NO
         6 /u01/app/oracle/Datafile/data_D-ORCL_TS-USERS_FNO-6                               3361521 ONLINE  NO
         7 /u01/app/oracle/Datafile/data_D-ORCL_TS-WCPVM_IAS_WEBCENTER_FNO-7                 3361521 ONLINE  NO
         8 /u01/app/oracle/Datafile/data_D-ORCL_TS-WCPVM_IAS_DISCUSS_FNO-8                   3361521 ONLINE  NO
         9 /u01/app/oracle/Datafile/data_D-ORCL_TS-WCPVM_STB_FNO-9                           3361521 ONLINE  NO
        10 /u01/app/oracle/Datafile/data_D-ORCL_TS-WCPVM_IAU_FNO-10                          3361521 ONLINE  NO
        11 /u01/app/oracle/Datafile/data_D-ORCL_TS-WCPVM_MDS_FNO-11                          3361521 ONLINE  NO
        12 /u01/app/oracle/Datafile/data_D-ORCL_TS-WCPVM_OCS_FNO-12                          3361521 ONLINE  NO
        13 /u01/app/oracle/Datafile/data_D-ORCL_TS-WCPVM_IAS_ACTIVITY_FNO-13                 3361521 ONLINE  NO
        14 /u01/app/oracle/Datafile/data_D-ORCL_TS-DBFS_TS_FNO-14                            3361521 ONLINE  NO

14 rows selected.



Restore and Recover the system datafile using backups


RMAN> restore datafile 1;

Starting restore at 29-12-2022 01:42:42
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 00001 to /u01/app/oracle/Datafile/data_D-ORCL_TS-SYSTEM_FNO-1
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/RmanBackup/ORCL_T20221229_db_s19_p1_t1124716704
channel ORA_DISK_1: piece handle=/u01/app/oracle/RmanBackup/ORCL_T20221229_db_s19_p1_t1124716704 tag=RMANFULL
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:25
Finished restore at 29-12-2022 01:43:08


SQL> SQL> /

     FILE# NAME                                                                   CHECKPOINT_CHANGE# STATUS  REC
---------- ---------------------------------------------------------------------- ------------------ ------- ---
         1 /u01/app/oracle/Datafile/data_D-ORCL_TS-SYSTEM_FNO-1                                    0 ONLINE
         2 /u01/app/oracle/Datafile/data_D-ORCL_TS-WCPVM_IAS_OPSS_FNO-2                      3361521 ONLINE  NO
         3 /u01/app/oracle/Datafile/data_D-ORCL_TS-SYSAUX_FNO-3                              3361521 ONLINE  NO
         4 /u01/app/oracle/Datafile/data_D-ORCL_TS-UNDOTBS1_FNO-4                            3361521 ONLINE  NO
         5 /u01/app/oracle/Datafile/data_D-ORCL_TS-WCPVM_IAS_PORTLET_FNO-5                   3361521 ONLINE  NO
         6 /u01/app/oracle/Datafile/data_D-ORCL_TS-USERS_FNO-6                               3361521 ONLINE  NO
         7 /u01/app/oracle/Datafile/data_D-ORCL_TS-WCPVM_IAS_WEBCENTER_FNO-7                 3361521 ONLINE  NO
         8 /u01/app/oracle/Datafile/data_D-ORCL_TS-WCPVM_IAS_DISCUSS_FNO-8                   3361521 ONLINE  NO
         9 /u01/app/oracle/Datafile/data_D-ORCL_TS-WCPVM_STB_FNO-9                           3361521 ONLINE  NO
        10 /u01/app/oracle/Datafile/data_D-ORCL_TS-WCPVM_IAU_FNO-10                          3361521 ONLINE  NO
        11 /u01/app/oracle/Datafile/data_D-ORCL_TS-WCPVM_MDS_FNO-11                          3361521 ONLINE  NO
        12 /u01/app/oracle/Datafile/data_D-ORCL_TS-WCPVM_OCS_FNO-12                          3361521 ONLINE  NO
        13 /u01/app/oracle/Datafile/data_D-ORCL_TS-WCPVM_IAS_ACTIVITY_FNO-13                 3361521 ONLINE  NO
        14 /u01/app/oracle/Datafile/data_D-ORCL_TS-DBFS_TS_FNO-14                            3361521 ONLINE  NO

14 rows selected.




RMAN> recover datafile 1;

Starting recover at 29-12-2022 01:44:29
using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 3 is already on disk as file /u01/app/oracle/Archive/ORCL/archivelog/2022_12_29/o1_mf_1_3_ktw1221o_.arc
archived log for thread 1 with sequence 4 is already on disk as file /u01/app/oracle/Archive/ORCL/archivelog/2022_12_29/o1_mf_1_4_ktw124o0_.arc
archived log for thread 1 with sequence 5 is already on disk as file /u01/app/oracle/Archive/ORCL/archivelog/2022_12_29/o1_mf_1_5_ktw124sc_.arc
archived log for thread 1 with sequence 6 is already on disk as file /u01/app/oracle/Archive/ORCL/archivelog/2022_12_29/o1_mf_1_6_ktw12fc8_.arc
archived log file name=/u01/app/oracle/Archive/ORCL/archivelog/2022_12_29/o1_mf_1_3_ktw1221o_.arc thread=1 sequence=3
archived log file name=/u01/app/oracle/Archive/ORCL/archivelog/2022_12_29/o1_mf_1_4_ktw124o0_.arc thread=1 sequence=4
media recovery complete, elapsed time: 00:00:01
Finished recover at 29-12-2022 01:44:31

RMAN>



   FILE# NAME                                                                   CHECKPOINT_CHANGE# STATUS  REC
---------- ---------------------------------------------------------------------- ------------------ ------- ---
         1 /u01/app/oracle/Datafile/data_D-ORCL_TS-SYSTEM_FNO-1                              3382895 ONLINE  NO  <<<<<<<----- 10="" 11="" 12="" 13="" 14="" 2="" 3361521="" 3="" 4="" 5="" 6="" 7="" 8="" 9="" app="" atafile="" data_d-orcl_ts-dbfs_ts_fno-14="" data_d-orcl_ts-sysaux_fno-3="" data_d-orcl_ts-undotbs1_fno-4="" data_d-orcl_ts-users_fno-6="" data_d-orcl_ts-wcpvm_ias_activity_fno-13="" data_d-orcl_ts-wcpvm_ias_discuss_fno-8="" data_d-orcl_ts-wcpvm_ias_opss_fno-2="" data_d-orcl_ts-wcpvm_ias_portlet_fno-5="" data_d-orcl_ts-wcpvm_ias_webcenter_fno-7="" data_d-orcl_ts-wcpvm_iau_fno-10="" data_d-orcl_ts-wcpvm_mds_fno-11="" data_d-orcl_ts-wcpvm_ocs_fno-12="" data_d-orcl_ts-wcpvm_stb_fno-9="" no="" online="" oracle="" rman="" rows="" selected.="" u01=""> alter database open ;

Statement processed

RMAN>


====================

now all the scn no are same 


SQL> r
  1* select file#,NAME,checkpoint_change#, status, recover from v$datafile_header

    FILE# NAME                                                                   CHECKPOINT_CHANGE# STATUS  RECOVER
---------- ---------------------------------------------------------------------- ------------------ ------- ------------
         1 /u01/app/oracle/Datafile/data_D-ORCL_TS-SYSTEM_FNO-1                              3402898 ONLINE  NO
         2 /u01/app/oracle/Datafile/data_D-ORCL_TS-WCPVM_IAS_OPSS_FNO-2                      3402898 ONLINE  NO
         3 /u01/app/oracle/Datafile/data_D-ORCL_TS-SYSAUX_FNO-3                              3402898 ONLINE  NO
         4 /u01/app/oracle/Datafile/data_D-ORCL_TS-UNDOTBS1_FNO-4                            3402898 ONLINE  NO
         5 /u01/app/oracle/Datafile/data_D-ORCL_TS-WCPVM_IAS_PORTLET_FNO-5                   3402898 ONLINE  NO
         6 /u01/app/oracle/Datafile/data_D-ORCL_TS-USERS_FNO-6                               3402898 ONLINE  NO
         7 /u01/app/oracle/Datafile/data_D-ORCL_TS-WCPVM_IAS_WEBCENTER_FNO-7                 3402898 ONLINE  NO
         8 /u01/app/oracle/Datafile/data_D-ORCL_TS-WCPVM_IAS_DISCUSS_FNO-8                   3402898 ONLINE  NO
         9 /u01/app/oracle/Datafile/data_D-ORCL_TS-WCPVM_STB_FNO-9                           3402898 ONLINE  NO
        10 /u01/app/oracle/Datafile/data_D-ORCL_TS-WCPVM_IAU_FNO-10                          3402898 ONLINE  NO
        11 /u01/app/oracle/Datafile/data_D-ORCL_TS-WCPVM_MDS_FNO-11                          3402898 ONLINE  NO
        12 /u01/app/oracle/Datafile/data_D-ORCL_TS-WCPVM_OCS_FNO-12                          3402898 ONLINE  NO
        13 /u01/app/oracle/Datafile/data_D-ORCL_TS-WCPVM_IAS_ACTIVITY_FNO-13                 3402898 ONLINE  NO
        14 /u01/app/oracle/Datafile/data_D-ORCL_TS-DBFS_TS_FNO-14                            3402898 ONLINE  NO

14 rows selected.



 set pages 200 lines 200
col FILE_NAME for a70
 select FILE_ID,TABLESPACE_NAME,FILE_NAME,STATUS from dba_data_files;

   FILE_ID TABLESPACE_NAME                FILE_NAME                                                              STATUS
---------- ------------------------------ ---------------------------------------------------------------------- ---------
         1 SYSTEM                         /u01/app/oracle/Datafile/data_D-ORCL_TS-SYSTEM_FNO-1                   AVAILABLE
         3 SYSAUX                         /u01/app/oracle/Datafile/data_D-ORCL_TS-SYSAUX_FNO-3                   AVAILABLE
         4 UNDOTBS1                       /u01/app/oracle/Datafile/data_D-ORCL_TS-UNDOTBS1_FNO-4                 AVAILABLE
         6 USERS                          /u01/app/oracle/Datafile/data_D-ORCL_TS-USERS_FNO-6                    AVAILABLE
         2 WCPVM_IAS_OPSS                 /u01/app/oracle/Datafile/data_D-ORCL_TS-WCPVM_IAS_OPSS_FNO-2           AVAILABLE
         5 WCPVM_IAS_PORTLET              /u01/app/oracle/Datafile/data_D-ORCL_TS-WCPVM_IAS_PORTLET_FNO-5        AVAILABLE
         7 WCPVM_IAS_WEBCENTER            /u01/app/oracle/Datafile/data_D-ORCL_TS-WCPVM_IAS_WEBCENTER_FNO-7      AVAILABLE
         8 WCPVM_IAS_DISCUSS              /u01/app/oracle/Datafile/data_D-ORCL_TS-WCPVM_IAS_DISCUSS_FNO-8        AVAILABLE
         9 WCPVM_STB                      /u01/app/oracle/Datafile/data_D-ORCL_TS-WCPVM_STB_FNO-9                AVAILABLE
        10 WCPVM_IAU                      /u01/app/oracle/Datafile/data_D-ORCL_TS-WCPVM_IAU_FNO-10               AVAILABLE
        11 WCPVM_MDS                      /u01/app/oracle/Datafile/data_D-ORCL_TS-WCPVM_MDS_FNO-11               AVAILABLE
        12 WCPVM_OCS                      /u01/app/oracle/Datafile/data_D-ORCL_TS-WCPVM_OCS_FNO-12               AVAILABLE
        13 WCPVM_IAS_ACTIVITY             /u01/app/oracle/Datafile/data_D-ORCL_TS-WCPVM_IAS_ACTIVITY_FNO-13      AVAILABLE
        14 DBFS_TS                        /u01/app/oracle/Datafile/data_D-ORCL_TS-DBFS_TS_FNO-14                 AVAILABLE

14 rows selected.

=====


SET LINES 200 pagesize 300
COL name             FORM a80
COL status           FORM A12
COL file#            FORM 9999
COL control_file_SCN FORM 999999999999999
COL datafile_SCN     FORM 999999999999999
--
SELECT
 a.name
,a.status
,a.file#
,a.checkpoint_change# control_file_SCN
,b.checkpoint_change# datafile_SCN
,CASE
   WHEN ((a.checkpoint_change# - b.checkpoint_change#) = 0) THEN 'Startup Normal'
   WHEN ((b.checkpoint_change#) = 0)                        THEN 'File Missing?'
   WHEN ((a.checkpoint_change# - b.checkpoint_change#) > 0) THEN 'Media Rec. Req.'
   WHEN ((a.checkpoint_change# - b.checkpoint_change#) < 0) THEN 'Old Control File'
   ELSE 'what the ?'
 END datafile_status
FROM v$datafile        a -- control file SCN for datafile
    ,v$datafile_header b -- datafile header SCN
WHERE a.file# = b.file#
ORDER BY a.file#;


NAME                                                                             STATUS       FILE# CONTROL_FILE_SCN     DATAFILE_SCN DATAFILE_STATUS
-------------------------------------------------------------------------------- ------------ ----- ---------------- ---------------- ----------------
/u01/app/oracle/Datafile/data_D-ORCL_TS-SYSTEM_FNO-1                             SYSTEM           1          3405085          3405085 Startup Normal
/u01/app/oracle/Datafile/data_D-ORCL_TS-WCPVM_IAS_OPSS_FNO-2                     ONLINE           2          3405085          3405085 Startup Normal
/u01/app/oracle/Datafile/data_D-ORCL_TS-SYSAUX_FNO-3                             ONLINE           3          3405085          3405085 Startup Normal
/u01/app/oracle/Datafile/data_D-ORCL_TS-UNDOTBS1_FNO-4                           ONLINE           4          3405085          3405085 Startup Normal
/u01/app/oracle/Datafile/data_D-ORCL_TS-WCPVM_IAS_PORTLET_FNO-5                  ONLINE           5          3405085          3405085 Startup Normal
/u01/app/oracle/Datafile/data_D-ORCL_TS-USERS_FNO-6                              ONLINE           6          3405085          3405085 Startup Normal
/u01/app/oracle/Datafile/data_D-ORCL_TS-WCPVM_IAS_WEBCENTER_FNO-7                ONLINE           7          3405085          3405085 Startup Normal
/u01/app/oracle/Datafile/data_D-ORCL_TS-WCPVM_IAS_DISCUSS_FNO-8                  ONLINE           8          3405085          3405085 Startup Normal
/u01/app/oracle/Datafile/data_D-ORCL_TS-WCPVM_STB_FNO-9                          ONLINE           9          3405085          3405085 Startup Normal
/u01/app/oracle/Datafile/data_D-ORCL_TS-WCPVM_IAU_FNO-10                         ONLINE          10          3405085          3405085 Startup Normal
/u01/app/oracle/Datafile/data_D-ORCL_TS-WCPVM_MDS_FNO-11                         ONLINE          11          3405085          3405085 Startup Normal
/u01/app/oracle/Datafile/data_D-ORCL_TS-WCPVM_OCS_FNO-12                         ONLINE          12          3405085          3405085 Startup Normal
/u01/app/oracle/Datafile/data_D-ORCL_TS-WCPVM_IAS_ACTIVITY_FNO-13                ONLINE          13          3405085          3405085 Startup Normal
/u01/app/oracle/Datafile/data_D-ORCL_TS-DBFS_TS_FNO-14                           ONLINE          14          3405085          3405085 Startup Normal

14 rows selected.

Oracle DBA unix useful command

Unix for the DBA How to kill all similar processes with single command (in this case opmn)

 

stty erase ^?
 stty erase ^H

ps -ef | grep opmn |grep -v grep | awk ‘{print $2}’ |xargs -i kill -9 {}
Locating Files under a particular directory

find . -print |grep -i test.sql

 Using AWK in UNIX

To remove a specific column of output from a UNIX command – for example to determine the UNIX process Ids for all Oracle processes on server (second column)

ps -ef |grep -i oracle |awk '{ print $2 }'

Changing the standard prompt for Oracle Users

Edit the .profile for the oracle user

PS1="`hostname`*$ORACLE_SID:$PWD>"

 Display top 10 CPU consumers using the ps command

/usr/ucb/ps auxgw | head -11

 Show number of active Oracle dedicated connection users for a particular ORACLE_SID

ps -ef | grep $ORACLE_SID|grep -v grep|grep -v ora_|wc -l

 Display the number of CPU’s in Solaris

psrinfo -v | grep "Status of processor"|wc -l

Display the number of CPU’s in AIX

lsdev -C | grep Process|wc -l

Display RAM Memory size on Solaris

prtconf |grep -i mem

Display RAM memory size on AIX

First determine name of memory device

lsdev -C |grep mem

then assuming the name of the memory device is ‘mem0’

lsattr -El mem0

Swap space allocation and usage

Solaris : swap -s or swap -l
Aix : lsps -a

 Total number of semaphores held by all instances on server

ipcs -as | awk '{sum += $9} END {print sum}'

View allocated RAM memory segments

ipcs -pmb

Manually deallocate shared memeory segments

ipcrm -m ''

 Show mount points for a disk in AIX

lspv -l hdisk13

 Display amount of occupied space (in KB) for a file or collection of files in a directory or sub-directory

du -ks * | sort -n| tail

Display total file space in a directory

du -ks .

 Cleanup any unwanted trace files more than seven days old

find . *.trc -mtime +7 -exec rm {} \;

 Locate Oracle files that contain certain strings

find . -print | xargs grep rollback

 Locate recently created UNIX files (in the past one day)

find . -mtime -1 -print

 Finding large files on the server (more than 100MB in size)

find . -size +102400 -print

Remove DOS CR/LFs (^M)
Remove DOS style CR/LF characters (^M) from UNIX files using:

    sed -e 's/^M$//' filename > tempfile


sar
$ sar -u 10 8
Reports CPU Utilization (10 seconds apart; 8 times):
Time 	%usr 	%sys 	%wio 	%idle
11:57:31 	72 	28 	0 	0
11:57:41 	70 	30 	0 	0
11:57:51 	70 	30 	0 	0
11:58:01 	68 	32 	0 	0
11:58:11 	67 	33 	0 	0
11:58:21 	65 	28 	0 	7
11:58:31 	73 	27 	0 	0
11:58:41 	69 	31 	0 	0
Average 	69 	30 	0 	1

%usr: Percent of CPU in user mode
%sys: Percent of CPU in system mode
%wio: Percent of CPU running idle with a process waiting for block I/O
%idle: Percent of CPU that is idle


mpstat
$ mpstat 10 2
Reports per-processor statistics on Sun Solaris (10 seconds apart; 8 times):
CPU 	minf 	mjf 	xcal 	intr 	ithr 	csw 	icsw 	migr 	smtx 	srw 	syscl 	usr 	sys 	wt 	idl
0 	6 	8 	0 	438 	237 	246 	85 	0 	0 	21 	8542 	23 	9 	9 	59
0 	0 	29 	0 	744 	544 	494 	206 	0 	0 	95 	110911 	65 	29 	6 	0
ps
$ ps -e -o pcpu -o pid -o user -o args | sort -k 1 | tail -21r
Displays the top 20 CPU users on the system.
%CPU 	PID 	USER 	COMMAND
78.1 	4789 	oracle 	ora_dbwr_DDDS2
8.5 	4793 	oracle 	ora_lgwr_DDDS2
2.4 	6206 	oracle 	oracleDDDS2 (LOCAL=NO)
0.1 	4797 	oracle 	ora_smon_DDDS2
0.1 	6207 	oracle 	oracleDDDS2 (LOCAL=NO)
etc. 	etc. 	etc. 	etc.

The PID column can then be matched with the SPID column on the V$PROCESS view to provide more information on the process:

    SELECT a.username, 
           a.osuser, 
           a.program, 
           spid, 
           sid, 
           a.serial#
    FROM   v$session a,
           v$process b
    WHERE  a.paddr = b.addr
    AND    spid = '&pid';


CRON
There are two methods of editing the crontab file. First you can use the "crontab -l > filename" option to list the contents and pipe this to a file. Once you've editied the file you can then apply it using the "crontab filename":

    * Login as root
    * crontab -l > newcron
    * Edit newcron file.
    * crontab newcron

Alternatively you can use the "crontab -e" option to edit the crontab file directly.

The entries have the following elements:

    field          allowed values
    -----          --------------
    minute         0-59
    hour           0-23
    day of month   1-31
    month          1-12
    day of week    0-7 (both 0 and 7 are Sunday)
    user           Valid OS user
    command        Valid command or script.

The first 5 fields can be specified using the following rules:

    *       - All available values or "first-last".
    3-4     - A single range representing each possible from the start to the end of the range inclusive.
    1,2,5,6 - A specific list of values.
    1-3,5-8 - A specific list of ranges.
    0-23/2  - Every other value in the specified range.

The following entry runs a cleanup script a 01:00 each Sunday. Any output or errors from the script are piped to /dev/null to prevent a buildup of mails to root:

    0 1 * * 0 /u01/app/oracle/dba/weekly_cleanup > /dev/null 2>&1


vmstat
$ vmstat 5 3
Displays system statistics (5 seconds apart; 3 times):
procs 	memory 	page 	disk 	faults 	cpu
r 	b 	w 	swap 	free 	re 	mf 	pi 	po 	fr 	de 	sr 	s0 	s1 	s2 	s3 	in 	sy 	cs 	us 	sy 	id
0 	0 	0 	28872 	8792 	8 	5 	172 	142 	210 	0 	24 	3 	11 	17 	2 	289 	1081 	201 	14 	6 	80
0 	0 	0 	102920 	1936 	1 	95 	193 	6 	302 	1264 	235 	12 	1 	0 	3 	240 	459 	211 	0 	2 	97
0 	0 	0 	102800 	1960 	0 	0 	0 	0 	0 	464 	0 	0 	0 	0 	0 	107 	146 	29 	0 	0 	100

Having any processes in the b or w columns is a sign of a problem system.
Having an id of 0 is a sign that the cpu is overburdoned.
Having high values in pi and po show excessive paging.

    * procs (Reports the number of processes in each of the following states)
          o r : in run queue
          o b : blocked for resources (I/O, paging etc.)
          o w : runnable but swapped
    * memory (Reports on usage of virtual and real memory)
          o swap : swap space currently available (Kbytes)
          o free : size of free list (Kbytes)
    * page (Reports information about page faults and paging activity (units per second)
          o re : page reclaims
          o mf : minor faults
          o pi : Kbytes paged in
          o po : Kbytes paged out
          o fr : Kbytes freed
          o de : anticipated short-term memory shortfall (Kbytes)
          o sr : pages scanned by clock algorith
    * disk (Reports the number of disk operations per second for up to 4 disks
    * faults (Reports the trap/interupt rates (per second)
          o in : (non clock) device interupts
          o si : system calls
          o cs : CPU context switches
    * cpu (Reports the breakdown of percentage usage of CPU time (averaged across all CPUs)
          o us : user time
          o si : system time
          o cs : idle time


File Exists Check
The Korn shell allows you to check for the presence of a file using the "test -s" command. In the following script a backup log is renamed and moved if it is present:

    #!/bin/ksh
    if test -s /backup/daily_backup.log
    then
      DATE_SUFFIX=`date +"%y""%m""%d""%H""%M"`
      mv /backup/daily_backup.log /backup/archive/daily_backup$DATE_SUFFIX.log
    fi

==


Automatic Startup Scripts on Linux
Create a file in the /etc/init.d/ directory, in this case the file is called myservice, containing the commands you wish to run at startup and/or shutdown.

Use the chmod command to set the privileges to 750:

    chmod 750 /etc/init.d/myservice

Link the file into the appropriate run-level script directories:

    ln -s /etc/init.d/myservice /etc/rc0.d/K10myservice
    ln -s /etc/init.d/myservice /etc/rc3.d/S99myservice

Associate the myservice service with the appropriate run levels:

    chkconfig --level 345 dbora on

The script should now be automatically run at startup and shutdown (with "start" or "stop" as a commandline parameter) like other service initialization scripts.

Oracle too many parse errors PARSE ERROR

DB Alert log Filled With WARNING'S 'too many parse errors PARSE ERROR: ORA-00923' (Doc ID 2649163.1)

WARNING: too many parse errors, count

To find Sql id !!!




SQL ending up in the shared pool 

$ grep -c "SQL hash" alert_<DBName>.log
2027
$ grep -c "hash=0xbbcb647d" alert_<DBName>.log
2027 



test 


from https://jonathanlewis.wordpress.com/2017/10/06/12c-parse/

declare
        m1 number;
begin
        for i in 1..1000000 loop
        begin
                execute immediate 'select count(*) frm dual' into m1;
                dbms_output.put_line(m1);
        exception
                when others then null;
        end;
        end loop;
end;
/



from alert log !!!!

Additional information: hd=0x1002f46e0 phd=0x10cd45dc0 flg=0x28 cisid=0 sid=0 ciuid=0 uid=0
2022-12-14T04:42:30.206000-05:00
----- PL/SQL Call Stack -----
  object      line  object
  handle    number  name
0x1175e2778         6  anonymous block  <<<<<<<<-------------------Check This 
WARNING: too many parse errors, count=1010000 SQL hash=0x19a22496
PARSE ERROR: ospid=32580, error=923 for statement:
2022-12-14T04:42:30.219167-05:00
select count(*) frm dual
Additional information: hd=0x1002f46e0 phd=0x10cd45dc0 flg=0x28 cisid=0 sid=0 ciuid=0 uid=0
2022-12-14T04:42:30.219363-05:00
----- PL/SQL Call Stack -----
  object      line  object
  handle    number  name
  

0x1175e2778         6  anonymous block





set linesize 300 
col sql_text for a50 wrap
 select address,child_address, sql_text from V$sql where child_address like '%2778%';




set linesize 300
col sql_text for a50 wrap
select sql_id,address,child_address, sql_text from gv$sql where child_address like '%2778%';  --<<<< --- 0x1175e2778         6  anonymous block


SQL_ID        ADDRESS          CHILD_ADDRESS    SQL_TEXT
------------- ---------------- ---------------- --------------------------------------------------
2r9g466tw2h18 000000010CD45798 00000001175E2778 declare         m1 number; begin         for i in
                                                1..1000000 loop         begin                 exec
                                                ute immediate 'select count(*) frm dual' into m1;
                                                                dbms_output.put_line(m1);
                                                exception                 when others then null;
                                                       end;         end loop; end;



===

from 0x1175e2778         6  anonymous block 




set linesize 300
col sql_text for a50 wrap
select sql_id,address,child_address, sql_text from gv$sql where child_address like '%1175E2778%';


SQL_ID        ADDRESS          CHILD_ADDRESS    SQL_TEXT
------------- ---------------- ---------------- --------------------------------------------------
2r9g466tw2h18 000000010CD45798 00000001175E2778 declare         m1 number; begin         for i in
                                                1..1000000 loop         begin                 exec
                                                ute immediate 'select count(*) frm dual' into m1;
                                                                dbms_output.put_line(m1);
                                                exception                 when others then null;
                                                       end;         end loop; end;





set linesize 150 pagesize 300
col PLAN_TABLE_OUTPUT for a125

SELECT t.* FROM v$sql s, table(DBMS_XPLAN.DISPLAY_CURSOR(s.sql_id, s.child_number)) t 
WHERE 1=1
--and sql_text LIKE '%XXX%'
and sql_id = '&sql_id'
;





alter session set nls_date_format='dd-mon-yyyy hh24:mi:ss';
set pages 200  line 900
col sql_text for a80 
col PROGRAM for a20
con inst_id for 99
col MACHINE for a20
col CPU 9999999999
col kill for a17
col event for a40
select distinct * from (select ''''||s.sid ||','|| s.serial#||',@'||s.inst_id ||'''' kill,s.con_id,sa.sql_id,vp.spid,
sa.CPU_TIME "CPU",
round(s.last_call_et,0) ,
s.program 		"PROGRAM",
s.machine 		"MACHINE",
sw.event,s.logon_time,s.username,
s.status,s.osuser ,
sa.SQL_TEXT 		"SQL_TEXT"
from gv$sqlarea sa, gv$session s, gv$process vp, gv$session_wait sw
where sa.address = s.sql_address
and sw.sid = s.sid 
and s.serial# <> 1 
and s.status='ACTIVE'
and s.paddr = vp.addr 
and sw.event not like '%client%'
order by round(s.last_call_et,0) desc
)
;