Online Redo Log File Management In 11gR2 RAC/ASM.

SQL> select GROUP#,THREAD#  from v$log;

    GROUP#    THREAD#
---------- ----------
         1          1
         2          1
         3          2
         4          2


Here Thread 1 refers to Node 1 and Thread 2 refers to Node 2.

SQL> alter database add logfile thread 1 group 5 ('+DATA') size 50M;

Database altered.

SQL> alter database add logfile thread 1 group 6 ('+DATA') size 50M;

Database altered.

SQL> alter database add logfile thread 2 group 7 ('+DATA') size 50M;

Database altered.

SQL> alter database add logfile thread 2 group 8 ('+DATA') size 50M;

Database altered.


SQL> select THREAD#,GROUP# from v$log order by THREAD#;

   THREAD#     GROUP#
---------- ----------
         1          1
         1          6
         1          5
         1          2
         2          7
         2          8
         2          3
         2          4

8 rows selected.

To Add logfile member:

SQL> alter database add logfile member '+DATA' to group 1;

Database altered.

SQL> select group#,member from v$logfile order by group#;


    GROUP#
----------
MEMBER
---------------------------------------------------------------------------------------------------
         1
+DATA/orcl/onlinelog/group_1.261.823159585

         1
+FRA/orcl/onlinelog/group_1.257.823159591

         1
+DATA/orcl/onlinelog/group_1.277.823782961

         2
+DATA/orcl/onlinelog/group_2.262.823159597

         2
+FRA/orcl/onlinelog/group_2.258.823159599

         3
+DATA/orcl/onlinelog/group_3.266.823160637

         3
+FRA/orcl/onlinelog/group_3.259.823160641

         4
+FRA/orcl/onlinelog/group_4.260.823160649

         4
+DATA/orcl/onlinelog/group_4.267.823160645

         5
+DATA/orcl/onlinelog/group_5.273.823782521

         6
+DATA/orcl/onlinelog/group_6.274.823782539

         7
+DATA/orcl/onlinelog/group_7.275.823782599

         8
+DATA/orcl/onlinelog/group_8.276.823782615


13 rows selected.

Read more >>

How to create tablespaces in 11gR2 RAC/ASM ?

SQL> create tablespace test datafile
  2  '+DATA'
  3  size 50m autoextend on next 100m
  4  extent management local
  5  segment space management auto;

Tablespace created.



SQL> select tablespace_name from dba_data_files;

TABLESPACE_NAME
------------------------------
USERS
UNDOTBS1
SYSAUX
SYSTEM
EXAMPLE
UNDOTBS2
TEST



SQL> alter tablespace test add datafile
  2  '+DATA'
  3  size 50M autoextend on next 100M;

Tablespace altered.


SQL> select file_name from dba_data_files where tablespace_name='TEST';

FILE_NAME
--------------------------------------------------------------------
+DATA/orcl/datafile/test.271.823780803
+DATA/orcl/datafile/test.272.823781017

Read more >>

Multiplex Control Files In 11gR2 RAC/ASM.

1) Add location to the control_files parameter.

[oracle@rac1 ~]$ export ORACLE_SID=orcl1
[oracle@rac1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Sun Aug 18 11:22:46 2013

Copyright (c) 1982, 2009, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, Real Application Clusters, Automatic Storage Management,                                                    OLAP,
Data Mining and Real Application Testing options

SQL> show parameter control_files;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_files                        string      +DATA/orcl/controlfile/current
                                                 .260.823159575, +FRA/orcl/cont
                                                 rolfile/current.256.823159577




SQL> alter system set control_files='+DATA/orcl/controlfile/current.260.823159575', 
'+FRA/orcl/controlfile/current.256.823159577', '+DATA', '+FRA' scope=spfile;

2) Stop the database and start it in nomount mode.

[oracle@rac1 ~]$ srvctl stop database -d orcl
[oracle@rac1 ~]$ srvctl start database -d orcl -o nomount


3) Create the control file using RMAN by pointing to an actual existing controlfile.

[oracle@rac1 ~]$ rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Sun Aug 18 11:38:56 2013

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORCL (not mounted)


RMAN> restore controlfile from '+DATA/orcl/controlfile/current.260.823159575';

Starting restore at 18-AUG-2013 11:39:31
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1 instance=orcl1 device type=DISK

channel ORA_DISK_1: copied control file copy
output file name=+DATA/orcl/controlfile/current.260.823159575
output file name=+FRA/orcl/controlfile/current.256.823159577
output file name=+DATA/orcl/controlfile/current.270.823779575
Finished restore at 18-AUG-2013 11:39:39

4) Stop the database and start it in normal mode.

[oracle@rac1 ~]$ srvctl stop database -d orcl
[oracle@rac1 ~]$ srvctl start database -d orcl


SQL> select open_mode,name from v$database;

OPEN_MODE            NAME
-------------------- ---------
READ WRITE           ORCL


+DATA/orcl/controlfile/current.260.823159575
+FRA/orcl/controlfile/current.256.823159577
+DATA/orcl/controlfile/current.269.823780197
+FRA/orcl/controlfile/current.271.823780199

Read more >>