Oracle Database Scripts

Monday, April 20, 2026

Enable OpenSSH on Windows 11

Step 1: Install OpenSSH Server
You can do this via PowerShell (run as Administrator):
1. Check if it's already available: Get-WindowsCapability -Online | Where-Object Name -like 'OpenSSH.Server*'
2. If State is NotPresent, install it: Add-WindowsCapability -Online -Name OpenSSH.Server~~~~0.0.1.0
Step 2: Start the Service & Set to Automatic
Installation doesn't automatically start the "listener." Run these commands:
1. Start the service: Start-Service sshd
2. Set it to start whenever Windows boots: Set-Service -Name sshd -StartupType 'Automatic'
Step 3: Verify the Firewall Rule Again
Now that the server is installed, Windows should have automatically generated the rules. Run your original command again: Get-NetFirewallRule -Name *OpenSSH-Server* | select Name, DisplayName, Enabled
It should now show OpenSSH-Server-In-TCP as True.
Step 4: Final Connection Test
Go back to your Oracle Linux (ol8) terminal and try to "poke" the port to see if the timeout is gone:
nc -zv 192.###.##.98 22
If it says "Connection refused" instead of "Timed out": This is actually progress! It means the network is open, but the service might need a restart. Restart-Service sshd
If it still times out: Check if you have a third-party antivirus (like McAfee, Norton, or Quick Heal) installed on Windows. These often have their own "Stealth Mode" or firewalls that block Port 22 regardless of Windows' internal settings.
Once you get a "Succeeded" or "Connected" message from the nc command, your sgpt command for SFTP should work immediately. Do you have any third-party security software running on that Windows 11 box?

Friday, April 17, 2026

SHELL-GPT WITH GEMINI TO MONITOR ORACLE DB

# Install Python and create environment

sudo dnf install python3.12 python3.12-pip -y

python3.12 -m venv ~/sgpt_env

source ~/sgpt_env/bin/activate

# Install Shell-GPT and LiteLLM

pip install "shell-gpt[litellm]"

# Add configuration to .bashrc (Replace PASTE_KEY with your key)

cat << 'EOF' >> ~/.bashrc

# Shell-GPT Config

# Shell-GPT Gemini Fix
export USE_LITELLM=true
export DEFAULT_MODEL="gemini/gemini-2.5-flash"
export GEMINI_API_KEY="PASTE_YOUR_GEMINI_KEY_HERE"
export OPENAI_API_KEY=sk-placeholder
alias sgpt='~/sgpt_env/bin/sgpt'

EOF

source ~/.bashrc

uninstallation:

# Remove the virtual environment folder

rm -rf ~/sgpt_env

# Remove the config lines from .bashrc

sed -i '/# Shell-GPT Config/,/alias sgpt/d' ~/.bashrc

# Optional: Remove Python 3.12 if no longer needed

# sudo dnf remove python3.12 -y

# Refresh terminal

source ~/.bashrc

Saturday, April 11, 2026

11g DR sync process by manual steps

to forcefully delete the archivelogs either from primary or standby
DELETE FORCE ARCHIVELOG ALL COMPLETED BEFORE 'SYSDATE-1';

Process to follow :
1. archive log list on primay
2. archive log list on standby
3. on standby
SQL> SELECT * FROM v$archive_gap;

   THREAD# LOW_SEQUENCE# HIGH_SEQUENCE#
---------- ------------- --------------
         1        910218         910218

4. copy the archivelog from primary to standby using below
on primary:
PROD oracle@primary:/oracle09/path/to/\ $ sftp dr.sample.com
Connected to dr.sample.com.
sftp> pwd
Remote working directory: /home/oracle
sftp> cd /oracle09
sftp> ls
lost+found    sss           sssbkpinc     nohup.out     stand01.ctl
sftp> cd sss
sftp> ls
admin      nohup.out
sftp> cd admin
sftp> ls
arch          audit         bdump         cdump         create        diag          export        listener      lsnr_extproc nohup.out
osw         sftp> cd arch
sftp> ls
...*.arc files will be displayed
sftp> put redo_569515577_1_910218.arc
Uploading redo_569515577_1_910218.arc to /oracle09/path/to/\/redo_569515577_1_910218.arc
redo_569515577_1_910218.arc                                                                                100% 8739KB   4.3MB/s   00:02
sftp>

On standby:
sqlplus / as sysdba
SQL> ALTER DATABASE REGISTER LOGFILE '/oracle09/path/to/\/redo_569515577_1_910218.arc';

Database altered.

SQL> SELECT * FROM v$archive_gap;

no rows selected

SQL> SELECT process, status, sequence#
FROM v$managed_standby
WHERE process LIKE 'MRP%';
2    3
PROCESS   STATUS        SEQUENCE#
--------- ------------ ----------
MRP0      APPLYING_LOG     804225

-------------------------------------------------------------------------------------------------------------------------------------
PROD oracle@primary:/oracle09/path/to/\ $ ls -lrth *910218*
-rw-r-----   1 oracle   dba         8.5M Mar 18 06:12 redo_569515577_1_910218.arc
PROD oracle@primary:/oracle09/path/to/\ $ sftp dr.sample.com
Connected to dr.sample.com.
sftp> pwd
Remote working directory: /home/oracle
sftp> cd /oracle09
sftp> ls
lost+found    sss           sssbkpinc     nohup.out     stand01.ctl
sftp> cd sss
sftp> ls
admin      nohup.out
sftp> cd admin
sftp> ls
arch          audit         bdump         cdump         create        diag          export        listener      lsnr_extproc nohup.out
osw

sftp> cd arch
sftp> ls
...*.arc files will be displayed
sftp> put redo_569515577_1_910218.arc
Uploading redo_569515577_1_910218.arc to /oracle09/path/to/\/redo_569515577_1_910218.arc
redo_569515577_1_910218.arc                                                                                100% 8739KB   4.3MB/s   00:02
sftp>

on standby :
PROD-DR oracle@dr:/oracle09/path/to/\ $ sqlplus / as sysdba

SQL*Plus: Release 11.1.0.7.0 - Production on Tue Mar 24 00:39:49 2026

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

Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production

SQL> select thread#,sequence#,applied,registrar from v$archived_log where applied='YES' and sequence#='804754' ORDER BY SEQUENCE#;

no rows selected

SQL> !ls -lrth *select thread#,sequence#,applied,registrar from v$archived_log where applied='YES' and sequence#='804754' ORDER BY SEQUENCE#;^C

SQL> !ls -lrth *804754*
-rw-r-----   1 oracle   dba         119M Mar 24 00:12 redo_569515577_2_804754.arc

SQL> ALTER DATABASE REGISTER LOGFILE '/oracle09/path/to/\/redo_569515577_2_804754.arc';
ALTER DATABASE REGISTER LOGFILE '/oracle09/path/to/\/redo_569515577_2_804754.arc'
*
ERROR at line 1:
ORA-16089: archive log has already been registered

SQL> select thread#,sequence#,applied,registrar from v$archived_log where sequence#='804754' ORDER BY SEQUENCE#;

   THREAD# SEQUENCE# APPLIED   REGISTR
---------- ---------- --------- -------
         2     804754 NO        RFS

SQL> SELECT * FROM v$archive_gap;

   THREAD# LOW_SEQUENCE# HIGH_SEQUENCE#
---------- ------------- --------------
         1        910218         910218

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
PROD-DR oracle@dr:/oracle09/path/to/\ $ ls -lrth *910218*
-rw-r-----   1 oracle   dba         8.5M Mar 24 00:45 redo_569515577_1_910218.arc
PROD-DR oracle@dr:/oracle09/path/to/\ $ date
Tue Mar 24 00:45:45 EDT 2026
PROD-DR oracle@dr:/oracle09/path/to/\ $ sqlplus / as sysdba

