Refer Me for Oracle

Sunday, 26 May 2013

Re-Extract scenario in golden gate

Re-Extracting Scenario in Golden gate

Scenario.
1) One extract process which extract the details for 2 tables.
2) One Pump process been configured in the source.
3) Two replicat process on the target.

-- The pump process in the source has been configured to pump for only one table, and that table
is in sync now..
Test case--> The second table which is in the extract process but not added in the pump process
hence we need to reextract data and pump the details of the second table as well.

   --> Stop the pump process on the source.
GGSCI (rhel11gr2rac1.manzoor.com) 3> stop pgrp1
Sending STOP request to EXTRACT PGRP1 ...
Request processed.

   --> Stop all the replicat process on the target --( Since we have the same pump for all the replicat process so we need to stop the replicate to avoid the data corruption issue)


-- Check the currnet rba for all the other replicates, beacuse up to this rba this replicate has been processed the
transactions, hence we need to start the replicat after this.
GGSCI (rhel11gr2rac2.manzoor.com) 2> send rgrp1 status
Sending STATUS request to REPLICAT RGRP1 ...
Current status: At EOF
Sequence #: 9
RBA: 16760430
0 records in current transaction

GGSCI (rhel11gr2rac2.manzoor.com) 3> send rgrp1 logend
Sending LOGEND request to REPLICAT RGRP1 ...
YES.

GGSCI (rhel11gr2rac2.manzoor.com) 13> stop replicat RGRP1
Sending STOP request to REPLICAT RGRP1 ...
Request processed.

GGSCI (rhel11gr2rac2.manzoor.com) 4> stop replicat RGRP2
Sending STOP request to REPLICAT RGRP2 ...
Request processed.

--> We need to identify from where to re extract and pump
    -- in target we will open the trail file and find from where to re extract

Logdump 225 >open ./dirdat/fg000009
Current LogTrail is /golden_gate/dirdat/fg000009
Logdump 226 >ghdr on
Logdump 227 >detail on
Logdump 228 >detail data
Logdump 229 >pos 0
Reading forward from RBA 0
Logdump 233 >n
___________________________________________________________________
Hdr-Ind    :     E (x45)     Partition :     . (x04)
UndoFlag   :     . (x00)     BeforeAfter:     A (x41)
RecLength :    54 (x0036)   IO Time    : 2013/05/25 18:59:37.062.868
IOType     :     5 (x05)     OrigNode   :   255 (xff)
TransInd   :     . (x03)     FormatType :     R (x52)
SyskeyLen :     0 (x00)     Incomplete :     . (x00)
AuditRBA   :        113       AuditPos   : 224964
Continued :     N (x00)     RecCount   :     1 (x01)
2013/05/25 18:59:37.062.868 Insert               Len    54 RBA 1314
Name: MANZOOR.EMP
After Image:                                             Partition 4   G s
0000 000a 0000 0006 3130 3030 3031 0001 0008 0000 | ........100001......
0004 5465 7374 0002 000a 0000 0006 3130 3030 3031 | ..Test........100001
0003 000a 0000 0006 3130 3030 3031                | ........100001
Column     0 (x0000), Len    10 (x000a)
0000 0006 3130 3030 3031                          | ....100001
Column     1 (x0001), Len     8 (x0008)
0000 0004 5465 7374                               | ....Test
Column     2 (x0002), Len    10 (x000a)
0000 0006 3130 3030 3031                          | ....100001
Column     3 (x0003), Len    10 (x000a)
0000 0006 3130 3030 3031                          | ....100001

---> Since its a test case we can identify that above is the first record
not dows then auditpos -- 224964

-- Now go to source and find the rba for this auditpos 224964
-- We need to find out in which trail the auditpos start, lets start with the last two.
Logdump 125 >open ./dirdat/fg000065
Current LogTrail is /golden_gate/dirdat/fg000065
Logdump 116 >ghdr on
Logdump 117 >detail on
Logdump 118 >detail data
Logdump 119 >fileheader on
Logdump 120 >filter include auditrba 224964
Logdump 121 >show filter
Data filters are ENABLED
Include Match ANY
AuditRba   : = 224964 (x0000000000036ec4)
Exclude Match ANY
Logdump 126 >n
___________________________________________________________________
Hdr-Ind    :     E (x45)     Partition :     . (x04)
UndoFlag   :     . (x00)     BeforeAfter:     A (x41)
RecLength :    54 (x0036)   IO Time    : 2013/05/25 18:59:37.000.000
IOType     :     5 (x05)     OrigNode   :   255 (xff)
TransInd   :     . (x00)     FormatType :     R (x52)
SyskeyLen :     0 (x00)     Incomplete :     . (x00)
AuditRBA   :        113       AuditPos   : 224964
Continued :     N (x00)     RecCount   :     1 (x01)
2013/05/25 18:59:37.000.000 Insert               Len    54 RBA 1106
Name: MANZOOR.EMP
After Image:                                             Partition 4   G b
0000 000a 0000 0006 3130 3030 3031 0001 0008 0000 | ........100001......
0004 5465 7374 0002 000a 0000 0006 3130 3030 3031 | ..Test........100001
0003 000a 0000 0006 3130 3030 3031                | ........100001
Column     0 (x0000), Len    10 (x000a)
0000 0006 3130 3030 3031                          | ....100001
Column     1 (x0001), Len     8 (x0008)
0000 0004 5465 7374                               | ....Test
Column     2 (x0002), Len    10 (x000a)
0000 0006 3130 3030 3031                          | ....100001
Column     3 (x0003), Len    10 (x000a)
0000 0006 3130 3030 3031                          | ....100001

Filtering suppressed      2 records

--> We have found the record --- node down the rba -- 1106 , we need re extract pump from this rba.

--> Now inlcude the table in the pump process
extract pgrp1
userid ggate@source, password ggate
rmthost 192.168.0.21, mgrport 7809
rmttrail ./dirdat/fg
passthru
table manzoor.emp;
table manzoor.emp1;

--ALTER EXTRACT PGRP1, EXTSEQNO 65, EXTRBA 1106
--ALTER EXTRACT PGRP1, ETROLLOVER

GGSCI (rhel11gr2rac1.manzoor.com) 7> ALTER EXTRACT PGRP1, ETROLLOVER
2013-05-25 20:37:43 INFO    OGG-01520 Rollover performed. For each affected output trail of Version 10 or higher format, after starting the source extract,
issue ALTER EXTSEQNO for that trail's reader (either pump EXTRACT or REPLICAT) to move the reader's scan to the new trail file; it will not happen automatically.
EXTRACT altered.

GGSCI (rhel11gr2rac1.manzoor.com) 8> ALTER EXTRACT PGRP1, EXTSEQNO 65, EXTRBA 1106
EXTRACT altered.

GGSCI (rhel11gr2rac1.manzoor.com) 2> start extract p*
Sending START request to MANAGER ...
EXTRACT PGRP1 starting

GGSCI (rhel11gr2rac1.manzoor.com) 3> info all
Program     Status      Group       Lag at Chkpt Time Since Chkpt
MANAGER     RUNNING
EXTRACT     RUNNING     PGRP1       00:00:00      00:04:08
EXTRACT     RUNNING     XGRP1       00:00:00      00:00:08

Target

Now in target check whether the 2nd table data are reflected in the trail.

Logdump 365 >open ./dirdat/fg000012
Current LogTrail is /golden_gate/dirdat/fg000012
Logdump 366 >ghdr on
Logdump 367 >detail on
Logdump 368 >detail data
Logdump 369 >pos 0
Logdump 370 > n

Hdr-Ind    :     E (x45)     Partition :     . (x04)
UndoFlag   :     . (x00)     BeforeAfter:     A (x41)
RecLength :    54 (x0036)   IO Time    : 2013/05/25 18:59:36.997.462
IOType     :     5 (x05)     OrigNode   :   255 (xff)
TransInd   :     . (x02)     FormatType :     R (x52)
SyskeyLen :     0 (x00)     Incomplete :     . (x00)
AuditRBA   :        113       AuditPos   : 227352
Continued :     N (x00)     RecCount   :     1 (x01)
2013/05/25 18:59:36.997.462 Insert               Len    54 RBA 1265
Name: MANZOOR.EMP1
After Image:                                             Partition 4   G e
0000 000a 0000 0006 3130 3030 3031 0001 0008 0000 | ........100001......
0004 5465 7374 0002 000a 0000 0006 3130 3030 3031 | ..Test........100001
0003 000a 0000 0006 3130 3030 3031                | ........100001
Column     0 (x0000), Len    10 (x000a)
0000 0006 3130 3030 3031                          | ....100001
Column     1 (x0001), Len     8 (x0008)
0000 0004 5465 7374                               | ....Test
Column     2 (x0002), Len    10 (x000a)
0000 0006 3130 3030 3031                          | ....100001
Column     3 (x0003), Len    10 (x000a)
0000 0006 3130 3030 3031                          | ....100001

--- Yes now the second table data is reflected in the trail file

GGSCI (rhel11gr2rac2.manzoor.com) 1> info all
Program     Status      Group       Lag at Chkpt Time Since Chkpt
MANAGER     RUNNING
REPLICAT    STOPPED     RGRP1       00:00:00      01:10:19
REPLICAT    STOPPED     RGRP2       00:00:00      01:45:25

-- Now alter the first replict to start from when its completed earlier.
16760262

GGSCI (rhel11gr2rac2.manzoor.com) 2> send rgrp1 status
Sending STATUS request to REPLICAT RGRP1 ...
Current status: At EOF
Sequence #: 9
RBA: 16760430
0 records in current transaction

---
Below is the report file of the RGRP1 group.
Last record for the last committed transaction is the following:
___________________________________________________________________
Trail name : ./dirdat/fg000009
Hdr-Ind    :     E (x45)     Partition :     . (x04)
UndoFlag   :     . (x00)     BeforeAfter:     A (x41)
RecLength :    54 (x0036)    IO Time    : 2013-05-25 18:59:56.995530
IOType     :     5 (x05)     OrigNode   :   255 (xff)
TransInd   :     . (x03)     FormatType :     R (x52)
SyskeyLen :     0 (x00)     Incomplete :     . (x00)
AuditRBA   :        115       AuditPos   : 48669052
Continued :     N (x00)     RecCount   :     1 (x01)
2013-05-25 18:59:56.995530 Insert             Len    54 RBA 16760262
Name: MANZOOR.EMP
___________________________________________________________________
Reading ./dirdat/fg000009, current RBA 16760430, 200000 records
Report at 2013-05-25 19:57:24 (activity since 2013-05-25 18:49:12)

