Pages

Advertisement

Showing posts with label 18c. Show all posts
Showing posts with label 18c. Show all posts

Sunday, May 17, 2020

Oracle Database (All) : Archive Log Generation (Distribution - hourly/daily/size)

 
In this blog I am going to cover on how to find out 
1. total number of archives generated per hour & per day 
2. The size of the archives MB/ hour & MB per day



Output Sample for redo switches / hour

Redo Switches / hour /day
Redo Size / hour /day

The complete script can be download as a GIST from here 


Thursday, November 21, 2019

OEM 13cR3 - Silent Installation with DB 18c Templates - Part 1

 
In this blog I am going to cover installation of OEM 13cR3 silent with Templates on DB 18
I will be referring to my previous blogs here to keep the length of this Series minimal.
This is a 2 Part blog

 1. The first part covers Database Installation and DB Creation
2. The second part which can be found here covers the part where we install the OEM.

 Part 1
The step 1 is obviously to have your database installed, you can refer to my blog on Database Installation Silent of 18c 

 Once the Installation is complete, download the template for Database 18c for OEM 13cR3 from below link
https://www.oracle.com/enterprise-manager/downloads/db-templates-13c-release3-downloads.html

Unzip the template in $ORACLE_HOME/assistants/dbca/templates

$ cd /u01/app/oracle/product/183/db/assistants/dbca/templates
$ unzip -qq /u01/Template/18.1.0.0.0_database_template_for_em13_3_0_0_0_linux_x64.zip

 Next Install EMREPO using the template


$ /u01/app/oracle/product/183/db/bin/dbca -silent -createDatabase -templateName  18_1_0_0_0_Database_Template_for_EM13_3_0_0_0_Small_deployment.dbc -gdbName EMPRD -sid EMPRD -sysPassword oracle -systemPassword  oracle

 Once the DB is Installed and running, set SPFILE parameters according to your need and set  all the spfile parameters as specified in below link for an 18c Installation. 

 https://www.oracle.com/enterprise-manager/downloads/db-templates-13c-release3-downloads.html

After that do a Bounce of the Instance.


This completes my database creation.
Ensure you have a listener running which registers the DB Services
IN the next part of the blog we will setup the OEM. 

Friday, September 6, 2019

Oracle Database: Process RSM0, PID = , will be killed

 
When you check status of database using dgmgrl and you get the error message in alert log - 
'Process RSM0, PID = 1524144984, will be killed'

 that means that dgmgrl is not able to get the response within stipulated amount of  time. 
This can be because of many reasons such has high CPU, Load Average , network etc. 
So, what you can do is increase the overall timeout period using 

 'EDIT CONFIGURATION SET PROPERTY OperationTimeout=90'

 another symptom of this is when you will check the database details, there will be no metrics which will be populated. 

Wednesday, August 21, 2019

Oracle Database: TFACTL Blackout ORA-600/7445/700

 
tfactl or oracle's trace file analyzer utility can be really helpful sometimes, and also a lot of pain in many, when it sends a lot of alerts some of which you know and it's a known issue or you don't want to do anything about it. 

 Fortunately, you can blackout such events from tfactl 

 To supppress a specific string ORA-0600 this is what I would do. 
tfactl blackout add -targettype database -target mydb -c -event  "kewrspbr_2"  -timeout none -reason "skipping ORA-0600 Doc ID 2313603.1" 

 where 
-target : dbname
-c : clusterwide
-event: String contained in the event (find it from the email you got)

-timeout : no timeout

 Similarly, you can play around with the parameters and blackout a lot of unwanted emails.
You can find them documentation below. 
Reference - https://docs.oracle.com/en/engineered-systems/health-diagnostics/trace-file-analyzer/tfaug/running-administration-commands.html#GUID-56A03EC7-3324-48BD-9701-2411626D16CB

Friday, August 2, 2019

Oracle Database: Permissions to Execute Job Class

 
There is a great mis conception in DBA's who primarily work on OLTP about Job classes.

 Job classes are one of the essentially used components in job definitions which people seldom use and seldom know about. 

 This blog however is a small and nice one which talks about how to see permissions to execute a job class.

 Yes you read that right. You need execute permission for a job class. 
