Jeyaseelan Blog

Dear Friends, The content in this blog are purely based on my own opinion ,it is not reflecting any of my current or previous employers materials or official documents. All my posts here are not warranted to be free of errors. Please use at your own risk and after thorough testing in your environment. If you feel that i am violating any of the company's policies or documents, kindly mail me at jeyaseelan.hi@gmail.com,I am happy to take out those content from this blog.

Showing posts with label RAC. Show all posts

Wednesday, 4 July 2018

GI Patch level mismatch between Nodes - after rolling back failed patch at GI_HOME

Error Messages:

We may get the following error messages while doing ASM operations

ORA-15137: The ASM cluster is in rolling patch state.
The cluster upgrade state is [ROLLING PATCH].

Before implement the solution, Please validate where is the actual problem

1. Check patchlevel

Node1

$ crsctl query crs releasepatch
Oracle Clusterware release patch level is [1295926169] and the complete list of patches [27678276 ] have been applied on the local node. The release patch string is [18.2.0.0.0].

Node 2

$ crsctl query crs releasepatch
Oracle Clusterware release patch level is [3087104924] and the complete list of patches [27676517 27678271 27678276 ] have been applied on the local node. The release patch string is [18.2.0.0.0].

# Node 2 is ahead of Node 1

2. Kfod on GI Binary to list the patch levels

Node 1:

$ $ORACLE_HOME/bin/kfod op=patchlvl
-------------------
Current Patch level
===================
1295926169

$ORACLE_HOME/bin/kfod op=patches
---------------
List of Patches
===============
27678276

Node 2:

$ORACLE_HOME/bin/kfod op=patchlvl
-------------------
Current Patch level
===================
3087104924

$ORACLE_HOME/bin/kfod op=patches
---------------
List of Patches
===============
27676517
27678271
27678276

# In Node 1 27676517 & 27678271 are missing.

3. lsinventory

Node 1
$ORACLE_HOME/OPatch/opatch lsinventory | grep -i desc
ARU platform description:: Linux x86-64
Patch description: "OCW RELEASE UPDATE 18.2.0.0.0 (27678276)"
Patch description: "ACFS RELEASE UPDATE 18.2.0.0.0 (27678271)"
Patch description: "Database Release Update : 18.2.0.0.180417 (27676517)"

Node 2
$ORACLE_HOME/OPatch/opatch lsinventory | grep -i desc
ARU platform description:: Linux x86-64
Patch description: "OCW RELEASE UPDATE 18.2.0.0.0 (27678276)"
Patch description: "ACFS RELEASE UPDATE 18.2.0.0.0 (27678271)"
Patch description: "Database Release Update : 18.2.0.0.180417 (27676517)"

# But lsinventory shows both patchlevel same

4. Patch log files

Node 1 logs:

Node1_opatch_logs

Applying /data/oracle/OraclePatches/27681568/27676517 ...
Patch /data/oracle/OraclePatches/27681568/27676517 applied.

Applying /data/oracle/OraclePatches/27681568/27678271 ...
Patch /data/oracle/OraclePatches/27681568/27678271 applied.

Applying /data/oracle/OraclePatches/27681568/27678276 ...
Patch /data/oracle/OraclePatches/27681568/27678276 applied. <==== applied successfully

Solutions:

Try the following solutions one by one. Check the releaselevel after each solution implemented, if that particular solution fixed, then you can skip the other steps.

# Sol-1
ALTER SYSTEM STOP ROLLING PATCH

# SOl-2
crsctl stop rollingpatch

# Sol-3

As root user,
$GRID_HOME/crs/install/rootcrs.sh -prepatch
$GRID_HOME/crs/install/rootcrs.sh -postpatch

if above does not help, execute following and check status
$GI_HOME/bin/clscfg -patch

# Sol-4

Action Plan
==============
Step 1

$GRID_HOME/crs/install/rootcrs.sh -prepatch

Step 2

Add the missing patches to the kfod output[which are available in opatch lsinventory],
As grid owner,
$GRID_HOME/bin/patchgen commit -pi 27676517
$GRID_HOME/bin/patchgen commit -pi 27678271

Incase if we need to rollback extra patch

$GRID_HOME/bin/patchgen commit -rb 27678271
Check the below on both nodes and notify if there is any difference
kfod op=patchlvl
kfod op=patches

Step 3
If they are same run the below,
$GRID_HOME/crs/install/rootcrs.sh -postpatch

Saturday, 27 May 2017

How to collect RDA in RAC env. This is applicable for only RDA 4.XXX

How to collect RDA in RAC env. This is applicable for only RDA 4.XXX

1.1 To setup the config file
./rda.sh -vX RDA::Remote setup_cluster

1.2 verify the nodes for which rda will be collected.

./rda.sh -vX RDA::Remote list

1.3 Collect RDA
./rda.sh -v -e REMOTE_TRACE=1

Wednesday, 24 May 2017

How to generate awr in a rac instance for every one hour ( snapshot)

How to generate awr in a rac instance for every one hour ( snapshot)

# Jeyaseelan

