Oracle 19C RAC Database stop and start Step By Step process

 

Dear All,

In this post i am going to show you how to start and stop Oracle 19c RAC Database services including cluster services .

Check the db and grid background process status from putty ( backend ) 

ps -ef |grep oracle / ps -fu $USER  >> this will show you the all the process which are running under specific login user .

ps -ef |grep grid

ps -ef |grep d.bin

ps -ef |grep smon

Check the DB and Oracle Instance status from SQL prompt : 

select name,open_mode,instance_name from v$database,v$instance;

 

 

 

 Stop the database :: 

 

srvctl stop database -d RACSDB -o immediate

srvctl stop database -d RACSDB

 

Start and stop individual Instances : 

srvctl start instance -d RACSDB -i racsdb1

srvctl start instance -d RACSDB -i racsdb2

 

srvctl stop instance -d RACSDB -i racsdb1

srvctl stop instance -d RACSDB -i racsdb2

 

 Start and stop the cluster services :

Note : login to root user and go the grid home/bin path and execute below commands 

##cluster status 

./crsctl check cluster -all

./crsctl stat res -t

./crsctl check crs

##stop the cluster services 

./crsctl stop crs

##start the cluster services 

./crsctl start crs

 

crsctl check cluster -all

crsctl start res ora.crsd -init

crsctl check crs

./crsctl stat res -t

crsctl check crs

>>> Check Oracle High Availability Services On The Local Server.

crsctl stop crs

>>> Stop Oracle High Availability Services On The Local Server.

crsctl start crs

 >>> Start Oracle High Availability Services On The Local Server.

 

crsctl query crs software version

>>> Check Oracle Cluster ware Software Version

crsctl query crs active version

 >>> Check Oracle Cluster ware Active Version

crsctl query crs release version

>>> Check Oracle Cluster ware Release Version

 

 Thanks,

srini

Oracle DBA Interview Q&A - Part 3

 Dear All,

Here i'm posting few Oracle DBA Interview Question and answers these will help you on Fresher or experience level technical Interviews .

1. Oracle Database Administration ( DBA ):

Q: How do you create a new user in Oracle DB?

Answer: To create a new user in Oracle, you can use the CREATE USER SQL statement. For example:

SQL > CREATE USER username IDENTIFIED BY password;

Q: Explain the difference between ARCHIVELOG and NOARCHIVELOG mode in Oracle DB.

Answer:

 In ARCHIVELOG mode, the database archives filled redo log files to a specified storage location, allowing for complete recovery from media or instance failure. In NOARCHIVELOG mode, the database does not archive filled redo log files, limiting recovery options.

 

Q. What is Oracle Database ( DB)?

 

Answer: 

Oracle Database is a relational database management system (RDBMS) developed by Oracle Corporation. It is a collection of data and procedures, which can be accessed and managed by various applications.

 

Q. Explain the architecture of Oracle Database ( DB).

 

Answer: 

Oracle Database architecture consists of the following components:

 

Instance: It is a set of memory structures and background processes that manage a database.

Database: It is a collection of physical files on disk managed by Oracle.

Tablespace: Logical storage unit within a database where actual data is stored.

Data files, Control files, Redo log files: Physical files associated with an Oracle database.

Q. Explain the role of the REDO log in Oracle DB.

 

Answer: 

The REDO log records all changes made to the database. It plays a crucial role in database recovery in case of a failure. REDO log files store changes before they are actually written to the database files, ensuring data integrity and recoverability.

 

2. Performance Tuning:

Q: How do you identify and resolve performance bottlenecks in Oracle Database ?

Answer: Performance bottlenecks can be identified using tools like Oracle Enterprise Manager or by querying dynamic performance views (e.g., V$SESSION, V$SQL, V$SQL_PLAN). Common tuning techniques include optimizing SQL queries, adding indexes, adjusting memory parameters, and optimizing I/O.

Q: What is the purpose of an Oracle index and how does it improve query performance in Database?

