Daily DBA Task related queries

 ###############Grep Ora- error from Alert log:#################

grep 'Ora-' alert_db.log |tail

Only 5 lines with timings

grep -A5 'Ora-' alert_db.log |tail

#####Total Oracle proces spawned####

ps -ef |grep 'ora'|wc -l

####Total process###

ps -ef |wc -l

--OS Process

expr total process - oracle process count

oerr ora 600

---How to find parallel process:

ps -ef |grep 'ora_p0'

======= KILL Sessions Scenerios====================

You are in situation to quickly kill multiple sessions which consume high resource on database which cause performance issues. You can use the below scripts to quickly act based on your scenario

--SQL script to kill all the sessions from the database — Note: Use this script with caution as this will kill all the sessions from the database

SELECT 'ALTER SYSTEM KILL SESSION '''||sid||','||serial#||''' IMMEDIATE;' FROM v$session;

--SQL script to kill all the sessions run with username called TEST

SELECT 'ALTER SYSTEM KILL SESSION '''||sid||','||serial#||''' IMMEDIATE;' FROM v$session WHERE USERNAME LIKE '%TEST%' 

SELECT 'ALTER SYSTEM KILL SESSION '''||sid||','||serial#||''' IMMEDIATE;' FROM v$session WHERE USERNAME LIKE '%TEST%' and sql_id='dpgzymcxvwmba';

--SQL script to kill all the sessions which run with user TEST and which are INACTIVE

SELECT 'ALTER SYSTEM KILL SESSION '''||sid||','||serial#||''' IMMEDIATE;' FROM v$session WHERE USERNAME LIKE '%TEST%' AND STATUS='INACTIVE'

---SQL script to kill all the sessions which run with user TEST and which are INACTIVE and run for more than 10000 seconds. You can modify the script according to your scenario

SELECT 'ALTER SYSTEM KILL SESSION '''||sid||','||serial#||''' IMMEDIATE;' FROM v$session WHERE USERNAME LIKE '%TEST%' AND STATUS='INACTIVE' and LAST_CALL_ET > 10000;

--SQL script to kill all the sessions which run with user TEST and which are INACTIVE and run for more than 10000 seconds and logged on users from last 24 hours

SELECT 'ALTER SYSTEM KILL SESSION '''||sid||','||serial#||''' IMMEDIATE;' FROM v$session WHERE USERNAME LIKE '%TEST%' AND STATUS='INACTIVE' and LAST_CALL_ET > 10000 and LOGON_TIME > sysdate - 1 ;

--SQL script to kill all the sessions which run sql queries

SELECT 'ALTER SYSTEM KILL SESSION '''||sid||','||serial#||''' IMMEDIATE;' FROM v$session WHERE SQL_ID is NOT NULL;

--SQL script to kill all the sessions which has blockings

SELECT 'ALTER SYSTEM KILL SESSION '''||sid||','||serial#||''' IMMEDIATE;' FROM v$session WHERE BLOCKING_SESSION is NOT NULL;

--Script to kill RMAN jobs

SELECT 'ALTER SYSTEM KILL SESSION '''||sid||','||serial#||''' IMMEDIATE;' FROM v$session WHERE CLIENT_INFO LIKE '%rman%;

==========================================================================

######SPID, MAPING SPID WITH SID#######

select se.sid,se.serial#,se.sql_id,se.machine,se.terminal,se.module,se.action,pr.spid from v$session se

inner join v$process pr on (pr.addr = se.paddr) where pr.spid ='&spid';

####Shell script to select data####

#!/bin/bash

export ORACLE_SID=db9zx

export ORACLE_HOME=/u01/app/oracle/product/19.0.0/dbhome_1

while true

do

$ORACLE_HOME/bin/sqlplus -S '/ as sysdba' <<EOF

select * from kish.xtbl;

exit;

EOF

done

==========Table Lock and Blocking====================================

lock table xtbl in exclusive mode;

select sid as blocked,serial#,machine,program,sql_id,command,blocking_session as blocker from v$session where blocking_session is not null;