===== Need to know the scn for the last transaction up to which we have already completed for the 1st transaction.

Logdump 777 >open ./dirdat/fg000009
Current LogTrail is /golden_gate/dirdat/fg000009
Logdump 778 >ghdr on
Logdump 779 >detail on
Logdump 780 >detail data
Logdump 781 >pos 16760262
Reading forward from RBA 16760262
Logdump 782 >ggstoken on
Logdump 783 >ggstoken detail
Logdump 784 >n
___________________________________________________________________
Hdr-Ind    :     E (x45)     Partition :     . (x04)
UndoFlag   :     . (x00)     BeforeAfter:     A (x41)
RecLength :    54 (x0036)   IO Time    : 2013/05/25 18:59:56.995.530
IOType     :     5 (x05)     OrigNode   :   255 (xff)
TransInd   :     . (x03)     FormatType :     R (x52)
SyskeyLen :     0 (x00)     Incomplete :     . (x00)
AuditRBA   :        115       AuditPos   : 48669052
Continued :     N (x00)     RecCount   :     1 (x01)
2013/05/25 18:59:56.995.530 Insert               Len    54 RBA 16760262
Name: MANZOOR.EMP
After Image:                                             Partition 4   G s
0000 000a 0000 0006 3230 3030 3030 0001 0008 0000 | ........200000......
0004 5465 7374 0002 000a 0000 0006 3230 3030 3030 | ..Test........200000
0003 000a 0000 0006 3230 3030 3030                | ........200000
Column     0 (x0000), Len    10 (x000a)
0000 0006 3230 3030 3030                          | ....200000
Column     1 (x0001), Len     8 (x0008)
0000 0004 5465 7374                               | ....Test
Column     2 (x0002), Len    10 (x000a)
0000 0006 3230 3030 3030                          | ....200000
Column     3 (x0003), Len    10 (x000a)
0000 0006 3230 3030 3030                          | ....200000
GGS tokens:
TokenID x52 'R' ORAROWID         Info x00 Length   20
4141 4153 6859 4141 4641 4141 416e 6441 4271 0001 | AAAShYAAFAAAAndABq..
TokenID x4c 'L' LOGCSN           Info x00 Length    7
3139 3637 3336 36                                 | 1967366
TokenID x36 '6' TRANID           Info x00 Length    9
362e 3232 2e31 3532 38                            | 6.22.1528

== The scn no is 1967366 , hence we need to start the
alter replicat RGRP1, extseqno 09 extrba 0
start replicat RGRP1 aftercsn 1967366

== Now we need to start the 2 nd replicat.
alter replicat RGRP2 EXTSEQNO 12 EXTRBA 0
start replicat rgrp2

=======================

Wednesday, 26 December 2012

Points to Remember

Points to Remember.
==================

A) 11gR1 Rac - ONS is not coming up in one server (AIX).
========================================================

Check the file /etc/netsvc.conf

B) RMAN Backup / Restoration using SBT.
======================================
run
{
ALLOCATE CHANNEL chnl1 TYPE 'SBT_TAPE';
SEND 'NB_ORA_CLIENT=rhel11g1,NB_ORA_POLICY=bkp_policy,NB_ORA_SERV=rhelmastersrv,NB_ORA_SCHED=BKP_SCH';
BACKUP DATABASE PLUS ARCHIVELOG ALL;
BACKUP CURRENT CONTROLFILE FORMAT 'rman_arch_%d_%U_%t.ctl';
release channel chnl1;
}

run
{
ALLOCATE CHANNEL chnl1 TYPE 'SBT_TAPE';
SEND 'NB_ORA_CLIENT=rhel11g1,NB_ORA_POLICY=bkp_policy,NB_ORA_SERV=rhelmastersrv,NB_ORA_SCHED=BKP_SCH';
restore database;
recover database;
release channel chnl1;
}

For incomplete recovery.

run
{
allocate channel chnl1 type 'SBT_TAPE';
SEND 'NB_ORA_CLIENT=rhel11g1,NB_ORA_POLICY=bkp_policy,NB_ORA_SERV=rhelmastersrv,NB_ORA_SCHED=BKP_SCH';
set until sequence 5000 thread 1;
restore controlfile;
alter database mount;
RESTORE DATABASE;
RECOVER DATABASE;
RELEASE CHANNEL chnl1;
}

C) Static Instance registration in Listener. Eg.
================================================

lsnr_mydb1 =
(DESCRIPTION=
(ADDRESS_LIST =
(ADDRESS =
(PROTOCOL = tcp)
(HOST = rhel11g1.manzoor.com)
(PORT = 15000)
)
)
)
SID_LIST_lsnr_mydb1 =
(SID_LIST =
(SID_DESC =
(SID_NAME = mydb1)
(ORACLE_HOME = /u01/app/oracle/product/11.2/db )
)
)

D) Logminer.
============
https://community.oracle.com/thread/948629?tstart=0
http://docs.oracle.com/cd/B14117_01/server.101/b10825/logminer.htm

Prereq - Supplemental logging should be enabled in the db
alter database add supplemental log data;

SQL> alter system set utl_file_dir='/mydb1/oradata' scope=spfile;

Restart the db.

Build the dictionary:-

begin
dbms_logmnr_d.build
(
dictionary_filename => 'dictionary.ora',
dictionary_location => '/mydb1/oradata',
options => dbms_logmnr_d.store_in_flat_file
);
end;
/

Add the archived log files to be analyzed:-

begin
dbms_logmnr.add_logfile
(
logfilename => '/u01/app/oracle/mydb1/arch_728_1_774698775.arc',
options => dbms_logmnr.new
);
end;
/

begin
dbms_logmnr.add_logfile
(
logfilename => '/u01/app/oracle/mydb1/arch_729_1_774698775.arc',
options => dbms_logmnr.addfile
);
end;
/

Start the logminer:-

begin
dbms_logmnr.start_logmnr( dictfilename=>'/mydb1/oradata/dictionary.ora');
end;
/

Select the results from V$logmnr_contents

Stop the logminer once done

SQL> begin
DBMS_LOGMNR.END_LOGMNR();
end;
/

Logminer using Redo log files.
======================

SQL> desc dbms_logmnr_d;
PROCEDURE BUILD
Argument Name                  Type                    In/Out Default?
------------------------------ ----------------------- ------ --------
DICTIONARY_FILENAME            VARCHAR2                IN     DEFAULT
DICTIONARY_LOCATION            VARCHAR2                IN     DEFAULT
OPTIONS                        NUMBER                  IN     DEFAULT
PROCEDURE SET_TABLESPACE
Argument Name                  Type                    In/Out Default?
------------------------------ ----------------------- ------ --------
NEW_TABLESPACE                 VARCHAR2                IN

select sql_id, to_char(SQL_EXEC_START,'DD-MM-YY HH24:MI:SS'), sql_opname from dba_hist_active_sess_history where sql_opname = 'TRUNCATE TABLE';

SQL> select name , sequence# from V$archived_log where sequence# between 118 and 123;
NAME
--------------------------------------------------------------------------------
SEQUENCE#
----------
/u01/app/oracle/fast_recovery_area/SOURCE/archivelog/2013_06_05/o1_mf_1_118_8tzwnyc5_.arc
       118
/u01/app/oracle/fast_recovery_area/SOURCE/archivelog/2013_06_05/o1_mf_1_119_8tzwo2kk_.arc
       119
/u01/app/oracle/fast_recovery_area/SOURCE/archivelog/2013_06_05/o1_mf_1_120_8tzwo8gt_.arc
       120
/u01/app/oracle/fast_recovery_area/SOURCE/archivelog/2013_06_05/o1_mf_1_121_8tzws9z6_.arc
       121
/u01/app/oracle/fast_recovery_area/SOURCE/archivelog/2013_06_05/o1_mf_1_122_8tzwsjpk_.arc
       122
/u01/app/oracle/fast_recovery_area/SOURCE/archivelog/2013_06_05/o1_mf_1_123_8tzy3wkl_.arc
       123

SQL> EXECUTE DBMS_LOGMNR_D.BUILD (OPTIONS => DBMS_LOGMNR_D.STORE_IN_REDO_LOGS);
PL/SQL procedure successfully completed.

SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE (logfilename => '/u01/app/oracle/fast_recovery_area/SOURCE/archivelog/2013_06_05/o1_mf_1_118_8tzwnyc5_.arc', options => DBMS_LOGMNR.NEW);
PL/SQL procedure successfully completed.
SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE (logfilename => '/u01/app/oracle/fast_recovery_area/SOURCE/archivelog/2013_06_05/o1_mf_1_119_8tzwo2kk_.arc', options => DBMS_LOGMNR.ADDFILE);
PL/SQL procedure successfully completed.
SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE (logfilename => '/u01/app/oracle/fast_recovery_area/SOURCE/archivelog/2013_06_05/o1_mf_1_120_8tzwo8gt_.arc', options => DBMS_LOGMNR.ADDFILE);
PL/SQL procedure successfully completed.
SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE (logfilename => '/u01/app/oracle/fast_recovery_area/SOURCE/archivelog/2013_06_05/o1_mf_1_121_8tzws9z6_.arc', options => DBMS_LOGMNR.ADDFILE);
PL/SQL procedure successfully completed.
SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE (logfilename => '/u01/app/oracle/fast_recovery_area/SOURCE/archivelog/2013_06_05/o1_mf_1_122_8tzwsjpk_.arc', options => DBMS_LOGMNR.ADDFILE);
PL/SQL procedure successfully completed.
SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE (logfilename => '/u01/app/oracle/fast_recovery_area/SOURCE/archivelog/2013_06_05/o1_mf_1_123_8tzy3wkl_.arc', options => DBMS_LOGMNR.ADDFILE);
PL/SQL procedure successfully completed.
Below are the redo log files - its is mandatory to add because the dictory is stored in the redo log files, or we will get an error as below.
ERROR at line 1:
ORA-01371: Complete LogMiner dictionary not found
ORA-06512: at "SYS.DBMS_LOGMNR", line 58
ORA-06512: at line 1

