User DDL

set termout off
set linesize 19000
set pages 50000
set feedback off
set trim on
set echo off
set serveroutput on
set long 99999999
set longchunksize 20000 pagesize 0
column Extracted_DDL for a1000
EXEC DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'PRETTY',TRUE);
EXEC DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SQLTERMINATOR',TRUE);
select (case
when ((select count(*)
from dba_users
where username = '$dbuser' and profile <> 'DEFAULT') > 0)
then chr(10)||' -- Note: Profile'||(select dbms_metadata.get_ddl('PROFILE', u.profile) AS ddl from dba_users u where u.username = '$dbuser')
else to_clob (chr(10)||' -- Note: Default profile, no need to create!')
end ) from dual
UNION ALL
select (case
when ((select count(*)
from dba_users
where username = '$dbuser') > 0)
then ' -- Note: Create user statement'||dbms_metadata.get_ddl ('USER', '$dbuser')
else to_clob (chr(10)||' -- Note: User not found!')
end ) Extracted_DDL from dual
UNION ALL
select (case
when ((select count(*)
from dba_ts_quotas
where username = '$dbuser') > 0)
then ' -- Note: TBS quota'||dbms_metadata.get_granted_ddl( 'TABLESPACE_QUOTA', '$dbuser')
else to_clob (chr(10)||' -- Note: No TS Quotas found!')
end ) from dual
UNION ALL
select (case
when ((select count(*)
from dba_role_privs
where grantee = '$dbuser') > 0)
then ' -- Note: Roles'||dbms_metadata.get_granted_ddl ('ROLE_GRANT', '$dbuser')
else to_clob (chr(10)||' -- Note: No granted Roles found!')
end ) from dual
UNION ALL
select (case
when ((select count(*)
from V\$PWFILE_USERS
where username = '$dbuser' and SYSDBA='TRUE') > 0)
then ' -- Note: sysdba'||chr(10)||to_clob (' GRANT SYSDBA TO '||'"'||'$dbuser'||'"'||';')
else to_clob (chr(10)||' -- Note: No sysdba administrative Privilege found!')
end ) from dual
UNION ALL
select (case
when ((select count(*)
from dba_sys_privs
where grantee = '$dbuser') > 0)
then ' -- Note: System Privileges'||dbms_metadata.get_granted_ddl ('SYSTEM_GRANT', '$dbuser')
else to_clob (chr(10)||' -- Note: No System Privileges found!')
end ) from dual
UNION ALL
select (case
when ((select count(*)
from dba_tab_privs
where grantee = '$dbuser') > 0)
then ' -- Note: Object Privileges'||dbms_metadata.get_granted_ddl ('OBJECT_GRANT', '$dbuser')
else to_clob (chr(10)||' -- Note: No Object Privileges found!')
end ) from dual;

Gather Stats Script

 -- -----------------------------------------------------------------------------------
-- File Name    : gather_stats_script.sql
-- Author       : Mohamed Fowjil
-- Description  : Gathers statistics for the database using DBMS_STATS procedures.
-- Last Modified: 2024-07-01
-- -----------------------------------------------------------------------------------
SET SERVEROUTPUT ON
-- Define variables for timing
VARIABLE start_time VARCHAR2(100);
VARIABLE end_time VARCHAR2(100);
-- Get current timestamp as start time
BEGIN
   SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') INTO :start_time FROM DUAL;
   DBMS_OUTPUT.PUT_LINE('Start Time: ' || :start_time);
END;
/
-- Spool start time to separate log file
SPOOL gather_stats_log.txt
SELECT 'Start Time: ' || :start_time FROM DUAL;
SPOOL OFF
-- Set global preferences for DBMS_STATS
EXEC DBMS_STATS.SET_GLOBAL_PREFS('DEGREE', 8);
EXEC DBMS_STATS.SET_GLOBAL_PREFS('CONCURRENT', 'AUTOMATIC');
-- Gather dictionary stats
BEGIN
   DBMS_STATS.GATHER_DICTIONARY_STATS();
   DBMS_OUTPUT.PUT_LINE('Gathered dictionary stats.');
EXCEPTION
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('Error gathering dictionary stats: ' || SQLERRM);
END;
/
-- Gather fixed objects stats
BEGIN
   DBMS_STATS.GATHER_FIXED_OBJECTS_STATS();
   DBMS_OUTPUT.PUT_LINE('Gathered fixed objects stats.');