Note: Command value 6 means update, 2 means insert, 3 means select,

==========================================================================

Database Version Check:

select * from v$version;

Select * from product_component_version;

or

[oracle@hostname]locate globalvariables.xml

############UNDO TABLESPACE CONCEPT##############

kIsH@Xhydra<>select max(MAXQUERYLEN) from dba_hist_undostat;

                                                      MAX(MAXQUERYLEN)

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

                                                                  3408

kIsH@Xhydra<>select max(UNDOBLKS / ((end_time - begin_time) * 60 * 60 * 24)) undo_blk_per_sec from dba_hist_undostat;

                                                      UNDO_BLK_PER_SEC

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

                                                                 8.965

Telecom sector - There are different types of customers.

example:

Compare the situation of telecom to undo configuration

1) premium customer (300 mbps speed) - 10 hours query (consume 10gb undo)

2) non premium customer (100 mbps speed) - 5 minutes query (consume 5 mb undo)

We cannot say to premium customer that 100 mbps speed will be provided. Premium customers disagree with that since they pay more.

Similarly, we cannot configure less undo for 10 hours query. 10 hours query needs more undo since it process more data.

That is why we use this formula "max(UNDOBLKS / ((end_time - begin_time) * 60 * 60 * 24))" to calculate undo blocks per second generation

 kIsH@Xhydra<>--optimal undo retention

kIsH@Xhydra<>--optimal undo tablespace size

kIsH@Xhydra<>

kIsH@Xhydra<>show parameter undo_retention

NAME                                 TYPE        VALUE

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

undo_retention                       integer     900

kIsH@Xhydra<>select sum(bytes) from dba_data_files where file_name like '%undo%' or file_name like '%UNDO%';

                                                            SUM(BYTES)

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

                                                            6103957504

kIsH@Xhydra<>show parameter db_block_size

NAME                                 TYPE        VALUE

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

db_block_size                        integer     8192

kIsH@Xhydra<>select max(UNDOBLKS / ((end_time - begin_time) * 60 * 60 * 24)) undo_blk_per_sec from dba_hist_undostat;

                                                      UNDO_BLK_PER_SEC

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

                                                                 8.965