SQL*Plus: Release 11.1.0.7.0 - Production on Tue Mar 24 00:45:54 2026

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

Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production

SQL> ALTER DATABASE REGISTER LOGFILE '/oracle09/path/to/\/redo_569515577_1_910218.arc';

Database altered.

SQL> SELECT * FROM v$archive_gap;

no rows selected

SQL> SELECT process, status, sequence#
FROM v$managed_standby
WHERE process LIKE 'MRP%';
2    3
PROCESS   STATUS        SEQUENCE#
--------- ------------ ----------
MRP0      APPLYING_LOG     804225

SQL> /

PROCESS   STATUS        SEQUENCE#
--------- ------------ ----------
MRP0      APPLYING_LOG     804237

SQL> /

PROCESS   STATUS        SEQUENCE#
--------- ------------ ----------
MRP0      APPLYING_LOG     804238

SQL> /

PROCESS   STATUS        SEQUENCE#
--------- ------------ ----------
MRP0      APPLYING_LOG     804238

SQL> /

PROCESS   STATUS        SEQUENCE#
--------- ------------ ----------
MRP0      WAIT_FOR_LOG     804239

SQL> /

PROCESS   STATUS        SEQUENCE#
--------- ------------ ----------
MRP0      APPLYING_LOG     804239

SQL> /

PROCESS   STATUS        SEQUENCE#
--------- ------------ ----------
MRP0      APPLYING_LOG     804239

SQL> /

PROCESS   STATUS        SEQUENCE#
--------- ------------ ----------
MRP0      APPLYING_LOG     804239

SQL> /

PROCESS   STATUS        SEQUENCE#
--------- ------------ ----------
MRP0      APPLYING_LOG     804239

SQL> /

PROCESS   STATUS        SEQUENCE#
--------- ------------ ----------
MRP0      APPLYING_LOG     804240

SQL> /

PROCESS   STATUS        SEQUENCE#
--------- ------------ ----------
MRP0      APPLYING_LOG     804240

SQL> /

PROCESS   STATUS        SEQUENCE#
--------- ------------ ----------
MRP0      APPLYING_LOG     804244

SQL> /

PROCESS   STATUS        SEQUENCE#
--------- ------------ ----------
MRP0      APPLYING_LOG     804245

SQL> /

PROCESS   STATUS        SEQUENCE#
--------- ------------ ----------
MRP0      APPLYING_LOG     804245

SQL> /

PROCESS   STATUS        SEQUENCE#
--------- ------------ ----------
MRP0      APPLYING_LOG     804245

SQL> /

PROCESS   STATUS        SEQUENCE#
--------- ------------ ----------
MRP0      APPLYING_LOG     804245

SQL> /

PROCESS   STATUS        SEQUENCE#
--------- ------------ ----------
MRP0      APPLYING_LOG     804246

SQL>

Transparent Data Encryption (TDE)

To implement Oracle
Transparent Data Encryption (TDE) in a single workflow, follow these commands in order. This assumes you are using Oracle 19c with a File-based Keystore.
1. Setup Environment (Instance Level)
Run these as a user with SYSDBA or SYSKM privileges.
-- Define where the keys will live
ALTER SYSTEM SET WALLET_ROOT = '/u01/app/oracle/admin/ORCL/wallet' SCOPE=SPFILE;
ALTER SYSTEM SET TDE_CONFIGURATION="KEYSTORE_CONFIGURATION=FILE" SCOPE=BOTH;

-- Restart Database to apply WALLET_ROOT
SHUTDOWN IMMEDIATE;
STARTUP;

2. Create and Activate Keystore
This creates the "container" and the "master key" that encrypts everything else.
-- Create the software keystore (the wallet file)
ADMINISTER KEY MANAGEMENT CREATE KEYSTORE IDENTIFIED BY "YourSecurePassword";

-- Open the keystore to make it active
ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY "YourSecurePassword";

-- Set the Master Encryption Key (The actual lock)
ADMINISTER KEY MANAGEMENT SET KEY IDENTIFIED BY "YourSecurePassword" WITH BACKUP;

3. Encrypt the Data (Application Level)
Once the "lock" is set, you can apply it to your tables or tablespaces.
-- Option A: Encrypt an entire Tablespace (Recommended for Security)
CREATE TABLESPACE secure_data DATAFILE 'sec01.dbf' SIZE 100M ENCRYPTION USING 'AES256' ENCRYPT;

-- Option B: Encrypt a specific column in an existing table
ALTER TABLE employees MODIFY (social_security_number ENCRYPT);

4. Verification
Always verify the status to ensure your data is actually protected.
-- Check if the wallet is OPEN and AVAILABLE
SELECT status, wallet_type, con_id FROM v$encryption_wallet;

-- Check which tablespaces are currently encrypted
SELECT tablespace_name, encrypted FROM dba_tablespaces;
Crucial Security Tip: In a real production environment, you should enable Auto-Login so the database
can decrypt data automatically after a reboot without a DBA manually entering the password.

how to login to db using keystore :
automatic:
2. Auto-Login (Automated)
This creates a local token (the .sso file) that allows the database to open the keystore automatically upon startup.
This is the industry standard for production environments to ensure application uptime after a reboot.
Step 1: Create the Auto-Login Token
    You must have the password-based keystore open first.
    SQL> ADMINISTER KEY MANAGEMENT CREATE AUTO_LOGIN KEYSTORE FROM KEYSTORE IDENTIFIED BY "YourSecurePassword";

Step 2: Verify the Files
In your wallet directory (defined by WALLET_ROOT), you will now see two files:

    ewallet.p12: The password-protected master (NEVER delete this).
    cwallet.sso: The auto-login token.

Step 3: Test the Auto-Login
Restart your database. You no longer need to run the SET KEYSTORE OPEN command. Check the status immediately after startup:
    SQL> SELECT status, wallet_type FROM v$encryption_wallet;
-- Status should show 'OPEN' and Wallet Type should show 'AUTOLOGIN'

manually login :
1. Manual Open (Password-Based)
This is the default and most secure method. After every database restart, the keystore is closed, and the DBA must manually provide the password to "unlock" the data.

    To Open: You must execute this command after every instance startup.
    SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY "YourSecurePassword";
    To Close: If you need to immediately lock all encrypted data without shutting down the database.
    SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE CLOSE IDENTIFIED BY "YourSecurePassword";

Linux Ssh Commands and scripts

-bash-4.4$ read -p "Enter server hostname: " server; ssh -tq ${server} "(echo 'oracle db osusername: '; ps -ef | grep pmon | grep 'ora_pmon_'| grep -v grep | awk '{print \$1}';echo 'oracle pmon process: ';ps -ef | grep pmon | grep 'ora_pmon_' | grep -v grep | awk '{print \$NF}')"
Enter server hostname: hostname
oracle db osusername:
oracle
oracle pmon process:
ora_pmon_hostname

correct way to use awk in ssh :
-bash-4.4$ ssh -tq hostname "(ps -ef | grep pmon | grep 'ora_pmon_'| grep -v grep | awk '{print \$1}')"
oracle

-bash-4.4$ ssh -tq hostname "(ps -ef | grep pmon | grep 'ora_pmon_'| grep -v grep | awk '{print \$NF}')"
ora_pmon_hostname