SQL> select member from V$logfile;
MEMBER
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
/u01/app/oracle/oradata/source/redo03.log
/u01/app/oracle/oradata/source/redo02.log
/u01/app/oracle/oradata/source/redo01.log

SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE (logfilename => '/u01/app/oracle/oradata/source/redo03.log',options => dbms_logmnr.addfile);
PL/SQL procedure successfully completed.
SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE (logfilename => '/u01/app/oracle/oradata/source/redo02.log', options => dbms_logmnr.addfile);
PL/SQL procedure successfully completed.
SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE (logfilename => '/u01/app/oracle/oradata/source/redo01.log' , options => dbms_logmnr.addfile);
PL/SQL procedure successfully completed.
SQL> EXECUTE DBMS_LOGMNR.START_LOGMNR (OPTIONS =>DBMS_LOGMNR.DDL_DICT_TRACKING + DBMS_LOGMNR.DICT_FROM_REDO_LOGS);
PL/SQL procedure successfully completed.

SQL> select count(*) from V$logmnr_contents;
COUNT(*)
----------
    603198

-- Now filter the data as per the requirement from V$logmnr_contents.

SQL> exec dbms_logmnr.end_logmnr;
PL/SQL procedure successfully completed.

###############################

Analyzing the Log files of an database using Another database, e.g. Analyzing the logfiles of
Production database using the uat database on another server,

Note . The database in which we are analyzing (here uat) should be same / higher version of the database (here prod) from which we got the logfiles.

For this eg. the prod database is 10g and uat is 11g.

Prod database
-----------------
SQL> select * from V$log;

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
1 1 4 52428800 2 YES INACTIVE 531894 28-MAY-15
2 1 5 52428800 2 NO CURRENT 574844 28-MAY-15
3 2 5 52428800 2 NO CURRENT 615297 03-JUN-15
4 2 4 52428800 2 YES INACTIVE 594874 28-MAY-15

SQL> create user manzoor identified by ahamed;

User created.

SQL> grant dba to manzoor;

Grant succeeded.

SQL> conn manzoor/ahamed
Connected.
SQL> create table emp (emp_id number, ename varchar2(30));

Table created.

SQL> create table dept (dept_id number, dname varchar2(30));

Table created.

SQL> begin
2 for i in 1..1000 loop
3 insert into emp values (i, dbms_random.string('U',30));
4 insert into dept values (i, dbms_random.string('U',30));
5 end loop;
6 commit;
7 end;
8 /

PL/SQL procedure successfully completed.

SQL> select count(*) from emp;

COUNT(*)
----------
1000

SQL> select count(*) from dept;

COUNT(*)
----------
1000

SQL> select SUPPLEMENTAL_LOG_DATA_MIN from v$database;

SUPPLEME

--------

SQL> alter database add supplemental log data;

Database altered.

SQL> select SUPPLEMENTAL_LOG_DATA_MIN from v$database;

SUPPLEME

--------

YES

SQL> EXECUTE DBMS_LOGMNR_D.BUILD( OPTIONS=> DBMS_LOGMNR_D.STORE_IN_REDO_LOGS);

PL/SQL procedure successfully completed.

SQL> select name, sequence# FROM V$ARCHIVED_LOG WHERE DICTIONARY_BEGIN='YES';

NAME SEQUENCE#

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

+DATA1/devdb/1_9_770241379.dbf 9

SQL> select name, sequence# FROM V$ARCHIVED_LOG WHERE DICTIONARY_END='YES';

NAME SEQUENCE#

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

+DATA1/devdb/1_9_770241379.dbf 9

-- Initially before performing the DML's the current sequence was 5 , the dictionary is created in sequence 9, so we required the sequence 5 to 9 for analyzing.

-- Here the filesystem is ASM in the target so we will make a copy to normal filesystem.

RMAN> copy archivelog '+DATA1/devdb/1_5_770241379.dbf' to '/home/oracle/sequence5.arc';

Starting backup at 04-JUN-15

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=131 instance=devdb1 devtype=DISK

channel ORA_DISK_1: starting archive copy

input archive log thread=1 sequence=5 recid=9 stamp=881471729

output filename=/home/oracle/sequence5.arc recid=16 stamp=881472890

channel ORA_DISK_1: archivelog copy complete, elapsed time: 00:00:02

Finished backup at 04-JUN-15

RMAN> copy archivelog '+DATA1/devdb/1_6_770241379.dbf' to '/home/oracle/sequence6.arc';

RMAN> copy archivelog '+DATA1/devdb/1_7_770241379.dbf' to '/home/oracle/sequence7.arc';

RMAN> copy archivelog '+DATA1/devdb/1_8_770241379.dbf' to '/home/oracle/sequence8.arc';

RMAN> copy archivelog '+DATA1/devdb/1_9_770241379.dbf' to '/home/oracle/sequence9.arc';

Now scp the archive log files to the target server (UAT).

rhelrac1-> scp seq* oracle@192.168.0.30:/home/oracle/

The authenticity of host '192.168.0.30 (192.168.0.30)' can't be established.

RSA key fingerprint is ef:59:75:8b:95:5c:7f:2b:ce:56:01:e1:58:c0:f8:d0.

Are you sure you want to continue connecting (yes/no)? yes

Warning: Permanently added '192.168.0.30' (RSA) to the list of known hosts.

oracle@192.168.0.30's password:

sequence5.arc 100% 13MB 13.2MB/s 00:01

sequence6.arc 100% 900KB 900.0KB/s 00:00

sequence7.arc 100% 12KB 12.0KB/s 00:00

sequence8.arc 100% 14MB 7.2MB/s 00:02

sequence9.arc 100% 9218KB 9.0MB/s 00:01

On the Target Server (UAT).

-- Add the logfiles from 5 to 6 to the logminer.

