WORKLOAD MANAGEMENT IN 11g R2 RAC : LOAD BALANCING

will discuss about various types of load balancing techniques that can be configured in 11g R2 RAC for workload management.

In RAC we have multiple instances of a database running on multiple servers.

Workload management involves :

   – Failover : If connection to an instance fails, client should automatically connect to another instance

   – Load balancing : Workload should spread across various instances to obtain maximum performance/throughput

Failover can be

  – Connect Time Connection Failover (CTCF) while making initial connection ()

  - Run time connection failover (RTCF) after connection has been established or (transparent application failover (TAF)

     FAILOVER_METHOD = NONE, BASIC, PRECONNECT

     FAILOVER TYPE   = SESSION, SELECT

Load balancing can be :

  – Connect time load balancing (CTLB)

    . On client side

    . On server side

  –  Run time load balancing (RTLB)

TO configure failover/load balancing, tnsnames.ora should contain multiple listener addresses to connect to multiple instances e.g.

Multiple Listener addresses within a description: i.e. User is trying to connect to a service which is supported by multiple instances.

RAC=

(DESCRIPTION=

   (ADDRESS_LIST=

     (ADDRESS= (PROTOCOL=TCP) (HOST=node1-vip) (PORT=1521))

     (ADDRESS= (PROTOCOL=TCP) (HOST=node2-vip) (PORT=1521))

     (ADDRESS= (PROTOCOL=TCP) (HOST=node3-vip) (PORT=1521))

     (ADDRESS= (PROTOCOL=TCP) (HOST=node4-vip) (PORT=1521))

                      )

    (CONNECT_DATA= (SERVICE_NAME= RAC))

       )

In case SCAN is used, SCAN name is used in the address which resolves to 3 SCAN listeners.

e.g.

RAC=

(DESCRIPTION=

   (ADDRESS_LIST=

     (ADDRESS= (PROTOCOL=TCP) (HOST=cluster01-scan) (PORT=1521))

                      )

    (CONNECT_DATA= (SERVICE_NAME= RAC))

       )

In this post, I will discuss in detail about Load Balancing. To know more about failover , please click here.

LOAD BALANCING 

————–

Load balancing in RAC implies distributing the workload over multiple instances accessing the same physical database. Two kinds load balancing can be configured

  – Connect time load balancing (CTLB)

    . On client side

    . On server side

  – On server side – Run time load balancing (RTLB)

Connect time load balancing (CTLB) : This enables user to connect to one of the instances supporting the service. The connection stays with the same instance until the user disconnects orthe session is killed. It can be configured on the client side and/or server side.

- Connect time load balancing on client side: When a user session attempts to connect to the database, Oracle Net chooses an address specified in tnsnames.ora to connect to in a random order rather than sequential order. This has the effect of clients connecting through addresses which are picked up at random and no one address is overloaded. Its configuration is quite simple. You just need to set the parameter LOAD_BALANCE=ON in the client connection definition in tnsnames.ora. For example :

RAC=

(DESCRIPTION=

  (ADDRESS_LIST=

                       (LOAD_BALANCE=ON)

    (ADDRESS= (PROTOCOL=TCP) (HOST=node1-vip) (PORT=1521))

                       (ADDRESS= (PROTOCOL=TCP) (HOST=node2-vip) (PORT=1521))

    (ADDRESS= (PROTOCOL=TCP) (HOST=node3-vip) (PORT=1521))

    (ADDRESS= (PROTOCOL=TCP) (HOST=node4-vip) (PORT=1521))

                      )

   (CONNECT_DATA= (SERVICE_NAME= RAC)

       )

LOAD_BALANCE parameter is set to ON by default. When this parameter is set to ON, Oracle Net Services progresses through the list of listener addresses in a random sequence, balancing the load on several listeners. When it is set to OFF, the addresses are tried out sequentially until one succeeds.

When using SCAN in the connection definition, Oracle database randomly connects to one of the available SCAN listeners in a round robin fashion and balances the connections on the three scan listeners. Here is a sample tnsnames.ora for a RAC database using SCAN.

RAC=

(DESCRIPTION=

    (LOAD_BALANCE=ON)

    (ADDRESS= (PROTOCOL=TCP) (HOST=SCAN-HOSTNAME) (PORT=1521))

                  )

   (CONNECT_DATA= (SERVICE_NAME= RAC)

       )

Limitation of connect time load balancing : The connection stays with the same instance for the life of a session. If connection lasts a long time, it might be possible that load of current instance increases and some other less loaded instance might be preferable. In that case we would like the connection to switch the other more appropriate instance. This can be achieved by using Run time Load Balancing (RTLB).

- Connect time load balancing on server side: After a listener receives the connection request, it can forward the request to another instance based on the connect time load balancing goal (CLB_GOAL)specified for the service. CLB_GOAL can be :

LONG(Default) - used for application connections that are connected for a long period such as third party connection pools and SQL*Forms applications. In this case,

   . the listener will load balance on number of sessions

   . Run time load balancing goal will not be used in this case

SHORT - used for application connections that are short in duration. This should be used with connection pools integrated with the load balancing advisory. In this case, listener uses Load Balancing Advisory (LBA) to make the connection based on CPU utilization on the node.

Limitation of Connect time load balancing on client side : The listener has no idea if the session has been assigned to an endpoint whose corresponding database server is already overloaded. Hence, timeouts can occur if the node is heavily loaded and unable to respond quickly.Hence, to overcome this problem, server side connect time load balancing needs to be configured. It is useful to spread initial connection load among all listeners inthe cluster. Client may then be redirected based on server side load balancing.

Run Time (Server side) load Balancing (RTLB): In this case, the listener routes incoming client connections according to policies and based on the current service level provided by the database instances. The listener determines the connection distribution depending upon profile statistics that  are dynamically updated by PMON. The higher the load on the node, the more frequently PMON updates the load profile.Thus connections may be switched depending upon changes in cluster configuration, application wrokload  overworked nodes or hangs.

   The core of server side laod balancing id Dynamic service registration so that a services are registered with all the listeners. Since PMON on each node sends load profile to all the listeners with which the service is registered,  all the  listeners come to know about load profile of all the instances and hence the connection is forwarded to the most appropriate listener depending upon the goal of the run time load balancing.

  Run time load balancing is achieved using connection pools. Work requests are automatically balanced across the pool of connections.The connection allocation is based on the current performance level provided by the database instances as indicated by the LBA FAN events. This provides load balancing at the transaction level instead of load balancing at the time of initial connection. 

With server-side load balancing, the listener directs a connection request to the best instance currently providing the service by using the load balancing advisory.

Load Balancing Advisory

- is an advisory for balancing work across RAC instances

- Monitors workload activity for a service across all instances in the cluster

- Analyzes the service level for each instance based on defined metric goal

    Metric: service time (GOAL_SERVICE_TIME)

    Metric: throughput (GOAL_THROUGHPUT)

- Publishes FAN events recommending amount of work to be sent to each instance and data quality  flag

- Default is Off.

-  Directs work to where services are executing well and resources are available

- Adjusts distribution for different power nodes, different priority and shape workloads, changing demand

- Stops sending work to slow, hung, failed nodes early

How to configure server side load balancing:

1. set parameters LOCAL_LISTENER and REMOTE_LISTENER

2. set CLB_GOAL = SHORT for the service

3. set RTLB_GOAL for the service

1. set parameters LOCAL_LISTENER and REMOTE_LISTENER

LOCAL_LISTENER parameter should be set to name of the listener defined in the same node

REMOTE_LISTENER should be set to names of the listeners running on other nodes

For example in a 3 node setup

Host01 running instance orcl1

Host02 running instance orcl2

Host03 running instance orcl3

For host01,

local_listener  – (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP) HOST=host01-vip)(PORT=1521))))

