Oracle Database Administration

Tuesday, January 14, 2025

delete archive log from RMAN of last 8 days

delete force archivelog until time 'SYSDATE-08';

Thursday, November 28, 2024

Linux Memory

ps -A --sort -rss -o comm, pcpu, pmem, pid, user| head -n 15

free | grep Mem | awk '{print $3/$2 * 100.0}'

Wednesday, August 7, 2024

DR scripts

select max(sequence#) from v$log_history ;

set lines 200

SELECT thread#, process, pid, status, client_process, sequence#, block# FROM gv$managed_standby ORDER BY thread#, process;

col FIRST for a25

col NEXT for a25

col COMPLETION for a25

select thread#, sequence#, applied,to_char(first_time,'mm/dd/yy hh24:mi:ss') first,to_char(next_time, 'mm/dd/yy hh24:mi:ss') next,to_char(completion_time, 'mm/dd/yy hh24:mi:ss') completion

from v$archived_log where thread# = 1 order by first_time;

SELECT a.thread#, b. last_seq, a.applied_seq, a. last_app_timestamp, b.last_seq-a.applied_seq ARC_DIFF FROM (SELECT thread#, MAX(sequence#) applied_seq, MAX(next_time) last_app_timestamp FROM gv$archived_log WHERE applied = 'YES' GROUP BY thread#) a, (SELECT thread#, MAX (sequence#) last_seq FROM gv$archived_log GROUP BY thread#) b WHERE a.thread# = b.thread#;

select max(sequence#) from v$archived_log;

SELECT /*+ rule */ ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference" FROM (SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH, (SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL WHERE ARCH.THREAD# = APPL.THREAD# ORDER BY 1;

archive generation - compact script Oracle

select trunc(COMPLETION_TIME,'DD') Day, thread#,

round(sum(BLOCKS*BLOCK_SIZE)/1024/1024/1024) GB,

count(*) Archives_Generated from v$archived_log

group by trunc(COMPLETION_TIME,'DD'),thread# order by 1;

Tuesday, August 6, 2024

archive generation per hour Oracle !

set linesize 140
set feedback off
set timing off
set pagesize 1000
col ARCHIVED format a8
col ins format 99 heading "DB"
col member format a80
col status format a12
col archive_date format a20
col member format a60
col type format a10
col group# format 99999999
col min_archive_interval format a20
col max_archive_interval format a20
col h00 heading "H00" format a3
col h01 heading "H01" format a3
col h02 heading "H02" format a3
col h03 heading "H03" format a3
col h04 heading "H04" format a3
col h05 heading "H05" format a3
col h06 heading "H06" format a3
col h07 heading "H07" format a3
col h08 heading "H08" format a3
col h09 heading "H09" format a3
col h10 heading "H10" format a3
col h11 heading "H11" format a3
col h12 heading "H12" format a3
col h13 heading "H13" format a3
col h14 heading "H14" format a3
col h15 heading "H15" format a3
col h16 heading "H16" format a3
col h17 heading "H17" format a3
col h18 heading "H18" format a3
col h19 heading "H19" format a3
col h20 heading "H20" format a3
col h21 heading "H21" format a3
col h22 heading "H22" format a3
col h23 heading "H23" format a3
col total format a6
col date format a10
select * from v$logfile order by group#;
select * from v$log order by SEQUENCE#;
select max( sequence#) last_sequence, max(completion_time) completion_time, max(block_size) block_size from v$archived_log ;
SELECT instance ins,
log_date "DATE" ,
lpad(to_char(NVL( COUNT( * ) , 0 )),6,' ') Total,
lpad(to_char(NVL( SUM( decode( log_hour , '00' , 1 ) ) , 0 )),3,' ') h00 ,
lpad(to_char(NVL( SUM( decode( log_hour , '01' , 1 ) ) , 0 )),3,' ') h01 ,
lpad(to_char(NVL( SUM( decode( log_hour , '02' , 1 ) ) , 0 )),3,' ') h02 ,
lpad(to_char(NVL( SUM( decode( log_hour , '03' , 1 ) ) , 0 )),3,' ') h03 ,
lpad(to_char(NVL( SUM( decode( log_hour , '04' , 1 ) ) , 0 )),3,' ') h04 ,
lpad(to_char(NVL( SUM( decode( log_hour , '05' , 1 ) ) , 0 )),3,' ') h05 ,
lpad(to_char(NVL( SUM( decode( log_hour , '06' , 1 ) ) , 0 )),3,' ') h06 ,
lpad(to_char(NVL( SUM( decode( log_hour , '07' , 1 ) ) , 0 )),3,' ') h07 ,
lpad(to_char(NVL( SUM( decode( log_hour , '08' , 1 ) ) , 0 )),3,' ') h08 ,
lpad(to_char(NVL( SUM( decode( log_hour , '09' , 1 ) ) , 0 )),3,' ') h09 ,
lpad(to_char(NVL( SUM( decode( log_hour , '10' , 1 ) ) , 0 )),3,' ') h10 ,
lpad(to_char(NVL( SUM( decode( log_hour , '11' , 1 ) ) , 0 )),3,' ') h11 ,
lpad(to_char(NVL( SUM( decode( log_hour , '12' , 1 ) ) , 0 )),3,' ') h12 ,
lpad(to_char(NVL( SUM( decode( log_hour , '13' , 1 ) ) , 0 )),3,' ') h13 ,
lpad(to_char(NVL( SUM( decode( log_hour , '14' , 1 ) ) , 0 )),3,' ') h14 ,
lpad(to_char(NVL( SUM( decode( log_hour , '15' , 1 ) ) , 0 )),3,' ') h15 ,
lpad(to_char(NVL( SUM( decode( log_hour , '16' , 1 ) ) , 0 )),3,' ') h16 ,
lpad(to_char(NVL( SUM( decode( log_hour , '17' , 1 ) ) , 0 )),3,' ') h17 ,
lpad(to_char(NVL( SUM( decode( log_hour , '18' , 1 ) ) , 0 )),3,' ') h18 ,
lpad(to_char(NVL( SUM( decode( log_hour , '19' , 1 ) ) , 0 )),3,' ') h19 ,
lpad(to_char(NVL( SUM( decode( log_hour , '20' , 1 ) ) , 0 )),3,' ') h20 ,
lpad(to_char(NVL( SUM( decode( log_hour , '21' , 1 ) ) , 0 )),3,' ') h21 ,
lpad(to_char(NVL( SUM( decode( log_hour , '22' , 1 ) ) , 0 )),3,' ') h22 ,
lpad(to_char(NVL( SUM( decode( log_hour , '23' , 1 ) ) , 0 )),3,' ') h23
FROM (
SELECT thread# INSTANCE ,
TO_CHAR( first_time , 'YYYY-MM-DD' ) log_date ,
TO_CHAR( first_time , 'hh24' ) log_hour
FROM v$log_history
)
GROUP BY
instance,log_date
ORDER BY
log_date ;

select trunc(min(completion_time - first_time))||' Day '||
to_char(trunc(sysdate,'dd') + min(completion_time - first_time),'hh24:mm:ss')||chr(10) min_archive_interval,
trunc(max(completion_time - first_time))||' Day '||
to_char(trunc(sysdate,'dd') + max(completion_time - first_time),'hh24:mm:ss')||chr(10) max_archive_interval
from gv$archived_log
where sequence# <> ( select max(sequence#) from gv$archived_log ) ;
set feedback on
set timing on

Thursday, April 25, 2024

As seen below obsolete EXTRACT1_NAME is showing REQ_SCN = 0.

SQL>SELECT CAPTURE_NAME, CAPTURE_TYPE, STATUS, to_char(REQUIRED_CHECKPOINT_SCN,'999999999999999')as REQ_SCN ,to_char(OLDEST_SCN,'999999999999999')as OLDEST_SCN FROM DBA_CAPTURE;

CAPTURE_NAME    CAPTURE_TYPE STATUS          REQ_SCN      OLDEST_SCN
-------------- ------------ -------        --------      ----------
OGG$CAP_EXTRACT-NAME LOCAL        ENABLED        64983227        64983227
OGG$CAP_EXTRACT1-NAME LOCAL        ENABLED               0               0

After unregistering as follows RMAN was able to delete the old archive logs:

UNREGISTER EXTRACT EXTRACT-NAME DATABASE

Thursday, November 16, 2023

find /home -xdev -type f -size +1G -exec ls -lrth {} \;

find . -name '*.trc' -mtime +2 -exec rm -f {} \;

Monday, October 23, 2023

UNDO required size - using Stats

SELECT 'The Required undo tablespace size using Statistics In Memory is ' || dbms_undo_adv.required_undo_size(900) || ' MB' required_undo_size FROM dual;

Query to get 10 Large Object in Oracle Database

SELECT * FROM (select SEGMENT_NAME,SEGMENT_TYPE,BYTES/1024/1024/1024 GB,TABLESPACE_NAME from dba_segments order by 3 desc) WHERE ROWNUM <= 10;

Saturday, October 21, 2023

Recovery file usage

col name format a32
col size_mb format 999,999,999
col used_mb format 999,999,999
col pct_used format 999
select
name,
ceil( space_limit / 1024 / 1024) size_mb,
ceil( space_used / 1024 / 1024) used_mb,
decode( nvl( space_used, 0),0, 0,
ceil ( ( space_used / space_limit) * 100) ) pct_used
from
v$recovery_file_dest
order by
name desc;

Meta data tablespace

set lines 190
set pages 5000
set long 50000
set head off echo off
select 'select dbms_metadata.get_ddl(''TABLESPACE'','''
|| tablespace_name || ''') from dual;' from dba_tablespaces where tablespace_name='TSTDOCMGMT01';

Wednesday, October 11, 2023

killing inactive session from Oracle database

SELECT    'ALTER SYSTEM KILL SESSION '''
      || s.sid
      || ','
      || s.serial#
      || ''' IMMEDIATE;'
         AS ddl
FROM v$session s
WHERE s.status='INACTIVE';

Wednesday, September 27, 2023

UNDO Main

SET SERVEROUTPUT ON
-- Not wrriten by me but its a good script to check details on UNDO tablespace usage and recommendations
SET LINES 600
ALTER SESSION SET NLS_DATE_FORMAT = 'DD/MM/YYYY HH24:MI:SS';
DECLARE
v_analyse_start_time DATE := SYSDATE - 7;
v_analyse_end_time DATE := SYSDATE;
v_cur_dt DATE;
v_undo_info_ret BOOLEAN;
v_cur_undo_mb NUMBER;
v_undo_tbs_name VARCHAR2(100);
v_undo_tbs_size NUMBER;
v_undo_autoext BOOLEAN;
v_undo_retention NUMBER(5);
v_undo_guarantee BOOLEAN;
v_instance_number NUMBER;
v_undo_advisor_advice VARCHAR2(100);
v_undo_health_ret NUMBER;
v_problem VARCHAR2(1000);
v_recommendation VARCHAR2(1000);
v_rationale VARCHAR2(1000);
v_retention NUMBER;
v_utbsize NUMBER;
v_best_retention NUMBER;
v_longest_query NUMBER;
v_required_retention NUMBER;
BEGIN
select sysdate into v_cur_dt from dual;
DBMS_OUTPUT.PUT_LINE(CHR(9));
DBMS_OUTPUT.PUT_LINE('- Undo Analysis started at : ' || v_cur_dt || ' -');
DBMS_OUTPUT.PUT_LINE('--------------------------------------------------');
v_undo_info_ret := DBMS_UNDO_ADV.UNDO_INFO(v_undo_tbs_name, v_undo_tbs_size, v_undo_autoext, v_undo_retention, v_undo_guarantee);
select sum(bytes)/1024/1024 into v_cur_undo_mb from dba_data_files where tablespace_name = v_undo_tbs_name;
DBMS_OUTPUT.PUT_LINE('NOTE:The following analysis is based upon the database workload during the period -');
DBMS_OUTPUT.PUT_LINE('Begin Time : ' || v_analyse_start_time);
DBMS_OUTPUT.PUT_LINE('End Time : ' || v_analyse_end_time);

DBMS_OUTPUT.PUT_LINE(CHR(9));
DBMS_OUTPUT.PUT_LINE('Current Undo Configuration');
DBMS_OUTPUT.PUT_LINE('--------------------------');
DBMS_OUTPUT.PUT_LINE(RPAD('Current undo tablespace',55) || ' : ' || v_undo_tbs_name);
DBMS_OUTPUT.PUT_LINE(RPAD('Current undo tablespace size (datafile size now) ',55) || ' : ' || v_cur_undo_mb || 'M');
DBMS_OUTPUT.PUT_LINE(RPAD('Current undo tablespace size (consider autoextend) ',55) || ' : ' || v_undo_tbs_size || 'M');
IF V_UNDO_AUTOEXT THEN
DBMS_OUTPUT.PUT_LINE(RPAD('AUTOEXTEND for undo tablespace is',55) || ' : ON');
ELSE
DBMS_OUTPUT.PUT_LINE(RPAD('AUTOEXTEND for undo tablespace is',55) || ' : OFF');
END IF;
DBMS_OUTPUT.PUT_LINE(RPAD('Current undo retention',55) || ' : ' || v_undo_retention);
IF v_undo_guarantee THEN
DBMS_OUTPUT.PUT_LINE(RPAD('UNDO GUARANTEE is set to',55) || ' : TRUE');
ELSE
dbms_output.put_line(RPAD('UNDO GUARANTEE is set to',55) || ' : FALSE');
END IF;
DBMS_OUTPUT.PUT_LINE(CHR(9));
SELECT instance_number INTO v_instance_number FROM V$INSTANCE;
DBMS_OUTPUT.PUT_LINE('Undo Advisor Summary');
DBMS_OUTPUT.PUT_LINE('---------------------------');
v_undo_advisor_advice := dbms_undo_adv.undo_advisor(v_analyse_start_time, v_analyse_end_time, v_instance_number);
DBMS_OUTPUT.PUT_LINE(v_undo_advisor_advice);
DBMS_OUTPUT.PUT_LINE(CHR(9));
DBMS_OUTPUT.PUT_LINE('Undo Space Recommendation');
DBMS_OUTPUT.PUT_LINE('-------------------------');
v_undo_health_ret := dbms_undo_adv.undo_health(v_analyse_start_time, v_analyse_end_time, v_problem, v_recommendation, v_rationale, v_retention, v_utbsize);
IF v_undo_health_ret > 0 THEN
DBMS_OUTPUT.PUT_LINE('Minimum Recommendation : ' || v_recommendation);
DBMS_OUTPUT.PUT_LINE('Rationale : ' || v_rationale);
DBMS_OUTPUT.PUT_LINE('Recommended Undo Tablespace Size : ' || v_utbsize || 'M');
ELSE
DBMS_OUTPUT.PUT_LINE('Allocated undo space is sufficient for the current workload.');
END IF;

SELECT dbms_undo_adv.best_possible_retention(v_analyse_start_time, v_analyse_end_time) into v_best_retention FROM dual;
SELECT dbms_undo_adv.longest_query(v_analyse_start_time, v_analyse_end_time) into v_longest_query FROM dual;
SELECT dbms_undo_adv.required_retention(v_analyse_start_time, v_analyse_end_time) into v_required_retention FROM dual;
DBMS_OUTPUT.PUT_LINE(CHR(9));
DBMS_OUTPUT.PUT_LINE('Retention Recommendation');
DBMS_OUTPUT.PUT_LINE('------------------------');
DBMS_OUTPUT.PUT_LINE(RPAD('The best possible retention with current configuration is ',60) || ' : ' || v_best_retention || ' Seconds');
DBMS_OUTPUT.PUT_LINE(RPAD('The longest running query ran for ',60) || ' : ' || v_longest_query || ' Seconds');
DBMS_OUTPUT.PUT_LINE(RPAD('The undo retention required to avoid errors is ',60) || ' : ' || v_required_retention || ' Seconds');
END;
/

Thursday, June 22, 2023

Oracle UNDO related document

SET SERVEROUTPUT ON

SET LINES 600

ALTER SESSION SET NLS_DATE_FORMAT = 'DD/MM/YYYY HH24:MI:SS';

DECLARE

v_analyse_start_time DATE := SYSDATE - 7;

v_analyse_end_time DATE := SYSDATE;

v_cur_dt DATE;

v_undo_info_ret BOOLEAN;

v_cur_undo_mb NUMBER;

v_undo_tbs_name VARCHAR2(100);

v_undo_tbs_size NUMBER;

v_undo_autoext BOOLEAN;

v_undo_retention NUMBER(5);

v_undo_guarantee BOOLEAN;

v_instance_number NUMBER;

v_undo_advisor_advice VARCHAR2(100);

v_undo_health_ret NUMBER;

v_problem VARCHAR2(1000);

v_recommendation VARCHAR2(1000);

v_rationale VARCHAR2(1000);

v_retention NUMBER;

v_utbsize NUMBER;

v_best_retention NUMBER;

v_longest_query NUMBER;

v_required_retention NUMBER;

BEGIN

select sysdate into v_cur_dt from dual;

DBMS_OUTPUT.PUT_LINE(CHR(9));

DBMS_OUTPUT.PUT_LINE('- Undo Analysis started at : ' || v_cur_dt || ' -');

DBMS_OUTPUT.PUT_LINE('--------------------------------------------------');

v_undo_info_ret := DBMS_UNDO_ADV.UNDO_INFO(v_undo_tbs_name, v_undo_tbs_size, v_undo_autoext, v_undo_retention, v_undo_guarantee);

select sum(bytes)/1024/1024 into v_cur_undo_mb from dba_data_files where tablespace_name = v_undo_tbs_name;

DBMS_OUTPUT.PUT_LINE('NOTE:The following analysis is based upon the database workload during the period -');

DBMS_OUTPUT.PUT_LINE('Begin Time : ' || v_analyse_start_time);

DBMS_OUTPUT.PUT_LINE('End Time : ' || v_analyse_end_time);

DBMS_OUTPUT.PUT_LINE(CHR(9));

DBMS_OUTPUT.PUT_LINE('Current Undo Configuration');

DBMS_OUTPUT.PUT_LINE('--------------------------');

DBMS_OUTPUT.PUT_LINE(RPAD('Current undo tablespace',55) || ' : ' || v_undo_tbs_name);

DBMS_OUTPUT.PUT_LINE(RPAD('Current undo tablespace size (datafile size now) ',55) || ' : ' || v_cur_undo_mb || 'M');

DBMS_OUTPUT.PUT_LINE(RPAD('Current undo tablespace size (consider autoextend) ',55) || ' : ' || v_undo_tbs_size || 'M');

IF V_UNDO_AUTOEXT THEN

DBMS_OUTPUT.PUT_LINE(RPAD('AUTOEXTEND for undo tablespace is',55) || ' : ON');

ELSE

DBMS_OUTPUT.PUT_LINE(RPAD('AUTOEXTEND for undo tablespace is',55) || ' : OFF');

END IF;

DBMS_OUTPUT.PUT_LINE(RPAD('Current undo retention',55) || ' : ' || v_undo_retention);

IF v_undo_guarantee THEN

DBMS_OUTPUT.PUT_LINE(RPAD('UNDO GUARANTEE is set to',55) || ' : TRUE');

ELSE

dbms_output.put_line(RPAD('UNDO GUARANTEE is set to',55) || ' : FALSE');

END IF;

DBMS_OUTPUT.PUT_LINE(CHR(9));

SELECT instance_number INTO v_instance_number FROM V$INSTANCE;

DBMS_OUTPUT.PUT_LINE('Undo Advisor Summary');

DBMS_OUTPUT.PUT_LINE('---------------------------');

v_undo_advisor_advice := dbms_undo_adv.undo_advisor(v_analyse_start_time, v_analyse_end_time, v_instance_number);

DBMS_OUTPUT.PUT_LINE(v_undo_advisor_advice);

DBMS_OUTPUT.PUT_LINE(CHR(9));

DBMS_OUTPUT.PUT_LINE('Undo Space Recommendation');

DBMS_OUTPUT.PUT_LINE('-------------------------');

v_undo_health_ret := dbms_undo_adv.undo_health(v_analyse_start_time, v_analyse_end_time, v_problem, v_recommendation, v_rationale, v_retention, v_utbsize);

IF v_undo_health_ret > 0 THEN

DBMS_OUTPUT.PUT_LINE('Minimum Recommendation : ' || v_recommendation);

DBMS_OUTPUT.PUT_LINE('Rationale : ' || v_rationale);

DBMS_OUTPUT.PUT_LINE('Recommended Undo Tablespace Size : ' || v_utbsize || 'M');

ELSE

DBMS_OUTPUT.PUT_LINE('Allocated undo space is sufficient for the current workload.');

END IF;

SELECT dbms_undo_adv.best_possible_retention(v_analyse_start_time, v_analyse_end_time) into v_best_retention FROM dual;

SELECT dbms_undo_adv.longest_query(v_analyse_start_time, v_analyse_end_time) into v_longest_query FROM dual;

SELECT dbms_undo_adv.required_retention(v_analyse_start_time, v_analyse_end_time) into v_required_retention FROM dual;

DBMS_OUTPUT.PUT_LINE(CHR(9));

DBMS_OUTPUT.PUT_LINE('Retention Recommendation');

DBMS_OUTPUT.PUT_LINE('------------------------');

DBMS_OUTPUT.PUT_LINE(RPAD('The best possible retention with current configuration is ',60) || ' : ' || v_best_retention || ' Seconds');

DBMS_OUTPUT.PUT_LINE(RPAD('The longest running query ran for ',60) || ' : ' || v_longest_query || ' Seconds');

DBMS_OUTPUT.PUT_LINE(RPAD('The undo retention required to avoid errors is ',60) || ' : ' || v_required_retention || ' Seconds');

END;

Wednesday, July 12, 2017

12c Binaries Installation - Silent Installation Oracle Software 12c

I have prepared silent mode installation of 12c software , please validate

cd /local/dbatemp/software/12102/database
export DISTRIB=`pwd`
./runInstaller -silent \
-responseFile $DISTRIB/response/db_install.rsp \
oracle.install.option=INSTALL_DB_SWONLY \
UNIX_GROUP_NAME=jbs_sudo \
INVENTORY_LOCATION=/local/app/oraInventory \
SELECTED_LANGUAGES=en \
ORACLE_HOME=/local/app/oracle/product/12.1.0.2 \
ORACLE_BASE=/local/app/oracle \
oracle.install.db.InstallEdition=EE \
oracle.install.db.isCustomInstall=false \
oracle.install.db.DBA_GROUP=jbs_sudo \
oracle.install.db.OPER_GROUP=jbs_sudo \
oracle.install.db.BACKUPDBA_GROUP=jbs_sudo \
oracle.install.db.DGDBA_GROUP=jbs_sudo \
oracle.install.db.KMDBA_GROUP=jbs_sudo \
SECURITY_UPDATES_VIA_MYORACLESUPPORT=false \
DECLINE_SECURITY_UPDATES=true

This has started installation in the background: you need to wait until something similar to the following is displayed:

The installation of Oracle Database 12c was successful.
Please check '/local/app/oraInventory/logs/silentInstallXXX.log' for more details.

As a root user, execute the following script(s):
1. /local/app/oraInventory/orainstRoot.sh
2. /local/app/oracle/product/12.1.0.2/root.sh

Successfully Setup Software.

Thursday, March 16, 2017

RMAN-06563: control file or SPFILE must be restored using FROM AUTOBACKUP

RMAN-06563: control file or SPFILE must be restored using FROM AUTOBACKUP

autobackup, catalog, dbid, restore controlfile, rman, RMAN-06563

Backup and Recovery best practices dictate that we must use a RMAN recovery catalog and also have the controlfile AUTOBACKUP enabled.

If we do not do either and we lose all the controlfiles, we cannot restore the controlfiles even if we have taken a backup to tape as shown in the case below.

We will encounter the RMAN-06563 error even if we set the DBID or explicitly alllocate a channel for a tape device.

set dbid=693232013;

executing command: SET DBID

RMAN> run {
2> restore controlfile;
3> recover database;
4> }

Starting restore at 28-JUL-09
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=159 devtype=DISK

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 07/28/2009 12:17:19
RMAN-06563: control file or SPFILE must be restored using FROM AUTOBACKUP

RMAN> run {
2> allocate channel c1 device type sbt_tape;
3> restore controlfile;
4> alter database mount;
5> recover database;
6> }

released channel: ORA_DISK_1
allocated channel: c1
channel c1: sid=159 devtype=SBT_TAPE
channel c1: Data Protection for Oracle: version 5.5.1.0

Starting restore at 28-JUL-09

released channel: c1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 07/28/2009 12:19:36
RMAN-06563: control file or SPFILE must be restored using FROM AUTOBACKUP

However, if we connect to a RMAN recovery catalog, we can restore a controlfile without using the AUTOBACKUP keyword.

$ rman target / catalog rman9p/xxx@rcatp

Recovery Manager: Release 9.2.0.8.0 - 64bit Production

Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.

connected to target database: ofsap (not mounted)
connected to recovery catalog database

RMAN> run {
2> allocate channel ch1 type 'sbt_tape' parms 'ENV=(TDPO_OPTFILE=/usr/tivoli/tsm/client/oracle/bin64/tdpo.opt)';
3> restore controlfile;
4> release channel ch1;
5> }

allocated channel: ch1
channel ch1: sid=8 devtype=SBT_TAPE
channel ch1: Data Protection for Oracle: version 5.4.1.0

Starting restore at 27-JUN-08

channel ch1: starting datafile backupset restore
channel ch1: restoring controlfile
output filename=/u04/oradata/ofsap/control01.ctl
channel ch1: restored backup piece 1
piece handle=c-2764499561-20080627-08 tag=null params=NULL
channel ch1: restore complete
replicating controlfile
input filename=/u04/oradata/ofsap/control01.ctl
output filename=/u04/oradata/ofsap/control02.ctl
Finished restore at 27-JUN-08

released channel: ch1

Saturday, March 4, 2017

Block Corruption in Oracle

Normally when block Corruption Happened with Oracle Database Oracle reports the corrupted block number in Error Message,

SQL> select * from update7sp7.test1; select * from update7sp7.test1 * ERROR at line 1: ORA-01578: ORACLE data block corrupted (file # 8, block # 138 ) ORA-01110: data file 8: '+DATA_NEW/srpstb/datafile/myts.262.8 19379005'

This way ..

we need to fix Corrupted block using few available methods, Best approach is RMAN where we have other Utilities as well

BBED for block Corruption recovery.

here in this case i tried to recover a block using RMAN

---------

check DB / Datafile / status using validate command , which will list you the entire block details, with

Used / Un Used and corrupted block if any

RMAN> validate database;

Or at Database level we can get the list using

select * from v$database_block_corruption;

once we get the corrupted block details , we can recover it with steps given below.

[oracle@ora1-2 trace]$ rman target /

Recovery Manager: Release 11.2.0.3.0 - Production on Fri Oct 418:41:21 2013

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

connected to target database: SRPRIM (DBID=216679430)

RMAN> recover datafile 8 block 137 to 138,146;

Starting recover at 04-OCT-13

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=59 device type=DISK

searching flashback logs for block images until SCN 2501389

finished flashback log search, restored 0 blocks

channel ORA_DISK_1: restoring block(s) from datafile copy /u02/bkp/myts_8

failover to previous backup

channel ORA_DISK_1: restoring block(s)

channel ORA_DISK_1: specifying block(s) to restore from backupset

restoring blocks of datafile 00008

channel ORA_DISK_1: reading from backup piece /u02/bkp/srprim_inc0_37olibpj_1_1.bak

channel ORA_DISK_1: piece handle=/u02/bkp/srprim_inc0_37olibpj_1_1.bak tag=TAG20131004T120611

channel ORA_DISK_1: restored block(s) from backup piece 1

channel ORA_DISK_1: block restore complete, elapsed time: 00:00:01

starting media recovery

media recovery complete, elapsed time: 00:00:03

Finished recover at 04-OCT-13

SQL> select status,instance_name,database_role from v$instance,v$database;

STATUS INSTANCE_NAME DATABASE_ROLE

------ -------------  --------------

OPEN   SRPRIM        PRIMARY

SQL> select * from v$database_block_corruption;

no rows selected

Sunday, January 22, 2017

Online migration of ASM disk groups

Online migration of ASM disk groups from one SAN to another

Below mentioned are the detailed steps from Database Admin perspective for migrating ASM disks from one SAN to another[from EMC to HP] in a RAC setup.

1. Capture the current ASM disk/diskgroups details as below. Also decide upon the new LUN and ASM disk names. Share these with OS Admins, so that they can create same size LUN's with new SAN.

To get ASM disk, group and size details use the below queries.
select group_number,disk_number,name,mount_status,state,total_mb,free_mb From v$asm_disk order by 1,2;
select GROUP_NUMBER,NAME from v$asm_diskgroup;

To get the LUN-ASM disk mapping details use the below command.

/usr/sbin/oracleasm querydisk -p /dev/mapper/*

This command needs to be run with root privilege and in all the RAC instances just to be sure that you see the same.

ASM Disk Group Existing ASM Disk Existing LUN ASM disk Size in GB New LUN New Disk Name
ASMVOTE01 OCR_VOTE1 OCR_VOTE1p1 1 N_OCR_VOTE1p1 N_OCR_VOTE1
OCR_VOTE2 OCR_VOTE2p1 1 N_OCR_VOTE2p1 N_OCR_VOTE2
OCR_VOTE3 OCR_VOTE3p1 1 N_OCR_VOTE3p1 N_OCR_VOTE3
OCR_VOTE4 OCR_VOTE4p1 1 N_OCR_VOTE4p1 N_OCR_VOTE4
OCR_VOTE5 OCR_VOTE5p1 1 N_OCR_VOTE5p1 N_OCR_VOTE5
ASMDATA01 ASMDATA01 ASM1p1 100 N_ASM1p1 N_ASMDATA01
ASMDATA02 ASM2p1 100 N_ASM2p1 N_ASMDATA02
ASMDATA03 ASM3p1 100 N_ASM3p1 N_ASMDATA03
ASMDATA04 ASM4p1 100 N_ASM4p1 N_ASMDATA04
ASMFRA01 ASMDATA05 ASM5p1 100 N_ASM5p1 N_ASMDATA05
ASMDATA06 ASM6p1 100 N_ASM6p1 N_ASMDATA06
ASMDATA07 ASM7p1 100 N_ASM7p1 N_ASMDATA07
ASMDATA08 ASM8p1 100 N_ASM8p1 N_ASMDATA08
ASMDATA09 ASM9p1 100 N_ASM9p1 N_ASMDATA09
ASMDATA10 ASM10p1 100 N_ASM10p1 N_ASMDATA10

2. Take full database backup.

3. Both old and new SAN LUNs must be presented to all the RAC nodes.

Confirm whether you see the new LUNs under /dev/mapper/ from all the cluster nodes.

4. Once the LUNs are visible we need to stamp them to use them as ASM disks.

Below commands needs to be run with root privilege. This commands should be run only once from anyone of the cluster node.

sudo /usr/sbin/oracleasm createdisk N_OCR_VOTE1 '/dev/mapper/N_OCR_VOTE1p1'
sudo /usr/sbin/oracleasm createdisk N_OCR_VOTE2 '/dev/mapper/N_OCR_VOTE2p1'
sudo /usr/sbin/oracleasm createdisk N_OCR_VOTE3 '/dev/mapper/N_OCR_VOTE3p1'
sudo /usr/sbin/oracleasm createdisk N_OCR_VOTE4 '/dev/mapper/N_OCR_VOTE4p1'
sudo /usr/sbin/oracleasm createdisk N_OCR_VOTE5 '/dev/mapper/N_OCR_VOTE5p1'
sudo /usr/sbin/oracleasm createdisk N_ASMDATA1 '/dev/mapper/N_ASM1p1'
sudo /usr/sbin/oracleasm createdisk N_ASMDATA2 '/dev/mapper/N_ASM2p1'
sudo /usr/sbin/oracleasm createdisk N_ASMDATA3 '/dev/mapper/N_ASM3p1'
sudo /usr/sbin/oracleasm createdisk N_ASMDATA4 '/dev/mapper/N_ASM4p1'
sudo /usr/sbin/oracleasm createdisk N_ASMDATA5 '/dev/mapper/N_ASM5p1'
sudo /usr/sbin/oracleasm createdisk N_ASMDATA6 '/dev/mapper/N_ASM6p1'
sudo /usr/sbin/oracleasm createdisk N_ASMDATA7 '/dev/mapper/N_ASM7p1'
sudo /usr/sbin/oracleasm createdisk N_ASMDATA8 '/dev/mapper/N_ASM8p1'
sudo /usr/sbin/oracleasm createdisk N_ASMDATA9 '/dev/mapper/N_ASM9p1'
sudo /usr/sbin/oracleasm createdisk N_ASMDATA10 '/dev/mapper/N_ASM10p1'

5. Now scan and list the newly added ASM disks on other nodes.

Again this needs to be run with root privilege.

sudo /usr/sbin/oracleasm scandisks
sudo /usr/sbin/oracleasm listdisks

You should be able to see both old and newly added ASM disks from all the nodes.

6. Login to any ASM instance and verify the newly added ASM disks.Make sure you login as sysasm to avoid any privilege issues while logging into ASM instances.

select group_number,disk_number,name,mount_status,state,total_mb,free_mb From v$asm_disk order by 1,2;

7. Rebalancing

a. Add the newly created ASM disks to its mapping ASM diskgroups, which was decided in step 1.

Once the ALTER DISKGROUP is run rebalancing operation will begin in background and the prompt will be returned. Rebalancing of one diskgroup is not dependent on other, so below commands can be run at once.

SQL> alter diskgroup ASMDATA01
add disk 'ORCL:N_ASMDATA1','ORCL:N_ASMDATA2','ORCL:N_ASMDATA3','ORCL:N_ASMDATA4'
rebalance power 11;

SQL> alter diskgroup ASMFRA01
add disk 'ORCL:N_ASMDATA5','ORCL:N_ASMDATA6',
'ORCL:N_ASMDATA7','ORCL:N_ASMDATA8','ORCL:N_ASMDATA9','ORCL:N_ASMDATA10'
rebalance power 11;

SQL> alter diskgroup ASMVOTE01
add disk
'ORCL:N_OCR_VOTE1','ORCL:N_OCR_VOTE2','ORCL:N_OCR_VOTE3' ,'ORCL:N_OCR_VOTE4',
'ORCL:N_OCR_VOTE5' rebalance power 11;

b. Drop the old ASM disks from its corresponding ASM diskgroups.

SQL> alter diskgroup ASMDATA01
drop disk 'ORCL:ASMDATA1','ORCL:ASMDATA2','ORCL:ASMDATA3','ORCL:ASMDATA4'
rebalance power 11;

SQL> alter diskgroup ASMFRA01
drop disk 'ORCL:ASMDATA5','ORCL:ASMDATA6',
'ORCL:ASMDATA7','ORCL:ASMDATA8','ORCL:ASMDATA9','ORCL:ASMDATA10'
rebalance power 11;

SQL> alter diskgroup ASMVOTE01
drop disk
'ORCL:OCR_VOTE1','ORCL:OCR_VOTE2',
'ORCL:OCR_VOTE3' ,'ORCL:OCR_VOTE4','ORCL:OCR_VOTE5' rebalance power 11;

Both steps a and b can be performed in a single command and oracle recommends the same to save time. But to be on the safer side its better to perform them separately.

8. Monitor Rebalancing operation

Login to any ASM instance and monitor rebalancing operation using the below queries.

select * from v$asm_operation;
select * from gv$asm_operation;

9. Verify the diskgroups for the last time.

select group_number,disk_number,mount_status,state,redundancy from v$asm_disk;
select group_number,name,state,type from v$asm_diskgroup;
select group_number,disk_number,name,mount_status,state,total_mb,free_mb from v$asm_disk order by 1,2;

This successfully completes the migration.

10. Once above steps are complete, the old SAN can be dismounted.

Monday, October 24, 2016

Tning.sql - performance analyser

-- -----------------------------------------------------------------------------------
-- File Name    : https://oracle-base.com/dba/monitoring/tuning.sql
-- Author       : Tim Hall
-- Description  : Displays several performance indicators and comments on the value.
-- Requirements : Access to the V$ views.
-- Call Syntax  : @tuning
-- Last Modified: 15/07/2000
-- -----------------------------------------------------------------------------------
SET SERVEROUTPUT ON
SET LINESIZE 1000
SET FEEDBACK OFF

SELECT *
FROM   v$database;
PROMPT

DECLARE
  v_value  NUMBER;

  FUNCTION Format(p_value  IN  NUMBER) 
    RETURN VARCHAR2 IS
  BEGIN
    RETURN LPad(To_Char(Round(p_value,2),'990.00') || '%',8,' ') || '  ';
  END;

BEGIN

  -- --------------------------
  -- Dictionary Cache Hit Ratio
  -- --------------------------
  SELECT (1 - (Sum(getmisses)/(Sum(gets) + Sum(getmisses)))) * 100
  INTO   v_value
  FROM   v$rowcache;

  DBMS_Output.Put('Dictionary Cache Hit Ratio       : ' || Format(v_value));
  IF v_value < 90 THEN
    DBMS_Output.Put_Line('Increase SHARED_POOL_SIZE parameter to bring value above 90%');
  ELSE
    DBMS_Output.Put_Line('Value Acceptable.');  
  END IF;

  -- -----------------------
  -- Library Cache Hit Ratio
  -- -----------------------
  SELECT (1 -(Sum(reloads)/(Sum(pins) + Sum(reloads)))) * 100
  INTO   v_value
  FROM   v$librarycache;

  DBMS_Output.Put('Library Cache Hit Ratio          : ' || Format(v_value));
  IF v_value < 99 THEN
    DBMS_Output.Put_Line('Increase SHARED_POOL_SIZE parameter to bring value above 99%');
  ELSE
    DBMS_Output.Put_Line('Value Acceptable.');  
  END IF;

  -- -------------------------------
  -- DB Block Buffer Cache Hit Ratio
  -- -------------------------------
  SELECT (1 - (phys.value / (db.value + cons.value))) * 100
  INTO   v_value
  FROM   v$sysstat phys,
         v$sysstat db,
         v$sysstat cons
  WHERE  phys.name  = 'physical reads'
  AND    db.name    = 'db block gets'
  AND    cons.name  = 'consistent gets';

  DBMS_Output.Put('DB Block Buffer Cache Hit Ratio  : ' || Format(v_value));
  IF v_value < 89 THEN
    DBMS_Output.Put_Line('Increase DB_BLOCK_BUFFERS parameter to bring value above 89%');
  ELSE
    DBMS_Output.Put_Line('Value Acceptable.');  
  END IF;
  
  -- ---------------
  -- Latch Hit Ratio
  -- ---------------
  SELECT (1 - (Sum(misses) / Sum(gets))) * 100
  INTO   v_value
  FROM   v$latch;

  DBMS_Output.Put('Latch Hit Ratio                  : ' || Format(v_value));
  IF v_value < 98 THEN
    DBMS_Output.Put_Line('Increase number of latches to bring the value above 98%');
  ELSE
    DBMS_Output.Put_Line('Value acceptable.');
  END IF;

  -- -----------------------
  -- Disk Sort Ratio
  -- -----------------------
  SELECT (disk.value/mem.value) * 100
  INTO   v_value
  FROM   v$sysstat disk,
         v$sysstat mem
  WHERE  disk.name = 'sorts (disk)'
  AND    mem.name  = 'sorts (memory)';

  DBMS_Output.Put('Disk Sort Ratio                  : ' || Format(v_value));
  IF v_value > 5 THEN
    DBMS_Output.Put_Line('Increase SORT_AREA_SIZE parameter to bring value below 5%');
  ELSE
    DBMS_Output.Put_Line('Value Acceptable.');  
  END IF;
  
  -- ----------------------
  -- Rollback Segment Waits
  -- ----------------------
  SELECT (Sum(waits) / Sum(gets)) * 100
  INTO   v_value
  FROM   v$rollstat;

  DBMS_Output.Put('Rollback Segment Waits           : ' || Format(v_value));
  IF v_value > 5 THEN
    DBMS_Output.Put_Line('Increase number of Rollback Segments to bring the value below 5%');
  ELSE
    DBMS_Output.Put_Line('Value acceptable.');
  END IF;

  -- -------------------
  -- Dispatcher Workload
  -- -------------------
  SELECT NVL((Sum(busy) / (Sum(busy) + Sum(idle))) * 100,0)
  INTO   v_value
  FROM   v$dispatcher;

  DBMS_Output.Put('Dispatcher Workload              : ' || Format(v_value));
  IF v_value > 50 THEN
    DBMS_Output.Put_Line('Increase MTS_DISPATCHERS to bring the value below 50%');
  ELSE
    DBMS_Output.Put_Line('Value acceptable.');
  END IF;
  
END;
/

PROMPT
SET FEEDBACK ON

- ORDS (oracle Rest Data services)

- ORDS (oracle Rest Data services)

Oracle now support data services - running on standalone machines, means no need of weblogic , glassfish application/ tools,

installation / is simple need to define
Port and Image in

standalone.http.port=8080
standalone.static.images=/home/oracle/apex/images

Based on the installation in the linked article, you will find the standalone settings in the following file.

/home/oracle/ords-3.0.7-conf/ords/standalone/standalone.properties

-------------

you can always star the service using

cd ~/ords-3.0.7
$JAVA_HOME/bin/java -jar ords.war

ctrl+C will stop the service, for production environment , use Nohup to run the process in backgrond and you can kill the process when you want to stop.

Tuesday, January 14, 2025

Thursday, November 28, 2024

Wednesday, August 7, 2024

Tuesday, August 6, 2024

Thursday, April 25, 2024

Thursday, November 16, 2023

Monday, October 23, 2023

Saturday, October 21, 2023

Wednesday, October 11, 2023

Wednesday, September 27, 2023

Thursday, June 22, 2023

Wednesday, July 12, 2017

Thursday, March 16, 2017

Saturday, March 4, 2017

Sunday, January 22, 2017

Monday, October 24, 2016

Total Pageviews