Search This Blog

Saturday, November 30, 2013

Oracle Undo Tablespace Advisor(DBMS_UNDO_ADV).

As per Doc ID 1580225.1
The package DBMS_UNDO_ADV is undocumented, and it is used internally by the Undo Advisor .

dbms_undo_adv package gives advise based on historical information present in memory or Automatic Workload
Repository. The default retention of AWR is 7 days.

Function longest_query : Returns the length of the longest query for a given period .

Method 1

SELECT dbms_undo_adv.longest_query LONGEST_QUERY FROM dual;

 
Method 2 (using Start/End time)

SELECT dbms_undo_adv.longest_query(SYSDATE-1/24, SYSDATE) LONGEST_QUERY FROM dual;

 
Method 3 (using Begin/End AWR snapshot id)

SELECT dbms_undo_adv.longest_query(345, 768) LONGEST_QUERY FROM dual;


Function required_retention: returns the undo_retention value required for running the longest query.

Method 1

select dbms_undo_adv.required_retention from dual;

Method 2 (using Start/End time)

SELECT Ddbms_undo_adv.required_retention(SYSDATE-1/24, SYSDATE) required_retention FROM dual;

Method 3 (using Begin/End AWR snapshot id)

SELECT dbms_undo_adv.longest_query(345, 768) LONGEST_QUERY FROM dual;



Function best_possible_retention: Returns the best possible undo retention which the current 
undo tablespace can support.

Note:If undo tablespace autoextensible then "maxsize" setting will be taken into account to
 calculate best_possible_retention.


Method 1

SELECT dbms_undo_adv.best_possible_retention best_retention FROM dual;

Method 2 (using Start/End time)

SELECT dbms_undo_adv.best_possible_retention(SYSDATE-1/24, SYSDATE) best_retention FROM dual;

Method 3 (using Begin/End AWR snapshot id)

SELECT dbms_undo_adv.best_possible_retention(345, 768) best_retention FROM dual; 


Function required_undo_size: Returne the appropriate undo tablespace size to satisfy undo retention value.

Method 1

SELECT dbms_undo_adv.required_undo_size(900) required_undo_size FROM dual;

Method 2 (using Start/End time)

SELECT dbms_undo_adv.required_undo_size(900,SYSDATE-1/24, SYSDATE) required_undo_size FROM dual;

Method 3 (using Begin/End AWR snapshot id)

SELECT dbms_undo_adv.required_undo_size(900, 345, 768) required_undo_size FROM dual;



Read more >>

Monday, November 25, 2013

Starting And Stopping OEM/OMS 12c Agent and Server.


[oracle@afr bin]$ pwd
/u01/app/oracle/Middleware/agent/agent_inst/bin

[oracle@afr bin]$ ./emctl stop agent
Oracle Enterprise Manager 12c Cloud Control 12.1.0.1.0
Copyright (c) 1996, 2012 Oracle Corporation.  All rights reserved.
Stopping agent ..... stopped.

[oracle@afr bin]$ ./emctl start agent
Oracle Enterprise Manager 12c Cloud Control 12.1.0.1.0
Copyright (c) 1996, 2012 Oracle Corporation.  All rights reserved.
Starting agent ........................................... started.



[oracle@afr bin]$ pwd
/u01/app/oracle/Middleware/oms/bin

[oracle@afr bin]$ ./emctl stop oms -all
Oracle Enterprise Manager Cloud Control 12c Release 12.1.0.1.0
Copyright (c) 1996, 2012 Oracle Corporation.  All rights reserved.
Stopping WebTier...
WebTier Successfully Stopped
Stopping Oracle Management Server...
Oracle Management Server Successfully Stopped
AdminServer Successfully Stopped
Oracle Management Server is Down




Read more >>

Saturday, November 9, 2013

How To Find Views And What Information They Provide In Oracle Database ?

SQL> desc dba_views;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 OWNER                                     NOT NULL VARCHAR2(30)
 VIEW_NAME                                 NOT NULL VARCHAR2(30)
 TEXT_LENGTH                                        NUMBER
 TEXT                                               LONG
 TYPE_TEXT_LENGTH                                   NUMBER
 TYPE_TEXT                                          VARCHAR2(4000)
 OID_TEXT_LENGTH                                    NUMBER
 OID_TEXT                                           VARCHAR2(4000)
 VIEW_TYPE_OWNER                                    VARCHAR2(30)
 VIEW_TYPE                                          VARCHAR2(30)
 SUPERVIEW_NAME                                     VARCHAR2(30)
 EDITIONING_VIEW                                    VARCHAR2(1)
 READ_ONLY                                          VARCHAR2(1)


SQL> select view_name from dba_views where view_name like'%ROLE%';


VIEW_NAME
------------------------------
V$XS_SESSION_ROLE
DBA_APPLICATION_ROLES
USER_APPLICATION_ROLES
EXU9NTAROLE
EXU81APPROLE
DBA_ROLES
DBA_ROLE_PRIVS
USER_ROLE_PRIVS
ROLE_ROLE_PRIVS
ROLE_SYS_PRIVS
ROLE_TAB_PRIVS

VIEW_NAME
------------------------------
EXU10NTAROLE
SESSION_ROLES
XS_SESSION_ROLES
DBA_SCHEDULER_JOB_ROLES
KU$_DEFROLE_LIST_VIEW
KU$_DEFROLE_VIEW
KU$_DUMMY_ROLE_VIEW
KU$_PROXY_ROLE_LIST_VIEW
KU$_ROLE_VIEW
DBA_CONNECT_ROLE_GRANTEES
PROXY_ROLES

VIEW_NAME
------------------------------
PROXY_USERS_AND_ROLES
ROLE_WM_PRIVS
MGMT$ESA_DBA_ROLE_REPORT

25 rows selected.


SQL> desc dict;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 TABLE_NAME                                         VARCHAR2(30)
 COMMENTS                                           VARCHAR2(4000)


SQL> select * from dict where table_name like '%ROLE%';

TABLE_NAME
------------------------------
COMMENTS
--------------------------------------------------------------------------------
DBA_ROLES
All Roles which exist in the database

DBA_ROLE_PRIVS
Roles granted to users and roles

USER_ROLE_PRIVS
Roles granted to current user


TABLE_NAME
------------------------------
COMMENTS
--------------------------------------------------------------------------------
DBA_SCHEDULER_JOB_ROLES
All scheduler jobs in the database by database role

DBA_CONNECT_ROLE_GRANTEES
Information regarding which users are granted CONNECT

ROLE_ROLE_PRIVS
Roles which are granted to roles


TABLE_NAME
------------------------------
COMMENTS
--------------------------------------------------------------------------------
ROLE_SYS_PRIVS
System privileges granted to roles

ROLE_TAB_PRIVS
Table privileges granted to roles

SESSION_ROLES
Roles which the user currently has enabled.


9 rows selected.



Read more >>

Tuesday, October 22, 2013

Create a System Trigger to Enable SQL TRACE for a session..


