DOYENSYS Knowledge Portal




We Welcome you to visit our DOYENSYS KNOWLEDGE PORTAL : Doyensys Knowledge Portal




Friday, January 20, 2017

Add,drop and rebalance ASM diskgroups

Adding Disks to a Disk Group

ALTER DISKGROUP DATA_DG ADD DISK '/devices/DiskA1’,’/device/DiskB1';

 Dropping Disks and Disk Groups

ALTER DISKGROUP DATA_DG DROP DISK DiskA1;
DROP DISKGROUP DATA_DG INCLUDING CONTENTS;
Note:
· DROP DISKGROUP statements requires the instance to be in MOUNT state.

Rebalance Disk Group

· ASM rebalance a disk group automatically, whenever we add or remove disks form disk group.
· Disk groups can be rebalanced manually using the REBALANCE clause of the ALTER DISKGROUP statement.
· If the POWER clause is omitted the ASM_POWER_LIMIT parameter value is used.

Example :

SQL> ALTER DISKGROUP DATA_DG REBALANCE POWER 5;  [By Default is 1]

SQL> select * from v$asm_operation;


GROUP_NUMBER OPERA STAT      POWER     ACTUAL      SOFAR   EST_WORK   EST_RATE
------------ ----- ---- ---------- ---------- ---------- ---------- ----------
           1 REBAL WAIT          5          0          0          0

Thursday, January 19, 2017

Permission access denied in APEX with companion

Log file detail :-

ORA-1017 ORA-01017: invalid username/password; logon denied\n


cd $ORACLE_HOME/Apache/modplsql/conf

$ vim dads.conf   

Alias /i/ "/u02/app/oracle/product/10.2.0/ias/Apache/Apache/images/"

AddType text/xml xbl
AddType text/x-component htc

<Location /pls/apex>
  SetHandler pls_handler
  Order deny,allow
  Allow from all
  AllowOverride                 None
  PlsqlDatabaseUsername         APEX_PUBLIC_USER
  PlsqlDatabasePassword         oracle
  PlsqlDatabaseConnectString    192.168.1.20:1521:apex
  PlsqlAuthenticationMode       Basic
  PlsqlDefaultPage              apex
  PlsqlDocumentTablename        wwv_flow_file_objects$
  PlsqlDocumentPath             docs
  PlsqlDocumentProcedure        wwv_flow_file_mgr.process_download
  PlsqlNLSLanguage              AMERICAN_AMERICA.AL32UTF8
</Location>

Change the password in dads.conf file and in database level

Solution:-

SQL >ALTER USER APEX_PUBLIC_USER  IDENTIFIED BY oracle;

Wednesday, January 18, 2017

Steps to Backup And Restore ASM Metadata

Steps to Backup And Restore ASM Metadata
--------------------------------------------------------------

Set the environment to ASM using .oraenv
Issue asmcmd to start the ASM client

The md_backup command makes a copy of the metadata for one or more disk groups. The summary usage is shown below.

     md_backup [-b location_of_backup] [-g dgname [-g dgname …]]

An example of the command is shown below. The resulting file contains all the metadata needed to recreate the ASM setup.

     ASMCMD [+] > md_backup -b /tmp/backup.txt -g data

     Disk group to be backed up: DATA

     ASMCMD [+] >

RESTORE METADATA

The md_restore command allows a restore of a disk group from the metadata created by the md_backup command. It also allows a certain amount of manipulation of the final disk groups during the restore. The summary usage is shown below.

     md_restore -b <backup_file> [-li]
                       [-t (full)|nodg|newdg] [-f <sql_script_file>]
                       [-g '<diskgroup_name>,<diskgroup_name>,...']
                       [-o '<old_diskgroup_name>:<new_diskgroup_name>,...']

A straight restore of the backup taken previously is shown below.

     ASMCMD [+] > md_restore -b /tmp/backup.txt -t full -g data

ASM REBALANCE AND ASM_POWER_LIMIT

