Oracle Solaris Zone management

Solaris Zones is a software partitioning technology, which provides a means of virtualizing operating system services to create an isolated environment for running applications. 

This isolation prevents processes that are running in one zone from monitoring or affecting processes running in other zones

After installing Oracle Solaris 10 on a system, but before creating any zones, all processes run in the global zone. 

After you create a zone, it has processes that are associated with that zone and no other zone. 

Any process created by a process in a non-global zone is also associated with that non-global zone. Any zone which is not the global zone is called a non-global zone.

File and Directory : 

As a  Oracle Dba script to start database as part of cluster are placed under  /etc/rc0.d/.  Eg  /etc/rc0.d/K10dbora 

1) 

The directory /etc/zones contains the configuration files for each zone by the zone name. Examining these files will show the show the configuration of the zones.

The index file in this directory also contains the status of the zones:

# cat <zonename>.xml

cat /etc/zones/myzone.xml

Viewing Global Zone name from Local Zone :

Global-Zone# cat globalzonename.sh

#!/usr/bin/bash

for i in `/usr/sbin/zoneadm list -v | /usr/bin/egrep -v "ID|global" |/usr/bin/awk ' { print $4 } '`; do echo `uname -n` > $i/root/etc/GLOBALZ; done

Commands : 

1) 

# /usr/sbin/zoneadm list -vi to show status of all installed zones (i-stands for installed)

# /usr/sbin/zoneadm list -vc to show status of all configured zones (c-stands for configured which includes installed zones).

2) 

zonecfg -z zonename info    -->  To list resources used by each zones : Use zonecfg -z with the info option to list a specific zone configuration.

3) 

zonename

4) 

zpool list

5) 

zonestat -z zonename -r physical-memory 2

zonestat -z zone1,zone2 5 

Oracle Database SQLTXPLAIN (SQLT) Installation and report generation

 

There are  lot of documents online sqlt however  i have documented below for my quick reference and if it helps someone 

Sqlt Needs  installation which  becomes challenge sometimes , in that case lighter version sqlhc can be used  

Installation : 

Install SQLT

Install SQL by executing the installation script sqlt/install/sqcreate.sql connected as SYS:

# cd sqlt/install

# sqlplus / as sysdba

SQL> START sqcreate.sql

During the installation you will be asked to enter values for these parameters:

Optional Connect Identifier - In some restricted-access systems you may need to specify a connect identifier like @PROD. If a connect identifier is not needed, enter nothing and just hit the "Enter" key.

SQLTXPLAIN password - Case sensitive in most systems.

SQLTXPLAIN Default Tablespace - Select from a list of available permanent tablespaces which one should be used by SQLTXPLAIN for the SQLT repository. It must have more than 50MB of free space.

SQLTXPLAIN Temporary Tablespace - Select from a list of available temporary tablespaces which one should be used by SQLTXPLAIN for volatile operations and objects.

Optional Application User - This is the user that issued the SQL statement to be analyzed. For example, if this were an EBS system specify APPS, on Siebel you would specify SIEBEL and on People Soft 

SYSADM. You won't be asked to enter the password for this user. You can add additional SQLT users after the tool is installed, by granting them role SQLT_USER_ROLE or by using provided script sqlt/install/sqguser.sql

Licensed Oracle Pack. (T, D or N) - You can specify T for Oracle Tuning, D for Oracle Diagnostic or N for none. If T or D is selected, SQLT may include licensed content within the diagnostics files it produces. Default is T. If N is selected, SQLT installs with limited functionality.

Report Generation : 

Note  :  after 19c migration  sqltadmin object becomes invalid and  sqlt has to be reinstalled as per doc id 2920902.1 

== EXTRACT SQLT reports:

Starting with 12cR1, SQLT can be executed under SYS user -- for so, do as follows before starting execution:

Fix for :  ORA-06598   - inherit any  and  sqlt role 

SQL> CONN /as sysdba

SQL> GRANT INHERIT PRIVILEGES ON USER sys TO sqltxadmin;

1: To speedup SQLT Extraction -- connect as SQLTXADMIN user and run:

EXEC sqltxplain.sqlt$a.set_param('sta_time_limit_secs', '30');

EXEC sqltxplain.sqlt$a.set_sess_param('sql_tuning_advisor','N');

EXEC sqltxadmin.sqlt$a.set_param('ash_reports', '0');

EXEC sqltxadmin.sqlt$a.set_param('awr_reports', '0');

