Monitoring long-running operations

 Monitoring long-running operations

set pages 1000 lines 1000

col start_time for a20

col opname for a20

col message for a20

SELECT SID,serial#,TO_CHAR (start_time, 'YYYY-MM-DD HH24:MI:SS') start_time,

        opname, time_remaining, elapsed_seconds, message,

        totalwork sofar, (sofar / totalwork) * 100 pct_done

   FROM gv$session_longops

  WHERE totalwork > sofar

    AND opname NOT LIKE '%aggregate%' and sid='&SID';

SQL Tuning Task using the Oracle DBMS_SQLTUNE package

 SQL Tuning Task using the Oracle DBMS_SQLTUNE package

-- Set environment formatting for SQL*Plus

SET PAGES 1000

SET LINES 1000

SET LONG 600000                -- Allow large output for tuning report

SET PAGESIZE 9999

SET LINESIZE 155

SET VERIFY OFF                 -- Suppress variable substitution display

-- Define column format for the output

COLUMN recommendations FOR a150

-- Accept input from user for tuning task name

ACCEPT task_name -

       PROMPT 'Task_Name: '

-- PL/SQL block to create and execute the SQL tuning task

DECLARE

    ret_val VARCHAR2(4000);

BEGIN

    -- Create a tuning task for the specified SQL_ID and time limit

    ret_val := DBMS_SQLTUNE.create_tuning_task(

                  task_name    => '&&Task_name',      -- Task name passed by user

                  sql_id       => '&sql_id',          -- SQL ID to be tuned

                  time_limit   => &time_limit         -- Time limit for tuning in seconds

               );

    -- Execute the tuning task

    DBMS_SQLTUNE.execute_tuning_task('&&Task_name');

END;

/

-- Display tuning recommendations

SELECT DBMS_SQLTUNE.report_tuning_task('&&task_name') AS recommendations 

FROM dual;

-- Clear substitution variable

UNDEF task_name

Essential DBA Scripts

 

Essential DBA Scripts

Database Size check

-- Calculates total size of datafiles, redo logs, control files, and overall database size in MB

SELECT 

    DF.TOTAL / 1048576 AS "DataFile Size MB",

    LOG.TOTAL / 1048576 AS "Redo Log Size MB",

    CONTROL.TOTAL / 1048576 AS "Control File Size MB",

    (DF.TOTAL + LOG.TOTAL + CONTROL.TOTAL) / 1048576 AS "Total Size MB"

FROM 

    dual,

    (SELECT SUM(bytes) AS TOTAL FROM dba_data_files) DF,

    (SELECT SUM(bytes) AS TOTAL FROM v$log) LOG,

    (SELECT SUM((cffsz+1)*cfbsz) AS TOTAL FROM x$kcccf) CONTROL;

Database Uptime

-- Shows database instance startup time

SELECT 

    instance_name,

    TO_CHAR(startup_time, 'MM/DD/YYYY HH24:MI:SS') AS startup_time 

FROM 

    v$instance;

-- Displays the database name and PMON process start time (approximate DB start time)

COL database_name FOR a20

SELECT 

    database_name, 

    TO_CHAR(logon_time, 'DD-MM-YYYY HH24:MI:SS') AS pmon_start_time 

FROM 

    v$session 

WHERE 

    program LIKE '%PMON%';

Redo Log Details

-- Redo log group and member details with size and status

COL REDOLOG_FILE_NAME FORMAT a50

COL STATUS FOR a15

SELECT

    a.GROUP#,

    a.THREAD#,

    a.SEQUENCE#,

    a.ARCHIVED,

    a.STATUS,

    b.MEMBER AS REDOLOG_FILE_NAME,

    (a.BYTES / 1024 / 1024) AS SIZE_MB

FROM 

    v$log a

JOIN 

    v$logfile b ON a.GROUP# = b.GROUP#

ORDER BY 

    a.GROUP#;

-- Summary of redo log groups

SELECT 

    GROUP#,

    THREAD#,

    SEQUENCE#,

    BYTES / 1024 / 1024 AS SIZE_MB,

    MEMBERS,

    STATUS 

