How to use the purge command in adrci

 

How to use the purge command in adrci

Active Diagnostic Repository (ADR), which serves as a common repository for all log files, tracefiles, incidents etc, that the database produces.

ADR has a "short" and a "long" purging policy. The default values are:

• 720 hours (30 days) for SHORTP_POLICY
• 8760 hours (356 days) for LONGP_POLICY

Each policy controls different types of content:

These files are controlled by the value of LONGP_POLICY:

ALERT
INCIDENT
SWEEP
STAGE
HM

These files are controlled by the value of SHORTP_POLICY:

TRACE
CDUMP
UTSCDMP
IPS

Sometimes it may be necessary to manually purge the ADR. The following example will delete all trace files older than 48 hours:

adrci> purge -age 48 -type trace

Other examples (2160 hrs = 90 days):

purge -age 2160 -type alert
purge -age 2160 -type incident
purge -age 2160 -type cdump
purge -age 2160 -type stage
purge -age 2160 -type sweep
purge -age 2160 -type hm

The policies can be adjusted according to need by using the following commands :

adrci> show control

ADR Home = /u01/oracle/diag/rdbms/proddb01/proddb01:

*************************************************************************
ADRID                SHORTP_POLICY        LONGP_POLICY
-------------------- -------------------- --------------------
597879840            720                  8760

Set a new policy (2 and 3 days, respectively):

set control (SHORTP_POLICY = 48)
set control (LONGP_POLICY = 72)

An example from one of my databaes:

adrci> show incident -mode basic

ADR Home = /u01/oracle/diag/rdbms/proddb01/proddb01:
*************************************************************************

INCIDENT_ID          PROBLEM_KEY                                                 CREATE_TIME
-------------------- ----------------------------------------------------------- ----------------------------------------
30858                ORA 603                                                     2014-03-19 11:26:41.287000 +01:00
30857                ORA 600 [kqlidchg0]                                         2014-03-19 11:26:38.226000 +01:00
27284                ORA 603                                                     2014-03-18 14:42:28.228000 +01:00
27283                ORA 600 [kqlidchg0]                                         2014-03-18 14:42:23.706000 +01:00
.
.
.
17044                ORA 603                                                     2013-09-26 15:37:30.870000 +02:00
17043                ORA 600 [kqlidchg0]                                         2013-09-26 15:37:27.255000 +02:00
17042                ORA 603                                                     2013-09-26 15:35:45.918000 +02:00
17041                ORA 600 [kqlidchg0]                                         2013-09-26 15:35:42.239000 +02:00

45 rows fetched

Remove incidents that are older than 90 days:

adrci> purge -age 2160 -type incident

Another check of the incident list shows that the number has been reduced:

adrci> show incident -mode basic

ADR Home = /u01/oracle/diag/rdbms/sergat/sergat:
*************************************************************************

INCIDENT_ID          PROBLEM_KEY                                                 CREATE_TIME
-------------------- ----------------------------------------------------------- ----------------------------------------
30858                ORA 603                                                     2014-03-19 11:26:41.287000 +01:00
30857                ORA 600 [kqlidchg0]                                         2014-03-19 11:26:38.226000 +01:00
27284                ORA 603                                                     2014-03-18 14:42:28.228000 +01:00
27283                ORA 600 [kqlidchg0]                                         2014-03-18 14:42:23.706000 +01:00
.
.
.
24 rows fetched

 

Enable and disable data guard broker

https://logic.edchen.org/how-to-stop-data-guard-services/

1. Login to primary db server

$ dgmgrl sys/Manager_1@sslcdb

DGMGRL> show configuration

2. Check the apply and transport status:

Disable Data Guard with Broker

DGMGRL> show database sslcdb

Database - SSLCDBDR

Role:               PRIMARY

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

@stanby

$ dgmgrl sys/Manager_1@sslcdbdr

DGMGRL> show database sslcdbdr

Database - SSLCDBDR

  Role:               PHYSICAL STANDBY

3. DGMGRL Stop Transport

disable transport service of the primary database.

dgmgrl sys/Manager_1@sslcdb

DGMGRL> EDIT DATABASE sslcdbdr SET STATE='TRANSPORT-OFF';

Succeeded.

4. DGMGRL Stop Apply

dgmgrl sys/Manager_1@sslcdbdr

DGMGRL> edit database SSLCDBDR set state='APPLY-OFF';

Succeeded.

5. Disable DG Configuration

DGMGRL> DISABLE CONFIGURATION;

Disabled.

Enable Data Guard with Broker

6. Disable DG Configuration

DGMGRL> ENABLE CONFIGURATION;

Enableabled.

7. DGMGRL Start Transport

Enableable transport service of the primary database.

dgmgrl sys/Manager_1@sslcdb

DGMGRL> EDIT DATABASE sslcdbdr SET STATE='TRANSPORT-ON';

Succeeded.

8. DGMGRL Start Apply

dgmgrl sys/Manager_1@sslcdbdr

DGMGRL> edit database SSLCDBDR set state='APPLY-ON';

Succeeded.

SQL> show parameter dg_broker_start

NAME                                 TYPE        VALUE

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

dg_broker_start                      boolean     TRUE

SQL> select name, value from v$parameter where name = 'dg_broker_start';