ASM ha the ability to add and remove disks/luns from a diskgroup with no down time and an automatic rebalancing of the data on the luns.  
To remove a lun, use the following command:

alter diskgroup dgroup1 drop disk asmdisk01;

10G R2

The init.ora parameter ASM_POWER_LIMIT is used to influence the throughput and speed of the rebalance operation. The range of values for ASM_POWER_LIMIT is 0–11, where a value of 11 is full throttle and a value of 1 (the default) is low speed.A value of 0, which turns off automatic rebalance, should be used with caution.


If the POWER clause is not specified in an ALTER DISKGROUP command, or when rebalance is implicitly invoked by adding or dropping a disk, the rebalance power defaults to the value of the ASM_POWER_LIMIT initialization parameter. You can adjust this parameter dynamically. The higher the limit, the faster a rebalance operation may complete. Lower values cause rebalancing to take longer, but consume fewer processing and I/O resources. This leaves these resources available for other applications, such as the database.

The V$ASM_OPERATION view provides information that can be used for adjusting ASM_POWER_LIMIT and the resulting power of rebalance operations. The V$ASM_OPERATION view also gives an estimate in the EST_MINUTES column of the amount of time remaining for the rebalance operation to complete. You can see the effect of changing the rebalance power by observing the change in the time estimate.

You can manually rebalance the files in a disk group using the REBALANCE clause of the ALTER DISKGROUP statement.This will alter the power limit for any new operations, but not existing ones. To change an existing operation, you must directly alter the power limit on the diskgroup involved in the operation:

The POWER clause of the ALTER DISKGROUP…REBALANCE statement specifies the degree of parallelization, and thus the speed of the rebalance operation. It can be set to a value from 0 to 11. A value of 0 halts a rebalancing operation until the statement is either implicitly or explicitly reinvoked.

If you want the ALTER DISKGROUP…REBALANCE command to wait until the rebalance operation is complete before returning, you can add the WAIT keyword to the REBALANCE clause. This is especially useful in scripts.

The command also accepts a NOWAIT keyword, which invokes the default behavior of conducting the rebalance operation asynchronously. You can interrupt a rebalance running in wait mode by typing CTRL-C on most platforms. This causes the command to return immediately with the message ORA-01013: user requested cancel of current operation, and to continue the rebalance operation asynchronously.

11G R2
The ASM_POWER_LIMIT initialization parameter specifies the default power for disk rebalancing in a disk group. The range of values is 0 to 1024. The default value is 1. A value of 0 disables rebalancing. Higher numeric values enable the rebalancing operation to complete more quickly, but might result in higher I/O overhead and more rebalancing processes.

For disk groups that have the disk group ASM compatibility set to less than 11.2.0.2, the operational range of values is 0 to 11 inclusive. If the value for ASM_POWER_LIMIT is larger than 11, a value of 11 is used for these disk groups.

Oracle  recommendation set asm_power_limit value 4 on Exadata Database Machine ,and the value can  impact of the application due to the the ASM rebalance generated Io loss minimization on Exadata.
The big advantage to being able to set the power limit to 0 comes when it is necessary to add/drop several disks at the same time. If the power limit is already set, then each add/drop will be serialized and each operation will have to complete before the next can begin. If, however, the power limit is set to 0 first, then submit all the add/drop operations and finally set the power limit to a non-zero number for the disk group, the operations are paralellized and run at the same time.

STEPS TO MULTIPLEX CONTROL FILE IN ASM

For single-instance

1, make sure asm diskgroup is avaliable

Select name,total_mb,free_mb from v$asm_diskgroup where state='CONNECTED';
2, check current controlfile and using spfile

show parameter control_files
show parameter pfile
3, add new controlfile to spfile

alter system set control_files='[original file]','[asm diskgroup name where new control file will stored]' scope=spfile ;

for example:
alter system set control_files='+OLD/TESTDB/controlfile/current.258.798205861','+NEW_DATA' scope=spfile ;
4, restart oracle database instance, shutdown and startup in mount (not ASM instance)