Only if you have execute permission to job class, you will be able to add it to your create job. 

 the way to grant is same 

 Grant execute 'on job class' to <user>';

 to verify you should use dba_tab_privs to know if your user has execute privilege on job class or not. 

 select * from dba_tab_privs where grantee = '<your user>' and type = 'JOB CLASS';

 Example output below (from TOAD)

By default permission to execute 'DEFAULT JOB CLASS' is granted to PUBLIC, so you if you do not specify any job class name, default job class will be taken and it will be okay. 

Wednesday, June 12, 2019

Oracle Database: Duplicate Specific Tablespaces only

 
Oracle allows you to duplicate a database using specific tablespaces only, .

 The command is same as the duplicate command 

Mention the tablespace comma separated names (highlighted in bold)
Ofcourse temp, system,sysaux, undo will be duplicated along
  • rman run at the new database site
  • target is source
  • auxiliary is the new db
  • list of tablespaces is comma seperated.
  • if you want to skip a specific tablespace use 'skip <tablespace_name>' 


rman 
connect TARGET sys/passs@PROD;
connect AUXILIARY sys/pass@XDB1_STDBY;
run 
{
set newname for database to '+FRA';
DUPLICATE TARGET DATABASE 
  TO XDB
  FROM ACTIVE DATABASE
  TABLESPACE users
  NOFILENAMECHECK;

}

The highlight here is that oracle will internally check during duplicate that if the tablespaces are linked (dependent) or not.

You will have to ensure that static entries is present for the new database in the listener, as it will need to be shut down and started by the rman. 

Oracle Database: Planning for New Storage

 
In this blog I cover topics which need to be considered when doing a new storage design for your Oracle Database. 

 1. Review the current storage speed using the storage consoles which you have and find out the iops happening on the SSD Cluster + HDD Cluster, you can get iops and data transferred from the storages itself. From the current design, you must add the iops of SSD and HDD cluster and the data transfer as well

  • design should include at least these much iops and probably a 20-30% more.
  • design should be done considering the peak hours storage iops and the data transfer rates.
2. Design should/not enforce redundancy. 

  • data is better handled by ASM redundancy, in case using ASM use ASM redundancy - Normal is preferred for DATA and FRA, High for REDO, CF and OCR.
  • in case you are not using ASM then ask storage to include external parity and redundancy
  • the effective storage capacity should be given as per the approach followed. 
3. A design having multiple disks performs better in oracle, as when you give the luns to oracle, it will stripe the data across the disks, thus maximizing performance. 

  • design with many small disks can mean additional controllers and more management and more sockets to insert to.
  • design with big disks again can reduce performance, so you should have a tradeoff with respect to cost and design.
4.  Latency which you plan should be as seen by Oracle and not from storage perspective

  • from the AWR find out the current latency, min, max and average.
  • In the AWR, you will have details of what you want or you can use my blog to find out latency of the events. 
  • latency introduced by networking components should be considered
  • networking should be such that it does not introduces a significant amount of latency
  • latency planned should be the same at the maximum iops and maximum data transfer
  • prefer to have redo, controlfile, ocr on fastest disks as these require less storage area.
  • if you have a read intensive and dw application, you can have temp to on faster storage area. 

5. Any storage design should exclude any type of inline compression or storage optimization, unless the storage compression (at storage level) is certified by Oracle.

6. Migration to new storage depends on what type of design you use.
  • when migrating to new storage always follow standby first approach
  • when using ASM, use drop and add disks in the same operation
  • these operations are resumable after restart of the cluster also
  • when not using ASM, use methods such as duplicate and rolling switch of data files to minimize downtime.

Tuesday, June 11, 2019

Oracle Database: Flashback RAC Primary and Standby

 
In this blog I am going to discuss on how to flashback a RAC Primary and RAC Standby both and ensure Standby is in sync. 
These are for some maintenance activities you might want to perform on Primary and to have a rollback plan. 
You can re-order few steps if you ant
My primary Database name is PRIM and Standby is STDBY.

