Oracle DBA Daily Health Check & Pre-Clone Information Script

By -

 As an Oracle DBA, one of our routine responsibilities is to collect critical database information for various operational and strategic activities such as:

  • Pre-clone activities
  • Disaster Recovery (DR) readiness
  • Audits and compliance
  • Migration planning
  • Environment documentation

Running multiple SQL queries manually every time is time-consuming, error-prone, and may result in missed configuration details.

To address this, I use a single consolidated SQL script that safely gathers all essential Oracle database information in one execution.

What Information Does This Script Collect?

Database & Environment

  • SPFILE / PFILE details
  • Archive log mode
  • Controlfile locations

Storage Details

  • Datafiles and tempfiles
  • Tablespace sizes
  • Autoextend and maxsize settings
  • Physical file paths

Redo & Recovery

  • Redo log groups and members
  • Redo log status

Security & Objects

  • Database links (with recreate statements)
  • Directory objects (with recreate statements)
  • SYS, SYSTEM, and application users
  • Password hashes (useful during migration activities)

Oracle E-Business Suite (If Applicable)

  • Application node names (apps.fnd_nodes)

How to Use This Script

1.      Connect to the database as a privileged user (SYS or SYSTEM).

2.      Ensure write permission for the spool directory.

3.      Execute the script using SQL*Plus.

4.      The output will be saved in a log file for reference and documentation.

Consolidated Oracle DBA Information Collection Script

#!/bin/bash

# -----------------------------------------------------------------------------

# Script Name  : oracle_db_inventory_collection.sh

# Purpose      : Collect Oracle database configuration and inventory details

# -----------------------------------------------------------------------------

sqlplus / as sysdba <<EOF

spool info_\$ORACLE_SID.log 

PROMPT ==========================================

PROMPT Backup Control File

PROMPT ==========================================

ALTER DATABASE BACKUP CONTROLFILE TO TRACE AS '\$HOME/phani/ctrl_\$ORACLE_SID.sql';

PROMPT ==========================================

PROMPT Check SPFILE

PROMPT ==========================================

 

SHOW PARAMETER spfile;

 

SET LINES 900

SET PAGES 900

 

COL owner FOR A11

COL db_link FOR A50

COL username FOR A20

COL host FOR A50

 

PROMPT ==========================================

PROMPT Database Links

PROMPT ==========================================

 

SELECT * FROM dba_db_links;

 

PROMPT ==========================================

PROMPT Generate DB Link Recreate Statements

PROMPT ==========================================

 

