Database Consultant (Passionate about Database & Cloud Technologies)

Database Architect,Core DBA ,APPSDBA,Mysql DBA,MongoDB,PostgreSQL,Installations,Upgrades on all Flavors of UNIX (LINUX,SOLARIS,HP(AIX)). Oracle E-Business Suite R12 (Upgrades,Patching,Cloning) AWS upgrades and implementation expert. OCI (Oracle Cloud Infrastructure) Architect, Exadata (Oracle Database Exadata Cloud at Customer(Exacc)),Superclusters ,Autonomous Databases, AWS RDS Customs , Sql Tuning Expert. ***Never Stop sharing,Learning and Growing***

Tuesday, August 17, 2021

Startup and Shutdown of a Container Database

start and Shutdown Databases :-

SHUTDOWN CONTAINER DATABASES

Connect to CDB

$sqlplus / as sysdba

SQL>shutdown immediate

Start-up Container (CDB)

Connect to CDB

$sqlplus / as sysdba

SQL>startup

Check Pluggable Databases (PDB)

Connect to CDB

$sqlplus / as sysdba

SQL>set linesize 100

col open_time format a25

select con_id,name,open_mode,open_time,ceil(total_size)/1024/1024 total_size_in_mb from v$pdbs

order by con_id asc;

Note: After you restart the CDB your PDBs will be in a mounted state you need to open the PDBs.

CDB CONNECT

Open Pluggable Databases (PDB)

Connect to CDB

$sqlplus / as sysdba

SQL>alter pluggable database all open;

Note: If you just want to open one pluggable database you can use the following.

SQL>alter pluggable database <pdb_name> open;

Check Pluggable Databases (PDB)

Connect to CDB

$sqlplus / as sysdba

SQL>set linesize 100

col open_time format a25

select con_id,name,open_mode,open_time,ceil(total_size)/1024/1024 total_size_in_mb from v$pdbs

order by con_id asc;

We can see after issuing the open state on all pluggable databases the open mode changes to read write.

PDB CONNECT

Check Services

$sqlplus / as sysdba
SQL>col name format a20
col network_name format a20
select con_id,con_name,name,network_name from v$active_services
order by con_id asc;

CON_ID CON_NAME NAME NETWORK_NAME

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

1 CDB$ROOT SYS$USERS

1 CDB$ROOT FNSTCDBXDB FNSTCDBXDB

1 CDB$ROOT FNSTCDB FNSTCDB

1 CDB$ROOT SYS$BACKGROUND

3 FNST20 FNST20_ebs_patch FNST20_ebs_patch

3 FNST20 fnst20 fnst20

3 FNST20 ebs_FNST20 ebs_FNST20

7 rows selected.

Ref:- http://db12c.blogspot.com/

AUTOMATIC WORKLOAD REPOSITORY – AWR

A list of the snapshots

set lines 100 pages 999

select snap_id

, snap_level

, to_char(begin_interval_time, 'dd/mm/yy hh24:mi:ss') begin

from dba_hist_snapshot

order by 1

REPORT QUERY

$ORACLE_HOME/rdbms/admin/awrrpt.sql

The retention and interval time settings

col snap_interval format a30

col retention format a30

select snap_interval

, retention

from dba_hist_wr_control;

Changing the interval settings

exec dbms_workload_repository.modify_snapshot_settings

(interval => 30) -- Gets a snapshot every 30 mins

Changing the retention periods

exec dbms_workload_repository.modify_snapshot_settings (retention => 10*24*60) -- Set to 10 days

Taking a snapshot manually

exec dbms_workload_repository.create_snapshot

Listing all the baselines

set lines 100

col baseline_name format a40

select baseline_id

, baseline_name

, start_snap_id

, end_snap_id

from dba_hist_baseline

order by 1

Creating a baseline

exec dbms_workload_repository.create_baseline (<start snap>, <endsnap>,'<name>')

Deleting a baseline

exec dbms_workload_repository.drop_baseline('<baseline name>')

Activating the default snapshot (active by default)

exec dbms_scheduler.enable('GATHER_STATS_JOB')

Deactivating the default snapshot (active by default)

exec dbms_scheduler.disable('GATHER_STATS_JOB')

The system time model

set lines 100 pages 999

select stat_name

, value

from v$sys_time_model

order by value desc

The session time model

set set lines 100 pages 999

select stat_name

, value

from v$sess_time_model

where sid = '&sid'

order by value desc