remote_listener  – (DESCRIPTION=(ADDRESS_LIST=

                                 (ADDRESS=(PROTOCOL=TCP) HOST=host02-vip)(PORT=1521)

                                 (ADDRESS=(PROTOCOL=TCP) HOST=host03-vip)(PORT=1521))))

For host02,

local_listener  – (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP) HOST=host02-vip)(PORT=1521))))

remote_listener  – (DESCRIPTION=(ADDRESS_LIST=

                                 (ADDRESS=(PROTOCOL=TCP) HOST=host01-vip)(PORT=1521)

                                 (ADDRESS=(PROTOCOL=TCP) HOST=host03-vip)(PORT=1521))))

For host03,

local_listener  – (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP) HOST=host03-vip)(PORT=1521))))

remote_listener  – (DESCRIPTION=(ADDRESS_LIST=

                                 (ADDRESS=(PROTOCOL=TCP) HOST=host02-vip)(PORT=1521)

                                 (ADDRESS=(PROTOCOL=TCP) HOST=host01-vip)(PORT=1521))))

When we start the three instances the corresponding PMON processes get dynamically registered with all the listeners and start feeding listeners with load profile information. Now all the listeners come to know about load profile of all the instances and hence the connection is forwarded to the listener of the least loaded node.

When SCAN is used, remote_listener parameter should be set to SCAN name on all the nodes i.e.

remote_listener – myrac-cluster-scan:1521

