Skip to main content

Welcome to DBA Master – Database Tips, Tricks, and Tutorials

Welcome to DBA Master ! This blog is dedicated to all things related to database administration , SQL optimization , and performance tuning . Whether you're a beginner or a seasoned DBA, you'll find practical guides, troubleshooting tips, and real-world tutorials to help you work smarter with data. What to Expect: SQL performance tuning tips Indexing strategies Backup and recovery best practices High availability and replication techniques Database creation, configuration, and setup Monitoring queries and scripts for proactive performance management Migration guides across different database platforms Security essentials and best practices Recommended tools for DBAs Real-world error fixes and how to solve them Stay tuned — exciting content is coming soon. Feel free to bookmark and share: www.dbamaster.com ! Thanks for visiting!
Post a Comment

Dataguard Setup in Oracle 19C Container Database

Environment:

Oracle Version: 19.3.0
OS Version: Oracle Linux Server 8

Primary Server

DB Unique Name=pearl
DB Name=pearl
Hostname=192.168.31.201

Standby Server

DB Unique Name=muthu
DB Name=muthu
Hostname=192.168.31.202

Add listener Entery primary Side:

[oracle@localhost admin]$ cat /u01/app/oracle/product/19.0.0/dbhome_1/network/admin/listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/19.0.0/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = pearl)
      (ORACLE_HOME = /u01/app/oracle/product/19.0.0/dbhome_1)
      (SID_NAME = pearl)
    )

  )

LISTENER =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.31.201)(PORT = 1521))
  )

ADR_BASE_LISTENER = /u01/app/oracle

Add listener Entery Standby Side:

[oracle@localhost admin]$ cat /u01/app/oracle/product/19.0.0/dbhome_1/network/admin/listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/19.0.0/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = muthu)
      (ORACLE_HOME = /u01/app/oracle/product/19.0.0/dbhome_1)
      (SID_NAME = muthu)
    )

  )

LISTENER =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.31.202)(PORT = 1521))
  )

ADR_BASE_LISTENER = /u01/app/oracle

Enable TNS Entry For Both Side:

[oracle@localhost admin]$ cat /u01/app/oracle/product/19.0.0/dbhome_1/network/admin/tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/19.0.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

PEARL =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.31.201)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = pearl)
    )
  )

MUTHU =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.31.202)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = muthu)
    )
  )

Note: add the pluggable database entry also

Check TND ping for Both Side:
tnsping pearl 4
tnsping muthu 4

Step 1: (prim)Enable archivelog mode and Force logging

select log_mode from v$database;

(OR)

archive log list
set linesize 250
select name,force_logging,flashback_on,log_mode from v$database;

How to enable Archiver and set the location:
shut immediate
startup mount
alter database archivelog;

alter database force logging;
alter database open;

(prim)Enable the FRA
alter system set db_recovery_file_dest_size=15G scope=both;
!mkdir -p /u02/oradata/fra/
alter system set db_recovery_file_dest='/u02/oradata/fra/' scope=both;

Flashback should be enabled in primary database:

ALTER DATABASE FLASHBACK ON;
 
select name,open_mode,force_logging,flashback_on,log_mode from v$database;

NAME      OPEN_MODE            FORCE_LOGGING                           FLASHBACK_ON       LOG_MODE
--------- -------------------- --------------------------------------- ------------------ ------------
PEARL     READ WRITE           YES                                     YES                ARCHIVELOG

Step 2: (prim)Add the Standby  Redo Log files

set linesize 250
col MEMBER format a80
select group#,member,type,status from v$logfile;

alter database add standby logfile group 4 '/u02/oradata/PEARL/redo04.log' size 150m;
alter database add standby logfile group 5 '/u02/oradata/PEARL/redo05.log' size 150m;
alter database add standby logfile group 6 '/u02/oradata/PEARL/redo06.log' size 150m;

SELECT GROUP#,THREAD#,SEQUENCE#,ARCHIVED,STATUS FROM V$STANDBY_LOG;

Step 3: Configure the dataguard parameters primary side

ALTER SYSTEM SET log_archive_config='dg_config=(pearl,muthu)' SCOPE=both;
ALTER SYSTEM SET fal_server='muthu' SCOPE=both;
ALTER SYSTEM SET fal_client='pearl' SCOPE=both;
ALTER SYSTEM SET standby_file_management='AUTO' SCOPE=both;

ALTER SYSTEM SET log_archive_dest_1='location=use_db_recovery_file_dest valid_for=(all_logfiles,all_roles) db_unique_name=pearl' SCOPE=both;

ALTER SYSTEM SET log_archive_dest_2='service=muthu async valid_for=(online_logfiles,primary_role) db_unique_name=muthu' SCOPE=both;
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE scope=both;
alter system set log_archive_format='%t_%s_%r.dbf' scope=spfile;