[oracle@standalone2 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Thu Jun 4 00:07:51 2015

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> exec dbms_logmnr.add_logfile(LogFileName => '/home/oracle/sequence5.arc', Options=>dbms_logmnr.NEW);

PL/SQL procedure successfully completed.

SQL> exec dbms_logmnr.add_logfile(LogFileName => '/home/oracle/sequence6.arc', Options=>dbms_logmnr.addfile);

PL/SQL procedure successfully completed.

SQL> exec dbms_logmnr.add_logfile(LogFileName => '/home/oracle/sequence7.arc', Options=>dbms_logmnr.addfile);

PL/SQL procedure successfully completed.

SQL> exec dbms_logmnr.add_logfile(LogFileName => '/home/oracle/sequence8.arc', Options=>dbms_logmnr.addfile);

PL/SQL procedure successfully completed.

SQL> exec dbms_logmnr.add_logfile(LogFileName => '/home/oracle/sequence9.arc', Options=>dbms_logmnr.addfile);

PL/SQL procedure successfully completed.

SQL> exec dbms_logmnr.start_logmnr(OPTIONS =>DBMS_LOGMNR.DICT_FROM_REDO_LOGS);

PL/SQL procedure successfully completed.

SQL> SELECT COUNT(*) FROM v$LOGMNr_CONTENTS WHERE seg_owner = 'MANZOOR';

COUNT(*)

----------

2003

SQL> select operation, count(*) from V$logmnr_contents where seg_owner = 'MANZOOR' group by operation;

OPERATION COUNT(*)

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

DDL 2

INSERT 2000

SQL> select sql_redo from V$logmnr_contents where seg_owner = 'MANZOOR' and operation = 'DDL';

SQL_REDO

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

create table emp (emp_id number, ename varchar2(30));

create table dept (dept_id number, dname varchar2(30));

=====================================

E. Patching in 11.1.0.7.
======================

1. Stop the service running in oracle home

$ srvctl stop database -d dbname
$ srvctl stop listener -n nodename
$ srvctl stop asm -n nodename

2. Stop the CRS - root user

# crsctl stop crs

3. Grant the read permission for other to /etc/init.cssd file - root user

chmod o+r /etc/init.cssd

4. Run the Preroot patch for on crs home as root user.

cd /u01/crs/PATCH/10248535
custom/scripts/prerootpatch.sh -crshome /u01/crs -crsuser oracle

5. Run the prepatch script as oracle user on crs/asm/db home.

$ cd /psu/PATCH/10248535
$ custom/scripts/prepatch.sh -crshome /u01/crs
$ custom/server/10248535/custom/scripts/prepatch.sh -dbhome /u01/app/oracle/product/11.1/asm
$ custom/server/10248535/custom/scripts/prepatch.sh -dbhome /u01/app/oracle/product/11.1/db

6. Apply the crs patch on crs home as oracle user.

$ export ORACLE_HOME=/u01/crs
$ export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:$PATH
$ cd /psu/PATCH/10248535
$ opatch napply -local -oh /u01/crs -id 10248535 -invPtrLoc $ORACLE_HOME/oraInst.loc
$ opatch lsinventory -local -invPtrLoc $ORACLE_HOME/oraInst.loc

6. Apply the database patch on asm home as oracle user

a. Set the environment variable and point to asm home.

$ export ORACLE_HOME=/u01/app/oracle/product/11.1/asm
$ export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:$PATH
$ cd /psu/PATCH/10248535
$ opatch napply custom/server/ -local -oh /u01/app/oracle/product/11.1/asm -id 10248535 -invPtrLoc $ORACLE_HOME/oraInst.loc
$ opatch lsinventory -local -invPtrLoc $ORACLE_HOME/oraInst.loc

7. Apply the database patch on db home as oracle user.

a. Set the environment variable and point to asm home.

$ export ORACLE_HOME=/u01/app/oracle/product/11.1/db
$ export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:$PATH
$ cd /psu/PATCH/10248535
$ opatch napply custom/server/ -local -oh /u01/app/oracle/product/11.1/db -id 10248535 -invPtrLoc $ORACLE_HOME/oraInst.loc
$ opatch lsinventory -local -invPtrLoc $ORACLE_HOME/oraInst.loc

8. Run the post patch script as oracle user.

$ custom/scripts/postpatch.sh -crshome /u01/crs
$ custom/server/10248535/custom/scripts/postpatch.sh -dbhome /u01/app/oracle/product/11.1/asm
$ custom/server/10248535/custom/scripts/postpatch.sh -dbhome /u01/app/oracle/product/11.1/db

9. Run the post patch root script as root user.

# chmod o-r /etc/init.cssd
# cd /psu/PATCH/10248535
# custom/scripts/postrootpatch.sh -crshome /u01/crs

10. Continue the above procedure on the remaining nodes.

F. Highwater mark enque Contention "enq: HW – contention" record the high wait time in AWR report
=================================================================================================

Version : 11.1.0.7
Oracle bug : 6376915

Description :

HW enqueue contention can occur for LOB segments which are ASSM managed
as space allocation only acquires one block at a time.

With this fix ASSM lobs get a minimum number of chunks based
on the value of event 44951 (up to 1024) which should help
remove enqueue contention.

Fix :

The fix is already included in 11.1.0.7 and 10.2.0.4/10.2.0.5 but not implemented until the event is enabled
- for 10.2.0.3 Download and apply the patch for BUG 6376915:

for Spfile

ALTER SYSTEM SET EVENT='44951 TRACE NAME CONTEXT FOREVER, LEVEL 1024' scope=spfile;

for pfile

EVENT="44951 TRACE NAME CONTEXT FOREVER, LEVEL 1024"

G. Enable Rac.
==============

$ export ORACLE_HOME=/u01/app/oracle/product/11.1/db
$ cd $ORACLE_HOME/rdbms/lib
$ make -f ins_rdbms.mk rac_on
$ make -f ins_rdbms.mk ioracle

H. Disable RAC.
===============

$ export ORACLE_HOME=/u01/app/oracle/product/11.1/db
$ cd $ORACLE_HOME/rdbms/lib
$ make -f ins_rdbms.mk rac_off
$ make -f ins_rdbms.mk ioracle

I. Enable partitioning.
=======================

$ chopt enable partitioning

J. Disable partitioning.
========================

$ chopt disable partitioning

K. Attach an oracle home to inventory.
======================================

export ORACLE_HOME=/u01/app/oracle/product/11.1/db
cd $ORACLE_HOME/bin/oui
./attachHome.sh -silent -attachHome "CLUSTER_NODES={rhel11g1, rhel11g2}" LOCAL_NODE=rhel11g1 -invPtrLoc $ORACLE_HOME/oraInst.loc
./runInstaller.sh -silent -updateNodeList CLUSTER_NODES=rhel11g1,rhel11g2 ORACLE_HOME=$ORACLE_HOME -local -invPtrLoc $ORACLE_HOME/oraInst.loc

L. Pre-requisite for using lowerr version db with higher version crs.
=====================================================================

Grid version -11gr2 (11.2.0.2)
db version -11gr1 (11.1.0.7)

Inorder to use the lower version db with higher version crs we need
to pin all the nodes in the cluster.

We need the below to be executed on one node as root user.

# cd /u01/crs/bin
# ./crsctl pin css -n rhel11g1 rhel11g2

M. Auto startup in AIX.

a. Script to be enterd in a file eg. /etc/rc.oracle
b.this file has to be called in /etc/inittab.

M . Getting error as Below in RHEL 5
====================================

/etc/profile[40]: ulimit: pipe: is read only

solution :

if [ \$USER = "oracle" ]; then
if [ \$SHELL = "/bin/ksh" ]; then
##ulimit -p 16384
ulimit -u 16384
ulimit -n 65536
else
ulimit -u 16384 -n 65536
fi
fi

Patch 11gr2

1. Download the patch from Metalink.

2. Make a new directory and copy the patch to the location (Directory should be empty)

eg. $ mkdir -p /home/oracle/patch

$ cp p* /home/oracle/patch

3. For patching we can use the opatch auto utitlity to patch both GI & DB homes, this has to be run on each node.

$ cd /home/oracle/patch
$ unzip p*

$ opatch auto /home/oracle/patch -ocmrf ocm.rsp

Tuesday, 11 December 2012

AIX - OS Readiness for 11gRac

AIX Os -Prerequsite for 11gr2.

A. System. Parameters.

Maixmum no. of userprocess - maxuproc - 16384
No. of arguments - ncargs - 128

-> list the current

# lsattr -E -l sys0 -a maxuproc
# lsattr -E -l sys0 -a ncargs

-> To change to the desired value.

# chdev -l sys0 -a maxuproc=16384
# chdev -l sys0 -a ncargs=128

B. Tune Virtual memory parameters. IBM recommended numbers are:

minperm%=3
maxperm%=90
maxclient%=90
lru_file_repage=0
strict_maxperm=0
strict_maxclient=1
page_steal_method=1
lru_poll_interval=10

Script to change the parameters:-

#!/usr/bin/ksh
vmo -p -o maxperm%=90;
vmo -p -o minperm%=3;
vmo -p -o maxclient%=90;
vmo -p -o lru_file_repage=0;
vmo -r -o page_steal_method=1; (need to reboot to take into effect)
vmo -p -o strict_maxclient=1
vmo -p -o strict_maxperm=0;
vmo -p -o lru_poll_interval=10

C. Do not assign PVIDs (Physical Volume IDs) to disks or volumes that are being used for ASM Diskgroups. PVIDs should be cleared on all nodes from any candidate disks or volumes prior to being added to an ASM Diskgroup. Once a disk or volume is added to an ASM Diskgroup, PVIDs should never be assigned after the fact, from any node in the cluster, including nodes that are being added to an existing cluster.

CAUTION: Assigning PVIDs to ASM disks will corrupt the disk header resulting in catastrophic data loss!!

To check

# lspv

hdisk0 0003286f04bc73ee rootvg active
hdisk1 0003286f867d77e1 rootvg active
hdisk2 0003286fb3470dae vg01 active
hdisk3 0003286fb3474190 vg01 active
hdisk4 0003286fb34747d1 vg01 active
hdisk5 0003286fb3474dff vg01 active
hdisk6 0003286fb3475428 vg01 active
hdisk7 0003286fb347607d vg01 active
hdisk8 0003286fb34766f3 vg01 active
hdisk9 0003286fb3476d70 vg01 active
hdisk10 0003286fb34773d5 vg01 active

The second column is the PVID.

To Change

# chdev -l hdiskn -a pv=clear

D. Network Consideration.

Ensure that the network tuning parameters are set in accordance with the following to ensure optimal interconnect performance:

tcp_recvspace = 65536
tcp_sendspace = 65536
udp_sendspace = ((DB_BLOCK_SIZE * DB_MULTIBLOCK_READ_COUNT) + 4 KB) but no lower than 65536
udp_recvspace = 655360 (Minimum recommended value is 10x udp_sendspace, parameter value must be less than sb_max)
rfc1323 = 1
sb_max = 4194304
ipqmaxlen = 512

NOTE: Failure to set the udp_sendspace will result in failure of root.sh for 11.2.0.2 GI installations, see Document 1280234.1.

To check
no -a | fgrep ephemeral
no -a | fgrep tcp
no -a | fgrep udp
no -a | fgrep max

To change
no -p -o tcp_ephemeral_low=9000 -o tcp_ephemeral_high=65500"
no -p -o udp_ephemeral_low=9000 -o udp_ephemeral_high=65500"
no -p -o udp_sendspace=65536 -o udp_recvspace=655360"
no -p -o sb_max=4194304"
no -p -o tcp_sendspace=65536
no -p -o tcp_recvspace=65536

E. Resever lock should not be set on the disks.

To check:-

# lsattr -El hdiskn | grep reserve

To Change:-

# chdev -l hdiskn -a reserve_lock=no

F. Checking the user capabilities.

Ensure that the GI and ORACLE owner account has the CAP_NUMA_ATTACH, CAP_BYPASS_RAC_VMM, and CAP_PROPAGATE capabilities.
This is required per the 11gR2 installation guide and it is also required for all pre-11gR2 installations.
Check and Set example for GRID user is as follows:

To check

# lsuser -f oracle | grep capabilities
# lsuser -f grid | grep capabilities

To Change

# chuser capabilitie=CAP_NUMA_ATTACH,CAP_BYPASS_RAC_VMM,CAP_PROPAGATE grid
# chuser capabilitie=CAP_NUMA_ATTACH,CAP_BYPASS_RAC_VMM,CAP_PROPAGATE oracle

G. Create a softlink for ssh and scp , because the oui will look for these tools in /usr/bin

ln -s /usr/local/bin/ssh /usr/bin/ssh
ln -s /usr/local/bin/scp /usr/bin/scp

H. Create a softlink for network parameter check tool no

ln -s /usr/sbin/no /etc/no"

I. Checking os version.

# oslevel

# uname -a

J. Checking memory .

-- Ram Size.

# lsattr -E -l sys0 -a realmem

-- Swap memory.

# lsps

Recommended by oralce if RAM is less than 2 gb then swap should be 2 * RAM size, for 2gb -16 gb the swap
should be size of the RAM, and above 16 gb the swap should be 16gb.

K. OS Fileset checking.

# lslpp -L | grep fileset

Need to apply the db one off patch on AIX server.
10190759 : PROCESSES CONSUMING ADDITIONAL MEMORY DUE TO 'USLA HEAP'

Monday, 3 December 2012

Changing Date time in Linux and in CRS

Chaning Date in Linux and in oracle grid 11.2

1) Chanage the timezone file

vi /etc/sysconfig/clock

Chnage the ZONE accordingly to set the timezone.

Here we have set the ZONE="SGT" , save and close the file.

2) Check the date

# date
Tue Nov 27 19:21:02 PST 2012

2) Create a link between the timezone and the localtime files.

ln -sf /usr/share/zoneinfo/Singapore /etc/localtime

3) Now checke the date

Wed Nov 28 11:22:22 SGT 2012

The date has been changed now.

Change timezone in Grid
=======================

1) Stop the crs.

2) Take the backup of the current timezone file.

cd /grid/11.2.0.3/crs/install
cp s_crsconfig_gridtest2_env.txt s_crsconfig_gridtest2_env.txt_bkp

3) Edit the file as per the timezone required.

vi s_crsconfig_gridtest2_env.txt

Change the TZ=SGT (set according to your timezone) and save and close the file.

4) Start the CRS.

Now check the time in instance.

SQL> select systimestamp from dual;

SYSTIMESTAMP
---------------------------------------------------------------------------
28-NOV-12 11.33.03.349919 AM +08:00

SQL> ! date
Wed Nov 28 11:33:08 SGT 2012