-- sqltxtract.sql script gather the details from memory or else from AWR snapshots.

cd sqlt/run

sqlplus ep/ep; -- connect as application user

@sqltxtract.sql sqlid sqltpassword

@/home/oracle/sqlt/run/sqltxtract.sql jkz7v96ym42c6 SQLTXPLAIN

 

Reference : 

How to Collect Standard Diagnostic Information Using SQLT for SQL Issues (Doc ID 1683772.1)

Document 215187.1 SQLT Diagnostic Tool

Tracing Oracle Database PLSQL using DBMS_HPROF and PLSQL Profiler

 

There may be times when a normal SQL trace of a PLSQL package is not sufficient. For example, when the SQL statements seem to be running fast, but the procedure takes a long time. It may be useful to understand which calls in the PLSQL package are taking the time. With this in mind, this document provides a step by step guide to tracing PLSQL using DBMS_HPROF.

One of way to debug  compilation issues is using below before compilcation

SQL> alter session set plsql_warnings='enable:all';

If we are writing Procedure ,  its always advice able to  add below in procedure to capture more details on error 

DBMS_UTILITY.FORMAT_ERROR_BACKTRACE holds the line number of the error.

DBMS_UTILITY.FORMAT_ERROR_STACK still holds the last error message.

DBMS_HPROF.

create directory PROF_DIR as '/home/oracle/pl';

grant all on directory plshprof_dir to pltest;

grant execute on dbms_hprof to pltest;

-- as user PLTEST 

execute dbms_hprof.create_tables();

With the last call the tables DBMSHP_TRACE_DATA, DBMSHP_RUNS, DBMSHP_FUNCTION_INFO and DBMSHP_PARENT_CHILD_INFO are created. They store the information in the database schema  and can be queried afterwards. 

Create the DBMS_HRPOF objects if not present .

Log on to the PLTEST  that owns the PLSQL package being profiled.

sqlplus <user>/<password>

SQL> @$ORACLE_HOME/rdbms/admin/dbmshptab.sql

Profiling Pl/sql 

SET SERVEROUTPUT ON;

EXEC DBMS_HPROF.START_PROFILING('PROF_DIR','P1_PROFILE_DATA.trc');

/* Within the same session run the P1 procedure */

EXEC P1;

/* Stop the profiling */

EXEC DBMS_HPROF.STOP_PROFILING;

Analyzing Raw File Generated : 

This raw profiler trace file can be analyzed in two ways:

1) Using the DBMS_HPROF.ANALYZE function.

2) Using the "plshprof" command line utility. 

Eg :

plshprof -output <out_file_name> P1_PROFILE_DATA.trc

Reference : 

How to Tune PL/SQL Applications and Identify Hot Spots using DBMS_HPROF (Doc ID 763944.1)

How to resolve error - PLS-00201: identifier 'DBMS_HPROF' must be declared? (Doc ID 2867790.1)

SRDC - Providing Supporting Information for PL/SQL Runtime Issues (Doc ID 2288591.1)

https://blogs.oracle.com/coretec/post/plsql-tuning-with-plsql-hierarchical-profiler

DBMS_PROFILER

 What about customers who have only limited privileges to their Oracle environment and who may not have access to a logical directory. The solution is the usage of the package DBMS_PROFILER, which writes the data directly into database tables. The procedure works similar to the described procedure of DBMS_HPROF.

First the corresponding repository tables PLSQL_PROFILER_RUNS, PLSQL_PROFILER_DATA and PLSQL_PROFILER_UNITS are created in the application schema. To do this, use the proftab.sql utility from the $ORACLE_HOME/rdbms/admin directory.

Configuring the profiler is fairly easy; in the schema that owns the PL/SQL objects the proftab.sql script (located at $ORACLE_HOME/rdbms/admin) 

must be run to create the tables that the profiler needs to store run data; those tables are:

PLSQL_PROFILER_UNITS

PLSQL_PROFILER_RUNS

PLSQL_PROFILER_DATA

An example of how to use the profiler once the setup has been successfully completed follows:

set linesize 1000 trimspool on

select dbms_profiler.start_profiler('DATES PACKAGE') from dual;

select dates_pkg.minutes_elapsed(sysdate-10, sysdate) from dual;

select dbms_profiler.stop_profiler from dual;

spool dates_package_profiler_run.rpt

@?/rdbms/admin/profsum

spool off

or 

execute dbms_profiler.start_profiler;

execute testprof;

execute dbms_profiler.stop_profiler;