So that PMON process of each instance registers the database services with the default listener on the local node and with each SCAN listener, which is specified by the REMOTE_LISTENER database parameter.

2. set CLB_GOAL = SHORT for the service

   EXECUTE DBMS_SERVICE.MODIFY_SERVICE

   (service_name => ‘sjob’ -

   , clb_goal => DBMS_SERVICE.CLB_GOAL_SHORT);

OR

   srvctl modify service

      -s orcl_serv database -d orcl

      -j SHORT   // connection load balancing goal {long|short}

3. set RTLB_GOAL for the service

A request for a connection is serviced by selecting a connection based on the service goal as determined by the Load Balancing Advisory. The service goal determines whether the connection provides best service quality, that is, how efficiently a single transaction completes, or best throughput, that is, how efficiently an entire job or long-running query completes.

For RTLB, we can define service level goal which will be used only if CLB_GOAL= SHORT

There are 3 options available

NONE – Default setting, you are not taking advantage of this feature

THROUGHPUT – Work requests are directed based on throughput.  THROUGHPUT should be used when the work in a service completes at homogenous rates.  An example is a trading system where work requests are similar lengths. Attempts to direct work requests according to throughput. The load balancing advisory analyzes the service level for each instance based on the service time and is based on the rate that work is completed in the service plus available bandwidth to the service. For example for the use of THROUGHPUT is for workloads such as batch processes,trading system work requests have similar lengths and  next job starts when the last job completes:

EXECUTE DBMS_SERVICE.MODIFY_SERVICE

 (service_name => ‘sjob’ -

  , goal => DBMS_SERVICE.GOAL_THROUGHPUT );

OR

srvctl modify  service

      -s orcl_serv database -d orcl

      -B throughput      // runtime connection load balancing goal { service_time|throughput | none}

SERVICE_TIME – Work requests are directed based on response time. SERVICE_TIME should be used when the work in a service completes at various rates.  In this case, Load balancing advisory data is based on elapsed time for work done in the service plus available bandwidth to the service. An example for the use of SERVICE_TIME is for workloads such as internet shopping where the rate of demand changes and work requests are of differing various lengths.:

EXECUTE DBMS_SERVICE.MODIFY_SERVICE

(service_name => ‘OE’ -

, goal => DBMS_SERVICE.GOAL_SERVICE_TIME -

);

OR

srvctl modify  service

      -s orcl_serv database -d orcl

      -B service_time      // runtime connection load balancing goal { service_time|throughput | none}

 You can see the goal settings for a service in the data dictionary and in the DBA_SERVICES, V$SERVICES, and V$ACTIVE_SERVICES views.

SUMMARY:

Workload management involves :

   – Failover : If connection to an instance fails, client should automatically connect to another instance

   – Load balancing : Workload should spread across various instances to obtain maximum performance/throughput

Load balancing can be :

  – Connect time load balancing (CTLB)

    . On client side

    . On server side

  –  Run time load balancing (RTLB)

TO configure failover/load balancing, tnsnames.ora should contain multiple listener addresses to connect to multiple instances

Connect time load balancing (CTLB) : This enables user to connect to one of the instances supporting the service. The connection stays with the same instance until the user disconnects orthe session is killed. It can be configured on the client side and/or server side.

- Connect time load balancing on client side: When a user session attempts to connect to the database, Oracle Net chooses an address specified in tnsnames.ora to connect to in a random order rather than sequential order. This has the effect of clients connecting through addresses which are picked up at random and no one address is overloaded. Its configuration is quite simple. You just need to set the parameter LOAD_BALANCE=ON in the client connection definition in tnsnames.ora.

Limitation of Connect time load balancing (CTLB) on client side : The listener has no idea if the session has been assigned to an endpoint whose corresponding database server is already overloaded.

- Connect time load balancing (CTLB) on server side: After a listener receives the connection request, it can forward the request to another instance based on the connect time load balancing goal (CLB_GOAL)specified for the service. CLB_GOAL can be : LONG or SHORT

LONG(Default) – In this case,

   . the listener will load balance on number of sessions

   . Run time load balancing goal will not be used in this case

SHORT - used for application connections that are short in duration. This should be used with connection pools integrated with the load balancing advisory. In this case, listener uses Load Balancing Advisory (LBA) to make the connection based on CPU utilization on the node.

Limitation of connect time load balancing : The connection stays with the same instance for the life of a session. If connection lasts a long time, it might be possible that load of current instance increases and some other less loaded instance might be preferable.

Run Time (Server side) load Balancing (RTLB): In this case, the listener routes incoming client connections according to policies and based on the current service level provided by the database instances. With server-side load balancing, the listener directs a connection request to the best instance currently providing the service by using the load balancing advisory.