Saturday, 1 December 2012

Usefull Sites

Some Usefull sites
http://oracleinaction.com/11gr2rac/ - Good one on 11gr2 grid
http://balaoracledba.com/2013/12/23/step-by-step-install-of-oracle-rac-11gr2-on-oel-6-5-on-oracle-vm-virtual-box/
http://www.thegeekstuff.com/2011/11/strace-examples/ -- strace Examples
http://www.orafaq.com/maillist/oracle-l/2003/04/29/2369.htm
http://www.oracle.com/technetwork/articles/hunter-rac11gr2-iscsi-2-088698.html
http://oramurali.files.wordpress.com/2012/03/oracle11gr2-rac-solaris10-iscsi-zfs-vmware-rageshmoyan.pdf
http://www.oracledba.org/index.html
http://www.oraclelicensestore.com/en/licensing/tutorial/licensing-tutorial
http://oracleinnotiive.wordpress.com/2010/07/26/simple-guide-on-installing-2-nodes-oracle-10g-rac-on-solaris-10-64bit/
http://gundepudiln.blogspot.com/2007/03/install-10g-r2-rac-on-solaris-10-0606.html
http://easyoradba.com/2010/11/
http://nandakumarappsdba.blogspot.sg/2011/10/alert-log-diskgroup-mounted-dismounted.html

wait event- http://askdba.org/weblog/2008/03/hw-enqueue-contention-with-lob/
http://nadeemmohammed.wordpress.com/2012/09/24/oracle-11-2-standalone-database-creation-using-asm-manually/

http://myoracle4u.blogspot.com/2011/07/gpnp-grid-plug-and-play-in-11gr2-rac.html
http://computernetworkingnotes.com/network-administrations/dns-server.html
http://www.linuxquestions.org/questions/linux-newbie-8/configuration-to-access-internet-from-vmware-host%3Dwinxp-guest-on-vmware%3Dlinux-5-a-693486/
https://www.digitalocean.com/community/articles/how-to-install-the-bind-dns-server-on-centos-6

http://balaoracledba.com/2013/12/23/step-by-step-install-of-oracle-rac-11gr2-on-oel-6-5-on-oracle-vm-virtual-box/

FULL IMPORT-http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1463976400346989259
http://i.i.com.com/cnwk.1d/html/itp/Confio_Statistical_Analysis_Database_Performance_Delays.pdf

For more detail, see “Oracle Wait Interface, © Oracle
Press 2004” (1) as an excellent reference

http://www.dbspecialists.com/files/presentations/buffercache.html

about character sets

http://docs.oracle.com/cd/E11882_01/server.112/e10729/ch2charset.htm#i1006853

AIX Commands

http://www.ibm.com/developerworks/views/aix/libraryview.jsp?search_by=tips

domain indexes

http://docs.oracle.com/cd/B10500_01/appdev.920/a96595/dci07idx.htm

Important Oracle Support Notes.

Some of the important MOS Notes.

