Soumya's Database Blog

April 17, 2023

How to purge dba_recyclebin older than 30 days in oracle database 19c

In the following post I will show you how to purge dba_recyclebin older than X number of days.

For e.g I want to purge recyclebin data older than 30 days

Oracle recommends not to use delete statement on dba_recyclebin as this doesn’t allow to delete data older than 7 days

SQL> Delete from dba_recylebin where droptime <sysdate -30;

ERROR at line 1:

ORA-01752: cannot delete from view without exactly one key-preserved table

Rather run the following script to generate a select sql and execute it to purge dba_recyclebin

SQL>set head off

SQL>spool purge.sql

SQL> select 'purge table '||owner||'."'||OBJECT_NAME||'";'

from dba_recyclebin where type='TABLE'

and to_date(droptime,'YYYY-MM-DD:HH24:MI:SS')<sysdate-30;

SQL>spool off;

This will create a sql with output like below

purge table JHO."BIN$xXn88xp3GejgU/GEF6wbcA==$0";

purge table JHO."BIN$xwxOc+5mLK7gU/GEF6yizA==$0";

purge table JHO."BIN$w4MOItowfC/gU/GEF6xrHA==$0";

purge table JHO."BIN$x4UA4M5zTZTgU/OEF6xB8w==$0";

Execute purge.sql file to purge all the tables for older than 30 days in recylebin

SQL>@purge.sql

Table purged.

…

April 10, 2023

Handy Oracle Data guard Broker Commands Part-II

Following command will validate the configuration of the specified database.

DGMGRL> validate database 'db_name';

DGMGRL> validate database proddr;

Database Role: Physical standby database

Primary Database: prod

Ready for Switchover: Yes

Ready for Failover: Yes (Primary Running)

Flashback Database Status:

prod : On

proddr: Off

Managed by Clusterware:

prod : NO

proddr: NO

Validating static connect identifier for the primary database prod...

The static connect identifier allows for a connection to database "prod".

DGMGRL>

Following command will display the current Fast-Start Failover configuration.

DGMGRL> show fast_start failover

Fast-Start Failover: Disabled

Protection Mode: MaxPerformance

Lag Limit: 30 seconds

Threshold: 30 seconds

Active Target: (none)

Potential Targets: (none)

Observer: (none)

Shutdown Primary: TRUE

Auto-reinstate: TRUE

Observer Reconnect: (none)

Observer Override: FALSE

Configurable Failover Conditions

Health Conditions:

Corrupted Controlfile YES

Corrupted Dictionary YES

Inaccessible Logfile NO

Stuck Archiver NO

Datafile Write Errors YES

Oracle Error Conditions:

(none)

The following command is used to convert database to snapshot standby.

DGMGRL> convert database 'db_name' to snapshot standby;

DGMGRL> convert database sbdb to Snapshot standby;

Converting database "sbdb" to a Snapshot Standby database, please wait...

Database "sbdb" converted successfully

The following command is used to convert database to physical standby.

DGMGRL> convert database 'db_name' to physical standby;

DGMGRL> convert database sbdb to Physical standby;

Converting database "sbdb" to a Physical Standby database, please wait...

Operation requires shut down of instance "PROD" on database "sbdb"

Shutting down instance "PROD"...

Connected to "SBDB"

Database closed.

Database dismounted.

ORACLE instance shut down.

Operation requires start up of instance "PROD" on database "sbdb"

Starting instance "PROD"...

Connected to an idle instance.

ORACLE instance started.

Connected to "SBDB"

Database mounted.

Connected to "SBDB"

Database "sbdb" converted successfully

The following command validates static connect identifier for given db_name

DGMGRL> VALIDATE STATIC CONNECT IDENTIFIER FOR SBDB;

Oracle Clusterware is not configured on database "sbdb".

Connecting to database "sbdb" using static connect identifier "(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=az-psdb1)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=SBDB_DGMGRL)(INSTANCE_NAME=PROD)(SERVER=DEDICATED)(STATIC_SERVICE=TRUE)))" ...

Succeeded.

The static connect identifier allows for a connection to database "sbdb".

DGMGRL>