Answer:

 An Oracle index is a database object that improves the speed of data retrieval operations on a table at the cost of additional storage and decreased performance on data modification operations. Indexes provide a faster way to access rows, reducing the need for full-table scans.

 

3. Oracle Backup and Recovery:

Q: How do you perform a hot backup in Oracle database?

Answer: 

Hot backup, also known as online backup, can be performed using tools like RMAN (Recovery Manager) or by using the ALTER TABLESPACE BEGIN BACKUP and END BACKUP commands to put tablespaces in backup mode and copy the data files while the database is online.

Q: Explain the steps to recover a database using RMAN after a critical failure of database.

Answer: 

To recover a database using RMAN, you would typically:

 

Restore the database files from a valid backup.

Apply incremental backups, if available, to reduce recovery time.

Apply archived redo logs to roll forward the database to the desired point in time.

Open the database with the RESETLOGS option to reset the redo log sequence.

 

4. Security in Oracle DB:

Q: How do you grant and revoke privileges in Oracle DB?

Answer:

 Privileges can be granted using the GRANT statement and revoked using the REVOKE statement. For example:

 

SQL > GRANT SELECT, INSERT ON table_name TO username;

SQL > REVOKE SELECT ON table_name FROM username;

Q: What is Oracle Transparent Data Encryption (TDE) and how does it enhance security in Oracle DB?

Answer: 

Oracle TDE encrypts sensitive data stored in database columns, tablespaces, or entire databases, adding an additional layer of security. It helps protect data at rest, preventing unauthorized access to sensitive information.

 

5. Troubleshooting Questions :

Q: How do you diagnose and resolve locking issues in Oracle database?

Answer: 

Locking issues can be diagnosed by querying the V$LOCK and V$SESSION views. To resolve locking problems, you can either commit or rollback open transactions, release locks explicitly, or tune SQL statements causing excessive locks.

Q: What are common causes of performance degradation in Oracle databases ( DB), and how do you address them?

Answer:

 Performance degradation can be caused by suboptimal SQL queries, inadequate hardware resources, inefficient database design, or improper configuration. Addressing these issues involves optimizing SQL queries, adjusting memory and I/O configurations, and periodically reviewing database schema and indexing strategies.

Q. How do you optimize SQL queries for better performance in Oracle database?

 

Answer: 

Optimization techniques include:

Analyzing query execution plans using tools like Explain Plan.

Indexing frequently queried columns.

Avoiding the use of SELECT * and fetching only necessary columns.

Using appropriate hints to guide the optimizer.

Regularly analyzing and rebuilding indexes.

Properly designing database schema and normalizing data.

Q. Explain what a deadlock is and how to resolve it in oracle.

 Answer:

 A deadlock occurs when two or more transactions are blocked, each waiting for the other to release a resource. To resolve a deadlock, Oracle automatically detects and resolves it by rolling back one of the statements involved in the deadlock. Proper transaction management and application design can also prevent deadlocks.

Thanks,

Srini

Step by Step Oracle 19C Data Guard setup on Oracle VM

 Dear All,

In this post i am going to share you oracle 19c Data guard step by step activity guide .

Primary database details :

 

Db name : PROD

Db unique name :stby

Host & IP :  standby.localdomain

SID : stby

 

Standby Database Details :

 

Db name : PROD

Db unique name : prod

Host & IP : primary.localdomain

SID : prod

DG Configuration steps : 

Step:1 Enable the archive log  mode in the primary database.

 

alter database archivelog;

archive log list;

Step:2 Enable the force logging. 

SQL> alter database force logging;

Database altered.

 

Note : to enable flash back we need to setup below pre-requisites

alter system set db_recovery_file_dest_size= 10G scope=both;

 

alter system set db_recovery_file_dest='/u02/app/oracle/fast_recovery_area' scope=both;

Step:3 Flashback should be enabled in primary database.

SQL> ALTER DATABASE FLASHBACK ON;

Database altered.

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

Step:4 Add the Standby redo log files for standby side.

 set lines 200

col MEMBER format a65