To configure server side load balancing:

1. set parameters LOCAL_LISTENER and REMOTE_LISTENER

2. set CLB_GOAL = SHORT for the service

3. set RTLB_GOAL for the service

create of physical standby through active database

source and target database should be same configuration



primary





SQL> select name, open_mode, database_role, INSTANCE_NAME from v$database,v$instance;



NAME      OPEN_MODE            DATABASE_ROLE    INSTANCE_NAME

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

Ram    READ WRITE           PRIMARY          Ram





SQL> select force_logging from v$database;



FORCE_LOGGING

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

NO







SQL> ALTER DATABASE FORCE LOGGING;



Database altered.



SQL> select force_logging from v$database;



FORCE_LOGGING

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

YES





[oracle@rac1 ~]$ cd $ORACLE_HOME/dbs

[oracle@rac1 dbs]$ rm hc_apac.dat

[oracle@rac1 dbs]$ ls -ltr

total 20

-rw-r--r-- 1 oracle oinstall 2851 May 15  2009 init.ora

-rw-r----- 1 oracle oinstall   24 Jan  6 22:46 lkW5005PR

-rw-r----- 1 oracle oinstall 1536 Jan  6 22:47 orapww5005pr

-rw-r----- 1 oracle oinstall   41 Jan  6 22:48 initw5005pr.ora

-rw-rw---- 1 oracle oinstall 1544 Jan  6 22:48 hc_w5005pr.dat

[oracle@rac1 dbs]$





configure standby redolog on primary



SQL> set lines 180

col MEMBER for a60

select b.thread#, a.group#, a.member, b.bytes FROM v$logfile a, v$log b WHERE a.group# = b.group#;SQL> SQL>



   THREAD#     GROUP# MEMBER                                                            BYTES

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

         1          3 +DATA01/Ram/ONLINELOG/group_3.259.1018027407               209715200

         1          3 +FLASH01/Ram/ONLINELOG/group_3.1590.1018027409             209715200

         1          2 +DATA01/Ram/ONLINELOG/group_2.267.1018027407               209715200

         1          2 +FLASH01/Ram/ONLINELOG/group_2.1589.1018027409             209715200

         1          1 +DATA01/Ram/ONLINELOG/group_1.260.1018027407               209715200

         1          1 +FLASH01/Ram/ONLINELOG/group_1.1591.1018027409             209715200



6 rows selected.





ALTER DATABASE ADD standby logfile thread 1 group 4 ( '+DATA01','+FLASH01') SIZE 200m;



SQL> ALTER DATABASE ADD standby logfile thread 1 group 5 ('+DATA01','+FLASH01') size 200m;



Database altered.



SQL> ALTER DATABASE ADD standby logfile thread 1 group 6 ('+DATA01','+FLASH01') size 200m;



Database altered.



SQL>  ALTER DATABASE ADD standby logfile thread 1 group 7 ('+DATA01','+FLASH01') size 200m;



Database altered.





SQL> show parameter pfile;



NAME       TYPE         VALUE

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

spfile     string       +DATA/w5005pr/spfilew5005pr.ora



SQL> create pfile='/home/oracle/initw5005pr.ora.bkp' from spfile;



File created.



SQL> alter system set db_unique_name='Ram' scope=spfile;



System altered.



SQL> ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(Ram,RamSBY)' scope=both;



System altered.



SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_1='LOCATION=+FLASH01 VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=Ram' scope=both;



System altered.



SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=RamSBY LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=RamSBY' scope=both;



System altered.



SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_1=ENABLE scope=both;



System altered.



SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE scope=both;



System altered.



SQL> ALTER SYSTEM SET LOG_ARCHIVE_FORMAT='%t_%s_%r.arc' SCOPE=SPFILE;



System altered.



SQL> ALTER SYSTEM SET LOG_ARCHIVE_MAX_PROCESSES=30 scope=both;



System altered.



SQL> ALTER SYSTEM SET REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE SCOPE=SPFILE;



System altered.



SQL> ALTER SYSTEM SET fal_client=Ram scope=both;



System altered.



SQL> ALTER SYSTEM SET fal_server=RamSBY scope=both;



System altered.



SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;



System altered.



SQL> create pfile='/oraclebase/app/oracle/product/18.0.0/dbhome_1/dbs/initRam' from spfile;



File created.



SQL>





Copy the password file from the primary $ORACLE_HOME/dbs and rename it to the standby database name.

The username is required to be SYS and the password needs to be the same on the Primary and Standby.

The best practice for this is to copy the passwordfile as suggested.