Step 1 and 2 are setup for the flashback. 

 Step 1  - Stop the apply on STDBY
dgmgrl /
edit datbase 'STDBY' set state 'APPLY-OFF'

 Step 2 - Create GRP (Guranteed Restore Point)
i. Switch logfile on Primary (alter system archive log current) 
ii. Create GRP on Standby (Create Restore  Point BEFORE_RELOAD GUARANTEE FLASHBACK DATABASE)
iii. Create GRP on Primary  (Create Restore  Point BEFORE_RELOAD GUARANTEE FLASHBACK DATABASE)
iv. take output of select * from v$restore_point and note the scn


 Now do your changes (patching etc), assuming your changes were not successful and you want to rollback.  

 Step 3 - Do the Flashback
i. Stop Database Primary (srvctl stop database -d PRIM)
ii. Start one instance in mount (startup mount exclusive)
iii. Flashback Database (flashback database to restore point BEFORE_RELOAD);
iv. alter database open reset logs

 Step 4 - Start Primary (all nodes)
i. srvctl start database -d PRIM


 Step 5 - Flashback Standby 
i. Check the current_scn (select current_scn from V$database)
ii. if current_scn of Standby is less than 2.iv, i.e scn of GRP on primary then you need not to do any flashback and can skip
iii. If current_scn is greater then you have 2 options
First - srvctl stop database -d STDBY and then startup mount exclusive for 1 instance
Then - 
    a. Flashback to scn of the GRP of primary (flashback database to scn <> )
    b. Flashback to scn of the GRP of standby (flashback database to restore point BEFORE_RELOAD). This assumes the scn of standby GRP is less than scn of primary GRP
iv. start DB (srvctl stop database -d CHMSTDBY, and srvctl start database -d CHMSTDBY)
v. Ensure apply is working fine. (edit database 'STDBY' set state='APPLY-ON')

 Step 6 - Finally Drop Restore Points
i. Primary - Drop restore point BEFORE_RELOAD
ii. Standby - for this you will have to have database is mount mode (in case your standby was read only) and then 'drop restore point BEFORE_RELOAD'

 References: How To Flashback Primary Database In Standby Configuration (Doc ID 728374.1)

Friday, May 31, 2019

Oracle Database: Tools for Monitoring (I/O, Network)

 
In this blog I discuss few tools which can be used for database or systems monitoring to be precisely. 

 If you want to know what rpms you should be installing just beyond the required ones to install oracle Database, then these are the ones  

 Now assuming you have these installed

 1. IOTOP
One of the really good tools which can help in debugging i/o performance 
this can tell 
1. Total disk read and write happening
2. Disk read and write by process
3. IO wait % for a process
4. Swap information for a process

Just run iotop as root
Total DISK READ :     574.79 K/s | Total DISK WRITE :       2.92 M/s
Actual DISK READ:     574.79 K/s | Actual DISK WRITE:       2.95 M/s
   TID  PRIO  USER     DISK READ  DISK WRITE  SWAPIN     IO>    COMMAND
  5174 rt/4 grid      969.66 B/s    0.00 B/s  0.00 %  1.64 % ocssd.bin
175547 be/4 oracle    106.06 K/s    0.00 B/s  0.00 %  0.87 % ora_j001_PR11
 10213 be/4 oracle    151.51 K/s  227.26 K/s  0.00 %  0.86 % ora_ckpt_PR11
 10335 be/4 oracle      0.00 B/s  674.22 K/s  0.00 %  0.74 % ora_rvwr_PR11
175549 be/4 oracle    151.51 K/s    0.00 B/s  0.00 %  0.68 % ora_j002_PR11
 17843 be/4 grid      121.21 K/s   15.15 K/s  0.00 %  0.66 % mdb_ckpt_-MGMTDB
  5170 rt/4 grid      969.66 B/s    0.00 B/s  0.00 %  0.17 % ocssd.bin

  5167 rt/4 grid      969.66 B/s    0.00 B/s  0.00 %  0.16 % ocssd.bin


 2. IOSTAT
