Oracle Database Commands and Practical Examples

Wednesday 10 March 2021

Dataguard 12c: Configure Dataguard on physical standby 12c

Data guard Configuration 12.1.0.2.0 Release

Configure the Broker in a Data Guard configuration

Create physical standby and tnsping both machine.

Create necessary directories for DG_BROKER_CONFIG_FILE

(In our case we mentioned $ORACLE_HOME/dbs localtion )

• Set Broker configuration file parameters

NOTE : 2 DG_BROKER_CONFIG_FILE is for multiplexing

(DG_BROKER_CONFIG_FILE1, DG_BROKER_CONFIG_FILE2)

• Set up the static listener entries on all databases

• Start the Broker processes on all the databases

(DG_BROKER_START=TRUE)

• Start DGMGRL and connect to the primary database

(dgmgrl sys/type_sys_pwd@db_service_name)

• Create the base configuration

DGMGRL> CREATE CONFIGURATION zabbix AS PRIMARY DATABASE IS zabbix CONNECT IDENTIFIER IS zabbix;

• Add the standby database to the Broker configuration

DGMGRL> ADD DATABASE zabbixdr AS CONNECT IDENTIFIER IS zabbixdr;

• Set the database properties

SHOW DATABASE VERBOSE Primary or database name;

• Enable the configuration

DGMGRL> ENABLE CONFIGURATION;
• Verify the configuration

DGMGRL> SHOW CONFIGURATION;

Step 1: Set Broker configuration file parameters (DG_BROKER_CONFIG_FILE1, DG_BROKER_CONFIG_FILE2)

[oracle@srv3 dbs]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Wed Feb 17 20:10:08 2021

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

SQL> sho pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED

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

2 PDB$SEED READ ONLY NO

3 PDB1 READ WRITE NO

SQL> ALTER SYSTEM SET DG_BROKER_CONFIG_FILE1='/u01/app/oracle/product/12.1.0.2/db_1/dbs/pr1ORADB.dat';

System altered.

SQL> ALTER SYSTEM SET DG_BROKER_CONFIG_FILE2='/u01/app/oracle/product/12.1.0.2/db_1/dbs/pr2ORADB.dat';

System altered.

SQL> exit

Step 2: Start the Broker processes on all the databases

SQL> SHOW PARAMETER DG_BROKER_START

NAME TYPE VALUE

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

dg_broker_start boolean FALSE

SQL> ALTER SYSTEM SET DG_BROKER_START=TRUE SCOPE=BOTH;

System altered.

Step 3:

SQL> alter system set LOG_ARCHIVE_DEST_2='' SCOPE=BOTH sid='*';

System altered.

SQL> exit

Step 3: Connect standby database Configure DG broker config file

SQL> ALTER SYSTEM SET DG_BROKER_CONFIG_FILE1='/u01/app/oracle/product/12.1.0.2/db_1/dbs/dr1ORADB.dat';

System altered.

SQL> ALTER SYSTEM SET DG_BROKER_CONFIG_FILE2='/u01/app/oracle/product/12.1.0.2/db_1/dbs/dr2ORADB.dat';

System altered.

SQL> SHOW PARAMETER DG_BROKER_START

NAME TYPE VALUE

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

dg_broker_start boolean FALSE

SQL> ALTER SYSTEM SET DG_BROKER_START=TRUE SCOPE=BOTH;

System altered.

SQL> alter system set LOG_ARCHIVE_DEST_2='' SCOPE=BOTH sid='*';

System altered.

Step4 : Start DGMGRL and connect to the primary database

[oracle@srv3 admin]$ dgmgrl sys/system123@zabbix

DGMGRL for Linux: Version 12.1.0.2.0 - 64bit Production

Welcome to DGMGRL, type "help" for information.

Connected as SYSDBA.

DGMGRL> show configuration

ORA-16532: Oracle Data Guard broker configuration does not exist

Configuration details cannot be determined by DGMGRL

#####################################################

STEP 5 : Create the base configuration on primary db host

DGMGRL> CREATE CONFIGURATION zabbix AS PRIMARY DATABASE IS zabbix CONNECT IDENTIFIER IS zabbix;

Configuration "zabbix" created with primary database "zabbix"

DGMGRL> show configuration

Configuration - zabbix

Protection Mode: MaxPerformance

Members:

zabbix - Primary database

Fast-Start Failover: DISABLED

Configuration Status:

DISABLED

Step 6 : Add Standby database in DGMGRL configuration on primary host.

DGMGRL> ADD DATABASE zabbixdr AS CONNECT IDENTIFIER IS zabbixdr;

Database "zabbixdr" added

