Oracle Database Scripts : April 2026

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;

__________________________________________________________________

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