Steps to Create Physical Standby Database in Oracle

Steps to Create Physical Standby Database in Oracle 

Introduction

Here Steps to Create Physical Standby Database in Oracle  will build a physical standby setup. Our assumption is primary database is already up and running fine And ORACLE_HOME is installed on standby server.

SERVER ----->PRIMARY---->primary_host

DB_UNIQUE_NAME---->PRIMARY---->PRODDB

SERVER ----->STANDBY---->standby_host

DB_UNIQUE_NAME---->STANDBY---->PRODSTAN

PRIMARY

Make sure database is archive log mode and enable force logging

PRIMARY > select log_mode from v$database;

 

LOG_MODE

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

ARCHIVELOG

 

 Enable force loggin:

 FORCE LOGGING is required, every changes in database will go to redo logs, which will be applied on standby system

 

SQL > ALTER DATABASE FORCE LOGGING;

 

Database altered.

[PRIMARY] Add standby logfiles;

 In standby setup, archives log from primary shipped to standby and applied there. But if the primary database crashes, then the online redo logs will stay in primary side. As these logs hasn’t been archives, means those won’t be applied to standby, Which results in data loss. So if we add standby logfile, Data guard writes the Primary’s current redo log to a “standby redo log” allowing complete recovery in case of Primary site is lost.

SQL > select GROUP#,BYTES/1024/1024,thread# from v$log;

 

    GROUP# BYTES/1024/1024    THREAD#

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

         1             138          1

         2             138          1

         3             138          1

         4             138          1

         5             138          1

         6             138          1

         7             138          1

         8             138          1

 

8 rows selected.

SQL >col member for a45

SQL > set pagesize 200

SQL > set lines 200

SQL >select GROUP#,MEMBER from v$logfile;

    GROUP# MEMBER

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

         1 /u01/redo1/PROD/redo_01a.dbf

         1 /u01/redo2/PROD/redo_01b.dbf

         2 /u01/redo1/PROD/redo_02a.dbf

         2 /u01/redo2/PROD/redo_02b.dbf

         3 /u01/redo1/PROD/redo_03a.dbf

         3 /u01/redo2/PROD/redo_03a.dbf

         4 /u01/redo1/PROD/redo_04a.dbf

         4 /u01/redo2/PROD/redo_04a.dbf

         5 /u01/redo1/PROD/redo_05a.dbf

         5 /u01/redo2/PROD/redo_05a.dbf

         6 /u01/redo1/PROD/redo_06a.dbf

         6 /u01/redo2/PROD/redo_06a.dbf

         7 /u01/redo1/PROD/redo_07a.dbf

         7 /u01/redo2/PROD/redo_07b.dbf

         8 /u01/redo1/PROD/redo_08a.dbf

         8 /u01/redo2/PROD/redo_08b.dbf

Dynamics query to generate the sqls.

 

SQL > select 'alter database add standby logfile '''||

regexp_substr(MEMBER,'/.+/')||'prodstan_'||

regexp_replace(member,regexp_substr(MEMBER,'/.+/'),'')||

