Thinus Meyer's DBA adventures: Administration

I recently had to create a new index on a fairly large date ranged partitioned table. I tried a few permutations on test, but found the following steps to be the fastest;


01:01:03 SQL> alter session set workarea_size_policy=manual

Session altered.

01:13:44 SQL> alter session set hash_area_size=1073741824

Session altered.

01:13:44 SQL> alter session set sort_area_size=1073741824

Session altered.

01:13:44 SQL> select force_logging from v$database

FOR
---
YES

01:13:44 SQL> alter database no force logging

Database altered.

01:13:46 SQL>create index imei_prod.usage_profile_idx1 on imei_prod.usage_profile(msisdn)
01:13:46 2 nologging
01:13:46 3 compress
01:13:46 4 parallel 16
01:13:46 5 local
01:13:46 6 (
01:13:46 7 partition USAGE_199401 tablespace indx nologging compress,
01:13:46 8 partition USAGE_200106 tablespace indx nologging compress,
01:13:46 9 partition USAGE_200202 tablespace indx nologging compress,
01:13:46 10 partition USAGE_200207 tablespace indx nologging compress,
...
01:13:46 56 partition USAGE_200809 tablespace indx nologging compress,
01:13:46 57 partition USAGE_200810 tablespace indx nologging compress,
01:13:46 58 partition USAGE_200811 tablespace indx nologging compress
01:13:46 59 )
01:13:46 60 /

Index created

Elapsed: 01:58:57.99


03:12:44 SQL> SELECT st.sid, se.username, TO_CHAR(se.logon_time,'dd-mon-yy hh24:mi')
03:12:44 2 logtime, se.program, (value/1048576) VALUE
03:12:44 3 FROM gv$sesstat st, gv$statname sn, gv$session se
03:12:44 4 WHERE username = 'MEYERM'
03:12:44 5 and sn.name = 'redo size'
03:12:44 6 AND sn.statistic# = st.statistic#
03:12:44 7 AND st.sid = se.sid
03:12:44 8 /


Redo
Generated
SID USERNAME Logon Time PROGRAM in MB
--------------------------------------------------------------
146 MEYERM 26-nov-08 01:13 sqlplus@pxxxx1 (TNS V1-V3) 116.583

03:12:46 SQL> alter database force logging

Database altered.

03:12:46 SQL> alter index imei_prod.usage_profile_idx1 logging;

Index altered.

We found a lot of sessions waiting for Log File Sync and in turn found that LGWR was waiting for Log File Parallel Write.

We have 2 dedicated disks for Online redo logs. They are multiplexed, with members of the same group on different disks. When a Log Switch occurs 1 member of the Redo Logs being Archived gets READS from the ARCH process while both members of the CURRENT group are receiving WRITE requests from LGWR. We decided to add 2 more disks and split the even numbered groups from the uneven numbered groups. So that we can separate the READS and WRITES.

Current layout:


SQL>select GROUP#, STATUS, TYPE, MEMBER from v$logfile;

G# STATUS TYPE MEMBER
-------------------------
1 ONLINE /dev/vx/rdsk/redodg/casprd_REDO1
1 ONLINE /dev/vx/rdsk/redodg/casprd_REDO1b
2 ONLINE /dev/vx/rdsk/redodg/casprd_REDO2
2 ONLINE /dev/vx/rdsk/redodg/casprd_REDO2b
3 ONLINE /dev/vx/rdsk/redodg/casprd_REDO3
3 ONLINE /dev/vx/rdsk/redodg/casprd_REDO3b
4 ONLINE /dev/vx/rdsk/redodg/casprd_REDO4
4 ONLINE /dev/vx/rdsk/redodg/casprd_REDO4b
5 ONLINE /dev/vx/rdsk/redodg/casprd_REDO5
5 ONLINE /dev/vx/rdsk/redodg/casprd_REDO5b
6 ONLINE /dev/vx/rdsk/redodg/casprd_REDO6
6 ONLINE /dev/vx/rdsk/redodg/casprd_REDO6b

SQL> select GROUP#,SEQUENCE#,BYTES,MEMBERS,ARC,STATUS,FIRST_TIME from v$log;

G# S# BYTES MEMBERS ARC STATUS FIRST_TIME
------------------------------------------
1 45181 2147483648 2 YES INACTIVE 03-SEP-2008 08:00:18
2 45182 2147483648 2 YES INACTIVE 03-SEP-2008 08:45:28
3 45183 2147483648 2 NO CURRENT 03-SEP-2008 09:00:47
4 45178 2147483648 2 YES INACTIVE 03-SEP-2008 06:25:02
5 45179 2147483648 2 YES INACTIVE 03-SEP-2008 07:02:21
6 45180 2147483648 2 YES INACTIVE 03-SEP-2008 07:48:50

We use RAW devices, so after the Unix Admins created the new RAW volumes on the new disks I simply added the members:

Creating Redo Log Members for the even groups on the 2 new disks:


ALTER DATABASE ADD LOGFILE MEMBER '/dev/vx/rdsk/redodg/casprd_REDO2c' TO GROUP 2;
ALTER DATABASE ADD LOGFILE MEMBER '/dev/vx/rdsk/redodg/casprd_REDO2d' TO GROUP 2;
ALTER DATABASE ADD LOGFILE MEMBER '/dev/vx/rdsk/redodg/casprd_REDO4c' TO GROUP 4;
ALTER DATABASE ADD LOGFILE MEMBER '/dev/vx/rdsk/redodg/casprd_REDO4d' TO GROUP 4;
ALTER DATABASE ADD LOGFILE MEMBER '/dev/vx/rdsk/redodg/casprd_REDO6c' TO GROUP 6;
ALTER DATABASE ADD LOGFILE MEMBER '/dev/vx/rdsk/redodg/casprd_REDO6d' TO GROUP 6;

The status of the new log member is shown as INVALID. This is normal and it will change to active (blank) when it is first used.


SQL> select GROUP#,STATUS,TYPE,MEMBER from v$logfile order by group#, member;

G STATUS TYPE MEMBER
-----------------------------------------
1 ONLINE /dev/vx/rdsk/redodg/casprd_REDO1
1 ONLINE /dev/vx/rdsk/redodg/casprd_REDO1b
2 ONLINE /dev/vx/rdsk/redodg/casprd_REDO2
2 ONLINE /dev/vx/rdsk/redodg/casprd_REDO2b
2 INVALID ONLINE /dev/vx/rdsk/redodg/casprd_REDO2c
2 INVALID ONLINE /dev/vx/rdsk/redodg/casprd_REDO2d
3 ONLINE /dev/vx/rdsk/redodg/casprd_REDO3
3 ONLINE /dev/vx/rdsk/redodg/casprd_REDO3b
4 ONLINE /dev/vx/rdsk/redodg/casprd_REDO4
4 ONLINE /dev/vx/rdsk/redodg/casprd_REDO4b
4 INVALID ONLINE /dev/vx/rdsk/redodg/casprd_REDO4c
4 INVALID ONLINE /dev/vx/rdsk/redodg/casprd_REDO4d
5 ONLINE /dev/vx/rdsk/redodg/casprd_REDO5
5 ONLINE /dev/vx/rdsk/redodg/casprd_REDO5b
6 ONLINE /dev/vx/rdsk/redodg/casprd_REDO6
6 ONLINE /dev/vx/rdsk/redodg/casprd_REDO6b
6 INVALID ONLINE /dev/vx/rdsk/redodg/casprd_REDO6c
6 INVALID ONLINE /dev/vx/rdsk/redodg/casprd_REDO6d

SQL> select GROUP#,SEQUENCE#,BYTES,MEMBERS,ARC,STATUS,FIRST_TIME from v$log; 

G# S# BYTES MEMBERS ARC STATUS FIRST_TIME 
------------------------------------------------
1 45181 2147483648 2 YES ACTIVE 03-SEP-2008 09:24:18
2 45182 2147483648 4 YES ACTIVE 03-SEP-2008 09:27:28
3 45183 2147483648 2 NO CURRENT 03-SEP-2008 09:30:47
4 45178 2147483648 4 YES INACTIVE 03-SEP-2008 09:17:02
5 45179 2147483648 2 YES INACTIVE 03-SEP-2008 09:19:21
6 45180 2147483648 4 YES INACTIVE 03-SEP-2008 09:22:50

Before dropping Redo Log Members you should be aware of the following rules:
You can drop a redo log member only if it is not part of an active or current group.
Make sure the group to which a redo log member belongs is archived before dropping the member.
If the member you want to drop is the last valid member of the group, you cannot drop the member until the other members become valid.
So after a few log switches (ALTER SYSTEM SWITCH LOGFILE) I was ready to drop the old members.
Note that V$LOG.STATUS show ACTIVE for group 1 and 2. This means the Log is active but is not the current log. It is needed for crash recovery. It may be in use for block recovery. It may or may not be archived. This was the result of all the log switches I did. Once their STATUS changed to INACTIVE I continued dropping the old member.


ALTER DATABASE DROP LOGFILE MEMBER '/dev/vx/rdsk/redodg/casprd_REDO2';
ALTER DATABASE DROP LOGFILE MEMBER '/dev/vx/rdsk/redodg/casprd_REDO2b';
ALTER DATABASE DROP LOGFILE MEMBER '/dev/vx/rdsk/redodg/casprd_REDO4';
ALTER DATABASE DROP LOGFILE MEMBER '/dev/vx/rdsk/redodg/casprd_REDO4b';
ALTER DATABASE DROP LOGFILE MEMBER '/dev/vx/rdsk/redodg/casprd_REDO6';
ALTER DATABASE DROP LOGFILE MEMBER '/dev/vx/rdsk/redodg/casprd_REDO6b';

Thinus Meyer's DBA adventures

Friday, December 5, 2008

Fast Index Creation

Wednesday, September 3, 2008

Moving Online Redo Log Files

Blog Archive