Skip to content

🔧 Advanced DBA Commands: Data Guard, GoldenGate, and More

This guide is a comprehensive cheat sheet for Oracle DBAs covering ADRCI, ASMCMD, CRSCTL, DGMGRL, Flashback, Job Scheduling, SRVCTL, GoldenGate, OPatch, Auditing, RMAN, and Stats Gathering commands.

📁 ADRCI Commands in Oracle

ADRCI (Automatic Diagnostic Repository Command Interpreter) is used to manage Oracle diagnostic data.

# Show current ADR base location
adrci> show base

# Set new ADR base
adrci> set base /u01/app/grid

# View alert logs
adrci> show alert
adrci> show alert -tail 100

# Purge alerts and trace files
adrci> purge -age 600 -type ALERT
adrci> purge -age 600 -type TRACE
adrci> purge -age 10080 -type cdump

# Set control policies
adrci> set control (SHORTP_POLICY = 240)
adrci> set control (LONGP_POLICY = 600)

# Create incident package
adrci> ips create package incident
adrci> ips generate package 2 in /home/dbaclass/housekeeping

🧱 ASMCMD Commands

ASMCMD is a command-line utility to manage ASM (Automatic Storage Management).

# List all diskgroups
ASMCMD> lsdg

# List ASM disks
ASMCMD> lsdsk -k -G CDATA

# Unmount diskgroup
ASMCMD> umount ARCH

# Mount diskgroup
ASMCMD> mount ARCH

# Rebalance diskgroup
ASMCMD> rebal --power 8 ARCH

🧩 CRSCTL Commands

CRSCTL is used to manage Oracle Clusterware components.

# Start/Stop CRS
crsctl start crs
crsctl stop crs

# Check CRS status
crsctl check crs

# Find cluster name and version
crsctl query crs softwareversion

# Replace voting disk
crsctl replace votedisk +OCRVD

🌐 DGMGRL Commands for Data Guard

DGMGRL is used to configure and manage Data Guard Broker.

-- Enable DG Broker
ALTER SYSTEM SET DG_BROKER_START=TRUE SCOPE=BOTH;

-- Create configuration
DGMGRL> CREATE CONFIGURATION 'PROD_DG' AS PRIMARY DATABASE IS 'PRIMDB' CONNECT IDENTIFIER IS PRIMDB;

-- Add standby
DGMGRL> ADD DATABASE 'STYDB' AS CONNECT IDENTIFIER IS STYDB MAINTAINED AS PHYSICAL;

-- Switchover
DGMGRL> switchover to STYDB;

🕰️ Flashback Database Commands

-- Enable Flashback
ALTER SYSTEM SET db_recovery_file_dest='/path' SCOPE=BOTH;
ALTER DATABASE FLASHBACK ON;

-- Create restore point
CREATE RESTORE POINT FLASHBACK_PREP GUARANTEE FLASHBACK DATABASE;

-- Flashback to restore point
FLASHBACK DATABASE TO RESTORE POINT FLASHBACK_PREP;
ALTER DATABASE OPEN RESETLOGS;

🕹️ Scheduler Job Commands

-- Create schedule
BEGIN
  DBMS_SCHEDULER.CREATE_SCHEDULE(...);
END;

-- Create program
BEGIN
  DBMS_SCHEDULER.CREATE_PROGRAM(...);
END;

-- Create job
BEGIN
  DBMS_SCHEDULER.CREATE_JOB(...);
END;

-- Run/Enable/Disable/Drop job
EXEC DBMS_SCHEDULER.RUN_JOB('JOB_NAME');
EXEC DBMS_SCHEDULER.ENABLE('JOB_NAME');
EXEC DBMS_SCHEDULER.DISABLE('JOB_NAME');
EXEC DBMS_SCHEDULER.DROP_JOB('JOB_NAME');

⚙️ SRVCTL Commands

SRVCTL is used to manage resources in Oracle RAC.

# Start/Stop database
srvctl start database -d DBNAME -o open
srvctl stop database -d DBNAME -o immediate

# Manage services
srvctl add service -d DBNAME -s SERVICE_NAME -r "INST1,INST2"
srvctl start service -d DBNAME -s SERVICE_NAME

🪄 GoldenGate OBEY Command

# Create obey file
vi addtran.oby
# Add commands
dblogin USERID ggate, PASSWORD ggate123
add trandata dbaclass.TEST3

# Execute
GGSCI> obey addtran.oby

Nested OBEY Example:

ALLOWNESTED
obey infotran1.oby
obey infotran2.oby

🔧 OPatch Commands

# List patches
$ORACLE_HOME/OPatch/opatch lsinventory

# Apply a patch
$ORACLE_HOME/OPatch/opatch apply

# Rollback a patch
$ORACLE_HOME/OPatch/opatch rollback -id PATCH_ID

🔍 Oracle Auditing

-- Enable auditing
ALTER SYSTEM SET audit_trail=db SCOPE=SPFILE;

-- Audit DML actions
AUDIT SELECT, INSERT, UPDATE ON schema.table BY USER;

-- Unified Auditing
CREATE AUDIT POLICY test_policy ACTIONS CREATE TABLE;
AUDIT POLICY test_policy;

💾 RMAN Backup Commands

# Full backup
BACKUP AS COMPRESSED BACKUPSET INCREMENTAL LEVEL 0 DATABASE PLUS ARCHIVELOG;

# Archive log backup
BACKUP ARCHIVELOG ALL DELETE INPUT;

# Delete old archives
DELETE NOPROMPT ARCHIVELOG ALL COMPLETED BEFORE 'SYSDATE-1';

📊 DB Statistics Commands

-- Gather stats
EXEC DBMS_STATS.GATHER_DATABASE_STATS(estimate_percent => 15);

-- Export stats
EXEC DBMS_STATS.EXPORT_TABLE_STATS(...);

-- Lock stats
EXEC DBMS_STATS.LOCK_TABLE_STATS('SCHEMA','TABLE');

📌 Summary

This comprehensive list of advanced DBA commands acts as a handy reference for DBAs working on:

  • Oracle RAC
  • ASM
  • Data Guard
  • RMAN backups
  • GoldenGate
  • Scheduler jobs
  • Flashback
  • System audits

Want a downloadable Markdown version or a split-by-section blog post? Just ask!

Would you like this as separate blog entries per topic or a single blog post?

Comments

------ADD SCRIPT HERE ----------