Iostat is complimentary to iotop, it provides details at the disk level 
run iostat -dmx 2 sdb sdc sds sdq sdm as root
You will get information like this below 
1. You can tell individual disk utilization 
2. -dmx 2 are arguments (you can look up in the man page)
3. to find out the sd devices you want to see use lsblk
Linux 3.10.0-693.17.1.el7.x86_64 (CHUMR1DB001)  05/31/2019      _x86_64_        (16 CPU)
Device:         rrqm/s   wrqm/s     r/s     w/s    rMB/s    wMB/s avgrq-sz avgqu-sz   await r_await w_await  svctm  %util
sdb               0.00     0.00    4.73    0.73     0.15     0.01    59.82     0.05    9.07   10.02    2.91   4.71   2.57
sdc               0.00     0.00    5.38    5.32     2.82     1.98   918.41     1.44  134.90  140.15  129.58  12.13  12.98
sdm               0.00     0.00    2.21    1.33     0.00     0.00     2.20     0.01    2.28    1.90    2.92   2.09   0.74
sdq               0.00     0.00    2.33    1.21     0.10     0.04    79.00     0.02    6.47    8.15    3.22   5.63   1.99
sds               0.00     0.00    2.23    1.34     0.00     0.00     2.30     0.01    2.32    1.88    3.05   2.21   0.79
Device:         rrqm/s   wrqm/s     r/s     w/s    rMB/s    wMB/s avgrq-sz avgqu-sz   await r_await w_await  svctm  %util
sdb               0.00     0.00    0.00    0.00     0.00     0.00     0.00     0.00    0.00    0.00    0.00   0.00   0.00
sdc               0.00     0.00    0.00    2.50     0.00     0.76   624.00     0.01    3.60    0.00    3.60   3.60   0.90
sdm               0.00     0.00    2.00    1.00     0.00     0.00     1.00     0.00    0.67    0.50    1.00   0.67   0.20
sdq               0.00     0.00    1.50    1.00     0.02     0.05    63.60     0.00    1.40    1.00    2.00   1.40   0.35
sds               0.00     0.00    2.00    1.00     0.00     0.00     1.00     0.00    0.83    0.75    1.00   0.83   0.25


2. iptraf-ng
The next big tool is which is for networking is iptraf-ng
this can be a very useful network tool with a lot of features such as interfaces stats, breakdowns, traffic monitoring. 
You really want this as it is there in official redhat repo 

 Just run iptraf-ng as root, and you will get all the options which you can choose from 


 IP traffic monitor             
General interface statistics   
Detailed interface statistics  
Statistical breakdowns...      
LAN station monitor           

Choose the option you want - say traffic monitor, then select the interfaces and then you can see the live monitoring. 
You can look into Detailed interface statistics and choose the interface to find out the bandwidth (in rate and out rate currently)
It supports logical interfaces as well

Oracle Database: How to relocate tablespaces to different Diskgroup (except SYS)

 
in this blog, I list out steps for Oracle RAC relocating tablespaces to different diskgroups with partial downtime only 

 Step1 - 
backup tablespaces to new diskgroup - 

 run 
{
allocate channel c1 type disk;
allocate channel c2 type disk;
allocate channel c3 type disk;
allocate channel c4 type disk;
allocate channel c5 type disk;
allocate channel c6 type disk;
allocate channel c7 type disk;
allocate channel c8 type disk;
backup as copy tablespace TB1,TB2,TB3 format '+DATA2';
}

test

 Repeat for each tablespaces
1. SQL> 
     alter tablespace TB1 offline;
2. rman  
     switch tablespace TB1 to copy;
     run {
    allocate channel c1 type disk;
    recover tablespace TB1;
    }
3. SQL> 
    alter tablespace TB1 online;

This completes the relocation.
If you want to do it for datafile level (in case your tabelspace is large)
You can follow same steps (with minor modifications from tablespace to datafile )

Oracle Database: RAC - Relocate UNDO to different Diskgroup (and Standby)

 
In this blog I discuss steps to relocate Undotablespaces on a 2 node rac cluster.

 Currently tablespaces are