FROM 

    v$log;

Control File Details

-- Displays the location of control files from the parameter file

SHOW PARAMETER control_files;

-- Lists control file names from dynamic performance view

SELECT NAME FROM V$CONTROLFILE;

RMAN Backup Location

-- (Placeholder) You may run:

SHOW ALL;

-- or check the default backup destination

SELECT * FROM v$rman_configuration;

Daily Archive Log Generation

-- Archive log generation per day in GB and number of archives

SELECT 

    TRUNC(COMPLETION_TIME, 'DD') AS day,

    THREAD#,

    ROUND(SUM(BLOCKS * BLOCK_SIZE) / 1024 / 1024 / 1024) AS GB,

    COUNT(*) AS archives_generated

FROM 

    v$archived_log

GROUP BY 

    TRUNC(COMPLETION_TIME, 'DD'), THREAD#

ORDER BY 

    day;

Hourly Archive Log Generation

-- Displays hourly archive generation in GB

SET PAGES 1000;

ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';

SELECT 

    TRUNC(COMPLETION_TIME, 'HH') AS hour,

    THREAD#,

    ROUND(SUM(BLOCKS * BLOCK_SIZE) / 1024 / 1024 / 1024) AS GB,

    COUNT(*) AS archives

FROM 

    v$archived_log

GROUP BY 

    TRUNC(COMPLETION_TIME, 'HH'), THREAD#

ORDER BY 

    hour;

Character Set of the Database

-- Check current NLS character set used in the database

COL PARAMETER FOR a30

COL VALUE FOR a50

SELECT 

    * 

FROM 

    nls_database_parameters 

WHERE 

    parameter = 'NLS_CHARACTERSET';

Query to display tablespace usage details: size, used space, free space, and percentage used

Query to display tablespace usage details: size, used space, free space, and percentage used

SELECT 

    Substr(df.tablespace_name, 1, 20) "Tablespace Name",   -- Tablespace name (first 20 characters)

    Substr(df.file_name, 1, 40) "File Name",               -- Datafile name (first 40 characters)

    Round(df.bytes / 1024 / 1024, 2) "Size (M)",           -- Total size of the datafile in MB

    Round(e.used_bytes / 1024 / 1024, 2) "Used (M)",       -- Used space in the datafile in MB

    Round(f.free_bytes / 1024 / 1024, 2) "Free (M)",       -- Free space in the datafile in MB

    Rpad(                                                   -- ASCII-based progress bar to represent % used

        ' ' || Rpad('X', Round(e.used_bytes * 10 / df.bytes, 0), 'X'), 

        11, 

        '-'

    ) "% Used"

FROM 

    DBA_DATA_FILES df                                        -- Datafile metadata from DBA view

    LEFT OUTER JOIN (

        -- Aggregating used space from dba_extents per file

        SELECT 

            file_id, 

            Sum(Decode(bytes, NULL, 0, bytes)) used_bytes

        FROM 

            dba_extents

        GROUP BY 

            file_id

    ) e ON e.file_id = df.file_id

    LEFT OUTER JOIN (

        -- Getting maximum free extent size per file from dba_free_space

        SELECT 

            Max(bytes) free_bytes, 

            file_id

        FROM 

            dba_free_space

        GROUP BY 

            file_id

    ) f ON f.file_id = df.file_id

ORDER BY 

    df.tablespace_name, 

    df.file_name;

ORDS Configuration Multiple database in single tomcat server.

 ORDS Configuration Multiple database in single tomcat server.

 

Create directories for ords configuration

 

Create configuration and logs directory

 

Cd /u01/test/ords

Mkdir -p  conf

Cd conf

Mkdir logs

 

Ords configuration database 1

 

Execute below script and mention that java home and ords path  correctly to configure ORDS.

 