The following script will enable session trace for the user scott.

[oracle@canada u01]$ cat trace_trigger.sql
set echo on

spool trace_trigger.log

DROP TRIGGER SYS.trace_trigger;

CREATE OR REPLACE TRIGGER sys.trace_trigger
After logon on database
Begin
            if ( user='SCOTT') then
                execute immediate 'alter session set sql_trace=true';
                execute immediate 'alter session set timed_statistics=true';
                execute immediate 'alter session set tracefile_identifier="SCOTT"';
                execute immediate 'alter session set max_dump_file_size=unlimited';
                execute immediate 'alter session set events ''10046 trace name context forever, level 12''';
            End if;
End;
/

spool off;


[oracle@canada u01]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Tue Oct 22 06:04:31 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, OLAP, Data Mining and Real Application Testing options

SQL> @trace_trigger.sql
SQL>
SQL> spool trace_trigger.log
SQL>
SQL> DROP TRIGGER SYS.trace_trigger;
DROP TRIGGER SYS.trace_trigger
*
ERROR at line 1:
ORA-04080: trigger 'trace_trigger' does not exist


SQL>
SQL> CREATE OR REPLACE TRIGGER sys.trace_trigger
  2  After logon on database
  3  Begin
  4              if ( user='SCOTT') then
  5                  execute immediate 'alter session set sql_trace=true';
  6                  execute immediate 'alter session set timed_statistics=true';
  7                  execute immediate 'alter session set tracefile_identifier="SCOTT"';
  8                  execute immediate 'alter session set max_dump_file_size=unlimited';
  9                  execute immediate 'alter session set events ''10046 trace name context forever, level 12''';
 10              End if;
 11  End;
 12  /

Trigger created.

SQL>
SQL> spool off;
SQL>


SQL> select status from dba_triggers where trigger_name like'%TRACE_TRIGGER%';

STATUS
--------
ENABLED

To disable the trigger.

SQL> alter trigger trace_trigger disable;

Trigger altered.

SQL> select status from dba_triggers where trigger_name like'%TRACE_TRIGGER%';

STATUS
--------
DISABLED

To enable the trigger.

SQL> alter trigger trace_trigger enable;

Trigger altered.

SQL> select status from dba_triggers where trigger_name like'%TRACE_TRIGGER%';

STATUS
--------
ENABLED




Read more >>

Sunday, October 6, 2013

Rman Disaster Recovery Walkthrough.

On Source


Make a backup location.

mkdir -p /u01/backup

Maker sure database is in archive log mode.

Configure control file backup parameters in Rman.

RMAN> configure controlfile autobackup on;

new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters are successfully stored

RMAN> configure controlfile autobackup format for device type disk to '/u01/backup/cf%F';

new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/u01/backup/cf%F';
new RMAN configuration parameters are successfully stored


Take backup of database plus archivelog.

[oracle@canada ~]$ rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Sat Oct 5 19:13:05 2013

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

connected to target database: DELL (DBID=3764293269)

RMAN> run
2> {
3> allocate channel c1 device type disk format '/u01/backup/%U';
4> allocate channel c2 device type disk format '/u01/backup/%U';
5> allocate channel c3 device type disk format '/u01/backup/%U';
6> allocate channel c4 device type disk format '/u01/backup/%U';
7> backup database plus archivelog;
8> }

using target database control file instead of recovery catalog
allocated channel: c1
channel c1: SID=51 device type=DISK

allocated channel: c2
channel c2: SID=28 device type=DISK

allocated channel: c3
channel c3: SID=49 device type=DISK

allocated channel: c4
channel c4: SID=34 device type=DISK


Starting backup at 05-OCT-13
current log archived
channel c1: starting archived log backup set
channel c1: specifying archived log(s) in backup set
input archived log thread=1 sequence=3 RECID=1 STAMP=828040174
channel c1: starting piece 1 at 05-OCT-13
channel c2: starting archived log backup set
channel c2: specifying archived log(s) in backup set
input archived log thread=1 sequence=4 RECID=2 STAMP=828040176
input archived log thread=1 sequence=5 RECID=3 STAMP=828040184
channel c2: starting piece 1 at 05-OCT-13
channel c3: starting archived log backup set
channel c3: specifying archived log(s) in backup set
input archived log thread=1 sequence=6 RECID=4 STAMP=828040185
input archived log thread=1 sequence=7 RECID=5 STAMP=828040187
channel c3: starting piece 1 at 05-OCT-13
channel c4: starting archived log backup set
channel c4: specifying archived log(s) in backup set
input archived log thread=1 sequence=8 RECID=6 STAMP=828040188
input archived log thread=1 sequence=9 RECID=7 STAMP=828040446
channel c4: starting piece 1 at 05-OCT-13
channel c1: finished piece 1 at 05-OCT-13
piece handle=/u01/backup/01ollp7v_1_1 tag=TAG20131005T191406 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:00
channel c2: finished piece 1 at 05-OCT-13
piece handle=/u01/backup/02ollp7v_1_1 tag=TAG20131005T191406 comment=NONE
channel c2: backup set complete, elapsed time: 00:00:00
channel c3: finished piece 1 at 05-OCT-13
piece handle=/u01/backup/03ollp7v_1_1 tag=TAG20131005T191406 comment=NONE
channel c3: backup set complete, elapsed time: 00:00:00
channel c4: finished piece 1 at 05-OCT-13
piece handle=/u01/backup/04ollp7v_1_1 tag=TAG20131005T191406 comment=NONE
channel c4: backup set complete, elapsed time: 00:00:01
Finished backup at 05-OCT-13

Starting backup at 05-OCT-13
channel c1: starting full datafile backup set
channel c1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/oradata/dell/system01.dbf
channel c1: starting piece 1 at 05-OCT-13
channel c2: starting full datafile backup set
channel c2: specifying datafile(s) in backup set
input datafile file number=00002 name=/u01/app/oracle/oradata/dell/sysaux01.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/dell/users01.dbf
channel c2: starting piece 1 at 05-OCT-13
channel c3: starting full datafile backup set
channel c3: specifying datafile(s) in backup set
input datafile file number=00005 name=/u01/app/oracle/oradata/dell/example01.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/dell/undotbs01.dbf
channel c3: starting piece 1 at 05-OCT-13
channel c3: finished piece 1 at 05-OCT-13
piece handle=/u01/backup/07ollp81_1_1 tag=TAG20131005T191408 comment=NONE
channel c3: backup set complete, elapsed time: 00:00:37
channel c2: finished piece 1 at 05-OCT-13
piece handle=/u01/backup/06ollp81_1_1 tag=TAG20131005T191408 comment=NONE
channel c2: backup set complete, elapsed time: 00:01:18
channel c1: finished piece 1 at 05-OCT-13
piece handle=/u01/backup/05ollp81_1_1 tag=TAG20131005T191408 comment=NONE
channel c1: backup set complete, elapsed time: 00:01:50
Finished backup at 05-OCT-13

