Tales From A Lazy Fat DBA

Den of an Oracle DB Performance & Troubleshooting freak … \,,/

  • Likes

    • 204,125
  • Archives

  • Categories

  • Subscribe

Standby/Dataguard:– RFS Process not working.

Posted by FatDBA on November 12, 2013

Not feeling well today!! 😦 , but as  It’s been a long time since i wrote my last article on Oracle database issues … here i am back again with one of the problem that we faced in our production database of our erstwhile customer, some time ago.
It was a typical Physical Standby environment with Maximum Performance model set. This is a story happened on one fine day when we started receiving some issues after a small network outage which blocked the redo stream to transfer logs from Production to standby server. ** * The bad part was  – the issue had happened during peak hours where we had confined amount of time to rectify the case.

Issue: RFS Process not working
Problem Description:
The filesystem containing the archive destination in DR server was not accessible. As a result the log shipping got stopped. We deferred the log shipping in the production server. After the filesystem was back, we enabled the log shipping but the RFS process in DR server was not running…The problem occurred when the log 24717 was being shipped… When we queried

SQL> Select status, sequence# from v$managed_standby;

The status for MRP showed that it was waiting for gap…We then manually shipped the log file and then applied… When we enabled the shipping we found that the RFS process was still not started…..There were no error in the alert log of DR…We found a trace file in the production server with the following message..

tkcrrsarc: (WARN) Failed to find ARCH for message (message:0xa)
tkcrrpa: (WARN) Failed initial attempt to send ARCH message (message:0xa)

So we thought that the issue is with the archiver process….

Solution:
Check whether the archiver process is available for shipping log files. You can identify this by querying V$ARCHIVE_PROCESSES view.
SQL> Select * from v$archive_processes;

The output have the following columns:
Process: Indicates the process number.
Status: This should be ACTIVE
Log Sequence: Log sequence number of the log that is being shipped by the archiver. If it is not shipping any log then it should be 0.
State: This should be IDLE if the archiver is not shipping any log. If it is shipping any log then its state is BUSY.

In our case we had two archiver process running.

The status of both the arch process is ACTIVE.
The log sequence of First arch process is 0 and its state is IDLE. Hence it is healthy. However the log sequence of 2nd arch process is 24717 and its state is BUSY.

This was interesting because the problem occurred when the arch process was transferring the log 24717. This log was then manually shipped and applied. But the process still shows that it was shipping the 24717 log…
So we thought of increasing the arch processes. We increased the arch process from two to four.
SQL> alter system set log_archive_max_processes=4 scope=both;

We queried the v$archive_processes, the 3rd and 4th arch process was ready to ship the log files 24718 and 24719 logs respectively with their corresponding state as IDLE…
We enabled the log shipping and the RFS process in DR was started and the log shipping went smoothly.

However the 2nd arch process still was showing the same log sequence (24717) and state (BUSY)…. We then killed that archiver process…. And we were all good!

Hope That Helped
Prashant Dixit

4 Responses to “Standby/Dataguard:– RFS Process not working.”

  1. prince said

    tis is cool stuff…

  2. Good Explanation. Keep it up !.

  3. sankar said

    870 sid 030333 033303
    1190 \\10.6.1.126\h$ 100530699137 cluster group “SSCDB01” /move cluster group “SSCDB01″ /move
    Zaqplm@123

    oracleexp0rts @EXPORTS select * from DBA_TABLESPACE_USAGE_METRICS where tablespace_name=’UNDOTBS01′;

    oracleimpex @impex

    select ‘alter system kill session ”’ ||sid|| ‘,’ || serial#|| ”’ immediate;’ from v$session where status=’ACTIVE’ and logon_time < SYSDATE-(4/24)

    select 'alter system kill session ''' ||sid|| ',' || serial#|| ''' immediate;' from v$session where username='LTF_LEA_MIL' and status='ACTIVE';,'LTF_EOD_MIL'; and status='ACTIVE';
    select 'alter system kill session ''' ||sid|| ',' || serial#|| ''' immediate;' from v$session where sid='640';

    nohup impdp sys/sys#2009 directory=dpexp dumpfile=expdp_COLLPRD_03_12032018_%U.dmp.gz logfile=collprd03162018.log remap_schemas=collprd:collprd remap_tablespace=FCL_MST_DATA1:FCL_MST_DATA1

    select username,sid,status from v$session where username is not null AND USERNAME’SYS’;

    select ‘alter system kill session ”’ ||sid|| ‘,’ || serial#|| ”’ immediate;’ from gv$session where status=’INACTIVE’ and username!=’SYS’;

    select ‘alter system kill session ”’ ||sid|| ‘,’ || serial#|| ”’ immediate;’ from gv$session where username!=’SYS’ and PROGRAM !=’JDBC Thin Client’;

    select ‘alter system kill session ”’ ||sid|| ‘,’ || serial#|| ”’ immediate;’ from gv$session where status=’INACTIVE’ and username!=’SYS’;

    select object_type,count(*) from dba_objects where owner=’COLLPRD’ and status=’VALID’ group by object_type;

    SELECT name,ctime,ptime,EXPTIME, FROM sys.user$ WHERE name = ‘PIM_DB_ADMIN’;

    select min(FHSCN) “LOW FILEHDR SCN”,max(FHSCN) “MAX FILEHDR SCN”,max(FHAFS) “Min PITR ABSSCN” from X$KCVFH;

    select file#,status,to_char(checkpoint_change#) from v$datafile where status like ‘%SYS%’;

    Kindly specify the database name which needs to be created on the below mentioned sever.

    create public database link DBLOSFINNONE.US.ORACLE.COM connect to finnonelea identified by finnonelea using ‘FINNLIVE’;

    select SCHEMA_USER,JOB,BROKEN,NEXT_DATE,NEXT_SEC,LAST_DATE from dba_jobs where job in (440683);

    00400100009296
    ifsc : UCBA000040

    select SID,a.module,a.osuser, sql_text, disk_reads, executions, last_active_time ,
    hash_value,address,PROGRAM_ID,a.status
    from gv$session a, gv$sqlarea b
    where b.hash_value = a.sql_hash_value
    and a.schemaname != ‘SYS’
    and a.status = ‘ACTIVE’ and a.module like ‘%AUTO%’;

    select c.name, b.spid, a.sid
    from v$session a, v$process b, v$bgprocess c
    where c.paddr ’00’
    and c.paddr = b.addr
    and b.addr = a.paddr;

    select SCHEMA_USER,JOB,BROKEN,NEXT_DATE,NEXT_SEC,LAST_DATE from dba_jobs where job in (893017) 10613596 Mumbai@567 10.2.5.66

    select max(sequence#),applied from v$archived_log group by applied;

    set line 200;
    set line 200;
    set pagesize 400;
    col username for a20;
    col object_name for a20;
    select ‘CALLED PLSQL’, vs.username, d_o.object_name ,d_o.object_type, vs.sid
    from dba_objects d_o
    inner join
    v$session vs
    on d_o.object_id = vs.plsql_entry_object_id
    union all
    select ‘CURRENT PLSQL’, vs.username, d_o.object_name ,d_o.object_type,vs.sid
    from dba_objects d_o
    inner join
    v$session vs
    on d_o.object_id = vs.plsql_object_id ;

    expdp sys/sscteam attach=SYS_EXPORT_FULL_26
    STOP_JOB=IMMEDIATE

    set lines 200 pagesize 200
    col OWNER_NAME for a20
    col JOB_NAME for a39
    col OPERATION for a20
    col JOB_MODE for a30
    col STATE for a25
    select * from dba_datapump_jobs;

    cooluat_data1

    SELECT a.sqltext .nbfc_cheque_dtl
    FROM v$session s, v$sqlarea a
    WHERE s.sql_address = a.addr
    AND s.sql_hash_value = a.hash_value
    AND s.username=’FINEOD_PROD’;

    select l.* from v$locked_object l, dba_objects o
    where l.object_id = o.object_id
    and o.object_type = ‘TABLE’
    and o.owner = upper(‘FINNONELEA’)
    and o.object_name = upper(‘NBFC_CHEQUE_DTL’);

    9769385110 it is outof scope in our domain.

    select b.tsname tablespace_name , MAX(b.used_size_mb) cur_used_size_mb ,
    round(AVG(inc_used_size_mb),2)avg_increas_mb from ( SELECT a.days,a.tsname , used_size_mb , used_size_mb – LAG
    (used_size_mb,1) OVER ( PARTITION BY a.tsname ORDER BY a.tsname,a.days) inc_used_size_mb
    from ( SELECT TO_CHAR(sp.begin_interval_time,’MM-DD-YYYY’) days
    ,ts.tsname ,MAX(round((tsu.tablespace_usedsize* dt.block_size )/(1024*1024),2)) used_size_mb
    from dba_hist_tbspc_space_usage tsu , dba_hist_tablespace_stat ts ,dba_hist_snapshot sp, dba_tablespaces dt
    where tsu.tablespace_id= ts.ts# AND tsu.snap_id = sp.snap_id
    AND ts.tsname = dt.tablespace_name AND sp.begin_interval_time > sysdate-7
    GROUP BY TO_CHAR(sp.begin_interval_time,’MM-DD-YYYY’), ts.tsname
    ORDER BY ts.tsname, days ) a
    ) b GROUP BY b.tsname ORDER BY b.tsname;

    pass@123

    2400+2250
    #############################################

    Tablespace 85% full

    set line 200
    set pagesize 300
    select tablespace_name,free_percent,tablespace_size_mb,free_size_mb
    from (
    SELECT b.tablespace_name, b.tablespace_size_mb,
    round((sum(nvl(fs.bytes,0))/1024/1024/b.tablespace_size_mb *100)) free_percent,
    round(sum(nvl(fs.bytes,0))/1024/1024) free_size_mb
    FROM dba_free_space fs,
    (SELECT tablespace_name, sum(bytes)/1024/1024 tablespace_size_mb FROM dba_data_files
    GROUP BY tablespace_name
    ) b
    where fs.tablespace_name = b.tablespace_name
    group by b.tablespace_name, b.tablespace_size_mb
    ) ts_free_percent
    WHERE free_percent < 15
    order by free_percent
    /

    set lines 200
    col file_name for a60
    SELECT a.tablespace_name, a.file_name, a.bytes allocated_bytes,
    b.free_bytes
    FROM dba_data_files a,
    (SELECT file_id, SUM(bytes/1024/1024/1024) free_bytes
    FROM dba_free_space b GROUP BY file_id) b
    WHERE a.file_id=b.file_id
    ORDER BY a.tablespace_name;

    select OWNER,

    sed -n '/JAN 06 08:30/,/Jan 23 10:30/p' /var/log/mail.log

    grep "06/Jan/2018:08:3[1-5]" logfile

    grep -A 999999999 '06/Jan/2018:08:30' access_log

    sed -n '/06\/Jan\/2018:08:/,$p' /orahomep02/prod2/product/10.2.0/Db_1/network/log/listener_ltfp02.log
    set lines 200 pagesize 200
    col REQ_START_DATE for a50
    col RUN_DURATION for a50
    col LOG_DATE for a50

    select
    log_date,
    job_name,
    status,
    req_start_date,
    actual_start_date,
    run_duration
    from
    dba_scheduler_job_run_details
    — where
    — job_name = 'DEMO_JOB'
    — status ‘SUCCEEDED’
    order by
    actual_start_date;

    select
    log_date,
    job_name,
    status,
    run_duration
    from
    dba_scheduler_job_run_details where job_name=’REPO_COLLECTION’;

    6b4a3t56tduwf

    Set long 999999999
    SELECT DBMS_METADATA.GET_DDL(‘DB_LINK’,’DB_LINK_SPOORS_COLLUAT’,’PUBLIC’) from dual;

    run
    {
    ALLOCATE CHANNEL c1 DEVICE TYPE DISK;
    ALLOCATE CHANNEL c2 DEVICE TYPE DISK;
    ALLOCATE CHANNEL c3 DEVICE TYPE DISK;
    ALLOCATE CHANNEL c4 DEVICE TYPE DISK;
    ALLOCATE CHANNEL c5 DEVICE TYPE DISK;
    ALLOCATE CHANNEL c6 DEVICE TYPE DISK;
    recover database noredo;
    }

    col STATUS format a9
    col hrs format 999.99
    select SESSION_KEY, INPUT_TYPE, STATUS,
    to_char(START_TIME,’mm/dd/yy hh24:mi’) start_time,
    to_char(END_TIME,’mm/dd/yy hh24:mi’) end_time,
    elapsed_seconds/3600 hrs from V$RMAN_BACKUP_JOB_DETAILS
    order by session_key;
    /

    SELECT SID, SERIAL#, CONTEXT, SOFAR, TOTALWORK,
    ROUND (SOFAR/TOTALWORK*100, 2) “% COMPLETE”
    FROM V$SESSION_LONGOPS
    WHERE OPNAME LIKE ‘RMAN%’ AND OPNAME NOT LIKE ‘%aggregate%’
    AND TOTALWORK! = 0 AND SOFAR TOTALWORK;

    SID SERIAL# CONTEXT SOFAR TOTALWORK %COMPLETE
    ———- ———- ———- ———- ———- ———-
    18 29 1 9115569 19258880 47.33

    You can also check historical backup status with the help of following script:

    set linesize 500 pagesize 2000
    col Hours format 9999.99
    col STATUS format a10
    select SESSION_KEY, INPUT_TYPE, STATUS,
    to_char(START_TIME,’mm-dd-yyyy hh24:mi:ss’) as RMAN_Bkup_start_time,
    to_char(END_TIME,’mm-dd-yyyy hh24:mi:ss’) as RMAN_Bkup_end_time,
    elapsed_seconds/3600 Hours from V$RMAN_BACKUP_JOB_DETAILS
    order by session_key;
    SESSION_KEY INPUT_TYPE STATUS RMAN_BKUP_START_TIM RMAN_BKUP_END_TIME HOURS
    ———– ————- ———- ——————- ——————- ——–
    137764 DB FULL COMPLETED 06-06-2017 02:00:32 06-06-2017 04:12:13 2.19

    select SCHEMA_USER,JOB,BROKEN,NEXT_DATE,NEXT_SEC,LAST_DATE from dba_jobs where job in (282183);
    exec dbms_job.broken(282183,false);

    select max(sequence#) from v$archived_log where thread#=1;
    select max(sequence#) from v$archived_log where thread#=2;

    exec dbms_job.change( 282183,’REPO_COLLECTION;’,trunc(sysdate+1)+2.75/24,’trunc(sysdate+1)+3/24′);

    Hi Team,

    Filesystem is used for datafile hence we are not able to free space.

    exec dbms_job.change( 440683, ‘REPO_COLLECTION;’, trunc(sysdate+1)+3/24, ‘trunc(sysdate+1)+3/24’);

    select file_name, bytes, maxbytes,
    increment_by*(bytes/blocks) “INCREMENT”,
    maxbytes-bytes remaining,
    (maxbytes-bytes)/(increment_by*(bytes/blocks)) EXTENSIONS
    from dba_data_files
    where autoextensible = ‘YES’
    /

    02267364500

    SELECT sql_text, executions,
    ROUND(disk_reads / executions,2) reads_per_run,
    disk_reads, buffer_gets,
    ROUND((buffer_gets–disk_reads)/
    buffer_gets, 2) hit_ratio,
    SQL_TEXT
    FROM v$sqlarea
    WHERE executions > 0
    AND buffer_gets > 0
    AND (buffer_gets-disk_reads)/buffer_gets 0)
    ORDER BY id1, request;

    set ORACLE_SID=QUANTIS

    purge -age 14400 -type ALERT
    sqlplus /nolog

    1. Removing files older than 14 Days from the current working directory

    find . -type f -mtime +1 -exec rm -f {} \;

    2. Finding the top 5 largest files from the current working directory

    find . -ls | sort -nrk 7 | head -5

    3. Find files larger than 100MB from the current working directory

    find . -size +100000k

    4. Delete audit records that’s older than 30 days

    find $ORACLE_HOME/rdbms/audit -name “*.aud” -mtime +30 -exec rm {} \;

    5. Delete files in /tmp that’s older than 30 days

    find /tmp -group dba -type f -mtime +5 -exec rm -f {} \;
    find /tmp/dba -group dba -type f -mtime +5 -exec rm -f {} \;

    6. Delete *.trc files more than 5 days old.

    find $TRACE_DIR -name ‘*.trc’ -type f -mtime +5 -exec rm {} \;

    ASMM SPACE CHECK==================
    select name, state, total_mb, free_mb from v$asm_diskgroup;

    SELECT name, type, ceil (total_mb/1024) TOTAL_GB , ceil (free_mb/1024) FREE_GB, required_mirror_free_mb,
    ceil ((usable_file_mb)/1024) FROM V$ASM_DISKGROUP;

    SPACE %percentage

    SELECT name, free_mb, total_mb, free_mb/total_mb*100 as percentage FROM v$asm_diskgroup;

    set lines 255
    col path for a35
    col Diskgroup for a15
    col DiskName for a20
    col disk# for 999
    col total_mb for 999,999,999
    col free_mb for 999,999,999
    compute sum of total_mb on DiskGroup
    compute sum of free_mb on DiskGroup
    break on DiskGroup skip 1 on report –

    set pages 255

    select a.name DiskGroup, b.disk_number Disk#, b.name DiskName, b.total_mb, b.free_mb, b.path, b.header_status
    from v$asm_disk b, v$asm_diskgroup a
    where a.group_number (+) =b.group_number
    order by b.group_number, b.disk_number, b.name
    /

    sqlplus / as sysdba
    col name for a60
    col header_change for 999999999999
    col controlfile_change for 999999999999
    set lines 300
    select dh.name, dh.checkpoint_change# as Header_Change, df.checkpoint_change# as Controlfile_Change
    from v$datafile df, v$datafile_header dh
    where df.file# = dh.file#
    order by dh.name;

    select name Diskgroup,round(total_mb/1024,2) “Total_TB”,round(free_mb/1024,2) “Free_GB”,round(((free_mb/total_mb)*100),2) “Available%” from v$asm_diskgroup;

    SELECT BS_KEY, I.NAME, BACKUP_TYPE, B.PIECES,B.CONTROLFILE_INCLUDED, COMPLETION_TIME, KEEP_UNTIL FROM rman.RC_DATABASE_INCARNATION i,
    rman.RC_BACKUP_SET b.WHERE i.DB_KEY = b.DB_KEY AND i.CURRENT_INCARNATION = ‘YES’ AND COMPLETION_TIME>SYSDATE-1 ORDER BY 6;

    Check the status of service

    C:\>sc query oracleserviceORADB

    ARCHIVE PROCESS GOT STRUCK
    select
    a.osuser,
    a.sid,
    a.serial#,
    p.pid,
    p.spid,
    b.name
    from
    v$session a,
    v$process p,
    v$bgprocess b
    where
    a.paddr=b.paddr
    and
    a.paddr=p.addr
    and
    p.background=1
    and b.name like ‘ARC%’
    ;

    expdp system/password@db10g full=Y directory=TEST_DIR dumpfile=full.dmp logfile=expdp_full.log query=’SCOTT.EMP:”WHERE deptno=0″,SCOTT.DEPT:”WHERE deptno=0″‘

    Automatic Gap Resolution:

    1. Shutdown the physical standby database.
    2. Determine the current sequence on the primary database.
    3. Perform at least three log switches on the primary database.
    4. Verify that the logs did not get transferred to the standby archive dest.
    5. Start the standby database.
    6. Perform a log switch on the primary and verify that the gap gets resolved on the standby.

    ls -lrth
    df -h
    cd /pool3_redo/archbkP02
    ls -lrt | head
    rm LTFP02_1_47[5-6]*.ARC
    df -h .
    ls -lrt | head
    rm LTFP02_1_477*.ARC
    df -h .
    ls -lrt | head
    rm LTFP02_1_478*.ARC

    schemas=DM_BR_STAGE,LTF_TLR_MIL,LTF_STAGE_MIL,
    ‘LTF_SSO1_MIL’,’LTF_SEC_MIL’,’LTF_SDB_MIL’,’LTF_PDC_MIL’,’LTF_MAS_STAGE_MIL’,’LTF_LEA_MIL’,’LTF_INT_MIL’,’LTF_FA_MIL’,’LTF_FAS_MIL’,’LTF_EOD_MIL’,’LTF_DEP_MIL’,’LTF_CPS_MIL’,’LTF_COMM_MIL’,’LTF_CLR_MIL’,’LTF_CASTRANS_MIL’,’LMS_STAGING_MIL’ table_exists_action=skip

    set long 999999999
    select dbms_metadata.get_ddl(‘INDEX’,index_name,owner)||’;’from dba_indexes where owner=’LTF_CASTRANS_MIL’;

    select owner,object_type,count(1) from dba_objects where object_type in(‘INDEX’,’TRIGGER’) and owner=’AIG_RMS’ group by owner,object_type;

    set long 999999999
    select dbms_metadata.get_ddl(‘TRIGGER’, trigger_name, owner)||’;’from dba_triggers where owner=’AIG_RMS’;

    select owner,object_type,count(1) from dba_objects where object_type in(‘INDEX’,’TRIGGER’) and owner in (‘LTF_SSO1_MIL’,’LTF_SEC_MIL’,’LTF_SDB_MIL’,’LTF_PDC_MIL’,’LTF_MAS_STAGE_MIL’,’LTF_LEA_MIL’,
    ‘LTF_INT_MIL’,’LTF_FA_MIL’,’LTF_FAS_MIL’,’LTF_EOD_MIL’,’LTF_DEP_MIL’,’LTF_TLR_MIL’,’LTF_CPS_MIL’,’LTF_COMM_MIL’,’LTF_CLR_MIL’,’LTF_CASTRANS_MIL’,’LMS_STAGING_MIL’) group by owner,object_type;

    COL DAY FORMAT a15;
    COL HOUR FORMAT a4;
    COL TOTAL FORMAT 999;
    SELECT TO_CHAR(FIRST_TIME,’YYYY-MM-DD’) DAY,TO_CHAR(FIRST_TIME,’HH24′) HOUR,COUNT(*) TOTAL FROM V$LOG_HISTORY
    GROUP BY TO_CHAR(FIRST_TIME,’YYYY-MM-DD’),TO_CHAR(FIRST_TIME,’HH24′)
    ORDER BY TO_CHAR(FIRST_TIME,’YYYY-MM-DD’),TO_CHAR(FIRST_TIME,’HH24′)
    ASC;

    create public database link
    mylink
    connect to
    remote_username
    identified by
    mypassword
    using ‘tns_service_name’;

    Starting in 11g release 2, the syntax has been enhanced to remove the need to epscify a TNS service_name:

    create public database link
    mylink
    connect to
    remote_username
    identified by
    mypassword
    using ‘myserver:1521/MYSID’;

    4461710444

    Thanks & Regards
    CIT-Service Desk Administrator
    Contact no – 022 6705 8649
    ===============================
    LONGOPS

    SELECT * FROM (select username,opname,sid,serial#,context,sofar,totalwork ,round(sofar/totalwork*100,2) “% Complete” from v$session_longops)
    WHERE “% Complete” != 100;
    /

    or
    set lines 300
    col TARGET for a40
    col SQL_ID for a20
    select SID,TARGET||OPNAME TARGET, TOTALWORK, SOFAR,TIME_REMAINING/60 Mins_Remaining,
    ELAPSED_SECONDS,SQL_ID from v$session_longops
    where TIME_REMAINING>0 order by TIME_REMAINING
    /
    ==================================================================

    select owner,table_name,round((blocks*8192),2)/1024/1024 “size (MB)” , round((num_rows*avg_row_len/1024/1024),2) “actual_data (MB)”,
    round((round((blocks*8192),2)/1024/1024 – round((num_rows*avg_row_len/1024/1024),2)),1) “wasted_space (MB)”
    from dba_tables where (round((blocks*8192),2)/1024/1024 > round((num_rows*avg_row_len/1024/1024),2))
    and (round((round((blocks*8192),2)/1024/1024 – round((num_rows*avg_row_len/1024/1024),2)),1)) > 100 and owner=’FINLEA_PROD’
    order by 5 desc;
    ================================
    TOP 20 FRAGMENTED tables listener
    SELECT * FROM
    (SELECT
    SUBSTR(TABLE_NAME, 1, 21) TABLE_NAME,
    NUM_ROWS,
    AVG_ROW_LEN ROWLEN,
    BLOCKS,
    ROUND((AVG_ROW_LEN + 1) * NUM_ROWS / 1000000, 0) NET_MB,
    ROUND(BLOCKS * (8000 – 23 * INI_TRANS) *
    (1 – PCT_FREE/100)/1000000, 0) GROSS_MB,
    ROUND((BLOCKS * (8000 – 23 * INI_TRANS) * (1 – PCT_FREE / 100) –
    (AVG_ROW_LEN + 1)*NUM_ROWS)/1000000) “WASTED_MB”
    FROM DBA_TABLES
    WHERE
    NUM_ROWS IS NOT NULL AND
    OWNER LIKE ‘%OWNER%’ AND
    PARTITIONED = ‘NO’ AND
    (IOT_TYPE != ‘IOT’ OR IOT_TYPE IS NULL)
    ORDER BY 7 DESC)
    WHERE ROWNUM <=20;

    To Turn Off Auto Extend On Data files

    select 'alter database datafile '''||file_name||''' AUTOEXTEND OFF;' from dba_data_files where autoextensible='YES' and tablespace_name='SSCTEMP01';

    To Turn On Auto Extend On Data files

    select 'alter database datafile '''||file_name||''' AUTOEXTEND ON;' from dba_data_files where autoextensible='NO' and tablespace_name='SSCTEMP01';

    For Temp files Turn Off

    select 'alter database tempfile '''||file_name||''' AUTOEXTEND OFF;' from dba_temp_files where autoextensible='YES';

    For Temp files Turn On
    select 'alter database tempfile '''||file_name||''' AUTOEXTEND ON;' from dba_temp_files where autoextensible='NO';

    bplist -S CITBKNBU01 -C POVSSSCDBPRM -t 4 -s 05/02/2019 -e 05/03/2019 -l -R /

    prctl -n project.max-shm-memory -i project 101
    prctl -n project.max-shm-memory -r -v 12G -i project 101

    expdp dumpfile=expdppre_EOM_GTBPROD_31080018_%U.dmp DIRECTORY=EXPORT_DIR SCHEMAS=WCH_LT,CSCR_LT,CORPLOS,ARX_LT,INTG_LT logfile=expdppre_EOM_GTBPROD_31082018.log compression=all parallel=8

    expdp dumpfile=expdppost_EOM_GTBPROD_31082018_%U.dmp DIRECTORY=EXPORT_DIR SCHEMAS=WCH_LT,CSCR_LT,CORPLOS,ARX_LT,INTG_LT logfile=expdpost_EOM_GTBPROD_31082018.log compression=all parallel=8

    compression=all parallel=6
    expdp dumpfile=expdppre_EOM_GTBPROD_30112018_%U.dmp DIRECTORY=expdp SCHEMAS=WCH_LT,CSCR_LT,CORPLOS,ARX_LT,INTG_LT logfile=expdppre_EOM_GTBPROD_31082018.log compression=all parallel=12

    expdp dumpfile=expdppost_EOM_GTBPROD_30112018_%U.dmp DIRECTORY=EXPORT_DIR SCHEMAS=WCH_LT,CSCR_LT,CORPLOS,ARX_LT,INTG_LT logfile=expdpost_EOM_GTBPROD_30112018.log compression=all parallel=12

    SET PAUSE ON
    SET PAUSE 'Press Return to Continue'
    SET PAGESIZE 60
    SET LINESIZE 300

    SELECT to_char(first_time, 'yyyy – mm – dd') aday,
    to_char(first_time, 'hh24') hour,
    count(*) total
    FROM v$log_history
    WHERE thread#=&EnterThreadId
    GROUP BY to_char(first_time, 'yyyy – mm – dd'),
    to_char(first_time, 'hh24')
    ORDER BY to_char(first_time, 'yyyy – mm – dd'),
    to_char(first_time, 'hh24') asc
    /

    SET PAGESIZE 90
    SET LINESIZE 150
    set heading on
    column "00:00" format 9999
    column "01:00" format 9999
    column "02:00" format 9999
    column "03:00" format 9999
    column "04:00" format 9999
    column "05:00" format 9999
    column "06:00" format 9999
    column "07:00" format 9999
    column "08:00" format 9999
    column "09:00" format 9999
    column "10:00" format 9999
    column "11:00" format 9999
    column "12:00" format 9999
    column "13:00" format 9999
    column "14:00" format 9999
    column "15:00" format 9999
    column "16:00" format 9999
    column "17:00" format 9999
    column "18:00" format 9999
    column "19:00" format 9999
    column "20:00" format 9999
    column "21:00" format 9999
    column "22:00" format 9999
    column "23:00" format 9999
    SELECT * FROM (
    SELECT * FROM (
    SELECT TO_CHAR(FIRST_TIME, 'DD/MM') AS "DAY"
    , SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '00', 1, 0), '99')) "00:00"
    , SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '01', 1, 0), '99')) "01:00"
    , SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '02', 1, 0), '99')) "02:00"
    , SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '03', 1, 0), '99')) "03:00"
    , SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '04', 1, 0), '99')) "04:00"
    , SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '05', 1, 0), '99')) "05:00"
    , SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '06', 1, 0), '99')) "06:00"
    , SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '07', 1, 0), '99')) "07:00"
    , SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '08', 1, 0), '99')) "08:00"
    , SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '09', 1, 0), '99')) "09:00"
    , SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '10', 1, 0), '99')) "10:00"
    , SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '11', 1, 0), '99')) "11:00"
    , SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '12', 1, 0), '99')) "12:00"
    , SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '13', 1, 0), '99')) "13:00"
    , SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '14', 1, 0), '99')) "14:00"
    , SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '15', 1, 0), '99')) "15:00"
    , SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '16', 1, 0), '99')) "16:00"
    , SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '17', 1, 0), '99')) "17:00"
    , SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '18', 1, 0), '99')) "18:00"
    , SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '19', 1, 0), '99')) "19:00"
    , SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '20', 1, 0), '99')) "20:00"
    , SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '21', 1, 0), '99')) "21:00"
    , SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '22', 1, 0), '99')) "22:00"
    , SUM(TO_NUMBER(DECODE(TO_CHAR(FIRST_TIME, 'HH24'), '23', 1, 0), '99')) "23:00"
    FROM V$LOG_HISTORY
    WHERE extract(year FROM FIRST_TIME) = extract(year FROM sysdate)
    GROUP BY TO_CHAR(FIRST_TIME, 'DD/MM')
    ) ORDER BY TO_DATE(extract(year FROM sysdate) || DAY, 'YYYY DD/MM') DESC
    ) WHERE ROWNUM
    round((num_rows * avg_row_len / 1024/1024), 2))
    order by 4 desc)
    WHERE ROWNUM <= 60;
    spool off;
    DEFRAGMENTAION FOR TOP 20 TABLES

    SELECT * FROM
    (SELECT
    SUBSTR(TABLE_NAME, 1, 21) TABLE_NAME,
    NUM_ROWS,
    AVG_ROW_LEN ROWLEN,
    BLOCKS,
    ROUND((AVG_ROW_LEN + 1) * NUM_ROWS / 1000000, 0) NET_MB,
    ROUND(BLOCKS * (8000 – 23 * INI_TRANS) *
    (1 – PCT_FREE / 100) / 1000000, 0) GROSS_MB,
    ROUND((BLOCKS * (8000 – 23 * INI_TRANS) * (1 – PCT_FREE / 100) –
    (AVG_ROW_LEN + 1) * NUM_ROWS) / 1000000) “WASTED_MB”
    FROM DBA_TABLES
    WHERE
    NUM_ROWS IS NOT NULL AND
    OWNER LIKE '%OWNER%' AND
    PARTITIONED = 'NO' AND
    (IOT_TYPE != 'IOT' OR IOT_TYPE IS NULL)
    ORDER BY 7 DESC)
    WHERE ROWNUM round((num_rows*avg_row_len/1024),2))
    order by 4 desc;

    DEFRAGMENTAION FOR SCHEMA WISE

    select owner,table_name,blocks,num_rows,avg_row_len,round(((blocks*8/1024)),2)||’MB’ “TOTAL_SIZE”, round((num_rows*avg_row_len
    /1024/1024),2)||’Mb’ “ACTUAL_SIZE”, round(((blocks*8/1024)-(num_rows*avg_row_len/1024/1024)),2) ||’MB’ “FRAGMENTED_SPACE” from
    dba_tables where owner in(‘a’,’b’,’c’,’d’) and round(((blocks*8/1024)-(num_rows*avg_row_len/1024/1024)),2)
    > 100 order by 8 desc;

    Dear Oracle Team,

    Please find the Queries below :

    alter session set nls_date_format = ‘dd.mm.yyyy hh24:mi:ss’;

    SELECT s.username,s.program, s.machine,s.program,s.logon_time,s.osuser
    FROM gv$session s
    WHERE s.last_call_et < (60*60*24)
    AND USERNAME NOT IN ('SYS','ÓRACLE','SYSRAC')
    ORDER BY 1 DESC;

    ——How to find objects in a currently running package.

    Run the package.Find SID from session browser.then query the below sql:

    select OWNER,TABLE_NAME,INDEX_NAME,STATUS from dba_indexes where table_name in (select object from gv$access where sid=1069 and owner not like '%SYS%' and type ='TABLE')

    select * from dba_objects where object_name in ('UII_COMMON_AFF_SERVICES_MSI','UII_API_ERROR_PKG','UII_API_CONSTANT_PKG','UII_API_COMMON_PKG')

    select * from dba_objects where object_name='EXPORTS';

  4. sankar said

    conn sys/wipr0cas@casdbnew as sysdba
    set colsep |
    SET LINES 180 PAGESIZE 1000
    select * from tbs_sizes order by PERC_FREE;

    SELECT /* + RULE */ df.tablespace_name “Tablespace”,
    df.bytes / (1024 * 1024) “Size (Mb)”,
    SUM(fs.bytes) / (1024 * 1024) “Free (Mb)”,
    Nvl(Round(SUM(fs.bytes) * 100 / df.bytes),1) “% Free”,
    Round((df.bytes – SUM(fs.bytes)) * 100 / df.bytes) “% Used”
    FROM dba_free_space fs,
    (SELECT tablespace_name,SUM(bytes) bytes
    FROM dba_data_files
    GROUP BY tablespace_name) df
    WHERE fs.tablespace_name (+) = df.tablespace_name
    GROUP BY df.tablespace_name,df.bytes
    ORDER BY 4;

    PROMPT####################TEMP_TABLESPACE_CASDBNEW#################################################################

    SELECT A.tablespace_name tablespace, D.mb_total, SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used,
    D.mb_total – SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_free
    FROM v$sort_segment A,
    (SELECT B.name,C.block_size, SUM (C.bytes) / 1024 / 1024 mb_total
    FROM v$tablespace B, v$tempfile C
    WHERE B.ts#= C.ts#
    GROUP BY B.name, C.block_size) D
    WHERE A.tablespace_name = D.name
    GROUP by A.tablespace_name, D.mb_total;

    show parameter undo

Leave a Reply to prince Cancel reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

 
%d bloggers like this: