How add or alter ASM DG in RAC

1. Dismount the diskgroup in one node

17:11:52 SYS@+ASM2 SQL> alter diskgroup tba1 dismount;

Diskgroup altered.

2. Drop it from the other instance where it is mouted

17:09:32 SYS@+ASM1 SQL> drop diskgroup tba1 ;

Diskgroup dropped.

3. Use that disk in any of the existing diskgroup or add a new group.

17:12:02 SYS@+ASM2 SQL> alter diskgroup sys2 add disk '/dev/rdsk/emcpower15a' rebalance power 3;

Diskgroup altered.

How to Size Oracle database/segment

I am trying to put together some numbers and calculation for Oracle's space handling. These old ways of calculating and over years Oracle has done significant changes in space management.
So my #s may not be 100% accurate but may give you an idea


Table Name: EPTORA1.H_STOCK_EXEC


Table size calculations:
DB Block Size = 8192 (8k)

With the above column datatypes each row would take around 27 bytes. Formula is Scale-precision/2+1 and for date type its 7
So A = 27

In each block, Oracle stores header information which takes around (141 bytes + 2x) where x is # of records in that block
B = 141 + 2x

At the table level, PCTFREE is 2%, space for updates.
C = 2% of 8192 = 163

So Space Available for data = 8192 - (A+B+C) = 8192 - (27+141+2x+163) = 7861 -2x

If we have x rows in one block

7861 -2x = 27x => x= 271 records approx

# of rows = 3380055613 as of 28th Jan

Space required by all rows (3380055613/x) blocks = (3380055613/271)*8192/1024/1024/1024 = 95 GB

In reality, Oracle allocated 97 GB as of 28th Jan

12:12:46 SYS@gptprd2 SQL> select last_analyzed, num_rows, round(blocks*8192/1024/1024/1024) size_gb from dba_tables where table_name = 'H_STOCK_EXEC';

LAST_ANALYZED NUM_ROWS SIZE_GB
-------------------- ---------- ----------
28-JAN-2011 22:04:36 3300080148 97

1 row selected.

This wastage can be due to last row not fitting in the remaining space and get moved to the next block.



Now lets calculate for Index.
Same stuff but only Indexed column. While considering the space for index we need to consider the precision. Index is on

COLUMN_NAME COLUMN_LENGTH
------------------------- -------------
H_STOCK_ENTRY_ID 22


A=22

In each block, Oracle stores header information which takes around (165 bytes). For index header is bit more
B = 165

At the table level, PCTFREE is 2%, space for updates.
C = 2% of 8192 = 163

Index has some additional header information like ROWID etc
D = 9

So Space Available for index data= 8192 - (A+B+C+D) = 8192 - (22+165+163+6) = 7836

# of rows = 3380055613 as of 28th Jan

# of blocks required for these many rows = 3380055613*28/7836

Index is B-Tree and branches has a 10% space overhead. so multiplying that

So the Index size in GB is 1.1*(3380055613*28/7836)*8192/1024/1024/1024 = 101 GB


16:12:20 SYS@gptprd2 SQL> select last_analyzed, num_rows, round(leaf_blocks*8192/1024/1024/1024) size_gb from dba_indexes where table_name = 'H_STOCK_EXEC';

LAST_ANALYZED NUM_ROWS SIZE_GB
-------------------- ---------- ----------
28-JAN-2011 22:05:07 3380055613 97

1 row selected.

Basic Dataguard Procedures

Shutdown dataguard standby database
alter database recover managed standby database cancel;
shutdown immediate;

Startup dataguard standby database
startup nomount;
alter database mount standby database;
alter database recover managed standby database disconnect from session ;
Check the listener is up and running and able to connect to database

Validation
1. Check if MRP is running.
--On STBY
SQL> SELECT PROCESS, DELAY_MINS
FROM V$MANAGED_STANDBY
WHERE PROCESS like 'MRP%';

# ps -efgrep mrp
2. Check if dr is in sync
Run the following sqls on both Primary and DR. Both #s should match
select thread#,max(sequence#) from v$archived_log group by thread# order by 1;
SELECT THREAD#, MAX(SEQUENCE#) FROM V$LOG_HISTORY WHERE RESETLOGS_CHANGE#=( SELECT RESETLOGS_CHANGE# FROM V$DATABASE_INCARNATION WHERE STATUS = 'CURRENT')
GROUP BY THREAD# order by 1;
On STBY only
grep "Media Recovery" alert.log tail -10
tail -f alert.log --alert log must be showing logs shipped and applied.
Note: If there is any DELAY set in log_archive_dest parameter in primary archive will not be applied immediately but there will be a msg in alert log for the same

Troubleshooting
1. Check if archives are getting shipped from primary to DR.
Verify which log_archive_dest is set for DR, assuming its log_archive_dest_2. Run the following on primary
alter system set log_archive_dest_state_2=enable;
select dest_id, status from v$archive_dest;
alter system archive log current;
select dest_id, status from v$archive_dest;
select dest_id, status from v$archive_dest;
select dest_id, status from v$archive_dest;
destination status to be VALID
identify the archive destination on DR and check if archives are getting transfered.

2. Check if MRP is running and there is no GAP. On STBY
SELECT PROCESS, DELAY_MINS
FROM V$MANAGED_STANDBY
WHERE PROCESS like 'MRP%';
select * from v$archive_gap;

3. Check the fal_server and fal_client parameters on Primary and DR
show parameter fal

ORA-01119/ORA-01111

Thu Feb 17 22:11:29 EST 2011
File #51 added to control file as 'UNNAMED00051'.
Originally created as:
'+DATA02/infra/datafile/geneos_data.262.743357055'
Recovery was unable to create the file as:
'+ITPDR_DATA01'
Errors with log +ITPDR_ARCH/infra20/archivelog/2011_02_17/thread_1_seq_22690.586.743364737
MRP0: Background Media Recovery terminated with error 1119
Thu Feb 17 22:11:29 EST 2011
Errors in file /u01/app/oracle/admin/infra0/bdump/infra0_mrp0_1282.trc:
ORA-01119: error in creating database file '+ITPDR_DATA01'
ORA-17502: ksfdcre:4 Failed to create file +ITPDR_DATA01
ORA-15041: diskgroup space exhausted
Some recovered datafiles maybe left media fuzzy
Media recovery may continue but open resetlogs may fail
Thu Feb 17 22:11:31 EST 2011
Errors in file /u01/app/oracle/admin/infra0/bdump/infra0_mrp0_1282.trc:
ORA-01119: error in creating database file '+ITPDR_DATA01'
ORA-17502: ksfdcre:4 Failed to create file +ITPDR_DATA01
ORA-15041: diskgroup space exhausted
Thu Feb 17 22:11:31 EST 2011
MRP0: Background Media Recovery process shutdown (infra0)


22:25:18 SYS@infra0 SQL> select * from v$datafile where name like '%UNNAMED%';

FILE# CREATION_CHANGE# CREATION_TIME TS# RFILE# STATUS ENABLED CHECKPOINT_CHANGE# CHECKPOINT_TIME UNRECOVERABLE_CHANGE# UNRECOVERABLE_TIME LAST_CHANGE# LAST_TIME OFFLINE_CHANGE# ONLINE_CHANGE# ONLINE_TIME BYTES BLOCKS
---------- ---------------- -------------------- ---------- ---------- ------ ---------- ------------------ ------------------------------ --------------------- -------------------- ------------ -------------------- --------------- -------------- -------------------- ---------- ----------
CREATE_BYTES BLOCK_SIZE NAME PLUGGED_IN BLOCK1_OFFSET
------------ ---------- ------------------------------ ---------- -------------
AUX_NAME
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
FIRST_NONLOGGED_SCN FIRST_NONLOGGED_TIME
------------------- --------------------
51 1.3522E+11 17-FEB-2011 16:06:41 26 51 RECOVE READ WRITE 1.3522E+11 17-FEB-2011 16:06:41 0 0 0 0 0
8589934592 8192 /u01/app/oracle/product/10.2.0 0 4294967295
.5/db_1/dbs/UNNAMED00051
UNKNOWN
0


1 row selected.

Elapsed: 00:00:01.45

22:34:05 SYS@infra0 SQL> show parameter standby

NAME TYPE VALUE
------------------------------------ ------------ ------------------------------
standby_archive_dest string +ITPDR_ARCH
standby_file_management string AUTO
22:34:10 SYS@infra0 SQL> alter system set standby_file_management=manual;

System altered.

Elapsed: 00:00:00.18

22:35:19 SYS@infra0 SQL> alter database create datafile '/u01/app/oracle/product/10.2.0.5/db_1/dbs/UNNAMED00051' as '+DATA02' size 8192m;

Database altered.

Elapsed: 00:01:45.27
22:37:44 SYS@infra0 SQL> alter system set standby_file_management=auto scope=both ;

System altered.

Elapsed: 00:00:00.08
22:37:50 SYS@infra0 SQL> alter database recover managed standby database disconnect from session;

Database altered.

Elapsed: 00:00:07.12
22:38:19 SYS@infra0 SQL> exit


Check if mrp is started and logs are getting applied
[wbeqdora7:oracle]/u01/app/oracle/admin/infra0/bdump> tail -f alert_infra0.log
SUCCESS: diskgroup DATA02 was mounted
Thu Feb 17 22:38:17 EST 2011
parallel recovery started with 7 processes
Thu Feb 17 22:38:19 EST 2011
Waiting for all non-current ORLs to be archived...
SUCCESS: diskgroup ITPDR_ARCH was mounted
Media Recovery Log +ITPDR_ARCH/infra20/archivelog/2011_02_17/thread_1_seq_22690.586.743364737
Media Recovery Log +ITPDR_ARCH/infra20/archivelog/2011_02_17/thread_2_seq_29220.464.743358599
Thu Feb 17 22:38:19 EST 2011
Completed: alter database recover managed standby database disconnect from session
Thu Feb 17 22:38:40 EST 2011
Media Recovery Log +ITPDR_ARCH/infra20/archivelog/2011_02_17/thread_2_seq_29221.368.743362199
Thu Feb 17 22:38:54 EST 2011
Media Recovery Log +ITPDR_ARCH/infra20/archivelog/2011_02_17/thread_2_seq_29222.348.743364745
Thu Feb 17 22:39:04 EST 2011
Media Recovery Log +ITPDR_ARCH/infra20/archivelog/2011_02_17/thread_2_seq_29223.610.743364765
Media Recovery Log +ITPDR_ARCH/infra20/archivelog/2011_02_17/thread_1_seq_22691.433.743364765
Media Recovery Delayed for 240 minute(s) (thread 1 sequence 22692)