Node 1 - undotbs1
Node 2 - undotbs2
Step 1 - Create Undotablespaces (as per the size required) 
(Note the name difference) 

  CREATE UNDO TABLESPACE "UNDOTBS11" DATAFILE '+DATA2' SIZE 209715200 AUTOEXTEND ON NEXT 52428800 MAXSIZE 32767M,
  BLOCKSIZE 8192
  EXTENT MANAGEMENT LOCAL AUTOALLOCATE;

  CREATE UNDO TABLESPACE "UNDOTBS22" DATAFILE '+DATA2' SIZE 209715200 AUTOEXTEND ON NEXT 52428800 MAXSIZE 32767M,
  BLOCKSIZE 8192

   EXTENT MANAGEMENT LOCAL AUTOALLOCATE;
Next set the undo tablespace parameter 

   alter system set undo_tablespace=UNDOTBS11 scope=spfile sid='NODE1';  
  alter system set undo_tablespace=UNDOTBS22 scope=spfile sid='NODE2';


Next drop the the undo tablespace after undo retention has expired
Drop tablespace UNDOTBS1 including contents and datafiles;
Drop tablespace UNDOTBS2 including contents and datafiles;

 This completes the relocation.

 In case you are running a standby RAC, then you will need to set these a well, however these parameters are not dynamic, so you will have to set it to spfile and restart the standby DB (not just instance)

Oracle Database: How much can you shrink tablespace / Size

 
It's one of the big questions on how much I will be able to shrink my tablespace or my datafiles. 

 It is easy just run this and substitute the name of tablespace (currently SYSAUX in bold)
You will find list of files and how much they can be reduced 

 set verify off
column file_name format a50 word_wrapped
column smallest format 999,990 heading "Smallest|Size|Poss."
column currsize format 999,990 heading "Current|Size"
column savings  format 999,990 heading "Poss.|Savings"
break on report
compute sum of savings on report
column value new_val blksize
select value from v$parameter where name = 'db_block_size';
/
select file_name,
       ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) smallest,
       ceil( blocks*&&blksize/1024/1024) currsize,
       ceil( blocks*&&blksize/1024/1024) -
       ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) savings
from dba_data_files a,
     ( select file_id, max(block_id+blocks-1) hwm
         from dba_extents
        group by file_id ) b
where a.file_id = b.file_id(+)  and
a.tablespace_name = 'SYSAUX' 
order by savings desc

/

 Output similar to below 
                                                   Smallest
                                                       Size  Current    Poss.
FILE_NAME                                             Poss.     Size  Savings
-------------------------------------------------- -------- -------- --------
+DATA2/chmprod/datafile/sysaux.431.1009335297         9,372   30,720   21,348
+DATA2/chmprod/datafile/sysaux.421.1009334935        29,894   30,720      826
+DATA2/chmprod/datafile/sysaux.299.1009331535        32,269   32,767      498
+DATA2/chmprod/datafile/sysaux.297.1009331535        32,669   32,750       81
+DATA2/chmprod/datafile/sysaux.296.1009331535        32,717   32,750       33
+DATA2/chmprod/datafile/sysaux.422.1009334937        30,690   30,720       30
+DATA2/chmprod/datafile/sysaux.298.1009331535        32,764   32,767        3
+DATA2/chmprod/datafile/sysaux.420.1009334933        30,720   30,720        0
                                                                     --------

                                                    Smallest
                                                       Size  Current    Poss.
FILE_NAME                                             Poss.     Size  Savings
-------------------------------------------------- -------- -------- --------

sum                                                                    22,819

Friday, May 10, 2019

Oracle Database: Recommended RPM's except the default ones

 
In this blog I list RPM's which I always recommend on a Linux installation additional to what Oracle mandates. 

 these RPM's are helpful on operational purposes and are required by DBAs and sys-admins when they are doing debugging of an issue. 

So below is the list which I do -
1. psmisc
2. bc
3. unzip
4. zip
5. bind-utils 
6. iotop
7. traceroute
8. tcpdump
9. strace
10. lsof 
11. sysstat
12. procps-ng
13. net-tools
14. iptraf-ng