EXCEPTION
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('Error gathering fixed objects stats: ' || SQLERRM);
END;
/
-- Gather database stats
DECLARE
   l_options VARCHAR2(200);
BEGIN
   l_options := 'GATHER STALE';
   DBMS_STATS.GATHER_DATABASE_STATS(
      options => l_options,
      estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
      method_opt => 'FOR ALL COLUMNS SIZE SKEWONLY',
      granularity => 'ALL',
      cascade => TRUE,
      degree => 8,
      no_invalidate => DBMS_STATS.AUTO_INVALIDATE
   );
   DBMS_OUTPUT.PUT_LINE('Gathered database stats.');
EXCEPTION
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('Error gathering database stats: ' || SQLERRM);
END;
/
-- Get current timestamp as end time
BEGIN
   SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') INTO :end_time FROM DUAL;
   DBMS_OUTPUT.PUT_LINE('End Time: ' || :end_time);
END;
/

--SET PAGESIZE 14
SET FEEDBACK ON
SET VERIFY ON
SET TIMING ON
SET HEADING ON

SRVCTL Commands

 Usage: srvctl {-version | -version -fullversion | -fullversion}
Usage: srvctl add database -db <db_unique_name> -oraclehome <oracle_home> [-domain <domain_name>] [-spfile <spfile>] [-pwfile <password_file_path>] [-role {PRIMARY | PHYSICAL_STANDBY | LOGICAL_STANDBY | SNAPSHOT_STANDBY | FAR_SYNC}] [-startoption <start_options>] [-stopoption <stop_options>] [-dbname <db_name>] [-instance <inst_name>] [-policy {AUTOMATIC | MANUAL | NORESTART | USERONLY}] [-diskgroup "<diskgroup_list>"] [-verbose]
Usage: srvctl config database [-db <db_unique_name> [-all] | -home] [-verbose]
Usage: srvctl start database -db <db_unique_name> [-startoption <start_options>]
Usage: srvctl stop database -db <db_unique_name> [-stopoption <stop_options>] [-drain_timeout <timeout>] [-force]
Usage: srvctl status database {-db <db_unique_name> [-sid] [-home] | -thisversion | -thishome} [-force] [-verbose]
Usage: srvctl enable database -db <db_unique_name>
Usage: srvctl disable database -db <db_unique_name>
Usage: srvctl modify database -db <db_unique_name> [-dbname <db_name>] [-instance <inst_name>] [-oraclehome <oracle_home>] [-user <oracle_user>] [-domain <domain>] [-spfile <spfile>] [-pwfile <password_file_path>] [-role {PRIMARY | PHYSICAL_STANDBY | LOGICAL_STANDBY | SNAPSHOT_STANDBY}] [-startoption <start_options>] [-stopoption <stop_options>] [-policy {AUTOMATIC | MANUAL | NORESTART | USERONLY}] [-diskgroup "<diskgroup_list>"|-nodiskgroup] [-force]
Usage: srvctl remove database -db <db_unique_name> [-force] [-noprompt] [-verbose]
Usage: srvctl update database -db <db_unique_name> -startoption <start_options>
Usage: srvctl getenv database -db <db_unique_name> [-envs "<name>[,...]"]
Usage: srvctl setenv database -db <db_unique_name> {-envs "<name>=<val>[,...]" | -env "<name>=<val>"}
Usage: srvctl unsetenv database -db <db_unique_name> -envs "<name>[,...]"
Usage: srvctl upgrade database -db <db_unique_name> -oraclehome <oracle_home>
Usage: srvctl downgrade database -db <db_unique_name> -oraclehome <oracle_home> -targetversion <to_version>
Usage: srvctl add service -db <db_unique_name> -service "<service_name_list>"
       [-role [PRIMARY][,PHYSICAL_STANDBY][,LOGICAL_STANDBY][,SNAPSHOT_STANDBY]] [-policy {AUTOMATIC | MANUAL}]
       [-notification {TRUE | FALSE}] [-clbgoal {SHORT | LONG}] [-rlbgoal {NONE | SERVICE_TIME | THROUGHPUT}]
       [-failovertype {NONE | SESSION | SELECT | TRANSACTION | AUTO}] [-failovermethod {NONE | BASIC}][-failoverretry <failover_retries>] [-failoverdelay <failover_delay>] [-failover_restore {NONE | LEVEL1}]
       [-edition <edition>] [-pdb <pluggable_database>] [-global <TRUE | FALSE>] [-maxlag <max_lag_time>] [-sql_translation_profile <sql_translation_profile>]
       [-commit_outcome {TRUE | FALSE}] [-retention <retention>] [replay_init_time <replay_initiation_time>] [-tablefamilyid <table_family_id>] [-drain_timeout <timeout>]
       [-stopoption <stop_option>] [-session_state {STATIC | DYNAMIC}] [-force]