''' size '||bytes||';' "Create Standby redo"

from v$logfile lf , v$log l

where l.group# = lf.group#

/  2    3    4    5    6    7

Create Standby redo

alter database add standby logfile '/u01/redo1/PROD/prodstan_redo_01a.dbf' size 124217729;

alter database add standby logfile '/u01/redo2/PROD/prodstan_redo_01b.dbf' size 124217729;

alter database add standby logfile '/u01/redo1/PROD/prodstan_redo_02a.dbf' size 124217729;

alter database add standby logfile '/u01/redo2/PROD/prodstan_redo_02b.dbf' size 124217729;

alter database add standby logfile '/u01/redo1/PROD/prodstan_redo_03a.dbf' size 124217729;

alter database add standby logfile '/u01/redo2/PROD/prodstan_redo_03a.dbf' size 124217729;

alter database add standby logfile '/u01/redo1/PROD/prodstan_redo_04a.dbf' size 124217729;

alter database add standby logfile '/u01/redo2/PROD/prodstan_redo_04a.dbf' size 124217729;

alter database add standby logfile '/u01/redo1/PROD/prodstan_redo_05a.dbf' size 124217729;

alter database add standby logfile '/u01/redo2/PROD/prodstan_redo_05a.dbf' size 124217729;

alter database add standby logfile '/u01/redo1/PROD/prodstan_redo_06a.dbf' size 124217729;

alter database add standby logfile '/u01/redo2/PROD/prodstan_redo_06a.dbf' size 124217729;

alter database add standby logfile '/u01/redo1/PROD/prodstan_redo_07a.dbf' size 124217729;

alter database add standby logfile '/u01/redo2/PROD/prodstan_redo_07b.dbf' size 124217729;

alter database add standby logfile '/u01/redo1/PROD/prodstan_redo_08a.dbf' size 124217729;

alter database add standby logfile '/u01/redo2/PROD/prodstan_redo_08b.dbf' size 124217729;

 

16 rows selected.

SQL> alter database add standby logfile '/u01/redo1/PROD/prodstan_redo_01a.dbf' size 124217729;

alter database add standby logfile '/u01/redo2/PROD/prodstan_redo_01b.dbf' size 124217729;

alter database add standby logfile '/u01/redo1/PROD/prodstan_redo_02a.dbf' size 124217729;

alter database add standby logfile '/u01/redo2/PROD/prodstan_redo_02b.dbf' size 124217729;

alter database add standby logfile '/u01/redo1/PROD/prodstan_redo_03a.dbf' size 124217729;

alter database add standby logfile '/u01/redo2/PROD/prodstan_redo_03a.dbf' size 124217729;

alter database add standby logfile '/u01/redo1/PROD/prodstan_redo_04a.dbf' size 124217729;

alter database add standby logfile '/u01/redo2/PROD/prodstan_redo_04a.dbf' size 124217729;

alter database add standby logfile '/u01/redo1/PROD/prodstan_redo_05a.dbf' size 124217729;

alter database add standby logfile '/u01/redo2/PROD/prodstan_redo_05a.dbf' size 124217729;

alter database add standby logfile '/u01/redo1/PROD/prodstan_redo_06a.dbf' size 124217729;

alter database add standby logfile '/u01/redo2/PROD/prodstan_redo_06a.dbf' size 124217729;

alter database add standby logfile '/u01/redo1/PROD/prodstan_redo_07a.dbf' size 134217728;

alter database add standby logfile '/u01/redo2/PROD/prodstan_redo_07b.dbf' size 134217728;

alter database add standby logfile '/u01/redo1/PROD/prodstan_redo_08a.dbf' size 134217728;

alter database add standby logfile '/u01/redo2/PROD/prodstan_redo_08b.dbf' size 134217728;

 

 

 

database altered

[PRIMARY] create password file

cd $ORACLE_HOME/dbs

 

orapwd file=orapwPRODDB password=prod123 entries=30

[PRIMARY]Configure listener and tns entries as below.

 

cat tnsnames.ora

PRODDB =

(DESCRIPTION = (ADDRESS = (PROTOCOL = tcp)(HOST = primary-host )(PORT = 1571)) (CONNECT_DATA = (SID = PRODDB)))

PRODSTAN =

(DESCRIPTION = (ADDRESS = (PROTOCOL = tcp)(HOST = standby-host )(PORT = 1571)) (CONNECT_DATA = (SID = PRODSTAN)))

 

 

cat listener.ora

 

LISTENER_PRODDB =

   (address_list =

    (address =

           (protocol = tcp)

           (host     = primary-host)

           (port     = 1571)

      )

)

SID_LIST_LISTENER_PRODDB =

  (SID_LIST =

    (SID_DESC =

      (GLOBAL_DBNAME = PRODDB)

      (ORACLE_HOME = /u01/app/oracle/product/11.2.0)

      (SID_NAME = PRODDB)

    )

)

 

lsnrctl start LISTENER_PRODDB

 

[PRIMARY]Do below changes in spfile of primary

 log_archive_config parameter enables sending and receiving of archive logs to remote dest

DG_CONFIG - need to specify the list of db_unique_name of both primary and standby.

alter system set log_archive_config='DG_CONFIG=(PRODDB,PRODSTAN)' scope=BOTH;

 

--- archive destination in local(primary server)

alter system set log_archive_dest_1='LOCATION=/u01/arch/PROD VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=PRODDB'  scope=BOTH;

 

---log_archive_dest_2 parameter defines the archive destination of standby

--- ASYNC - This is used for maximum performance mode(DEFAULT) .Means redo logs generated in primary need not be shipped/applied in standby

alter system set log_archive_dest_2='SERVICE=PRODSTAN LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=PRODSTAN' scope=BOTH;

 

alter system set log_archive_dest_state_2='ENABLE'  scope=BOTH;

alter system set log_archive_dest_state_1='ENABLE'  scope=BOTH;

 

 Tns entry name for primary

alter system set fal_client='PRODDB'  scope=BOTH;

 

--- tns entry name for primary -- i.e It will fetch the archives from standby .

alter system set fal_server='PRODSTAN'  scope=BOTH;

[PRIMARY]So after the changes the spfile in primary will look as below.

 strings spfilePRODDB.ora

 

*.audit_trail='DB'

*.compatible='11.2.0'

*.control_files='/u01/data1/PROD/control1.ora','/u01/data2/PROD/control2.ora','/u01/undo/PROD/control3.ora'

*.core_dump_dest='/u01/admin/PROD/cdump'

*.db_block_size=8193

*.db_cache_size=3009M

*.db_file_multiblock_read_count=63

*.db_keep_cache_size=512M

*.db_name='PRODDB'

*.db_unique_name='PRODDB'

*.diagnostic_dest='/u01/admin/PROD/diag'

*.dml_locks=100

*.fal_client='PRODDB'

*.fal_server='PRODSTAN'

*.instance_name='PRODDB'

*.job_queue_processes=2

*.log_archive_config='DG_CONFIG=(PRODDB,PRODSTAN)'

*.log_archive_dest_1='LOCATION=/u01/arch/PROD VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=PRODDB'

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

*.log_archive_dest_state_2='ENABLE'

*.log_archive_format='arch_PROD_%t_%s_%r.arc'

*.log_buffer=31367168# log buffer update

*.log_checkpoint_interval=300000

*.max_dump_file_size='unlimited'

*.nls_date_format='DD-MON-RR'

*.os_authent_prefix='NULL'

*.pga_aggregate_target=1500M

*.processes=600

*.remote_dependencies_mode='SIGNATURE'

*.remote_login_passwordfile='EXCLUSIVE'

*.resource_limit=TRUE

*.sga_max_size=5152M# internally adjusted

*.shared_pool_size=512M

*.timed_statistics=TRUE

*.undo_management='AUTO'

*.undo_retention=30000

*.undo_tablespace='UNDO'

STANDBY

[STANDBY ] create directory structure same as that point

mkdir -p /u01/data1/PROD/

mkdir -p /u01/data2/PROD/

mkdir -p /u01/arch/PROD

mkdir -p /u01/undo/PROD

mkdir -p /u01/admin/PROD/cdump

mkdir -p /u01/admin/PROD/diag

[STANDBY]pfile and password file creation in $ORACLE_HOME/dbs location:

 

 

-----copy the password file from production and rename to standby name.

cd $ORACLE_HOME/dbs

scp oracle@primary-host:/u01/app/oracle/product/11.2.0/dbs/orapwPRODDB orapwPRODSTAN

 

-----create an init file with minimum parameter as below

 

vi initPRODSTAN.ora

 

DB_NAME=PRODDB

DB_UNIQUE_NAME=PRODSTAN

control_files='/u01/data1/PROD/control1.ora','/u01/data2/PROD/control2.ora','/u01/undo/PROD/control3.ora'

fal_client='PRODSTAN'

fal_server='PRODDB'

db_file_name_convert='/u01/data1/PROD/','/u01/data1/PROD/','/u01/data2/PROD/','/u01/data2/PROD/'

log_file_name_convert = '/u01/redo1/PROD/,'/u01/redo1/PROD/','/u01/redo2/PROD/','/u01/redo2/PROD/'

standby_file_management='AUTO'

log_archive_config='DG_CONFIG=(PRODDB,PRODSTAN)'

log_archive_dest_1='LOCATION=/u01/arch/PRODDB VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=PRODSTAN'

log_archive_dest_2='service=PRODDB ASYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=PRODDB'

 [STANDBY] Listener and tns setup  

cat tnsnames.ora

PRODDB =

(DESCRIPTION = (ADDRESS = (PROTOCOL = tcp)(HOST = primary-host )(PORT = 1571)) (CONNECT_DATA = (SID = PRODDB)))

PRODSTAN =

(DESCRIPTION = (ADDRESS = (PROTOCOL = tcp)(HOST = standby-host )(PORT = 1571)) (CONNECT_DATA = (SID = PRODSTAN)))

 

cat listener.ora

 

LISTENER_PRODSTAN =

   (address_list =

    (address =

           (protocol = tcp)

           (host     = standby-host)

           (port     = 1571)

      )

)

SID_LIST_LISTENER_PRODSTAN =

  (SID_LIST =

    (SID_DESC =

      (GLOBAL_DBNAME = PRODSTAN)

      (ORACLE_HOME = /u01/app/oracle/product/11.2.0)

      (SID_NAME = PRODSTAN)

    )

)

 

lsnrctl start LISTENER_PRODSTAN

[STANDBY]start the standby database in nomount state:

SQL > startup nomount

ORACLE instance started.

 

Total System Global Area 5415597569 bytes

Fixed Size                  3170303 bytes

Variable Size             805970240 bytes

Database Buffers         3502926846 bytes

Redo Buffers                3530176 bytes

[STANDBY]Now check the connectivity between primary and standby using sys password

 

 

-----In primary:

 

$ sqlplus sys/prod123@PRODSTAN as sysdba

 

SQL*Plus: Release 11.2.0.3.0 Production on Sun May 21 15:17:56 2020

 

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

 

 

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

 

  IN STANDBY:

 

sqlplus sys/prod123@PRODDB as sysdba

 

SQL*Plus: Release 11.2.0.3.0 Production on Sun May 21 15:17:56 2020

 

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

 

 

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

 

 STANDBY:

[STANDBY]Now connect to rman and run the duplicate command as mentioned below.

 

rman target sys/prod123@PRODDB auxiliary sys/prod123@PRODSTAN

 

 

Recovery Manager: Release 11.2.0.3.0 - Production on Tue June 16 08:42:46 2020

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

connected to target database: PRODDB (DBID=728741429)

connected to auxiliary database: PRODDB (not mounted)

 

rman >

 

run

{

allocate channel primy1 type disk;

allocate channel primy2 type disk;

allocate channel primy3 type disk;

allocate channel primy4 type disk;

allocate channel primy5 type disk;

allocate channel primy6 type disk;

allocate channel primy7 type disk;

allocate auxiliary channel stanby1 type disk;

allocate auxiliary channel stanby2 type disk;

allocate auxiliary channel stanby3 type disk;

allocate auxiliary channel stanby4 type disk;

allocate auxiliary channel stanby5 type disk;

allocate auxiliary channel stanby6 type disk;

allocate auxiliary channel stanby7 type disk;

allocate auxiliary channel stanby8 type disk;

 

DUPLICATE TARGET DATABASE

FOR STANDBY

FROM ACTIVE DATABASE  nofilenamecheck;

}

 

[STANDBY]Once duplication is done. Start the recovery

SQL > ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

 

DATABASE ALTERED.

 

SQL > select process,status,sequence# from v$managed_standby:

If you wish you open the standby with read only mode as active dataguard, then use below one.

sql> alter datbase open;

 

sql> alter database recover managed standby database using current logfile disconnect from session;

 

 The physcial standby setup is ready for use

Note: Info on Physical standby datbase in oracle it may be differ in your environment like production,testing,development and directory structures and na,ming conventions etc 

THANKS FOR VIEWING MY BLOG FOR MORE UPDATES FOLLOW ME OR SUBSCRIBE ME

Dataguard Broker Configuration DG Broker in Oracle 12c

 Dataguard Broker Configuration DG Broker in Oracle 12c 

Introduction

Dataguard BrokerDG Broker in Oracle 12c  is used to automate monitoring and controlling standby setups. it is very useful when we have multiple standby systems

In this DG Broker in Oracle 12c blog we are going to expalin how to enable db broker configuration in Oracle 12c in existing standby setup. before going to start the process dg broker make sure standby setup  is ready

Prerequisites

PRIMARY UNIQUE DB NAME---------->PROCDB

STANDBY UNIQUE DB NAME------------>PROCSTAN

Now let us start the process

Step 1 : Set dg_broker_start to true [ON BOTH PRIMARY DB  AND STANDBY DB]

PROCDB> alter system set dg_broker_start=true sid='*';

 

System altered.

 

 

PROCSTAN> alter system set dg_broker_start=true sid='*';

 

System altered.

Step 2:  Add primary db configuration[ ON PRIMARY]

$dgmgrl

DGMGRL for Solaris: Release 12.2.0.1.0 - Production on Sun May 3 12:22:13 2020

 

Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.

 

Welcome to DGMGRL, type "help" for information.

DGMGRL> connect sysdg

Password:

Connected to "PROCDB"

Connected as SYSDG.

 

DGMGRL> CREATE CONFIGURATION 'procdb_dg' AS PRIMARY DATABASE IS 'PROCDB' CONNECT IDENTIFIER IS PROCDB;

Configuration "procdb_dg" created with primary database "PROCDB"

 

 

DGMGRL> SHOW CONFIGURATION

 

Configuration - procdb_dg

 

  Protection Mode: MaxPerformance

  Members:

  PROCDB - Primary database

 

Fast-Start Failover: DISABLED

 

Configuration Status:

DISABLED

Step 3: Add standby Configuration[ ON PRIMARY]

DGMGRL>  add database PROCSTAN as connect identifier is PROCSTAN;

Database "procstan" added

DGMGRL> show configuration

 

Configuration - procdb_dg

 

  Protection Mode: MaxPerformance

  Members:

  PROCDB    - Primary database

    procstan - Physical standby database

 

Fast-Start Failover: DISABLED

 

Configuration Status:

DISABLED

If you are getting error as ORA-16698: member has a LOG_ARCHIVE_DEST_n parameter with SERVICE attribute set, then

To fix this error, disable any log_archive_dest_n parameter set on standby( excluding log_archive_dest_1)

On standby:

SQL> alter system set log_Archive_dest_2=”;

System altered.

Step4: Enable configuration [ON PRIMARY]

DGMGRL> show configuration

 

Configuration - procdb_dg

 

  Protection Mode: MaxPerformance

  Members:

  PROCDB    - Primary database

    procstan - Physical standby database

      Warning: ORA-16792: configurable property value is inconsistent with member setting

 

Fast-Start Failover: DISABLED

 

Configuration Status:

WARNING   (status updated 57 seconds ago)

Now lets trouble shoot the ORA-16792 error, we are getting error standby database

Check the properties for inconsistency

 

DGMGRL> show database 'procstan' InconsistentProperties;

INCONSISTENT PROPERTIES

   INSTANCE_NAME        PROPERTY_NAME         MEMORY_VALUE         SPFILE_VALUE         BROKER_VALUE

         PROCSTAN  StandbyFileManagement               MANUAL                                    MANUAL

         PROCSTAN     ArchiveLagTarget                    0                                         0

         PROCSTAN   LogArchiveMaxProcesses                    4                                         4

         PROCSTAN   LogArchiveMinSucceedDest                    1                                         1

         PROCSTAN   DataGuardSyncLatency                    0                                         0

         PROCSTAN      LogArchiveTrace                    0            (missing)                    0

         PROCSTAN     LogArchiveFormat         %t_%s_%r.dbf            (missing)         %t_%s_%r.dbf

 

Here we can see one critical parameter StandbyFileManagement is set to be MANUAL. to fix the error set to auto and try again

SQL> alter system set standby_file_management=AUTO scope=both;

 

System altered.

Enable the configuration again

DGMGRL> enable configuration

Enabled.

 

DGMGRL> show database 'procstan' InconsistentProperties;

INCONSISTENT PROPERTIES

   INSTANCE_NAME        PROPERTY_NAME         MEMORY_VALUE         SPFILE_VALUE         BROKER_VALUE

DGMGRL> show configuration

 

Configuration - procdb_dg

 

  Protection Mode: MaxPerformance

  Members:

  PROCDB    - Primary database

    procstan - Physical standby database

 

Fast-Start Failover: DISABLED

 

Configuration Status:

SUCCESS   (status updated 46 seconds ago)

Dg broker setup is ready and we can do switchover and failover using dgmgrl  easily

 

Note : Info on Databroker configuration it may  be differ in your environment like production,testing,development and etc

THANKS FOR VIEWING MY BLOG FOR MORE UPDATES FOLLOW ME OR SUBSCRIBE ME

Enable or Disable in Archive log mode in Oracle Database

 Enable or Disable in Archive log mode in Oracle Database

Introduction

Two types of logging mode in oracle database

1.Archivelog mode: In this Archivelog mode after the online redo logs are filled , it will move to the archive location,archivelog mode you can put the database in for creating a backup of all transactions occured in the database so that you can recover at any point of time 

2.Noarchivelog mode:In this Noarchivelog mode Filled  online redo logs wont be accepted,archives are insted they will be overwritten, In this mode absence of archivelog and database not be recoverd at any point of time 

Enable archive log mode

SQL > select name,log_mode from v$database;

NAME      LOG_MODE

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

PROD      NOARCHIVELOG

 

SQL > archive log list

Database log mode              No Archive Mode

Automatic archival             Disbled

Archive destination            /chaitanya/arch/PROD

Oldest online log sequence     206506

Next log sequence to archive   206512

Current log sequence           206512

 

 make sure db is running in spfile

SQL > alter system set log_archive_dest_1='LOCATION=/chaitanya/arch/PROD' scope=spfile;

database altered.

 

SQL >shutdown  immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

 

SQL > startup mount

ORACLE instance started.

Total System Global Area 5415597568 bytes

Fixed Size                  2170304 bytes

Variable Size             805970240 bytes

Database Buffers         6502926848 bytes

Redo Buffers                3530176 bytes

Database mounted.

 

SQL >alter database archivelog;

 

database altered.

 

SQL >alter database open;

 

database altered.

 

SQL >select name,log_mode from v$database;

 

NAME      LOG_MODE

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

PROD      ARCHIVELOG

 

SQL >archive log list

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            /chaitanya/arch/PROD

Oldest online log sequence     206506

Next log sequence to archive   206512

Current log sequence           206512

 

Disable archivelog mode

SQL >select name,log_mode from v$database;

 

NAME      LOG_MODE

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

PROD      ARCHIVELOG

 

SQL > archive log list

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            /chaitanya/arch/PROD

Oldest online log sequence     206506

Next log sequence to archive   206512

Current log sequence           206512

 

 

SQL > shutdown  immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

 

SQL > startup mount

ORACLE instance started.

Total System Global Area 5415597568 bytes

Fixed Size                  2170304 bytes

Variable Size             805970240 bytes

Database Buffers         6502926848 bytes

Redo Buffers                3530176 bytes

Database mounted.

 

SQL >alter database noarchivelog;

 

database altered.

 

SQL >alter database open;

 

database altered.

 

 

SQL > select name,log_mode from v$database;

 

NAME      LOG_MODE

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

PROD      NOARCHIVELOG

 

SQL > archive log list

Database log mode              No Archive Mode

Automatic archival             Disbled

Archive destination            /chaitanya/arch/PROD

Oldest online log sequence     206506

Next log sequence to archive   206512

Current log sequence           206512

Info: Info on enable or disable archivelog mode it may be differ in your environment like production,testing,develoment etc

THANKS FOR VIEWING MY BLOG FOR MORE UPDATES FOLLOW ME OR SUBSCRIBE ME

Enable or Disable Flashback Technology in Oracle

 Enable or Disable Flashback Technology in Oracle

Introduction

Using Flashback Technology  we can restore the database and dropped users,and tables,schemas  in oracle we will flashback the database to past when the database ,user,table,schema is available at the time of dropped database before.

Here in this blog i am going to expalin how to enable or disable by using Flashback technology in oracle

Let us start the process

Enable Flashback

The Database must be in archive log mode

Here i am showing how to enable archive log mode

SQL > select name,log_mode from v$database;

 

NAME      LOG_MODE

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

PROD      NOARCHIVELOG

 

SQL > archive log list

Database log mode              No Archive Mode

Automatic archival             Disbled

Archive destination            /chaitanya/arch/PROD

Oldest online log sequence     206506

Next log sequence to archive   206512

Current log sequence           206512

 

 make sure db is running in spfile

SQL > alter system set log_archive_dest_1='LOCATION=/chaitanya/arch/PROD' scope=spfile;

database altered.

 

SQL >shutdown  immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

 

SQL > startup mount

ORACLE instance started.

Total System Global Area 5415597568 bytes

Fixed Size                  2170304 bytes

Variable Size             805970240 bytes

Database Buffers         4502926848 bytes

Redo Buffers                3530176 bytes

Database mounted.

 

SQL >alter database archivelog;

 

database altered.

 

SQL >alter database open;

 

database altered.

 

SQL >select name,log_mode from v$database;

 

NAME      LOG_MODE

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

PROD      ARCHIVELOG

 

SQL >archive log list

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            /chaitanya/arch/PROD

Oldest online log sequence     206506

Next log sequence to archive   206512

Current log sequence           206512

 

To enable flashback we need to set two parameters 

DB_RECOVERY_FILE_DEST

DB_RECOVERY_FILE_DEST_SIZE

 

SQL> alter system set db_recovery_file_dest='/home/oracle/prod';

 

System altered.

 

SQL> alter system set db_recovery_file_dest_size=12g;

 

System altered.

 

SQL> show parameter db_recovery_file

 

NAME TYPE VALUE

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

db_recovery_file_dest string /home/oracle/prod

db_recovery_file_dest_size big integer 10G

 

Turn on Flashback

SQL> select flashback_on from v$database;

 

FLASHBACK_ON

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

NO

 

SQL> alter database flashback on;

 

Database altered.

 

SQL> select flashback_on from v$database;

 

FLASHBACK_ON

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

YES

Disable Flashback

 

SQL> select flashback_on from v$database;

 

FLASHBACK_ON

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

YES

 

SQL> alter database flashback off;

 

Database altered.

 

 

SQL> select flashback_on from v$database;

 

FLASHBACK_ON

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

NO

 

Note : if you are using 10g or above versions then we need to enable or disable in flashback mode in mount stage

shutdown immediate

startup mount

alter database flashback off;

alter database open

Note : Info on enable or disable flashback technology it may be differ in your environment like production,development,testing and directories etc

THANKS FOR VIEWING MY BLOG FOR MORE UPDATES FOLLOW ME OR SUBSCRIBE ME

Oracle Database Security Assessment Tool (DBSAT)

 Oracle Database Security Assessment Tool (DBSAT) 

Introduction:

The oracle Database security assesment tool it is known as DBSAT tool is used to scan the complete database scan and provide report security configuration and vulnerability list

DBSAT has two components

The Collector: Collector is to collect all information from the database by running SQL and OS aginst database

The Reporter : Reporter it will Analyze the database and gives the complete report for the database

Let us Start the Process

Step 1.Download the DBSAT TOOL from oracle support website in category Oracle Database security Asssement tool

Step 2.Copy the DBSAT tool and unzip it

unzip dbsat.zip

Archive:  dbsat.zip

  inflating: dbsat

  inflating: dbsat.bat

  inflating: sat_reporter.py

  inflating: sat_analysis.py

  inflating: sat_collector.sql

  inflating: xlsxwriter/app.py

  inflating: xlsxwriter/chart_area.py

  inflating: xlsxwriter/chart_bar.py

  inflating: xlsxwriter/chart_column.py

  inflating: xlsxwriter/chart_doughnut.py

  inflating: xlsxwriter/chart_line.py

  inflating: xlsxwriter/chart_pie.py

  inflating: xlsxwriter/chart.py

  inflating: xlsxwriter/chart_radar.py

  inflating: xlsxwriter/chart_scatter.py

  inflating: xlsxwriter/chartsheet.py

  inflating: xlsxwriter/chart_stock.py

  inflating: xlsxwriter/comments.py

  inflating: xlsxwriter/compat_collections.py

  inflating: xlsxwriter/compatibility.py

  inflating: xlsxwriter/contenttypes.py

  inflating: xlsxwriter/core.py

  inflating: xlsxwriter/drawing.py

  inflating: xlsxwriter/format.py

  inflating: xlsxwriter/__init__.py

  inflating: xlsxwriter/packager.py

  inflating: xlsxwriter/relationships.py

  inflating: xlsxwriter/shape.py

  inflating: xlsxwriter/sharedstrings.py

  inflating: xlsxwriter/styles.py

  inflating: xlsxwriter/table.py

  inflating: xlsxwriter/theme.py

  inflating: xlsxwriter/utility.py

  inflating: xlsxwriter/vml.py

  inflating: xlsxwriter/workbook.py

  inflating: xlsxwriter/worksheet.py

  inflating: xlsxwriter/xmlwriter.py

  inflating: xlsxwriter/LICENSE.txt

Step 3. Now use the Collect Command before that make sure to set proper ORACLE_HOME , ORACLE_SID and PATH before running this command

             ./dbsat collect {username/password} {DESTINATION_PATH}

 

./dbsat collect system/oracle /export/home/oracle/chaitanya

 

This tool is intended to assist in you in identifying potential

vulnerabilities in your system, but you are solely responsible for

your system and the effect and results of the execution of this tool

(including, without limitation, any damage or data loss). Further,

the output generated by this tool may include potentially sensitive

system configuration data and information that could be used by a

skilled attacker to penetrate your system. You are solely responsible

for ensuring that the output of this tool, including any generated

reports, is handled in accordance with your company's policies.

 

Connecting to the target Oracle database...

 

 

SQL*Plus: Release 12.1.0.2.0 Production on Tue Aug 25 15:30:03 2020

 

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

 

Last Successful login time: Tue Aug 10 2020 13:16:12 +03:00

 

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

 

Database Security Assessment Tool version 1.0.2 (October 2016)

Setup complete.

SQL queries complete.

/oracle/app/oracle/product/12.1.0/dbhome/bin/osdbagrp -r

Usage: /oracle/app/oracle/product/12.1.0/dbhome/bin/osdbagrp -a | -d | -o | -b | -g | -k

Warning: Exit status 256 from OS rule: sysrac_group

OS commands complete.

Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

DBSAT Collector completed successfully.

 

Calling /oracle/app/oracle/product/12.1.0/dbhome/bin/zip to encrypt chaitanya.json...

 

Enter password:

Verify password:

  adding: chaitanya.json (deflated 86%)

zip completed successfully.

This will generate file called chaitanya.zip

 

Step 4. Generate  the Report

           ./dbsat report {DESTINATION_FILE}

./dbsat  report /export/home/oracle/audit_sec

This tool is intended to assist in you in identifying potential

vulnerabilities in your system, but you are solely responsible for

your system and the effect and results of the execution of this tool

(including, without limitation, any damage or data loss). Further,

the output generated by this tool may include potentially sensitive

system configuration data and information that could be used by a

skilled attacker to penetrate your system. You are solely responsible

for ensuring that the output of this tool, including any generated

reports, is handled in accordance with your company's policies.

 

Archive:  bsstdba.zip

[bsstdba.zip] bsstdba.json password:

  inflating: bsstdba.json

Database Security Assessment Tool version 1.0.2 (October 2016)

DBSAT Reporter ran successfully.

 

Calling /usr/bin/zip to encrypt the generated reports...

 

Enter password:

Verify password:

  adding: chaitanya.txt (deflated 78%)

  adding: chaitanya.html (deflated 84%)

  adding: chaitanya.xlsx (deflated 3%)

zip completed successfully.

 

audit_sec_report.zip file will be generated

Step 5.  The report will  looks like:

While unzipping the file, it will ask for the password, (pass the same which we used while generating the report)

/export/home/oracle# unzip audit_sec_report.zip

Archive:  bsstdba_report.zip

[bsstdba_report.zip] chaitanya.txt password:

  inflating:chaitanya.txt

  inflating: chaitanya.html

  inflating: chaitanya.xlsx

Note: Info on DBSAT tool it may be differ on your environment like production,testing ,development etc and naming conventions and directory structure

THANKS FOR VIEWING MY BLOG FOR MORE UPDATES FOLLOW ME OR SUBSCRIBE ME

Oracle Dba Online Test (MCQ) Advanced Level ASM

Oracle Database 19c Features

 Oracle Database 19c Features

Introduction

Oracle Database 19c is the long term release of the Oracle Database 12c and 18c family of products,it is available on all platformsWindows,Linux,Solaris,HP/UX and AIX as well as the Oracle cloud. Oracle Database 19c offers customers the best performance,scalibility,reliability, and security for all their operational and analytical workloads

Installation

Rpm based Installation install oracle 19c datbase using RPM method

Simlified image based installation of client as well

Upgrades

Auto Upgrade Utility for oracle Database

Docker Container for oracle 19c

Dryrun mode for Gridsetup in clusterware installation

General

Clear Flashback logs from time to time

Passwords removed from user accounts (default accounts)

Flush Metadata Cache for passwords

Multi-model partitioning with hybrid partioning allowing some partitions in database and some as external partitions even in HDFS

New ALTER SYSTEM  statement clause FLUSH PASSWORDFILE_METADATA_CACHE

Hybrid  Partitioned tables - to integerate internal partitions and external partitions  into a single partition table. partitions to reside in both oracle database segments and in external files and sources

Schema-only accounts -Passwords Removed from oracle database accounts  

Database Performance

SQL Quarantine - using Oracle's Resource manager tool is a great way to make sure SQL statements dont become resource hogs and slow down database performance everyone,if a system asks for more system resorces than the DBA allows ,Resources manager kills it,However in existing versions of oracle database,nothing stops users from executing problematic SQL statements again. In oracle 19C ,Resource manager can automatically quarantine the statements, user try to issue once again it wont be run at all

Automatic Indexing

This new feature puts oarcale automation capabilities to work.if oracle 19c thinks a database table would be benifit from an index,the system will automatically create the index and initially mark it as invisible so it cant be used .oracle 19c will then run SQL statements  from your application to see if the index improves query execution you can control this feature  with DBMS_AUTO_INDEX< a new PL?SQL package that's included in 19c

SQL Statement Diagnosability 

SQL Statement Diagnosability with SQL Advisor repair and SQL Test case for procedures

Automatic Database Diagnostic Monitor(ADDM)

ADDM supports for pluggable Database (PDB'S)

Realtime  Statastics For DML Operations

Oracle database 19c intoduces real time statastics which extend online support to conventional DML statements

Automatic Flashback of Standby Database

in prior versions DBA's wanted touse oracle flashback features to return  aprimary database to previous state,In oracle 19c ,a DBA can put the standby database in MOUNT mode with no managed recovery and then flashback the primary one ,the standby will aslo be reverted,thus keeping it in sync with the primary Statistics Collection on custom frequency automatically From 19c database onwards ,High frequency automatic optimizer statastics collection complements the standard statasticscollection job

DataPump

Oracle data pump test mode for transportable tablespace(TTS)

Oracle data pump allows tablespace to stay read -only during TTS import

Oracle data pump import supports more object store credentials

Oracle data pump ability to exclude ENCRYPTION clause on import-new transform  parameter OMIT_ENCRYPTION_CLAUSE

Oracle data pump support for resource usage limitations_new parameter MAX_DATAPUMP_PARALLEL_PER_JOB

Oracle data pump prevents inadvertent use of protected roles-new ENABLE_SECURE_ROLES parameter is available

Oracle data pump loads partitioned table data one operation-GROUP_PARTITION_TABLE_DATA,  new value for the import DATA_OPTIONS Command line paramaeter

Pluggable Databases

Create Duplicate of an oracle database create duplicatedb command , in DBCA silent mode

Ability to relocate a PDB to another CDB using DBCA in silent mode

Create a PDB by cloning a remote PDB using DBCA in silent mode

ADDM Analysis at PDB level

Data Guard

Replicate Restore points from primary to standby

Dynamically change  fast-start- failover (FSFO) target standby database to another standby database in the target list without disabling FSFO

Re-creation of broker configuration

Propagate restore points from primary to standby site

DML redirect to standby/ADG for read mostly applications

Simplified Dataguard broker parameter configurations

Observe only mode for data guard broker fast-satrt failover (FSFO)

Oracle dataguard multi-instance redo apply works with the in-memory column store

Finer granularity supplemental logging for logical standby databases

New Initialization Parameters in Oracle Database 19c 

"-optimizer_gather_stats_on_conventional_dml" and " _optimizer_use_stats_on_conventional_dml" which are true by default

-optimer_stats_on_conventional_dml_sample_rate(at 100%)

DATA_GUARD_MAX_IO_TIME

DATA_GUARD_MAX_LONGIO_TIME

MAX_DATAPUMP_JOBS_PER_PDB

THANKS FOR VIEWING MY BLOG FOR MORE UPDATES FOLLOW ME OR SUBSCRIBE ME