Starting backup at 05-OCT-13
current log archived
channel c1: starting archived log backup set
channel c1: specifying archived log(s) in backup set
input archived log thread=1 sequence=10 RECID=8 STAMP=828040560
channel c1: starting piece 1 at 05-OCT-13
channel c1: finished piece 1 at 05-OCT-13
piece handle=/u01/backup/08ollpbg_1_1 tag=TAG20131005T191600 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:01
Finished backup at 05-OCT-13

Starting Control File and SPFILE Autobackup at 05-OCT-13
piece handle=/u01/backup/cfc-3764293269-20131005-00 comment=NONE
Finished Control File and SPFILE Autobackup at 05-OCT-13
released channel: c1
released channel: c2
released channel: c3
released channel: c4

Take backup of spfile.


RMAN> backup spfile to destination '/u01/backup/';

Starting backup at 05-OCT-13
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=49 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 05-OCT-13
channel ORA_DISK_1: finished piece 1 at 05-OCT-13
piece handle=/u01/backup/DELL/backupset/2013_10_05/o1_mf_nnsnf_TAG20131005T192028_950694xh_.bkp tag=TAG20131005T192028 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 05-OCT-13

Starting Control File and SPFILE Autobackup at 05-OCT-13
piece handle=/u01/backup/cfc-3764293269-20131005-01 comment=NONE
Finished Control File and SPFILE Autobackup at 05-OCT-13

DBID of database to be restored DBID=3764293269


On new host where database is to be restored
make required directories

[oracle@newyork ~]$ mkdir -p /u01/app/oracle/admin/dell/adump
[oracle@newyork ~]$ mkdir -p /u01/app/oracle/oradata/dell/
[oracle@newyork ~]$ mkdir -p /u01/app/oracle/flash_recovery_area/dell/


Copy backup files to the destination server.

On Destination Server

1) Restore the spfile



[oracle@newyork ~]$ export ORACLE_SID=dell
[oracle@newyork ~]$ rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Sat Oct 5 19:34:27 2013

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

connected to target database (not started)

RMAN> set dbid=3764293269

executing command: SET DBID

RMAN> startup force nomount;

startup failed: ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/u01/app/oracle/product/11.2.0/db_1/dbs/initdell.ora'

starting Oracle instance without parameter file for retrieval of spfile
Oracle instance started

Total System Global Area     159019008 bytes

Fixed Size                     1335192 bytes
Variable Size                 75497576 bytes
Database Buffers              79691776 bytes
Redo Buffers                   2494464 bytes


RMAN> restore spfile from '/u01/backup/cfc-3764293269-20131005-00';

Starting restore at 05-OCT-13
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=19 device type=DISK

channel ORA_DISK_1: restoring spfile from AUTOBACKUP /u01/backup/cfc-3764293269-20131005-00
channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete
Finished restore at 05-OCT-13




RMAN> shutdown immediate

Oracle instance shut down



2) Restore controlfile

RMAN> startup nomount

connected to target database (not started)
Oracle instance started

Total System Global Area     272011264 bytes

Fixed Size                     1335952 bytes
Variable Size                201330032 bytes
Database Buffers              67108864 bytes
Redo Buffers                   2236416 bytes





RMAN> run
2> {
3> set controlfile autobackup format for device type disk to '/u01/backup/cf%F';
4> restore controlfile from autobackup;
5> }

executing command: SET CONTROLFILE AUTOBACKUP FORMAT

Starting restore at 05-OCT-13
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=19 device type=DISK

recovery area destination: /u01/app/oracle/flash_recovery_area
database name (or database unique name) used for search: DELL
channel ORA_DISK_1: no AUTOBACKUPS found in the recovery area
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20131005
channel ORA_DISK_1: AUTOBACKUP found: /u01/backup/cfc-3764293269-20131005-01
channel ORA_DISK_1: restoring control file from AUTOBACKUP /u01/backup/cfc-3764293269-20131005-01
channel ORA_DISK_1: control file restore from AUTOBACKUP complete
output file name=/u01/app/oracle/oradata/dell/control01.ctl
output file name=/u01/app/oracle/flash_recovery_area/dell/control02.ctl
Finished restore at 05-OCT-13



3) Restore the database.

RMAN> alter database mount;

database mounted
released channel: ORA_DISK_1


RMAN> catalog start with '   ';  (if backup destination is different from source)



RMAN> restore database;

Starting restore at 05-OCT-13
Starting implicit crosscheck backup at 05-OCT-13
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=19 device type=DISK
Crosschecked 10 objects
Finished implicit crosscheck backup at 05-OCT-13

Starting implicit crosscheck copy at 05-OCT-13
using channel ORA_DISK_1
Finished implicit crosscheck copy at 05-OCT-13

searching for all files in the recovery area
cataloging files...
no files cataloged

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/dell/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00005 to /u01/app/oracle/oradata/dell/example01.dbf
channel ORA_DISK_1: reading from backup piece /u01/backup/07ollp81_1_1
channel ORA_DISK_1: piece handle=/u01/backup/07ollp81_1_1 tag=TAG20131005T191408
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:23
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00002 to /u01/app/oracle/oradata/dell/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/dell/users01.dbf
channel ORA_DISK_1: reading from backup piece /u01/backup/06ollp81_1_1
channel ORA_DISK_1: piece handle=/u01/backup/06ollp81_1_1 tag=TAG20131005T191408
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:37
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /u01/app/oracle/oradata/dell/system01.dbf
channel ORA_DISK_1: reading from backup piece /u01/backup/05ollp81_1_1
channel ORA_DISK_1: piece handle=/u01/backup/05ollp81_1_1 tag=TAG20131005T191408
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:58
Finished restore at 05-OCT-13



4) Recover the database

RMAN> recover database;

Starting recover at 05-OCT-13
using channel ORA_DISK_1

starting media recovery

channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=10
channel ORA_DISK_1: reading from backup piece /u01/backup/08ollpbg_1_1
channel ORA_DISK_1: piece handle=/u01/backup/08ollpbg_1_1 tag=TAG20131005T191600
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
archived log file 

name=/u01/app/oracle/flash_recovery_area/DELL/archivelog/2013_10_05/o1_mf_1_10_9507y3bg_.arc thread=1 

sequence=10
channel default: deleting archived log(s)
archived log file 

name=/u01/app/oracle/flash_recovery_area/DELL/archivelog/2013_10_05/o1_mf_1_10_9507y3bg_.arc RECID=9 

STAMP=828042523
unable to find archived log
archived log thread=1 sequence=11
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 10/05/2013 19:48:45
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 11 and starting SCN 
of 797450


Note: Ignore the error and relogin to thr rman prompt.





5) open with resetlogs

[oracle@newyork ~]$ rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Sat Oct 5 19:52:07 2013

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

connected to target database: DELL (DBID=3764293269, not open)

RMAN> sql 'alter database open resetlogs';

using target database control file instead of recovery catalog
sql statement: alter database open resetlogs

RMAN> exit