Usage: srvctl config service -db <db_unique_name> [-service <service_name>] [-verbose]
Usage: srvctl start service -db <db_unique_name> [-service  "<service_name_list>" | -pdb <pluggable_database>] [-startoption <start_options>] [-global_override] [-role] [-verbose]
Usage: srvctl stop service -db <db_unique_name> [-service "<service_name_list>" | -pdb <pluggable_database>] [-drain_timeout <timeout>] [-stopoption <stop_option>] [-global_override] [-wait <wait_option>] [-force] [-verbose]
Usage: srvctl status service -db <db_unique_name> [-service  "<service_name_list>" | -pdb <pluggable_database>] [-force] [-verbose]
Usage: srvctl enable service -db <db_unique_name> -service  "<service_name_list>" [-global_override]
Usage: srvctl disable service -db <db_unique_name> -service  "<service_name_list>" [-global_override]
Usage: srvctl modify service -db <db_unique_name> -service <service_name>
       [-role [PRIMARY][,PHYSICAL_STANDBY][,LOGICAL_STANDBY][,SNAPSHOT_STANDBY]] [-policy {AUTOMATIC | MANUAL}]
       [-notification {TRUE | FALSE}] [-clbgoal {SHORT | LONG}] [-rlbgoal {NONE | SERVICE_TIME | THROUGHPUT}]
       [-failovertype {NONE | SESSION | SELECT | TRANSACTION | AUTO}] [-failovermethod {NONE | BASIC}] [-failoverretry <integer>] [-failoverdelay <integer>] [-failover_restore {NONE | LEVEL1}]
       [-edition <edition>] [-pdb <pluggable_database>] [-sql_translation_profile <sql_translation_profile>] [-commit_outcome {TRUE | FALSE}]
       [-retention <retention>] [replay_init_time <replay_initiation_time>] [-tablefamilyid <table_family_id>] [-drain_timeout <timeout>] [-stopoption <stop_option>]
       [-session_state {STATIC | DYNAMIC}] [-global_override]