if [ $# -ne 2 ] ; then
echo usage : ./awr.sh instance_number date
echo         ./awr.sh 1 12-JUN-2011
exit
fi
# Directory where this script resides
set -xv
STATSDIR=.
STATSREP=.
INSTANCE_NUMBER=$1
CURR_DATE=$2

AWRREPORT=snap.sql
# -------------------------------------------------->
# ---------- Start of program

rm $AWRREPORT
echo "Generating reports in $STATSREP ... Pls wait ..."
sqlplus -s /nolog <<!
conn / as sysdba
set serveroutput on size 1000000
set feed off term off trims on linesize 300
dbms_output.put_line('database connection');
spool $AWRREPORT
declare
    min_snap number;
    next_snap number;
    min_snap_time varchar2(20);
    next_snap_time varchar2(20);
    fileName varchar2(255);
    cursor c1 is select instance_number,snap_id, to_char(startup_time,'ddmm_hh24mi') snap_time from dba_hist_snapshot where instance_number=$INSTANCE_NUMBER and to_char(BEGIN_INTERVAL_TIME,'dd-MON-yyyy') = '$CURR_DATE' order by snap_id;
    c1_rec c1%rowtype;
begin
    for c1_rec in c1 loop
        select min(snap_id) into next_snap from dba_hist_snapshot where snap_id > c1_rec.snap_id and instance_number=$INSTANCE_NUMBER;
        select to_char(end_interval_time,'ddmonyyy_hh24mi') into next_snap_time from dba_hist_snapshot where instance_number=$INSTANCE_NUMBER and   snap_id = next_snap;
        dbms_output.put_line('define begin_snap='||c1_rec.snap_id);
        dbms_output.put_line('define end_snap='||next_snap);
       fileName := '$STATSREP'||'/'||'$ORACLE_SID'||'_awrrpt'||'_'||c1_rec.instance_number||'_'||c1_rec.snap_id||'_'||next_snap||'.lst';
      dbms_output.put_line('define report_name='||fileName);
        dbms_output.put_line('define report_type='||'text');
        dbms_output.put_line('define num_days='||'3');
        dbms_output.put_line('@$ORACLE_HOME/rdbms/admin/awrrpt');
    end loop;
exception
    when no_data_found then
        -- Only last snap remaining
        null;
    when others then
        dbms_output.put_line(sqlerrm);
end;
/
spool off
!
echo test
sqlplus -s /nolog <<!
conn / as sysdba
        @$AWRREPORT
!

Sunday, 27 November 2011

Control Parallel Operations in 11gR2 RAC

Friday, 14 October 2011

How to move the OCR diskgroup contents to another group online

How to move the OCR diskgroup contents to another group online in a RAC enviornment

Sometime due to some reason we may need to move the ocr contents to new diskgroup , the reason could be like Asm lable is wrong,diskmapper is wrong,disk is having problem,disk location need to be changed,

1. Check the current Disk Group name

[root@ocservera 11.2.0.2]# ocrcheck

Status of Oracle Cluster Registry is as follows :

Version : 3

Total space (kbytes) : 262120

Used space (kbytes) : 3284

Available space (kbytes) : 258836

ID : 1837802780

Device/File Name : +OCR

Device/File integrity check succeeded

Device/File not configured

Cluster registry integrity check succeeded

Logical corruption check succeeded

2. Create another diskgroup

CREATE DISKGROUP OCR1

external redundancy

disk 'ORCL:ASM_DISK_0011',

'ORCL:ASM_DISK_0012',

attribute 'compatible.asm' = '11.2',

'compatible.advm' = '11.2'

Diskgroup created.

3. Verify through asmcmd or sqlplus

ASMCMD> lsdg

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

MOUNTED NORMAL N 512 4096 1048576 3057 1372 563 404 0 Y OCR/

MOUNTED NORMAL N 512 4096 1048576 558030 556983 349 278317 0 N OCR1/

ASMCMD> exit

4. Add the OCR to the new DISK group

[root@ocservera 11.2.0.2]# ocrconfig -add +OCR1

5 . Verify if new disk has been added.

[root@ocservera 11.2.0.2]# ocrcheck

Status of Oracle Cluster Registry is as follows :

Version : 3

Total space (kbytes) : 262120

Used space (kbytes) : 3304

Available space (kbytes) : 258816

ID : 1837802780

Device/File Name : +OCR

Device/File integrity check succeeded

Device/File Name : +OCR1

Device/File integrity check succeeded

Device/File not configured

Cluster registry integrity check succeeded

Logical corruption check succeeded

6. Delete the ocr from the old diskgroup

[root@ocservera 11.2.0.2]# ocrconfig -delete +OCR

7. Check ocr is moved to new diskgroup

[root@ocservera 11.2.0.2]# ocrcheck

Status of Oracle Cluster Registry is as follows :

Version : 3

Total space (kbytes) : 262120

Used space (kbytes) : 3304

Available space (kbytes) : 258816

ID : 1837802780

Device/File Name : +OCR1

Device/File integrity check succeeded

Device/File not configured

Cluster registry integrity check succeeded

Logical corruption check succeeded

8. Move the voting disk from the current diskgroup to new diskgroup

8.1 Verify the current location

crsctl query css votedisk

## STATE File Universal Id File Name Disk group

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

1. ONLINE a63ccacdc0b84f6cbfeeeee913657936 (ORCL:ASM_OCR_0001P1) [OCR]

2. ONLINE 15f320594b7d4fbdbf6fb952b3665683 (ORCL:ASM_OCR_0002P1) [OCR]

3. ONLINE 1e80bcca0d994fc5bfc403e9ce01b951 (ORCL:ASM_OCR_0003P1) [OCR]

8.2 replcate the disk group.

crsctl replace votedisk +OCR1

Successful addition of voting disk 1a8593de9c654f77bf7d55440dc20ce0.

Successful addition of voting disk 49e5d45ebe874f7fbfbc514fa51cc641.

Successful addition of voting disk 248240fba9784f57bf2021416760d415.

Successful deletion of voting disk a63ccacdc0b84f6cbfeeeee913657936.

Successful deletion of voting disk 15f320594b7d4fbdbf6fb952b3665683.

Successful deletion of voting disk 1e80bcca0d994fc5bfc403e9ce01b951.

Successfully replaced voting disk group with +OCR1.

CRS-4266: Voting file(s) successfully replaced

8.3 verify the current location

[oragrid@ocservera ~]$ crsctl query css votedisk

## STATE File Universal Id File Name Disk group

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

1. ONLINE 1a8593de9c654f77bf7d55440dc20ce0 (ORCL:ASM_FRA_0001) [OCR1]

2. ONLINE 49e5d45ebe874f7fbfbc514fa51cc641 (ORCL:ASM_FRA_0002) [OCR1]

3. ONLINE 248240fba9784f57bf2021416760d415 (ORCL:ASM_FRA_0003) [OCR1]

Located 3 voting disk(s).

8.4 Verify using asmcmd if voting disk is 'Y' against the disk group

[oragrid@ocservera ~]$ asmcmd

ASMCMD> lsdg

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

MOUNTED NORMAL N 512 4096 1048576 3057 1468 531 468 0 N OCR/

MOUNTED NORMAL N 512 4096 1048576 558030 556342 563 277889 0 Y OCR1/

9. move the spfile to new diskgroup using spmove

9.1 create spfile directory in the new diskgroup

asmcmd> mkdir +OCR1/ocserver-cl/asmparameterfile

9.2 spmove will copy the current spfile to the new location and update the gpn profile for next startup. This command throw erros as not all alterations performed as current spfile is being used, in the next startup

the new spfile will be effective and we can manually remove the old spfile.

ASMCMD> spmove +OCR/ocserver-cl/asmparameterfile/registry.253.762275705 +OCR1/ocserver-cl/asmparameterfile/spfileASM.ora

ORA-15032: not all alterations performed

ORA-15028: ASM file '+DATA/asm/asmparameterfile/REGISTRY.253.715881237' not dropped; currently being accessed (DBD ERROR: OCIStmtExecute

Alternatively we can do this using the below steps Ref:How to move ASM spfile to a different disk group [ID 1082943.1

To move ASM spfile to another disk group, either make use of intermediate pfile:

Create intermediate pfile from the current spfile

Create spfile in a new disk group from the intermediate pfile

Restart the HA stack to verify that ASM starts up fine with moved spfile

Remove the original spfile

or use 'asmcmd spcopy' command

Copy spfile with -u option - to update GPnP profile in RAC environment

Restart the HA stack to verify that ASM starts up fine with copiedspfile

Remove the original spfile

9.3 . Restart the HA stack to verify that ASM starts up fine with moved spfile

crsctl stop cluster -all

crsctl start cluster -all

9.4. Verify spfile has been changed

[oragrid@ocservera ~]$ sqlplus /nolog

SQL*Plus: Release 11.2.0.2.0 Production on Mon Sep 26 15:12:42 2011

SQL> conn / as sysasm

Connected.

SQL> show parameter spfile

NAME TYPE VALUE

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

spfile string +OCR1/ocserver-cl/asmparamete

rfile/spfileasm.ora

SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production

With the Real Application Clusters and Automatic Storage Management options

10. Dismount the ocr diskgroup from all the instances

SQL> alter diskgroup ocr mount;

Diskgroup altered.

if the disk group is used or mounted by any of the process/instance we may get the below error

ASMCMD> dropdg -r -f OCR

ORA-15039: diskgroup not dropped

ORA-15073: diskgroup OCR is mounted by another ASM instance (DBD ERROR: OCIStmtExecute)

11. Beside spfile,ocr,votingdisk if any of the files are created, please remove the files manualy using asmcmd rm command or drop/move the files from the respective database

12. Now we are ready to drop the diskgroup

dropdg -r -f OCR

13. Verify all are intact.

ASMCMD> lsdg

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

MOUNTED NORMAL N 512 4096 1048576 558030 556342 563 277889 0 Y OCR1/

Tuesday, 11 October 2011

Flashback database on a RAC database with rac standby setup

Flashback Database Test case in a RAC with Data guard Setup

This has been tested in a 8 Node RAC with 8 node Standby RAC database.

1. Verify if flashback and force_logging database is enabled on both primary and standby.

Primary:

select flashback_on,force_logging from v$database;

[RACFDP1]oracle@primarydb1.ora.com$ sqlplus /nolog

SQL*Plus: Release 11.2.0.1.0 Production on Fri Oct 14 10:23:58 2011

SQL> conn / as sysdba

Connected.

SQL> select flashback_on,force_logging from v$database;

FLASHBACK_ON FOR

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

YES YES

Stanbdy:

select flashback_on,force_logging from v$database;

[RACFDP1]oracle@primarydb1.ora.com$ sqlplus /nolog

SQL*Plus: Release 11.2.0.1.0 Production on Fri Oct 14 10:23:58 2011

SQL> conn / as sysdba

Connected.

SQL> select flashback_on,force_logging from v$database;

FLASHBACK_ON FOR

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

YES YES

SQL>

2. Verify if DG configuration is fine through dgmgrl

If any issue with any of the configuration, pelase fix the issue before create restore point or the release

DGMGRL> show configuration

DGMGRL> show database RACFDP

DGMGRL> show database RACFDP_sb

DGMGRL> show configuration

Configuration - RACFDP_dataguard

Protection Mode: MaxAvailability

Databases:

RACFDP - Primary database

RACFDP_sb - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:

SUCCESS

DGMGRL> show database RACFDP

Database - RACFDP

Role: PRIMARY

Intended State: TRANSPORT-ON

Instance(s):

RACFDP1

RACFDP2

RACFDP3

RACFDP4

RACFDP5

RACFDP6

RACFDP7

RACFDP8

Database Status:

SUCCESS

DGMGRL> show database RACFDP_sb

Database - RACFDP_sb

Role: PHYSICAL STANDBY

Intended State: APPLY-ON

Transport Lag: 0 seconds

Apply Lag: 0 seconds

Real Time Query: OFF

Instance(s):

RACFDP_SB1

RACFDP_SB2

RACFDP_SB3

RACFDP_SB4

RACFDP_SB5 (apply instance)

RACFDP_SB6

RACFDP_SB7

RACFDP_SB8

Database Status:

SUCCESS

3. Create restore points on Both primary and stanbdy

3.1 First create restore point in the standby database. To create restore point in the standby we need to disable the log apply and create the restore point and then enable back the log shipping

3.1.1 Disable log apply using dgmgrl

edit database RACFDP_sb SET STATE='APPLY-OFF';

DGMGRL> edit database RACFDP_sb SET STATE='APPLY-OFF';

Succeeded.

DGMGRL>

DGMGRL> show database RACFDP_sb

Database - RACFDP_sb

Role: PHYSICAL STANDBY

Intended State: APPLY-OFF

Transport Lag: 0 seconds

Apply Lag: 0 seconds

Real Time Query: OFF

Instance(s):

RACFDP_SB1

RACFDP_SB2

RACFDP_SB3

RACFDP_SB4

RACFDP_SB5 (apply instance)

RACFDP_SB6

RACFDP_SB7

RACFDP_SB8

Database Status:

SUCCESS

DGMGRL> show configuration

Configuration - RACFDP_dataguard

Protection Mode: MaxAvailability

Databases:

RACFDP - Primary database

RACFDP_sb - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:

SUCCESS

3.1.2 Create restore point:

CREATE RESTORE POINT FLASHBACK_TEST1 GUARANTEE FLASHBACK DATABASE;

[RACFDP_SB1]oracle@standbydb1.ora.com$ sqlplus /nolog

SQL*Plus: Release 11.2.0.1.0 Production on Fri Oct 14 10:39:43 2011

SQL> conn / as sysdba

Connected.

SQL> CREATE RESTORE POINT FLASHBACK_TEST1 GUARANTEE FLASHBACK DATABASE;

Restore point created.

SQL>

SQL> select current_scn from v$database;

CURRENT_SCN

-----------

21015113

3.1.3 Enable log apply on standby server

edit database RACFDP_sb SET STATE='APPLY-ON';

DGMGRL> edit database RACFDP_sb SET STATE='APPLY-ON';

Succeeded.

DGMGRL> show database RACFDP_sb

Database - RACFDP_sb

Role: PHYSICAL STANDBY

Intended State: APPLY-ON

Transport Lag: 0 seconds

Apply Lag: 0 seconds

Real Time Query: OFF

Instance(s):

RACFDP_SB1

RACFDP_SB2

RACFDP_SB3

RACFDP_SB4

RACFDP_SB5 (apply instance)

RACFDP_SB6

RACFDP_SB7

RACFDP_SB8

Database Status:

SUCCESS

DGMGRL> show configuration

Configuration - RACFDP_dataguard

Protection Mode: MaxAvailability

Databases:

RACFDP - Primary database

RACFDP_sb - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:

SUCCESS

3.2 Now create restore point in the primary database.

CREATE RESTORE POINT FLASHBACK_TEST1 GUARANTEE FLASHBACK DATABASE;

[RACFDP1]oracle@primarydb1.ora.com$ sqlplus /nolog

SQL*Plus: Release 11.2.0.1.0 Production on Fri Oct 14 10:48:24 2011

SQL> conn / as sysdba

Connected.

SQL> CREATE RESTORE POINT FLASHBACK_TEST1 GUARANTEE FLASHBACK DATABASE;

Restore point created.

SQL>

3.3 verify if restore points are created from the primary ( node1)

SQL> select INST_ID,name from gv$restore_point;

INST_ID NAME

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

1 FLASHBACK_TEST1

2 FLASHBACK_TEST1

6 FLASHBACK_TEST1

3 FLASHBACK_TEST1

5 FLASHBACK_TEST1

8 FLASHBACK_TEST1

7 FLASHBACK_TEST1

4 FLASHBACK_TEST1

8 rows selected.

4. Do the release – run @ primary database

The below scripts are just for testing purpose. We need to run the actual scripts

4A. Create user ,tables,insert,update and deletes

[RACFDP1]oracle@primarydb1.ora.com$ sqlplus /nolog

SQL*Plus: Release 11.2.0.1.0 Production on Tue Oct 11 07:51:17 2011

SQL> conn / as sysdba

Connected.

SQL> create user flashback_test identified by flashback_test;

User created.

SQL> grant dba to flashback_test;

Grant succeeded.

SQL> grant create session to flashback_test;

Grant succeeded.

SQL> conn flashback_test/flashback_test

Connected.

SQL> create table test1 as select * from dba_objects;

Table created.

SQL> insert into test1 values ( select * from dba_objects);

insert into test1 values ( select * from dba_objects)

ERROR at line 1:

ORA-00936: missing expression

SQL> insert into test1 ( select * from dba_objects);

13717 rows created.

SQL> insert into test1 ( select * from dba_objects);

13717 rows created.

SQL> insert into test1 ( select * from dba_objects);

13717 rows created.

SQL> update test1 set object_name='null';

54868 rows updated.

SQL> rollback;

Rollback complete.

SQL> update test1 set object_name='null';

13717 rows updated.

SQL> commit;

Commit complete.

SQL> delete from test1;

13717 rows deleted.

SQL> rollback;

Rollback complete.

SQL> truncate table test1;

Table truncated.

SQL> exit

5. Flashback the primary and stanbdy databases

5. Flashback the primary database

5.1 Convert the DG mode to Maxperformance

EDIT CONFIGURATION SET PROTECTION MODE AS MAXPERFORMANCE

DGMGRL> EDIT CONFIGURATION SET PROTECTION MODE AS MAXPERFORMANCE;

Succeeded.

DGMGRL> show configuration

Configuration - RACFDP_dataguard

Protection Mode: MaxPerformance

Databases:

RACFDP - Primary database

RACFDP_sb - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:

SUCCESS

DGMGRL>

5.1 Disable log transport from primary, we need to convert the database to maxperformance to make

edit database RACFDP set state='TRANSPORT-OFF';

DGMGRL> edit database RACFDP set state='TRANSPORT-OFF';

Succeeded.

DGMGRL> show configuration

Configuration - RACFDP_dataguard

Protection Mode: MaxPerformance

Databases:

RACFDP - Primary database

RACFDP_sb - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:

SUCCESS

DGMGRL>

5.3 Switch the logfiles from all primary instances

alter system switch logfile ( on all primary instances)

5.4 Disable log apply on stanbdy database

EDIT DATABASE RACFDP_sb SET STATE='APPLY-OFF';

DGMGRL> EDIT DATABASE RACFDP_sb SET STATE='APPLY-OFF';

Succeeded.

DGMGRL> show database RACFDP_sb

Database - RACFDP_sb

Role: PHYSICAL STANDBY

Intended State: APPLY-OFF

Transport Lag: 0 seconds

Apply Lag: 3 minutes 23 seconds

Real Time Query: OFF

Instance(s):

RACFDP_SB1

RACFDP_SB2

RACFDP_SB3

RACFDP_SB4

RACFDP_SB5 (apply instance)

RACFDP_SB6

RACFDP_SB7

RACFDP_SB8

Database Status:

SUCCESS

5.5 stop both primary and standby database.

[RACFDP1]oracle@primarydb1.ora.com$ srvctl stop database -d RACFDP

[RACFDP1]oracle@primarydb1.ora.com$ srvctl stop database -d RACFDP

[RACFDP1]oracle@primarydb1.ora.com$ srvctl status database -d RACFDP

Instance RACFDP1 is not running on node primarydb1

Instance RACFDP2 is not running on node primarydb2

Instance RACFDP3 is not running on node primarydb3

Instance RACFDP4 is not running on node primarydb4

Instance RACFDP5 is not running on node primarydb5

Instance RACFDP6 is not running on node primarydb6

Instance RACFDP7 is not running on node primarydb7

Instance RACFDP8 is not running on node primarydb8

[RACFDP1]oracle@primarydb1.ora.com$

[RACFDP_SB1]oracle@standbydb1.ora.com$ srvctl stop database -d RACFDP_SB

[RACFDP_SB1]oracle@standbydb1.ora.com$ srvctl stop database -d RACFDP_SB

[RACFDP_SB1]oracle@standbydb1.ora.com$ srvctl status database -d RACFDP_SB

Instance RACFDP_SB1 is not running on node standbydb1

Instance RACFDP_SB2 is not running on node standbydb2

Instance RACFDP_SB3 is not running on node standbydb3

Instance RACFDP_SB4 is not running on node standbydb4

Instance RACFDP_SB5 is not running on node standbydb5

Instance RACFDP_SB6 is not running on node standbydb6

Instance RACFDP_SB7 is not running on node standbydb7

Instance RACFDP_SB8 is not running on node standbydb8

5.6. Start the primary in mount mode from node1 (primarydb1.uk.db.com)

[RACFDP1]oracle@primarydb1.ora.com$ sqlplus /nolog

SQL*Plus: Release 11.2.0.1.0 Production on Fri Oct 14 11:35:37 2011

SQL> conn / as sysdba

Connected to an idle instance.

SQL> startup mount

ORACLE instance started.

Total System Global Area 1.6034E+10 bytes

Fixed Size 2229184 bytes

Variable Size 1.0402E+10 bytes

Database Buffers 5469372416 bytes

Redo Buffers 160735232 bytes

Database mounted.

5.7 Start the standby in mount mode from node1(standbydb1.uk.db.com)

[RACFDP_SB1]oracle@standbydb1.ora.com$ sqlplus /nolog

SQL*Plus: Release 11.2.0.1.0 Production on Fri Oct 14 11:35:26 2011

SQL> conn / as sysdba

Connected to an idle instance.

SQL> startup mount

ORACLE instance started.

Total System Global Area 1.6034E+10 bytes

Fixed Size 2229184 bytes

Variable Size 1.0805E+10 bytes

Database Buffers 5066719232 bytes

Redo Buffers 160735232 bytes

Database mounted.

SQL>

5.8 Flashback primary database

flashback database to restore point FLASHBACK_TEST1;

SQL> flashback database to restore point FLASHBACK_TEST1;

Flashback complete.

SQL> select current_scn from v$database;

CURRENT_SCN

-----------

SQL>

5.9 open primary with resetlogs ( from node1 primarydb1.uk.db.com). It may sometime as it has to resetlogs all the redolog in all the threads

alter database open resetlogs;

SQL> alter database open resetlogs;

Database altered.

SQL>

5.10. flashback standby database from node1 (standbydb1.uk.db.com)

flashback database to restore point FLASHBACK_TEST1;

SQL> flashback database to restore point FLASHBACK_TEST1;

Flashback complete.

SQL>

SQL> select current_scn from v$database;

CURRENT_SCN

-----------

21015113

5.11. Shutdown the primary database from node1 (primarydb1.uk.db.com)

[RACFDP1]oracle@primarydb1.uk.db.com

SQL> SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL>

5.11. Shutdown the standby from node1 (standbydb1.uk.db.com)

SQL> shutdown immediate;

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

SQL> exit

5.12 start both primary and standby databases

srvctl start database -d RACFDP

srvctl start database -d RACFDP_SB

[RACFDP1]oracle@primarydb1.ora.com$ srvctl start database -d RACFDP

[RACFDP1]oracle@primarydb1.ora.com$ srvctl status database -d RACFDP

Instance RACFDP1 is running on node primarydb1

Instance RACFDP2 is running on node primarydb2

Instance RACFDP3 is running on node primarydb3

Instance RACFDP4 is running on node primarydb4

Instance RACFDP5 is running on node primarydb5

Instance RACFDP6 is running on node primarydb6

Instance RACFDP7 is running on node primarydb7

Instance RACFDP8 is running on node primarydb8

[RACFDP_SB1]oracle@standbydb1.ora.com$ srvctl start database -d RACFDP_SB

[RACFDP_SB1]oracle@standbydb1.ora.com$ srvctl status database -d RACFDP_SB

Instance RACFDP_SB1 is running on node standbydb1

Instance RACFDP_SB2 is running on node standbydb2

Instance RACFDP_SB3 is running on node standbydb3

Instance RACFDP_SB4 is running on node standbydb4

Instance RACFDP_SB5 is running on node standbydb5

Instance RACFDP_SB6 is running on node standbydb6

Instance RACFDP_SB7 is running on node standbydb7

Instance RACFDP_SB8 is running on node standbydb8

5.13. check the status of both primary and stanbdy using crsctl to find if instance status in proper mode.

Primary:

ora.RACFDP.db

1 ONLINE ONLINE primarydb1 Open

2 ONLINE ONLINE primarydb2 Open

3 ONLINE ONLINE primarydb3 Open

4 ONLINE ONLINE primarydb4 Open

5 ONLINE ONLINE primarydb5 Open

6 ONLINE ONLINE primarydb6 Open

7 ONLINE ONLINE primarydb7 Open

8 ONLINE ONLINE primarydb8 Open

Standby:

ora.RACFDP_sb.db

1 ONLINE INTERMEDIATE standbydb1 Mounted (Closed)

2 ONLINE INTERMEDIATE standbydb2 Mounted (Closed)

3 ONLINE INTERMEDIATE standbydb3 Mounted (Closed)

4 ONLINE INTERMEDIATE standbydb4 Mounted (Closed)

5 ONLINE INTERMEDIATE standbydb5 Mounted (Closed)

6 ONLINE INTERMEDIATE standbydb6 Mounted (Closed)

7 ONLINE INTERMEDIATE standbydb7 Mounted (Closed)

8 ONLINE INTERMEDIATE standbydb8 Mounted (Closed)

6. Drop the Restore points

6.1 Drop the restore points from Primary database ( Node1 - primarydb1.ora.com)

drop restore point FLASHBACK_TEST1;

[RACFDP1]oracle@primarydb1.ora.com$ sqlplus /nolog

SQL*Plus: Release 11.2.0.1.0 Production on Fri Oct 14 12:07:54 2011

SQL> conn / as sysdba

Connected.

SQL> drop restore point FLASHBACK_TEST1;

Restore point dropped.

SQL>

6.2 Drop the restore points from standby database ( Node1 - standbydb1.ora.com)

Please note if the log apply is enabled we need to disable it first to drop the restore point from standby. Since it has been disabled.so we are ignoring that step in this stage

drop restore point FLASHBACK_TEST1;

[RACFDP_SB1]oracle@standbydb1.ora.com$ sqlplus /nolog

SQL*Plus: Release 11.2.0.1.0 Production on Fri Oct 14 12:11:08 2011

SQL> conn / as sysdba

Connected.

SQL> drop restore point FLASHBACK_TEST1;

Restore point dropped.

7. Post Flashback activities

7.1 Verify current configuration

show configuration

show database RACFDP

show database RACFDP_sb

DGMGRL> show configuration

Configuration - RACFDP_dataguard

Protection Mode: MaxPerformance

Databases:

RACFDP - Primary database

RACFDP_sb - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:

SUCCESS

DGMGRL> show database RACFDP

Database - RACFDP

Role: PRIMARY

Intended State: TRANSPORT-OFF

Instance(s):

RACFDP1

RACFDP2

RACFDP3

RACFDP4

RACFDP5

RACFDP6

RACFDP7

RACFDP8

Database Status:

SUCCESS

DGMGRL> show database RACFDP_sb

Database - RACFDP_sb

Role: PHYSICAL STANDBY

Intended State: APPLY-OFF

Transport Lag: (unknown)

Apply Lag: (unknown)

Real Time Query: OFF

Instance(s):

RACFDP_SB1 (apply instance)

RACFDP_SB2

RACFDP_SB3

RACFDP_SB4

RACFDP_SB5

RACFDP_SB6

RACFDP_SB7

RACFDP_SB8

Database Status:

SUCCESS

7.2 Enable the log transport from primary node1

edit database RACFDP set state = 'TRANSPORT-ON';

DGMGRL> edit database RACFDP set state = 'TRANSPORT-ON';

Succeeded.

DGMGRL> exit

7.3 Switch logfile

alter system switch logfile ( on all the primary instances)

7.4 Enable the log apply from node1 standby

edit database RACFDP_sb set state='APPLY-ON';

DGMGRL> edit database RACFDP_sb set state='APPLY-ON';

Succeeded.

7.5. Ensure the preferred instances

edit database 'RACFDP_sb' set property PreferredApplyInstance='RACFDP_sb5';

edit database 'RACFDP' set property PreferredApplyInstance='RACFDP5';

EDIT DATABASE RACFDP_sb SET STATE='ONLINE' WITH APPLY INSTANCE=RACFDP_SB5;

show database RACFDP_sb PreferredApplyInstance

show database RACFDP PreferredApplyInstance

DGMGRL> edit database 'RACFDP_sb' set property PreferredApplyInstance='RACFDP_sb5';

Property "preferredapplyinstance" updated

DGMGRL> edit database 'RACFDP' set property PreferredApplyInstance='RACFDP5';

Property "preferredapplyinstance" updated

DGMGRL> EDIT DATABASE RACFDP_sb SET STATE='ONLINE' WITH APPLY INSTANCE=RACFDP_SB5;

Succeeded.

DGMGRL> show database RACFDP_sb PreferredApplyInstance

PreferredApplyInstance = 'RACFDP_sb5'

DGMGRL> show database RACFDP PreferredApplyInstance

PreferredApplyInstance = 'RACFDP5'

7.6 Enable MAXAVAILABILITY Protection Mode

EDIT CONFIGURATION SET PROTECTION MODE AS MAXAVAILABILITY;

DGMGRL> EDIT CONFIGURATION SET PROTECTION MODE AS MAXAVAILABILITY;

Succeeded.

7.7 Verify configuration through dgmgrl

show configuration

show database RACFDP

show database RACFDP_sb

DGMGRL> show configuration

Configuration - RACFDP_dataguard

Protection Mode: MaxPerformance

Databases:

RACFDP - Primary database

RACFDP_sb - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:

SUCCESS

DGMGRL> show database RACFDP

Database - RACFDP

Role: PRIMARY

Intended State: TRANSPORT-ON

Instance(s):

RACFDP1

RACFDP2

RACFDP3

RACFDP4

RACFDP5

RACFDP6

RACFDP7

RACFDP8

Database Status:

SUCCESS

DGMGRL> show database RACFDP_sb

Database - RACFDP_sb

Role: PHYSICAL STANDBY

Intended State: APPLY-ON

Transport Lag: 0 seconds

Apply Lag: 0 seconds

Real Time Query: OFF

Instance(s):

RACFDP_SB1

RACFDP_SB2

RACFDP_SB3

RACFDP_SB4

RACFDP_SB5 (apply instance)

RACFDP_SB6

RACFDP_SB7

RACFDP_SB8

Database Status:

SUCCESS

DBAs can follow the below steps if they want to confirm that standby database flashback is working in a reda-only mode . Thi is not part of the actual restore steps.

A.Create a test table with current date as value - Before restore point

Do this before the restore point created

Create a test table to test if the flashback is working as expected. Infact this is not correct method of testing this feature.

At the primary database – From Node 1 (primarydb1.uk.db.com)

SQL> create table test_flashback ( dt date );

Table created.

SQL> insert into test_flashback values(sysdate);

1 row created.

SQL> commit;

Commit complete.

B.Verify the test_flashback table on both primary and standby – Before restore point/

Please note that to access the application tables in standby database we need to convert the standby database into read-only mode . We need to bounce the standby database to keep the standby in mount mode otherwise the database will be in “real time query on” state

B.1 Primary:

SQL> alter session set nls_date_format='dd/mm/yyyy hh24:mi:ss';

Session altered.

SQL> select * from test_flashback;

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

11/10/2011 06:57:05

B.2 Stanbdy

B.2.1 Set read-only

DGMGRL> edit database RACFDP_sb set state='read-only';

Succeeded.

B.2.2 Query the test_flashback table

[RACFDP_SB1]oracle@standbydb1.ora.com$ sqlplus /nolog

SQL*Plus: Release 11.2.0.1.0 Production on Tue Oct 11 07:22:16 2011

SQL> conn / as sysdba

Connected.

SQL> alter session set nls_date_format='dd/mm/yyyy hh24:mi:ss';

Session altered.

SQL> select * from test_flashback;

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

11/10/2011 06:57:05

SQL>

B.2.3 Set APPLY-ON

DGMGRL> edit database RACFDP_sb SET STATE='APPLY-ON';

Succeeded.

DGMGRL>

B.2.4. bounce the standby database to bring into mount mode

srvctl stop database -d RACFDP_SB1

srvctl start database –d RACFDP_SB1

C.Update test_flasback table during the release

Update the test_flashback table - run @ primary

SQL> conn / as sysdba

Connected.

SQL> update test_flashback set dt=sysdate;

1 row updated.

SQL> commit;

Commit complete.

SQL> alter session set nls_date_format='dd/mm/yyyy hh24:mi:ss';

Session altered.

SQL> select * from test_flashback;

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

11/10/2011 07:56:16

D.Verify the test_flashback table on both primary and standby – After flashback

D.1 verify the test_flashback table from primary;

SQL> alter session set nls_date_format='dd/mm/yyyy hh24:mi:ss';

Session altered.

SQL> select * from test_flashback;

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

11/10/2011 06:57:05

D.2 Verify the test_flashback table from stanbdy

Set read-only

DGMGRL> edit database RACFDP_sb set state='read-only';

Succeeded.

Query the table

[RACFDP_SB1]oracle@standbydb1.ora.com$ sqlplus /nolog

SQL*Plus: Release 11.2.0.1.0 Production on Tue Oct 11 09:43:03 2011

SQL> conn / as sysdba

Connected.

SQL> select * from test_flashback;

---------

11-OCT-11

SQL> alter session set nls_date_format='dd/mm/yyyy hh24:mi:ss';

Session altered.

SQL> select * from test_flashback;

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

11/10/2011 06:57:05

Set the Apply-ON

DGMGRL> edit database 'RACFDP_sb' set state='APPLY-ON';

Succeeded.

DGMGRL> exit

Bounce the standby database to bring into mount mode

srvctl stop database -d RACFDP_SB1

srvctl start database –d RACFDP_SB1

Jeyaseelan Blog

Wednesday, 4 July 2018

GI Patch level mismatch between Nodes - after rolling back failed patch at GI_HOME

Saturday, 27 May 2017

How to collect RDA in RAC env. This is applicable for only RDA 4.XXX

Wednesday, 24 May 2017

How to generate awr in a rac instance for every one hour ( snapshot)

Sunday, 27 November 2011

Control Parallel Operations in 11gR2 RAC

Friday, 14 October 2011

How to move the OCR diskgroup contents to another group online

How to move the OCR diskgroup contents to another group online in a RAC enviornment

Tuesday, 11 October 2011

Flashback database on a RAC database with rac standby setup

1. Verify if flashback and force_logging database is enabled on both primary and standby.

2. Verify if DG configuration is fine through dgmgrl

3. Create restore points on Both primary and stanbdy

4. Do the release – run @ primary database

5. Flashback the primary and stanbdy databases

6. Drop the Restore points

7. Post Flashback activities

A.Create a test table with current date as value - Before restore point

B.Verify the test_flashback table on both primary and standby – Before restore point/

C.Update test_flasback table during the release

D.Verify the test_flashback table on both primary and standby – After flashback

ZFS

Public Cloud tools comparison

About Me