DGMGRL> SHOW CONFIGURATION;

Configuration - zabbix

Protection Mode: MaxPerformance

Members:

zabbix - Primary database

zabbixdr - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:

DISABLED

STEP 7 : Enable configuration on primary database

DGMGRL> ENABLE CONFIGURATION;

Enabled.

DGMGRL> SHOW CONFIGURATION;

Configuration - zabbix

Protection Mode: MaxPerformance

Members:

zabbix - Primary database

zabbixdr - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:

SUCCESS (status updated 17 seconds ago)

DGMGRL>

STEP 7: Check standby database as well on DR database

[oracle@srv4 zabbixdr]$ dgmgrl sys/system123@zabbixdr

DGMGRL for Linux: Version 12.1.0.2.0 - 64bit Production

Welcome to DGMGRL, type "help" for information.

Connected as SYSDBA.

DGMGRL> show configuration

Configuration - zabbix

Protection Mode: MaxPerformance

Members:

zabbix - Primary database

zabbixdr - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:

SUCCESS (status updated 15 seconds ago)

DGMGRL>

##################################################################

DGMGRL> SHOW DATABASE VERBOSE zabbix;

Database - zabbix

Role: PRIMARY

Intended State: TRANSPORT-ON

Instance(s):

zabbix

Properties:

DGConnectIdentifier = 'zabbix'

ObserverConnectIdentifier = ''

LogXptMode = 'ASYNC'

RedoRoutes = ''

DelayMins = '0'

Binding = 'optional'

MaxFailure = '0'

MaxConnections = '1'

ReopenSecs = '300'

NetTimeout = '30'

RedoCompression = 'DISABLE'

LogShipping = 'ON'

PreferredApplyInstance = ''

ApplyInstanceTimeout = '0'

ApplyLagThreshold = '0'

TransportLagThreshold = '0'

TransportDisconnectedThreshold = '30'

ApplyParallel = 'AUTO'

StandbyFileManagement = 'AUTO'

ArchiveLagTarget = '1800'

LogArchiveMaxProcesses = '4'

LogArchiveMinSucceedDest = '1'

DbFileNameConvert = '/u01/app/oracle/oradata/zabbixdr/, /u01/app/oracle/oradata/zabbix/'

LogFileNameConvert = '/u01/app/oracle/oradata/zabbixdr/, /u01/app/oracle/oradata/zabbix/'

FastStartFailoverTarget = ''

InconsistentProperties = '(monitor)'

InconsistentLogXptProps = '(monitor)'

SendQEntries = '(monitor)'

LogXptStatus = '(monitor)'

RecvQEntries = '(monitor)'

StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=srv3.example.com)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=zabbix_DGMGRL)(INSTANCE_NAME=zabbix)(SERVER=DEDICATED)))'

StandbyArchiveLocation = 'USE_DB_RECOVERY_FILE_DEST'

AlternateLocation = ''

LogArchiveTrace = '0'

LogArchiveFormat = '%t_%s_%r.dbf'

TopWaitEvents = '(monitor)'

Database Status:

SUCCESS

DGMGRL>

#############################################################################

DGMGRL> SHOW DATABASE VERBOSE zabbixdr;

Database - zabbixdr

Role: PHYSICAL STANDBY

Intended State: APPLY-ON

Transport Lag: 0 seconds (computed 1 second ago)

Apply Lag: 0 seconds (computed 1 second ago)

Average Apply Rate: 1.00 KByte/s

Active Apply Rate: 2.37 MByte/s

Maximum Apply Rate: 2.38 MByte/s

Real Time Query: OFF

Instance(s):

zabbixdr

Properties:

DGConnectIdentifier = 'zabbixdr'

ObserverConnectIdentifier = ''

LogXptMode = 'ASYNC'

RedoRoutes = ''

DelayMins = '0'

Binding = 'optional'

MaxFailure = '0'

MaxConnections = '1'

ReopenSecs = '300'

NetTimeout = '30'

RedoCompression = 'DISABLE'

LogShipping = 'ON'

PreferredApplyInstance = ''

ApplyInstanceTimeout = '0'

ApplyLagThreshold = '0'

TransportLagThreshold = '0'

TransportDisconnectedThreshold = '30'

ApplyParallel = 'AUTO'

StandbyFileManagement = 'AUTO'

ArchiveLagTarget = '1800'

LogArchiveMaxProcesses = '4'

LogArchiveMinSucceedDest = '1'

DbFileNameConvert = '/u01/app/oracle/oradata/zabbix/, /u01/app/oracle/oradata/zabbixdr/'