select runid, run_date, run_comment from plsql_profiler_runs;  

Reference : 

https://docs.oracle.com/cd/E11882_01/appdev.112/e41502/adfns_profiler.htm#ADFNS02301

Script to produce HTML report with top consumers out of PL/SQL Profiler DBMS_PROFILER data (Doc ID 243755.1)

https://oracle-base.com/articles/11g/plsql-hierarchical-profiler-11gr1

Oracle Rac Node failover Testing

     As part of new build we need to  do  node failover testing hence documenting some steps  known to me 

Objective	Action	Expectations
To test accidental change of  IP addresses of the cluster	Modify etc/hosts and change theVIP addresses - Try starting thecluster	The cluster must not be able to bootup with error messages related to network Ips.
To test the failure of ASM instance  (ASM Instancecrash)	On Node 1, kill the processes  related to ASM	The cluster must not be affected by ASM crash on node 1
To test the failover of the network  card of the public IPaddress.	Plug out one of the cableconnecting  the node to thepublic network	The ping from the client  should beable to go thru
To test the stability of the db by killing  a ora server process	kill off a process related to the oracle daemons from a terminal.This should crash the database processes.	The sql session should not be affected and failover to the next node without any disruption
To test thecomplete failover of the session in event of a node(e.g. sudden shutdown	1. Connect from the client to the database via sqlplus. 2. power off the unix box inwhich the sqlplus connection isat.	The sql session should not be  affected and failover to the nextnode without any disruption
To test the failover session of a connected oracleuser in the event of an instance failureTo test the failoversession of aconnected oracleuser in the event of an instance failure	1. Connect from a client to node1 in the RAC Environment usingsqlplus. 2. Connect to the database onnode 1 and do a shutdown. sqlplus / as sysdba	The sql connection should be able to swing over to other instance and stillcontinue the select query # – sqlplus connection from client todb is still available
To test Crash of Grid  Processes	Kill Lmon process on rac node	They must be restarted  automatically
To  Test for automatically shifting of  scan listeners	Reboot Node 1 using  the OS reboot command	1. When noe 1 goes down all scan listeners must shift to remaining node 2. When node 1 comes up  scan listeners must again shift back to original node
To test storage  crash	Forcibly make scan disk unvisible to both nodes	The cluster must go down and should not start since rac does not safeguard to storage failures
TAF failoer test	to test sql and session   failover

 

Oracle Database Performance Hub report using DBMS_PERF

  When we  have performance issues normally we have to login to  oem  to get  performance details . 

  We can get quick snap of  performance using DBMS_PERF.REPORT_PERFHUB    as mentioned below .   Its old tool  introduced in 12c but  not frequently used . 

Command : 

SET  LONG   10000000 ;

 SET LONGC 10000000 ;

 SET LINESIZE 32767 ;

 SET PAGESIZE 0 ;

 SET TRIMSPOOL ON ;

SPOOL perfhub.html

SELECT DBMS_PERF.REPORT_PERFHUB FROM DUAL;

SPOOL OFF;

.Other option used :

SET HEADING OFF LINESIZE 32767 PAGESIZE 0 TRIMSPOOL ON TRIMOUT ON LONG 9999999 VERIFY OFF LONGCHUNKSIZE 100000 FEEDBACK OFF

SET TERMOUT OFF

spool perfhub.html

SELECT DBMS_PERF.REPORT_PERFHUB (

    is_realtime         => 0           -- (  0 = dba_hist, 1 = v$ash  )

  , outer_start_time    => sysdate-1

  , outer_end_time      => sysdate

  , selected_start_time => TIMESTAMP'2023-05-15 20:15:00'

  , selected_end_time   => TIMESTAMP'2023-05-15 20:45:00'

  , type=>'ACTIVE'

)

FROM dual ;

spool off

SET TERMOUT ON HEADING ON PAGESIZE 5000 LINESIZE 999 FEEDBACK ON 

 

Historical SQL monitoring report can be extracted for particular SQL statement using SQL Details report.

Example: To generate SQL details report to get the SQL monitoring information for particular SQL statement in historical mode.

SQL> set pages 0 linesize 32767 trimspool on trim on long 1000000 longchunksize 10000000

SQL> spool sql_details_history.html

SQL> select dbms_perf.report_sql(sql_id=>'9vkyyg1xj6fgc',is_realtime=>0,type=>'active',selected_start_time=>to_date('10-SEP-18 04:00:00','dd-MON-YY hh24:mi:ss'),selected_end_time=>to_date('10-SEP-18 05:00:00','dd-MON-YY hh24:mi:ss')) from dual;

SQL> spool off

Note in 19c or higher it is better to add "outer_start_time" and  "outer_end_time" to get the desired results:

SQL> set pages 0 linesize 32767 trimspool on trim on long 1000000 longchunksize 10000000

SQL> spool sql_details_history.html

SQL> select dbms_perf.report_perfhub( is_realtime=>0, outer_start_time=>to_date('06-SEP-2022 12:00:00','dd-MON-YYYY hh24:mi:ss'), outer_end_time=>to_date('06-SEP-2022 13:00:00','dd-MON-YYYY hh24:mi:ss'), selected_start_time=>to_date('06-SEP-2022 12:00:00','dd-MON-YYYY hh24:mi:ss'), selected_end_time=>to_date('06-SEP-2022 13:00:00','dd-MON-YYYY hh24:mi:ss')) from dual;

SQL> spool off

Generating Database Performance Hub Report :

@?/rdbms/admin/perfhubrpt.sql

Syntax

DBMS_PERF.REPORT_PERFHUB (
   is_realtime          IN NUMBER   DEFAULT NULL,
   outer_start_time     IN DATE     DEFAULT NULL,
   outer_end_time       IN DATE     DEFAULT NULL,
   selected_start_time  IN DATE     DEFAULT NULL,
   selected_end_time    IN DATE     DEFAULT NULL,
   inst_id              IN NUMBER   DEFAULT NULL,
   dbid                 IN NUMBER   DEFAULT NULL,
   monitor_list_detail  IN NUMBER   DEFAULT NULL,
   workload_sql_detail  IN NUMBER   DEFAULT NULL,
   addm_task_detail     IN NUMBER   DEFAULT NULL,
   report_reference     IN VARCHAR2 DEFAULT NULL,
   report_level         IN VARCHAR2 DEFAULT NULL,
   type                 IN VARCHAR2 DEFAULT 'ACTIVE',
   base_path            IN VARCHAR2 DEFAULT NULL);
 RETURN CLOB;

 REPORT_PERFHUB Function Parameters

Parameter	Description
is_realtime	If 1, then real-time. If NULL (default) or 0, then historical mode.
outer_start_time	Start time of outer period shown in the time selector. If NULL (default): If is_realtime=0 (historical), then 24 hours before outer_end_time. If is_realtime=1 (realtime mode), then 1 hour before outer_end_time.
outer_end_time	End time of outer period shown in the time selector. If NULL (default), then latest AWR snapshot. If is_realtime=0 (historical), then the latest AWR snapshot If is_realtime=1 (realtime mode), this is the current time (and any input is ignored)
selected_start_time	Start time period of selection. If NULL (default) If is_realtime=0, then 1 hour before selected_end_time If is_realtime=1, then 5 minutes before selected_end_time
selected_end_time	End time period of selection. If NULL (default) If is_realtime=0, then latest AWR snapshot If is_realtime=1, then current time
inst_id	Instance ID to for which to retrieve data If -1, then current instance If number is specified, then for that instance If NULL (default), then all instances
dbid	DBID to query. If NULL, then current DBID. If is_realtime=1, then DBID must be the local DBID.
monitor_list_detail	Top N in SQL monitor list for which to retrieve SQL monitor details. If NULL (default), then retrieves top 10 If 0, then retrieves no monitor list details
workload_sql_detail	Top N in Workload Top SQL list to retrieve monitor details, If NULL (default), then retrieves top 10 If 0, then retrieves no monitor list details
addm_task_detail	Maximum N latest ADDM tasks to retrieve If NULL (default), retrieves available data but no more than N If 0, then retrieves no ADDM task details
report_reference	Must be NULL when used from SQL*Plus.
report_level	'typical' will get all tabs in performance hub
type	Report type: 'ACTIVE' (default) 'xml' returns XML
base_path	URL path for HTML resources since flex HTML requires access to external files. This is only valid for type='ACTIVE' and is typically not used. Default value will retrieve the required files from OTN.

 

Reference : 

Monitoring Database Performance Using Performance Hub Report (Doc ID 2436566.1)

https://docs.oracle.com/en/database/oracle/oracle-database/21/arpls/DBMS_PERF.html#GUID-85CD8AB9-B6E0-444F-91A5-762EB92A74E9 

How To Get Historical SQL Monitor Report For SQL Statements (Doc ID 2555350.1)