vi aoratab
#!/bin/ksh
# To view / change current Oracle SID and ORALCE_HOME, PATH
#
# Biju Thomas - 10/15/97
# Modified - 13/02/98 - Add PATH feature!
#
echo ' '
echo '********************************************************************************'
echo ' The current ORACLE_SID value is ' $ORACLE_SID
echo '********************************************************************************'
echo ' The following are valid instance names along with the ORACLE_HOME value:'
echo ' '
wcntr=0
while read LINE
do
case $LINE in
\#*) ;; #comment-line in oratab
*)
# Proceed only if third field is 'Y'.
#if [ "`echo $LINE | awk -F: '{print $3}' -`" = "Y" ] ; then
wcntr=`expr $wcntr + 1`
OSID=`echo $LINE | awk -F: '{print $1}' -`
if [ "$ORACLE_SID" = '*' ] ; then
echo ' *** Unknown Value ***'
fi
OHOME=`echo $LINE | awk -F: '{print $2}' -`
echo ' Slno:' $wcntr ' SID Name:' $OSID ' Oracle Home:' $OHOME
#fi
esac
done < /etc/oratab
echo ' '
echo '********************************************************************************'
echo ' '
echo ' Do you want to change ORACLE_SID [Y/N]? \c'
read choice
case $choice in
Y|y) continue;;
*) return 0;;
esac
echo ' Enter Slno of the SID from above list : \c'
read choice
if test $choice -lt 0 || test $choice -gt $wcntr
then
echo ' Invalid choice.... Sorry!!!'
return 1
fi
wcntr=0
while read LINE
do
case $LINE in
\#*) ;; #comment-line in oratab
*)
# Proceed only if third field is 'Y'.
#if [ "`echo $LINE | awk -F: '{print $3}' -`" = "Y" ] ; then
wcntr=`expr $wcntr + 1`
if test $wcntr -eq $choice
then
ORACLE_SID=`echo $LINE | awk -F: '{print $1}' -`
if [ "$ORACLE_SID" = '*' ] ; then
ORACLE_SID=''
fi
ORACLE_HOME=`echo $LINE | awk -F: '{print $2}' -`
export ORACLE_HOME ORACLE_SID
break;
fi
#fi
esac
done < /etc/oratab
#
# Change PATH with new oracle home
#
path=`echo $PATH | sed "s/:/ /g"`
NEWPATH=$ORACLE_HOME/bin
for dirname in $path
do
if [ `echo $dirname | grep oracle | grep bin | wc -l` -eq 0 ]
then
NEWPATH=`echo ${NEWPATH}:${dirname}`
fi
done
export PATH=$NEWPATH
export ORA_NLS=
#
echo ' '
echo '********************************************************************************'
echo ' New value of ORACLE_SID is ' $ORACLE_SID
echo ' New value of ORACLE_HOME is ' $ORACLE_HOME
echo ' New value of PATH is ' $PATH
echo '********************************************************************************'
echo ' '
#
====
[root@rac01 ~]# . aoratab
********************************************************************************
The current ORACLE_SID value is
********************************************************************************
The following are valid instance names along with the ORACLE_HOME value:
Slno: 1 SID Name: Oracle Home:
Slno: 2 SID Name: Oracle Home:
Slno: 3 SID Name: Oracle Home:
Slno: 4 SID Name: Oracle Home:
Slno: 5 SID Name: ora11g Oracle Home: /u01/app/oracle/product/11.2.0/dbhome_1
Slno: 6 SID Name: ibrac1 Oracle Home: /u01/app/oracle/product/12.2.0/dbhome_1
Slno: 7 SID Name: -MGMTDB Oracle Home: /u01/app/18.0.0/grid
Slno: 8 SID Name: LRS Oracle Home: /u01/app/oracle/product/12.2.0/dbhome_1
Slno: 9 SID Name: ugarb Oracle Home: /u01/app/oracle/product/12.1.0/dbhome_1
Slno: 10 SID Name: ugarbx Oracle Home: /u01/app/oracle/product/12.1.0/dbhome_1
Slno: 11 SID Name: ugary Oracle Home: /u01/app/oracle/product/12.1.0/dbhome_1
Slno: 12 SID Name: ugarx Oracle Home: /u01/app/oracle/product/12.1.0/dbhome_1
Slno: 13 SID Name: -MGMTDB Oracle Home: /u01/app/19.0.0/grid
Slno: 14 SID Name: ugari Oracle Home: /u01/app/oracle/product/12.1.0/dbhome_1
Slno: 15 SID Name: +ASM1 Oracle Home: /u01/app/19.0.0/grid
Slno: 16 SID Name: garbage Oracle Home: /u01/app/oracle/product/12.1.0/dbhome_1
Slno: 17 SID Name: ugaryd Oracle Home: /u01/app/oracle/product/12.1.0/dbhome_1
Slno: 18 SID Name: Oracle Home:
********************************************************************************
Do you want to change ORACLE_SID [Y/N]? \c
Y
-bash: continue: only meaningful in a `for', `while', or `until' loop
Enter Slno of the SID from above list : \c
5
********************************************************************************
New value of ORACLE_SID is ora11g
New value of ORACLE_HOME is /u01/app/oracle/product/11.2.0/dbhome_1
New value of PATH is /u01/app/oracle/product/11.2.0/dbhome_1/bin:/usr/lib64/qt-3.3/bin:/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin:/root/bin
********************************************************************************
Saturday, 22 June 2024
Oracle new oratab --
From web
Wednesday, 5 June 2024
Oracle partition export and import using datapump
Oracle partition export and import using datapump via parfile ....
SQL> create user user1 identified by user1;
SQL> create user user2 identified by user2;
grant connect, resource to user1, user2;
2.Create datapump directory.
CREATE OR REPLACE DIRECTORY EXPDP_DIR AS '/dumps/expdp';
GRANT READ, WRITE ON DIRECTORY EXPDP_DIR to user1,user2;
3.Create partition table SALES.
alter session set current_schema=user1;
or
conn user1/user1
Create table
CREATE TABLE sales
( name varchar2(5),
time_id DATE)
PARTITION BY RANGE (time_id)
(PARTITION sales_AUG VALUES LESS THAN (TO_DATE('01-AUG-2014','dd-MON-yyyy'))
TABLESPACE users,
PARTITION sales_SEP VALUES LESS THAN (TO_DATE('01-SEP-2014','dd-MON-yyyy'))
TABLESPACE users,
PARTITION sales_OCT VALUES LESS THAN (TO_DATE('01-OCT-2014','dd-MON-yyyy'))
TABLESPACE users,
PARTITION sales_NOV VALUES LESS THAN (TO_DATE('01-NOV-2014','dd-MON-yyyy'))
TABLESPACE users,
PARTITION sales_DEV VALUES LESS THAN (TO_DATE('01-DEC-2014','dd-MON-yyyy'))
TABLESPACE users );
Table created.
insert into USER1.sales values('prod1','01-AUG-2014');
insert into USER1.sales values('prod2','01-SEP-2014');
insert into USER1.sales values('prod3','01-OCT-2014');
SELECT partition_name FROM dba_tab_partitions WHERE table_name = 'SALES' and TABLE_OWNER='USER1';
PARTITION_NAME
--------------------------------------------------------------------------------------------------------------------------------
SALES_AUG
SALES_DEV
SALES_NOV
SALES_OCT
SALES_SEP
col owner for a20
SELECT owner,partitioned FROM dba_tables WHERE table_name = 'SALES';
OWNER PAR
-------------------- ---
USER1 YES
SELECT partition_name FROM dba_tab_partitions WHERE table_name = 'SALES';
PARTITION_NAME
------------------------------
SALES_AUG
SALES_DEV
SALES_NOV
SALES_OCT
SALES_SEP
to create par file
set linesize 400
col OBJECT_NAME for a20
col SUBOBJECT_NAME for a20
col created for a25
col owner for a20
col list for a40
col list for a40
select 'tables='||owner||'.'|| object_name||':'||subobject_name list
--||',' ||'--'||created list
from dba_objects
where object_type='TABLE PARTITION'
and owner='USER1'
--and trunc(created) = trunc(sysdate)
order by CREATED
;
tables=USER1.SALES:SALES_DEV
tables=USER1.SALES:SALES_NOV
tables=USER1.SALES:SALES_AUG
tables=USER1.SALES:SALES_SEP
tables=USER1.SALES:SALES_OCT
userid="/ as sysdba"
directory=EXPDP_DIR
logfile=sales_table_partition.log
dumpfile=sales_table_partition.dmp
tables=USER1.SALES:SALES_DEV
tables=USER1.SALES:SALES_NOV
tables=USER1.SALES:SALES_AUG
tables=USER1.SALES:SALES_SEP
tables=USER1.SALES:SALES_OCT
expdp parfile=expdp_table.par
expdp parfile=expdp_table.par
Export: Release 12.2.0.1.0 - Production on Wed Jun 5 01:55:56 2024
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Starting "SYS"."SYS_EXPORT_TABLE_01": /******** AS SYSDBA parfile=expdp_table.par
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "USER1"."SALES":"SALES_NOV" 5.492 KB 1 rows
. . exported "USER1"."SALES":"SALES_OCT" 5.492 KB 1 rows
. . exported "USER1"."SALES":"SALES_SEP" 5.492 KB 1 rows
. . exported "USER1"."SALES":"SALES_AUG" 0 KB 0 rows
. . exported "USER1"."SALES":"SALES_DEV" 0 KB 0 rows
Master table "SYS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TABLE_01 is:
/dumps/expdp/sales_table_partition.dmp
Job "SYS"."SYS_EXPORT_TABLE_01" successfully completed at Wed Jun 5 01:58:35 2024 elapsed 0 00:02:38
for imp
cat impdp_table.par
userid="/ as sysdba"
directory=EXPDP_DIR
remap_schema=USER1:USER2
dumpfile=sales_table_partition.dmp
tables=USER1.SALES:SALES_DEV
tables=USER1.SALES:SALES_NOV
tables=USER1.SALES:SALES_AUG
tables=USER1.SALES:SALES_SEP
tables=USER1.SALES:SALES_OCT
impdp parfile=impdp_table.par
Import: Release 12.2.0.1.0 - Production on Wed Jun 5 02:06:57 2024
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Master table "SYS"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_TABLE_01": /******** AS SYSDBA parfile=impdp_table.par
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "USER2"."SALES":"SALES_NOV" 5.492 KB 1 rows
. . imported "USER2"."SALES":"SALES_OCT" 5.492 KB 1 rows
. . imported "USER2"."SALES":"SALES_SEP" 5.492 KB 1 rows
. . imported "USER2"."SALES":"SALES_AUG" 0 KB 0 rows
. . imported "USER2"."SALES":"SALES_DEV" 0 KB 0 rows
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Job "SYS"."SYS_IMPORT_TABLE_01" successfully completed at Wed Jun 5 02:07:07 2024 elapsed 0 00:00:08
===========================================================
to check row count !!!
define TABLE_OWNER='USER2'
define TABLE_NAME='SALES'
set serverout on size 1000000 verify off
declare
sql_stmt varchar2(1024);
row_count number;
cursor get_tab is
select table_name,partition_name from dba_tab_partitions where table_owner=upper('&TABLE_OWNER') and table_name='&TABLE_NAME'
--and rownum<20
;
begin
dbms_output.put_line('Record Counts for table_name..... ');
dbms_output.put_line('Log file to numrows_part_&TABLE_OWNER.lst ....');
dbms_output.put_line('....');
for get_tab_rec in get_tab loop
BEGIN
sql_stmt := 'select count(*) from &TABLE_OWNER..'||get_tab_rec.table_name||' partition ( '||get_tab_rec.partition_name||' )';
EXECUTE IMMEDIATE sql_stmt INTO row_count;
dbms_output.put_line('Table '||rpad(get_tab_rec.table_name||'('||get_tab_rec.partition_name||')',50)||' '||TO_CHAR(row_count)||' rows.');
exception when others then dbms_output.put_line ('Error counting rows for table '||get_tab_rec.table_name);
END;
end loop;
end;
/
Table SALES(SALES_AUG) 0 rows.
Table SALES(SALES_DEV) 0 rows.
Table SALES(SALES_NOV) 1 rows.
Table SALES(SALES_OCT) 1 rows.
Table SALES(SALES_SEP) 1 rows.
PL/SQL procedure successfully completed.
or
define 1 ='USER1';
set serveroutput on
declare
p_count number;
begin
for i in (select * from DBA_tab_partitions where TABLE_OWNER='&1') loop
execute immediate 'select count(*) from '||i.TABLE_OWNER||'.'||i.TABLE_NAME|| ' partition('||i.partition_name||')' into p_count;
dbms_output.put_line(i.partition_name||' '||to_char(p_count));
end loop;
end ;
SALES_AUG 0
SALES_SEP 1
SALES_OCT 1
SALES_NOV 1
SALES_DEV 0
Thursday, 30 May 2024
Listener log data mining with SQL
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%'
/
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
====================
[oracle@ora26 dbs]$ sqlplus / as sysdba
SQL*Plus: Release 23.26.3.0.0 - Production on Fri Aug 14 13:34:11 2026
Version 23.26.3.0.0
Copyright (c) 1982, 2026, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup nomount ;
ORACLE instance started.
Total System Global Area 1792274208 bytes
Fixed Size 5033760 bytes
Variable Size 503316480 bytes
Database Buffers 1275068416 bytes
Redo Buffers 8855552 bytes
SQL> Disconnected from Oracle AI Database 26ai Enterprise Edition Release 23.26.3.0.0 - Production
Version 23.26.3.0.0
[oracle@ora26 dbs]$ rman target /
Recovery Manager: Release 23.26.3.0.0 - Production on Fri Aug 14 13:34:33 2026
Version 23.26.3.0.0
Copyright (c) 1982, 2026, Oracle and/or its affiliates. All rights reserved.
connected to target database: CDB26 (not mounted)
RMAN> restore controlfile to '+DATA' from '/u01/app/oracle/ora26/CDB26/control01.ctl' ;
restore controlfile to '+DATA' from '/u01/app/oracle/ora26/CDB26/control01.ctl' ;
Starting restore at 14-AUG-26
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=382 device type=DISK
channel ORA_DISK_1: copied control file copy
Finished restore at 14-AUG-26
RMAN>
[grid@ora26 ~]$ asmcmd -p
ASMCMD [+] > find --type CONTROLFILE +data *
+data/CDB26/CONTROLFILE/current.275.1241271299
ASMCMD [+] > ls -s +data/CDB26/CONTROLFILE/current.275.1241271299
Block_Size Blocks Bytes Space Name
16384 1151 18857984 33554432 current.275.1241271299
ASMCMD [+] > ls -l +data/CDB26/CONTROLFILE/current.275.1241271299
Type Redund Striped Time Sys Name
CONTROLFILE UNPROT FINE AUG 14 13:00:00 Y current.275.1241271299
ASMCMD [+] > ls -ls +data/CDB26/CONTROLFILE/current.275.1241271299
Type Redund Striped Time Sys Block_Size Blocks Bytes Space Name
CONTROLFILE UNPROT FINE AUG 14 13:00:00 Y 16384 1151 18857984 33554432 current.275.1241271299
ASMCMD [+] >
alter system set control_files='+data/CDB26/CONTROLFILE/current.275.1241271299' scope=spfile;
SQL> startup ;
ORACLE instance started.
Total System Global Area 1792274208 bytes
Fixed Size 5033760 bytes
Variable Size 503316480 bytes
Database Buffers 1275068416 bytes
Redo Buffers 8855552 bytes
Database mounted.
Database opened.
SQL> show parameter control;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time integer 7
control_files string +DATA/CDB26/CONTROLFILE/curren
t.275.1241271299
control_management_pack_access string DIAGNOSTIC+TUNING
diagnostics_control string IGNORE
SQL>
Subscribe to:
Posts (Atom)