shutdown immediate
stratup nomount
5, Dumplicate controlfile to new path in spfile using rman

rman target /

rman> restore controlfile from '[original file full name]' ;  exmaple: '+SYSTEM/TESTDB/controlfile/current.258.798205861'
Note: that the command prints the name of the new created file in new ASM diskgroup(my case is +NEW_DATA)

6, Modify the control_file parameter with the complete path of the new file

alter system set control_files='+OLD/TESTDB/controlfile/current.258.798205861','[ full name of the new controlfile generated in the previous step ]' scope=spfile sid='*';
new control file name like this “+NEW_DATA/TESTDB/controlfile/current.258.798205861”

7, restart instance and verify controlfile

shutdown immediate
startup
show parameter control
Another way on RAC

1). Identify the location of the current controlfile::

SQL> select name from v$controlfile;
2) For can duplicate our control file our database must be down. So I am going to close my database first:

srvctl stop database -d TESTDB
3) After my database down. I am loginning one of the my node. In my case I am loginning node 1:

On node1:
Set DB env. than:

sqlplus / as sysdba
startup nomount
4) On node1 set ASM env. than check your current controlfile:

# asmcmd -p
ASMCMD> cd ORADATA/RACTEST/CONTROLFILE/
ASMCMD [+ORADATA/RACTEST/controlfile] > ls
Current.240.737948655
5) On node1 Set DB env. Use RMAN to duplicate the controlfile:

rman target /
RMAN> restore controlfile to '+ORADATA' from '+ORADATA/RACTEST/controlfile/current.240.737948655' ; # this will create 2th controlfile
note:
We are only specifying the name of the diskgroup, so Oracle will create an OMF (Oracle Managed File). Use ASMCMD or sqlplus to identify the name assigned to the controlfile or rman restore log .

6) Checking those file create or not follow setp 4 and use ls command

# asmcmd -p
ASMCMD> cd ORADATA/RACTEST/CONTROLFILE/
ASMCMD [+ORADATA/RACTEST/controlfile] > ls
Current.240.737948655
CONTROLFILE UNPROT FINE AUG 03 18:00:00 Y current.1026.790368247
note:
As you can see We have new 1 controlfile “current.1026.790368247”

7) On node1 Set DB env. Modify the control_file parameter in spifle .than:

sqlplus “/as sysdba”

SQL> alter system set control_files='+ORADATA/RACTEST/controlfile/current.240.737948655','+ORADATA/RACTEST/controlfile/current.1026.790368247' scope=spfile sid='*';

SQL> shutdown immediate
8) Start your db

srvctl start database -d RACTEST

Steps to enable recyclebin in oracle 10 & 11g:


SQL> SHOW PARAMETER RECYCLEBIN

NAME                                 TYPE        VALUE
------------------------------------------------------------
recyclebin                           string      on

To turn ON or OFF the RECYCLEBIN

10g database:
----------------

SQL> ALTER SYSTEM SET recyclebin = OFF;

System altered.

SQL> ALTER SYSTEM SET recyclebin = ON;

System altered.

11g database:
-----------------

SQL> ALTER SYSTEM SET recyclebin = OFF DEFERRED;

System altered.

SQL> ALTER SYSTEM SET recyclebin = ON DEFERRED;

System altered.


DEFERRED is used because it takes effect in new sessions in the database.

case :


SQL> ALTER SYSTEM SET recyclebin = OFF;

ALTER SYSTEM SET recyclebin = OFF
*
ERROR at line 1:
ORA-02096: specified initialization parameter is not modifiable with this option


To enable the recycle bin for a session:
 SQL> ALTER SESSION SET recyclebin = ON;
 To disable the recycle bin for a session:
SQL> ALTER SESSION SET recyclebin = OFF;

            Query to check the cpu usage for active sessions and show cpu usage in seconds.


Query
---------