LogFileNameConvert = '/u01/app/oracle/oradata/zabbix/, /u01/app/oracle/oradata/zabbixdr/'

FastStartFailoverTarget = ''

InconsistentProperties = '(monitor)'

InconsistentLogXptProps = '(monitor)'

SendQEntries = '(monitor)'

LogXptStatus = '(monitor)'

RecvQEntries = '(monitor)'

StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=srv4.example.com)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=zabbixdr_DGMGRL)(INSTANCE_NAME=zabbixdr)(SERVER=DEDICATED)))'

StandbyArchiveLocation = 'USE_DB_RECOVERY_FILE_DEST'

AlternateLocation = ''

LogArchiveTrace = '0'

LogArchiveFormat = '%t_%s_%r.dbf'

TopWaitEvents = '(monitor)'

Database Status:

SUCCESS

DGMGRL>

##########################################

STEP 8 : Shutdown DG broker configuration to stop MRP process in standby

DGMGRL> EDIT DATABASE zabbixdr SET STATE=APPLY-OFF;

Succeeded.

DGMGRL> show configuration;

Configuration - zabbix

Protection Mode: MaxPerformance

Members:

zabbix - Primary database

zabbixdr - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:

SUCCESS (status updated 3 seconds ago)

DGMGRL>

Saturday 6 March 2021

ASM - Add and remove asmdiskgroup in 12c

Add and remove asmdiskgroup in 12c using command line

Make raw partition of disk

[root@srv1 ~]# fdisk /dev/sdf

Device contains neither a valid DOS partition table, nor Sun, SGI or OSF disklabel

Building a new DOS disklabel with disk identifier 0x20717bf6.

Changes will remain in memory only, until you decide to write them.

After that, of course, the previous content won't be recoverable.

Warning: invalid flag 0x0000 of partition table 4 will be corrected by w(rite)

WARNING: DOS-compatible mode is deprecated. It's strongly recommended to

switch off the mode (command 'c') and change display units to

sectors (command 'u').

Command (m for help): n

Command action

e extended

p primary partition (1-4)

Partition number (1-4): 1 dsdsdsd

First cylinder (1-1566, default 1):

Using default value 1

Last cylinder, +cylinders or +size{K,M,G} (1-1566, default 1566):

Using default value 1566

Command (m for help): w

The partition table has been altered!

Calling ioctl() to re-read partition table.

Syncing disks.

[root@srv1 ~]# fdisk -l

Disk /dev/sda: 85.9 GB, 85899345920 bytes

255 heads, 63 sectors/track, 10443 cylinders

Units = cylinders of 16065 * 512 = 8225280 bytes

Sector size (logical/physical): 512 bytes / 512 bytes

I/O size (minimum/optimal): 512 bytes / 512 bytes

Disk identifier: 0x00047a8e

Device Boot Start End Blocks Id System

/dev/sda1 * 1 77 614400 83 Linux

Partition 1 does not end on cylinder boundary.

/dev/sda2 77 599 4194304 83 Linux

Partition 2 does not end on cylinder boundary.

/dev/sda3 599 10444 79076352 83 Linux

Disk /dev/sdb: 12.9 GB, 12884901888 bytes

255 heads, 63 sectors/track, 1566 cylinders

Units = cylinders of 16065 * 512 = 8225280 bytes

Sector size (logical/physical): 512 bytes / 512 bytes

I/O size (minimum/optimal): 512 bytes / 512 bytes

Disk identifier: 0xb374f6ac

Device Boot Start End Blocks Id System

/dev/sdb1 1 1566 12578863+ 83 Linux

Disk /dev/sdc: 21.5 GB, 21474836480 bytes

255 heads, 63 sectors/track, 2610 cylinders

Units = cylinders of 16065 * 512 = 8225280 bytes

Sector size (logical/physical): 512 bytes / 512 bytes

I/O size (minimum/optimal): 512 bytes / 512 bytes

Disk identifier: 0xbf063e34

Device Boot Start End Blocks Id System

/dev/sdc1 1 2610 20964793+ 83 Linux

Disk /dev/sdd: 21.5 GB, 21474836480 bytes

255 heads, 63 sectors/track, 2610 cylinders

Units = cylinders of 16065 * 512 = 8225280 bytes

Sector size (logical/physical): 512 bytes / 512 bytes

I/O size (minimum/optimal): 512 bytes / 512 bytes

Disk identifier: 0x8af70889

Device Boot Start End Blocks Id System

/dev/sdd1 1 2610 20964793+ 83 Linux

Disk /dev/sde: 4294 MB, 4294967296 bytes

255 heads, 63 sectors/track, 522 cylinders