export JAVA_HOME=/u01/app/tomcat_install/java/jdk-13.0.1 export PATH=/u01/app/tomcat_install/java/jdk-13.0.1/bin:$PATH export ORDS_HOME=/u01/test/ords export ORDS_CONFIG=/u01/test/ords/conf export ORDS_LOGS=${ORDS_CONFIG}/logs export DB_PORT=1523 export DB_SERVICE=test export SYSDBA_USER=SYS export SYSDBA_PASSWORD=oracle export ORDS_PASSWORD=**********     ${ORDS_HOME}/bin/ords --config ${ORDS_CONFIG} install \ --log-folder ${ORDS_LOGS} \ --admin-user ${SYSDBA_USER} \ --db-hostname ${HOSTNAME} \ --db-port ${DB_PORT} \ --db-servicename ${DB_SERVICE} \ --feature-db-api true \ --feature-rest-enabled-sql true \ --feature-sdw true \ --gateway-mode proxied \ --gateway-user APEX_PUBLIC_USER \ --proxy-user \ --password-stdin <<EOF ${SYSDBA_PASSWORD} ${ORDS_PASSWORD} EOF

 

Copy war file and apex images to tomcat webapps location

 

Create image directory in tomcat webapps location Cd /u01/test/tomcat/apache-tomcat-9.0.73/webapps/ Mkdir i   [oracle@tom ~]$ cd /u01/test/apex/images/ [oracle@tom images]$ cp -R * /u01/test/tomcat/apache-tomcat-9.0.73/webapps/i [oracle@tom images]$   [oracle@tom ords]$ cp ords.war /u01/test/tomcat/apache-tomcat-9.0.73/webapps/

 

create tomcat env and add java_opts variable.

 

export JAVA_HOME=/u01/app/tomcat_install/java/jdk-13.0.1 export CATALINA_HOME=/u01/test/tomcat/apache-tomcat-9.0.73 export CATALINA_BASE=$CATALINA_HOME export PATH=/u01/app/tomcat_install/java/jdk-13.0.1/bin:$PATH export ORDS_CONFIG=/u01/test/ords/conf export JAVA_OPTS="-Dconfig.url=${ORDS_CONFIG} -Xms1024M -Xmx1024M"

 

 

Start the tomcat services. Verify the url.

 

Adding another ords connection in new database.

 

 

Mentioned that new database service name listener port correctly

 

export JAVA_HOME=/u01/app/tomcat_install/java/jdk-13.0.1 export PATH=/u01/app/tomcat_install/java/jdk-13.0.1/bin:$PATH export ORDS_HOME=/u01/test/ords export ORDS_CONFIG=/u01/test/ords/conf export ORDS_LOGS=${ORDS_CONFIG}/logs export DB_PORT=1524 export DB_SERVICE=loans export SYSDBA_USER=SYS export SYSDBA_PASSWORD=oracle export ORDS_PASSWORD=******     ${ORDS_HOME}/bin/ords --config ${ORDS_CONFIG} install \ --log-folder ${ORDS_LOGS} \ --admin-user ${SYSDBA_USER} \ --db-hostname ${HOSTNAME} \ --db-pool sample\ --db-port ${DB_PORT} \ --db-servicename ${DB_SERVICE} \ --feature-db-api true \ --feature-rest-enabled-sql true \ --feature-sdw true \ --gateway-mode proxied \ --gateway-user APEX_PUBLIC_USER \ --proxy-user \ --password-stdin <<EOF ${SYSDBA_PASSWORD} ${ORDS_PASSWORD} EOF

 

To check that new connection pool status

 

[oracle@tom loans]$ cd .. [oracle@tom databases]$ tree . ├── default │   ├── pool.xml │   └── wallet │       └── cwallet.sso └── sample ├── pool.xml └── wallet └── cwallet.sso   4 directories, 4 files

 

Now have two paths available, representing the two connections. The URL will be different, depending on if you configured ORDS

 

REF Link ---- https://oracle-base.com/articles/misc/oracle-rest-data-services-ords-configure-multiple-databases-22-onward

 

 

http://192.168.106.128:9090/ords/  --

 

 

http://192.168.106.128:9090/ords/sample--

 

 

http://192.168.106.128:9090/ords/apxprd 

 

  

Sample Screens