Active Session History (ASH) report, identifying active sessions and information about them

@$ORACLE_HOME/rdbms/admin/ashrpt.sql

Sunday, August 15, 2021

Alter Database Convert To Snapshot Standby Hangs

Oracle Database - Enterprise Edition - Version 19.3.0.0.0 and later

"SQL > alter database convert to snapshot standby"

hangs for indefinite time

alter database convert to snapshot standby
2021-02-09T12:39:12.909511-07:00
Created guaranteed restore point SNAPSHOT_STANDBY_REQUIRED_02/09/2021 12:39:12
.... (PID:5058): Database role changed from PHYSICAL STANDBY to SNAPSHOT STANDBY [kcvs.c:3111]
.... (PID:5058): Begin: SRL archival

CAUSE

snapshot standby conversion through sqlplus hangs while clearing standby redologs

Solution

Follow below steps to resolve the issue

clear all standby redeologs manually

SQL > alter database clear logfile group <n>;

SQL> shutdown immediate;

SQL> startup mount;

SQL> alter database convert to snapshot standby;

Example -

SQL> select group#,MEMBER from v$logfile where TYPE='STANDBY';

GROUP# MEMBER

---------- ------------------------------------------------- 4 /clu01/WYOFNPRD/archivelog/WYPRDCDB/onlinelog/o1_mf_4_jk8xvopq_.log

SQL> alter database clear logfile group 4;

Database altered.

Doc - 2758384.1

Saturday, August 7, 2021

Find the last RMAN backup’s log?

Execute the script to check RMAN log

set pagesize 2000

set linesize 2000

select

output

from

GV$RMAN_OUTPUT

where

session_recid =

(

select

session_recid

from

V$RMAN_BACKUP_JOB_DETAILS

where

session_key=(select max(session_key) from v$RMAN_BACKUP_JOB_DETAILS)

);

Output

connected to target database: WYPTCDB (DBID=2161084537)

using target database control file instead of recovery catalog