Recovery Manager complete.


6) verify



[oracle@newyork ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Sat Oct 5 19:54:11 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, OLAP, Data Mining and Real Application Testing options

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     1
Next log sequence to archive   1
Current log sequence           1


SQL> select name, dbid from v$database;

NAME            DBID
--------- ----------
DELL      3764293269









Read more >>

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

Tuesday, May 28, 2013

11gR2 RAC (ASM) to Single Instance (NON-ASM) Cloning.

Source = 2 node rac cluster with asm  SID=dell
Target = single instance non asm      SID=dup(to be made)

Notes:

1) For cloning Rman active database duplication will be used,
   that means no backups are required(11gR2 feature). 

Other Methods which can be used for duplication using RMAN.

1.1) Duplication without connection to target(11gR2 Feature).
1.2) Backup and recreate database manually.
1.3) Rman duplication using backups.

2) Since source database is OMF managed target will be OMF managed as well.

   following parameters will be used to achieve that.

2.1) db_create_file_dest---------------->> This directory will contain data,redo & control files.
2.2) db_recovery_file_dest-------------->> This will contain Flash recover Area.

3) In case where source is NON-OMF managed use following parameters : -

3.1) control_files
3.2) db_file_name_convert
3.3) log_file_name_convert 


Source

1) Make sure rac database is in archive log mode.
2) Copy password file to target.

[oracle@rac1 dbs]$ scp orapwdell1 192.168.1.69:/u01/app/oracle/product/11.2.0/db_1/dbs/orapwdup
oracle@192.168.1.69's password:
orapwdell1                                        100% 1536     1.5KB/s   00:00

3) make tnsnames.ora entry using "NETCA" as "Oracle" user
   so that a connection could be made to target.


Sample on both nodes:

[oracle@rac1 admin]$ cat tnsnames.ora
# tnsnames.ora.rac1 Network Configuration File: /u01/app/oracle/product/11.2.0/
dbhome_1/network/admin/tnsnames.ora.rac1
# Generated by Oracle configuration tools.

DUP =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.69)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = dup)
    )
  )

DELL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = rac-cluster-scan)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = dell.example.com)
    )
  )




   Copy the modified tnsnames.ora file to target.

[oracle@rac1 admin]$ scp tnsnames.ora 192.168.1.69:/u01/app/oracle/product/11.2.0/db_1/network/admin/
oracle@192.168.1.69's password:
tnsnames.ora                                                100%  533     0.5KB/s   00:00


4) Make pfile out of spfile and modify it for single instance
   cloning then copy it to target.


Original:

[oracle@rac1 oracle]$ cat initdup.ora

dell1.__db_cache_size=29360128
dell2.__db_cache_size=29360128
dell1.__java_pool_size=4194304
dell2.__java_pool_size=4194304
dell1.__large_pool_size=4194304
dell2.__large_pool_size=4194304
dell1.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
dell1.__pga_aggregate_target=109051904
dell2.__pga_aggregate_target=109051904
dell1.__sga_target=155189248
dell2.__sga_target=155189248
dell1.__shared_io_pool_size=0
dell2.__shared_io_pool_size=0
dell1.__shared_pool_size=113246208
dell2.__shared_pool_size=113246208
dell1.__streams_pool_size=0
dell2.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/dell/adump'
*.audit_trail='DB'
*.cluster_database=true
dell1.cluster_database=TRUE
*.compatible='11.2.0.0.0'
*.control_files='+DATA/dell/controlfile/current.261.800523723','+FRA/dell/controlfile/current.268.800523725'
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_domain='example.com'
*.db_name='dell'
*.db_recovery_file_dest='+FRA'
*.db_recovery_file_dest_size=4039114752
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=dellXDB)'
dell2.instance_number=2
dell1.instance_number=1
*.memory_target=262144000
*.open_cursors=300
*.processes=150
*.remote_listener='rac-cluster-scan:1521'
*.remote_login_passwordfile='exclusive'
dell2.thread=2
dell1.thread=1
dell1.undo_tablespace='UNDOTBS1'
dell2.undo_tablespace='UNDOTBS2'


Modified:

dup.__db_cache_size=29360128
dup.__java_pool_size=4194304
dup.__large_pool_size=4194304
dup.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
dup.__pga_aggregate_target=109051904
dup.__sga_target=155189248
dup.__shared_io_pool_size=0
dup.__shared_pool_size=113246208
dup.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/dup/adump'
*.audit_trail='DB'
*.compatible='11.2.0.0.0'
*.db_block_size=8192
*.db_create_file_dest='/u01/app/oracle/oradata'
*.db_domain='example.com'
*.db_name='dup'
*.db_recovery_file_dest='/u01/app/oracle/flash_recovery_area'
*.db_recovery_file_dest_size=4039114752
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=dupXDB)'
*.memory_target=262144000
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='exclusive'
*.undo_tablespace='UNDOTBS1'


Target

1) Make ORACLE_SID entry in the bash profile before doing anything else.

----------------------------------snip------------------------------------
ORACLE_UNQNAME=orcl; export ORACLE_UNQNAME
ORACLE_BASE=/u01/app/oracle; export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1; export ORACLE_HOME
ORACLE_SID=dup; export ORACLE_SID
PATH=/usr/sbin:$PATH; export PATH
PATH=$ORACLE_HOME/bin:$PATH; export PATH
------------------------------------snip------------------------------------

2) Make a listener and register the "dup" database and start it using lsnrctl command.

Sample listener.

[oracle@newyork admin]$ cat listener.ora

# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0
/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = dup)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
      (SID_NAME = dup)
    )
  )

LISTENER =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.69)(PORT = 1521))
  )

ADR_BASE_LISTENER = /u01/app/oracle


3) Make appropriate directory structures.

[oracle@newyork ~]$ mkdir -p /u01/app/oracle/admin/dup/adump
[oracle@newyork ~]$ mkdir -p /u01/app/oracle/oradata
[oracle@newyork ~]$ mkdir -p /u01/app/oracle/flash_recovery_area

4) Make appropriate entries in "/etc/resolv.conf" so a connection can
   be made through SCAN.

search example.com
nameserver 192.168.1.100

5) Do tnsping from both nodes to make sure both systems are reachable.

tnsping dell
tnsping dup

6) create spfile from pfile and start database in nomount stage.

[oracle@newyork ~]$ echo $ORACLE_SID
dup
[oracle@newyork ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Tue May 28 01:24:39 2013

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

Connected to an idle instance.

SQL> create spfile from pfile='/u01/initdup.ora';

File created.

SQL> startup nomount
ORACLE instance started.

Total System Global Area  263639040 bytes
Fixed Size                  1335892 bytes
Variable Size             230690220 bytes
Database Buffers           29360128 bytes
Redo Buffers                2252800 bytes

7) Through RMAN do active database cloning.

[oracle@newyork ~]$ echo $ORACLE_SID
dup
[oracle@newyork ~]$ rman target sys/sys@dell nocatalog auxiliary sys/sys@dup