The password file name must match the instance name/SID used at the standby site, not the DB_NAME





copy the initora file to standylocation and change it accordingly.



prod pfile

Ram.__data_transfer_cache_size=0

Ram.__db_cache_size=22817013760

Ram.__inmemory_ext_roarea=0

Ram.__inmemory_ext_rwarea=0

Ram.__java_pool_size=268435456

Ram.__large_pool_size=335544320

Ram.__oracle_base='/oraclebase/app/oracle'#ORACLE_BASE set from environment

Ram.__pga_aggregate_target=3422552064

Ram.__sga_target=26910654464

Ram.__shared_io_pool_size=536870912

Ram.__shared_pool_size=2885681152

Ram.__streams_pool_size=0

*.audit_file_dest='/oraclebase/app/oracle/admin/Ram/adump'

*.audit_trail='db'

*.compatible='18.0.0'

*.control_files='+DATA01/Ram/CONTROLFILE/current.261.1018026219','+FLASH01/Ram/CONTROLFILE/current.256.1018026219'#Restore Controlfile

*.db_block_size=8192

*.db_create_file_dest='+DATA01'

*.db_create_online_log_dest_1='+DATA01'

*.db_create_online_log_dest_2='+FLASH01'

*.db_name='Ram'

*.db_recovery_file_dest_size=100006m

*.db_recovery_file_dest=''

*.db_unique_name='Ram'

*.diagnostic_dest='/oraclebase/app/oracle'

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

*.fal_client='Ram'

*.fal_server='RamSBY'

*.local_listener='LISTENER_Ram'

*.log_archive_config='DG_CONFIG=(Ram,RamSBY)'

*.log_archive_dest_1='LOCATION=+FLASH01 VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=Ram'

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

*.log_archive_dest_state_1='ENABLE'

*.log_archive_dest_state_2='ENABLE'

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

*.log_archive_max_processes=30

*.nls_language='AMERICAN'

*.nls_territory='AMERICA'

*.open_cursors=300

*.pga_aggregate_target=3201m

*.processes=1280

*.remote_login_passwordfile='EXCLUSIVE'

*.sga_target=25602m

*.standby_file_management='AUTO'

*.star_transformation_enabled='TRUE'

*.undo_tablespace='UNDOTBS1'





standby pfile





RamSBY.__data_transfer_cache_size=0

RamSBY.__db_cache_size=22817013760

RamSBY.__inmemory_ext_roarea=0

RamSBY.__inmemory_ext_rwarea=0

RamSBY.__java_pool_size=268435456

RamSBY.__large_pool_size=335544320

RamSBY.__oracle_base='/oraclebase/app/oracle'#ORACLE_BASE set from environment

RamSBY.__pga_aggregate_target=3422552064

RamSBY.__sga_target=26910654464

RamSBY.__shared_io_pool_size=536870912

RamSBY.__shared_pool_size=2885681152

RamSBY.__streams_pool_size=0

*.audit_file_dest='/oraclebase/app/oracle/admin/RamSBY/adump'

*.audit_trail='db'

*.compatible='18.0.0'

*.control_files='+DATA01','+FLASH01'#Restore Controlfile

*.db_block_size=8192

*.db_create_file_dest='+DATA01'

*.db_create_online_log_dest_1='+DATA01'

*.db_create_online_log_dest_2='+FLASH01'

*.db_name='Ram'

*.db_recovery_file_dest_size=100006m

*.db_recovery_file_dest=''

*.db_unique_name='RamSBY'

*.diagnostic_dest='/oraclebase/app/oracle'

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

*.fal_client='RamSBY'

*.fal_server='Ram'

*.local_listener='LISTENER_RamSBY'

*.log_archive_config='DG_CONFIG=(RamSBY,Ram)'

*.log_archive_dest_1='LOCATION=+FLASH01 VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=RamSBY'

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

*.log_archive_dest_state_1='ENABLE'

*.log_archive_dest_state_2='DEFER'

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

*.log_archive_max_processes=30

*.nls_language='AMERICAN'

*.nls_territory='AMERICA'

*.open_cursors=300

*.pga_aggregate_target=3201m

*.processes=1280

*.remote_login_passwordfile='EXCLUSIVE'

*.sga_target=25602m

*.standby_file_management='AUTO'

*.star_transformation_enabled='TRUE'

*.undo_tablespace='UNDOTBS1'





***********************************************







mkdir -p /u01/app/oracle/admin/Ramsby/adump





startup nomount pfile='/oraclebase/app/oracle/product/18.0.0/dbhome_1/dbs/initRamSBY';









create spfile='+DATA01/RamSBY/spfileRamSBY.ora' from pfile='/oraclebase/app/oracle/product/18.0.0/dbhome_1/dbs/initRamSBY';