alter system set log_archive_max_processes=30 scope=both;
alter system set remote_login_passwordfile=exclusive scope=spfile;

##Optional
alter system set db_file_name_convert='/u02/oradata/PEARL','/u02/oradata/MUTHU/' scope=spfile;
alter system set log_file_name_convert='/u02/oradata/PEARL','/u02/oradata/MUTHU/' scope=spfile;

Step 4: restart the Primary database and verify the parameters:

shut immediate
startup

Verify the parameter Values:
col name for a30
col value for a85
set lin 300 pagesize 300

select name, value from v$parameter
where name in ('db_name','db_unique_name','log_archive_config','log_archive_dest_1','log_archive_dest_2',
'log_archive_dest_state_1′,’log_archive_dest_state_2','remote_login_passwordfile','log_archive_format','log_archive_max_processes',
'fal_server','fal_client','db_file_name_convert','log_file_name_convert','standby_file_management');

Step 5:(prim) create password file and send to standby

cd $ORACLE_HOME/dbs
orapwd file=orapwpearl password=admin123 entries=20 format=12
scp orapwpearl oracle@192.168.31.202:/u01/app/oracle/product/19.0.0/dbhome_1/dbs/orapwmuthu

Step 6:(stand) Create the required directory on the standby side

check primary server data and log file locations:

col name format a80
select name from v$datafile;
show parameter audit;
show parameter db_recovery_file_dest;

create the directory like:

mkdir -p /u02/oradata/MUTHU/
mkdir -p /u02/oradata/MUTHU/pdbseed/
mkdir -p /u02/oradata/MUTHU/pdb1/
mkdir -p /u02/oradata/MUTHU/pdb2/
mkdir -p /u01/app/oracle/admin/muthu/adump
mkdir -p /u02/oradata/fra/

Step 7: create pfile for standby

[oracle@localhost dbs]$ cat /u01/app/oracle/product/19.0.0/dbhome_1/dbs/initmuthu.ora
db_name=pearl
enable_pluggable_database=true

Step 8: start the standby database use pfile with nomount stage

export ORACLE_SID=muthu

sqlplus / as sysdba

startup pfile='/u01/app/oracle/product/19.0.0/dbhome_1/dbs/initmuthu.ora' nomount;

Step 9: connect RMAN from standby  (both password need to same)

rman target sys/admin123@pearl auxiliary sys/admin123@muthu

Step 10: Build the standby use rman

RMAN> duplicate target database for standby from active database nofilenamecheck;

(OR)

duplicate target database for standby from active database;

(OR)

Recommended:  

RMAN>
run
{
allocate channel c1 type disk;
allocate auxiliary channel c3 type disk;
duplicate target database for standby from active database
spfile
parameter_value_convert 'pearl','muthu'
set db_name='pearl'
set db_unique_name='muthu'
set audit_file_dest='/u01/app/oracle/admin/muthu/adump'
set diagnostic_dest='/u01/app/oracle/admin/muthu/adump'
set db_file_name_convert='/u02/oradata/PEARL/','/u02/oradata/MUTHU/'
set log_file_name_convert='/u02/oradata/PEARL/','/u02/oradata/MUTHU/'
set control_files='/u02/oradata/MUTHU/control01.ctl'
set log_archive_max_processes='5'
set fal_client='muthu'
set fal_server='pearl'
set standby_file_management='AUTO'
set log_archive_config='dg_config=(pearl,muthu)'
set compatible='19.0.0.0.0'
nofilenamecheck;
}

Step 11: start the MRP process in standby.

select name,database_role,open_mode from v$database;

SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

alter database open;

alter database recover managed standby database disconnect from session;

Step 12: check MRP process

select sequence#,process,status from v$managed_standby;

Step 13: verify the standby status

select name,open_mode,database_role from v$database;

select name, value from v$dataguard_stats where name in ('transport lag', 'apply lag');

NAME      OPEN_MODE            DATABASE_ROLE
--------- -------------------- ----------------
PEARL     READ ONLY WITH APPLY PHYSICAL STANDBY

Step 14: log applying status in standby

SELECT sequence#, first_time, next_time, applied FROM v$archived_log ORDER BY sequence#;

Step 15: Check the differences redo log sequences(Both Sides)