select l.GROUP#,l.THREAD#,f.member,l.ARCHIVED,l.status,(bytes/1024/1024) "size(MB)" from v$log l,v$logfile f where f.group#=l.group# order by 1,2;

 

select l.GROUP#,l.THREAD#,f.member,l.ARCHIVED,l.status,(bytes/1024/1024) "size(MB)" from v$standby_log l,v$logfile f where f.group#=l.group# order by 1,2;

 

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

alter database add standby logfile group 4 '/u01/app/oracle/oradata/STBY/redo04.log' size 200m;

 alter database add standby logfile group 5 '/u01/app/oracle/oradata/STBY/redo05.log' size 200m;

alter database add standby logfile group 6 '/u01/app/oracle/oradata/STBY/redo06.log' size 200m;

alter database add standby logfile group 7 '/u01/app/oracle/oradata/STBY/redo07.log' size 200m;

select member from v$logfile;

Step:5 Take the backup of Primay database , pfile and password files on Primary database

 

create one directory for Primary side backups .

mkdir -p /u01/backup

cd /u01/backup

Create pfile from spfile on Primary database this we can use in the standby database side . there we need to modify some parameter as per the standby db .

create pfile='/u01backup/initprod.ora' from spfile;

copy the primary password file to backup location on Primary node this we need to copy to standby side oracle home / dbs location there we need to rename the sid as per the standby db.

cp /u01/app/oracle/product/19.0.0/db_home/dbs/initstby.ora .

rman target=/ nocatalog <<EOF

run {

    sql "alter system archive log current";

               allocate channel ch1 type disk format '/u01/backup/PROD_bkp_standby_%U';

               backup as compressed backupset database plus archivelog;

               backup current controlfile for standby;

               sql "alter system archive log current";

}

               exit;

EOF

 

Note : Tar the rman backup if you are using manual restore use this backup , if you are using active clone / active rman command no need of this backup .

tar -cvzf rmanbackup.tar.gz rman

tar -xvzf rmanbackup.tar.gz

 

Step:6  set the log archive config and other DG  parameters on primary database . see the below snap for all the parameters which i set on primary node side .

 ALTER SYSTEM SET log_archive_config='dg_config=(prod,stby)' SCOPE=both;

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

ALTER SYSTEM SET log_archive_dest_2='service=stby async valid_for=(online_logfiles,primary_role) db_unique_name=stby' SCOPE=both;

ALTER SYSTEM SET fal_client='prod' SCOPE=both;

ALTER SYSTEM SET fal_server='stby' SCOPE=both;

ALTER SYSTEM SET standby_file_management='AUTO' SCOPE=both;

ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE;

 

alter system set db_file_name_convert='/u01/app/oracle/oradata/STBY/','/u01/app/oracle/oradata/PROD' scope=spfile;

alter system set log_file_name_convert='/u01/app/oracle/oradata/STBY/','/u01/app/oracle/oradata/PROD' scope=spfile;

converts the filename of a

new datafile and logfile respectively on the primary database to a filename on the

standby database.

 

 

crosscheck the DR parameters on primary once

 

col name for a30

col value for a85

set lines 200 pagesize 200

 

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');

 

 

Set the log archive format = ‘%t_%s_%r.arc ,it determines the name of the archive log file it comes into role when archive log is enabled.

 

alter system set log_archive_format='%t_%s_%r.arc' scope=spfile;

 

Step:7 configure the log_archive_process to 6 ,it prevents to avoid any runtime overhead of invoking additional ARCn process,you can set the LOG_ARCHIVE_MAX_PROCESSES

 

alter system set log_archive_max_processes=6; ( optional ) default value is 4 

show parameter remote_login_passwordfile  ( default value )

 

Step:8 Configure standby database .

 

alter system set db_unique_name=stby scope=spfile;

select name from v$datafile;

 

show parameter audit

 

 

mkdir -p /u01/app/oracle/oradata/STBY/

mkdir -p /u01/app/oracle/oradata/STBY/pdbseed/