to install (if you have yum confiugred)

yum install psmisc bc unzip zip bind-utils iotop traceroute tcpdump strace lsof sysstat procps-ng net-tools -y

Tuesday, May 7, 2019

Oracle Database: Updating Grid / Oracle Inventory Manually

 
In this small blog, I will tell you how to update the inventory file with node names manually.

 This is useful in many cases
1.  Inventory does not have node names
2. RAC Node addition / deletion - automatic update does not happen
3. During Installation - inventory does not have node names
Case 1
Update Inventory RAC Node - Add Node names
(To be run as grid installation owner)
$ /opt/oracle/product/180/grid/oui/bin/runInstaller -silent -ignoreSysPrereqs -updateNodeList ORACLE_HOME=/opt/oracle/product/180/grid "CLUSTER_NODES={Node1,Node2}" CRS=true LOCAL_NODE=Node1

 This will update the inventory on all cluster nodes (2 in this case)

Case 2
Update only on one node in case of cluster installation
(To be run as grid installation owner)
$ /opt/oracle/product/180/grid/oui/bin/runInstaller -updateNodeList -noClusterEnabled ORACLE_HOME=/opt/oracle/product/180/grid "CLUSTER_NODES={Node1,Node2}" CRS=true  LOCAL_NODE=Node2

Oracle Database: VOTE Disk (Closed) / Not Available

 
In this blog I am going to discuss an issue which I faced during my maintenance on cluster. 
One of the votedisk became unavailable and was dropped from the system in my cluster.
crsctl will only show me 2 Vote disks
$ crsctl query css votedisk
##  STATE    File Universal Id                File Name Disk group
--  -----    -----------------                --------- ---------
 1. ONLINE   578049fd7bd34f51bfdf9cd7d2e02205 (/dev/oracleasm/disks/OCR_VOTE1) [OCR_VOTE]
 3. ONLINE   232d262659714f84bfbede64f662f566 (/dev/oracleasm/disks/OCR_VOTE2) [OCR_VOTE]

From V$asm_disk I try to see the status of disk. 

SQL> select mount_status, path, header_status, mode_status from V$asm_disk  where path like '%OCR_VOTE%'


MOUNT_S PATH                                     HEADER_STATU MODE_ST
------- ---------------------------------------- ------------ -------
CLOSED  /dev/oracleasm/disks/OCR_VOTE2           MEMBER       ONLINE
CACHED  /dev/oracleasm/disks/OCR_VOTE3           MEMBER       ONLINE
CACHED  /dev/oracleasm/disks/OCR_VOTE1           MEMBER       ONLINE