Execute sql statement from DGMGRL prompt. Select command doesn’t work from this prompt.

DGMGRL> sql "alter system switch logfile"

Succeeded.

DGMGRL>

April 3, 2023

Shell script to purge diagnostic files using ADRCI utility

Using following shell script we can easily purge diagnostic files such as trace, alert, cdump etc.

$ vi /home/oracle/adrci_purge.sh

#!/usr/bin/ksh

# Description

# Shell script to purge ADR contents using ADRCI utility.

###

LOCKFILE=/tmp/adrci_purge_.lck

###

######

# Start Of Functions

# tidyup . common fuction called if error has occured

tidyup () {

rm -f ${LOCKFILE}

echo "ERROR: Purge aborted at `date` with exit code ${ERR}"

exit ${ERR}

}

######

# End Of Functions

### Main Program

# Check user is oracle

USERID=`/usr/bin/id -u -nr`

if [ $? -ne 0 ]

then

echo "ERROR: unable to determine uid"

exit 99

#if [ "${USERID}" != "oracle" ]

#then

# echo "ERROR: This script must be run as oracle"

# exit 98

#fi

echo "INFO: Purge started at `date`"

# Check if lockfile exists

if [ -f ${LOCKFILE} ]

then

echo "ERROR: Lock file already exists"

echo " Purge already active or incorrectly terminated"

echo " If you are sure tidy isn.t active, please remove "

echo " ${LOCKFILE}"

#rm -f ${LOCKFILE}

exit 97

# Create lock file

touch ${LOCKFILE} 2>/dev/null

if [ $? -ne 0 ]

then

echo "ERROR: Unable to create lock file"

exit 96

# Purge ADR contents

echo "INFO: adrci purge started at `date`"

ALL_DATABASES=`cat /etc/oratab|grep -v "^#"| grep -Eo '^[^ ]+' |cut -f1 -d: -s`

for DB in $ALL_DATABASES

unset TWO_TASK

export ORACLE_SID=$DB

export ORACLE_HOME=`grep "^${DB}:" /etc/oratab|cut -d: -f2 -s`

export PATH=$ORACLE_HOME/bin:$PATH

echo "---> Database $ORACLE_SID, using home $ORACLE_HOME"

$ORACLE_HOME/bin/adrci exec="set base /u01/oracle;show homes"|grep -v : | while read file_line

echo "INFO: adrci purging diagnostic destination " $file_line

echo "INFO: purging ALERT older than 60 days .."

$ORACLE_HOME/bin/adrci exec="set base /u01/oracle;set homepath $file_line;purge -age 43200 -type ALERT"

echo "INFO: purging INCIDENT older than 60 days .."

$ORACLE_HOME/bin/adrci exec="set base /u01/oracle;set homepath $file_line;purge -age 43200 -type INCIDENT"

echo "INFO: purging TRACE older than 60 days .."

$ORACLE_HOME/bin/adrci exec="set base /u01/oracle;set homepath $file_line;purge -age 43200 -type TRACE"

echo "INFO: purging CDUMP older than 60 days .."

$ORACLE_HOME/bin/adrci exec="set base /u01/oracle;set homepath $file_line;purge -age 43200 -type CDUMP"

echo "INFO: purging HM older than 60 days .."

$ORACLE_HOME/bin/adrci exec="set base /u01/oracle;set homepath $file_line;purge -age 43200 -type HM"

echo ""

done

# All completed

rm -f ${LOCKFILE}

echo "SUCC: Purge completed successfully at `date`"

exit 0

# crontab -e

0 1 * * * /orabackup/scripts/adrci_purge.sh

March 27, 2023

How to execute sql tuning advisor for SQL ID in 19c

Description: - SQL Tuning Advisor is a program provided by sql that comes along with oracle installation. This advisor can be executed against a sql id and it generates a report with recommendations which generally includes

· Collection of object statistics

· Creation of indexes

· Rewriting SQL statements

· Creation of SQL profiles

· Creation of SQL plan baselines

The objective to use this program is to avoid tedious manual tuning which DBA’s generally do to fix a bad query.