How To Diagnose Components With NON VALID Status In DBA_REGISTRY After an Upgrade [ID 753041.1]
Data Guard Support for Heterogeneous Primary and Physical Standbys in Same Data Guard Configuration (Doc ID

How do you manually install/deinstall Oracle Workspace Manager [ID 731576.1]

11gR2 Clusterware and Grid Home - What You Need to Know [ID 1053147.1]
How to Configure GoldenGate Extract When Adding or Removing Redo Log Threads in an Oracle RAC ? (Doc ID 1267901.1)
DNS and DHCP Setup Example for Grid Infrastructure GNS (Doc ID 946452.1)
How to Setup SCAN Listener and Client for TAF and Load Balancing [Video][Article ID 1188736.1]
How To Convert an 11gR2 GNS Configuration To A Standard Configuration Using DNS Only[Article ID 1489121.1]
11gR2 RAC: How to re-install the Grid Infrastructure without disturbing the RDBMS installations.[Article ID 1276975.1]
NOTE:456021.1 - How to Reinstall CRS Without Disturbing Installed Oracle RDBMS Home(s)

DBFS (White Paper/Demo): How To Setup & Configure 11.2.0.X DBFS Filesystems On RAC Cluster Configurations (CRS Managed) On Linux. (Doc ID 1591515.1)

MOS Doc: How ToMove the Central Inventory to Another Location [299260.1]

How to Check Whether Oracle Binary/Instance is RAC Enabled and Relink Oracle Binary in RAC [ID 284785.1]

ORA-28002: the password will expire within days [ID 1064334.1]

Manual installation, deinstallation and verification of Oracle Text 11gR2 [ID 970473.1]

LogMiner Utility Release 8.1.x - 10g [ID 291686.1] - 300395.1

Exact Steps To Migrate ASM Diskgroups To Another SAN Without Downtime. [ID 837308.1]

Note:271196.1 Automatic SQL Tuning - SQL Profiles.

How To Downgrade From Database 11.2 To Previous Release (includes 11.2.0.3-11.2.0.1) [ID 883335.1]

How to Reload the JVM in 11.2.0.x [ID 1112983.1]

Troubleshooting Oracle JVM [ID 1220993.1]

RAC and Oracle Clusterware Best Practices and Starter Kit (Platform Independent) [Article ID 810394.1]

How to Modify Public Network Information including VIP in Oracle Clusterware (Doc ID 276434.1)

How To : How to Recreate OCR/Voting Disk Accidentally Deleted- [Article ID 399482.1]

How to Modify Private Network Information in Oracle Clusterware (Doc ID 283684.1)

How to restore ASM based OCR after complete loss of the CRS diskgroup on Linux/Unix systems [ID 1062983.1]

How to Downgrade 11.2.0.3 Grid Infrastructure Cluster to Lower 11.2 GI or Pre-11.2 CRS [ID 1364946.1

1053147.1 - About 11gr2 cluster

Things to Consider Before Upgrading to 11.2.0.2 Grid Infrastructure/ASM [ID 1312225.1]==================

How To Downgrade From Database 11.2 To Previous Release (includes 11.2.0.3-11.2.0.1) [ID 883335.1]

How to Add a New Node and/or RAC Instance to an Existing 10gR2 CRS/RAC System [ID 1076916.1]

RAC and Oracle Cluster0. .....xmmxware Best Practices and Starter Kit (Platform Independent) [Article ID 810394.1]

Oracle RACOne Node -- Changes in 11.2.0.2 [ID 1232802.1]

[Article ID 1332452.1]

10gR2, 11gR1 and 11gR2 Oracle Clusterware (CRS / Grid Infrastructure) & RAC Command (crsctl, srvctl, cluvfy etc) Syntax and Reference

Package for disabling AWR without a Diagnostic Pack license in Oracle [ID 436386.1]

11gR2 Grid Infrastructure Single Client Access Name (SCAN) Explained [ID 887522.1]

step By Step Guide On How To Configure And Test Client-Failover For Dataguard Switchover And Failover. [ID 740029.1]

http://www.oracle.com/technetwork/database/features/availability/oracle-database-maa-best-practices-155386.html

RAC and Oracle Clusterware Best Practices and Starter Kit (Linux) [ID 811306.1]

How To Reload the SYS.DBMS_STATS Package [ID 1310365.1]

Problem : DBMS_STATS_INTERNAL Invalid upgrading to 11.2.0.2 or 11.2.0.3

NOTE:1050908.1 - Troubleshoot Grid Infrastructure Startup Issues

NOTE:1053147.1 - 11gR2 Clusterware and Grid Home - What You Need to Know

NOTE:1069369.1 - How to Delete or Add Resource to OCR

NOTE:942166.1 - How to Proceed from Failed 11gR2 Grid Infrastructure (CRS) Installation

NOTE:969254.1 - How to Proceed from Failed Upgrade to 11gR2 Grid Infrastructure on Linux/Unix

Mount 10g Database reports error ORA-29701 when using ASM 11gR2 SIHA [ID 1061845.1]

Pre 11.2 Database Issues in 11gR2 Grid Infrastructure Environment [ID 948456.1]

CRS-4665 Error while trying to pin node [ID 1299367.1]

Linux: How to Configure the DNS Server for 11gR2 SCAN [ID 1107295.1]

RAC and Oracle Clusterware Best Practices and Starter Kit (AIX) [ID 811293.1]

My Oracle Support Note 1291877.1- patch availability

How to Validate Network and Name Resolution Setup for the Clusterware and RAC [ID 1054902.1]

Troubleshoot 11gR2 Grid Infrastructure/RAC Database runInstaller Issues [ID 1056322.1]

Note 395525.1 How to Enable Oracle SQLNet Client, Server, Listener, Kerberos and External procedure Tracing from Net Manager.

Ref: Why FRA Diskgroup Gets Mounted/Dismounted ? [ID 603204.1]

. Please refer to note 603505.1 for the rest of available recordings

WHEN AWR SNAPSHOTS ARE NOT GENERATED AUTOMATICALLY, HOW To DIAGNOSE. [ID 787409.1]

WRH$_ACTIVE_SESSION_HISTORY Does Not Get Purged [ID 387914.1]

10gR2, 11gR1 and 11gR2 Oracle Clusterware (CRS / Grid Infrastructure) & RAC Command (crsctl, srvctl, cluvfy etc) Syntax and Reference [ID 1332452.1]

How To Collect 10046 Trace (SQL_TRACE) Diagnostics for Performance Issues [ID 376442.1]

WARNING: Heavy Swapping Observed On System In Last 5 Mins.After upgrade to 11.2.0.3 [ID 1452790.1]
How to Modify SCAN Setting or SCAN Listener Port after Installation (Doc ID 972500.1)
Linux: How to Configure the DNS Server for 11gR2 SCAN (Doc ID 1107295.1)
How To Convert an 11gR2 GNS Configuration To A Standard Configuration Using DNS Only[Article ID 1489121.1

CTSSD Runs in Observer Mode Even Though No Time Sync Software is Running (Doc ID 1054006.1)

DNS and DHCP Setup Example for Grid Infrastructure GNS[Article ID 946452.1]

GNS configuration of RAC Database[Community Discussion ID 667324]

[Article ID 1264691.1] - exp/imp
[Article ID 1264715.1] - datapump
Oracle Server - Export and Import FAQ [ID 175624.1]
Note:556636.1 "Oracle Server - Export Data Pump and Import DataPump FAQ"

Configuring non-raw multipath devices for Oracle Clusterware 11g (11.1.0, 11.2.0) on RHEL5/OL5 [ID 605828.1]
How To identify a 'corrupt' row when error is raised but no row information provided [ID 869305.1]
Merged Incremental Backup Strategies [ID 745798.1]

It is important that you read MetaLink Doc 415884.1, if you are performing a migration to a
different platform with the same endian. Oracle suggests that you should perform a CONVERT DATABASE,
but later admits that you only need to perform a CONVERT on tablespaces containing UNDO segments.

how to Move Tablespaces Across Platforms Using Transportable Tablespaces With RMAN [ID 371556.1]
ORA-600/ORA-7445/ORA-700 Error Look-up Tool [ID 153788.1]
Master Note - Oracle GoldenGate [ID 1298817.1]
Note:215187.1 SQLT (SQLTXPLAIN) - Tool that helps to diagnose SQL statements performing poorly (Doc ID 215187.1)

Note:464246.1 - “Wait for stopper event to be increased” (http://srakwal.wordpress.com/2008/03/09/wait-for-stopper-event-to-be-increased/)

Information On Installed Database Components and Schemas [ID 472937.1]
How To Manually Install Data Mining In Oracle 11g? [ID 818314.1]

How to Specify Hidden Hints (Outlines) on SQL Statements in Oracle 8i [ID 92202.1]
Loading Hinted Execution Plans into SQL Plan Baseline.(787692.1)
How to Enable USE_STORED_OUTLINES Permanently [ID 560331.1]

How to Find out the Reason for PLS-00201 in PL/SQL[Article ID 269973.1]

How To Diagnose Components With NON VALID Status In DBA_REGISTRY After an Upgrade[Article ID 753041.1]
How to manage DB Control 11.x for RAC Database with emca [ID 578011.1]

Oracle ASMLib Software Update Policy for Red Hat Enterprise Linux Supported by Red Hat (Doc ID 1089399.1)
How To Rename A Diskgroup On ASM 11gR2?[Article ID 948040.1]

Handling Block Corruptions in Oracle7 / 8 / 8i / 9i / 10g / 11g

How to Add Node/Instance or Remove Node/Instance in 10gR2, 11gR1 and 11gR2 Oracle Clusterware and RAC [ID 1332451.1]
How to Remove a Listener From CRS Using Netca in Silent Mode [ID 452485.1]

Requirements for Installing Oracle 11gR2 RDBMS on RHEL6 or OL6 64-bit (x86-64) [ID 1441282.1]

Master Note: Troubleshooting Oracle Background Processes [ID 1509616.1]
How to Diagnose Slow TNS Listener / Connection Performance [ID 557416.1]

What to Do if 11gR2 Grid Infrastructure is Unhealthy [ID 1068835.1]

High 'cursor: pin S wait on X' and/or 'library cache lock' Waits. Cause: Shared Pool/Buffer Cache Resize Activity [ID 742599.1]

10gR2, 11gR1 and 11gR2 Oracle Clusterware (CRS / Grid Infrastructure) & RAC Command (crsctl, srvctl, cluvfy etc) Syntax and Reference [ID 1332452.1] To Bottom

http://www.idevelopment.info/data/Oracle/DBA_tips/Oracle10gRAC/CLUSTER_23.shtml#Remove the Node to be Deleted from Oracle Clusterware

undocumented parameter in 11g
http://oracleinaction.blogspot.com/2013/01/parameters-in-oracle-11g-in-this-post-i.html

12c em installation
http://www.oracle-base.com/articles/12c/cloud-control-12cr1-installation-on-oracle-linux-5-and-6.php

Encrypt and decrypt data
http://www.oracle-base.com/articles/8i/data-encryption.php

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1396058400346694178

http://arup.blogspot.com/2008/08/diagnosing-library-cache-latch.html

Error faced while oem config
http://taliphakanozturken.wordpress.com/tag/config-ora-01921-role-name-mgmt_user-conflicts-with-another-user-or-role-name/

Issues faced.

CREATE INDEX <tablename> ON <Column Name>
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [smboPut:fixedlen2], [4], [7], [0],
[8198014], [], [], [], [], [], [], []
Database Version: 11.2.0.3 RAC
Workaround:
Set following session level perameter
alter session set "_newsort_enabled"=false;
And recreate index.

vktm Background process consumes high cpu

Set the below parameter (11g) - Consult oracle support
_disable_highres_ticks=false
_timer_precision=900

Changing hint using outlines
==================
-

UPDATE OUTLN.OL$HINTS
SET OL_NAME=DECODE(OL_NAME,'HINTSQL','ORIGINALSQL','ORIGINALSQL','HINTSQL')
WHERE OL_NAME IN ('ORIGINALSQL','HINTSQL');

List all undocumented parameter:-

SET linesize 235
col Parameter FOR a50
col SESSION FOR a28
col Instance FOR a55
col S FOR a1
col I FOR a1
col D FOR a1
col Description FOR a90

SELECT
a.ksppinm "Parameter",
decode(p.isses_modifiable,'FALSE',NULL,NULL,NULL,b.ksppstvl) "Session",
c.ksppstvl "Instance",
decode(p.isses_modifiable,'FALSE','F','TRUE','T') "S",
decode(p.issys_modifiable,'FALSE','F','TRUE','T','IMMEDIATE','I','DEFERRED','D') "I",
decode(p.isdefault,'FALSE','F','TRUE','T') "D",
a.ksppdesc "Description"
FROM x$ksppi a, x$ksppcv b, x$ksppsv c, v$parameter p
WHERE a.indx = b.indx AND a.indx = c.indx
AND p.name(+) = a.ksppinm
AND UPPER(a.ksppinm) LIKE UPPER('%&1%')
ORDER BY a.ksppinm;

How to Diagnose Losing Characters , Getting "Funny" Output When Inserting or Selecting Other Than A-Z,a-z ( = Non English Data Like Chinese, Russian, Hebrew , Insert Any Language Here to the List That is Not English) in/from CHAR, VARCHAR2, LONG or CLOB (Doc ID 1628060.1)
Changing the NLS_CHARACTERSET to AL32UTF8 / UTF8 (Unicode) in 8i, 9i , 10g and 11g (Doc ID 260192.1)

How to Clone an 11.2.0.3 Grid Infrastructure Home and Clusterware (Doc ID 1413846.1)

To Bottom

ASM Admin - 10g

ASM Administration and Managment

ASM is a database file system that provides a cluster file system and volume manager capabilities
for oracle datailes that are integration into the oracle database. The asm environment
provides the performance of raw I/O with the easy management of a file system. It simplifies
database administration by eliminating the need to manage potentially thoushands of oracle
database files in a direct manner.

ASM simplifies storage management by enabling you to divide all the available storage into disk
groups. You manage the small set of diskgroup and asm automates the placement of database files
with those disk groups.

ASM dividies file into pieces and spreads them evenly across all the disks. This is the key
difference between the traditionaly striping techniques that used mathematical functions to stripe
complete logical volumes independent of files or directories. Striping requires careful capacity
planning at the beginning, as adding new volume requeires rebalancing and downtime, but with the
asm whenever a new storage is added or removed, asm does not restripe all the data, it just moves
the amount of data proportional to the amount of storage added or removed to redistribute the fies
evenly and maintain a balanced i/o load accross the disks. This occur while the databae is up
and running and totally transperant to the database and end user applications.

Disk groups:-

ASM diskgroup is a group of disks that are managed together as a single unit of storage. There are
three type of redundancy are provided by the disk groups they are external, normal and high reduancy,
an asm disk can be a partition of a LUN or a NAS deives

Allocation unit:-

ASM disks are divided into number of units or storage blocks that are small enough not to be hot.
The allocation unit of a storage is large enough for efficient sequential access. The allocation
unit defaults to 1MB in size and is sufficient for most configurations. ASM allows you to change
that allocation unit size, but that is not normally required unless the ASM hosts are very large.

Failure groups:-

Failure groups define ASM disks that share a common potential failure mechanism. A failure group
is a subset of disks of a disk group dependent on common hardware resource whose failure must
be tolerated, it is important only for normal and high redundancy configuration. Redundant copies of
the same data are placed in different failure groups. Failure groups are used to determine which
ASM disks are used for storig redundant copies of data. By default, each disk in an individual
failure group.

For eg. If you have two may mirroring for a file, asm automatically stores the redundant copies of
the file extents in seperate failure groups. Failure groups apply only to the normal and
high redundancy disk groups and are not applicable for external redundancy disk groups.

ASM Files:-

Fils written on asm disk are called the asm files. Normall all the asm files start with (+)
plus symbol, although the names are automatically generated by ASM, we can define a meaningful
/ userdefined name for each asm file. Each asm file is completely contained in a single
disk group and every spread across all the disks in that disk group.

Storage area network:-

A storage area network is the networked storage device connected via uniquely identified host
bus adapters (HBA's). This storage is divided in to LUN's and each LUN is logically represeted
as a single disk to the operating system.

The ASM disk are either LUN's or disk partitions. They are logically represented to the ASM
as raw device to the ASM. The name and the path of the raw device is dependent on the
operating system. For eg in SUN the raw device has the name as cNtNdNsN

cN - Controller number
tN - is the target ID (SCSI ID)
dN - is the disk number
sN - is the slice number.

so when you see a raw partition in sun listed as c0t0d2s3 we can say that the deivce is the
3rd partition of 2nd device connected to first controller of first port.

A typical linux configuration uses a straight disks. RAW functionality was an afterthought. However
linux imposes a limit of 255 possible raw files and this limiation is one of the reasons for the
development of oracle cluster file system and the use of ASMLib. Raw devices are typically
stored in /dev/raw and are named raw1 to raw255.

ASM Instance:-

Asm instance has their own SGA and background process which is similar to RDBMS architechture.

1) ASM instance does not have a data dictionary , as metadata is not stored
in the dictionary. Asm metadata is small and stored in the disk headers.

2) You can connect to ASM only using sysdba either using the os authentication
or by remotely using the password file.

Startup Modes:-

NO MOUNT -> Starts up the ASM instance without mounting any diskgroups.

MOUNT -> Starts up the ASM instance and mounts the diskgroups.

NORMAL -> Starts up the ASM instance monunts the diskgroup and allow connection from database.
This is the default startup option.

FORCE -> Starts up MOUNT after shutdown abort.

Shutdown :-

You cannot shutdown the ASM instance when database are connected to the ASM instance.

SQL> shutdown
ORA-15097: cannot SHUTDOWN ASM instance with connected RDBMS instance

You need to shutdown all the database which are using the ASM instnace before proceeding
with shuting down the ASM instance.

Shutdown abort -> ASM instance is termindated immediately and it does not dismount the diskgroup
in orderly fashion. All the database instance connected to this ASM instance will be terminated.
Next startup requires ASM recovery The database instance get terminated because it doest not
get the access to the storgae system managed by the ASM instance.

Normal, immediate, Transactional -> The asm instance shutdown once all the ASM sql
are completed ( The database should be down before shuting down the asm instance)

ASM Backgroup processes:-

ASM instance share the similar architechture of RDBMS instance, hence most of the
process are same in asm instance, below are the asm background processes in the
database instance.

ASMB -> The asmb background process runs in the database instance and connects to the
foreground process in an ASM instance. Over this connections periodic messages are
exchanged to update the statistics and to verify that both instances are healthy.
All extent maps describing the open files are sent to the database instance via asmb.
If the extent of a open file is relocated or the status for the disk is changed, messages
are received by the ASMB process in the affected database instance.

During operation that requires ASM intervention, such as file creation b a database
foreground , that database foreground connects directly to the ASM instance to perform
the operation. Each database maintains a pool of connections to the ASM instance to
avoid the overhead of reconnecting for every file operation.

O001 -> A group of slave process in O001 -> O0010 establishes a connection to the ASM instance.
and these slave process are used as connection pool for database process. Database
processes can send messages to the ASM instance using these slave proess. For eg.
Opening a file sends the open request to the ASM instance via a slave. However slave
are not use for long running operation, such as creating a file. The slave connections
eliminate the overhead of logging into the asm instance for short requests. These
slave process are automatically shutdown when not in use.

RBAL ->

Initialization paramters:-

instance_type This parameter instructs oracle executables about the instance type. By default, the oracle
executables assume the instance type is a database instance. This is the only mandatory
parameter in asm instance. All the other parameters have suitable default parameters
when not specified.

asm_power_limit - Sets the power limit for disk rebalancing. This parameter defaults to 1. Valid values are
0 to 11.

asm_diskstring - A comma seperated list of strings that limits the set of disks that asm discovers. This
parameter accepts wildcard characters. Only disks that match one of the strings are discoverd.

asm_diskgroups -a list of names of disk groups to be mounted by an asm instance at startup , or when
alter diskgroup all mount statement is used. If this parameter is not specified, no diskgroups
are mounted. The parameter is dynamic when using the spfile.

large_pool_size - the internal packages used by the asm istance are executed from the large pool, and therefore
you should set the value of the initialization parameter large_pool_size to a greater value than a 8mb.

Creating ASM Diskgroups:-

SQL> col path form a40;
SQL> select path, state, header_status, total_mb, free_mb, create_Date, group_number from V$asm_disk;

PATH STATE HEADER_STATU TOTAL_MB FREE_MB CREATE_DA GROUP_NUMBER
---------------------------------------- -------- ------------ ---------- ---------- --------- ------------
ORCL:MANZY3 NORMAL PROVISIONED 1137 0 0
ORCL:MANZY1 NORMAL PROVISIONED 964 0 0
ORCL:MANZY2 NORMAL PROVISIONED 964 0 0
ORCL:DISK1 NORMAL MEMBER 1286 1249 18-DEC-11 1
ORCL:DISK10 NORMAL MEMBER 1286 891 18-DEC-11 4
ORCL:DISK11 NORMAL MEMBER 1286 891 18-DEC-11 4
ORCL:DISK12 NORMAL MEMBER 1255 1020 18-DEC-11 2
ORCL:DISK2 NORMAL MEMBER 1286 1256 18-DEC-11 1
ORCL:DISK3 NORMAL MEMBER 1286 1255 18-DEC-11 1
ORCL:DISK4 NORMAL MEMBER 1255 1016 18-DEC-11 2
ORCL:DISK5 NORMAL MEMBER 1286 1254 28-FEB-12 3
ORCL:DISK6 NORMAL MEMBER 1286 1254 28-FEB-12 3
ORCL:DISK7 NORMAL MEMBER 1286 1253 28-FEB-12 3
ORCL:DISK8 NORMAL MEMBER 1255 1018 18-DEC-11 2
ORCL:DISK9 NORMAL MEMBER 1286 892 18-DEC-11 4
ORCL:NEWDISK1 NORMAL MEMBER 1019 997 28-FEB-12 5
ORCL:NEWDISK2 NORMAL MEMBER 1019 1003 28-FEB-12 5
ORCL:NEWDISK3 NORMAL MEMBER 1019 1003 28-FEB-12 5

Here we can see that we have 3 provisioned disks (ORCL:MANZY3,MANZY2/MANZY1) , we can create a new diskgroups
using the provisioned / canidate disks, if the header status shows as memeber then it means that its already
a part of a disk group. At any point of time a disk can only be part of only one diskgroup.

Creating diskgroup with external redundancy.

SQL> create diskgroup oradata external redundancy disk 'ORCL:MANZY3';

Diskgroup created.

SQL> select name, type, state from V$asm_diskgroup;

NAME TYPE STATE
------------------------------ ------ -----------
DATA1 EXTERN MOUNTED
DATA2 NORMAL MOUNTED
DATA3 EXTERN MOUNTED
FLASH EXTERN MOUNTED
NEWDISK EXTERN MOUNTED
ORADATA EXTERN MOUNTED

6 rows selected.

The diskgroup willbe mounted automatically once it is created. If we use spfile then the created disk
will dynamically get added to the asm_diskgroups parameter, so that each time the asm instance is
restarted the diskgroup will be mounted automatically.

Creating diskgroup with normal redundancy.

SQL> create diskgroup oranormal normal redundancy
failgroup fg1 disk 'ORCL:MANZY1'
failgroup fg2 disk 'ORCL:MANZY2'
/

Diskgroup created.

SQL> select a.group_number, b.name, b.type, a.failgroup, a.path from V$asm_disk a, V$asm_diskgroup b where
a.group_number = b.group_number order by 1;

GROUP_NUMBER NAME TYPE FAILGROUP PATH
------------ ------------------------------ ------ ------------------------------ ----------------------------------------
1 DATA1 EXTERN DISK2 ORCL:DISK2
1 DATA1 EXTERN DISK3 ORCL:DISK3
1 DATA1 EXTERN DISK1 ORCL:DISK1
2 DATA2 NORMAL DISK12 ORCL:DISK12
2 DATA2 NORMAL DISK4 ORCL:DISK4
2 DATA2 NORMAL DISK8 ORCL:DISK8
3 DATA3 EXTERN DISK6 ORCL:DISK6
3 DATA3 EXTERN DISK5 ORCL:DISK5
3 DATA3 EXTERN DISK7 ORCL:DISK7
4 FLASH EXTERN DISK11 ORCL:DISK11
4 FLASH EXTERN DISK10 ORCL:DISK10
4 FLASH EXTERN DISK9 ORCL:DISK9
5 NEWDISK EXTERN NEWDISK1 ORCL:NEWDISK1
6 ORADATA EXTERN MANZY3 ORCL:MANZY3
7 ORANORMAL NORMAL FG1 ORCL:MANZY1
7 ORANORMAL NORMAL FG2 ORCL:MANZY2

Each disk will be a part of a failgroup irrespective of a redunancy, if we have not defined any failgroup name
then the path (disk) name will the the failgroup name.

SQL> show parameter pfile;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------------------------
spfile string

Here am using the pfile for asm instance, we will change that to use the spfile.

a) Shutdown the database

rhelrac1-> srvctl stop database -d testthis

b) shutdown the asm instance

rhelrac1-> srvctl stop asm -n rhelrac2
rhelrac1-> srvctl stop asm -n rhelrac1

c) Create an spfile from the current pfile.

rhelrac1-> sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Sat Mar 17 07:08:39 2012

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Connected to an idle instance.

SQL> create spfile='/u01/app/oracle/admin/+ASM/pfile/spfile+ASM1.ora' from pfile;

File created.

SQL> exit
Disconnected

d) Modify the entry in the pfile to point the spfile. Open the pfile in vi editor
and remove all the parameters and add the below.

spfile=/u01/app/oracle/admin/+ASM/pfile/spfile+ASM1.ora

e) Login to the other nodes and create the spfile from pfile as mentioned above.

f) Start the asm instance and then the db.

rhelrac1-> srvctl start asm -n rhelrac1
rhelrac1-> srvctl start asm -n rhelrac2
rhelrac1-> srvctl start database -d testthis

e) Now check, the asm instance will be using the spfile.

SQL> select inst_id, name, type, value from GV$parameter where name ='spfile';

INST_ID NAME TYPE VALUE
---------- ---------- ---------- ----------------------------------------------------------------------
1 spfile 2 /u01/app/oracle/admin/+ASM/pfile/spfile+ASM1.ora
2 spfile 2 /u01/app/oracle/admin/+ASM/pfile/spfile+ASM2.ora

Altering a Diskgorup:-
---------------------

SQL> select inst_id, name, state from GV$asm_diskgroup order by name;

INST_ID NAME STATE
---------- ---------- -----------
2 DATA1 MOUNTED
1 DATA1 MOUNTED
1 DATA2 MOUNTED
2 DATA2 MOUNTED
2 DATA3 MOUNTED
1 DATA3 MOUNTED
2 FLASH MOUNTED
1 FLASH MOUNTED
1 ORADATA DISMOUNTED
2 ORADATA DISMOUNTED
2 ORANORMAL DISMOUNTED
1 ORANORMAL DISMOUNTED
2 NEWDISK DISMOUNTED
1 NEWDISK MOUNTED

We can see that the diskgroups newdisk, oradata and oranormal are not mounted yet. Will
mount it now.

SQL> alter diskgroup newdisk mount;

Diskgroup altered.

SQL> select inst_id, name, state from GV$asm_diskgroup where name = 'NEWDISK';

INST_ID NAME STATE
---------- ---------- -----------
2 NEWDISK DISMOUNTED
1 NEWDISK MOUNTED