SELECT
   s.username,
   t.sid,
   s.serial#,
   SUM(VALUE/100) as "cpu usage (seconds)"
FROM
   v$session s,
   v$sesstat t,
   v$statname n
WHERE
   t.STATISTIC# = n.STATISTIC#
AND
   NAME like '%CPU used by this session%'
AND
   t.SID = s.SID
AND
   s.status='ACTIVE'
AND
   s.username is not null
GROUP BY username,t.sid,s.serial#
             


                         Query to find the how many Receipt has been printer in a day.


Query
---------



 SELECT COUNT (*) "Receipts_So_Far"
  FROM FND_CONC_REQ_SUMMARY_V
 WHERE PHASE_CODE = 'C' AND (NVL (request_type, 'X') != 'S')
       AND ( (DESCRIPTION LIKE 'Receipt Print for Receipt%'
              OR (DESCRIPTION IS NULL
                  AND USER_CONCURRENT_PROGRAM_NAME LIKE
                         ' Print for Receipt%')))
       AND (TRUNC (request_date)) >= TRUNC (SYSDATE)

Database size growth in day,week and month wise.

SET LINESIZE 200
SET PAGESIZE 200
COL "Database Size" FORMAT a13
COL "Used Space" FORMAT a11
COL "Used in %" FORMAT a11
COL "Free in %" FORMAT a11
COL "Database Name" FORMAT a13
COL "Free Space" FORMAT a12
COL "Growth DAY" FORMAT a11
COL "Growth WEEK" FORMAT a12
COL "Growth DAY in %" FORMAT a16
COL "Growth WEEK in %" FORMAT a16
SELECT
(select min(creation_time) from v$datafile) "Create Time",
(select name from v$database) "Database Name",
ROUND((SUM(USED.BYTES) / 1024 / 1024 ),2) || ' MB' "Database Size",
ROUND((SUM(USED.BYTES) / 1024 / 1024 ) - ROUND(FREE.P / 1024 / 1024 ),2) || ' MB' "Used Space",
ROUND(((SUM(USED.BYTES) / 1024 / 1024 ) - (FREE.P / 1024 / 1024 )) / ROUND(SUM(USED.BYTES) / 1024 / 1024 ,2)*100,2) || '% MB' "Used in %",
ROUND((FREE.P / 1024 / 1024 ),2) || ' MB' "Free Space",
ROUND(((SUM(USED.BYTES) / 1024 / 1024 ) - ((SUM(USED.BYTES) / 1024 / 1024 ) - ROUND(FREE.P / 1024 / 1024 )))/ROUND(SUM(USED.BYTES) / 1024 / 1024,2 )*100,2) || '% MB' "Free in %",
ROUND(((SUM(USED.BYTES) / 1024 / 1024 ) - (FREE.P / 1024 / 1024 ))/(select sysdate-min(creation_time) from v$datafile),2) || ' MB' "Growth DAY",
ROUND(((SUM(USED.BYTES) / 1024 / 1024 ) - (FREE.P / 1024 / 1024 ))/(select sysdate-min(creation_time) from v$datafile)/ROUND((SUM(USED.BYTES) / 1024 / 1024 ),2)*100,3) || '% MB' "Growth DAY in %",
ROUND(((SUM(USED.BYTES) / 1024 / 1024 ) - (FREE.P / 1024 / 1024 ))/(select sysdate-min(creation_time) from v$datafile)*7,2) || ' MB' "Growth WEEK",
ROUND((((SUM(USED.BYTES) / 1024 / 1024 ) - (FREE.P / 1024 / 1024 ))/(select sysdate-min(creation_time) from v$datafile)/ROUND((SUM(USED.BYTES) / 1024 / 1024 ),2)*100)*7,3) || '% MB' "Growth WEEK in %",
ROUND(((SUM(USED.BYTES) / 1024 / 1024 ) - (FREE.P / 1024 / 1024 ))/(select sysdate-min(creation_time) from v$datafile)*30,2) || ' MB' "Growth MONTH",
ROUND((((SUM(USED.BYTES) / 1024 / 1024 ) - (FREE.P / 1024 / 1024 ))/(select sysdate-min(creation_time) from v$datafile)/ROUND((SUM(USED.BYTES) / 1024 / 1024 ),2)*100)*30,3) || '% MB' "Growth MONTH in %"
FROM (SELECT BYTES FROM V$DATAFILE
UNION ALL
SELECT BYTES FROM V$TEMPFILE
UNION ALL
SELECT BYTES FROM V$LOG) USED,
(SELECT SUM(BYTES) AS P FROM dba_free_space) FREE
GROUP BY FREE.P;

