Search This Blog

Sunday, August 18, 2013

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 >>