startup nomount force;



show parameter pfile



SELECT thread#, group#, sequence#, bytes, archived, status FROM v$standby_log order by thread#, group#;







--Example for two members

--alter database add standby logfile THREAD 1 group 5 ('D:\ORACLEXE\STANDBYREDO01A.log','D:\ORACLEXE\STANDBYREDO01B.log') SIZE 200M;


--Example for ASM
--alter database add standby logfile THREAD 1 group 7 ('+DATA(ONLINELOG)','+FRA(ONLINELOG)') SIZE 200M;



configure listener and tnsnames



srvctl modify scan_listener -p TCP:1523



srvctl modify listener -p TCP:1523



Verify connection ‘AS SYSDBA’ from Primary



Verify connection ‘AS SYSDBA’ from Standby



Run the duplicate from active database command from primary



rman target sys/ChangeMe2019@Ram auxiliary sys/ChangeMe2019@RamSBY

duplicate target database for standby from active database nofilenamecheck;

once completed


"alter database mount standby database;
database altered;"
alter database recover managed standby database disconnect from session;
alter system set log_archive_dest_state_2=defer;


on primary



select name, open_mode, database_role, INSTANCE_NAME from v$database,v$instance;


SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

SQL> alter system switch logfile;



System altered.



SQL> alter system switch logfile;



System altered.