Usage: srvctl remove service -db <db_unique_name> -service <service_name> [-global_override] [-force]
Usage: srvctl add asm [-listener <lsnr_name>] [-spfile <spfile>] [-pwfile <password_file_path>] [-diskstring <asm_diskstring>]
Usage: srvctl config asm [-detail]
Usage: srvctl start asm [-startoption <start_options>]
Usage: srvctl stop asm [-stopoption <stop_options>] [-force]
Usage: srvctl status asm [-all] [-verbose]
Usage: srvctl enable asm
Usage: srvctl disable asm
Usage: srvctl modify asm [-listener <lsnr_name>] [-spfile <spfile>] [-pwfile <password_file_path>] [-diskstring <asm_diskstring>]
Usage: srvctl remove asm [-force]
Usage: srvctl getenv asm [-envs "<name>[,...]"]
Usage: srvctl setenv asm {-envs "<name>=<val>[,...]" | -env "<name>=<value>"}
Usage: srvctl unsetenv asm -envs "<name>[,...]"
Usage: srvctl start diskgroup -diskgroup <dg_name>
Usage: srvctl stop diskgroup -diskgroup <dg_name> [-force]
Usage: srvctl status diskgroup -diskgroup <dg_name> [-all] [-verbose]
Usage: srvctl enable diskgroup -diskgroup <dg_name>
Usage: srvctl disable diskgroup -diskgroup <dg_name>
Usage: srvctl remove diskgroup -diskgroup <dg_name> [-force]
Usage: srvctl add listener [-listener <lsnr_name>] [-skip] [-endpoints "[TCP:]<port>[, ...][/IPC:<key>][/NMP:<pipe_name>][/TCPS:<s_port>][/SDP:<port>][/EXADIRECT:<port>]"] [-oraclehome <path>]
Usage: srvctl config listener [-listener <lsnr_name>]
Usage: srvctl start listener [-listener <lsnr_name>]
Usage: srvctl stop listener [-listener <lsnr_name>] [-force]
Usage: srvctl enable listener [-listener <lsnr_name>]
Usage: srvctl disable listener [-listener <lsnr_name>]
Usage: srvctl modify listener [-listener <lsnr_name>] [-oraclehome <path>] [-endpoints "[TCP:]<port>[, ...][/IPC:<key>][/NMP:<pipe_name>][/TCPS:<s_port>][/SDP:<port>][/EXADIRECT:<port>]"]
Usage: srvctl remove listener [-listener <lsnr_name> | -all] [-force]
Usage: srvctl getenv listener [-listener <lsnr_name>] [-envs "<name>[,...]"]
Usage: srvctl setenv listener [-listener <lsnr_name>] -envs "<name>=<val>[,...]" | -env "<name>=<value>"
Usage: srvctl unsetenv listener [-listener <lsnr_name>] -envs "<name>[,...]"
Usage: srvctl start home -oraclehome <oracle_home> -statefile <state_file>
Usage: srvctl stop home -oraclehome <oracle_home> -statefile <state_file> [-stopoption <stop_options>] [-force]
Usage: srvctl status home -oraclehome <oracle_home> -statefile <state_file>
Usage: srvctl add ons [-emport <em_port>] [-onslocalport <ons_local_port>]  [-onsremoteport <ons_remote_port>] [-remoteservers <host>[:<port>][,<host>[:<port>]...]] [-verbose]
Usage: srvctl remove ons [-force] [-verbose]
Usage: srvctl enable ons [-verbose]
Usage: srvctl disable ons [-verbose]
Usage: srvctl modify ons [-emport <em_port>] [-onslocalport <ons_local_port>]  [-onsremoteport <ons_remote_port>] [-remoteservers <host>[:<port>][,<host>[:<port>]...]] [-verbose]
Usage: srvctl config ons
Usage: srvctl status ons [-verbose]
Usage: srvctl start ons [-verbose]
Usage: srvctl stop ons [-verbose]
Usage: srvctl add oraclehome -name <home_name> -path <path>
Usage: srvctl config oraclehome [-name <home_name>]
Usage: srvctl start oraclehome -name <home_name>
Usage: srvctl stop oraclehome -name <home_name> [-force]
Usage: srvctl enable oraclehome -name <home_name>
Usage: srvctl disable oraclehome -name <home_name>
Usage: srvctl status oraclehome -name <home_name>
Usage: srvctl modify oraclehome -name <home_name> -path <path>
Usage: srvctl remove oraclehome -name <home_name> [-force]

Datafile Resize for Specific Tablespace

 DECLARE
  v_tablespace_name VARCHAR2(30) := 'YOUR_TABLESPACE_NAME'; -- Change to your tablespace name
  v_new_size        VARCHAR2(20) := '1024M'; -- New size for each datafile