11g Runinstaller Fails with Error: Java.Lang.Unsatisfiedlinkerror libmawt.so libxext.so.0

11g Runinstaller Fails with Error: Java.Lang.Unsatisfiedlinkerror libmawt.so libxext.so.0 or libmawt.so: libXext.so.6 (Doc ID 1366997.1)
SYMPTOMS
While installing Oracle Database 11.2.0.3 on RHEL 6.2 Linux x86-64 the following error is observed:
$ ./runInstaller
..
Preparing to launch Oracle Universal Installer from /tmp/OraInstall2014-01-02_10-33-37AM. Please wait ..$ Exception in thread "main" java.lang.UnsatisfiedLinkError: /tmp/OraInstall2014-01-02_10-33-37AM/jdk/jre/lib/amd64/xawt/libmawt.so: libXext.so.6: wrong ELF class: ELFCLASS32
       at java.lang.ClassLoader$NativeLibrary.load(Native Method)
       at java.lang.ClassLoader.loadLibrary0(ClassLoader.java:1753)
       at java.lang.ClassLoader.loadLibrary(ClassLoader.java:1649)
       at java.lang.Runtime.load0(Runtime.java:769)
       at java.lang.System.load(System.java:968)
       at java.lang.ClassLoader$NativeLibrary.load(Native Method)

CAUSE
Linux:   Library libXext.so.6 is missing
This is possibly due to the package providing the library being corrupt or not installed

SOLUTION
For Linux:
1. Confirm package libXext is installed. When installing 11.2, 64 bit on Linux 64 bit, ensure libXext  64 bit package is installed.And when installing 11.2, 32 bit client on Linux 32 bit /Linux 64 bit then ensure libXext 32 bit package is installed.Run the following Linux command to check if the package is missing:

$ rpm --query --whatprovides 'libXext.so.6'
2.  Re-try invoking the runInstaller (OUI) to install Oracle database 11.2
./runInstaller

Tuesday, January 17, 2017

Creating Cascading standby database

 A cascading standby database is a standby database that receives its redo logs from another standby database, not from the original primary database.

Primary Database      ----- >    Cascading Standby Database     -----> Cascaded Standby Database
(FEMP01 – primary.na)            (DRFEMP01 – standby.na)               (FEMCSD – csdstby.na)       


High Level Steps
·         Create a pfile for the cascading standby FEMCSD.
·         Copy the password file from the DRFEMP01 and rename it to FEMCSD.
·         Create the listener and enter the TNS Entries in the tnsnames.ora
·         Add/modify the log_archive_dest_2 parameter in the Primary(FEMP01) and DR(DRFEMP01) instance
·         Stop the recovery process in the DR database (DRFEMP01)
·         Start the active duplicate standby creation
Low Level Steps
1.       Enable the log_archive_dest_2_state in DRFEMP01.
2.       Start the MRP process in FEMCSD
3.       Enable the monitoring scripts for FEMCSD