Solution  - I add back the Disk to Diskgroup
SQL> alter diskgroup OCR_VOTE add disk '/dev/oracleasm/disks/OCR_VOTE2' force;
(Reference - 
OCR / Vote disk Maintenance Operations: (ADD/REMOVE/REPLACE/MOVE) (Doc ID 428681.1)

and then I check the status again from v$asm_disk and crsctl

SQL> select mount_status, path, header_status, mode_status from V$asm_disk  where path like '%OCR_VOTE%' ;

MOUNT_S PATH                                     HEADER_STATU MODE_ST
------- ---------------------------------------- ------------ -------
CACHED  /dev/oracleasm/disks/OCR_VOTE3           MEMBER       ONLINE
CACHED  /dev/oracleasm/disks/OCR_VOTE2           MEMBER       ONLINE
CACHED  /dev/oracleasm/disks/OCR_VOTE1           MEMBER       ONLINE

$ crsctl query css votedisk
##  STATE    File Universal Id                File Name Disk group
--  -----    -----------------                --------- ---------
 1. ONLINE   578049fd7bd34f51bfdf9cd7d2e02205 (/dev/oracleasm/disks/OCR_VOTE1) [OCR_VOTE]
 2. ONLINE   65cc344fc7da4fbbbf22772629b12203 (/dev/oracleasm/disks/OCR_VOTE3) [OCR_VOTE]
 3. ONLINE   232d262659714f84bfbede64f662f566 (/dev/oracleasm/disks/OCR_VOTE2) [OCR_VOTE]

Tuesday, April 16, 2019

Oracle Database: RAC 2 Nodes - Standby and DG Broker Build - Part 2

 
This blog is in continuation with my previous blog to build Standby and DG broker. 
In this blog I do the setup of Dataguard and DG Broker.
Step 1 - Add Standby Redo log files (Optional and on both primary and Standby)
Standby redo logfiles' count is n+1 where n = total redo logfiles per thread
Syntax is 
alter database add standby logfile thread <num> group <num> ('+DATA','+FRA' ) size 1024M;

 Step 2 - Create Directories for DG Broker Configuration
[Node 1 - grid user - Primary Cluster]
asmcmd mkdir +FRA/MYDBPROD/DATAGUARDCONFIG
asmcmd mkdir +DATA/MYDBPROD/DATAGUARDCONFIG

 [Node 1 - grid user - StandbyCluster ]
asmcmd mkdir +FRA/MYDBSTDBY/DATAGUARDCONFIG
asmcmd mkdir +DATA/MYDBSTDBY/DATAGUARDCONFIG

Step 3 - Setup Dataguard configuration
[Node 1 - oracle user - Standby Cluster]
alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(MYDBPROD,MYDBSTDBY)' scope=both sid='*';
alter system set LOG_ARCHIVE_DEST_2='SERVICE=MYDBPROD ASYNC NOAFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=MYDBPROD' scope=both sid='*';
alter system set LOG_ARCHIVE_DEST_STATE_2='ENABLE' scope=both sid='*';
alter system set fal_server=MYDBPROD scope=both sid='*';

 [Node 1 - oracle user - Primary Cluster]
alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(MYDBPROD,MYDBSTDBY)' scope=both sid='*';
alter system set LOG_ARCHIVE_DEST_2='SERVICE=MYDBSTDBY ASYNC NOAFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=MYDBSTDBY' scope=both sid='*';
alter system set fal_server=MYDBSTDBY scope=both sid='*';

alter system set LOG_ARCHIVE_DEST_STATE_2='ENABLE' scope=both sid='*';

Step 4 - Setup DG Broker configuration
[Node 1 - oracle user - Primary Cluster]
dgmgrl /
CREATE CONFIGURATION 'DG_Config' AS PRIMARY DATABASE IS 'MYDBPROD' CONNECT IDENTIFIER IS MYDBPROD;
show configuration;
ADD DATABASE 'MYDBSTDBY' AS CONNECT IDENTIFIER IS 'MYDBSTDBY';
show configuration;

Enable configuration;

Step 5 - Verify Configuration and Status
[Node 1 - oracle user - Primary Cluster]
dgmgrl /
show configuration;
show database verbose 'MYDBPROD';
show database verbose 'MYDBSTDBY';
show instance verbose 'MYDBPR11' on database 'MYDBPROD';
show instance verbose 'MYDBPR12' on database 'MYDBPROD';
show instance verbose 'MYDBPR11' on database 'MYDBSTDBY';

show instance verbose 'MYDBPR12' on database 'MYDBSTDBY';

 This completes the setup of Dataguard and DG Broker

Oracle Database: RAC 2 Nodes - Standby and DG Broker Build - Part 1

 
In this blog I show how to configure Dataguard Broker configuration for a 2-node RAC cluster to a 2-Node Standby.
This is a 2 blog series - you can find the second blog here - which only talks about configuration of Dataguard and Broker.

 Here is my configuration
Production DB
Unique Name - MYDBPROD
 - Instance 1 - MYDBPR11
 - Insance 2 - MYDBPR12
Unqiue Name - MYDBSTDBY
 - Instance 1 - MYDBPR11
 - Insance 2 - MYDBPR12
Step 1 - Create tnsnames.ora entries in all nodes
MYDBPROD =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = MYPRODDB-PRD-SCAN)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = MYDBPROD)
        (UR = A)
    )
  )

 MYDBSTDBY =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = MYSTDBYDB-PRD-SCAN)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = MYDBSTDBY)
        (UR = A)
    )
  )