BEGIN
  FOR rec IN (SELECT file_name
              FROM dba_data_files
              WHERE tablespace_name = v_tablespace_name) 
  LOOP
    EXECUTE IMMEDIATE 'ALTER DATABASE DATAFILE ''' || rec.file_name || ''' RESIZE ' || v_new_size;
  END LOOP;
END;
/

DGMGRL Commands

 The following commands are available:

@              Execute DGMGRL script file

!              Host operating system command

/              Repeat the last command

--             Comment to be ignored by DGMGRL

add            Adds a member to the broker configuration

connect        Connects to an Oracle database instance

convert        Converts a database from one type to another

create         Creates a broker configuration

disable        Disables a configuration, a member, or fast-start failover

edit           Edits a configuration or a member

enable         Enables a configuration, a member, or fast-start failover

exit           Exits the program

export         Export Data Guard Broker configuration to a file.

failover       Changes a standby database to be the primary database

help           Displays description and syntax for a command

host           Host operating system command

import         Import Data Guard Broker configuration from a file.

migrate        Migrate a pluggable database from one configuration to another.

quit           Exits the program

reinstate      Changes a database marked for reinstatement into a viable standby

rem            Comment to be ignored by DGMGRL

remove         Removes a configuration or a member

set            Set a DGMGRLI CLI property to a specified value

show           Displays information about a configuration or a member

shutdown       Shuts down a currently running Oracle database instance

spool          store input and output of DGMGRL CLI in a file

sql            Executes a SQL statement

start          Starts the fast-start failover observer

startup        Starts an Oracle database instance

stop           Stops the fast-start failover observer

switchover     Switches roles between a primary and standby database

validate       Performs an exhaustive set of validations for a member

Use "help <command>" to see syntax for individual commands

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

DGMGRL> help show

Displays information about a configuration or a member

Syntax:

  SHOW ALL;

  SHOW CONFIGURATION [LAG] [VERBOSE];

  SHOW CONFIGURATION WHEN PRIMARY IS <database name>;

  SHOW { RECOVERY_APPLIANCE | DATABASE | FAR_SYNC } [VERBOSE]

    <object name> [<property name>];

  SHOW { RECOVERY_APPLIANCE | DATABASE | FAR_SYNC | MEMBER }

    <object name> PARAMETER [<initialization parameter name>];

  SHOW INSTANCE [VERBOSE] <instance name> [<property name>]

    [ON { DATABASE | FAR_SYNC } <object name>];

  SHOW FAST_START FAILOVER;

  SHOW OBSERVER;

  SHOW OBSERVERS [FOR <configuration group name>];

  SHOW OBSERVERCONFIGFILE;

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

DGMGRL> help edit

Edits a configuration or a member

Syntax:

  EDIT CONFIGURATION SET PROTECTION MODE [AS]

    { MaxProtection | MaxAvailability | MaxPerformance };

  EDIT CONFIGURATION SET PROPERTY <property name> = <value>;

  EDIT CONFIGURATION RESET PROPERTY <property name>;

  EDIT CONFIGURATION RENAME TO <new configuration name>;

  EDIT { RECOVERY_APPLIANCE | DATABASE | FAR_SYNC }

    <object name> SET PROPERTY <property name> = <value>;

  EDIT { RECOVERY_APPLIANCE | DATABASE | FAR_SYNC }

    <object name> RESET PROPERTY <property name>;

  EDIT { RECOVERY_APPLIANCE | DATABASE | FAR_SYNC }

    <object name> RENAME TO <new object name>;

  EDIT DATABASE <database name> SET STATE = <state>

    [WITH APPLY INSTANCE = <instance name>];

  EDIT INSTANCE <instance name> [ON { DATABASE | FAR_SYNC } <object name>]

    SET AUTO PFILE [ = {<initialization file path> | OFF} ];

  EDIT INSTANCE <instance name> [ON { DATABASE | FAR_SYNC } <object name>]

    SET PROPERTY <property name> = <value>;

  EDIT INSTANCE <instance name> [ON { DATABASE | FAR_SYNC } <object name>]

    RESET PROPERTY <property name>;

  EDIT INSTANCE * ON { DATABASE | FAR_SYNC } <object name>

    SET PROPERTY <property name> = <value>;

  EDIT INSTANCE * ON { DATABASE | FAR_SYNC } <object name>

    RESET PROPERTY <property name>;

  EDIT { RECOVERY_APPLIANCE | DATABASE | FAR_SYNC | MEMBER }

    <object name> SET PARAMETER <parameter name> = <value>

                                [ <initialization parameter options> ];

  EDIT { RECOVERY_APPLIANCE | DATABASE | FAR_SYNC | MEMBER }

    <object name> RESET PARAMETER <parameter name>;

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

DGMGRL> help validate

Performs an exhaustive set of validations for a member

Syntax:

  VALIDATE DATABASE [VERBOSE] <database name>;

  VALIDATE DATABASE [VERBOSE] <database name> DATAFILE <datafile number>

    OUTPUT=<file name>;

  VALIDATE DATABASE [VERBOSE] <database name> SPFILE;

  VALIDATE FAR_SYNC [VERBOSE] <far_sync name>

    [WHEN PRIMARY IS <database name>];

  VALIDATE NETWORK CONFIGURATION FOR { ALL | <member name> };

  VALIDATE STATIC CONNECT IDENTIFIER FOR { ALL | <database name> };

  VALIDATE DGConnectIdentifier <connect identfier>;

  VALIDATE FAST_START FAILOVER;

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

DGMGRL> help switchover

Switches roles between a primary and standby database

Syntax:

  SWITCHOVER TO <standby database name> [WAIT [<timeout in seconds>]];

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

DGMGRL> help export

Export Data Guard Broker configuration to a file.

Syntax:

  EXPORT CONFIGURATION [TO <file_name>];