Recovery Manager: Release 11.2.0.1.0 - Production on Tue May 28 01:28:28 2013

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

connected to target database: DELL (DBID=3771264459)
using target database control file instead of recovery catalog
connected to auxiliary database: DUP (not mounted)

RMAN> duplicate database to dup
2> from active database
3> nofilenamecheck;

Starting Duplicate Db at 28-MAY-13
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=19 device type=DISK

contents of Memory Script:
{
   sql clone "alter system set  control_files = 
  ''/u01/app/oracle/oradata/DUP/controlfile/o1_mf_8t7gsyl1_.ctl'', ''/u01/app/oracle/
flash_recovery_area/DUP/controlfile/o1_mf_8t7gsyn9_.ctl'' comment=
 ''Set by RMAN'' scope=spfile";
   sql clone "alter system set  db_name = 
 ''DELL'' comment=
 ''Modified by RMAN duplicate'' scope=spfile";
   sql clone "alter system set  db_unique_name = 
 ''DUP'' comment=
 ''Modified by RMAN duplicate'' scope=spfile";
   shutdown clone immediate;
   startup clone force nomount
   backup as copy current controlfile auxiliary format  '/u01/app/oracle/oradata/DUP/controlfile/
o1_mf_8t7gsyns_.ctl';
   restore clone controlfile to  '/u01/app/oracle/flash_recovery_area/DUP/controlfile/o1_mf_8t7gsyo3_.ctl' from 
 '/u01/app/oracle/oradata/DUP/controlfile/o1_mf_8t7gsyns_.ctl';
   sql clone "alter system set  control_files = 
  ''/u01/app/oracle/oradata/DUP/controlfile/o1_mf_8t7gsyns_.ctl'',
 ''/u01/app/oracle/flash_recovery_area/DUP/controlfile/o1_mf_8t7gsyo3_.ctl'' comment=
 ''Set by RMAN'' scope=spfile";
   shutdown clone immediate;
   startup clone nomount;
   alter clone database mount;
}
executing Memory Script

sql statement: alter system set  control_files =  
 ''/u01/app/oracle/oradata/DUP/controlfile/o1_mf_8t7gsyl1_.ctl'',
 ''/u01/app/oracle/flash_recovery_area/DUP/controlfile/o1_mf_8t7gsyn9_.ctl''
 comment= ''Set by RMAN'' scope=spfile

sql statement: alter system set  db_name =  ''DELL'' comment= ''Modified by RMAN duplicate'' scope=spfile

sql statement: alter system set  db_unique_name =  ''DUP'' comment= ''Modified by RMAN duplicate'' scope=spfile

Oracle instance shut down

Oracle instance started

Total System Global Area     263639040 bytes

Fixed Size                     1335892 bytes
Variable Size                230690220 bytes
Database Buffers              29360128 bytes
Redo Buffers                   2252800 bytes

Starting backup at 28-MAY-13
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=51 instance=dell2 device type=DISK
channel ORA_DISK_1: starting datafile copy
copying current control file
output file name=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/snapcf_dell2.f tag=TAG20130528T013011 RECID=2 STAMP=816571814
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
Finished backup at 28-MAY-13

Starting restore at 28-MAY-13
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=18 device type=DISK

channel ORA_AUX_DISK_1: copied control file copy
Finished restore at 28-MAY-13

sql statement: alter system set  control_files = 
  ''/u01/app/oracle/oradata/DUP/controlfile/o1_mf_8t7gsyns_.ctl'',
 ''/u01/app/oracle/flash_recovery_area/DUP/controlfile/o1_mf_8t7gsyo3_.ctl''
 comment= ''Set by RMAN'' scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area     263639040 bytes

Fixed Size                     1335892 bytes
Variable Size                230690220 bytes
Database Buffers              29360128 bytes
Redo Buffers                   2252800 bytes

database mounted

contents of Memory Script:
{
   set newname for clone datafile  1 to new;
   set newname for clone datafile  2 to new;
   set newname for clone datafile  3 to new;
   set newname for clone datafile  4 to new;
   set newname for clone datafile  5 to new;
   set newname for clone datafile  6 to new;
   backup as copy reuse
   datafile  1 auxiliary format new
   datafile  2 auxiliary format new
   datafile  3 auxiliary format new
   datafile  4 auxiliary format new
   datafile  5 auxiliary format new
   datafile  6 auxiliary format new
   ;
   sql 'alter system archive log current';
}
executing Memory Script

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting backup at 28-MAY-13
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=+DATA/dell/datafile/system.267.800523427
output file name=/u01/app/oracle/oradata/DUP/datafile/o1_mf_system_0coanpe6_.dbf tag=TAG20130528T013045
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:39
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=+DATA/dell/datafile/sysaux.263.800523439
output file name=/u01/app/oracle/oradata/DUP/datafile/o1_mf_sysaux_0doanphc_.dbf tag=TAG20130528T013045
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:07
channel ORA_DISK_1: starting datafile copy
input datafile file number=00005 name=+DATA/dell/datafile/example.266.800523445
output file name=/u01/app/oracle/oradata/DUP/datafile/o1_mf_example_0eoanpjf_.dbf tag=TAG20130528T013045
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:21
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=+DATA/dell/datafile/undotbs1.265.800523447
output file name=/u01/app/oracle/oradata/DUP/datafile/o1_mf_undotbs1_0foanpk4_.dbf tag=TAG20130528T013045
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:08
channel ORA_DISK_1: starting datafile copy
input datafile file number=00006 name=+DATA/dell/datafile/undotbs2.258.800523875
output file name=/u01/app/oracle/oradata/DUP/datafile/o1_mf_undotbs2_0goanpkc_.dbf tag=TAG20130528T013045
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:10
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=+DATA/dell/datafile/users.262.800523447
output file name=/u01/app/oracle/oradata/DUP/datafile/o1_mf_users_0hoanpkm_.dbf tag=TAG20130528T013045
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:04
Finished backup at 28-MAY-13

sql statement: alter system archive log current

contents of Memory Script:
{
   backup as copy reuse
   archivelog like  "+FRA/dell/archivelog/2013_05_28/thread_2_seq_3.270.816570261" auxiliary format 
 "/u01/app/oracle/flash_recovery_area/DUP/archivelog/2013_05_28/o1_mf_2_3_%u_.arc"   archivelog like 
 "+FRA/dell/archivelog/2013_05_28/thread_1_seq_5.269.816572061" auxiliary format 
 "/u01/app/oracle/flash_recovery_area/DUP/archivelog/2013_05_28/o1_mf_1_5_%u_.arc"   archivelog like 
 "+FRA/dell/archivelog/2013_05_28/thread_2_seq_4.267.816572061" auxiliary format 
 "/u01/app/oracle/flash_recovery_area/DUP/archivelog/2013_05_28/o1_mf_2_4_%u_.arc"   ;
   catalog clone recovery area;
   switch clone datafile all;
}
executing Memory Script