Units = cylinders of 16065 * 512 = 8225280 bytes

Sector size (logical/physical): 512 bytes / 512 bytes

I/O size (minimum/optimal): 512 bytes / 512 bytes

Disk identifier: 0xff585e78

Device Boot Start End Blocks Id System

/dev/sde1 1 522 4192933+ 83 Linux

Disk /dev/sdf: 12.9 GB, 12884901888 bytes

255 heads, 63 sectors/track, 1566 cylinders

Units = cylinders of 16065 * 512 = 8225280 bytes

Sector size (logical/physical): 512 bytes / 512 bytes

I/O size (minimum/optimal): 512 bytes / 512 bytes

Disk identifier: 0x20717bf6

Device Boot Start End Blocks Id System

/dev/sdf1 1 1566 12578863+ 83 Linux

Create ASM disk using raw partition

[root@srv1 ~]# oracleasm createdisk backup /dev/sdf1

Writing disk header: done

Instantiating disk: done

[root@srv1 ~]# oracleasm listdisks

BACKUP

CRS

DATA

FRA

[root@srv1 ~]# su grid

[grid@srv1 root]$ cd

[grid@srv1 ~]$ . oraenv

ORACLE_SID = [+ASM1] ?

The Oracle base remains unchanged with value /u01/app/grid

[grid@srv1 ~]$ asmcmd

ASMCMD> lsdg

State Type Rebal Sector Block AU Total_MB Free_MB Req_mir_free_MB Usable_file_MB Offline_disks Voting_files Name

MOUNTED EXTERN N 512 4096 1048576 12284 7807 0 7807 0 Y CRS/

MOUNTED EXTERN N 512 4096 1048576 20473 17997 0 17997 0 N DATA/

MOUNTED EXTERN N 512 4096 1048576 20473 20142 0 20142 0 N FRA/

ASMCMD> exit

[grid@srv1 ~]$

SQL> col PATH for a40

SQL> /

PATH HEADER_STATU

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

/dev/oracleasm/disks/BACKUP PROVISIONED

/dev/oracleasm/disks/FRA MEMBER

/dev/oracleasm/disks/DATA MEMBER

/dev/oracleasm/disks/CRS MEMBER

SQL> sho parameter ASM_DISKSTRING

NAME TYPE VALUE

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

asm_diskstring string /dev/oracleasm/disks

Add ASM disk group with EXTERNAL redundancy level.

SQL> CREATE DISKGROUP backup EXTERNAL REDUNDANCY DISK '/dev/oracleasm/disks/BACKUP';

Diskgroup created.

SQL> select PATH,HEADER_STATUS from v$asm_disk;

PATH HEADER_STATU

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

/dev/oracleasm/disks/FRA MEMBER

/dev/oracleasm/disks/DATA MEMBER

/dev/oracleasm/disks/CRS MEMBER

/dev/oracleasm/disks/BACKUP MEMBER

[grid@srv1 ~]$ asmcmd

ASMCMD> lsdg

State Type Rebal Sector Block AU Total_MB Free_MB Req_mir_free_MB Usable_file_MB Offline_disks Voting_files Name

MOUNTED EXTERN N 512 4096 1048576 12284 12234 0 12234 0 N BACKUP/

MOUNTED EXTERN N 512 4096 1048576 12284 7807 0 7807 0 Y CRS/

MOUNTED EXTERN N 512 4096 1048576 20473 17997 0 17997 0 N DATA/

MOUNTED EXTERN N 512 4096 1048576 20473 20142 0 20142 0 N FRA/

ASMCMD>

SQL> set lines 200

SQL> col GROUP_NUMBER for a60

SQL> col GROUP_NUMBER for a40

SQL> col COMPATIBILITY for a40

SQL> col DATABASE_COMPATIBILITY for a40

SQL> select group_number, name, compatibility, database_compatibility from v$asm_diskgroup;

GROUP_NUMBER NAME COMPATIBILITY DATABASE_COMPATIBILITY

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

########## BACKUP 10.1.0.0.0 10.1.0.0.0

########## FRA 12.1.0.0.0 10.1.0.0.0

########## DATA 12.1.0.0.0 10.1.0.0.0

########## CRS 12.1.0.0.0 10.1.0.0.0

SQL> ALTER DISKGROUP BACKUP SET ATTRIBUTE 'compatible.asm' = '12.1.0.0.0';

Diskgroup altered.

SQL> select group_number, name, compatibility, database_compatibility from v$asm_diskgroup;

GROUP_NUMBER NAME COMPATIBILITY DATABASE_COMPATIBILITY

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

