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;
Saturday, November 30, 2013
Oracle Undo Tablespace Advisor(DBMS_UNDO_ADV).
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 agentOracle 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 agentOracle 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 -allOracle 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
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.
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.sqlset 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 -------- ENABLEDTo disable the trigger. SQL> alter trigger trace_trigger disable;Trigger altered.SQL> select status from dba_triggers where trigger_name like'%TRACE_TRIGGER%';STATUS -------- DISABLEDTo 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
Sunday, October 6, 2013
Rman Disaster Recovery Walkthrough.
On Source Make a backup location.mkdir -p /u01/backupMaker 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 storedRMAN> 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 storedTake 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: c4Take 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-13DBID 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=3764293269executing command: SET DBIDRMAN> 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 bytesRMAN> 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-13RMAN> shutdown immediateOracle instance shut down2) Restore controlfile RMAN> startup nomountconnected 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 bytesRMAN> 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-133) Restore the database. RMAN> alter database mount;database mounted released channel: ORA_DISK_1RMAN> 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-134) 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 797450Note: 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 resetlogsRMAN> 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 1SQL> select name, dbid from v$database;NAME DBID --------- ---------- DELL 3764293269
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 2Here 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.
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 TESTSQL> 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
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 sysdbaSQL*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 optionsSQL> show parameter control_files;NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ control_files string +DATA/orcl/controlfile/current .260.823159575, +FRA/orcl/cont rolfile/current.256.823159577SQL> 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:394) 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
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/orapwduporacle@192.168.1.69's password: orapwdell1 100% 1536 1.5KB/s 00:003) 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:004) Make pfile out of spfile and modify it for single instance cloning then copy it to target. Original: [oracle@rac1 oracle]$ cat initdup.oradell1.__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/oracle3) 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.1005) 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_SIDdup[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 nomountORACLE instance started. Total System Global Area 263639040 bytes Fixed Size 1335892 bytes Variable Size 230690220 bytes Database Buffers 29360128 bytes Redo Buffers 2252800 bytes7) Through RMAN do active database cloning. [oracle@newyork ~]$ echo $ORACLE_SIDdup[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-138) 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 PUBLICSQL> select group# from v$log where thread#=2;GROUP# ---------- 3 4SQL> 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 PUBLIC9) drop additional undo tablespaces SQL> show parameter undoNAME TYPE VALUE ------------------------------------ ----------- ------------------------------ undo_management string AUTO undo_retention integer 900 undo_tablespace string UNDOTBS1SQL> 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 ONLINESQL> 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 AVAILABLECloning Finished.
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.
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 --collectProduction 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 ~]# lltotal 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_INSTALLTo clean the collected data use the "--clean" option. [root@rac1 ~]# /u01/app/11.2.0/grid/bin/diagcollection.pl --cleanProduction 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
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 serverpoolNAME=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 ~]$ dbcaIn 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
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 prodCurrent Listener is prod#Change password(press enter for old password). LSNRCTL> change_passwordOld 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_configConnecting 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 successfullyIn 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 prodCurrent Listener is prodLSNRCTL> stopConnecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=canada.example.com)(PORT=1521))) The command completed successfullyLSNRCTL> startStarting /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> stopConnecting 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 passwordPassword: The command completed successfullyLSNRCTL> stopConnecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=canada.example.com)(PORT=1521))) The command completed successfullyLSNRCTL> startStarting /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
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
Subscribe to:
Posts (Atom)