Starting backup at 28-MAY-13
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log copy
input archived log thread=2 sequence=3 RECID=6 STAMP=816570261
output file name=/u01/app/oracle/flash_recovery_area/DUP/archivelog/2013_05_28/o1_mf_2_3_0ioanpl7_.arc RECID=0 STAMP=0
channel ORA_DISK_1: archived log copy complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting archived log copy
input archived log thread=1 sequence=5 RECID=7 STAMP=816572061
output file name=/u01/app/oracle/flash_recovery_area/DUP/archivelog/2013_05_28/o1_mf_1_5_0joanpla_.arc RECID=0 STAMP=0
channel ORA_DISK_1: archived log copy complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting archived log copy
input archived log thread=2 sequence=4 RECID=8 STAMP=816572062
output file name=/u01/app/oracle/flash_recovery_area/DUP/archivelog/2013_05_28/o1_mf_2_4_0koanplb_.arc RECID=0 STAMP=0
channel ORA_DISK_1: archived log copy complete, elapsed time: 00:00:02
Finished backup at 28-MAY-13

searching for all files in the recovery area

List of Files Unknown to the Database
=====================================
File Name: /u01/app/oracle/flash_recovery_area/DUP/controlfile/o1_mf_8t7gsyn9_.ctl
File Name: /u01/app/oracle/flash_recovery_area/DUP/archivelog/2013_05_28/o1_mf_2_3_0ioanpl7_.arc
File Name: /u01/app/oracle/flash_recovery_area/DUP/archivelog/2013_05_28/o1_mf_2_4_0koanplb_.arc
File Name: /u01/app/oracle/flash_recovery_area/DUP/archivelog/2013_05_28/o1_mf_1_5_0joanpla_.arc
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /u01/app/oracle/flash_recovery_area/DUP/archivelog/2013_05_28/o1_mf_2_3_0ioanpl7_.arc
File Name: /u01/app/oracle/flash_recovery_area/DUP/archivelog/2013_05_28/o1_mf_2_4_0koanplb_.arc
File Name: /u01/app/oracle/flash_recovery_area/DUP/archivelog/2013_05_28/o1_mf_1_5_0joanpla_.arc

List of Files Which Where Not Cataloged
=======================================
File Name: /u01/app/oracle/flash_recovery_area/DUP/controlfile/o1_mf_8t7gsyn9_.ctl
  RMAN-07517: Reason: The file header is corrupted

datafile 1 switched to datafile copy
input datafile copy RECID=2 STAMP=816572080 file name=/u01/app/oracle/oradata/DUP/datafile/o1_mf_system_0coanpe6_.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=3 STAMP=816572080 file name=/u01/app/oracle/oradata/DUP/datafile/o1_mf_sysaux_0doanphc_.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=4 STAMP=816572080 file name=/u01/app/oracle/oradata/DUP/datafile/o1_mf_undotbs1_0foanpk4_.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=5 STAMP=816572080 file name=/u01/app/oracle/oradata/DUP/datafile/o1_mf_users_0hoanpkm_.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=6 STAMP=816572080 file name=/u01/app/oracle/oradata/DUP/datafile/o1_mf_example_0eoanpjf_.dbf
datafile 6 switched to datafile copy
input datafile copy RECID=7 STAMP=816572080 file name=/u01/app/oracle/oradata/DUP/datafile/o1_mf_undotbs2_0goanpkc_.dbf

contents of Memory Script:
{
   set until scn  863230;
   recover
   clone database
    delete archivelog
   ;
}
executing Memory Script

executing command: SET until clause

Starting recover at 28-MAY-13
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=18 device type=DISK

starting media recovery

archived log for thread 1 with sequence 5 is already on disk as file /u01/app/oracle/flash_recovery_area/DUP/
archivelog/2013_05_28/o1_mf_1_5_0joanpla_.arc
archived log for thread 2 with sequence 4 is already on disk as file /u01/app/oracle/flash_recovery_area/DUP/
archivelog/2013_05_28/o1_mf_2_4_0koanplb_.arc
archived log file name=/u01/app/oracle/flash_recovery_area/DUP/archivelog/2013_05_28/o1_mf_1_5_0joanpla_.arc
 thread=1 sequence=5
archived log file name=/u01/app/oracle/flash_recovery_area/DUP/archivelog/2013_05_28/o1_mf_2_4_0koanplb_.arc
 thread=2 sequence=4
media recovery complete, elapsed time: 00:00:02
Finished recover at 28-MAY-13

contents of Memory Script:
{
   shutdown clone immediate;
   startup clone nomount;
   sql clone "alter system set  db_name = 
 ''DUP'' comment=
 ''Reset to original value by RMAN'' scope=spfile";
   sql clone "alter system reset  db_unique_name scope=spfile";
   shutdown clone immediate;
   startup clone nomount;
}
executing Memory Script

database dismounted
Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area     263639040 bytes

Fixed Size                     1335892 bytes
Variable Size                230690220 bytes
Database Buffers              29360128 bytes
Redo Buffers                   2252800 bytes

sql statement: alter system set  db_name =  ''DUP'' comment= ''Reset to original value by RMAN'' scope=spfile

sql statement: alter system reset  db_unique_name scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area     263639040 bytes

Fixed Size                     1335892 bytes
Variable Size                230690220 bytes
Database Buffers              29360128 bytes
Redo Buffers                   2252800 bytes
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "DUP" RESETLOGS ARCHIVELOG 
  MAXLOGFILES    192
  MAXLOGMEMBERS      3
  MAXDATAFILES     1024
  MAXINSTANCES    32
  MAXLOGHISTORY      292
 LOGFILE
  GROUP  1  SIZE 50 M ,
  GROUP  2  SIZE 50 M 
 DATAFILE
  '/u01/app/oracle/oradata/DUP/datafile/o1_mf_system_0coanpe6_.dbf'
 CHARACTER SET WE8MSWIN1252

sql statement: ALTER DATABASE ADD LOGFILE 
  
  INSTANCE 'i2' 
  GROUP  3  SIZE 50 M ,
  GROUP  4  SIZE 50 M 

contents of Memory Script:
{
   set newname for clone tempfile  1 to new;
   switch clone tempfile all;
   catalog clone datafilecopy  "/u01/app/oracle/oradata/DUP/datafile/o1_mf_sysaux_0doanphc_.dbf", 
 "/u01/app/oracle/oradata/DUP/datafile/o1_mf_undotbs1_0foanpk4_.dbf", 
 "/u01/app/oracle/oradata/DUP/datafile/o1_mf_users_0hoanpkm_.dbf", 
 "/u01/app/oracle/oradata/DUP/datafile/o1_mf_example_0eoanpjf_.dbf", 
 "/u01/app/oracle/oradata/DUP/datafile/o1_mf_undotbs2_0goanpkc_.dbf";
   switch clone datafile all;
}
executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to /u01/app/oracle/oradata/DUP/datafile/o1_mf_temp_%u_.tmp in control file

cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/DUP/datafile/o1_mf_sysaux_0doanphc_.dbf RECID=1 STAMP=816572129
cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/DUP/datafile/o1_mf_undotbs1_0foanpk4_.dbf RECID=2 STAMP=816572129
cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/DUP/datafile/o1_mf_users_0hoanpkm_.dbf RECID=3 STAMP=816572129
cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/DUP/datafile/o1_mf_example_0eoanpjf_.dbf RECID=4 STAMP=816572129
cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/DUP/datafile/o1_mf_undotbs2_0goanpkc_.dbf RECID=5 STAMP=816572129

datafile 2 switched to datafile copy
input datafile copy RECID=1 STAMP=816572129 file name=/u01/app/oracle/oradata/DUP/datafile/o1_mf_sysaux_0doanphc_.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=816572129 file name=/u01/app/oracle/oradata/DUP/datafile/o1_mf_undotbs1_0foanpk4_.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=816572129 file name=/u01/app/oracle/oradata/DUP/datafile/o1_mf_users_0hoanpkm_.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=4 STAMP=816572129 file name=/u01/app/oracle/oradata/DUP/datafile/o1_mf_example_0eoanpjf_.dbf
datafile 6 switched to datafile copy
input datafile copy RECID=5 STAMP=816572129 file name=/u01/app/oracle/oradata/DUP/datafile/o1_mf_undotbs2_0goanpkc_.dbf

contents of Memory Script:
{
   Alter clone database open resetlogs;
}
executing Memory Script

database opened
Finished Duplicate Db at 28-MAY-13

8) after cloning disable thread 2 and remove respective redo groups.

SQL> select thread#,status,enabled from v$thread;

   THREAD# STATUS ENABLED
---------- ------ --------
         1 OPEN   PUBLIC
         2 CLOSED PUBLIC

SQL> select group# from v$log where thread#=2;

    GROUP#
----------
         3
         4

SQL> alter database disable thread 2;

Database altered.

SQL> alter database drop logfile group 3;

Database altered.

SQL> alter database drop logfile group 4;

Database altered.

SQL> select thread#, status, enabled from v$thread;

   THREAD# STATUS ENABLED
---------- ------ --------
         1 OPEN   PUBLIC


9) drop additional undo tablespaces

SQL> show parameter undo

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      AUTO
undo_retention                       integer     900
undo_tablespace                      string      UNDOTBS1

SQL> select tablespace_name from dba_tablespaces;

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

7 rows selected.


SQL> drop tablespace UNDOTBS2 including contents and datafiles;

Tablespace dropped.



10) check status of tablespaces.

SQL> select tablespace_name,file_name,status from dba_temp_files;

TABLESPACE_NAME
------------------------------
FILE_NAME
--------------------------------------------------------------------------------
STATUS
-------
TEMP
/u01/app/oracle/oradata/DUP/datafile/o1_mf_temp_8t7h4w47_.tmp
ONLINE



SQL> select tablespace_name,file_name,status from dba_data_files;

TABLESPACE_NAME
------------------------------
FILE_NAME
---------------------------------------------------------------------------------------------------
STATUS
---------
SYSTEM
/u01/app/oracle/oradata/DUP/datafile/o1_mf_system_0coanpe6_.dbf
AVAILABLE

SYSAUX
/u01/app/oracle/oradata/DUP/datafile/o1_mf_sysaux_0doanphc_.dbf
AVAILABLE

TABLESPACE_NAME
------------------------------
FILE_NAME
---------------------------------------------------------------------------------------------------
STATUS
---------

UNDOTBS1
/u01/app/oracle/oradata/DUP/datafile/o1_mf_undotbs1_0foanpk4_.dbf
AVAILABLE

USERS
/u01/app/oracle/oradata/DUP/datafile/o1_mf_users_0hoanpkm_.dbf

TABLESPACE_NAME
------------------------------
FILE_NAME
---------------------------------------------------------------------------------------------------
STATUS
---------
AVAILABLE

EXAMPLE
/u01/app/oracle/oradata/DUP/datafile/o1_mf_example_0eoanpjf_.dbf
AVAILABLE



Cloning Finished.


Read more >>

Wednesday, March 27, 2013

General Performance Tuning Guidelines for Oracle RAC.

1) Rule of thumb - It is safe to assume that an application which
scales well in a single instance environment would scale well in
a RAC environment. Opposite of that is also true, If an application
has problems with scalability in a single instance environment,
Then it may not scale well in a RAC environment.

2) Speed up instance recovery. 

i) To speed up the instance recovery time use FAST_START_MTTR_TARGET.

 In a single instance environment instance start-up + crash recovery time
 is controlled by this parameter.
Read more >>

Wednesday, February 6, 2013

Collect Diagnostic Data in 11gR2 RAC Using DiagCollection.pl Script.


All the log files related to clusterware processes can be found in the "$GRID_HOME/logs"
directory. So, if there is some problem with the clusterware you could check
all the log files in the directory.


But mining data from all the log files is difficult. So, in order
to ease of this difficulty in 11gR2 you can use the "diagcollection.pl"
script to collect diagnostic data.

Run the diagcollection.pl script as the root user to collect diagnostic information 
from an Oracle Clusterware installation. The diagnostics provide additional information
so that Oracle Support Services can resolve problems. Run this script from the 
operating system prompt as follows, where CRS_home is the home directory of your Oracle 
Clusterware installation:

# Invoke this utility as the root user.
# CRS_home/bin/diagcollection.pl --collect

[root@rac1 ~]# /u01/app/11.2.0/grid/bin/diagcollection.pl --collect
Production Copyright 2004, 2008, Oracle.  All rights reserved
Cluster Ready Services (CRS) diagnostic collection tool
The following CRS diagnostic archives will be created in the local directory.
crsData_rac1_20130206_0017.tar.gz -> logs,traces and cores from CRS home. 
Note: core files will be packaged only with the --core option.
ocrData_rac1_20130206_0017.tar.gz -> ocrdump, ocrcheck etc
coreData_rac1_20130206_0017.tar.gz -> contents of CRS core files in text format

osData_rac1_20130206_0017.tar.gz -> logs from Operating System
Collecting crs data
/bin/tar: log/rac1/cssd/ocssd.log: file changed as we read it
/bin/tar: log/rac1/ctssd/octssd.log: file changed as we read it
Collecting OCR data
Collecting information from core files
No corefiles found
Collecting OS logs

[root@rac1 ~]# ll
total 14720
-rw------- 1 root root     2946 Aug 23 21:21 anaconda-ks.cfg
-rw-r--r-- 1 root root 14785218 Feb  6 00:17 crsData_rac1_20130206_0017.tar.gz
drwxr-xr-x 4 root root     4096 Feb  3 16:21 Desktop
-rw-r--r-- 1 root root    42129 Aug 23 21:20 install.log
-rw-r--r-- 1 root root     5259 Aug 23 21:20 install.log.syslog
-rw-r--r-- 1 root root    10960 Feb  6 00:18 ocrData_rac1_20130206_0017.tar.gz
-rw-r--r-- 1 root root   171884 Feb  6 00:18 osData_rac1_20130206_0017.tar.gz
-rwxr-x--- 1 root root      696 Jan 26 14:07 root.sh.racnode1.AFTER_INSTALL

