Listener log data mining with SQL
ref ..
https://connor-mcdonald.com/2019/12/16/listener-log-data-mining-with-sql/
https://www.pythian.com/blog/technical-track/how-to-make-an-in-database-listenerlog-file
https://anuj-singh.blogspot.com/2012/01/oracle-how-to-get-only-file-name-with.html
CREATE OR REPLACE DIRECTORY listener_log_xml AS '/u01/app/grid/diag/tnslsnr/irac02/listener/alert';
DROP TABLE listener_log_xml;
CREATE TABLE listener_log_xml (line VARCHAR2(4000))
ORGANIZATION EXTERNAL (
TYPE ORACLE_LOADER
DEFAULT DIRECTORY listener_log_xml
ACCESS PARAMETERS (
RECORDS DELIMITED BY NEWLINE
NOBADFILE
NOLOGFILE
NODISCARDFILE
FIELDS LDRTRIM
MISSING FIELD VALUES ARE NULL
REJECT ROWS WITH ALL NULL FIELDS
(
line CHAR(4000)
)
)
LOCATION ('log.xml')
)
REJECT LIMIT UNLIMITED
/
SELECT * FROM listener_log_xml WHERE line LIKE '%PORT%' AND rownum <= 100
SET LONG 999999999 PAGESIZE 100
select line from (SELECT line FROM listener_log_xml
WHERE 1=1
-- and rownum <= 10000
)
where 1=1
--and REGEXP_LIKE (line,'%PORT%')
and line LIKE '%PORT%'
/
--for IP
SET PAGESIZE 200
COLUMN host FORMAT a30
SELECT DISTINCT host
FROM
( SELECT SUBSTR(host, 1, INSTR(host, ')')-1) AS host
FROM
( SELECT
-- CASE WHEN line LIKE '%HOST=%' THEN SUBSTR(line, INSTR(line, 'HOST=', -1, 1)+5) END host
SUBSTR(line, INSTR(line, 'HOST=', -1, 1)+5) AS host
FROM listener_log_xml
WHERE line LIKE '%PORT%'
)
)
ORDER BY 1
/
--for port
SET PAGESIZE 200
COLUMN host FORMAT a30
SELECT DISTINCT host
FROM
( SELECT SUBSTR(host, 1, INSTR(host, ')')-1) AS host
FROM
( SELECT
-- CASE WHEN line LIKE '%HOST=%' THEN SUBSTR(line, INSTR(line, 'HOST=', -1, 1)+5) END host
SUBSTR(line, INSTR(line, 'PORT=', -1, 1)+5) AS host
FROM listener_log_xml
WHERE line LIKE '%PORT%'
)
)
ORDER BY 1
/
ww
SET LONG 999999999 PAGESIZE 100
select distinct substr(line,instr(line,'HOST',-1)-1) from (SELECT line FROM listener_log_xml
WHERE 1=1
-- and rownum <= 10000
)
where 1=1
--and REGEXP_LIKE (line,'%PORT%')
and line LIKE '%PORT%'
/
Thursday, 30 May 2024
Listener log data mining with SQL
Tuesday, 28 May 2024
Oracle oraset script
#!/bin/bash # Sets Oracle environment variables. # Setup: 1. Put oraset file in /etc (Linux), in /var/opt/oracle (Solaris) # 2. Ensure /etc or /var/opt/oracle is in $PATH # Usage: batch mode: . oraset# menu mode: . oraset #==================================================== if [ -f /etc/oratab ]; then OTAB=/etc/oratab elif [ -f /var/opt/oracle/oratab ]; then OTAB=/var/opt/oracle/oratab else echo 'oratab file not found.' exit fi # if [ -z $1 ]; then SIDLIST=$(egrep -v '#|\*' ${OTAB} | cut -f1 -d:) # PS3 indicates the prompt to be used for the Bash select command. PS3='SID? ' select sid in ${SIDLIST}; do if [ -n $sid ]; then HOLD_SID=$sid break fi done else if egrep -v '#|\*' ${OTAB} | grep -w "${1}:">/dev/null; then HOLD_SID=$1 else echo "SID: $1 not found in $OTAB" fi shift fi # export ORACLE_SID=$HOLD_SID export ORACLE_HOME=$(egrep -v '#|\*' $OTAB|grep -w $ORACLE_SID:|cut -f2 -d:) export ORACLE_BASE=${ORACLE_HOME%%/product*} export TNS_ADMIN=$ORACLE_HOME/network/admin export ADR_BASE=$ORACLE_BASE/diag export PATH=$ORACLE_HOME/bin:/usr/ccs/bin:/opt/SENSsshc/bin/\ :/bin:/usr/bin:.:/var/opt/oracle:/usr/sbin export LD_LIBRARY_PATH=/usr/lib:$ORACLE_HOME/lib
Thursday, 23 May 2024
Oracle sqlplus unix Prompt with colour
red
export PS1='\[\033[0;91m\]\T \d $(echo $ORACLE_SID ) $\[\033[0m\]'
blue
export PS1='\[\033[1;46m\]\T \d $(echo $ORACLE_SID ) $\[\033[0m\]'
Green
export PS1='\[\033[1;42m\]\T \d $(echo $ORACLE_SID ) $\[\033[0m\]'
select chr(27)||'[41m'||chr(27)||'[1;'||to_char(rownum+29)||'mRed' h from dual;
select chr(27)||'[42m'||chr(27)||'[1;'||to_char(rownum+29)||'mGreen' h from dual;
select chr(27)||'[43m'||chr(27)||'[1;'||to_char(rownum+29)||'mYellow' h from dual;
select chr(27)||'[44m'||chr(27)||'[1;'||to_char(rownum+29)||'mBlue' h from dual;
select chr(27)||'[46m'||chr(27)||'[1;'||to_char(rownum+29)||'mBlue' h from dual;
select chr(27)||'[47m'||chr(27)||'[1;'||to_char(rownum+29)||'moffwhite' h from dual;
select chr(27)||'[48m'||chr(27)||'[1;'||to_char(rownum+29)||'moffwhite' h from dual;
select chr(27)||'[49m'||chr(27)||'[1;'||to_char(rownum+29)||'mBlack' h from dual;
select chr(27)||'[50m'||chr(27)||'[1;'||to_char(rownum+29)||'mGreen' h from dual;
select chr(27)||'[51m'||chr(27)||'[1;'||to_char(rownum+29)||'mYellow' h from dual;
select chr(27)||'[42m'||chr(27)||'[1;'||to_char(rownum+38)||'mYellow' h from dual;
-- Green
SET TERMOUT OFF
DEFINE sqlprompt=none
COLUMN sqlprompt NEW_VALUE sqlprompt
SELECT chr(27)||'[42m'||chr(27)||'[1;'||to_char(rownum+29)||'[-'||sys_context('USERENV', 'DATABASE_ROLE')||'-]'||LOWER(SYS_CONTEXT('USERENV','CURRENT_USER')) || '@' || SYS_CONTEXT('USERENV','DB_UNIQUE_NAME') as sqlprompt FROM DUAL;
SET SQLPROMPT '&sqlprompt> '
UNDEFINE sqlprompt
SET TERMOUT ON
-- Orignal
SET TERMOUT OFF
DEFINE sqlprompt=none
COLUMN sqlprompt NEW_VALUE sqlprompt
SELECT chr(27)||'[40m'||chr(27)||'[1;'||to_char(rownum+29)||'[-'||sys_context('USERENV', 'DATABASE_ROLE')||'-]'||LOWER(SYS_CONTEXT('USERENV','CURRENT_USER')) || '@' || SYS_CONTEXT('USERENV','DB_UNIQUE_NAME') as sqlprompt FROM DUAL;
SET SQLPROMPT '&sqlprompt> '
UNDEFINE sqlprompt
SET TERMOUT ON
SET TERMOUT OFF
DEFINE sqlprompt=none
COLUMN sqlprompt NEW_VALUE sqlprompt
SELECT chr(27)||'[38m'||chr(27)||'[1;'||to_char(rownum+29)||'[-'||sys_context('USERENV', 'DATABASE_ROLE')||'-]'||LOWER(SYS_CONTEXT('USERENV','CURRENT_USER')) || '@' || SYS_CONTEXT('USERENV','DB_UNIQUE_NAME') as sqlprompt FROM DUAL;
SET SQLPROMPT '&sqlprompt> '
UNDEFINE sqlprompt
SET TERMOUT ON
SET TERMOUT OFF
DEFINE sqlprompt=none
COLUMN sqlprompt NEW_VALUE sqlprompt
SELECT chr(27)||'[91m'||chr(27)||'[1;'||to_char(rownum+29)||'[-'||sys_context('USERENV', 'DATABASE_ROLE')||'-]'||LOWER(SYS_CONTEXT('USERENV','CURRENT_USER')) || '@' || SYS_CONTEXT('USERENV','DB_UNIQUE_NAME') as sqlprompt FROM DUAL;
SET SQLPROMPT '&sqlprompt> '
UNDEFINE sqlprompt
SET TERMOUT ON
=====
RED !!
SET TERMOUT OFF
DEFINE sqlprompt=none
COLUMN sqlprompt NEW_VALUE sqlprompt
SELECT chr(27)||'[91m'||chr(27)||'[1;'||to_char(rownum+29)||'[-'||sys_context('USERENV', 'DATABASE_ROLE')||'-]'||LOWER(SYS_CONTEXT('USERENV','CURRENT_USER')) || '@' || SYS_CONTEXT('USERENV','DB_UNIQUE_NAME') as sqlprompt FROM DUAL;
SET SQLPROMPT '&sqlprompt> '
UNDEFINE sqlprompt
SET TERMOUT ON
# Bold
BBlack="\[\033[1;30m\]" # Black
BRed="\[\033[1;31m\]" # Red
BGreen="\[\033[1;32m\]" # Green
BYellow="\[\033[1;33m\]" # Yellow
BBlue="\[\033[1;34m\]" # Blue
BPurple="\[\033[1;35m\]" # Purple
BCyan="\[\033[1;36m\]" # Cyan
BWhite="\[\033[1;37m\]" # White
# Underline
UBlack="\[\033[4;30m\]" # Black
URed="\[\033[4;31m\]" # Red
UGreen="\[\033[4;32m\]" # Green
UYellow="\[\033[4;33m\]" # Yellow
UBlue="\[\033[4;34m\]" # Blue
UPurple="\[\033[4;35m\]" # Purple
UCyan="\[\033[4;36m\]" # Cyan
UWhite="\[\033[4;37m\]" # White
# Background
On_Black="\[\033[40m\]" # Black
On_Red="\[\033[41m\]" # Red
On_Green="\[\033[42m\]" # Green
On_Yellow="\[\033[43m\]" # Yellow
On_Blue="\[\033[44m\]" # Blue
On_Purple="\[\033[45m\]" # Purple
On_Cyan="\[\033[46m\]" # Cyan
On_White="\[\033[47m\]" # White
# High Intensty
IBlack="\[\033[0;90m\]" # Black
IRed="\[\033[0;91m\]" # Red
IGreen="\[\033[0;92m\]" # Green
IYellow="\[\033[0;93m\]" # Yellow
IBlue="\[\033[0;94m\]" # Blue
IPurple="\[\033[0;95m\]" # Purple
ICyan="\[\033[0;96m\]" # Cyan
IWhite="\[\033[0;97m\]" # White
# Bold High Intensty
BIBlack="\[\033[1;90m\]" # Black
BIRed="\[\033[1;91m\]" # Red
BIGreen="\[\033[1;92m\]" # Green
BIYellow="\[\033[1;93m\]" # Yellow
BIBlue="\[\033[1;94m\]" # Blue
BIPurple="\[\033[1;95m\]" # Purple
BICyan="\[\033[1;96m\]" # Cyan
BIWhite="\[\033[1;97m\]" # White
# High Intensty backgrounds
On_IBlack="\[\033[0;100m\]" # Black
On_IRed="\[\033[0;101m\]" # Red
On_IGreen="\[\033[0;102m\]" # Green
On_IYellow="\[\033[0;103m\]" # Yellow
On_IBlue="\[\033[0;104m\]" # Blue
On_IPurple="\[\033[10;95m\]" # Purple
On_ICyan="\[\033[0;106m\]" # Cyan
On_IWhite="\[\033[0;107m\]" # White=========
export PS1='\n[\u@\h DB:$( echo $ORACLE_SID ) \t \w]\n\$ '
Wednesday, 15 May 2024
ORA-32015: unable to restore SPFILE
How to restore spfile from Rman backup ? ..
RMAN>
run {
set dbid=2798080093;
set controlfile autobackup format for device type disk to '/u01/app/Rman/%F';
restore spfile from autobackup;
startup force nomount ;
restore controlfile from autobackup;
alter database mount ;
}
RMAN> 2> 3> 4> 5> 6> 7> 8>
executing command: SET DBID
executing command: SET CONTROLFILE AUTOBACKUP FORMAT
Starting restore at 15-MAY-2024 10:39:10
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=260 device type=DISK
recovery area destination: /u01/app/Rman
database name (or database unique name) used for search: ORADB
channel ORA_DISK_1: no AUTOBACKUPS found in the recovery area
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20240515
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20240514
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20240513
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20240512
channel ORA_DISK_1: AUTOBACKUP found: /u01/app/Rman/c-2798080093-20240512-08
channel ORA_DISK_1: restoring spfile from AUTOBACKUP /u01/app/Rman/c-2798080093-20240512-08
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 05/15/2024 10:39:14
ORA-19870: error while restoring backup piece /u01/app/Rman/c-2798080093-20240512-08
ORA-32015: unable to restore SPFILE
ORA-17502: ksfdcre:4 Failed to create file +DATADISK/ORADB/PARAMETERFILE/spfile.273.1168791713
ORA-15046: ASM file name '+DATADISK/ORADB/PARAMETERFILE/spfile.273.1168791713' is not in single-file creation form
remove spfile !!!
srvctl modify database -d oradb -spfile ''
====
Start again !!!!
rman target /
Recovery Manager: Release 19.0.0.0.0 - Production on Wed May 15 12:25:59 2024
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to target database (not started)
RMAN> startup force nomount;
Oracle instance started
Total System Global Area 4026531824 bytes
Fixed Size 9142256 bytes
Variable Size 805306368 bytes
Database Buffers 3204448256 bytes
Redo Buffers 7634944 bytes
RMAN> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST='/u01/app/Rman' ;
using target database control file instead of recovery catalog
Statement processed
RMAN> ALTER SYSTEM SET DB_CREATE_FILE_DEST='/u01/app';
Statement processed
RMAN> run {
set dbid=2798080093;
set controlfile autobackup format for device type disk to '/u01/app/Rman/%F';
restore spfile from autobackup;
startup force nomount ;
restore controlfile from autobackup;
alter database mount ;
}2> 3> 4> 5> 6> 7> 8>
executing command: SET DBID
executing command: SET CONTROLFILE AUTOBACKUP FORMAT
Starting restore at 15-MAY-2024 12:27:55
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=24 device type=DISK
recovery area destination: /u01/app/Rman
database name (or database unique name) used for search: ORADB
channel ORA_DISK_1: no AUTOBACKUPS found in the recovery area
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20240515
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20240514
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20240513
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20240512
channel ORA_DISK_1: AUTOBACKUP found: /u01/app/Rman/c-2798080093-20240512-08
channel ORA_DISK_1: restoring spfile from AUTOBACKUP /u01/app/Rman/c-2798080093-20240512-08
channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete
Finished restore at 15-MAY-2024 12:27:57
Oracle instance started
Total System Global Area 4026531824 bytes
Fixed Size 9142256 bytes
Variable Size 805306368 bytes
Database Buffers 3204448256 bytes
Redo Buffers 7634944 bytes
Starting restore at 15-MAY-2024 12:28:35
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=24 device type=DISK
recovery area destination: +DATADISK
database name (or database unique name) used for search: ORADB
channel ORA_DISK_1: no AUTOBACKUPS found in the recovery area
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20240515
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20240514
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20240513
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20240512
channel ORA_DISK_1: AUTOBACKUP found: /u01/app/Rman/c-2798080093-20240512-08
channel ORA_DISK_1: restoring control file from AUTOBACKUP /u01/app/Rman/c-2798080093-20240512-08
channel ORA_DISK_1: control file restore from AUTOBACKUP complete
output file name=+DATADISK/ORADB/CONTROLFILE/current.273.1169036919
Finished restore at 15-MAY-2024 12:28:39
released channel: ORA_DISK_1
Statement processed
*************************************************************************
Oracle Database mouted
SQL> show parameter spfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /u01/app/oracle/product/19.0.0
/db_1/dbs/spfileoradb.ora
SQL> show parameter control
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time integer 7
control_files string +DATADISK/ORADB/CONTROLFILE/cu
rrent.273.1169036919
control_management_pack_access string DIAGNOSTIC+TUNING
SQL>
RMAN> LIST BACKUP TAG RMANFULL;
List of Backup Sets
===================
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ --------------------
42 37.60M DISK 00:00:02 12-MAY-2024 17:11:28
BP Key: 42 Status: AVAILABLE Compressed: NO Tag: RMANFULL
Piece Name: /u01/app/Rman/ORADB_T20240512_arch_s46_p1_t1168794686
.
.
.
.
.
List of Archived Logs in backup set 68
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- -------------------- ---------- ---------
1 2 2557217 12-MAY-2024 17:11:22 2557230 12-MAY-2024 17:11:23
1 3 2557230 12-MAY-2024 17:11:23 2557240 12-MAY-2024 17:11:26
1 4 2557240 12-MAY-2024 17:11:26 2557313 12-MAY-2024 17:11:42
1 5 2557313 12-MAY-2024 17:11:42 2557394 12-MAY-2024 17:13:00
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ --------------------
69 8.00K DISK 00:00:00 12-MAY-2024 17:17:37
BP Key: 69 Status: AVAILABLE Compressed: NO Tag: RMANFULL
Piece Name: /u01/app/Rman/ORADB_T20240512_arch_s73_p1_t1168795057
List of Archived Logs in backup set 69
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- -------------------- ---------- ---------
1 14 2557801 12-MAY-2024 17:16:51 2557829 12-MAY-2024 17:17:24
1 15 2557829 12-MAY-2024 17:17:24 2557853 12-MAY-2024 17:17:27
1 16 2557853 12-MAY-2024 17:17:27 2557864 12-MAY-2024 17:17:28
1 17 2557864 12-MAY-2024 17:17:28 2557872 12-MAY-2024 17:17:32 <<<<< check this date
RUN
{
set until time "to_date('12.05.2024 17:17:32','DD.MM.YYYY HH24:MI:SS')";
restore database;
recover database;
alter database open resetlogs;
}
output !!!
===============
RMAN>
RUN
{
set until time "to_date('12.05.2024 17:17:32','DD.MM.YYYY HH24:MI:SS')";
restore database;
recover database;
alter database open resetlogs;
}
RMAN> 2> 3> 4> 5> 6> 7>
executing command: SET until clause
Starting restore at 15-MAY-2024 13:52:51
Starting implicit crosscheck backup at 15-MAY-2024 13:52:51
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=251 device type=DISK
Crosschecked 70 objects
Finished implicit crosscheck backup at 15-MAY-2024 13:52:53
Starting implicit crosscheck copy at 15-MAY-2024 13:52:53
using channel ORA_DISK_1
Finished implicit crosscheck copy at 15-MAY-2024 13:52:53
searching for all files in the recovery area
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: +DATADISK/ORADB/ARCHIVELOG/2024_05_12/thread_1_seq_18.296.1168795063
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 +DATADISK/ORADB/DATAFILE/system.261.1168789109
channel ORA_DISK_1: restoring datafile 00003 to +DATADISK/ORADB/DATAFILE/sysaux.260.1168789125
channel ORA_DISK_1: restoring datafile 00004 to +DATADISK/ORADB/DATAFILE/undotbs1.257.1168789135
channel ORA_DISK_1: restoring datafile 00007 to +DATADISK/ORADB/DATAFILE/users.259.1168789149
channel ORA_DISK_1: reading from backup piece /u01/app/Rman/ORADB_T20240512_db_s64_p1_t1168795013
channel ORA_DISK_1: piece handle=/u01/app/Rman/ORADB_T20240512_db_s64_p1_t1168795013 tag=RMANFULL
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:36
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 +DATADISK/ORADB/A98B865CBFAF359CE0537F01A8C08251/DATAFILE/system.271.1168789145
channel ORA_DISK_1: restoring datafile 00010 to +DATADISK/ORADB/A98B865CBFAF359CE0537F01A8C08251/DATAFILE/sysaux.256.1168789133
channel ORA_DISK_1: restoring datafile 00011 to +DATADISK/ORADB/A98B865CBFAF359CE0537F01A8C08251/DATAFILE/undotbs1.275.1168789149
channel ORA_DISK_1: restoring datafile 00012 to +DATADISK/ORADB/A98B865CBFAF359CE0537F01A8C08251/DATAFILE/users.276.1168789151
channel ORA_DISK_1: reading from backup piece /u01/app/Rman/ORADB_T20240512_db_s65_p1_t1168795029
channel ORA_DISK_1: piece handle=/u01/app/Rman/ORADB_T20240512_db_s65_p1_t1168795029 tag=RMANFULL
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
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 +DATADISK/ORADB/A98B64293AC52C90E0537F01A8C075D9/DATAFILE/system.270.1168789141
channel ORA_DISK_1: restoring datafile 00006 to +DATADISK/ORADB/A98B64293AC52C90E0537F01A8C075D9/DATAFILE/sysaux.258.1168789139
channel ORA_DISK_1: restoring datafile 00008 to +DATADISK/ORADB/A98B64293AC52C90E0537F01A8C075D9/DATAFILE/undotbs1.269.1168789147
channel ORA_DISK_1: reading from backup piece /u01/app/Rman/ORADB_T20240512_db_s66_p1_t1168795037
channel ORA_DISK_1: piece handle=/u01/app/Rman/ORADB_T20240512_db_s66_p1_t1168795037 tag=RMANFULL
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
Finished restore at 15-MAY-2024 13:53:45
Starting recover at 15-MAY-2024 13:53:45
using channel ORA_DISK_1
starting media recovery
archived log for thread 1 with sequence 14 is already on disk as file +DATADISK/ORADB/ARCHIVELOG/2024_05_12/thread_1_seq_14.292.1168795045
archived log for thread 1 with sequence 15 is already on disk as file +DATADISK/ORADB/ARCHIVELOG/2024_05_12/thread_1_seq_15.293.1168795047
archived log for thread 1 with sequence 16 is already on disk as file +DATADISK/ORADB/ARCHIVELOG/2024_05_12/thread_1_seq_16.294.1168795049
archived log for thread 1 with sequence 17 is already on disk as file +DATADISK/ORADB/ARCHIVELOG/2024_05_12/thread_1_seq_17.295.1168795053
archived log file name=+DATADISK/ORADB/ARCHIVELOG/2024_05_12/thread_1_seq_14.292.1168795045 thread=1 sequence=14
archived log file name=+DATADISK/ORADB/ARCHIVELOG/2024_05_12/thread_1_seq_15.293.1168795047 thread=1 sequence=15
archived log file name=+DATADISK/ORADB/ARCHIVELOG/2024_05_12/thread_1_seq_16.294.1168795049 thread=1 sequence=16
archived log file name=+DATADISK/ORADB/ARCHIVELOG/2024_05_12/thread_1_seq_17.295.1168795053 thread=1 sequence=17
media recovery complete, elapsed time: 00:00:01
Finished recover at 15-MAY-2024 13:53:49
Statement processed
RMAN>
RMAN>
Sunday, 12 May 2024
How to Migrate Control File From File System to ASM ?
select * from v$controlfile;
STATUS
-------
NAME
--------------------------------------------------------------------------------
IS_ BLOCK_SIZE FILE_SIZE_BLKS CON_ID
--- ---------- -------------- ----------
/u01/app/control01-1205.ctl
NO 16384 1142 0
shutdown immediate;
startup nomount;
restore controlfile to '+DATADISK' from '/u01/app/control01-1205.ctl';
RMAN> restore controlfile to '+DATADISK' from '/u01/app/control01-1205.ctl';
Starting restore at 12-MAY-2024 16:04:35
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=237 device type=DISK
channel ORA_DISK_1: copied control file copy
Finished restore at 12-MAY-2024 16:04:36
RMAN>
from Grid find control file
find the control file !!!
ASMCMD> find -t controlfile . *
ASMCMD [+] > find --type controlfile . *
+DATADISK/ORADB/CONTROLFILE/current.272.1168790677
ASMCMD [+] > ls -s +DATADISK/ORADB/CONTROLFILE/current.272.1168790677
Block_Size Blocks Bytes Space Name
16384 1143 18726912 33554432 current.272.1168790677
ASMCMD [+] > ls -l +DATADISK/ORADB/CONTROLFILE/current.272.1168790677
Type Redund Striped Time Sys Name
CONTROLFILE UNPROT FINE MAY 12 16:00:00 Y current.272.1168790677
ASMCMD [+] >
[oracle@srv1 dbs]$ srvctl start database -d oradb -o nomount
[oracle@srv1 dbs]$ sqlplus / as sysdba
create spfile
SQL> create spfile='+DATADISK/spfileoradb.ora' from pfile='initoradb.ora';
File created.
ASMCMD [+] > ls -lt +DATADISK/spfileoradb.ora
Type Redund Striped Time Sys Name
PARAMETERFILE UNPROT COARSE MAY 12 16:00:00 N spfileoradb.ora => +DATADISK/ORADB/PARAMETERFILE/spfile.273.1168791713
ASMCMD [+] >
srvctl modify database -d oradb -spfile +DATADISK/ORADB/PARAMETERFILE/spfile.273.1168791713
srvctl start database -d oradb -o nomount
alter the spfile for control file
[oracle@srv1 dbs]$
alter system set control_files='+DATADISK/ORADB/CONTROLFILE/current.272.1168790677' scope=spfile;
srvctl stop database -d oradb
srvctl start database -d oradb
[oracle@srv1 dbs]$ !sql
sqlplus / as sysdba
SQL> show parameter spfile;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string +DATADISK/ORADB/PARAMETERFILE/
spfile.273.1168791713
SQL> show parameter control;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time integer 7
control_files string +DATADISK/ORADB/CONTROLFILE/cu
rrent.272.1168790677
control_management_pack_access string DIAGNOSTIC+TUNING
SQL>
[oracle@srv1 dbs]$ srvctl config database -d oradb
Database unique name: oradb
Database name: oradb
Oracle home: /u01/app/oracle/product/19.0.0/db_1
Oracle user: oracle
Spfile: +DATADISK/ORADB/PARAMETERFILE/spfile.273.1168791713
Password file:
Domain: localdomain
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Disk Groups: DATADISK
Services:
OSDBA group: oinstall
OSOPER group:
Database instance: oradb
Sunday, 5 May 2024
How to Patch Oracle Grid Infrastructure 19c ?
How to Patch Oracle Grid Infrastructure 19c ?
Assistant: Download Reference for Oracle Database/GI Update, Revision, PSU, SPU(CPU), Bundle Patches, Patchsets and Base Releases (Doc ID 2118136.2)-----crsctl config has CRS-4622: Oracle High Availability Services autostart is enabled. ======= RAC: Frequently Asked Questions (RAC FAQ) (Doc ID 220970.1) p6880880_210000_Linux-x86-64.zip p34762026_190000_Linux-x86-64.zip >>>>>2.8GB file unzip p34762026_190000_Linux-x86-64.zip Archive: p34762026_190000_Linux-x86-64.zip creating: 34762026/ inflating: 34762026/bundle.xml creating: 34762026/34768569/ creating: 34762026/34768569/files/ [grid@srv1 ~]$ cd 34762026 [grid@srv1 34762026]$ pwd /home/grid/34762026 [grid@srv1 34762026]$ ls -ltr total 168 drwxr-x---. 5 grid oinstall 62 Feb 1 2023 34768569 drwxr-x---. 5 grid oinstall 81 Feb 1 2023 34765931 drwxr-x---. 4 grid oinstall 48 Feb 1 2023 33575402 -rw-r--r--. 1 grid oinstall 0 Feb 1 2023 README.txt drwxr-x---. 2 grid oinstall 4096 Feb 1 2023 automation drwxr-x---. 4 grid oinstall 48 Feb 1 2023 34863894 drwxr-x---. 5 grid oinstall 62 Feb 1 2023 34768559 -rw-rw-r--. 1 grid oinstall 5824 Feb 1 2023 bundle.xml -rw-rw-r--. 1 grid oinstall 156720 Feb 1 2023 README.html patch conflicts check. $ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir /home/grid/34762026/34768569 | grep checkConflictAgainstOHWithDetail $ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir /home/grid/34762026/34765931 | grep checkConflictAgainstOHWithDetail $ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir /home/grid/34762026/33575402 | grep checkConflictAgainstOHWithDetail $ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir /home/grid/34762026/34863894 | grep checkConflictAgainstOHWithDetail $ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir /home/grid/34762026/34768559 | grep checkConflictAgainstOHWithDetail [grid@srv1 34762026]$ [grid@srv1 34762026]$ $ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir /home/grid/34762026/34768569 | grep checkConflictAgainstOHWithDetail Prereq "checkConflictAgainstOHWithDetail" passed. [grid@srv1 34762026]$ [grid@srv1 34762026]$ [grid@srv1 34762026]$ $ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir /home/grid/34762026/34768569 | grep checkConflictAgainstOHWithDetail Prereq "checkConflictAgainstOHWithDetail" passed. [grid@srv1 34762026]$ [grid@srv1 34762026]$ $ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir /home/grid/34762026/34765931 | grep checkConflictAgainstOHWithDetail Prereq "checkConflictAgainstOHWithDetail" passed. [grid@srv1 34762026]$ [grid@srv1 34762026]$ $ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir /home/grid/34762026/33575402 | grep checkConflictAgainstOHWithDetail Prereq "checkConflictAgainstOHWithDetail" passed. [grid@srv1 34762026]$ [grid@srv1 34762026]$ $ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir /home/grid/34762026/34863894 | grep checkConflictAgainstOHWithDetail Prereq "checkConflictAgainstOHWithDetail" passed. [grid@srv1 34762026]$ [grid@srv1 34762026]$ $ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir /home/grid/34762026/34768559 | grep checkConflictAgainstOHWithDetail Prereq "checkConflictAgainstOHWithDetail" passed. [grid@srv1 34762026]$ echo $ORACLE_HOME /u01/app/19.0.0/grid --- stop datacases only !!!!!!! as root to patch GI home: [root@srv1 ~]# cd /home/grid export ORACLE_HOME=/u01/app/19.0.0/grid export PATH=$ORACLE_HOME/OPatch:$PATH $ORACLE_HOME/OPatch/opatchauto apply /home/grid/34762026 -oh $ORACLE_HOME [root@srv1 ~]# export ORACLE_HOME=/u01/app/19.0.0/grid [root@srv1 ~]# export PATH=$ORACLE_HOME/OPatch:$PATH [root@srv1 ~]# echo $ORACLE_HOME /u01/app/19.0.0/grid [root@srv1 ~]# $ORACLE_HOME/OPatch/opatchauto apply /home/grid/34762026 -oh $ORACLE_HOME opatch lsinventory | grep 19.18 [root@srv1 grid]# $ORACLE_HOME/OPatch/opatchauto apply /home/grid/34762026 -oh $ORACLE_HOME OPatchauto session is initiated at Sun May 5 09:06:22 2024 System initialization log file is /u01/app/19.0.0/grid/cfgtoollogs/opatchautodb/systemconfig2024-05-05_09-06-32AM.log. Session log file is /u01/app/19.0.0/grid/cfgtoollogs/opatchauto/opatchauto2024-05-05_09-06-38AM.log The id for this session is EU5X Executing OPatch prereq operations to verify patch applicability on home /u01/app/19.0.0/grid Patch applicability verified successfully on home /u01/app/19.0.0/grid Executing patch validation checks on home /u01/app/19.0.0/grid Patch validation checks successfully completed on home /u01/app/19.0.0/grid Performing prepatch operations on CRS - bringing down CRS service on home /u01/app/19.0.0/grid Prepatch operation log file location: /u01/app/grid/crsdata/srv1/crsconfig/hapatch_2024-05-05_09-08-01AM.log CRS service brought down successfully on home /u01/app/19.0.0/grid Start applying binary patch on home /u01/app/19.0.0/grid Binary patch applied successfully on home /u01/app/19.0.0/grid Running rootadd_rdbms.sh on home /u01/app/19.0.0/grid Successfully executed rootadd_rdbms.sh on home /u01/app/19.0.0/grid Performing postpatch operations on CRS - starting CRS service on home /u01/app/19.0.0/grid Postpatch operation log file location: /u01/app/grid/crsdata/srv1/crsconfig/hapatch_2024-05-05_09-19-15AM.log CRS service started successfully on home /u01/app/19.0.0/grid OPatchAuto successful. --------------------------------Summary-------------------------------- Patching is completed successfully. Please find the summary as follows: Host:srv1 SIHA Home:/u01/app/19.0.0/grid Version:19.0.0.0.0 Summary: ==Following patches were SUCCESSFULLY applied: Patch: /home/grid/34762026/33575402 Log: /u01/app/19.0.0/grid/cfgtoollogs/opatchauto/core/opatch/opatch2024-05-05_09-08-32AM_1.log Patch: /home/grid/34762026/34765931 Log: /u01/app/19.0.0/grid/cfgtoollogs/opatchauto/core/opatch/opatch2024-05-05_09-08-32AM_1.log Patch: /home/grid/34762026/34768559 Log: /u01/app/19.0.0/grid/cfgtoollogs/opatchauto/core/opatch/opatch2024-05-05_09-08-32AM_1.log Patch: /home/grid/34762026/34768569 Log: /u01/app/19.0.0/grid/cfgtoollogs/opatchauto/core/opatch/opatch2024-05-05_09-08-32AM_1.log Patch: /home/grid/34762026/34863894 Log: /u01/app/19.0.0/grid/cfgtoollogs/opatchauto/core/opatch/opatch2024-05-05_09-08-32AM_1.log OPatchauto session completed at Sun May 5 09:21:37 2024 Time taken to complete the session 15 minutes, 6 seconds ====================== as grid user [grid@srv1 ~]$ $ORACLE_HOME/OPatch/opatch lsinventory|grep -i 19.18 Patch description: "ACFS RELEASE UPDATE 19.18.0.0.0 (34768569)" Patch description: "OCW RELEASE UPDATE 19.18.0.0.0 (34768559)" Patch description: "DATABASE RELEASE UPDATE : 19.18.0.0.230117 (REL-JAN230131) (34765931)" 32218395, 32218498, 32218552, 32219179, 32219318, 32219835, 32219988 [grid@srv1 ~]$ $ORACLE_HOME/OPatch/opatch lsinventory|grep -i 19.18 Patch description: "ACFS RELEASE UPDATE 19.18.0.0.0 (34768569)" Patch description: "OCW RELEASE UPDATE 19.18.0.0.0 (34768559)" Patch description: "DATABASE RELEASE UPDATE : 19.18.0.0.230117 (REL-JAN230131) (34765931)" 32218395, 32218498, 32218552, 32219179, 32219318, 32219835, 32219988 [grid@srv1 ~]$ $ORACLE_HOME/OPatch/opatch lsinventory|grep -E "(^Patch.*applied)|(^Sub-patch)" Patch 34863894 : applied on Sun May 05 09:18:11 GST 2024 Patch 34768569 : applied on Sun May 05 09:17:40 GST 2024 Patch 34768559 : applied on Sun May 05 09:17:12 GST 2024 Patch 34765931 : applied on Sun May 05 09:12:51 GST 2024 Patch 33575402 : applied on Sun May 05 09:10:03 GST 2024 [grid@srv1 ~]$
rid@srv1 ~]$ crsctl query has releaseversion Oracle High Availability Services release version on the local node is [19.0.0.0.0][grid@srv1 ~]$ crsctl query has softwareversion Oracle High Availability Services version on the local node is [19.0.0.0.0][grid@srv1 ~]$ crsctl query has releasepatch Oracle Clusterware release patch level is [3161362881] and the complete list of patches [33575402 34765931 34768559 34768569 34863894 ] have been applied on the local node. The release patch string is [19.18.0.0.0].[grid@srv1 ~]$ crsctl query has softwarepatch Oracle Clusterware patch level on node srv1 is [3161362881].
crsctl stat res -t – to check the status of the resources in the Oracle Restart stack managed by OHASD
crsctl status res -t
--------------------------------------------------------------------------------
Name Target State Server State details
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.DATADISK.dg
ONLINE ONLINE srv1 STABLE
ora.LISTENER.lsnr
ONLINE ONLINE srv1 STABLE
ora.OCRDISK.dg
ONLINE ONLINE srv1 STABLE
ora.asm
ONLINE ONLINE srv1 Started,STABLE
ora.ons
OFFLINE OFFLINE srv1 STABLE
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.cssd
1 ONLINE ONLINE srv1 STABLE
ora.diskmon
1 OFFLINE OFFLINE STABLE
ora.evmd
1 ONLINE ONLINE srv1 STABLE
ora.oradb.db
1 OFFLINE OFFLINE Instance Shutdown,ST
ABLE
--------------------------------------------------------------------------------
[grid@srv1 ~]$
to stop has
[grid@srv1 ~]$ crsctl stop has CRS-2791: Starting shutdown of Oracle High Availability Services-managed resources on 'srv1' CRS-2673: Attempting to stop 'ora.DATADISK.dg' on 'srv1' CRS-2673: Attempting to stop 'ora.LISTENER.lsnr' on 'srv1' CRS-2677: Stop of 'ora.LISTENER.lsnr' on 'srv1' succeeded CRS-2677: Stop of 'ora.DATADISK.dg' on 'srv1' succeeded CRS-2673: Attempting to stop 'ora.OCRDISK.dg' on 'srv1' CRS-2677: Stop of 'ora.OCRDISK.dg' on 'srv1' succeeded CRS-2673: Attempting to stop 'ora.asm' on 'srv1' CRS-2677: Stop of 'ora.asm' on 'srv1' succeeded CRS-2673: Attempting to stop 'ora.evmd' on 'srv1' CRS-2677: Stop of 'ora.evmd' on 'srv1' succeeded CRS-2673: Attempting to stop 'ora.cssd' on 'srv1' CRS-2677: Stop of 'ora.cssd' on 'srv1' succeeded CRS-2793: Shutdown of Oracle High Availability Services-managed resources on 'srv1' has completed CRS-4133: Oracle High Availability Services has been stopped. [grid@srv1 ~]$
crsctl start has – to manually start the Oracle Restart stack when running disabled or after manually stopping it crsctl stop has [-f] – to manually stop the Oracle Restart stack. The -f option crsctl enable has – to enable the stack for automatic startup at server reboot crsctl disable has – to disable the stack for automatic startup at server reboot crsctl config has – to display the configuration of Oracle Restart crsctl check has – to check the current status of Restart Some of the crsctl commands used for clusters may also be used for Oracle Restart. A typical example follows but there are many more:
====== Patch 35940989 is a superset of patch 35642822 Note This patch was originally replaced by patch 35940989. The most recent replacement for this patch is 36916690. Replacement Options (Patches or Patchsets known to Include or Supersede this Patch) 35940989 GI RELEASE UPDATE 19.22.0.0.0 Patch
 |
|
Subscribe to:
Comments (Atom)
