RAC Node Pinning & Unpinning

sam1374:CADP3>olsnodes -n -t -s

sam1372      1       Active  Unpinned

sam1373      2       Active  Unpinned

sam1374      3       Active  Unpinned

sam1374:CADP3>

There is so much we learn every day. One of such is leanred today is pinning and unpinning of RAC node.

Pinning a node means that the association of a node name with a node number is fixed. If a node is not pinned, its node number may change if the lease expires while it is down. The lease of a pinned node never expires.

This concept is active since version 11.2. When we do fresh installtion Clusterware do it for us. For the clusterware which are upgraded from lower version, can be pinned by :  crsctl pin css -n node3 node4

I'm still in phase to understand advantage/dis-advantage of pinning and unpinning the node.

OPatch cleanup

[oracle@localhost OPatch]$ ./opatch util Cleanup

Oracle Interim Patch Installer version 12.1.0.1.0

Copyright (c) 2012, Oracle Corporation.  All rights reserved.

Oracle Home       : /u01/home/oracle/product/11.2.0/db_1

Central Inventory : /u01/app/oraInventory

   from           : /u01/home/oracle/product/11.2.0/db_1/oraInst.loc

OPatch version    : 12.1.0.1.0

OUI version       : 12.1.0.1.0

Log file location : /u01/home/oracle/product/11.2.0/db_1/cfgtoollogs/opatch/opatch2014-02-15_07-53-51AM_1.log

Invoking utility "cleanup"

OPatch will clean up 'restore.sh,make.txt' files and 'scratch,backup' directories.

You will be still able to rollback patches after this cleanup.

Do you want to proceed? [y|n]

y

User Responded with: Y

Size of directory "/u01/home/oracle/product/11.2.0/db_1/.patch_storage" before cleanup is 227169947 bytes.

Size of directory "/u01/home/oracle/product/11.2.0/db_1/.patch_storage" after cleanup is 227169947 bytes.

UtilSession: Backup area for restore has been cleaned up. For a complete list of files/directories

deleted, Please refer log file.

OPatch succeeded.

[oracle@localhost OPatch]$

ORA-39071: Value for EXCLUDE is badly formed

IN KSH shell below exclude format won't work 
exclude=TABLE:"IN ('DATA')" , this will prompt in error  ORA-39071: Value for EXCLUDE is badly formed.
This is SHELL issue, solution is to use format : "exclude=TABLE:\"IN ('DATA')\""

Rename Disk group using renamedg

1.First unmount the diskgroup

[oracle@vm6 ~]$ asmcmd umount FLASH

2.Verify whether diskgroup has been mounted or not

[oracle@vm6 ~]$ asmcmd lsdg

State    Type    Rebal  Sector  Block       AU  Total_MB  Free_MB  Req_mir_free_MB  Usable_file_MB  Offline_disks  Voting_files  Name
MOUNTED  EXTERN  N         512   4096  1048576      5114     1525                0            1525              0             N  DATA/
MOUNTED  EXTERN  N         512   4096  1048576     10236    10186                0           10186              0             N  DATA2/

3.Rename diskgroup

[oracle@vm6 ~]$ renamedg phase=both dgname=FLASH newdgname=FRA verbose=true

Parsing parameters..

Parameters in effect:

         Old DG name       : FLASH
         New DG name          : FRA
         Phases               :
                 Phase 1
                 Phase 2
         Discovery str        : (null)
         Clean              : TRUE
         Raw only           : TRUE
renamedg operation: phase=both dgname=FLASH newdgname=FRA verbose=true
Executing phase 1
Discovering the group
Performing discovery with string:
Identified disk ASM:/opt/oracle/extapi/32/asm/orcl/1/libasm.so:ORCL:VOL2 with disk number:0 and timestamp (32970697 -482714624)
Checking for hearbeat...
Re-discovering the group
Performing discovery with string:
Identified disk ASM:/opt/oracle/extapi/32/asm/orcl/1/libasm.so:ORCL:VOL2 with disk number:0 and timestamp (32970697 -482714624)
Checking if the diskgroup is mounted or used by CSS
Checking disk number:0
Generating configuration file..
Completed phase 1
Executing phase 2
Looking for ORCL:VOL2
Modifying the header
Completed phase 2
Terminating kgfd context 0xb7e70050

above command fails use asm_diskstring command

 renamedg phase=both dgname=FLASH newdgname=FRA verbose=true asm_diskstring='ORCL:VOL2'

4.Mount the new diskgroup

[oracle@vm6 ~]$ asmcmd mount FRA

5.Verify the diskgroup

[oracle@vm6 ~]$ asmcmd lsdg
State    Type    Rebal  Sector  Block       AU  Total_MB  Free_MB  Req_mir_free_MB  Usable_file_MB  Offline_disks  Voting_files  Name
MOUNTED  EXTERN  N         512   4096  1048576      5114     1525                0            1525              0             N  DATA/
MOUNTED  EXTERN  N         512   4096  1048576     10236    10186                0           10186              0             N  DATA2/
MOUNTED  EXTERN  N         512   4096  1048576      1019      967                0             967              0             N  FRA/
[oracle@vm6 ~]$

Standby archive apply hang due to space issue

Ideally at designing stage we should have allocated sufficient space for both PROD and STANDBY and they should match. For example, if a file system U05 is 500GB on PROD you should size the file system to 500GB on STANDBY as well. 

Well but not always. :)

We hang on issue when a DBA had added a datafile in Prod at U05 file system but due to lack space at DR Side for file system U05, archive apply stopped.

Standby_file_management is set to auto.

It was noted when archive apply was stopped due to space issue. 

Because is the steps taken to carried away: 

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL; 

SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=MANUAL; 

SQL> ALTER DATABASE CREATE DATAFILE '/u01/app/oracle/11.2.0/db_1/dbs/UNNAMED00143' AS '/u06/realfilename/gdata05.dbf'; 

SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO; 

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT; 

or, If you are using real time apply (it makes use of standby redo log file) 

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT; 

Hope this helps...

RMAN-08137: WARNING: archive log not deleted as it is still needed

Recently my friend raised a discussion for “RMAN-08137: WARNING: archived log not deleted “ .
That make me remind of couple of issues I have faced with archive deletion policy when standby is configured.

Issue discussed is on 3 node RAC database with  on version 11gR2 with 3 node RAC on standby configuration.

We have set Archive deletion to

RMAN> CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY;

Case (1) is for the ticket when error RMAN-08137 raised when user is trying to delete archive log using command  “backup archivelog all delete input”.

The error is very informative and it is because archive is not yet applied at Standby Database. Same can be checked with below command on production database:

select dest_id, thread#, sequence#, first_time, completion_time, registrar, archived, applied, deleted, status from v$archived_log where standby_dest='YES' and status='A';

Case (2) is user facing the FRA space, tried to deleted the archive log with below command and surprising archive logs are deleted before even shipped at standby database.

rman> backup archivelog all not backed up delete all input skip inaccessible;

Issue is stated as Bug:16692232 in MOS and One of patch is provided.

I have also faced couples of more cases for the rman-08137 when database is configured with streams replication  and those are because archived logs where still to be used by logminer .

query to check what query running into specific user at given time

SET HEADING ON

SET LINESIZE 300

SET PAGESIZE 60

COLUMN Sample_Time FOR A12

COLUMN username FOR A20

COLUMN sql_text FOR A40

COLUMN program FOR A25

COLUMN module FOR A25

SELECT

   to_char(sample_time,'DD Mon HH24:MI') as Sample_Time,

   u.username,

   h.program,

   h.module,

   s.sql_text

FROM

   DBA_HIST_ACTIVE_SESS_HISTORY h,

   DBA_USERS u,

   DBA_HIST_SQLTEXT s

WHERE  sample_time between '11-JUL-13 05.45.03.793 PM' and '11-JUL-13 06.45.04.455 PM'

   AND h.user_id=u.user_id

   AND h.sql_id = s.sql_iD

   AND u.username='SCOTT'

order by 1;

To check given query performance for any specific table:-

select a. snap_id, begin_interval_time,c.sql_text,

executions_delta,(elapsed_time_delta /executions_delta )/1000000,

plan_hash_value,(cpu_time_delta /executions_delta )/1000000,

rows_processed_delta/executions_delta ,(iowait_delta /executions_delta )/1000000

,(fetches_delta/ executions_delta)

,(disk_reads_delta/ executions_delta)

,(buffer_gets_delta/ executions_delta)

from dba_hist_sqlstat a, dba_hist_snapshot b ,

dba_hist_sqltext c where sql_text like '%table_name%' and a. snap_id= b. snap_id  and executions_delta ! =0 and a.sql_id=c.sql_id order by snap_id desc