Experience: is what you get soon after you need it.

Experience: is what you get soon after you need it.

Rasul Allah (sal Allahu alaihi wa sallam) said: "Restore the trusts of those who trust you, and deal not falsely with him who deals falsely with you." [Abu Dawud, Tirmidhi]

Search This Blog

Tuesday, September 8, 2015

Stripe data files across multiple physical devices and locations

Stripe data files across multiple physical devices and locations

Remember -- It is “data files”, not “datafiles:

  • Controlfiles
  • Redologs
  • Tablespaces (create more than one datafile for each)

Controlfiles:
Duplicating/Moving/Adding controlfiles to a new location:

Adding redolog groups:


SHAIKDB>select group#,thread#,sequence#,members,status from v$log;

    GROUP#    THREAD#  SEQUENCE#    MEMBERS STATUS
---------- ---------- ---------- ---------- ----------------
    1        1           7      2 CURRENT
    2        1           5      2 INACTIVE
    3        1           6      2 INACTIVE

SHAIKDB>select * from v$logifle;

    GROUP# STATUS  TYPE    MEMBER                         IS_
---------- ------- ------- -------------------------------------------------- ---
    1       ONLINE  /u01/app/oracle/mydb/redo1.log             NO
    1       ONLINE  /u01/app/oracle/mydb/redo2.log             NO
    2       ONLINE  /u01/app/oracle/mydb/redo3.log             NO
    2       ONLINE  /u01/app/oracle/mydb/redo4.log             NO
    3       ONLINE  /u01/app/oracle/mydb/redo5.log             NO
    3       ONLINE  /u01/app/oracle/mydb/redo6.log             NO

6 rows selected.


SHAIKDB>alter database add logfile group 4('/u02/app/oracle/mydb/redo41.log','/u02/app/oracle/mydb/redo42.log') size 100m;

Database altered.

SHAIKDB>select * from v$logfile;

    GROUP# STATUS  TYPE    MEMBER                         IS_
---------- ------- ------- -------------------------------------------------- ---
    1       ONLINE  /u01/app/oracle/mydb/redo1.log             NO
    1       ONLINE  /u01/app/oracle/mydb/redo2.log             NO
    2       ONLINE  /u01/app/oracle/mydb/redo3.log             NO
    2       ONLINE  /u01/app/oracle/mydb/redo4.log             NO
    3       ONLINE  /u01/app/oracle/mydb/redo5.log             NO
    3       ONLINE  /u01/app/oracle/mydb/redo6.log             NO
    4       ONLINE  /u02/app/oracle/mydb/redo41.log             NO
    4       ONLINE  /u02/app/oracle/mydb/redo42.log             NO

8 rows selected.



Striping tablespace :

SHAIKDB>select ts.name tsname,df.name dfname,df.status from v$datafile df,v$tablespace ts where ts.ts#=df.ts#;

TSNAME                    DFNAME                  STATUS
---------------------------------------- ---------------------------------------- -------
SYSTEM                    /u01/app/oracle/mydb/sys01.dbf       SYSTEM
SYSAUX                    /u01/app/oracle/mydb/sysaux01.db      ONLINE
UNDOTBS1                /u01/app/oracle/mydb/undo1.dbf       ONLINE
USERS                    /u01/app/oracle/mydb/users01.dbf      ONLINE
BIGTBS                    /u01/app/oracle/mydb/MYDB/datafile/bigtb ONLINE
                   s.dbf


SHAIKDB>alter tablespace users add datafile '/u02/app/oracle/mydb/users02.dbf' size 10m;

Tablespace altered.

SHAIKDB>select ts.name tsname,df.name dfname,df.status from v$datafile df,v$tablespace ts where ts.ts#=df.ts#;

TSNAME                    DFNAME                  STATUS
---------------------------------------- ---------------------------------------- -------
SYSTEM                    /u01/app/oracle/mydb/sys01.dbf           SYSTEM
SYSAUX                    /u01/app/oracle/mydb/sysaux01.db          ONLINE
UNDOTBS1                /u01/app/oracle/mydb/undo1.dbf              ONLINE
USERS                    /u01/app/oracle/mydb/users01.dbf          ONLINE
BIGTBS                    /u01/app/oracle/mydb/MYDB/datafile/bigtbs.dbf ONLINE
USERS                    /u02/app/oracle/mydb/users02.dbf         ONLINE

6 rows selected.

SHAIKDB>select ts.name tsname,df.name dfname,df.status from v$datafile df,v$tablespace ts where ts.ts#=df.ts# order by 1;

TSNAME                    DFNAME                  STATUS
---------------------------------------- ---------------------------------------- -------
BIGTBS                    /u01/app/oracle/mydb/MYDB/datafile/bigtbs.dbf ONLINE
SYSAUX                    /u01/app/oracle/mydb/sysaux01.db      ONLINE
SYSTEM                    /u01/app/oracle/mydb/sys01.dbf           SYSTEM
UNDOTBS1                /u01/app/oracle/mydb/undo1.dbf               ONLINE
USERS                    /u02/app/oracle/mydb/users02.dbf      ONLINE
USERS                    /u01/app/oracle/mydb/users01.dbf      ONLINE

6 rows selected.

No comments: