oracleagent - ORACLEAGENT BLOG

Check Installed RDBMS components

col comp_id for a10
col comp_name for a56
col version for a12
col status for a10
set pagesize 200
set lines 200
set long 999
select comp_id,comp_name,version,status from dba_registry;

Thank you for giving your valuable time to read the above information.
If you want to be updated with all our articles send us the Invitation or Follow us:
Ramkumar’s LinkedIn: https://www.linkedin.com/in/ramkumardba/
LinkedIn Group: https://www.linkedin.com/in/ramkumar-m-0061a0204/
Facebook Page: https://www.facebook.com/Oracleagent-344577549964301
Ramkumar’s Twitter : https://twitter.com/ramkuma02877110
Ramkumar’s Telegram: https://t.me/oracleageant
Ramkumar’s Facebook: https://www.facebook.com/ramkumarram8

How to Get Alert log location in db

set pagesize 299
set lines 299
col value for a65
select * from v$diag_info where NAME=’Diag Trace’;
(or)
Select value from V$diag_info;

How to Cleanup orphaned datapump jobs

— Find the orphaned Data Pump jobs:

SELECT owner_name, job_name, rtrim(operation) “OPERATION”,
rtrim(job_mode) “JOB_MODE”, state, attached_sessions
FROM dba_datapump_jobs
WHERE job_name NOT LIKE ‘BIN$%’ and state=’NOT RUNNING’
ORDER BY 1,2;

— Drop the tables

SELECT ‘drop table ‘ || owner_name || ‘.’ || job_name || ‘;’
FROM dba_datapump_jobs WHERE state=’NOT RUNNING’ and job_name NOT LIKE ‘BIN$%’

How to Cleanup orphaned datapump jobs

— Find the orphaned Data Pump jobs:

— Drop the tables

SELECT ‘drop table ‘ || owner_name || ‘.’ || job_name || ‘;’
FROM dba_datapump_jobs WHERE state=’NOT RUNNING’ and job_name NOT LIKE ‘BIN$%’

Get installed sqlpatches in database

— From 12c onward

set lines 2000
select patch_id,status,description from dba_registry_sqlpatch;
(or)
SET LINESIZE 400
COLUMN action_time FORMAT A20
COLUMN action FORMAT A10
COLUMN status FORMAT A10
COLUMN description FORMAT A40
COLUMN version FORMAT A10
COLUMN bundle_series FORMAT A10
SELECT TO_CHAR(action_time, ‘DD-MON-YYYY HH24:MI:SS’) AS action_time,action, status, description,version,patch_id, bundle_series FROM sys.dba_registry_sqlpatch;

— For 11g and below:
set lines 2000
select * from dba_registry_history;

GET INDEX USAGE

—Index monitoring is required, to find whether indexes are really in use or not. Unused can be dropped to avoid overhead.
— First enable monitoring usage for the indexes.

alter index INDEX_NAME.S_ASSET_TEST monitoring usage;

–Below query to find the index usage:

select * from v$object_usage;

Get sql query to html format

— We can spool output of an sql query to html format:

set pages 5000
SET MARKUP HTML ON SPOOL ON PREFORMAT OFF ENTMAP ON –
HEAD “EMPLOYEE REPORT –

–
–
” –
BODY “TEXT=’#FF00Ff'” –
TABLE “WIDTH=’90%’ BORDER=’5′”
spool report.html
Select * from scott.emp;
spool off
exit

row_count of all the tables of a schema

select table_name,
to_number(extractvalue(dbms_xmlgen.getXMLtype(‘select /+ PARALLEL(8) */ count() cnt from “&&SCHEMA_NAME”.’||table_name),’/ROWSET/ROW/CNT’))
rows_in_table from dba_TABLES
where owner=’&&SCHEMA_NAME’;

Disable or enable all triggers of schema

BEGIN
FOR j IN (SELECT trig_name
FROM user_triggers) LOOP

EXECUTE IMMEDIATE ‘ALTER TRIGGER ‘ || i.trig_name || ‘ DISABLE’;
END LOOP;
END;
/

Using RMAN Incremental Backups to Refresh Standby Database

In this article we are going to see about how to refresh the standby database using RMAN Incremental Backups.

My scenario is , I have faced the log difference between the primary and standby databases and then I sync the primary and standby databases by taking the incremental backup. Let us see in brief about here.

Overview steps:

Step:1 I have verified the log sequence on both the primary and standby databases then i found that there is a log difference between them.

Step:2 we can resolve this issue by taking the incremental backup on primary database and then restoring it in the standby database.

Step:3 First of all stop the redo transfer from primary to standby database by setting the log_archive_dest_2=DEFER.

Step:4 Find out the current_scn from v$database view in standby database.

Step:5 Then on primary, connect to the RMAN and take the incremental backup from the scn (which we noted in the standby database)

Step:6 Create the standby control file in primary database.

Step:7 By using server copy transfer the backup files, standby control file to the standby database.

Step:8 In standby database, replace the control file from backup.

Step:9 Stop the MRP(Managed Recovery Process) in standby database and shut down the database.

Step:10 Mount the standby database and connect to the RMAN, then catalog the backup pieces.

Step:11 Recover the standby database by using the incremental backup taken from the primary database.

Step:12 Enable log_archive_dest_state_2 parameter on primary and enable the MRP process on standby database.

Step:13 Then verify the log sequence sync between primary and standby databases.

Step:14 Finally we have refreshed/sync the standby database by using the incremental backup.

Step:1 Verify the log sequence on both primary and secondary database and found that there is a log difference between them.

on primary:

on standby:

Step:2 set the log_archive_dest_2=defer in primary database.

Step:3 Look for the current scn from the standby database.

Step:4 Take the incremental backup starting from that scn because after this scn only our primary and standby databases are not in sync.

Step:5 Create the standby controlfile in primary database.

Step:6 Move the incremental backup, standby controlfile from primary database to standby database through scp(server copy) and in standby database replace the controlfile from backup .