########## BACKUP 12.1.0.0.0 10.1.0.0.0

########## FRA 12.1.0.0.0 10.1.0.0.0

########## DATA 12.1.0.0.0 10.1.0.0.0

########## CRS 12.1.0.0.0 10.1.0.0.0

Drop diskgroup backup

SQL> DROP DISKGROUP BACKUP INCLUDING CONTENTS;

Diskgroup dropped.

SQL> select name, compatibility, database_compatibility from v$asm_diskgroup;

NAME COMPATIBILITY DATABASE_COMPATIBILITY

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

FRA 12.1.0.0.0 10.1.0.0.0

DATA 12.1.0.0.0 10.1.0.0.0

CRS 12.1.0.0.0 10.1.0.0.0

Delete at OS level

[grid@srv1 ~]$ oracleasm deletedisk BACKUP

Disk "BACKUP" defines an unmarked device

Dropping disk: failed

Unable to delete disk "BACKUP"

[grid@srv1 ~]$ su root

Password:

[root@srv1 grid]# +ASM1

bash: +ASM1: command not found

[root@srv1 grid]# . oraenv

ORACLE_SID = [+ASM1] ?

The Oracle base remains unchanged with value /u01/app/grid

[root@srv1 grid]# oracleasm deletedisk BACKUP

Disk "BACKUP" defines an unmarked device

Dropping disk: done

[root@srv1 grid]# oracleasm listdisks

CRS

DATA

FRA

[root@srv1 grid]#

RMAN : RMAN-08137: WARNING: archived log not deleted needed for standby or upstream capture process archived log file

Scenario Preview :

Recently we find some error using rman backup as we are unable to delete archivelog. Although backup was successful but archivelog not deleting at there end. Reason was physical standby database was not sync and archivelog not applied at DR site.

Even in primary database rman setting is set to CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default

Solution:

We forcefully deleted archivelog so NO error found at the next rman scheduled backup.

delete noprompt force archivelog all;

Error in rman backup log :

archived log file name=+FRA1/SMRPROD/ARCHIVELOG/2021_02_20/thread_2_seq_1002.666.1064966457 thread=2 sequence=1002

RMAN-08137: WARNING: archived log not deleted, needed for standby or upstream capture process

archived log file name=+FRA1/SMRPROD/ARCHIVELOG/2021_02_20/thread_2_seq_1003.667.1064979311 thread=2 sequence=1003

RMAN-08137: WARNING: archived log not deleted, needed for standby or upstream capture process

archived log file name=+FRA1/SMRPROD/ARCHIVELOG/2021_02_20/thread_1_seq_640.668.1064979313 thread=1 sequence=640

RMAN-08137: WARNING: archived log not deleted, needed for standby or upstream capture process

archived log file name=+FRA1/SMRPROD/ARCHIVELOG/2021_02_20/thread_2_seq_1004.669.1064979315 thread=2 sequence=1004

RMAN-08137: WARNING: archived log not deleted, needed for standby or upstream capture process

archived log file name=+FRA1/SMRPROD/ARCHIVELOG/2021_02_20/thread_1_seq_641.670.1064979317 thread=1 sequence=641

RMAN-08137: WARNING: archived log not deleted, needed for standby or upstream capture process

archived log file name=+FRA1/SMRPROD/ARCHIVELOG/2021_02_20/thread_1_seq_642.671.1064979761 thread=1 sequence=642

RMAN-08137: WARNING: archived log not deleted, needed for standby or upstream capture process

archived log file name=+FRA1/SMRPROD/ARCHIVELOG/2021_02_20/thread_2_seq_1005.672.1064979763 thread=2 sequence=1005

13659,1 97%

RMAN > crosscheck archivelog all;

RMAN > delete noprompt force archivelog all;

archived log file name=+FRA2/smrprod/archivelog/2_1065_1051537325.dbf RECID=2981 STAMP=1065309321

deleted archived log

archived log file name=+FRA2/smrprod/archivelog/2_1066_1051537325.dbf RECID=2983 STAMP=1065312067

deleted archived log

archived log file name=+FRA2/smrprod/archivelog/2_1067_1051537325.dbf RECID=2984 STAMP=1065324915

deleted archived log

archived log file name=+FRA2/smrprod/archivelog/2_1068_1051537325.dbf RECID=2986 STAMP=1065324921

deleted archived log

archived log file name=+FRA2/smrprod/archivelog/2_1069_1051537325.dbf RECID=2988 STAMP=1065325462

deleted archived log

archived log file name=+FRA2/smrprod/archivelog/2_1070_1051537325.dbf RECID=2990 STAMP=1065332821

Deleted 427 objects