Step 2 - Static Listener Entries for DG broker operations
[Node 1 - Grid user Standby Cluster]
File - $ORACLE_HOME/network/admin/listener.ora
SID_LIST_LISTENER =
 (SID_LIST =
  (SID_DESC =
    (GLOBAL_DBNAME = MYDBSTDBY_DGMGRL)
    (ORACLE_HOME = <OH>)
    (SID_NAME = MYDBPR11)
  )
  (SID_DESC =
    (GLOBAL_DBNAME = MYDBSTDBY)
    (ORACLE_HOME = <OH>)
    (SID_NAME = MYDBPR11)
  )
 )
lsnrctl stop
lsnrctl start

 [Node 1 - Grid user Standby Cluster]
File - $ORACLE_HOME/network/admin/listener.ora
SID_LIST_LISTENER =
 (SID_LIST =
  (SID_DESC =
    (GLOBAL_DBNAME = MYDBSTDBY_DGMGRL)
    (ORACLE_HOME = <OH>)
    (SID_NAME = MYDBPR12)
  )
 )

lsnrctl stop 
lsnrcl start

 [Node 1- Grid user - Primary Cluster]
File - $ORACLE_HOME/network/admin/listener.ora
SID_LIST_LISTENER =
 (SID_LIST =
  (SID_DESC =
    (GLOBAL_DBNAME = MYDBPROD_DGMGRL)
    (ORACLE_HOME = <OH>)
    (SID_NAME = MYDBPR11)
  )
 )
lsnrctl stop
lsnrctl start

 [Node 2 - Grid user - Primary Cluster]
File - $ORACLE_HOME/network/admin/listener.ora
SID_LIST_LISTENER =
 (SID_LIST =
  (SID_DESC =
    (GLOBAL_DBNAME = MYDBPROD_DGMGRL)
    (ORACLE_HOME = <OH>)
    (SID_NAME = MYDBPR12)
  )
 )

 lsnrctl stop
lsnrctl start


Step 3 - Reset sys and system password and copy password file from Node 1 of Primary to other 3 nodes (in $ORACLE_HOME/dbs), also change the name as per the name of the instance

 Step 4 -  Add Database and Instance in OCR registry 
[Node 1 - oracle user - Standby Cluster]
srvctl add database -d MYDBSTDBY -o /opt/oracle/product/112/db -c RAC \
-p +DATA/MYDBSTDBY/spfileMYDBSTDBY.ora -r PHYSICAL_STANDBY -s MOUNT -t IMMEDIATE -n <DB_NAME> -a DATA,REDO1,FRA

 srvctl add instance -d MYDBSTDBY -i MYDBPR11 -n Node1
srvctl add instance -d MYDBSTDBY -i MYDBPR12 -n Node2

Step 5 - Create init files
[Node 1 - oracle user - Standby Cluster]
cd /opt/oracle/product/112/db/dbs
cat initMYDBPR11.ora
SPFILE='+DATA/MYDBSTDBY/spfileMYDBSTDBY.ora'

 [Node 2 - oracle user - Standby Cluster]
cd /opt/oracle/product/112/db/dbs
cat initMYDBPR12.ora
SPFILE='+DATA/MYDBSTDBY/spfileMYDBSTDBY.ora'
Step 6 - Create Audit Directories
[Node 1 - oracle user - Standby Cluster]
mkdir -p /opt/oracle/base/admin/MYDBSTDBY/adump

 [Node 2 - oracle user - Standby Cluster]
mkdir -p /opt/oracle/base/admin/MYDBSTDBY/adump
Step 7 - Start the instance, mount it and recover standby database. 
You can use any method to recover/restore.
One of the methods which I recommend is to use active database duplication. It's generally faster. 

 rman << EOF
connect TARGET sys/<pwd>@MYDBPROD ;
connect AUXILIARY sys/<pwd>@MYDBSTDBY;
DUPLICATE TARGET DATABASE
FOR STANDBY
FROM ACTIVE DATABASE
DORECOVER
NOFILENAMECHECK;


In the next blog I discuss on DG Broker Build

Popular Posts

© Copyright 2019 DBA to DA. . Powered by Blogger.