Note:-As mentioned earlier, this advisor comes along with default oracle installation but It requires Diagnostic and Tuning packs to use the SQL Tuning Advisor.

Let me demonstrate how to execute SQL tuning advisor against a SQL id

Step 1.

To run the advisor we need to create tuning task ,

But before that , we need to have the sql id against which we would run the advisor.

For e.g. let us assume the sql id is – 8d5cry63fwerg65

SET serveroutput ON

DECLARE

l_sql_tune_task_id VARCHAR2(100);

BEGIN

l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (

sql_id => '&&my_sql_id',

scope => DBMS_SQLTUNE.scope_comprehensive,

time_limit => 120000,

task_name => 'sql_tuning_task_&&my_sql_id',

description => 'Tuning task for statement &&my_sql_id.');

DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);

END;

Enter value for my_sql_id: 8d5cry63fwerg65

Step 2.

Check the task status.

SELECT task_name, STATUS FROM dba_advisor_log WHERE task_name LIKE 'sql_tuning_task_&&my_sql_id';

TASK_NAME STATUS

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

sql_tuning_task_8d5cry63fwerg65 INITIAL

Step 3.

Execute the sql tuning task

EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => 'sql_tuning_task_&&my_sql_id');

Step 4.

Check the task status again

SELECT task_name, STATUS FROM dba_advisor_log WHERE task_name LIKE 'sql_tuning_task_&&my_sql_id';

TASK_NAME STATUS

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

sql_tuning_task_8d5cry63fwerg65 COMPLETED

Step 5.

Review the recommendations by SQL Tuning Advisor

SET LINES 222

SET pages 30000

SET long 999999

SET longc 99999999

SELECT DBMS_SQLTUNE.report_tuning_task('sql_tuning_task_&&my_sql_id') AS recommendations FROM dual;

Step 5.

Implement the Recommendations only if you find the recommendations are satisfactory.

execute dbms_sqltune.accept_sql_profile(task_name => ‘sql_tuning_task_8d5cry63fwerg65', task_owner => 'SYS', replace => TRUE);

Stpe 6.

To drop the tuning task

BEGIN

DBMS_SQLTUNE.drop_tuning_task (task_name => 'sql_tuning_task_&&my_sql_id');

END;

Step 7.

Verify the task status

SELECT task_name, STATUS FROM dba_advisor_log WHERE task_name LIKE 'sql_tuning_task_&&my_sql_id';

March 20, 2023

Unexpected error occurred during authentication in ODA

Issue:- oda-admin credential is not working

So, few days ago while trying to login ODA management url using its credential, it gave me following message

Solution:-

This issue happens when there is some problem with the credential of the user or maybe the password is expired.

To reset the password,

[root@server1 ~]# /opt/oracle/dcs/bin/odacli-adm set-credential --username oda-admin

Enter user password:

Retype user password:

More info:
The password length (minimum:9 maximum:30) characters
The password should start with an alpha character (a-z, A-Z)
The password should contain ALL of the following:

a) At least two uppercase letters
b) At least two lowercase letters
c) At least two numbers
d) At least two special characters, valid characters are # _ -

Once, password is reset try to login the console. It should work.

March 13, 2023

Handy Oracle Data guard Broker Commands Part-I

Handy Oracle Data guard Broker Commands: -

Here,

Primary database is : ORCL

Standby Database is : ORCLDR

dgmgrl / [Here connection happens through os authentication]

dgmgrl sys/<sys_password>@<connect dentifier>

To check current status and configuration

DGMGRL> show configuration;

Configuration - my_dg_config

Protection Mode: MaxPerformance

Members:

orcl - Primary database

orcldr - Physical standby database

Fast-Start Failover: Disabled

Configuration Status:

SUCCESS (status updated 16 seconds ago)

To Display information of members under Dataguard configuration

DGMGRL> show database orcl

Database - orcl

Role: PRIMARY

Intended State: TRANSPORT-ON

Instance(s):

orcl

Database Status:

SUCCESS

DGMGRL> show database orcldr

Database - orcldr

Role: PHYSICAL STANDBY

Intended State: APPLY-ON