=TEXTJOIN(" ", TRUE, "alter user system identified by """, D2:D50 & """)

hostname high cpu usage investigation:

hostname high cpu usage investigation:

oracle@PRD:hostname:hostname:(54) /home/oracle
$ ps -eo pcpu,pid,user,args --sort=-pcpu | head -n 11
%CPU     PID USER     COMMAND
62.7 131071 oracle   ora_j001_hostname
11.9 192558 oracle   oraclehostname (LOCAL=NO)
10.0 193835 oracle   oraclehostname (LOCAL=NO)
8.0 245093 root     s1-agent
5.8 184915 oracle   oraclehostname (LOCAL=NO)
5.6 109801 oracle   oraclehostname (LOCAL=NO)
5.1 184808 oracle   oraclehostname (LOCAL=NO)
4.9 133171 oracle   oraclehostname (LOCAL=NO)
4.8 133165 oracle   oraclehostname (LOCAL=NO)
4.7 130391 oracle   oraclehostname (LOCAL=NO)

SELECT s.sid, s.serial#, s.username, s.program, s.module, s.action
FROM v$session s, v$process p
WHERE s.paddr = p.addr
AND p.spid = '131071';

SELECT * FROM v$scheduler_running_jobs WHERE session_id = '2503';

SELECT sql_text
FROM v$sqltext
WHERE sql_id = (SELECT sql_id FROM v$session WHERE sid ='2503')
ORDER BY piece;

Recovery option with only archivelogs in oracle db

1. Recreate the Datafile (No Backup Method)
This uses the control file to "re-initialize" the file.
Warning: This only works if you have all archive logs since the file was first added to the database.

ERROR at line 1:
ORA-01122: database file 27 failed verification check
ORA-01110: data file 27: '/pac/instance/data/cim_dat13.dbf'
ORA-01210: data file header is media corrupt

-- Step 1: Identify and offline the file
ALTER DATABASE DATAFILE 27 OFFLINE;

-- Step 2: Recreate the empty structure
ALTER DATABASE CREATE DATAFILE 27;
-- OR to a new path if the old disk is failing:
ALTER DATABASE CREATE DATAFILE 27 AS '/pac/instance/data/cim_dat13.dbf';

-- Step 3: Apply the transactions from logs
RECOVER DATAFILE 27;

-- Step 4: Bring it back to the database
ALTER DATABASE DATAFILE 27 ONLINE;

2.datafile using rman:

RMAN> RUN {
SET NEWNAME FOR DATAFILE 27 TO '/pac/instance/data/cim_dat13.dbf'; -- Optional: change path
RESTORE DATAFILE 27; -- RMAN creates the file if it doesn't exist
RECOVER DATAFILE 27;
}

3. Block Media Recovery (Repairing specific errors)
If the header is okay but specific data blocks are corrupted (e.g., ORA-01578),
you can repair just those blocks without taking the whole file offline.
RMAN will use the archivelogs to "re-build" the block.
-- Repair a specific block in a file
RMAN> RECOVER DATAFILE 27 BLOCK 1;

-- Repair all blocks listed in V$DATABASE_BLOCK_CORRUPTION
RMAN> RECOVER CORRUPTION LIST;

4. Point-in-Time Recovery (PITR)
While PITR usually requires a backup, if you are performing a RECOVER DATABASE and want to stop at
a specific point because you are missing later logs, you can use these variants:
-- Stop recovery at a specific time
SQL> RECOVER DATABASE UNTIL TIME '2023-10-27:14:00:00';

-- Stop recovery at a specific System Change Number (SCN)
SQL> RECOVER DATABASE UNTIL SCN 123456789;

-- Manually apply logs until you choose to stop
SQL> RECOVER DATABASE UNTIL CANCEL;

5. Tablespace-Level Recovery
If multiple files in a single tablespace are corrupted, you can recover them together.
SQL> ALTER TABLESPACE cim_data_ts OFFLINE;
SQL> RECOVER TABLESPACE cim_data_ts;
SQL> ALTER TABLESPACE cim_data_ts ONLINE;

6. Critical Constraints

    The "SYSTEM" Restriction: You generally cannot recreate the SYSTEM tablespace (Datafile 1)
    this way because the database needs it just to read the logs and control files.
    Gap in Logs: If you are missing even one archivelog in the sequence, the RECOVER command
    will fail with an ORA-00308 (archived log not found).

Thursday, April 9, 2026

Complete Standby Database Setup from Scratch

Environment Details (Example)

Primary Server  : primary_server   IP: 192.168.1.10
Standby Server  : standby_server   IP: 192.168.1.20
DB Name         : ORCL
Primary SID     : ORCL
Standby SID     : ORCLDG
Oracle Home     : /u01/app/oracle/product/19c/dbhome_1
Oracle Base     : /u01/app/oracle

PHASE 1 — PRIMARY SERVER PREPARATION

Step 1 — Check Primary Database Mode

-- Must be in ARCHIVELOG mode
SQL> SELECT LOG_MODE FROM V$DATABASE;

-- If NOARCHIVELOG, enable it
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT;
SQL> ALTER DATABASE ARCHIVELOG;
SQL> ALTER DATABASE OPEN;

-- Verify
SQL> SELECT LOG_MODE FROM V$DATABASE;
-- OUTPUT: ARCHIVELOG

Step 2 — Enable Force Logging on Primary

SQL> ALTER DATABASE FORCE LOGGING;

-- Verify
SQL> SELECT FORCE_LOGGING FROM V$DATABASE;
-- OUTPUT: YES

Step 3 — Enable Flashback (Recommended)

SQL> ALTER DATABASE FLASHBACK ON;

-- Verify
SQL> SELECT FLASHBACK_ON FROM V$DATABASE;

Step 4 — Create Standby Redo Logs on Primary

-- Formula: (primary redo log groups + 1) x threads
-- Example: Primary has 3 groups → create 4 SRL groups

-- Check existing redo log size first
SQL> SELECT GROUP#, BYTES/1024/1024 MB FROM V$LOG;

-- Create Standby Redo Logs (same size as online redo logs)
SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 4
     '/u01/app/oracle/oradata/ORCL/stdby_redo04.log' SIZE 200M;

SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 5
     '/u01/app/oracle/oradata/ORCL/stdby_redo05.log' SIZE 200M;

SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 6
     '/u01/app/oracle/oradata/ORCL/stdby_redo06.log' SIZE 200M;

SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 7
     '/u01/app/oracle/oradata/ORCL/stdby_redo07.log' SIZE 200M;

-- Verify
SQL> SELECT GROUP#, MEMBER FROM V$STANDBY_LOG;

Step 5 — Configure Primary init.ora / spfile Parameters

-- Check current parameter file type
SQL> SHOW PARAMETER spfile;

-- Set Data Guard parameters on PRIMARY
SQL> ALTER SYSTEM SET LOG_ARCHIVE_MODE=TRUE SCOPE=SPFILE;

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_1=
     'LOCATION=/u01/app/oracle/archivelog/ORCL/
      VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
      DB_UNIQUE_NAME=ORCL' SCOPE=BOTH;

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_2=
     'SERVICE=ORCLDG LGWR ASYNC
      VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
      DB_UNIQUE_NAME=ORCLDG' SCOPE=BOTH;

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_1=ENABLE SCOPE=BOTH;
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE SCOPE=BOTH;

SQL> ALTER SYSTEM SET LOG_ARCHIVE_FORMAT='%t_%s_%r.arc' SCOPE=SPFILE;

SQL> ALTER SYSTEM SET DB_UNIQUE_NAME='ORCL' SCOPE=SPFILE;

SQL> ALTER SYSTEM SET LOG_ARCHIVE_CONFIG=
     'DG_CONFIG=(ORCL,ORCLDG)' SCOPE=BOTH;

SQL> ALTER SYSTEM SET FAL_SERVER='ORCLDG' SCOPE=BOTH;
SQL> ALTER SYSTEM SET FAL_CLIENT='ORCL' SCOPE=BOTH;

SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT='AUTO' SCOPE=BOTH;

SQL> ALTER SYSTEM SET DB_FILE_NAME_CONVERT=
     '/oradata/ORCL/','/oradata/ORCLDG/' SCOPE=SPFILE;

SQL> ALTER SYSTEM SET LOG_FILE_NAME_CONVERT=
     '/oradata/ORCL/','/oradata/ORCLDG/' SCOPE=SPFILE;

Step 6 — Configure listener.ora on Primary

# Edit /u01/app/oracle/product/19c/dbhome_1/network/admin/listener.ora

vi $ORACLE_HOME/network/admin/listener.ora

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = primary_server)(PORT = 1521))
    )
  )

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = ORCL)
      (ORACLE_HOME   = /u01/app/oracle/product/19c/dbhome_1)
      (SID_NAME      = ORCL)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = ORCL_DGMGRL)
      (ORACLE_HOME   = /u01/app/oracle/product/19c/dbhome_1)
      (SID_NAME      = ORCL)
    )
  )

Step 7 — Configure tnsnames.ora on Primary

vi $ORACLE_HOME/network/admin/tnsnames.ora

ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = primary_server)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ORCL)
    )
  )

ORCLDG =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = standby_server)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ORCLDG)
    )
  )

# Reload listener
lsnrctl reload
lsnrctl status

Step 8 — Create Password File on Primary

# Check existing password file
ls $ORACLE_HOME/dbs/orapwORCL

# Create if not exists
orapwd file=$ORACLE_HOME/dbs/orapwORCL password=SysPassword123 entries=10

# Copy password file to standby (MUST be identical)
scp $ORACLE_HOME/dbs/orapwORCL \
    oracle@standby_server:$ORACLE_HOME/dbs/orapwORCLDG

PHASE 2 — STANDBY SERVER PREPARATION

Step 9 — Create Directory Structure on Standby

# Login to standby server as oracle user
ssh oracle@standby_server

# Create all required directories
mkdir -p /u01/app/oracle/oradata/ORCLDG
mkdir -p /u01/app/oracle/archivelog/ORCLDG
mkdir -p /u01/app/oracle/fast_recovery_area/ORCLDG
mkdir -p /u01/app/oracle/admin/ORCLDG/adump
mkdir -p /u01/app/oracle/admin/ORCLDG/bdump
mkdir -p /u01/app/oracle/admin/ORCLDG/cdump
mkdir -p /u01/app/oracle/admin/ORCLDG/udump
mkdir -p /u01/app/oracle/admin/ORCLDG/pfile
mkdir -p /backup/standby

# Set correct permissions
chown -R oracle:oinstall /u01/app/oracle/oradata/ORCLDG
chown -R oracle:oinstall /u01/app/oracle/archivelog/ORCLDG
chown -R oracle:oinstall /u01/app/oracle/fast_recovery_area/ORCLDG
chown -R oracle:oinstall /u01/app/oracle/admin/ORCLDG
chown -R oracle:oinstall /backup/standby

chmod -R 755 /u01/app/oracle/oradata/ORCLDG
chmod -R 755 /u01/app/oracle/archivelog/ORCLDG

# Verify directories
ls -lR /u01/app/oracle/oradata/ORCLDG
ls -lR /u01/app/oracle/admin/ORCLDG

Step 10 — Create standby init.ora on Standby

vi /u01/app/oracle/admin/ORCLDG/pfile/init_ORCLDG.ora

# ── Database Identity ──────────────────────────────────
*.db_name='ORCL'                          # Same as primary
*.db_unique_name='ORCLDG'                 # Different from primary
*.db_domain=''

# ── Instance ───────────────────────────────────────────
*.instance_name='ORCLDG'

# ── Memory ─────────────────────────────────────────────
*.sga_target=2G
*.pga_aggregate_target=512M
*.memory_target=0

# ── Paths ──────────────────────────────────────────────
*.db_create_file_dest='/u01/app/oracle/oradata/ORCLDG'
*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area/ORCLDG'
*.db_recovery_file_dest_size=50G

*.audit_file_dest='/u01/app/oracle/admin/ORCLDG/adump'
*.core_dump_dest='/u01/app/oracle/admin/ORCLDG/cdump'
*.background_dump_dest='/u01/app/oracle/admin/ORCLDG/bdump'
*.user_dump_dest='/u01/app/oracle/admin/ORCLDG/udump'

# ── File Name Convert (Primary path → Standby path) ───
*.db_file_name_convert=
    '/u01/app/oracle/oradata/ORCL/',
    '/u01/app/oracle/oradata/ORCLDG/'

*.log_file_name_convert=
    '/u01/app/oracle/oradata/ORCL/',
    '/u01/app/oracle/oradata/ORCLDG/'

# ── Archivelog ─────────────────────────────────────────
*.log_archive_dest_1=
    'LOCATION=/u01/app/oracle/archivelog/ORCLDG/
     VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
     DB_UNIQUE_NAME=ORCLDG'

*.log_archive_dest_2=
    'SERVICE=ORCL LGWR ASYNC
     VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
     DB_UNIQUE_NAME=ORCL'

*.log_archive_dest_state_1=ENABLE
*.log_archive_dest_state_2=ENABLE
*.log_archive_format='%t_%s_%r.arc'
*.log_archive_config='DG_CONFIG=(ORCL,ORCLDG)'

# ── Data Guard ─────────────────────────────────────────
*.fal_server='ORCL'
*.fal_client='ORCLDG'
*.standby_file_management='AUTO'

# ── Redo Transport ─────────────────────────────────────
*.remote_login_passwordfile='EXCLUSIVE'

# ── Misc ───────────────────────────────────────────────
*.compatible='19.0.0'
*.open_cursors=300
*.processes=300
*.undo_tablespace='UNDOTBS1'
*.control_files='/u01/app/oracle/oradata/ORCLDG/control01.ctl',
                '/u01/app/oracle/oradata/ORCLDG/control02.ctl'

Step 11 — Configure listener.ora on Standby

vi $ORACLE_HOME/network/admin/listener.ora

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = standby_server)(PORT = 1521))
    )
  )

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = ORCLDG)
      (ORACLE_HOME   = /u01/app/oracle/product/19c/dbhome_1)
      (SID_NAME      = ORCLDG)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = ORCLDG_DGMGRL)
      (ORACLE_HOME   = /u01/app/oracle/product/19c/dbhome_1)
      (SID_NAME      = ORCLDG)
    )
  )

Step 12 — Configure tnsnames.ora on Standby

vi $ORACLE_HOME/network/admin/tnsnames.ora

ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = primary_server)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ORCL)
    )
  )

ORCLDG =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = standby_server)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ORCLDG)
    )
  )

# Start listener on standby
lsnrctl start
lsnrctl status

Step 13 — Set Oracle Environment on Standby

# Edit .bash_profile on standby
vi ~/.bash_profile

export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=/u01/app/oracle/product/19c/dbhome_1
export ORACLE_SID=ORCLDG
export PATH=$ORACLE_HOME/bin:$PATH
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib
export NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS'

# Apply
source ~/.bash_profile

# Verify
echo $ORACLE_SID

Step 14 — Test Connectivity Between Primary and Standby

# From primary — test connection to standby
tnsping ORCLDG

# From standby — test connection to primary
tnsping ORCL

# Test RMAN connection from standby
rman TARGET sys/password@ORCL
rman TARGET sys/password@ORCLDG

PHASE 3 — BUILD STANDBY USING RMAN DUPLICATE

Step 15 — Start Standby in NOMOUNT

# On standby server
export ORACLE_SID=ORCLDG

sqlplus / as sysdba
SQL> STARTUP NOMOUNT PFILE='/u01/app/oracle/admin/ORCLDG/pfile/init_ORCLDG.ora';

Step 16 — Run RMAN DUPLICATE from Standby Server

# Connect RMAN to both primary (TARGET) and standby (AUXILIARY)
rman TARGET sys/SysPassword123@ORCL \
     AUXILIARY sys/SysPassword123@ORCLDG

RMAN> DUPLICATE TARGET DATABASE FOR STANDBY
      FROM ACTIVE DATABASE
      DORECOVER
      SPFILE
        PARAMETER_VALUE_CONVERT
          '/u01/app/oracle/oradata/ORCL/',
          '/u01/app/oracle/oradata/ORCLDG/'
        SET db_unique_name='ORCLDG'
        SET db_file_name_convert=
            '/u01/app/oracle/oradata/ORCL/',
            '/u01/app/oracle/oradata/ORCLDG/'
        SET log_file_name_convert=
            '/u01/app/oracle/oradata/ORCL/',
            '/u01/app/oracle/oradata/ORCLDG/'
        SET control_files=
            '/u01/app/oracle/oradata/ORCLDG/control01.ctl',
            '/u01/app/oracle/oradata/ORCLDG/control02.ctl'
        SET log_archive_dest_1=
            'LOCATION=/u01/app/oracle/archivelog/ORCLDG/'
        SET fal_server='ORCL'
        SET fal_client='ORCLDG'
        SET standby_file_management='AUTO'
      NOFILENAMECHECK;

RMAN will now automatically copy all datafiles, controlfile, and apply archivelogs. This may take time depending on DB size.

PHASE 4 — POST DUPLICATE STEPS

Step 17 — Create spfile from pfile on Standby

SQL> CREATE SPFILE FROM PFILE=
     '/u01/app/oracle/admin/ORCLDG/pfile/init_ORCLDG.ora';

-- Restart using spfile
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT;

Step 18 — Start MRP Apply Process

-- Start Managed Recovery Process
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE
     DISCONNECT FROM SESSION;

-- OR with real-time apply (preferred)
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE
     USING CURRENT LOGFILE
     DISCONNECT FROM SESSION;

Step 19 — Verify Standby is Working

-- Check database role
SQL> SELECT DB_UNIQUE_NAME, DATABASE_ROLE, OPEN_MODE,
            PROTECTION_MODE FROM V$DATABASE;

-- Check MRP process
SQL> SELECT PROCESS, STATUS, SEQUENCE#,
            BLOCKS FROM V$MANAGED_STANDBY;

-- Check archive log apply status
SQL> SELECT SEQUENCE#, APPLIED, FIRST_TIME,
            NEXT_TIME FROM V$ARCHIVED_LOG
     ORDER BY SEQUENCE# DESC;

-- Check gap between primary and standby
SQL> SELECT * FROM V$ARCHIVE_GAP;

-- Compare sequences
-- On Primary:
SQL> SELECT MAX(SEQUENCE#) FROM V$ARCHIVED_LOG;

-- On Standby:
SQL> SELECT MAX(SEQUENCE#) FROM V$ARCHIVED_LOG
     WHERE APPLIED = 'YES';

Complete Checklist

PRIMARY SIDE
─────────────────────────────────────────────────
[ ] Database in ARCHIVELOG mode
[ ] FORCE LOGGING enabled
[ ] Standby Redo Logs created
[ ] LOG_ARCHIVE_DEST_2 pointing to standby
[ ] LOG_ARCHIVE_CONFIG set
[ ] FAL_SERVER / FAL_CLIENT set
[ ] listener.ora updated with static entry
[ ] tnsnames.ora has both ORCL and ORCLDG entries
[ ] Password file created and copied to standby

STANDBY SIDE
─────────────────────────────────────────────────
[ ] All directories created with correct permissions
[ ] init_ORCLDG.ora created with standby parameters
[ ] db_file_name_convert set correctly
[ ] log_file_name_convert set correctly
[ ] listener.ora configured with static SID entry
[ ] tnsnames.ora has both entries
[ ] ORACLE_SID set to ORCLDG in .bash_profile
[ ] tnsping working both ways
[ ] STARTUP NOMOUNT successful
[ ] DUPLICATE completed without errors
[ ] spfile created from pfile
[ ] MRP process started
[ ] Sequence# in sync with primary
[ ] No gaps in V$ARCHIVE_GAP

Key Points to Remember

Item	Value
`db_name`	Same on primary and standby
`db_unique_name`	Different (ORCL vs ORCLDG)
Password file	Must be identical on both
Standby Redo Logs	Must exist on both servers
`NOFILENAMECHECK`	Use when directory paths are different
MRP	Must be running for real-time sync
After switchover	Always take fresh Level 0 backup

___________________________________***______________________________________

Minimal init.ora for Standby Database

Absolute Minimum Parameters Required

# /u01/app/oracle/admin/ORCLDG/pfile/init_ORCLDG.ora

# ── Must Have ──────────────────────────────────────────

# Same as primary db_name

db_name='ORCL'

# Unique name for standby

db_unique_name='ORCLDG'

# Controlfile location on standby

control_files='/u01/app/oracle/oradata/ORCLDG/control01.ctl',

'/u01/app/oracle/oradata/ORCLDG/control02.ctl'

# Convert primary paths to standby paths

db_file_name_convert='/u01/app/oracle/oradata/ORCL/',

'/u01/app/oracle/oradata/ORCLDG/'

log_file_name_convert='/u01/app/oracle/oradata/ORCL/',

'/u01/app/oracle/oradata/ORCLDG/'

# Required for redo transport authentication

remote_login_passwordfile='EXCLUSIVE'

# Audit directory must exist

audit_file_dest='/u01/app/oracle/admin/ORCLDG/adump'

# Oracle version compatibility

compatible='19.0.0'

RMAN BACKUP RESTORE RECOVERY

Incremental backups:

Backuping up database based on changed blocks

stratergies:- incremental (differential(default) and cumulative )

incremental :- level 0 (full db) , level 1 ( backuping blocks since level 0 )

differential:- backuping db since last level 1 backup.

cumulative:- backuping db since last level 0 backup.

differential:- sun(level 0), mon (level 1) , tue(level 1), wed(level 1), thu(level 1), fri(level 1), sat(level 1)

cumulative:- sun(level 0), mon(sun+level 1), tue(sun+mon+level1), wed(sun+mon+tue+level1),

thu(sun+mon+tue+wed+level1), fri(sun+mon+tue+wed+thu+level1),sat(sun+mon+tue+wed+thu+fri+level1)

Recovery stepsfor both (differential & cumulative will be same rman auto-select backups):-

RMAN> STARTUP MOUNT;

RMAN> RESTORE DATABASE;

RMAN> RECOVER DATABASE;

RMAN> ALTER DATABASE OPEN RESETLOGS;

Side-by-Side Comparison

Factor	Differential	Cumulative
Backup size (daily)	Smaller (only daily changes)	Larger (grows each day)
Backup time	Faster	Slower (more blocks each day)
Storage usage	Less	More
Recovery time	Slower (apply multiple L1s)	Faster (apply only 1 L1)
Backup sets to restore	L0 + all L1s since L0	L0 + latest L1 only
Complexity	Slightly complex recovery	Simple recovery
Best for	Limited storage environments	Fast recovery requirement (low RTO)

Point-in-time-recovery:-

1. Restore to specific time:-

RMAN> STARTUP MOUNT;

RMAN> RUN {
SET UNTIL TIME "TO_DATE('2024-04-09 14:00:00','YYYY-MM-DD HH24:MI:SS')";
RESTORE DATABASE;
RECOVER DATABASE;
}
RMAN> ALTER DATABASE OPEN RESETLOGS;

2. Restore to specific scn:-

RMAN> STARTUP MOUNT;

RMAN> RUN {

SET UNTIL SCN 1234567;

RESTORE DATABASE;

RECOVER DATABASE;

}

RMAN> ALTER DATABASE OPEN RESETLOGS;

3. Restore to specific log sequence:-

RMAN> STARTUP MOUNT;

RMAN> RUN {

SET UNTIL SEQUENCE 500 THREAD 1;

RESTORE DATABASE;

RECOVER DATABASE;

}

RMAN> ALTER DATABASE OPEN RESETLOGS;

_________________________________________________________________

TABLESPACE RECOVERY:-

-- Take tablespace offline

SQL> ALTER TABLESPACE users OFFLINE IMMEDIATE;

-- Restore and recover tablespace

RMAN> RESTORE TABLESPACE users;

RMAN> RECOVER TABLESPACE users;

-- Bring tablespace online

SQL> ALTER TABLESPACE users ONLINE;

_________________________________________________________________

Datafile recovery:-

-- If datafile is missing/corrupted

RMAN> RESTORE DATAFILE 5;

RMAN> RECOVER DATAFILE 5;

-- Or by filename

RMAN> RESTORE DATAFILE '/oradata/users01.dbf';

RMAN> RECOVER DATAFILE '/oradata/users01.dbf';

__________________________________________________________________

Control file recovery:-

-- If controlfile is lost

RMAN> STARTUP NOMOUNT;

RMAN> RESTORE CONTROLFILE FROM AUTOBACKUP;

-- OR from specific location

RMAN> RESTORE CONTROLFILE FROM '/backup/ctrl_backup';

RMAN> ALTER DATABASE MOUNT;

RMAN> RECOVER DATABASE;

RMAN> ALTER DATABASE OPEN RESETLOGS;

__________________________________________________________________

Archivelog only recovery (no restore required)

-- If datafiles are intact but archivelogs needed

RMAN> RECOVER DATABASE; -- applies pending archivelogs

-- Recover using specific archivelog

RMAN> RECOVER DATABASE UNTIL SEQUENCE 450;

__________________________________________________________________

Sunday, September 7, 2025

Recover Standby Database Using Incremental Backup

##############################################

# STEP 1: On STANDBY - Check current SCN

##############################################

sqlplus / as sysdba <<EOF

SELECT CURRENT_SCN FROM V\$DATABASE;

EXIT;

EOF

##############################################

# STEP 2: On PRIMARY - Take incremental backup

# Replace <STANDBY_SCN> with value from above

##############################################

rman target / <<EOF

BACKUP INCREMENTAL FROM SCN <STANDBY_SCN> DATABASE FORMAT '/u01/backup/incr/incr_%U.bkp';

BACKUP CURRENT CONTROLFILE FOR STANDBY FORMAT '/u01/backup/incr/standby_ctl.bkp';

EXIT;

EOF

##############################################

# STEP 3: Copy backup files to STANDBY server

##############################################

scp /u01/backup/incr/* oracle@standby:/u01/backup/incr/

##############################################

# STEP 4: On STANDBY - Catalog the backup pieces

##############################################

sqlplus / as sysdba <<EOF

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

shut immediate;

startup nomount;

exit;

EOF

rman target / <<EOF

CATALOG START WITH '/u01/backup/incr/';

EXIT;

EOF

##############################################

# STEP 5: On STANDBY - Restore standby controlfile

##############################################

rman target / <<EOF

RESTORE STANDBY CONTROLFILE FROM '/u01/backup/incr/standby_ctl.bkp';

EXIT;

EOF

##############################################

# STEP 6: Mount the STANDBY database

##############################################

sqlplus / as sysdba <<EOF

ALTER DATABASE MOUNT;

EXIT;

EOF

##############################################

# STEP 7: Apply incremental backup to STANDBY

##############################################

rman target / <<EOF

RECOVER DATABASE NOREDO;

EXIT;

EOF

##############################################

# STEP 8: Start managed recovery on STANDBY

##############################################

sqlplus / as sysdba <<EOF

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

EXIT;

EOF

##############################################

# STEP 9: Verify STANDBY synchronization

##############################################

sqlplus / as sysdba <<EOF

SELECT NAME, OPEN_MODE, DATABASE_ROLE, CURRENT_SCN FROM V\$DATABASE;

SELECT PROCESS, STATUS, THREAD#, SEQUENCE# FROM V\$MANAGED_STANDBY;

EXIT;

EOF

_________________________________________________________________________

Full Flow Using RMAN DUPLICATE (Easier Method)

Instead of manual steps, use DUPLICATE — RMAN handles everything automatically.

-- Connect to both primary and standby
RMAN> CONNECT TARGET sys/password@primary
RMAN> CONNECT AUXILIARY sys/password@standby

-- Duplicate to standby (using active database — no backup needed)

RMAN> DUPLICATE TARGET DATABASE
FOR STANDBY
FROM ACTIVE DATABASE
DORECOVER
SPFILE
SET db_unique_name='STANDBY'
SET log_archive_dest_1='LOCATION=/arch/standby/'
SET fal_server='PRIMARY'
SET standby_file_management='AUTO'
NOFILENAMECHECK;

OR using existing backups with duplicat command:-

RMAN> DUPLICATE TARGET DATABASE

FOR STANDBY

BACKUP LOCATION '/backup/standby/'

DORECOVER

NOFILENAMECHECK;

Thursday, August 7, 2025

DATE AND TIME

Oracle DB:

Function -------------------	Description ------------------------------------------------	Time Zone Used -------------------------------
`SYSDATE`	Server date/time (from OS)	❌ Not affected by session TZ
`SYSTIMESTAMP`	Server timestamp with time zone	❌ Not affected by session TZ
`CURRENT_DATE`	Session date/time (no TZ shown)	✅ Affected by session TZ
`CURRENT_TIMESTAMP`	Session timestamp with time zone	✅ Affected by session TZ
`LOCALTIMESTAMP`	Session timestamp (no TZ info, but adjusted)	✅ Affected by session TZ

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

Linux:

export TZ=<Time_Zone_Name>

[root@localhost ~]# export TZ=Asia/Kolkata

[root@localhost ~]# date

Thu Aug  7 05:32:26 PM IST 2025

Region -------------	Time Zone Name -----------------------	UTC Offset -------------------------------	Description / Area -------------------------
Africa	`Africa/Johannesburg`	UTC+02:00	South Africa
	`Africa/Cairo`	UTC+02:00	Egypt
	`Africa/Nairobi`	UTC+03:00	Kenya
	`Africa/Lagos`	UTC+01:00	Nigeria
America	`America/Toronto`	UTC-05:00 / -04:00 DST	Canada – Eastern Time
	`America/Vancouver`	UTC-08:00 / -07:00 DST	Canada – Pacific Time
	`America/Edmonton`	UTC-07:00 / -06:00 DST	Canada – Mountain Time
	`America/Winnipeg`	UTC-06:00 / -05:00 DST	Canada – Central Time
	`America/St_Johns`	UTC-03:30 / -02:30 DST	Canada – Newfoundland
	`America/New_York`	UTC-05:00 / -04:00 DST	USA – Eastern Time
	`America/Chicago`	UTC-06:00 / -05:00 DST	USA – Central Time
	`America/Denver`	UTC-07:00 / -06:00 DST	USA – Mountain Time
	`America/Los_Angeles`	UTC-08:00 / -07:00 DST	USA – Pacific Time
	`America/Bogota`	UTC-05:00	Colombia
Asia	`Asia/Kolkata`	UTC+05:30	India
	`Asia/Dubai`	UTC+04:00	UAE
	`Asia/Tokyo`	UTC+09:00	Japan
	`Asia/Singapore`	UTC+08:00	Singapore
	`Asia/Shanghai`	UTC+08:00	China
	`Asia/Karachi`	UTC+05:00	Pakistan
	`Asia/Tehran`	UTC+03:30	Iran
Europe	`Europe/London`	UTC+00:00 / +01:00 DST	UK
	`Europe/Berlin`	UTC+01:00 / +02:00 DST	Germany
	`Europe/Paris`	UTC+01:00 / +02:00 DST	France
	`Europe/Moscow`	UTC+03:00	Russia
	`Europe/Istanbul`	UTC+03:00	Turkey
Australia	`Australia/Sydney`	UTC+10:00 / +11:00 DST	New South Wales
	`Australia/Perth`	UTC+08:00	Western Australia
	`Australia/Melbourne`	UTC+10:00 / +11:00 DST	Victoria
UTC/GMT	`Etc/UTC`	UTC+00:00	Coordinated Universal Time
	`Etc/GMT+5`	UTC-05:00	Fixed offset (reverse sign!)
Canada TZ	`Canada/Eastern` (alias)	UTC-05:00 / -04:00 DST	Alias for America/Toronto
	`Canada/Pacific` (alias)	UTC-08:00 / -07:00 DST	Alias for America/Vancouver

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

Upgrade and Patching oracle

Upgrade:

Path -----------------------------------------------------------------	Description ----------------------------------------------------
`$ORACLE_BASE/cfgtoollogs/dbua/<SID>/`	Main directory for DBUA logs per DB SID
`$ORACLE_BASE/cfgtoollogs/dbua/<SID>/upgrade*.log`	Main upgrade logs
`$ORACLE_BASE/cfgtoollogs/dbua/<SID>/trace.log`	Detailed trace of DBUA steps
`$ORACLE_BASE/cfgtoollogs/dbua/<SID>/postupgrade/*.log`	Logs of post-upgrade steps (script output)
`$ORACLE_HOME/cfgtoollogs/dbua/`	May also contain general DBUA logs (older versions)

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

OPatch:

Tool -------------	Log Location --------------------------------------------------------------------------------------------	Description ------------------------------------------
OPatch	`$ORACLE_HOME/cfgtoollogs/opatch/`	Main directory for `opatch` logs.
	`$ORACLE_HOME/cfgtoollogs/opatch/opatch<timestamp>.log`	Log file for each `opatch` execution.
OPatchAuto	`$ORACLE_HOME/cfgtoollogs/opatchauto/`	Root directory for all `opatchauto` sessions.
	`$ORACLE_HOME/cfgtoollogs/opatchauto/<timestamp>/`	Subfolder for a specific `opatchauto` run.
	`$ORACLE_HOME/cfgtoollogs/opatchauto/<timestamp>/opatchauto<timestamp>.log`	Main log for the auto-patching operation.
	Other files in the same folder	Logs for `root.sh`, database patching, rollback, etc.

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

Locking Unlocking oraInventory

Action ---------------------	Command ---------------------------------------------	Description ----------------------------------------
Find oraInventory path	Check from `oraInst.loc` file:`cat /etc/oraInst.loc` Look for `inventory_loc=`	Tells you where the `oraInventory` directory is located (e.g., `/u01/app/oraInventory`).
Check lock status	`ls -l $ORACLE_INVENTORY/locks/inventory.lck`	Checks if the lock file already exists. Replace `$ORACLE_INVENTORY` with the actual path.
Manually lock oraInventory	`touch $ORACLE_INVENTORY/locks/inventory.lck`	Creates the lock file manually to prevent concurrent OUI or patch operations.
Manually unlock oraInventory	`rm -f $ORACLE_INVENTORY/locks/inventory.lck`	Removes the lock file to release the lock (⚠️ only if no install/patch is running).
Check if Oracle install/patch process is running	`ps -ef	grep -i oraInst`

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

Wednesday, August 6, 2025

Dataguard standby views

View / Table ------------------------------	Purpose / Usage -----------------------------------------------------------------------------------
`v$dataguard_stats`	Shows Data Guard apply/transport lag statistics.
`v$dataguard_status`	Displays Data Guard error/status messages.
`v$dataguard_config`	❌ Not a standard Oracle view (possibly custom).
`v$dataguard_process`	Shows active Data Guard background processes (RFS, MRP, etc.).
`v$bgprocess`	Lists all background processes running in the instance.
`v$active_instances`	Lists active instances in RAC environments.
`v$active_services`	Shows currently active services in the instance.
`v$active_session_history`	ASH data; samples active sessions for performance diagnostics.
`dba_activity_table`	❌ Not standard — likely custom. Use `DBA_AUDIT_TRAIL` for DML audit.
`dba_app_errors`	Lists compilation errors for PL/SQL programs.
`dba_applications`	Lists applications registered (e.g., Workspace Manager).
`dba_ddl_locks`	Shows schema-level locks (DDL operations).
`dba_dml_locks`	Displays row-level DML locks (e.g., UPDATE, INSERT).
`dba_db_links`	Lists all defined DB links and their properties.
`dba_db_link_sources`	Shows which users/objects use DB links.
`dba_dependencies`	Shows object dependencies (views, packages, tables).
`dba_directories`	Lists Oracle Directory objects (for UTL_FILE, Data Pump, etc.).
`dba_goldengate_privileges`	Lists users with Oracle GoldenGate replication privileges.
`v$dbfile`	Lists datafiles and status info for current instance.
`v$tempfile`	Lists tempfiles used by temporary tablespaces.
`v$logfile`	Lists redo log files and their group/size/status.
`dba_data_files`	Lists all permanent datafiles and their details.
`dba_temp_files`	Lists all tempfiles in temp tablespaces.
`v$rman_configuration`	Displays RMAN configuration parameters (retention, device type).
`v$result_cache_objects`	Lists cached results; check for cache flushes here.
`v$passwordfile_info`	Shows info about Oracle password file usage.
`v$pdbs`	Lists PDBs (Pluggable Databases) in a CDB environment.
`v$parameter_valid_values`	Lists valid values for init parameters (for validation).
`v$obsolete_backup_files`	Shows obsolete backups eligible for deletion by RMAN.
`v$obsolete_parameter`	Lists deprecated/obsolete init parameters.
`v$instance_ping`	RAC view; shows block ping stats between instances.
`v$session_connect_info`	Shows connection protocol/service per session.

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

Friday, July 25, 2025

RMAN BACKUP FOR PATCHING AND UPGRADES

make sure:

1) database in archive log mode:

select log_mode from v$database;

archive log list;

-- look for how to make db in archivelog mode

https://kayyumoracledba.blogspot.com/2023/05/enable-archive-log-mode-in-oracle.html

2) database in flashback_on mode:

select flashback_on from v$database;

--look for how to make db in flashback_on mode

https://kayyumoracledba.blogspot.com/2023/05/enable-archive-log-mode-in-oracle.html

-- RMAN BACKUP Before Upgrades or patching --

run {

backup incremental level 0 database tag='BKP_DB_B4UPGRADE';

backup archivelog all not backed up delete input tag='BKP_ARC_B4UPGRADE';

backup current controlfile tag='BKP_CONTROLFILE_B4UPGRADE';

backup spfile tag='BKP_SPFILE_B4UPGRADE';

}

--GRP before upgrades or patching:-

-- Run this as SYSDBA

create restore point GRP_B4UPGRADE guarantee flashback database;

COL name FORMAT A30;

COL guarantee_flashback_database FORMAT A10;

COL time FORMAT A30;

SELECT

name,

scn,

guarantee_flashback_database,

time

FROM

v$restore_point;

NAME	SCN	GUARANTEE_FLASHBACK_DATABASE	TIME
GRP_B4UPGRADE	123456789	YES	25-JUL-25 07.10.05.000000 PM

--to see in indian time zone

COLUMN name FORMAT A30

COLUMN guarantee_flashback_database FORMAT A10

COLUMN ist_time FORMAT A30

SELECT

name,

scn,

guarantee_flashback_database,

CAST(

FROM_TZ(CAST(time AS TIMESTAMP), 'UTC') AT TIME ZONE 'Asia/Kolkata'

AS TIMESTAMP

) AS ist_time

FROM

v$restore_point;

SELECT

object_name,

object_type,

CAST(

FROM_TZ(CAST(created AS TIMESTAMP), DBTIMEZONE) AT TIME ZONE 'Asia/Kolkata'

AS TIMESTAMP

) AS ist_created

FROM

dba_objects;

--To see if your session is using a different time zone than the DB:

SELECT DBTIMEZONE, SESSIONTIMEZONE FROM dual;

DBTIMEZONE SESSIONTIMEZONE

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

+00:00 Asia/Kolkata

SELECT

SYSTIMESTAMP AS original_time,

FROM_TZ(CAST(SYSTIMESTAMP AS TIMESTAMP), DBTIMEZONE) AT TIME ZONE 'Asia/Kolkata' AS converted_to_ist,

FROM_TZ(CAST(SYSTIMESTAMP AS TIMESTAMP), SESSIONTIMEZONE) AT TIME ZONE DBTIMEZONE AS back_to_db_tz

FROM dual;

ORIGINAL_TIME CONVERTED_TO_IST BACK_TO_DB_TZ

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

25-JUL-25 14.45.00.000000 +00:00 25-JUL-25 20.15.00.000000 +05:30 25-JUL-25 14.45.00.000000 +00:00

-- For setting session for IST time zone

ALTER SESSION SET TIME_ZONE = 'Asia/Kolkata';

-- To convert startup_time to timestamp ist

SELECT

TO_CHAR(

FROM_TZ(CAST(startup_time AS TIMESTAMP), DBTIMEZONE) AT TIME ZONE 'Asia/Kolkata',

'dd-mon-yy hh:mi:ss am'

) AS startup_ist

FROM v$instance;

Column/Function	Respects Session Time Zone?	Type
`startup_time`	❌ No	`DATE`
`SYSTIMESTAMP`	✅ Yes	`TIMESTAMP WITH TIME ZONE`
`CURRENT_TIMESTAMP`	✅ Yes	`TIMESTAMP WITH TIME ZONE`
`LOCALTIMESTAMP`	✅ Yes	`TIMESTAMP`

To convert a DATE value to IST (Indian Standard Time) in Oracle, you need to:

Cast the DATE to TIMESTAMP (because DATE does not store time zone info).
Attach the database time zone using FROM_TZ.
Convert it to IST using AT TIME ZONE 'Asia/Kolkata'.

✅ General Syntax

sql
SELECT 
  FROM_TZ(CAST(your_date_column AS TIMESTAMP), DBTIMEZONE) 
  AT TIME ZONE 'Asia/Kolkata' AS converted_to_ist
FROM your_table;

Friday, July 4, 2025

Grants read-only access on all schemas tables in oracle

SQL> BEGIN
FOR x IN (select table_name from dba_tables where owner='SCHEMA_OWNER') LOOP
EXECUTE IMMEDIATE 'GRANT SELECT ON PUNE.'|| x.table_name ||' TO <role_name>';
END LOOP;
END;
/

SQL> CREATE USER <username> IDENTIFIED BY "<password>"
DEFAULT TABLESPACE "USERS"
TEMPORARY TABLESPACE "TEMP"
PROFILE "DEFAULT"; 2    3    4

User created.

SQL> create role pune_ro;
role created.

SQL> show user;
USER is "SYS"
SQL> BEGIN
FOR x IN (select table_name from dba_tables where owner='PUNE') LOOP
    EXECUTE IMMEDIATE 'GRANT SELECT ON PUNE.'|| x.table_name ||' TO pune_ro';
END LOOP;
END;
/ 2    3    4    5    6

PL/SQL procedure successfully completed.

SQL> alter user <username> default role all;

User altered.

SQL> grant app_user_dbpull to <username> ;

Grant succeeded.

SQL> revoke select on pune.alert from <username>;

Revoke succeeded.

SQL> grant pune_ro to <username>;

Grant succeeded.

---

1. create a user

1.1 alter user <username> default role all;

2. create a role

3. grant all tables select on access on schemas tables

4. grant the role to <username>

Saturday, May 24, 2025

Add & Drop ASM DISKS

Monday, April 21, 2025

export oracle environment shell script

What you can achieve ?

1. sqlplus / as sysdba

2. rman target /

3. expdp ...

4. impdp ...

5. srvctl

Thursday, March 20, 2025

Identify the Purpose of Patching

Thursday, March 13, 2025

SQL styling and formatting !!!!!

-- Increase the size for long DDL outputs
SET LONG 10000;           -- Allows displaying long DDL statements
SET LONGCHUNKSIZE 1000;   -- Controls how LONG data is fetched and displayed

-- Formatting options
SET LINESIZE 200;         -- Adjusts output width for better readability
SET PAGESIZE 100;         -- Controls the number of lines per page (0 removes headers)
SET TRIMOUT ON;           -- Removes trailing spaces from output
SET TRIMSPOOL ON;         -- Removes trailing spaces when spooling output
SET WRAP OFF;             -- Prevents line wrapping in output

-- Improve output structure
SET COLSEP '|';           -- Adds a column separator for better visibility
SET UNDERLINE '-';        -- Adds an underline separator under column headers

-- Display settings
SET SERVEROUTPUT ON;      -- Enables DBMS_OUTPUT messages
SET VERIFY OFF;           -- Suppresses display of substitution variable values
SET FEEDBACK OFF;         -- Removes row count messages from the output
SET HEADING ON;           -- Ensures column headers are displayed
SET ECHO OFF;             -- Hides command execution in the output

-- If using spooling (saving output to a file)
SPOOL my_output.txt;

-- Your SQL queries here...

SPOOL OFF;

--For DDL Queries:
SET LONG 10000;
SET PAGESIZE 0;
SET LINESIZE 200;
SET TRIMOUT ON;
SET WRAP OFF;

--For Tabular query result
SET PAGESIZE 100;
SET LINESIZE 200;
SET COLSEP '|';
SET UNDERLINE = '-';
------------------------------------------
/* For exporting result to a file */
SET LONG 10000;
SET PAGESIZE 0;
SET LINESIZE 200;
SET TRIMOUT ON;
SET WRAP OFF;
SPOOL my_output.txt;

-- Your queries here...

SPOOL OFF;
--------------------------------------------

Monday, April 20, 2026

Step 1: Install OpenSSH Server

Step 2: Start the Service & Set to Automatic

Step 3: Verify the Firewall Rule Again

Step 4: Final Connection Test

Friday, April 17, 2026

Saturday, April 11, 2026

Thursday, April 9, 2026

Complete Standby Database Setup from Scratch

Environment Details (Example)

PHASE 1 — PRIMARY SERVER PREPARATION

Step 1 — Check Primary Database Mode

Step 2 — Enable Force Logging on Primary

Step 3 — Enable Flashback (Recommended)

Step 4 — Create Standby Redo Logs on Primary

Step 5 — Configure Primary init.ora / spfile Parameters

Step 6 — Configure listener.ora on Primary

Step 7 — Configure tnsnames.ora on Primary

Step 8 — Create Password File on Primary

PHASE 2 — STANDBY SERVER PREPARATION

Step 9 — Create Directory Structure on Standby

Step 10 — Create standby init.ora on Standby

Step 11 — Configure listener.ora on Standby

Step 12 — Configure tnsnames.ora on Standby

Step 13 — Set Oracle Environment on Standby

Step 14 — Test Connectivity Between Primary and Standby

PHASE 3 — BUILD STANDBY USING RMAN DUPLICATE

Step 15 — Start Standby in NOMOUNT

Step 16 — Run RMAN DUPLICATE from Standby Server

PHASE 4 — POST DUPLICATE STEPS

Step 17 — Create spfile from pfile on Standby

Step 18 — Start MRP Apply Process

Step 19 — Verify Standby is Working

Complete Checklist

Key Points to Remember

Minimal init.ora for Standby Database

Absolute Minimum Parameters Required

Side-by-Side Comparison

Sunday, September 7, 2025

Full Flow Using RMAN DUPLICATE (Easier Method)

Thursday, August 7, 2025

Wednesday, August 6, 2025

Friday, July 25, 2025

✅ General Syntax

Friday, July 4, 2025

Saturday, May 24, 2025

Monday, April 21, 2025

Thursday, March 20, 2025

Thursday, March 13, 2025