Now the newdisk is mounted only on the one node, that is whenever we mount the diskgroup in
only node it will be mounted in RESTRICTED mode, inorder to do some maintainance activities.
The diskgroup should be mounted in all node for normal operation. Connect to the remaining
nodes and mount the diskgroup.

Repeat the above procedure to mount the diskgroups on all the nodes.

Set the asm_diskgroups parameters to mount the diskgroup at every reboot, execute the below in both nodes.

> alter system set asm_diskgroups='DATA1','DATA2','DATA3','FLASH','NEWDISK','ORADATA','ORANORMAL' SCOPE=BOTH;

SQL> select name, type, total_mb, free_mb from V$asm_diskgroup;

NAME TYPE TOTAL_MB FREE_MB
------------------------------ ------ ---------- ----------
DATA1 EXTERN 3858 3760
DATA2 NORMAL 3765 3054
DATA3 EXTERN 3858 3761
FLASH EXTERN 3858 2674
ORANORMAL NORMAL 1928 1742
ORADATA EXTERN 1137 1044
NEWDISK EXTERN 1019 926

Droping a diskgroup:-

Now we will drop a diskgroup and give the disks to some other diskgroup.
Here we will drop the newdisk diskgroup.

SQL> select a.path, a.state, a.header_status, a.group_number, b.name from V$asm_disk a, V$asm_diskgroup b
where a.group_number = b.group_number and b.name = 'NEWDISK';

PATH STATE HEADER_STATU GROUP_NUMBER NAME
-------------------- -------- ------------ ------------ ------------------------------
ORCL:NEWDISK1 NORMAL MEMBER 5 NEWDISK

To drop a diskgroup it should be mounted in only one node, if the diskgroup is mount in all the nodes
and if you try to drop it you will get the below error.

SQL> drop diskgroup newdisk;
drop diskgroup newdisk
*
ERROR at line 1:
ORA-15039: diskgroup not dropped
ORA-15073: diskgroup NEWDISK is mounted by another ASM instance

first dismount in all the others nodes here the 2nd node and then drop the diskgroup in the firstnode.

SQL> alter diskgroup newdisk dismount;

Diskgroup altered.

Then drop it in the first node.

SQL> drop diskgroup newdisk;

Diskgroup dropped.

SQL> select path,header_status from V$asm_disk where path = 'ORCL:NEWDISK1';

PATH HEADER_STATU
---------------------------------------- ------------

ORCL:NEWDISK1 FORMER

Now the header status shows as former, which means it has been removed from the diskgroup.
Do not add a disk which been taken from the other diskgroups until the header status
changed to former.

Now we can add this disk to another diskgroup.

SQL> select name, type, total_mb from V$asm_diskgroup;

NAME TYPE TOTAL_MB
------------------------------ ------ ----------
DATA1 EXTERN 3858
DATA2 NORMAL 3765
DATA3 EXTERN 3858
FLASH EXTERN 3858
ORANORMAL NORMAL 1928
ORADATA EXTERN 1137

We will add the disk to the FLASH diskgroup, cause currently all the datafiles are resting
in this diskgroup.

SQL> alter diskgroup flash add disk 'ORCL:NEWDISK1';

Diskgroup altered.

The control will be transfered immediately when we add / drop any disks, but rebalancing
will get happend in the background, it will always a good pratice to have the LUN's with
the same size in a diskgroup so that there will be equal load on all the disks.

This stripping / rebalancing is transperent to the application users / end users. No
outage are require for adding / droping a disk to a diskgroup.

SQL> select * from V$asm_operation;

GROUP_NUMBER OPERA STAT POWER ACTUAL SOFAR EST_WORK EST_RATE EST_MINUTES
------------ ----- ---- ---------- ---------- ---------- ---------- ---------- -----------
4 REBAL RUN 1 1 21 252 524 0

alternatively we can include the rebalance power in the same state and with wait clause
so that the control will not be transferred until the rebalancing is completed.

> alter diskgroup flash add disk 'ORCL:NEWDISK1' rebalance power 5 wait;

SQL> select name, state, total_mb from V$asm_diskgroup;

NAME STATE TOTAL_MB
------------------------------ ----------- ----------
DATA1 MOUNTED 3858
DATA2 MOUNTED 3765
DATA3 MOUNTED 3858
FLASH MOUNTED 4877
ORANORMAL MOUNTED 1928
ORADATA MOUNTED 1137

6 rows selected.

now we can see that the size of the FLASH diskgroup has been increased. Now we will delete
the disk from the diskgroup.

SQL> SELECT HEADER_STATUS , path,name , total_mb from V$asm_disk where group_number = (select distinct group_number from V$asm_diskgroup
where name ='FLASH');

HEADER_STATU PATH NAME TOTAL_MB
------------ ---------------------------------------- ------------------------------ ----------
MEMBER ORCL:DISK10 DISK10 1286
MEMBER ORCL:DISK11 DISK11 1286
MEMBER ORCL:DISK9 DISK9 1286
MEMBER ORCL:NEWDISK1 NEWDISK1 1019

Currenty there are four disks are attached to this diskgroup, lets remove the last one (NEWDISK1)

SQL> alter diskgroup flash drop disk newdisk1 rebalance power 6 wait;

Diskgroup altered.

You can use the undrop clause to cancel the droping operation of disk if still the drop operation is
not completed. If the operation is alread completed then you can use the undrop to restore the disk.

SQL> alter diskgroup flash undorop disk newdisk1;

SQL> SELECT HEADER_STATUS , path,name , total_mb, free_mb from V$asm_disk where group_number = (select distinct group_number from V$asm_diskgroup
where name ='FLASH' );

HEADER_STATU PATH NAME TOTAL_MB FREE_MB
------------ ---------------------------------------- ------------------------------ ---------- ----------
MEMBER ORCL:DISK10 DISK10 1286 892
MEMBER ORCL:DISK11 DISK11 1286 891
MEMBER ORCL:DISK9 DISK9 1286 891

Rebalancing has been completed and now we can see that the free_mb is almost equal in all the disks.

Resizing a Disk in a diskgroup.

If you do not specify a new size in the SIZE clause then ASM uses the size of the disk
as returned by the operating system. The new size is written to the ASM disk header
and if the size of the disk is increasing, then the new space is immediately available for
allocation. If the size is decreasing, rebalancing must relocate file extents beyond the
new size limit to available space below the limit. If the rebalance operation can
successfully relocate all extents, then the new size is made permanent, otherwise the
rebalance fails.

SQL> SELECT HEADER_STATUS , path,name , total_mb, free_mb from V$asm_disk where group_number = (select distinct group_number from V$asm_diskgroup
2 where name ='DATA3');

HEADER_STATU PATH NAME TOTAL_MB FREE_MB
------------ ---------------------------------------- ------------------------------ ---------- ----------
MEMBER ORCL:DISK5 DISK5 1286 1254
MEMBER ORCL:DISK6 DISK6 1286 1254
MEMBER ORCL:DISK7 DISK7 1286 1253

SQL> alter diskgroup data3
resize disk 'DISK5' size 1000m
disk 'DISK6' size 1000m
disk 'DISK7' size 1000m;

SQL> SELECT HEADER_STATUS , path,name , total_mb, free_mb, failgroup from V$asm_disk where group_number = (select distinct group_number from V$asm_diskgroup
2 where name ='DATA3');

HEADER_STATU PATH NAME TOTAL_MB FREE_MB FAILGROUP
------------ ---------------------------------------- ------------------------------ ---------- ---------- ------------------------------
MEMBER ORCL:DISK5 DISK5 1000 968 DISK5
MEMBER ORCL:DISK6 DISK6 1000 968 DISK6
MEMBER ORCL:DISK7 DISK7 1000 967 DISK7

Now we can see that the size of all the disks has been reduced to 1000m.

Lets look the calculation of free mb of normal redundancy disk group.

SQL> select HEADER_STATUS , path,name , total_mb, free_mb, failgroup from V$asm_disk where group_number = (select distinct group_number from V$asm_diskgroup
where name ='DATA2');

HEADER_STATU PATH NAME TOTAL_MB FREE_MB FAILGROUP
------------ ---------------------------------------- ------------------------------ ---------- ---------- ------------------------------
MEMBER ORCL:DISK12 DISK12 1255 1020 DISK12
MEMBER ORCL:DISK4 DISK4 1255 1016 DISK4
MEMBER ORCL:DISK8 DISK8 1255 1018 DISK8

SQL> SELECT name, type, total_mb, free_mb, required_mirror_free_mb,
usable_file_mb FROM V$ASM_DISKGROUP where name = 'DATA2';

NAME TYPE TOTAL_MB FREE_MB REQUIRED_MIRROR_FREE_MB USABLE_FILE_MB
------------------------------ ------ ---------- ---------- ----------------------- --------------
DATA2 NORMAL 3765 3054 1255 899

REQUIRED_MIRROR_FREE_MB indicates the amount of space that must be
available in a disk group to restore full redundancy after the worst failure that can
be tolerated by the disk group. The amount of space displayed in this column
takes the effects of mirroring into account. The value is computed as follows:
For a normal redundancy disk group, the value is the total raw space for all of the
disks in the largest failure group. The largest failure group is the one with the
largest total raw capacity. For example, if each disk is in its own failure group, then
the value would be the size of the largest capacity disk.
For a high redundancy disk group, the value is the total raw space for all of the
disks in the two largest failure groups.

USABLE_FILE_MB indicates the amount of space that can be used to store the newfiles, here we have 899 mb of
space that can be used for new files.

Free_mb - required mirror free mb = 2 * usable file mb

3054 - 1255 = 1799
2 * 899 = 1799

2 ( is for normal redundancy)

How ASM Manages Disk Failures
=============================

Depending on the redundancy level of a disk group and how you define failure
groups, the failure of one or more disks could result in either of the following:

The disks are first taken offline and then automatically dropped. In this case, the
disk group remains mounted and serviceable. In addition, because of mirroring,
all of the disk group data remains accessible. After the disk drop operation, ASM
performs a rebalance to restore full redundancy for the data on the failed disks.

The entire disk group is automatically dismounted, which means loss of data
accessibility.

Sunday, 26 May 2013

Wednesday, 26 December 2012

Tuesday, 11 December 2012

Monday, 3 December 2012

Saturday, 1 December 2012

About Me