To clean the collected data use the "--clean" option.

[root@rac1 ~]# /u01/app/11.2.0/grid/bin/diagcollection.pl --clean
Production Copyright 2004, 2008, Oracle.  All rights reserved
Cluster Ready Services (CRS) diagnostic collection tool
Cleaning up tar and gzip files
Done


[root@rac1 ~]# ll
total 88
-rw------- 1 root root  2946 Aug 23 21:21 anaconda-ks.cfg
drwxr-xr-x 5 root root  4096 Feb  6 00:19 cfgtoollogs
drwxr-xr-x 4 root root  4096 Feb  3 16:21 Desktop
drwxr-xr-x 2 root root  4096 Feb  6 00:19 install
-rw-r--r-- 1 root root 42129 Aug 23 21:20 install.log
-rw-r--r-- 1 root root  5259 Aug 23 21:20 install.log.syslog
drwxr-xr-x 3 root root  4096 Feb  6 00:19 log
-rwxr-x--- 1 root root   696 Jan 26 14:07 root.sh.racnode1.AFTER_INSTALL


Read more >>

Oracle RAC 11gR2 Policy Managed Database Creation using DBCA.

Policy managed databases depend upon server pools in order to work.


Starting from 11gR2, Oracle RAC has three kinds of server pools.

1) Free Server Pool - This pool contains servers which are not assigned to 
   any server pools.
2) Generic Server Pool - This pool contains Pre 11gR2 databases as well as administrator-managed
   databases. 
3) User Created pool - This server pool is created by the user and is used for running 
   policy managed databases.




I have a test three node cluster, Which will be used for creating 
policy managed database.

# Make sure that clusterware services are running on all the nodes
# before starting starting with the installation.

[root@rac1 ~]# su - grid
[grid@rac1 ~]$ crsctl check cluster -all
**************************************************************
rac1:
CRS-4537: Cluster Ready Services is online
CRS-4529: Cluster Synchronization Services is online
CRS-4533: Event Manager is online
**************************************************************
rac2:
CRS-4537: Cluster Ready Services is online
CRS-4529: Cluster Synchronization Services is online
CRS-4533: Event Manager is online
**************************************************************
rac3:
CRS-4537: Cluster Ready Services is online
CRS-4529: Cluster Synchronization Services is online
CRS-4533: Event Manager is online
**************************************************************

Also, make sure that you have appropriate servers in the free pool.

[grid@rac1 ~]$ crsctl status serverpool
NAME=Free
ACTIVE_SERVERS=rac1 rac2 rac3

NAME=Generic
ACTIVE_SERVERS=
 

In my test cluster i did not have servers in the free pool as they were being
used in the generic pool because of the administrator managed database that i had
created earlier.

So, i had to drop that database in order get servers in the free pool. 


Start with the database creation.

[root@rac1 ~]# xhost +
access control disabled, clients can connect from any host
[root@rac1 ~]# su - oracle
[oracle@rac1 ~]$ dbca


In the server pool creation step i have set the cardinality to "2"
this means that out of three nodes my database will run only on 2 nodes
at a time. 
[grid@rac1 ~]$ crsctl status serverpool NAME=Free ACTIVE_SERVERS=rac1 NAME=Generic ACTIVE_SERVERS= NAME=ora.myserverpool ACTIVE_SERVERS=rac2 rac3 [oracle@rac1 ~]$ srvctl status database -d dell Instance dell_1 is running on node rac2 Instance dell_2 is running on node rac3
Read more >>

Monday, January 21, 2013

How to Password Protect Listener in 11g ?


As per Oracle Documentation from Oracle 11g Release 2 (11.2), the password feature is being deprecated.
This does not cause a loss of security because authentication is enforced through local operating
system authentication.

If remote administration of listener is required, then use one of the following methods to
connect to and administer the listener.

1) Connect to the host where listener is running using SSH or other secure method.
2) Use Oracle Enterprise Manager to adminster the listener.


Listener Name: prod

Log in to the listener control utility.

[oracle@canada ~]$ lsnrctl

#Select listener name.

LSNRCTL> set current_listener prod
Current Listener is prod

#Change password(press enter for old password).

LSNRCTL> change_password
Old password: 
New password: 
Reenter new password: 
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=canada.example.com)(PORT=1521)))
Password changed for prod
The command completed successfully

#Save the configuration.

LSNRCTL> save_config
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=canada.example.com)(PORT=1521)))
Saved prod configuration parameters.
Listener Parameter File   /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
Old Parameter File   /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.bak
The command completed successfully


In listener.ora file insert the following line.
LOCAL_OS_AUTHENTICATION_PROD=OFF

#Restart the listener.

[oracle@canada ~]$ lsnrctl

LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 04-JAN-2013 11:43:43

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

Welcome to LSNRCTL, type "help" for information.

LSNRCTL> set current_listener prod
Current Listener is prod

LSNRCTL> stop
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=canada.example.com)(PORT=1521)))
The command completed successfully

LSNRCTL> start
Starting /u01/app/oracle/product/11.2.0/db_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 11.2.0.1.0 - Production
System parameter file is /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/canada/prod/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=canada.example.com)(PORT=1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=canada.example.com)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     prod
Version                   TNSLSNR for Linux: Version 11.2.0.1.0 - Production
Start Date                04-JAN-2013 11:43:59
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Password
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/canada/prod/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=canada.example.com)(PORT=1521)))
Services Summary...
Service "dell" has 1 instance(s).
  Instance "dell", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

#At this stage the password has been successfully implemented.
#Stopping a listener at this stage will result in an TNS error.

LSNRCTL> stop
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=canada.example.com)(PORT=1521)))
TNS-01169: The listener has not recognized the password


#To stop it set the password.

LSNRCTL> set password
Password: 
The command completed successfully

LSNRCTL> stop
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=canada.example.com)(PORT=1521)))
The command completed successfully

LSNRCTL> start
Starting /u01/app/oracle/product/11.2.0/db_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 11.2.0.1.0 - Production
System parameter file is /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/canada/prod/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=canada.example.com)(PORT=1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=canada.example.com)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     prod
Version                   TNSLSNR for Linux: Version 11.2.0.1.0 - Production
Start Date                04-JAN-2013 11:44:51
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Password
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/canada/prod/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=canada.example.com)(PORT=1521)))
Services Summary...
Service "dell" has 1 instance(s).
  Instance "dell", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully



Read more >>

Wednesday, January 16, 2013

How to Configure DNS Server in RHEL 6?

Server IP Address: 192.168.1.121
Hostname: newyork.example.com

[root@newyork named]# vi /etc/sysconfig/network-scripts/ifcfg-eth0
Read more >>