a.       Create a pfile for the cascading standby FEMCSD.
*.STANDBY_FILE_MANAGEMENT='AUTO'
*.FAL_SERVER='DRFEMP01'
*.FAL_CLIENT='FEMCSD'
log_archive_config='dg_config=(FEMP01,DRFEMP01,FEMCSD)'
log_archive_dest_1='location=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ONLINE_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=FEMCSD'
log_archive_dest_2='service=DRFEMP01valid_for=(STANDBY_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=DRFEMP01'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
DB_FILE_NAME_CONVERT='+IMPRD_DATA_DG/DRFEMP01','+IMTST_DATA_DG/FEMCSD'
LOG_FILE_NAME_CONVERT='+IMPRD_DATA_DG/DRFEMP01/ONLINELOG','+IMTST_DATA_DG/FEMCSD/ONLINELOG'

b.      Copy the password file from the DRFEMP01 and rename it to FEMCSD.
c.       Create the listener and enter the TNS Entries in the tnsnames.ora
LISTENER_FEMCSD =
  (ADDRESS_LIST=
        (ADDRESS=
         (PROTOCOL=TCP) (HOST=oh61g.example.com) (PORT=1530))
  )
SID_LIST_LISTENER_FEMCSD =
  (SID_LIST =
    (SID_DESC =
      (ORACLE_HOME= /im/tironi/db/FEMCSD/11.2.0.4)
      (SID_NAME = FEMCSD)
    )
  )

TNS ENTRY
FEMCSD =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = oh61g.example.com)(PORT = 1530))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
     (SERVICE_NAME = FEMCSD2)
    )
  )
d.      Add/modify the log_archive_dest_2 parameter in the Primary(FEMP01) and DR(DRFEMP01) instance,
Primary (FEMP01)

alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(FEMP01,DRFEMP01,FEMCSD)' scope=both;
alter system set LOG_ARCHIVE_DEST_2='SERVICE=DRFEMP01 LGWR SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=DRFEMP01';
alter system set LOG_ARCHIVE_DEST_3='SERVICE=FEMCSD LGWR SYNC VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE) DB_UNIQUE_NAME=FEMCSDS'scope=both;
alter system set LOG_ARCHIVE_DEST_STATE_3=DEFER;(This parameter will remain in DEFER state permanently)

DR (DRFEMP01)

alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(FEMP01,DRFEMP01,FEMCSD)' scope=both;
alter system set LOG_ARCHIVE_DEST_2='service=FEMP01valid_for=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=FEMP01';
alter system set LOG_ARCHIVE_DEST_3='SERVICE=FEMCSD LGWR SYNC VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE) DB_UNIQUE_NAME=FEMCSD'scope=both;
alter system set LOG_ARCHIVE_DEST_STATE_3=DEFER;(This parameter will remain in DEFER state permanently)

e.      Create necessary directories in the ASM in oh61g.example.com server

a.       Stop the recovery process in the DR database (DRFEMP01)
alter database recover managed standby database using current logfile disconnect from session;





f.        Connect to RMAN with target as DR(DRFEMP01) and auxiliary as Cascaded standby(FEMCSD) and start the active duplicate standby creation.
run
{
ALLOCATE CHANNEL tgt10 TYPE DISK;
ALLOCATE CHANNEL tgt20 TYPE DISK;
ALLOCATE CHANNEL tgt30 TYPE DISK;
ALLOCATE CHANNEL tgt40 TYPE DISK;
ALLOCATE CHANNEL tgt50 TYPE DISK;
ALLOCATE CHANNEL tgt60 TYPE DISK;
ALLOCATE CHANNEL tgt70 TYPE DISK;
ALLOCATE CHANNEL tgt80 TYPE DISK;
ALLOCATE AUXILIARY CHANNEL dup1 TYPE DISK;
ALLOCATE AUXILIARY CHANNEL dup2 TYPE DISK;
ALLOCATE AUXILIARY CHANNEL dup3 TYPE DISK;
ALLOCATE AUXILIARY CHANNEL dup4 TYPE DISK;
ALLOCATE AUXILIARY CHANNEL dup5 TYPE DISK;
ALLOCATE AUXILIARY CHANNEL dup6 TYPE DISK;
ALLOCATE AUXILIARY CHANNEL dup7 TYPE DISK;
ALLOCATE AUXILIARY CHANNEL dup8 TYPE DISK;
duplicate target database for standby from active database nofilenamecheck;
}