mkdir -p /u01/app/oracle/oradata/STBY/pdb         

mkdir -p /u01/app/oracle/oradata/STBY/adump

mkdir -p /u01/app/oracle/fast_recovery_area/

 

 

vi /u01/app/oracle/product/19.0.0/db_home/dbs/initstby.ora

*.audit_file_dest='/u01/app/oracle/admin/stby/adump'

*.audit_trail='db'

*.compatible='19.0.0.0.0'

*.control_files='/u01/app/oracle/oradata/stby/standby1.ctl'#Restore Controlfile

*.db_block_size=8192

*.db_file_name_convert='/u01/app/oracle/oradata/PROD/','/u01/app/oracle/oradata/STBY/'

*.db_name='prod'

*.db_recovery_file_dest_size=10737418240

*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area/'

*.db_unique_name='stby'

*.dg_broker_start=TRUE

*.diagnostic_dest='/u01/app/oracle/admin/stby/adump'

*.dispatchers='(PROTOCOL=TCP) (SERVICE=stbyXDB)'

*.enable_pluggable_database=true

*.fal_client='stby'

*.fal_server=''

*.local_listener='LISTENER_PROD'

*.log_archive_config='dg_config=(stby,prod)'

*.log_archive_dest_1='location=use_db_recovery_file_dest valid_for=(all_logfiles,all_roles) db_unique_name=stby'

*.log_archive_dest_2='service="prod"','ASYNC NOAFFIRM delay=0 optional compression=disable max_failure=0 reopen=300 db_unique_name="prod" net_timeout=30','valid_for=(online_logfile,all_roles)'

*.log_archive_dest_state_2='ENABLE'

*.log_archive_format='%t_%s_%r.dbf'

*.log_archive_max_processes=5

*.log_file_name_convert='/u01/app/oracle/oradata/PROD/','/u01/app/oracle/oradata/STBY/'

*.nls_language='AMERICAN'

*.nls_territory='AMERICA'

*.open_cursors=300

*.pga_aggregate_target=568m

*.processes=300

*.remote_login_passwordfile='EXCLUSIVE'

*.sga_target=1704m

*.standby_file_management='AUTO'

*.undo_tablespace='UNDOTBS1'

Step:9 Configure network files on both primary and standby databases.

 

Listener & TNS entry in the primary database. 

[oracle@primary admin]$ cat listener.ora

LISTENER =

      (DESCRIPTION =

           (ADDRESS = (PROTOCOL = TCP) (HOST = primary.localdomain) ( PORT = 1521))

     )

SID_LIST_LISTENER =

 (SID_LIST =

  (SID_DESC =

   (GLOBAL_DBNAME = prod)

   (ORACLE_HOME = /u01/app/oracle/product/19.0.0/db_home)

   (SID_NAME = prod)

 )

   (SID_DESC =

   (GLOBAL_DBNAME = prod_DGMGRL)

   (ORACLE_HOME = /u01/app/oracle/product/19.0.0/db_home)

   (SID_NAME = prod)

 )

)

ADR_BASE_LISTENER = /u01/app/oracle

[oracle@primary admin]$

 [oracle@primary admin]$ cat tnsnames.ora

# tnsnames.ora Network Configuration File: /u01/app/oracle/product/19.0.0/db_home/network/admin/tnsnames.ora

# Generated by Oracle configuration tools.

PROD =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = primary.localdomain)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = prod)

    )

  )

STBY =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = standby.localdomain)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = stby)

    )

  )

PDB =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = primary.localdomain)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = pdb)

    )

  )

LISTENER_PROD =

  (ADDRESS = (PROTOCOL = TCP)(HOST = primary.localdomain)(PORT = 1521))

[oracle@primary admin]$

Standby side Listener and TNS details : 

[oracle@standby admin]$ cat listener.ora

LISTENER =

      (DESCRIPTION =

           (ADDRESS = (PROTOCOL = TCP) (HOST = standby.localdomain) ( PORT = 1521))

     )

