Tuesday, 7 February 2023
Friday, 20 January 2023
Sequence Info
define sequence_name='SEQ'
set feed off
set pagesize 10000
set wrap off
set linesize 200
set heading on
set tab on
set scan on
set verify off
set numf 999999999999999999999999
column datum new_value datum noprint
column sequence_owner format A20 heading 'Sequence|Owner'
column sequence_name noprint new_value sequence
column min_value format 99 heading 'Minimal|Value '
column max_value format 99999999999999999999999999999999 heading 'Maximal |Value '
column increment_by format 99 heading 'Incr|By '
column last_number format 999999999999 heading 'Last |Number '
column cache_size format 9999999999999 heading 'Cache|Size '
column order_flag format a7 heading 'Order ?'
column cycle_flag format a7 heading 'Cycle ?'
ttitle left 'Sequence Info 'sequence skip 2
break on sequence_name skip page -
on sequence_owner skip 2 -
select to_char(sysdate, 'DD-MM-YY') datum,
substr(sequence_owner,1,12) sequence_owner,
substr(sequence_name,1,27) sequence_name,
min_value,
max_value,
increment_by,
last_number,
cache_size,
decode(order_flag,
'Y','Yes',
'N','No') order_flag,
decode(cycle_flag,
'Y','Yes',
'N','No') cycle_flag
from dba_sequences
where 1=1
--and sequence_owner LIKE UPPER('user')
and sequence_name like upper('&sequence_name')
order by 1,2,3,4;
define sequence_name='SEQ'
set feed off
set pagesize 10000
set wrap off
set linesize 200
set heading on
set tab on
set scan on
set verify off
--
ttitle left 'Shows next sequence number without incrementing it' skip 2
col sequence_owner format a20 heading 'Sequence|Owner'
col sequence_name format a25 heading 'Sequence|Name'
col next_seq format 9999999999999999999 heading 'Next|Value'
col cache format a25 heading 'Cache'
select sequence_owner, sequence_name, next_seq, cache
from (select sequence_owner, sequence_name,
nextvalue next_seq, 'in cache' cache
from v$_sequences
where nextvalue is not null
union
select sequence_owner, sequence_name,
highwater next_seq, 'created nocache' cache
from v$_sequences
where nextvalue is null
union
select sequence_owner, sequence_name,
last_number next_seq, 'not in cache' cache
from dba_sequences s
where not exists (select sequence_owner, sequence_name
from v$_sequences v
where v.sequence_name = s.sequence_name
and v.sequence_owner = s.sequence_owner))
where 1=1
--and sequence_owner like upper('sequence_owner')
and sequence_name like upper('&sequence_name')
order by sequence_owner, sequence_name
/
Saturday, 31 December 2022
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>
Friday, 30 December 2022
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.
Tuesday, 20 December 2022
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.
Wednesday, 14 December 2022
Oracle too many parse errors PARSE ERROR
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, countTo 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 name0x1175e2778 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 ) ;
Tuesday, 29 November 2022
SQL Monitoring
SQL Monitoring Real-Time SQL Monitoring
http://anuj-singh.blogspot.com/2017/08/oracle-active-session-report-ash-report.html
set long 5000 pagesize 300 linesize 300SELECT DBMS_SQLTUNE.report_sql_monitor(type => 'TEXT', report_level => 'ALL') AS report FROM dual;
set lines 1000 pages 9999 col sid for 9999 col serial for 999999 col status for a15 col username for a20 col sql_text for a30 col module for a30 col program for a30 col SQL_EXEC_START for a20 col kill for a17 SELECT * FROM (SELECT ''''||sid ||','|| session_serial#||',@'||inst_id ||'''' kill,con_id,status ,username,sql_id,SQL_PLAN_HASH_VALUE, sql_exec_id , MODULE,program, TO_CHAR(sql_exec_start,'dd-mon-yyyy hh24:mi:ss') AS sql_exec_start, ROUND(elapsed_time/1000000) AS "Elapsed (s)", ROUND(cpu_time /1000000) AS "CPU (s)", substr(sql_text,1,30) sql_text FROM gv$sql_monitor where 1=1 and status='EXECUTING' and module not like '%emagent%' ORDER BY sql_exec_start desc );
SELECT * FROM TABLE(SELECT DBMS_XPLAN.DISPLAY_CURSOR(sql_id,sql_child_number,'+PEEKED_BINDS +PARALLEL +PARTITION ALLSTATS LAST') FROM v$session WHERE sid IN (&1));
set lines 1000 pages 300
SELECT
''''||sid ||','|| session_serial#||',@'||inst_id ||'''' kill,
sql_id,
sql_exec_id,
con_id,
ROUND(elapsed_time /1000000) AS "Elapsed (s)",
ROUND(cpu_time /1000000,3) AS "CPU (s)",
ROUND(queuing_time /1000000,3) AS "Queuing (s)",
ROUND(application_wait_time/1000000,3) AS "Appli wait (s)",
ROUND(concurrency_wait_time/1000000,3) AS "Concurrency wait (s)",
ROUND(cluster_wait_time /1000000,3) AS "Cluster wait (s)",
ROUND(user_io_wait_time /1000000,3) AS "User io wait (s)",
ROUND(physical_read_bytes /(1024*1024)) AS "Phys reads (MB)",
ROUND(physical_write_bytes /(1024*1024)) AS "Phys writes (MB)",
buffer_gets AS "Buffer gets",
ROUND(plsql_exec_time/1000000,3) AS "Plsql exec (s)",
ROUND(java_exec_time /1000000,3) AS "Java exec (s)",
module,
substr(sql_text,1,10) sql_text
FROM gv$sql_monitor
WHERE 1=1
--and sql_id = '8cnh50qfgwg73'
-- AND sql_exec_id = 16777270
-- AND sql_exec_start > SYSTIMESTAMP - INTERVAL '60' second
and status='EXECUTING'
and module not like '%emagent%'
;
set long 30000 pagesize 500 linesize 300
col frm heading from
select * from (select 'gv$sql' frm , sql_fulltext sql_text from gv$sql where sql_id='&&sql_id'
union all
select 'dba_hist', sql_text from dba_hist_sqltext where sql_id='&&sql_id'
);
select xmltype(binds_xml) from gv$sql_monitor where sid = &sid and status = 'EXECUTING';
alter session set "_rowsource_execution_statistics" = TRUE; col PLAN_TABLE_OUTPUT for a200 SELECT * FROM TABLE(dbms_xplan.display_cursor(sql_id => '55w99t6tdk4z3', cursor_child_no => 0, FORMAT => 'ADVANCED +ROWS +BYTES +COST +PARALLEL +PARTITION +IOSTATS +MEMSTATS +ALIAS +PEEKED_BINDS +OUTLINE +PREDICATE -PROJECTION +REMOTE +NOTE'));
alter session set "_rowsource_execution_statistics" = TRUE;
col PLAN_TABLE_OUTPUT for a200
SELECT * FROM TABLE(dbms_xplan.display_cursor(sql_id => '&sql_id', cursor_child_no => 0, FORMAT => 'ADVANCED +ROWS +BYTES +COST +PARALLEL +PARTITION +IOSTATS +MEMSTATS +ALIAS +PEEKED_BINDS +OUTLINE +PREDICATE -PROJECTION +REMOTE +NOTE'));
SET lines 300 pages 1000 LONG 999999 longchunksize 200
SELECT dbms_sqltune.report_sql_monitor_list(sql_id=>'&sql_id',report_level=>'ALL') AS report FROM dual;
SET lines 200 pages 1000 LONG 999999 longchunksize 200
SELECT dbms_sqltune.report_sql_monitor(sql_id=>'5137dabysdzpw',sql_exec_id=>16777218,sql_exec_start=> TO_DATE('29-nov-2022 05:59:27','dd-mon-yyyy hh24:mi:ss')
,report_level=>'ALL') AS report FROM dual;
-- sql html !!! SET LONG 1000000 LONGCHUNKSIZE 1000000 LINESIZE 1000 PAGESIZE 0 TRIM ON TRIMSPOOL ON ECHO OFF FEEDBACK OFF SPOOL report_sql_detail.html SELECT DBMS_SQLTUNE.report_sql_detail( sql_id => '&sql_id', type => 'ACTIVE', report_level => 'ALL' ) AS report FROM dual;
======
select sysdate,sysdate - 30/(24*60) "-30min" ,sysdate - 10/(24*60) "-10min" ,sysdate - 5/(24*60) "-5min" from dual;
SYSDATE -30min -10min -5min
---------------- ---------------- ---------------- ----------------
30-11-2022 13:52 30-11-2022 13:22 30-11-2022 13:42 30-11-2022 13:47
set long 250000 longchunksize 65536 pagesize 100 trimspool on heading off
--set linesize 600
set linesize 254
column text_line format a254
select DBMS_SQLTUNE.REPORT_SQL_MONITOR_LIST(
INST_ID => NULL
, active_since_date => sysdate - 5/(24*60)
, report_level => 'BASIC'
-- , SQL_ID => 'dqbzzupk4xuz5'
,type => 'TEXT'
)
from dual;
set heading on
===
set linesize 255 pagesize 200 trimspool on long 200000
set heading off
column text_line format a254
define m_sql_id = '4mtury8zcpvkx'
spool rep_mon
SELECT dbms_sqltune.report_sql_monitor(
sql_id=> v.sql_id,
sql_exec_id => v.max_sql_exec_id
-- ,active_since_date => sysdate - 5/(24*60)
) text_line
from (
select
sql_id,
max(sql_exec_id) max_sql_exec_id
from v$sql_monitor
where 1=1
and sql_id = '&m_sql_id'
and status like 'DONE%'
group by
sql_id
) v
;
==========
#1: Issue an alter session set "_sqlmon_max_planlines" = 300; <<<< SQL with a plan in excess of 300 lines should not be monitored
to change
alter system set "_sqlmon_max_planlines"=500 scope=memory sid='*';
or
alter session set "_sqlmon_max_planlines"=500;
#2: Use a /*+ monitor */ hint when running the query.
set linesize 300 pagesize 300
col KSPPINM for a35
col KSPPSTVL for a15
col KSPPDESC for a90
select ksppinm, ksppstvl, ksppdesc from sys.x$ksppi a, sys.x$ksppsv b
where a.indx=b.indx
and lower(ksppinm) like lower('%sqlmon%')
order by ksppinm;
KSPPINM KSPPSTVL KSPPDESC
----------------------------------- --------------- ------------------------------------------------------------------------------------------
_sqlmon_binds_xml_format default format of column binds_xml in [G]V$SQL_MONITOR
_sqlmon_max_plan 1600 Maximum number of plans entry that can be monitored. Defaults to 20 per CPU
_sqlmon_max_planlines 300 Number of plan lines beyond which a plan cannot be monitored
_sqlmon_recycle_time 5 Minimum time (in s) to wait before a plan entry can be recycled
_sqlmon_threshold 5 CPU/IO time threshold before a statement is monitored. 0 is disabled
Subscribe to:
Posts (Atom)