NAME                       VALUE

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

dg_broker_start            TRUE

 SQL query to get DB info

spool dbinfo.html
set markup html on
select * from v$version;
select * from v$instance;
select * from v$parameter;
select * from v$resource_limit;
select * from dba_users;
select * from dba_registry;
SET linesize 235
col Parameter FOR a50
col SESSION FOR a28
col Instance FOR a55
col S FOR a1
col I FOR a1
col D FOR a1
col Description FOR a90

SELECT
a.ksppinm "Parameter",
decode(p.isses_modifiable,'FALSE',NULL,NULL,NULL,b.ksppstvl) "Session",
c.ksppstvl "Instance",
decode(p.isses_modifiable,'FALSE','F','TRUE','T') "S",
decode(p.issys_modifiable,'FALSE','F','TRUE','T','IMMEDIATE','I','DEFERRED','D') "I",
decode(p.isdefault,'FALSE','F','TRUE','T') "D",
a.ksppdesc "Description"
FROM x$ksppi a, x$ksppcv b, x$ksppsv c, v$parameter p
WHERE a.indx = b.indx AND a.indx = c.indx
AND p.name(+) = a.ksppinm
ORDER BY a.ksppinm;

set markup html off
spool off

 

ORA-10873: file 1 needs to be either taken out of backup mode or media recovered

Scenario :

Database went down due to power fluctuation and during that time hot backup was running.So once we started the database we face the following issue.

Error :

ORA-10873: file 1 needs to be either taken out of backup mode or media recovered

ORA-01110: data file 1: ‘D:\ORADB\RECTCDB\system01.dbf’

Cause :

Database is in backup mode while it went down.

Solution :

Step 1 : Sqlplus  ‘/as sysdba’

Step 2 : startup mount

Step 3 : Check backup file using below command.

select * from V$BACKUP

Step 4 : The following command can be used to take all of the data files out of hot backup mode:

SQL> ALTER DATABASE END BACKUP;

Step 5 :  SQL> Alter database open;

 

Enable and Disable the dataguard steps

Disable Data Guard

On Standby Database:
1. Cancel the Recovery job with following command

alter database recover managed standby database cancel;

2. Stop the destination

alter system set log_archive_dest_state_2=DEFER;


On Primary Database:
1. Stop the standby destination for transport archives.

ALTER SYSTEM SET log_archive_dest_state_2='DEFER' SCOPE=BOTH;


Enable the dataguard:
On Primary DB:
1. Enable the archive log transport:

ALTER SYSTEM SET log_archive_dest_state_2='ENABLE' SCOPE=BOTH;

On Standby DB:
1. Start the recovery process with following command:

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE disconnect from session;

2. Enable the log archive for switchover condition needed.

ALTER SYSTEM SET log_archive_dest_state_2='ENABLE' SCOPE=BOTH;

 
Shutdown and Start the Primary / Standby database dataguard.

https://dbaclass.com/article/remove-dataguard-configuration-from-primary-database/

 

Remove Dataguard Configuration From Primary Database:

    If you wish to remove the dataguard/standby setup from primary database i.e to make the primary database as standalone database without any standby database, then follow below steps.

1.Put primary database on maximum performance mode:

SQL> ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE;

2. Remove below parameters from spfile of primary database:

FAL_SERVER
FAL_CLIENT
LOG_ARCHIVE_CONFIG
DB_FILE_NAME_CONVERT
LOG_FILE_NAME_CONVERT
LOG_ARCHIVE_DEST_n – pointing to standby database like _2 or _3
LOG_ARCHIVE_DEST_STATE_n 
STANDBY_ARCHIVE_DEST
STANDBY_FILE_MANAGEMENT

alter system reset LOG_ARCHIVE_CONFIG scope=spfile;
alter system reset DB_FILE_NAME_CONVERT scope=spfile;
alter system reset LOG_FILE_NAME_CONVERT scope=spfile;
alter system reset STANDBY_FILE_MANAGEMENT scope=spfile;
alter system reset FAL_SERVER scope=spfile;
alter system reset FAL_CLIENT scope=spfile;
alter system reset LOG_ARCHIVE_DEST_STATE_2 scope=spfile;
alter system reset LOG_ARCHIVE_DEST_2 scope=spfile; --- destination pointing to standby
alter system reset STANDBY_ARCHIVE_DEST scope=spfile;

3. Drop all standby log files from primary:

SELECT GROUP# FROM V$STANDBY_LOG;

alter database drop standby logfile group 6;
alter database drop standby logfile group 7;
alter database drop standby logfile group 8;
alter database drop standby logfile group 9;

4. Restart the database to reflect the parameters

shutdown immediate;
startup

Now the standby database is completely separated from primary.
If you want to use the standby database as a standalone database, then follow below steps on standby

SQL> ALTER DATABASE ACTIVATE PHYSICAL STANDBY DATABASE;
SQL> SHUTDOWN IMMEDIATE;
SQL>STARTUP

You can change dbid using NID utility( as dbid would be same as primary)

 

ORA-02396: exceeded maximum idle time, please connect again.

Resolution

To resolve the above error there are three approaches:

1.Set the idle time to unlimited on the Oracle database. 
alter profile <profile_name> limit idle_time UNLIMITED;

before

After