g.       After the clone enable the log_archive_state_3 in the DR(DRFEMP01) and start the recovery process,
alter system set log_archive_dest_state_3=ENABLE scope=both;

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

h.      Start the recovery process in the cascaded standby database (FEMCSD),
alter database recover managed standby database using current logfile disconnect from session;


i.         Schedule the DR Sync check and Archive log removal script for the Cascades standby database(FEMCSD)

Opening a DR database in read/write mode temporarily using Snapshot Standby

Opening  a DR database in read write mode using Snapshot Standby method

Objective of this document is to convert a DR database (Mount state) into Read Write mode using Snapshot standby method and restoring it back to sync it up with the PRIMARY database.

DR Environment
The I****01 has a two node clustered DR database as follows,

NAME      DB_UNIQUE_NAME                 OPEN_MODE
--------- ------------------------------ --------------------
I****01   DRI****01                      MOUNTED
I****01   DRI****01                      MOUNTED

INSTANCE_NAME    STATUS       HOST_NAME
---------------- ------------ --------------------------------
I****011         MOUNTED      of90.egan.server.com
I****012         MOUNTED      of91.egan.server.com


The flashback mode is disabled in I****01 database.
The Flashback mode has to be enabled to restore the DR after application installation.

Steps to enable the flashback mode in both the Primary and DR I****01 database.

1.        Enabling Flashback in DR database.

a.        Cancel the recovery in the standby database.
STANDBY>ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

b.        Then activate the FLASHBACK on the standby database.
STANDBY> alter system set db_flashback_retention_target=1440 scope=both; ### retention value varies depending on the requirement.
STANDBY>ALTER DATABASE FLASHBACK ON;
c.        Verify the flashback mode,
SQL> select flashback_on from v$database;

2.        Enabling Flashback in the Primary database.
a.        Activate the FLASHBACK on the PRIMARY database.
PRIMARY> alter system set db_flashback_retention_target=1440 scope=both;#### retention value varies depending on the requirement.
PRIMARY> alter database flashback on;
b.        Verify the flashback mode,
SQL> select flashback_on from v$database;


Once the flashback mode is enabled, we shall go ahead and bring the DR in the read write mode.

Steps to open the DR database in Read/Write mode using Snapshot Standby

a.        Stop the recovery in the DR database.
STANDBY>ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
b.        Check The MRP status,
STANDBY>select PROCESS,CLIENT_PROCESS,THREAD#,SEQUENCE#,BLOCK# from v$managed_standby where process = 'MRP0' or client_process='LGWR';
                                                                                                                        (or)
host#     ps –ef|grep –imrp


c.        Bring down all instances except oneon which we will be using the conversion commands, and ensure the DR database is in mount state
d.        Convert the Physical standby DR to Snapshot standby DR,
STANDBY>ALTER DATABASE CONVERT TO SNAPSHOT STANDBY;
e.        The DR database will be dismounted after the conversion, so start it.
STANDBY>startup;
f.         The DR will be open in Read/Write mode now.


Steps to convert the Snapshot standby DR to Physical Standby DR

a.        Shutdown all the instances except one on which we will be using the conversion commands.
b.        Ensure that the database is mounted.
c.        Do the conversion of snapshot standby database to physical standby database.
STANDBY>ALTER DATABASE CONVERT TO PHYSICAL STANDBY;
d.        Change back the DR to Physical Standby database,
STANDBY>ALTER DATABASE CONVERT TO PHYSICAL STANDBY;
e.        Bounce the DR database and bring them to mount,
STANDBY>shutdown immediate
STANDBY>startup mount
f.         Enable the log shipping in the primary database,
PRIMARY>ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE;
g.        Start the MRP in the DR database,
STANDBY>ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;