SQL> select max(sequence#) from v$archived_log where archived='YES';



MAX(SEQUENCE#)

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

            10



standyb





SQL> select name, open_mode, database_role, INSTANCE_NAME from v$database,v$instance;



NAME      OPEN_MODE            DATABASE_ROLE    INSTANCE_NAME

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

ddd   READ ONLY WITH APPLY PHYSICAL STANDBY standy



SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;



 SEQUENCE# FIRST_TIM NEXT_TIME

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

         6 07-JAN-16 07-JAN-16

         7 07-JAN-16 07-JAN-16

         8 07-JAN-16 07-JAN-16

         9 07-JAN-16 07-JAN-16

        10 07-JAN-16 07-JAN-16



SQL> select max(sequence#) from v$archived_log where applied='YES';



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



SQL> ALTER SYSTEM SET log_archive_dest_state_2 = DEFER;



System altered.



SQL> ALTER SYSTEM SET log_archive_dest_state_2 =enable';

ALTER SYSTEM SET log_archive_dest_state_2 =enable'

                                                 *

ERROR at line 1:

ORA-01756: quoted string not properly terminated





SQL> ALTER SYSTEM SET log_archive_dest_state_2 = ENABLE;



System altered.







[oracle@rac2 ~]$ which srvctl

/u01/app/oracle/product/11.2.0/db_1/bin/srvctl

[oracle@rac2 ~]$ srvctl add database -d w5005prg -o /u01/app/oracle/product/11.2.0/db_1/ -r physical_standby -s 'READ ONLY'

[oracle@rac2 ~]$ srvctl start database -d w5005prg



[oracle@rac2 ~]$ /u01/app/11.2.0/grid/bin/crsctl stat res -t

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

NAME           TARGET  STATE        SERVER                   STATE_DETAILS

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

Local Resources

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





applying retention policy for rman on both primary and standby



CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY;

or


CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON STANDBY;



On Primary Site :
Connect with sys and :
SQL>Alter system set  "_log_deletion_policy" = 'ALL' scope=both;

The Archivelogs will be deleted on stanby site when a "Backup Database occurs in Primary Site" !!! To be confirmed



SELECT DEST_ID,dest_name,status,type,srl,RECOVERY_MODE FROM V$ARCHIVE_DEST_STATUS;

dataguard log shipping problem

•  Print 
•  Email

How to stop and start the Log shipping? With this procedure, we can simulate a disaster crash at the main site.

For our example, we use PRD and PRD-STBY to differentiate the two sites, and SAP is installed in Windows.

1 - Stop the log shipping at the PRD Site

C:\>sqlplus / as sysdba

*** Do Some validations Infos
SQL> select name,open_mode from v$database;
PRD       READ WRITE

SQL> select max(sequence#) from v$log_history;
         54275

SQL> alter system switch logfile;

SQL> select max(sequence#) from v$log_history;
         54276

SQL> select status, DEST_NAME, DESTINATION from v$archive_dest where status = 'VALID';
VALID      LOG_ARCHIVE_DEST_1    E:\oracle\PRD\saparch
VALID      LOG_ARCHIVE_DEST_2    prd_standby

SQL> show parameter LOG_ARCHIVE_DEST_2;
log_archive_dest_2     string      Service=prd_standby lgwr async
                                    VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
                                    db_unique_name=standby
SQL>

NOW DEACTIVATE THE LOG SHIPPING!!!!

SQL> alter system set log_archive_dest_state_2=defer scope=both;
System altered.

SQL> show parameter log_archive_dest_state_2
NAME                                 TYPE        VALUE
------------------------------------ ----------- -----
log_archive_dest_state_2             string      DEFER

SQL> select max(sequence#) from v$log_history;
         54276

SQL> alter system switch logfile;

SQL> select max(sequence#) from v$log_history;
         24277

You can check the Alert Log of the DR Side, you will see which last is applied and see Network Error

2 - Login to the PRD-STBY Site

C:\> NOTEPAD E:\oracle\PRD\saptrace\background\alert_PRD.log

RFS[2]: Archived Log: 'E:\ORACLE\PRD\SAPARCH\PRD_54276.1.834712720.DBF'
Primary database is in MAXIMUM PERFORMANCE mode
Sun Jun 19 12:33:32 2013
Media Recovery Log E:\ORACLE\PRD\SAPARCH\PRD_54268.1.834712720.DBF
Media Recovery Delayed for 240 minute(s) (thread 1 sequence 54269)
Sun Jun 19 12:51:27 2013
RFS[2]: Archived Log: 'E:\ORACLE\PRD\SAPARCH\PRD_54277.1.834712720.DBF'
RFS[2]: Possible network disconnect with primary database

3 -  RE-START LOG SHIPPING

3.1 – Validate the Status of Standby Database.

3.2 – Validate the last available redo-log

DIR E:\oracle\PRD\saparch\*dbf (You will see only the last shipped)

3.3 – Activate the Log Shipping (Real Production Site-PRD)

C:\>sqlplus / as sysdba

*** Do Some validations Infos

SQL> select name,open_mode from v$database;
NAME      OPEN_MODE
--------- ----------
PRD       READ WRITE

SQL> select max(sequence#) from v$log_history;

         54279

SQL> select status, DEST_NAME, DESTINATION from v$archive_dest where status = 'VALID';

STATUS     DEST_NAME             DESTINATION
---------  ----------            ----------------------------------
VALID      LOG_ARCHIVE_DEST_1    E:\oracle\PRD\saparch

SQL> show parameter LOG_ARCHIVE_DEST_2;

NAME                   TYPE        VALUE
---------------------- ----------- ---------------------------------------------
log_archive_dest_2     string      Service=prd_standby lgwr async
                                    VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
                                    db_unique_name=standby

SQL> show parameter log_archive_dest_state_2
NAME                                 TYPE        VALUE
------------------------------------ ----------- -----
log_archive_dest_state_2             string      DEFER

NOW ACTIVATE THE LOG SHIPPING!!!!

SQL> alter system set log_archive_dest_state_2=enable scope=both;

SQL> show parameter log_archive_dest_state_2
NAME                                 TYPE        VALUE
------------------------------------ ----------- -----
log_archive_dest_state_2             string      ENABLE


SQL> select max(sequence#) from v$log_history;
         54279

SQL> alter system switch logfile;

SQL> select max(sequence#) from v$log_history;
         54280

3.4 – Validate the shipping logs and appliance in theStandby Site (PRD-STBY)

DIR E:\oracle\PRD\saparch\*dbf

3.5 – Rebuild Synchronization without Delay (You are logged into the PRD-STBY Site)

C:\> SQLPLUS / AS SYSDBA
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE cancel;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

3.6 – Validate the Status of Standby Database. (PRD_STBY)
 

3.7 – Rebuild Synchronization With 180mn Delay (3 hours for example)

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE cancel;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DELAY 180 DISCONNECT FROM SESSION;
EXIT

And you are done!

ora -600 internal error and database connectivity issue.

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-00554: initialization of internal recovery manager package failed

RMAN-04005: error from target database:

ORA-00600: internal error code, arguments: [ksm_mga_pseg_cbk_attach:map_null], [], [], [], [], [], [], [], [], [], [], []

ORA-27300: OS system dependent operation:open failed with status: 2

ORA-27301: OS failure message: No such file or directory

ORA-27302: failure occurred at: sskgm_mga_at

09-04-19 06:20:05 Alert file: /tmp/PXRPDB.rman_archivelog_backup.error_found.txt

09-04-19 06:20:05 Errors found

crsctl status res -t init

check the crsd log

/oraclebase/app/oracle/product/18.0.0/grid/log/omexd001/crsd

oswatcher

ps -ef |grep osw

Below 1 and 2 need to executed as root user.

1)OS watcher process.

Node 1

+++++++

[oracle@wdbfffadm01 oswbb]$ pwd

/home/oracle/oswbb/oswbb

Cd /home/oracle/oswbb/oswbb  --root user

./startOSW.sh

Output file

# cd /opt/oracle.oswatcher/osw/archive

Run for 1 hour and issue

Cd /home/oracle/oswbb/oswbb  --root user

./stopOSW.sh

Node 2:

pxdwdbaffffdm02 :

[oracle@wdbadm02 oswbb]$ pwd

/home/oracle/oswbb

Cd /home/oracle/oswbb

./startOSW.sh

Output file in

 cd /opt/oracle.oswatcher/osw/archive

cd /home/oracle/oswbb

./stopOSW.sh

ORA-20001: Statistics Advisor: Invalid task name

ORA-20001: Statistics Advisor: Invalid task name for the current user

On checking the alert log file of database i am getting the following error:

Error

2018-03-07T01:38:40.435087-06:00
Errors in file E:\ORACLE\diag\rdbms\ic\ic\trace\ic_j000_532.trc:
ORA-12012: error on auto execute of job "SYS"."ORA$AT_OS_OPT_SY_5501"
ORA-20001: Statistics Advisor: Invalid task name for the current user
ORA-06512: at "SYS.DBMS_STATS", line 47207
ORA-06512: at "SYS.DBMS_STATS_ADVISOR", line 882
ORA-06512: at "SYS.DBMS_STATS_INTERNAL", line 20059
ORA-06512: at "SYS.DBMS_STATS_INTERNAL", line 22201
ORA-06512: at "SYS.DBMS_STATS", line 47197

Cause:
Issue occurred while creating the database DBCA, the advisory package is not created properly. This issue is happening because of the unavailability of the Stats Advisor Tasks from the created database.

Solution

1. Connect with the SYSDBA privilege user:

2. Run the following query for checking advisory package:

col name for a30
select name, ctime, how_created
from sys.wri$_adv_tasks
where owner_name = 'SYS'
and name in ('AUTO_STATS_ADVISOR_TASK','INDIVIDUAL_STATS_ADVISOR_TASK');

no rows selected
3. Initialize the package with following commands.

EXEC dbms_stats.init_package();

4. Verify the package create. It will fixed the issue.

col name for a30
select name, ctime, how_created
from sys.wri$_adv_tasks
where owner_name = 'SYS'
and name in ('AUTO_STATS_ADVISOR_TASK','INDIVIDUAL_STATS_ADVISOR_TASK');

NAME                           CTIME     HOW_CREATED                            
------------------------------ --------- --------------       
AUTO_STATS_ADVISOR_TASK        05-DEC-18 CMD                                    
INDIVIDUAL_STATS_ADVISOR_TASK  05-DEC-18 CMD 

If the query based on "where owner_name = 'SYS'" condition does not show any rows but the error continues, please change the query as shown below to

see if a non-SYS user like SYSTEM owns those tasks for some reason:

$ sqlplus / as sysdba

select name, ctime, how_created,OWNER_NAME from sys.wri$_adv_tasks
where name in ('AUTO_STATS_ADVISOR_TASK','INDIVIDUAL_STATS_ADVISOR_TASK');




If the OWNER is a non-SYS user, you have to drop the tasks as that user first and then try to solution mention in the Note again.

This was a case for one customer.

For example:

--- Connect as SYSTEM, for example, if that user owned the tasks and non SYS for some reason

SQL> conn system/&password


DECLARE
v_tname VARCHAR2(32767);
BEGIN
v_tname := 'AUTO_STATS_ADVISOR_TASK';
DBMS_STATS.DROP_ADVISOR_TASK(v_tname);
END;
/

SQL> DECLARE
v_tname VARCHAR2(32767);
BEGIN
v_tname := 'INDIVIDUAL_STATS_ADVISOR_TASK';
DBMS_STATS.DROP_ADVISOR_TASK(v_tname);
END;
/


connect / as sysdba
EXEC DBMS_STATS.INIT_PACKAGE();

What is a Cloud Architect?

Cloud computing architecture refers to the components and subcomponents required for cloud computing. These components typically consist of a front end platform, back end platforms, a cloud-based delivery, and a network. Combined, these components make up cloud computing architecture. Cloud solutions design is based on architectural procedures and methods that have been developed over the last 20 or so years.

A Cloud Architect is responsible for converting the technical requirements of a project into the architecture and design that will guide the final product. Often, Cloud Architects are also responsible for bridging the gaps between complex business problems and solutions in the cloud. Other members of a technology team, including DevOps engineers and developers, work with the Cloud Architect to ensure that the right technology or technologies are being built.