SELECT 'CREATE ' ||

       DECODE(u.name, 'PUBLIC', 'PUBLIC ') ||

       'DATABASE LINK ' || CHR(10) ||

       DECODE(u.name, 'PUBLIC', NULL, u.name || '.') ||

       l.name || CHR(10) ||

       'CONNECT TO ' || l.userid ||

       ' IDENTIFIED BY ' || l.password ||

       ' USING ''' || l.host || ''';' || CHR(10) AS text

FROM sys.link$ l,

     sys.user$ u

WHERE l.owner# = u.user#;

 

SET LINES 200

 

COL directory_name FOR A40

COL directory_path FOR A90

 

PROMPT ==========================================

PROMPT Oracle Directories

PROMPT ==========================================

 

SELECT * FROM dba_directories;

 

PROMPT ==========================================

PROMPT Generate Directory Recreate Statements

PROMPT ==========================================

 

SELECT 'CREATE OR REPLACE DIRECTORY ' ||

       directory_name ||

       ' AS ''' || directory_path || ''';'

FROM dba_directories;

 

PROMPT ==========================================

PROMPT SYS / SYSTEM / Application Users

PROMPT ==========================================

 

SELECT username,

       password,

       account_status

FROM dba_users

WHERE username IN ('SYS', 'SYSTEM', 'ITCNIMBUS');

 

SELECT name,

       password

FROM sys.user$

WHERE name IN ('SYS', 'SYSTEM', 'ITCNIMBUS');

 

PROMPT ==========================================

PROMPT Archive Log Mode

PROMPT ==========================================

 

ARCHIVE LOG LIST;

 

SET LINES 200

COL file_name FOR A80

 

PROMPT ==========================================

PROMPT Temporary Files

PROMPT ==========================================

 

SELECT file_name,

       tablespace_name,

       bytes/1024/1024 AS size_mb,

       status

FROM dba_temp_files;

 

PROMPT ==========================================

PROMPT Data Files

PROMPT ==========================================

 

SELECT file_name,

       tablespace_name,

       bytes/1024/1024 AS size_mb,

       status

FROM dba_data_files;

 

PROMPT ==========================================

PROMPT Control Files

PROMPT ==========================================

 

SELECT name

FROM v\$controlfile;

 

PROMPT ==========================================

PROMPT Redo Log Files

PROMPT ==========================================

 

SELECT *

FROM v\$logfile;

 

PROMPT ==========================================

PROMPT Redo Log Status

PROMPT ==========================================

 

SELECT *

FROM v\$log;

 

PROMPT ==========================================

PROMPT File Location Paths

PROMPT ==========================================

 

SELECT SUBSTR(name, 1, INSTR(name, '/', -1))

FROM v\$datafile

UNION

SELECT SUBSTR(name, 1, INSTR(name, '/', -1))

FROM v\$tempfile

UNION

SELECT SUBSTR(member, 1, INSTR(member, '/', -1))

FROM v\$logfile

UNION

SELECT SUBSTR(name, 1, INSTR(name, '/', -1))

FROM v\$controlfile;

 

PROMPT ==========================================

PROMPT File Status Check

PROMPT ==========================================

 

SELECT DISTINCT status

FROM v\$datafile

UNION

SELECT DISTINCT status

FROM v\$tempfile

UNION

SELECT DISTINCT status

FROM v\$logfile

UNION

SELECT DISTINCT status

FROM v\$controlfile;

 

PROMPT ==========================================

PROMPT Oracle EBS Nodes

PROMPT ==========================================

 

SELECT node_name

FROM apps.fnd_nodes;

 

PROMPT ==========================================

PROMPT Parameters

PROMPT ==========================================

 

SHOW PARAMETER pfile;

SHOW PARAMETER utl;

 

PROMPT ==========================================

PROMPT Generate User Recreation Statements

PROMPT ==========================================

 

SELECT 'ALTER USER ' || name ||

       ' IDENTIFIED BY VALUES ''' || password || ''';'

FROM sys.user$

WHERE name IN (

    SELECT username

    FROM dba_users

    WHERE username NOT LIKE 'SYS%'

);

 

SET LINES 300

 

COL creation_time FOR A15

COL status FOR A10

COL enabled FOR A15

COL bytes FOR 9999999999

COL blocks FOR 99999999

COL create_bytes FOR 9999999999

COL block_size FOR 9999999999

COL name FOR A70

 

PROMPT ==========================================

PROMPT Detailed Tempfile Information

PROMPT ==========================================

 

SELECT file#,

       creation_time,

       status,

       enabled,

       bytes,

       blocks,

       create_bytes,

       block_size,

       name

FROM v\$tempfile;

 

SET PAGES 50000

SET LINES 32767

 

COL tablespace_name FOR A18

COL creation_time FOR A15

COL file_name FOR A55

COL file# FOR 9999

COL autoextensible FOR A15

 

PROMPT ==========================================

PROMPT Detailed Datafile Information

PROMPT ==========================================

 

SELECT dd.tablespace_name,

       df.file#,

       dd.file_name,

       dd.bytes/1024/1024 AS size_mb,

       dd.autoextensible,

       dd.maxbytes/1024/1024 AS maxsize_mb,

       df.creation_time

FROM dba_data_files dd,

     v\$datafile df

WHERE dd.file_name = df.name

ORDER BY 2;

 

SPOOL OFF;

EXIT;

 

EOF

Comments

Post a Comment

Popular posts from this blog

How to troubleshoot long running concurrent request in R12.2

By -
We frequently encounter issues with concurrent programs running long and concurrent requests not picking up jobs. These problems are often expected from the development and functional team we need to address this issue To solve this, we should take the following steps: Collect below basis level of information to development team 1 Oracle seeded program/Custom program? 2 How much time it used earlier? 3 Is there any recent code change done in concurrent program? 4 Is this program fetching higher data compare to last run? 5 Does this job running any specific time/ It can be run any time? 6 Does this job fetching data using DB link? 7 Does the problem happen on both the test and production instance?   Troubleshooting Steps for Concurrent Requests Check for Locks and Blocking sessions Identify if the request is running but blocked by database locks/Blocking sessions. Use DBA tools or queries to identify and release locks if necessary. Review Parameters and Data ...
Read more

How to find EBS URL in R12.2

By -
There are different way to find the EBS URL Method 1 Login the R12.2 application server Source the Env File [oracle@san ~]$ grep login $CONTEXT_FILE          <login_page oa_var="s_login_page">http://dev1.apps.com:8001/OA_HTML/AppsLogin</login_page> [oracle@san~]$ Method 2: Connect sqlplus apps user SQL> SELECT home_url  FROM icx_parameters; HOME_URL -------------------------------------------------------------------------------- http://<Hostname>:8007/OA_HTML/AppsLogin How to find Weblogic URL and PORT number: Login the R12.2 application server Source the Env File [oracle@sanprod ~]$ grep s_wls_adminport $CONTEXT_FILE          <wls_adminport oa_var="s_wls_adminport" oa_type="PORT" base="7001" step="1" range="-1" label="WLS Admin Server Port"> 7002 </wls_adminport> [oracle@sanprod ~]$ Weblogic Port Number:7002  Weblogic URL:http://<...
Read more

How to Grant Privileges in Oracle EBS R12.2 Using AD_ZD.GRANT_PRIVS

By -
When working with Oracle E-Business Suite (EBS) R12.2, granting privileges to applicatation objects requires special care to avoid invalidating objects in the current edition.  Grants cannot be performed in the run edition when the application is being used. A direct GRANT (DDL) command can potentially cause object invalidation when the application is actively being used. To handle this in a more controlled manner, Oracle introduced the AD_ZD.GRANT_PRIVS utility. Why Use AD_ZD.GRANT_PRIVS? In Oracle EBS R12.2, all grants should be performed using the AD_ZD.GRANT_PRIVS procedure rather than the traditional GRANT statement.  The reason is simple: the GRANT statement directly modifies objects in the database, and this can invalidate them in the current edition. Invalidations during runtime can cause service interruptions, which is unacceptable in a live system. Syntax for Granting and Revoking Privileges The AD_ZD package provides two p...
Read more