kIsH@Xhydra<>--optimal undo retention = undo tablespace size / (db_block_size * max(undo_block_per_sec)

kIsH@Xhydra<>--optimal undo tablespace = optimal undo retention * db_block_size * max(undo_block_per_sec)

kIsH@Xhydra<>select 6103957504 / ( 8192 * 8.965 )  optimal_retention_undo from dual;

                                                OPTIMAL_RETENTION_UNDO

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

                              83113.4411600669269380925822643614054657

kIsH@Xhydra<>alter system set undo_retention=3408;

System altered.

kIsH@Xhydra<>alter system set undo_retention=900;

System altered.

kIsH@Xhydra<>select 83113 * 8192 * 8.965 optimal_undo_size from dual;

                                                     OPTIMAL_UNDO_SIZE

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

                                                         6103925104.64

kIsH@Xhydra<>select round(6103925104.64/1048576) MB from dual;

                                                                    MB

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

                                                                  5821

kIsH@Xhydra<>select sum(bytes)/1048576 MB from dba_data_files where file_name like '%undo%' or file_name like '%UNDO%';

                                                                    MB

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

                                                             5821.1875

----undostat

set lines 200 pages 1000

col SQL_TEXT for a20

col NOSPACEERRCNT for 999

col UNSC for 999

col ORA1555 for 999

select to_char(us.BEGIN_TIME,'DD-MM-YY HH24:MI') BT,

       to_char(us.END_TIME,'DD-MM-YY HH24:MI') ET,

           us.MAXQUERYLEN as MQL,

           us.MAXQUERYID,

           st.SQL_TEXT,

           us.TUNED_UNDORETENTION as TR,

           us.ACTIVEBLKS as ABLK,     --Retention not valid but space is valid

           us.UNEXPIREDBLKS as UXBLK, --Commited txn but retention not passed and need for Read consistency

           us.EXPIREDBLKS as XBLK, --Txn Commited and no longer required for RC and RB

           us.NOSPACEERRCNT as ORA30036,

           us.SSOLDERRCNT as ORA1555,

           us.UNXPSTEALCNT as UNSC

from v$undostat us

inner join v$sqltext st on (us.MAXQUERYID = st.sql_id)

order by us.MAXQUERYLEN desc;

=======================================================================

--temp usage

Scenario:

temp size - 2gb

TIME 1: A protracted query is launched by Session 1.

TIME 2: At this moment, Session 1 has used up 1.8 GB of the available space on TEMP and Session 2 has begun a query.

TIME 3: Because the tablespace has run out of free space, Sessions 1 and 2 get an ORA-1652.

Both sessions are terminated, and all temporary storage used by them is released (the segments used are marked FREE for reuse)

TIME 4: SMON decommissions the temporary segments utilised by Sessions 1 and 2. (deallocates the storage)

V$SORTSEG USAGE, V$TEMSEG USAGE, and V$SORT SEGMENT.

--Sort usage by amount sort and sqlid

set lines 200 pages 1000

col USERNAME format a10

col TABLESPACE format a15

col SQL_TEXT format a20

col SID format 999999

col EVENT format a15

col PROGRAM format a15

col serial# format 99999

col used format 99999

select su.username,

            su.sql_id,

            su.tablespace,

            sq.sql_text, 

            se.sid,

            se.program,

            se.serial#,

            se.event,

            (su.blocks)*(tb.block_size/1048576) usedMB

from v$sort_usage su

inner join v$sqlarea sq on su.sql_id=sq.sql_id

inner join v$session se on su.session_addr=se.saddr

inner join dba_tablespaces tb on su.tablespace=tb.tablespace_name

order by su.username;

--process id which consume high sort

set lines 200 pages 1000

col USERNAME format a10

col TABLESPACE format a10

col SQL_TEXT format a20

col SID format 999999

col EVENT format a15

col PROGRAM format a15

col serial# format 99999

col used format 99999

col spid format 999999

col sorts format 999

col SORT_CNT for 999

col USEDMB for 999999

select su.username,  su.sql_id,  su.tablespace,  p.spid,  count(*) sort_cnt,    se.sid,   se.program,  se.serial#,  se.event, sum(su.blocks)*(tb.block_size/1048576) usedMB from v$sort_usage su inner join v$session se on su.session_addr=se.saddr inner join v$process p on p.addr=se.paddr inner join dba_tablespaces tb on su.tablespace=tb.tablespace_name group by su.username,su.sql_id,su.tablespace,p.spid,se.sid,se.program,se.serial#,se.event,tb.block_size/1048576 order by su.username;

select a.* from dba_source a

inner join dba_source1 b on b.name = a.name

order by a.name desc;

select a.* from dba_source a

order by a.name desc;

==============================================

select * from product_component_version;

####### How to check last analysed of tables####

SELECT owner, table_name, last_analyzed FROM dba_tables WHERE last_analyzed IS NOT NULL and owner not in('SYS','SYSTEM','XDB','ORDDATA','APPQOSSYS','GSMADMIN_INTERNAL','DBSNMP','WMSYS','CTXSYS','OUTLN','ORDSYS','MDSYS','LBACSYS','APEX_040200','FLOWS_FILES','DVSYS','OJVMSYS','OLAPSYS','AUDSYS') ORDER BY last_analyzed DESC;

SQL script that retrieves all privileges of a specific user.

Below SQL script that retrieves all privileges of a specific user in an Oracle database, including:

• System privileges

• Object privileges

• Roles granted

• Privileges granted through roles (system & object)

-- Set the user you want to investigate
DEFINE target_user = 'PETER';
-- 1. System privileges directly granted to the user
SELECT 
    'SYSTEM_PRIVILEGE' AS PRIV_TYPE,
    GRANTEE,
    PRIVILEGE,
    NULL AS OWNER,
    NULL AS OBJECT_NAME,
    ADMIN_OPTION AS "CAN_ADMIN"
FROM 
    DBA_SYS_PRIVS
WHERE 
    GRANTEE = UPPER('&target_user')
UNION ALL
-- 2. Object privileges directly granted to the user
SELECT 
    'OBJECT_PRIVILEGE' AS PRIV_TYPE,
    GRANTEE,
    PRIVILEGE,
    OWNER,
    TABLE_NAME AS OBJECT_NAME,
    GRANTABLE AS "CAN_ADMIN"
FROM 
    DBA_TAB_PRIVS
WHERE 
    GRANTEE = UPPER('&target_user')
UNION ALL
-- 3. Roles granted to the user
SELECT 
    'ROLE_GRANTED' AS PRIV_TYPE,
    GRANTEE,
    GRANTED_ROLE AS PRIVILEGE,
    NULL AS OWNER,
    NULL AS OBJECT_NAME,
    ADMIN_OPTION AS "CAN_ADMIN"
FROM 
    DBA_ROLE_PRIVS
WHERE 
    GRANTEE = UPPER('&target_user')
UNION ALL
-- 4. System privileges granted via roles
SELECT 
    'ROLE_SYSTEM_PRIVILEGE' AS PRIV_TYPE,
    RP.GRANTEE,
    RSP.PRIVILEGE,
    NULL AS OWNER,
    NULL AS OBJECT_NAME,
    RSP.ADMIN_OPTION AS "CAN_ADMIN"
FROM 
    DBA_ROLE_PRIVS RP
JOIN 
    ROLE_SYS_PRIVS RSP ON RP.GRANTED_ROLE = RSP.ROLE
WHERE 
    RP.GRANTEE = UPPER('&target_user')
UNION ALL
-- 5. Object privileges granted via roles
SELECT 
    'ROLE_OBJECT_PRIVILEGE' AS PRIV_TYPE,
    RP.GRANTEE,
    RTP.PRIVILEGE,
    RTP.OWNER,
    RTP.TABLE_NAME AS OBJECT_NAME,
    RTP.GRANTABLE AS "CAN_ADMIN"
FROM 
    DBA_ROLE_PRIVS RP
JOIN 
    ROLE_TAB_PRIVS RTP ON RP.GRANTED_ROLE = RTP.ROLE
WHERE 
    RP.GRANTEE = UPPER('&target_user')
ORDER BY 
    PRIV_TYPE, PRIVILEGE;

How to find the priveleges under the role.

Below statement find the roles created by DBA.

select * from dba_roles where ORACLE_MAINTAINED !='Y';

ROLE                 PASSWORD AUTHENTICAT COM O

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

READ_ONLY            NO       NONE        NO  N

Below SQL query provide the details of the privelege under role "READ_ONLY"

SQL> SELECT * FROM ROLE_TAB_PRIVS WHERE ROLE = 'READ_ONLY';

ROLE                 OWNER        TABLE_NAME                     COLUMN_NAME          PRIVILEGE     GRA COM

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

READ_ONLY            METALS       MATERIAL_RECORDS                                    SELECT        NO  NO

READ_ONLY            METALS       MANUFACTURER_RECORDS                                SELECT        NO  NO

User Management : How to create user "ASHWANI" who has full access to all objects in a specific schema, "METALS", in your Oracle database.

 How to create user "ASHWANI" who has full access to all objects in a specific schema, "METALS", in your Oracle database.

-- 1. Create the user

CREATE USER ashwani IDENTIFIED BY ashwani
    DEFAULT TABLESPACE users
    TEMPORARY TABLESPACE temp
    QUOTA UNLIMITED ON users;

-- 2. Allow the user to connect

GRANT CREATE SESSION TO ashwani;
-- 3. Grant privileges on all existing objects in METALS schema
BEGIN
    FOR r IN (SELECT owner, object_name, object_type
              FROM all_objects
              WHERE owner = 'METALS'
                AND object_type IN ('TABLE','VIEW','SEQUENCE','PROCEDURE','FUNCTION','PACKAGE')) LOOP
        EXECUTE IMMEDIATE 'GRANT ALL ON ' || r.owner || '.' || r.object_name || ' TO ashwani';
    END LOOP;
END;
/
-- 4. Allow access to future objects in METALS schema

GRANT SELECT ANY TABLE TO ashwani;   -- optional for future read access

-- Note: To auto-grant all privileges for new objects, use schema triggers.

-- 5. (Optional) If you want ashwani to create objects in METALS schema

-- GRANT CREATE ANY TABLE TO ashwani;
-- GRANT CREATE ANY VIEW TO ashwani;

How to copy files from multiple servers to staging server and merge them into one consolidated file using shell script (Pull Method)

Below script can be used to scp all the csv files to staging server and merge them into single csv file.

 servers=("srv1" "srv2")

for srv in "${servers[@]}"
do
  scp oracle@"$srv":/tmp/OLAM_CP/CP_report/file.csv /tmp/OLAM_CP/CP_report/file_"$srv".csv
done

# Copy header from the first file
head -n 1 /tmp/OLAM_CP/CP_report/file_srv1.csv > /tmp/OLAM_CP/CP_report/final_report.csv

# Append data (skip header) from all files
for file in /tmp/OLAM_CP/CP_report/file_*.csv
do
  tail -n +2 "$file" >> /tmp/OLAM_CP/CP_report/final_report.csv
done

Proxy User and Connect Through

 Since Oracle 9i Release 2 it has been possible to create proxy users, allowing us to access a schema via a different username/password combination. 

This is done by using the GRANT CONNECT THROUGH clause on the destination user.

Why we need Proxy?

There are a two main reasons for using proxy users.

Some DBA tasks, like creating private database links or setting up jobs using the DBMS_JOB package, require the administrator to log in as a specific user. 

This can present a problem if the administrator doesn't know the password.

You have multiple developers working in a shared schema. Letting multiple people share the same credentials represents a security risk. Instead you create a separate proxy user for each individual, allowing them to connect to the schema owner with their own credentials. 

If a user leaves a project, you simply lock or drop their user, and they no longer have access to the shared schema.

We have Schema user "METALS"

Lets create proxy user:

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED

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

         2 PDB$SEED                       READ ONLY  NO

         3 MYPDB1                         READ WRITE NO

         4 PDB3                           READ WRITE NO

SQL> alter session set container=pdb3;

Session altered.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED

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

         4 PDB3                           READ WRITE NO

SQL> create user metalprxy identified by metalprxy;

User created.

SQL> grant create session to metalprxy;

Grant succeeded.

SQL> alter user metals grant connect through metalprxy;

User altered.

E:\my_scripts>sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Thu May 15 05:50:52 2025

Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.3.0.0.0

SQL> conn metalprxy[metals]/metalprxy@//localhost:1521/pdb3

Connected.

SQL> show user

USER is "METALS"

The proxy authentication can be revoked using the following command.

alter user metals revoke connect through metalprxy;

How to merge two or multiple csv files into one consolidated file using powershell (Automation)

1. Below Powershell script can be used to merge the multiple files into one consolidated file.

# Define file paths

$file1 = "C:\Users\admin\Desktop\automation\file1.csv"

$file2 = "C:\Users\admin\Desktop\automation\file2.csv"

$outputFile = "C:\Users\admin\Desktop\automation\consolidated.csv"

# Get header from file1 and write to output file

Get-Content $file1 | Select-Object -First 1 | Out-File -FilePath $outputFile -Encoding utf8

# Append data (excluding header) from file1

Get-Content $file1 | Select-Object -Skip 1 | Out-File -FilePath $outputFile -Append -Encoding utf8

# Append data (excluding header) from file2

Get-Content $file2 | Select-Object -Skip 1 | Out-File -FilePath $outputFile -Append -Encoding utf8

Write-Output "Merge completed. Output file: $outputFile"

 

This can be executed as below:

PS C:\Users\admin> C:\Users\admin\Desktop\automation\merge-script.ps1

C:\Users\admin\Desktop\automation\merge-script.ps1 : File C:\Users\admin\Desktop\automation\merge-script.ps1 cannot be

loaded because running scripts is disabled on this system. For more information, see about_Execution_Policies at

https:/go.microsoft.com/fwlink/?LinkID=135170.

At line:1 char:1

+ C:\Users\admin\Desktop\automation\merge-script.ps1

+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

    + CategoryInfo          : SecurityError: (:) [], PSSecurityException

    + FullyQualifiedErrorId : UnauthorizedAccess

Note: If you see above error use below command to fix the same.

PS C:\Users\admin> Set-ExecutionPolicy -Scope CurrentUser -ExecutionPolicy RemoteSigned

Execution Policy Change

The execution policy helps protect you from scripts that you do not trust. Changing the execution policy might expose

you to the security risks described in the about_Execution_Policies help topic at

https:/go.microsoft.com/fwlink/?LinkID=135170. Do you want to change the execution policy?

[Y] Yes  [A] Yes to All  [N] No  [L] No to All  [S] Suspend  [?] Help (default is "N"): y

Then try again it will workfine.

PS C:\Users\admin> C:\Users\admin\Desktop\automation\merge-script.ps1

Merge completed. Output file: C:\Data\consolidated.csv

2. Below script can be used if you have multiple files:

# Define source folder and output file

$sourceFolder = "C:\Users\admin\Desktop\automation"

$outputFile = "C:\Users\admin\Desktop\automation\consolidated_rpt.csv"

# Get list of CSV files in the folder

$csvFiles = Get-ChildItem -Path $sourceFolder -Filter "*.csv"

# Check if there are any files to merge

if ($csvFiles.Count -eq 0) {

    Write-Output "No CSV files found in $sourceFolder"

    exit

}

# Get header from the first file and write to output file

Get-Content $csvFiles[0].FullName | Select-Object -First 1 | Out-File -FilePath $outputFile -Encoding utf8

# Loop through each CSV file

foreach ($file in $csvFiles) {

    # Skip header and append data to output file

    Get-Content $file.FullName | Select-Object -Skip 1 | Out-File -FilePath $outputFile -Append -Encoding utf8

}

Write-Output "Merge completed. Output file: $outputFile"

 

GV$PX_PROCESS

This contains generic process-related information about parallel processes, including status, session ID, process ID, and other information. SELECT pxp.inst_id INT, pxp.server_name, pxp.status, pid, spid, pxp.sid, pxp.serial#, username, service_name svcname FROM gv$px_process pxp, gv$session gvs WHERE gvs.inst_id = pxp.inst_id AND gvs.sid = pxp.sid AND gvs.serial# = pxp.serial# AND username NOT IN ('SYS', 'SYSTEM');

SELECT pxp.inst_id INT, pxp.server_name, pxp.status, pid, spid, pxp.sid, pxp.serial#, username, service_name svcname FROM gv$px_process pxp, gv$session gvs WHERE gvs.inst_id = pxp.inst_id AND gvs.sid = pxp.sid AND gvs.serial# = pxp.serial# AND username NOT IN ('SYS', 'SYSTEM');

The output from the query illustrates that there are two parallel jobs currently running with the QC on instance 8 and instance 5. The output has 24 parallel execution servers on instance 5 and 4 parallel execution servers on instance 8. The parallel servers are confined to single instance because in this specific example, the parameter PARALLEL_FORCE_LOCAL has been enabled. 

INT SERV STATUS PID SPID SID SERIAL# USERNAME SVCNAME 

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

8 P000 IN USE 49 8630 59 13633 MVALLATH SSSVC1 8 P002 IN USE 51 8632160 7979 MVALLATH SSSVC1 8 P003 IN USE 54 8634 315 3890 MVALLATH SSSVC1 8 P001 IN USE 56 30395 413 324 MVALLATH SSSVC1 5 P005 IN USE 64 26015 17 842 DWH SSSVC1 5 P006 IN USE 65 26017 53 4300 DWH SSSVC1 5 P007 IN USE 66 26019 108 711 DWH SSSVC1 5 P012 IN USE 34 24171 111 1139 DWH SSSVC1 5 P008 IN USE 67 26021 163 8261 DWH SSSVC1 5 P009 IN USE 68 26023 209 3222 DWH SSSVC1 5 P014 IN USE 52 24175 219 1002 DWH SSSVC1 5 P010 IN USE 69 26025 257 1442 DWH SSSVC1 5 P015 IN USE 53 24177 263 1118 DWH SSSVC1 5 P000 IN USE 54 26005 306 1045 DWH SSSVC1 5 P011 IN USE 70 26027 319 1063 DWH SSSVC1 5 P001 IN USE 55 26007 350 820 DWH SSSVC1 5 P018 IN USE 71 24183 356 3612 DWH SSSVC1 5 P019 IN USE 72 24185 400 2714 DWH SSSVC1 5 P020 IN USE 73 24187 454 3617 DWH SSSVC1 5 P021 IN USE 74 24189 502 1276 DWH SSSVC1 5 P016 IN USE 58 24179 504 1404 DWH SSSVC1  272 5 P022 IN USE 75 24191 549 739 DWH SSSVC1 5 P017 IN USE 59 24181 562 22027 DWH SSSVC1 5 P023 IN USE 76 24193 592 7108 DWH SSSVC1 5 P013 IN USE 44 24173 605 210 DWH SSSVC1 5 P002 IN USE 61 26009 648 4453 DWH SSSVC1 5 P003 IN USE 62 26011 701 358 DWH SSSVC1 5 P004 IN USE 63 26013 738 7416 DWH SSSVC1

Playbook to merge two csv residng on different server

 ---

- name: Consolidate two CSV files from different servers without Python

  hosts: localhost

  gather_facts: no

  vars:

    server1: "srv1"

    server2: "srv2"

    file1: "/tmp/OLAM_CP/CP_report/file.csv"

    file2: "/tmp/OLAM_CP/CP_report/file.csv"

    local_tmp_dir: "/tmp/csv_consolidate"

    consolidated_file: "/tmp/OLAM_CP/CP_report/final_data.csv"

  tasks:

    - name: Ensure local temp directory exists

      ansible.builtin.file:

        path: "{{ local_tmp_dir }}"

        state: directory

        mode: '0755'

    - name: Fetch CSV from Server 1

      ansible.builtin.shell: |

        scp {{ server1 }}:{{ file1 }} {{ local_tmp_dir }}/file1.csv

      delegate_to: localhost

    - name: Fetch CSV from Server 2

      ansible.builtin.shell: |

        scp {{ server2 }}:{{ file2 }} {{ local_tmp_dir }}/file2.csv

      delegate_to: localhost

    - name: Consolidate the two CSV files (append using shell)

      ansible.builtin.shell: |

        head -n 1 {{ local_tmp_dir }}/file1.csv > {{ consolidated_file }}

        tail -n +2 {{ local_tmp_dir }}/file1.csv >> {{ consolidated_file }}

        tail -n +2 {{ local_tmp_dir }}/file2.csv >> {{ consolidated_file }}

      delegate_to: localhost

    - name: Clean up temp directory

      ansible.builtin.file:

        path: "{{ local_tmp_dir }}"

        state: absent

How to create a user in Oracle who has access to all existing objects of a particular schema

 Step 1: Create the User

New user: ashu
Schema: Metals

create user ashu identified by ashu
default tablespace metals
temporary tablespace temp
quota unlimited on metals;

Grant create session to ashu;

Step 2 : Grant Access to All Existing Objects in a Particular Schema called METALS to new user ASHU.

BEGIN
  FOR t IN (SELECT table_name FROM all_tables WHERE owner = 'METALS') LOOP
    EXECUTE IMMEDIATE 'GRANT SELECT ON METALS.' || t.table_name || ' TO ASHU';
  END LOOP;
END;
/

Step 3: Grant EXECUTE on all procedures and packages in schema METALS

BEGIN
  FOR p IN (SELECT object_name FROM all_objects WHERE owner = 'METALS' AND object_type IN ('PROCEDURE', 'PACKAGE')) LOOP
    EXECUTE IMMEDIATE 'GRANT EXECUTE ON METALS.' || p.object_name || ' TO ASHU';
  END LOOP;
END;
/

Step 4: Grant SELECT on all views in schema METALS

  BEGIN
  FOR v IN (SELECT view_name FROM all_views WHERE owner = 'METALS') LOOP
    EXECUTE IMMEDIATE 'GRANT SELECT ON METALS.' || v.view_name || ' TO ASHU';
  END LOOP;
END;
/

or use below command to provide the access on Valid views only.

BEGIN
  FOR v IN (SELECT object_name 
            FROM all_objects 
            WHERE owner = 'METALS' 
              AND object_type = 'VIEW' 
              AND status = 'VALID') LOOP
    EXECUTE IMMEDIATE 'GRANT SELECT ON METALS.' || v.object_name || ' TO ASHU';
  END LOOP;
END;
/
Verify using below queries:

col GRANTEE form a12
col OWNER form a12
col TABLE_NAME form a30
col GRANTOR form a13
col PRIVILEGE form a13
SELECT * 
FROM DBA_TAB_PRIVS 
WHERE GRANTEE = 'ASHU';

SELECT * 
FROM DBA_ROLE_PRIVS 
WHERE GRANTEE = 'ASHU';

SELECT * 
FROM DBA_SYS_PRIVS 

WHERE GRANTEE = 'ASHU';

How to Automate Oracle AHF Upgrade Using Ansible

  In this guide, we’ll walk through creating an Ansible playbook to automate the upgrade process for Oracle Autonomous Health Framework (AHF) across multiple servers.

Step 1: Create the Ansible Playbook on the Master Server

First, create your playbook file on the master (control) server under the following directory:

[root@srv1]# ls -lrth /home/oracle/mypdb/ahfupgrd.yml

 Step 2: Prepare Target Servers

On each target server where AHF needs to be upgraded, create a directory to store the installation files:

[[oracle@srv2]$ mkdir /tmp/ahf_upgrade

 Step 3: Validate and Execute the Playbook

Before executing the playbook, it's a good practice to run a syntax check:

ansible-playbook --syntax-check ahfupgrd.yml

If everything looks good, run the playbook as below:

ansible-playbook ahfupgrd.yml

 

Sample Ansible Playbook: ahfupgrd.yml

 ---
- name: Upgrade Oracle AHF
  hosts: all
  become: yes
  vars:
    ahf_zip: "/tmp/AHF-LINUX_v25.4.0.zip"
    ahf_extract_dir: "/tmp/ahf_upgrade"
    oracle_user: "oracle"
    install_user_home: "/home/oracle"
    ahf_install_command: "/tmp/ahf_upgrade/ahf_setup"
 
  tasks:
    - name: Ensure AHF zip is present
      copy:
        src: "{{ ahf_zip }}"
        dest: "/tmp/AHF-LINUX_v25.4.0.zip"
        owner: "{{ oracle_user }}"
        mode: '0644'
 
    - name: Create extraction directory
      file:
        path: "{{ ahf_extract_dir }}"
        state: directory
        owner: "{{ oracle_user }}"
        mode: '0755'
 
    - name: Extract AHF zip
      unarchive:
        src: "/tmp/AHF-LINUX_v25.4.0.zip"
        dest: "{{ ahf_extract_dir }}"
        remote_src: yes
        owner: "{{ oracle_user }}"
 
    - name: Run AHF upgrade installer
      become_user: "{{ oracle_user }}"
      command: "{{ ahf_install_command }}"
      args:
        chdir: "{{ ahf_extract_dir }}/ahf"
      register: ahf_upgrade_result
 
    - name: Show upgrade output
      debug:
        var: ahf_upgrade_result.stdout
 
    - name: Verify AHF version
      become_user: "{{ oracle_user }}"
      command: "/home/oracle/oracle.ahf/bin/ahfctl version"
      register: ahf_version_check
 
    - name: Output current AHF version
      debug:
        var: ahf_version_check.stdout