Transport Lag: 0 seconds (computed 0 seconds ago)

Apply Lag: 0 seconds (computed 0 seconds ago)

Average Apply Rate: 30.00 KByte/s

Real Time Query: OFF

Instance(s):

orcl

Database Status:

SUCCESS

Performing a Switchover using dataguard broker

DGMGRL> switchover to orcldr;

Performing switchover NOW, please wait...

Operation requires a connection to database "orcldr"

Connecting ...

Connected to "ORCLDR"

Connected as SYSDBA.

New primary database "orcldr" is opening...

Operation requires start up of instance "orcl" on database "orcl"

Starting instance "orcl"...

Connected to an idle instance.

ORACLE instance started.

Connected to "orcl"

Database mounted.

Switchover succeeded, new primary is "orcldr"

To Display detailed information of configuration

DGMGRL> show configuration verbose

Configuration - my_dg_config

Protection Mode: MaxPerformance

Members:

orcl - Primary database

orcldr - Physical standby database

Properties:

FastStartFailoverThreshold = '30'

OperationTimeout = '30'

TraceLevel = 'USER'

FastStartFailoverLagLimit = '30'

CommunicationTimeout = '180'

ObserverReconnect = '0'

FastStartFailoverAutoReinstate = 'TRUE'

FastStartFailoverPmyShutdown = 'TRUE'

BystandersFollowRoleChange = 'ALL'

ObserverOverride = 'FALSE'

ExternalDestination1 = ''

ExternalDestination2 = ''

PrimaryLostWriteAction = 'CONTINUE'

ConfigurationWideServiceName = 'orcl_CFG'

Fast-Start Failover: Disabled

Configuration Status:

SUCCESS

To Display properties and a brief information of the database

DGMGRL> show database verbose orcldr

Database - orcldr

Role: PHYSICAL STANDBY

Intended State: APPLY-ON

Transport Lag: 0 seconds (computed 1 second ago)

Apply Lag: 0 seconds (computed 1 second ago)

Average Apply Rate: 23.00 KByte/s

Active Apply Rate: 765.00 KByte/s

Maximum Apply Rate: 2.26 MByte/s

Real Time Query: OFF

Instance(s):

orcl

Properties:

DGConnectIdentifier = 'orcldr'

ObserverConnectIdentifier = ''

FastStartFailoverTarget = ''

PreferredObserverHosts = ''

LogShipping = 'ON'

RedoRoutes = ''

LogXptMode = 'ASYNC'

DelayMins = '0'

Binding = 'optional'

MaxFailure = '0'

ReopenSecs = '300'

NetTimeout = '30'

RedoCompression = 'DISABLE'

PreferredApplyInstance = ''

ApplyInstanceTimeout = '0'

ApplyLagThreshold = '30'

TransportLagThreshold = '30'

TransportDisconnectedThreshold = '30'

ApplyParallel = 'AUTO'

ApplyInstances = '0'

StandbyFileManagement = ''

ArchiveLagTarget = '0'

LogArchiveMaxProcesses = '0'

LogArchiveMinSucceedDest = '0'

DataGuardSyncLatency = '0'

LogArchiveTrace = '0'

LogArchiveFormat = ''

DbFileNameConvert = ''

LogFileNameConvert = ''

ArchiveLocation = ''

AlternateLocation = ''

StandbyArchiveLocation = ''

StandbyAlternateLocation = ''

InconsistentProperties = '(monitor)'

InconsistentLogXptProps = '(monitor)'

LogXptStatus = '(monitor)'

SendQEntries = '(monitor)'

RecvQEntries = '(monitor)'

HostName = 'EGC-MUN-STBY-PH'

StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=SERVER1)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=ORCLDR_DGMGRL)(INSTANCE_NAME=orcl)(SERVER=DEDICATED)))'

TopWaitEvents = '(monitor)'

SidName = '(monitor)'

Log file locations:

Alert log : D:\ORACLE\diag\rdbms\orcldr\orcl\trace\alert_orcl.log

Data Guard Broker log : D:\ORACLE\diag\rdbms\orcldr\orcl\trace\drcorcl.log

Database Status:

SUCCESS