run {

report obsolete recovery window of 30 days;

delete noprompt obsolete recovery window of 30 days;

no obsolete backups found

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=927 device type=DISK

allocated channel: ORA_DISK_2

channel ORA_DISK_2: SID=1382 device type=DISK

allocated channel: ORA_DISK_3

channel ORA_DISK_3: SID=1611 device type=DISK

allocated channel: ORA_DISK_4

channel ORA_DISK_4: SID=15 device type=DISK

allocated channel: ORA_DISK_5

channel ORA_DISK_5: SID=468 device type=DISK

allocated channel: ORA_DISK_6

channel ORA_DISK_6: SID=929 device type=DISK

allocated channel: ORA_DISK_7

channel ORA_DISK_7: SID=1384 device type=DISK

allocated channel: ORA_DISK_8

channel ORA_DISK_8: SID=1610 device type=DISK

no obsolete backups found

Starting backup at 07-AUG-2021 15:15:44

using channel ORA_DISK_1

using channel ORA_DISK_2

using channel ORA_DISK_3

using channel ORA_DISK_4

using channel ORA_DISK_5

using channel ORA_DISK_6

using channel ORA_DISK_7

using channel ORA_DISK_8

channel ORA_DISK_1: starting compressed incremental level 0 datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

Finished Control File and SPFILE Autobackup at 07-AUG-2021 15:22:30

released channel: ORA_DISK_1

released channel: ORA_DISK_2

released channel: ORA_DISK_3

released channel: ORA_DISK_4

released channel: ORA_DISK_5

released channel: ORA_DISK_6

released channel: ORA_DISK_7

released channel: ORA_DISK_8

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=927 device type=DISK

allocated channel: ORA_DISK_2

channel ORA_DISK_2: SID=1382 device type=DISK

allocated channel: ORA_DISK_3

channel ORA_DISK_3: SID=1611 device type=DISK

allocated channel: ORA_DISK_4

channel ORA_DISK_4: SID=15 device type=DISK

allocated channel: ORA_DISK_5

channel ORA_DISK_5: SID=468 device type=DISK

allocated channel: ORA_DISK_6

channel ORA_DISK_6: SID=929 device type=DISK

allocated channel: ORA_DISK_7

channel ORA_DISK_7: SID=1384 device type=DISK

allocated channel: ORA_DISK_8

channel ORA_DISK_8: SID=1610 device type=DISK

exit;

Grant SYSDBA Fails With "ORA-01994: GRANT Failed: Password File Missing Or Disabled"

You have set the database parameter REMOTE_LOGIN_PASSWORDFILE to EXCLUSIVE and you have created a password file using the "orapwd" utility
but when you execute the following statement it fails:

SQL> grant SYSDBA to SYS;
grant SYSDBA to SYS
*
ERROR at line 1:
ORA-01994: GRANT failed: password file missing or disabled

The password file was created using this command:

% orapwd file=$ORACLE_HOME/dbs/orapworcl password=<password> entries=10

but ORACLE_SID was set to ORCL.

The $ORACLE_SID part of the password file name is case sensitive.

SOLUTION

Re-create the password file:

% orapwd file=$ORACLE_HOME/dbs/orapwORCL password=<password> entries=10

or

% orapwd file=$ORACLE_HOME/dbs/orapw$ORACLE_SID password=<password> entries=10

and then execute the grant again:

SQL> grant SYSDBA to SYS;

for Multitenant Archicture

SQL> grant sysdba to c##RMANWYCDB container=all;

Grant succeeded.

Tuesday, August 3, 2021

SQL Tuning Health-Check Script

SQL Tuning Health-Check Scripts

Overview of the SQL Tuning Health-Check Script

The SQL Tuning Health-Check Script is a tool developed by the Oracle Server Technologies Center of Expertise.

The tool, also known as SQLHC, is used to check the environment in which a single SQL Statement runs, checking Cost-based Optimizer (CBO) statistics, schema object metadata, configuration parameters and other elements that may influence the performance of the one SQL being analyzed.

The intention of SQLHC is to allow users to avoid SQL Performance from being affected by avoidable problems by ensuring that the environment that an individual SQL runs in is sound.

When executed for one SQL_ID, this script generates an HTML report with the results of a set of health-checks around the one SQL statement provided. You can find the SQL_ID of a statement from an AWR or ASH report or you can select it from the database using the V$SQL view. See:

Health-checks are performed over:

CBO Statistics for schema objects accessed by the one SQL statement being analyzed
CBO Parameters
CBO System Statistics
CBO Data Dictionary Statistics
CBO Fixed-objects Statistics

Link -

https://punitoracledba.blogspot.com/2021/08/how-to-determine-sqlid-for-sql-statement.html

INSTRUCTIONS

Login to the database server and set the environment used by the Database Instance

Download the "sqlhc.zip" archive file and extract the contents to a suitable directory/folder

Connect into SQL*Plus as SYS, a DBA account, or a user with access to Data Dictionary views and simply execute the "sqlhc.sql" script. It will request to enter two parameters:

Oracle Pack License (Tuning, Diagnostics or None) [T|D|N] (required)
If site has both Tuning and Diagnostics licenses then specify T (Oracle Tuning pack includes Oracle Diagnostics)
A valid SQL_ID for the SQL to be analyzed.

# sqlplus / as sysdba

SQL> START sqlhc.sql "T" djkbyr8vkc64h

Doc id - 1366133.1

Oracle database Locks checking

Oracle Database Locks checking

Issue coming to our system and its reporting with the Oracle Error which says

Caused By: Error executing SQL ALTER TABLE ***

Caused By: ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

The most common reason for this are either 'SELECT FOR UPDATE ' or some uncommitted INSERT statements.

For checking we found the sql script which will provide the information what causing the issue.

set linesize 125
set pages 9999
set colsep |
SELECT O.OBJECT_NAME, S.SID, S.SERIAL#, P.SPID, S.PROGRAM,SQ.SQL_FULLTEXT, S.LOGON_TIME FROM V$LOCKED_OBJECT L, DBA_OBJECTS O, V$SESSION S, V$PROCESS P, V$SQL SQ WHERE L.OBJECT_ID = O.OBJECT_ID AND L.SESSION_ID = S.SID AND S.PADDR = P.ADDR AND S.SQL_ADDRESS = SQ.ADDRESS;

OUTPUT

--------------------|----------|----------|------------------------|--------------------|--------------------------------------------------------------------------------|---------------

HSSKSKKKS 732| 47771|63613 |JDBC Thin Client |INSERT INTO JJKKSLLSLLS VALUES (:B1 ,:B2 ,:B3 ,:B4 ,:B5 ,:B6 ,:|03-AUG-21

ON | | | |

---

select s.sid, s.serial#, p.spid

from

v$session s,

v$process p

where

s.paddr = p.addr

and

s.sid in (select SESSION_ID from v$locked_object);

----

select sid,serial#,username from v$session where sid IN(select blocking_Session from v$session);

How to Determine the SQL_ID for a SQL Statement

How to determine the SQL_ID for a SQL Statement's associated text using Data Dictionary queries that execute against the V$SQL view, AWR & ASH data or associated views.

How to identify the SQL_ID of a statement

The SQL_ID of a statement can be found in an AWR or ASH report or by selecting it from the database data dictionary using the V$SQL view.

If the SQL can be identified with a particular identifiable string or by some kind of unique comment such as: /* TARGET SQL */ then this will make it easier to locate.

For Example:

SELECT /* TARGET SQL */ * FROM dual;

SELECT sql_id, plan_hash_value, substr(sql_text,1,40) sql_text  
FROM  v$sql 
WHERE sql_text like 'SELECT /* TARGET SQL */%'

SQL_ID        PLAN_HASH_VALUE SQL_TEXT
------------- --------------- ----------------------------------------
0xzhrtn5gkpjs       272002086 SELECT /* TARGET SQL */ * FROM dual

The plan_hash_value is included here for convenience.

You can also find the SQL_ID in the V$SQL view using a substitution variable:

SELECT sql_id, plan_hash_value, SUBSTR(sql_text,1,40) Text
FROM v$sql
WHERE sql_text LIKE '%&An_Identifiable_String%';

If SQL is no longer available in v$sql, you can find it in the

AWR history views: DBA_HIST_SQLTEXT and DBA_HIST_SQLSTAT :

SELECT
    s.sql_id,
    s.plan_hash_value,
    t.sql_text,
    s.snap_id
FROM
    dba_hist_sqlstat   s,
    dba_hist_sqltext   t
WHERE s.dbid = t.dbid
AND   s.sql_id = t.sql_id
AND   sql_text LIKE 'SELECT /* TARGET SQL */%'
ORDER BY
    s.sql_id

You can use the snap_id to determine when the SQL was executed along with

other information from AWR.

Sunday, August 1, 2021

Oracle Multitenant -How to Create,Stop,Start,Delete,modify a Database Service Using DBMS_SERVICE in Oracle Database

Oracle Multitenant -

How to Create,Stop,Start,Delete,modify a Database Service Using DBMS_SERVICE in Oracle Database

Oracle database has PL/SQL package called DBMS_SERVICE which is introduced in Oracle 10g, and has been extended with later releases. DBMS_SERVICE is used to create,stop,start and define database services.

The dbms_service package has the following stored procedures.

create_service
start_service
stop_service
delete_service
disconnect_service
modify_service
activate_service

Set session to PDB

alter session set container=PDB ;

Check available Service

Display informations about existing services using dba_services view as follows.

COLUMN name FORMAT A30

COLUMN network_name FORMAT A30

SELECT name,

network_name

FROM dba_services

ORDER BY 1;

Create a Service

We create a new service using the CREATE_SERVICE procedure. There are two overloads allowing you to amend a number of features of the service.

One overload accepts an parameter array, while the other allows you to set some parameters directly.

The only mandatory parameters are the the SERVICE_NAME and the NETWORK_NAME, which represent the internal name of the service in the data

dictionary and the name of the service presented by the listener respectively.

BEGIN

DBMS_SERVICE.create_service(

service_name => 'my_new_service',

network_name => 'my_new_service'

);

END;

Modify a Service

The MODIFY_SERVICE procedure allows us to alter parameters of an existing service. Like the CREATE_SERVICE procedure, there are two overloads allowing you to amend a number of features of the service. One overload accepts an parameter array, while the other allows you to set some parameters directly.

BEGIN

DBMS_SERVICE.modify_service(

service_name => 'my_new_service',

goal => DBMS_SERVICE.goal_throughput

);

END;

Stop a Service

The STOP_SERVICE procedure stops an existing service, so it is no longer available for connections via the listener.

BEGIN

DBMS_SERVICE.stop_service(

service_name => 'my_new_service'

);

END;

Delete a Service

The DELETE_SERVICE procedure removes an existing service.

BEGIN

DBMS_SERVICE.delete_service(

service_name => 'my_new_service'

);

END;

Disconnect a Service

The DISCONNECT_SERVICE procedure removes an existing service.

BEGIN

DBMS_SERVICE.disconnect_service(

service_name => 'my_new_service'

);

END;

Save state the PDB.

Save state the PDB. Other wise service needs to be manually started after PDB open each time.

SQL> alter pluggable database save state;
Pluggable database altered.