SELECT  ARCH.THREAD# AS Thread, ARCH.SEQUENCE# AS "Last Sequence Received", APPL.SEQUENCE# AS "Last Sequence Applied",
(ARCH.SEQUENCE# - APPL.SEQUENCE#) AS Difference FROM (SELECT THREAD#, SEQUENCE# FROM V$ARCHIVED_LOG
WHERE (THREAD#, FIRST_TIME) IN (SELECT THREAD#, MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,
(SELECT THREAD#, SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#, FIRST_TIME) IN (SELECT THREAD#, MAX(FIRST_TIME)
FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL WHERE ARCH.THREAD# = APPL.THREAD# ORDER BY 1;

Enable flashback on standby database:

alter database recover managed standby database cancel;

alter database flashback on;

alter database recover managed standby database disconnect from session;

select sequence#,process,status from v$managed_standby;

select role, thread#, sequence#, action from v$dataguard_process;

select name,force_logging,flashback_on,log_mode from v$database;

NAME      FORCE_LOGGING                           FLASHBACK_ON       LOG_MODE
--------- --------------------------------------- ------------------ ------------
PEARL   YES                                     YES                ARCHIVELOG

select status,instance_name,database_role,protection_mode from v$database,v$instance;

select max(sequence#) from v$archived_log;

Configure Archive deletion policy:

We must set this policy in order to prevent accidental deletion of archive logs on primary database

On Primary:

rman target /
configure archivelog deletion policy to applied on all standby;

create some tables and insert the data primary side check standby

Primary Database:

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1                           MOUNTED
         4 PDB2                           MOUNTED

SQL> alter pluggable database all open;

Pluggable database altered.

SQL> alter session set container=pdb1;

Session altered.

SQL> create user shri identified by shri123;

User created.

SQL> grant connect,create session,resource,unlimited tablespace to shri;

Grant succeeded.

SQL> create table shri.emp(id number(3),name varchar2(50));

Table created.

SQL> insert into shri.emp values(1,'muthu');

1 row created.

SQL> insert into shri.emp values(2,'siva');

1 row created.

SQL> commit;

Commit complete.

SQL>  alter session set container=cdb$root;

Session altered.

SQL> alter system switch logfile;

System altered.

Standby Database:

SQL> alter pluggable database all open;

Pluggable database altered.

SQL> alter session set container=pdb1;

Session altered.

SQL> select * from shri.emp;

        ID NAME
---------- --------------------------------------------------
         1 muthu
         2 siva

Labels:

Comments

Post a Comment

Popular posts from this blog

Oracle Database 19C Performance Tunning - PART 1

Advantages: 1. Improved Query Performance •    Optimized SQL execution plans lead to faster query response times. •    Reduces unnecessary full table scans and improves indexing strategies. •    Parallel execution tuning speeds up large data processing tasks. 2. Better Resource Utilization •    Efficient use of CPU, memory, disk I/O, and network resources. •    Reduces contention on Redo Logs, Undo Tablespaces, and Buffer Cache. •    Helps in load balancing across multiple instances in RAC (Real Application Clusters). 3. Increased System Scalability •    Ensures that the database can handle a growing number of users and transactions. •    Proper tuning allows scaling without degrading performance. •    Optimized parallel processing ensures better performance on multi-core servers. 4. Lower Infrastructure Costs •    Reduces the need for add...
Post a Comment
Read more

Oracle RMAN Backup And Restore

RMAN: (Oracle 8) RMAN (Recovery Manager) is a utility provided by Oracle Database to perform backup, restore, and recovery operations. It is a command line tool. Features of RMAN in Oracle 19c Comprehensive Backup Capabilities: Full and incremental backups. Block-level backups for efficient data storage. Archived redo log backups. Fast Recovery Area (FRA) integration for centralized backup storage. Efficient Recovery Options: Point-in-time recovery (PITR). Complete and incomplete recovery. Flashback database capabilities for quick undo of changes. Multitenant Database Support: RMAN fully supports container databases (CDBs) and pluggable databases (PDBs). Provides flexibility to back up and recover individual PDBs or entire CDBs. Automatic Space Management: Manages disk space in the FRA. Automatically deletes obsolete backups and archived logs. Data Deduplication and Compression: Backup optimization through block-level deduplication. Built-in compression algorithms to reduce storage req...
Post a Comment
Read more

Oracle 19c Database Software Installation in OEL8

 Pre-requisites for OS level:            Set the static IP Address     Disable the Firewall (systemctl stop firewalld & systemctl disable firewalld)     set SELINUX=permissive on /etc/selinux/config  ##Need to restart the server use init 6 Oracle Installation Pre-requisites Methods     Automatic Setup     Manual Setup      Automatic requisites Setup: (avoid step 1 to step 5): dnf install -y oracle-database-preinstall-19c Install the dependencies: curl -o oracle-database-preinstall-19c-1.0-2.el8.x86_64.rpm https://yum.oracle.com/repo/OracleLinux/OL8/appstream/x86_64/getPackage/oracle-database-preinstall-19c-1.0-2.el8.x86_64.rpm dnf -y localinstall oracle-database-preinstall-19c-1.0-2.el8.x86_64.rpm Manual Setup: step 1: Add the karenl parameters and values vi /etc/sysctl.conf     fs.file-max = 6815744 kernel.sem = 250 32000 100 128 kernel....
Post a Comment
Read more