SID_LIST_LISTENER =

 (SID_LIST =

  (SID_DESC =

   (GLOBAL_DBNAME = stby)

   (ORACLE_HOME = /u01/app/oracle/product/19.0.0/db_home)

   (SID_NAME = stby)

 )

   (SID_DESC =

   (GLOBAL_DBNAME = stby_DGMGRL)

   (ORACLE_HOME = /u01/app/oracle/product/19.0.0/db_home)

   (SID_NAME = stby)

 )

)

ADR_BASE_LISTENER = /u01/app/oracle

[oracle@standby admin]$ cat tnsnames.ora

STBY =

 (DESCRIPTION =

  (ADDRESS = (PROTOCOL = TCP)(HOST = standby.localdomain)(PORT = 1521))

  (CONNECT_DATA =

   (SERVER = DEDICATED)

   (SERVICE_NAME = stby)

  )

 )

PROD =

 (DESCRIPTION =

  (ADDRESS = (PROTOCOL = TCP)(HOST = primary.localdomain)(PORT = 1521))

  (CONNECT_DATA =

   (SERVER = DEDICATED)

   (SERVICE_NAME = prod)

  )

 )

LISTENER_PROD =

(ADDRESS = (PROTOCOL = TCP)(HOST = standby.localdomain)(PORT = 1521))

[oracle@standby admin]$

tnsping should work on primary and standby side : 

=======================================

Step 10 :  update the parameter files on stand by and start standby database in nomount using newly created pfile.

 

 startup nomount pfile='initstby.ora';

 /u01/app/oracle/oradata/TEST,/u01/app/oracle/fast_recovery_area/TEST,/u01/app/oracle/oradata/TEST/pdb1

Fast recovery area location: /u01/app/oracle/fast_recovery_area/TEST

 

 rman target sys/Welcome_123@prod auxiliary sys/Welcome_123@stby

 

duplicate target database for standby from active database nofilenamecheck;

 

after restore completes enable the mrp process and then stop the db startup , it will start in read only mode .

select max(sequence#) from v$archived_log;

The same information can be obtained by checking the view v$archived_log, like this:

select sequence# from v$archived_log where registrar = 'RFS' and  applied = 'NO';

select sequence# , first_time,next_time from v$archived_log order by sequence#;

 

The actual lag that these non-applied logs causes, can be found with the query:

set lines 200

col source_db_unique_name format a10 

col value format a10

SELECT source_db_unique_name,name,value,unit,time_computed,datum_time FROM v$dataguard_stats WHERE name = 'apply lag';

 

select name,open_mode,database_role from v$database;

Note : start the mrp 

alter database recover managed standby database disconnect nodelay;

stop the mrp :

alter database recover managed standby database cancel;

DG commands : 

select name,open_mode,database_role from v$database;

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

select max(sequence#) from v$archived_log;

alter database recover managed standby database cancel;

alter database recover managed standby database disconnect from session;

alter database open read only;

alter database archivelog;

alter database open;

select FORCE_LOGGING,log_mode from v$database;

alter system switch logfile;

alter database flashback on;

show parameter recovery;

select group#, thread#, bytes/1024/1024 mb from v$log;

alter database add standby logfile thread 1 size 200m;

select member from v$logfile where type='STANDBY';

alter system set db_unique_name=prod scope=both;

set linesize 500 pages 100

col name format a30

col value format a100

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','db_file_name_convert', 'log_file_name_convert', 'standby_file_management');

alter system set STANDBY_FILE_MANAGEMENT=AUTO scope = both;

alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(prod,stby)' scope = both;

alter system set LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES)

DB_UNIQUE_NAME=prod' scope = both;

or 

alter system set LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST' scope = both;

ALTER SYSTEM SET log_archive_dest_2='service=stby async valid_for=(online_logfiles,primary_role) db_unique_name=stby' SCOPE=both; 

fal_client  no longer needed in 19C 

alter system set FAL_SERVER='stby' scope = both;

alter system set db_unique_name=prod scope=both;

Thanks,

Srini