Opening a DR database in Read/Write temporarily using restore points

Opening a DR database in read write mode

Objective of this document is to convert the DR database (Mount state) into Read Write mode temporarily and restoring it back to sync it up with the PRIMARY database.

DR Environment
For example  take a  two node clustered DR database as follows,
NAME      DB_UNIQUE_NAME                 OPEN_MODE
--------- ------------------------------ --------------------
I****01   DRI****01                      MOUNTED
I****01   DRI****01                      MOUNTED

INSTANCE_NAME    STATUS       HOST_NAME
---------------- ------------ --------------------------------
I****011         MOUNTED      of90.egan.server.com
I****012         MOUNTED      of91.egan.server.com

If the flashback mode is disabled in the database.
The Flashback mode has to be enabled to restore the DR after application installation.

Steps to enable the flashback mode in both the Primary and DR I****01 database.

1.        Enabling Flashback in DR database.

a.        Cancel the recovery in the standby database.
STANDBY>ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

b.        Then activate the FLASHBACK on the standby database.
STANDBY> alter system set db_flashback_retention_target=1440 scope=both;  #### retention value varies depending on the requirement.
STANDBY>ALTER DATABASE FLASHBACK ON;

c.        Verify the flashback mode,
SQL> select flashback_on from v$database;

2.        Enabling Flashback in the Primary database.

a.        Activate the FLASHBACK on the PRIMARY database.
PRIMARY> alter system set db_flashback_retention_target=1440 scope=both;#### retention value varies depending on the requirement.
PRIMARY> alter database flashback on;

b.        Verify the flashback mode,
SQL> select flashback_on from v$database;

Once the flashback mode is enabled, we shall go ahead and bring the DR in the read write mode.

Steps to open the DR database in Read/Write mode

a.        Stop the recovery in the DR database.
STANDBY>ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

b.        Check The MRP status,
STANDBY>select PROCESS,CLIENT_PROCESS,THREAD#,SEQUENCE#,BLOCK# from v$managed_standby where process = 'MRP0' or client_process='LGWR';
                                        (or)
host#     ps –ef|grep –imrp


c.        Creating a restore point to flashback the DR DB to the old stage & this will help us to bring back as a standby database.
STANDBY> CREATE RESTORE POINT stby_fb_dri****01 GUARANTEE FLASHBACK DATABASE;## stby_fb_dri****01 is the restore point name

d.        Check the SCN and restore point details in the DR database,
STANDBY> select NAME,SCN,TIME from v$restore_point;

e.        In the Primary database defer the log shipping to the DR,
PRIMARY> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=DEFER;

f.         Activate the DR physical standby database and open it in Read/Write mode,
STANDBY>alter database activate standby database;

g.        Check the status of the controlfile_type after changing the DR database in Read/Write
STANDBY>select CONTROLFILE_TYPE from v$database;

h.        Open the DR database in Read/Write mode,
STANDBY> ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE;
STANDBY> ALTER DATABASE OPEN;

i.         Opening the DR in node two is optional, if you would like to open it,
NODE 2 STANDBY>ALTER DATABASE OPEN;



Steps to restore the DR

a.        Stop the DR database completely.
srvctl stop database –d I****01

b.        Start one instance to restore the DR to its old state,
STANDBY>startup mount
STANDBY> FLASHBACK DATABASE TO RESTORE POINT stby_fb_dri****01;

c.        Check the controlfile_type of the DR database,
STANDBY> select controlfile_type from v$database;

d.        Change back the DR to Physical Standby database,
STANDBY> ALTER DATABASE CONVERT TO PHYSICAL STANDBY;

e.        Bounce the DR database and bring them to mount,
STANDBY>shutdown immediate
STANDBY>startup mount

f.         Enable the log shipping in the primary database,
PRIMARY> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE;

g.        Drop the restore point on the standby database,
STANDBY> DROP RESTORE POINT stby_fb_dri****01;

h.        Start the MRP in the DR database,
STANDBY>ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;