ORA-39083: Object type PROCACT_SYSTEM failed to create with error

Issue:
While Import full database, few sql profiles were not imported

Error in Import File:
Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/POST_SYSTEM_ACTIONS/PROCACT_SYSTEM
ORA-39083: Object type PROCACT_SYSTEM failed to create with error:
ORA-21560: argument 2 is null, invalid, or out of range
Failing sql is:
BEGIN
DECLARE
  sp_name VARCHAR2(30);
  sp_desc VARCHAR2(500);
  sp_category VARCHAR2(30);
  sp_force_match VARCHAR2(3);
  sp_sqltext CLOB;
  sp_hints CLOB;
  profile_already_exists exception;
  pragma EXCEPTION_INIT(profile_already_exists, -13829);
BEGIN
  sp_name := 'SYS_SQLPROF_014232aec76c0029';
  sp_desc := '';
  sp_category := 'DEFAULT';
  sp_force_match := 'NO';
     Completed 5 PROCACT_SYSTEM objects in 69 seconds


Cause:
Import of sql profile SYS_SQLPROF_014232aec76c0029 is causing the problem.


Solution:
Currently there is No Permanent Solution of this issue.Bug 18137408 is under development team.


Workaround:
Note# 457531.1 - How to Move SQL Profiles from One Database to Another (Including to Higher Versions) 

Script to find duplicate datafiles in Oracle

Query to Check duplicate datafiles in Oracle

select substr ( file_name, instr( file_name, '/', -1)) file_name, count(*)
from dba_data_files
group by substr ( file_name,instr( file_name, '/', -1))
having count(*) > 1
/

Apply PSU on Oracle 11g Database

Steps to Apply PSU 5 on Oracle 11.2.0.3 Database

Oracle recommends to apply Latest available PSU-1

1) Download and install p6880880_112000_Linux-x86-64(Pre-Req patch)

a) Backup the $ORACLE_HOME/OPatch directory
b) Remove the contents of OPatch directory
c) cp the patch in p6880880_112000_Linux-x86-64.zip at $ORACLE_HOME and unzip the patch(unzip -o patchno)
d) Check Prereq pf patch
Go to patch 14727310 location
Run -> opatch prereq CheckConflictAgainstOHWithDetail -ph ./


e) Rollback patch 13004894
opatch rollback -id 13004894

f) Apply the patch 14727310
opatch apply

g) Apply patch 13004894
opatch apply

ORA-00997: illegal use of LONG datatype

While moving one table to another tablespace I encountered ORA-00997 error.After searching on Metalink I got the below solution:

Error:
alter table VIVEK.IMAGES move tablespace APPS_TS_TX_DATA;

Solution:
CREATE OR REPLACE DIRECTORY dmpdir AS '/d01/vivek/migration/exp';

GRANT READ, WRITE ON DIRECTORY dmpdir TO vivek;

expdp system/manager directory=dmpdir dumpfile=image_tab.dmp logfile=image_tab.log TABLES=VIVEK.IMAGES

DROP TABLE VIVEK.IMAGES CASCADE CONSTRAINTS;

impdp  system/manager directory=dmpdir dumpfile=image_tab.dmp logfile=IMP_image_tab.log REMAP_SCHEMA=VIVEK:VIVEK REMAP_TABLESPACE=SYSTEM:APPS_TS_TX_DATA


Moving Table to Another Tablespace Fails with ORA-00997 [ID 165901.1]

ORA-00604 ORA-01555 ORA-06512

Error While Converting Dictionary Managed Tablespace to Locally Managed Tablespace

SQL> EXECUTE DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL('TABLESPACE_NAME');

BEGIN DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL('TABLESPACE_NAME'); END;
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01555: snapshot too old: rollback segment number 0 with name "SYSTEM" too
small
ORA-06512: at "SYS.DBMS_SPACE_ADMIN", line 227
ORA-06512: at line 1

Solution:
ORA-604 & ORA-1555 Rollback Segment 0 With Name "System" Too small (Doc ID 862469.1)
The SYSTEM rollback segment has no undo retention policy, and is usually not configured to be very large,it is possible to encounter a situation where we could run out of space and hence raise an ORA-1555 error.

set echo on
set feedback on
alter system set "_smu_debug_mode"=4;
drop table helper_for_1555;
create table helper_for_1555 (col1 varchar2(4000));

insert into helper_for_1555 values (rpad('a', 3999));
declare
a number;
begin
for a in 1..18 loop
insert /*+ APPEND +*/ into helper_for_1555 select * from helper_for_1555;
commit;
end loop;
end;
/

rem alter SYSTEM rbseg's storage params
alter rollback segment system storage (next 256M);
alter rollback segment system storage (optimal 1024M);
select segment_name, blocks, bytes, extents from  dba_segments where segment_type='ROLLBACK';

rem bloat the segment
alter system set "_in_memory_undo"=false;
set transaction use rollback segment SYSTEM;
delete from helper_for_1555;
rollback;

select segment_name, blocks, bytes, extents from dba_segments where segment_type='ROLLBACK';

alter system set "_smu_debug_mode"=0;
alter system set "_in_memory_undo"=true;

Now again try to convert the tablespace
SQL> EXECUTE DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL('TABLESPACE_NAME');

How To Change A Dictionary Managed Tablespace To A Locally Managed Tablespace

High Level Steps for Migration of  Dictionary managed tablespaces to locally managed tablespaces:

1) Migrate all other tablespaces to Local first
EXECUTE DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL('TABLESPACE_NAME1');
EXECUTE DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL('TABLESPACE_NAME2');

2) Check system is not the default temporary tablespace

3) If TEMP is dictionary managed drop it and recreate TEMP tablespace

3) Put database in restricted mode

4) Put SYSAUX tablespace in offline mode

5) Put all other tablespaces in read only mode (except SYSTEM, TEMP and UNDO)

6) Migrate system to local
    EXECUTE DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL('SYSTEM');

7) Disable restricted session

8) Revert SYSAUX to online

9) Revert all tablespaces to read write

How To Change A Dictionary Managed Tablespace To A Locally Managed Tablespace? [ID 735978.1]

Steps to perform switchover in physical standby

 Steps to switchover the Standby database to Primary and Primary database to Standby in Oracle10g

1.    Shutdown the primary database.

SQL> shutdown immediate

2.    Shutdown the standby database.

SQL> shutdown immediate

3.    Startup standby database

SQL> startup nomount

SQL> alter database mount standby database;

4.    Startup primary database

SQL> startup

5.    SQL> alter system archive log current;                     (On primary)

       6.  Start the managed recovery operation:          (On standby)

SQL> recover managed standby database disconnect from session;

7.    Check the MRP process                                            (On standby)

SQL> select process, status from v$managed_standby;

8.    If MRP process exist than stop it                                (On standby)

SQL>alter database recover managed standby database cancel;

9.    Stop and start the listener                               (On both)

$ lsnrctl stop

$ lsnrctl start

10.  Shutdown the primary database.

SQL> shutdown immediate

11.  Shutdown the standby database.

SQL> shutdown immediate

12.  Startup standby database

SQL> startup nomount

SQL>alter database mount standby database;

13.  Startup primary database

SQL> startup

14.  Check switchover status                                (On primary)

SQL>select switchover_status from v$database;

            It should be to_standby.

15.  Now run commands                                       (On primary)

SQL>alter database commit to switchover to standby with session    shutdown;

SQL> alter database commit to switchover to physical standby;

SQL> shutdown immediate

SQL>startup nomount

SQL>alter database mount standby database;

SQL> alter system set log_archive_dest_state_2=defer;

SQL> recover managed standby database disconnect from session;

Now primary have converted in standby.

16.  Check switchover status                                (On old standby)

SQL>select switchover_status from v$database;

It should be switchover_pending.

17.  Run the command                                          (On old standby)

SQL> alter database commit to switchover to primary;

18.  Shutdown the database                                              (On old standby)

SQL> shutdown immediate

SQL> startup

SQL> alter database force logging;

SQL> alter system set log_archive_dest_state_2=enable;

Now old standby have converted in Primary database.

 NOTE:-   If you want to Check  that data of primary is applying on standby or not than you can check by these steps:-

1. Make a test table

            SQL> create table test(id number(20));         (On new primary)

2. Switch the log to send the redo data to the standby database:

SQL> alter  system switch logfile;

3. Start managed recovery, if necessary, and applies the archived redo logs by entering the following SQL statement:

SQL> alter database recover managed standby database;   (On new standby)

4. Cancel managed recovery operations.

                        SQL> alter database recover managed standby database cancel;

            This command will stop the MRP process. Now open the database in read only mode.

                        SQL> alter database open read only

5. After that if you want standby database in archive recover mode than firstly you will have to shutdown & startup the standby database.

                        SQL> shutdown immediate

                        SQL> startup nomount

                        SQL> alter database mount standby database;

6. Start the managed recovery operation on standby database:

SQL> recover managed standby database disconnect from session;

What Happens During a Hot Backup

What happens when we put Oracle database in hot backup mode

The BEGIN BACKUP command checkpoints the datafiles and advances the datafile header checkpoints to the begin backup checkpoint SCN. Until the END BACKUP command is executed, checkpoints will cease to update the datafile headers. It starts the logging of block images.Each time the block is read into cache an image of the whole block before the first change is logged.
 

The END BACKUP command creates a redo record containing the begin backup  checkpoint SCN. It stops the logging of block images and causes the datafile checkpoints to be advanced to the database checkpoint.  

Datafile updates by DBWR go on as usual during the time the backup is being copied so the backup gets a "fuzzy" copy of the datafile.Some blocks may be ahead in time versus other blocks and some may contain updates by transactions that are later rolled back. The "fuzzy" backup copy is unusable without the "focusing" via the redo log that occurs when the backup is restored and undergoes media recovery. Media recovery applies redo (from all threads) from the checkpoint SCN that was captured at BEGIN BACKUP time through the end-point of the recovery operation. 
 

Once redo application is finished, uncommited transactions are rolled back to leave a transaction-consistent "focussed" version of the datafile.

Create SQL Profile

Sometimes we faced SQL plan changed issue.Using sqlt we can confirm whether plan of any sql has changed or not.

If plan is changed and we have best plan then we can set sql profile.

How to create SQL profile
Download the sqlt from metalink and Install it.
Once you generate the sqlt of any sqlid and confirmed the plan has changed then go to sqlt directory.

cd sqlt/utl
conn / as sysdba

SQL> START coe_xfr_sql_profile.sql 1jfdhkb18cg4c 3818097359;

1jfdhkb18cg4c --> SQL ID
3818097359--> BEST SQL hash value from sqlt report

This will generate a script to create the sql profile. Use it to create the profile.

How to backup and restore statistics using dbms_stats

1)Check last analyzed date of scott schema tables
ORCL>>select table_name,to_char(last_analyzed,'DD-MON-YYYY HH:MI:SS') FROM DBA_TABLES WHERE OWNER='SCOTT';
TABLE_NAME                     TO_CHAR(LAST_ANALYZE
------------------------------ --------------------
SALGRADE                       09-FEB-2009 10:00:04
BONUS                          09-FEB-2009 10:00:04
EMP                            09-FEB-2009 10:00:04
DEPT                           09-FEB-2009 10:00:04


2)Create stat table in users tablespace
ORCL>>exec dbms_stats.create_stat_table(ownname => 'SCOTT', stattab => 'stats_bkp_scott', tblspace => 'USERS');
PL/SQL procedure successfully completed.


3)Take the statistics backup of scott schema in stat table
ORCL>>exec dbms_stats.export_schema_stats(ownname => 'SCOTT', stattab => 'stats_bkp_scott');
PL/SQL procedure successfully completed.


4)Take the export backup of scott schema or the owner of stats table
$ exp scott/scott1 file=scott_stat_bkp_09122010.dmp tables=scott.STATS_BKP_SCOTT
Export: Release 10.2.0.4.0 - Production on Thu Dec 9 14:37:42 2010
Copyright (c) 1982, 2007, Oracle.  All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses WE8ISO8859P1 character set (possible charset conversion)
About to export specified tables via Conventional Path ...
. . exporting table                STATS_BKP_SCOTT         24 rows exported
Export terminated successfully without warnings.


5)Gather the statistics
14:41:39 ORCL>>exec dbms_stats.gather_table_stats(ownname=>'SCOTT', tabname=>'DEPT', cascade=>true, method_opt => 'for all indexed columns',granularity =>'all',estimate_percent=> 30,degree=>12);
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.14

14:41:51 ORCL>>exec dbms_stats.gather_table_stats(ownname=>'SCOTT', tabname=>'EMP', cascade=>true, method_opt => 'for all indexed columns',granularity =>'all',estimate_percent=> 30,degree=>12);
PL/SQL procedure successfully completed.

14:42:58 ORCL>>exec dbms_stats.gather_table_stats(ownname=>'SCOTT', tabname=>'BONUS', cascade=>true, method_opt => 'for all indexed columns',granularity =>'all',estimate_percent=> 30,degree=>12);
PL/SQL procedure successfully completed.

14:43:19 ORCL>>exec dbms_stats.gather_table_stats(ownname=>'SCOTT', tabname=>'SALGRADE', cascade=>true, method_opt => 'for all indexed columns',granularity =>'all',estimate_percent=> 30,degree=>12);
PL/SQL procedure successfully completed.


6)Check the last analyzed date of tables
14:43:41 ORCL>> select table_name,to_char(last_analyzed,'DD-MON-YYYY HH:MI:SS') FROM DBA_TABLES WHERE OWNER='SCOTT';
TABLE_NAME                     TO_CHAR(LAST_ANALYZE
------------------------------ --------------------
STATS_BKP_SCOTT
SALGRADE                       09-DEC-2010 02:43:41
BONUS                          09-DEC-2010 02:42:59
EMP                            09-DEC-2010 02:42:27
DEPT                           09-DEC-2010 02:41:50


7)Import/Revert the statistics of one/two table from the backup
15:07:22 ORCL>>exec dbms_stats.import_table_stats(ownname=>'scott', tabname=>'emp', statown=>'scott', stattab=>'stats_bkp_scott', cascade=>true);
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.30

15:10:28 ORCL>>exec dbms_stats.import_table_stats(ownname=>'scott', tabname=>'dept', statown=>'scott', stattab=>'stats_bkp_scott', cascade=>true);
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.26


8)Check the last analyzed date of the tables
15:120:31 ORCL>>select table_name,to_char(last_analyzed,'DD-MON-YYYY HH:MI:SS') FROM DBA_TABLES WHERE OWNER='SCOTT';
TABLE_NAME                     TO_CHAR(LAST_ANALYZE
------------------------------ --------------------
STATS_BKP_SCOTT
SALGRADE                       09-DEC-2010 02:43:41
BONUS                          09-DEC-2010 02:42:59
EMP                            09-FEB-2009 10:00:04
DEPT                           09-FEB-2009 10:00:04
Elapsed: 00:00:00.54


9)Revert the statistics of whole schema from the backup
15:40:38 ORCL>>EXECUTE DBMS_STATS.IMPORT_SCHEMA_STATS ('SCOTT','stats_bkp_scott');
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.34


10)Check the last analyzed date of the tables
15:45:53 ORCL>>select table_name,to_char(last_analyzed,'DD-MON-YYYY HH:MI:SS') FROM DBA_TABLES WHERE OWNER='SCOTT';
TABLE_NAME                     TO_CHAR(LAST_ANALYZE
------------------------------ --------------------
STATS_BKP_SCOTT
SALGRADE                       09-FEB-2009 10:00:04
BONUS                          09-FEB-2009 10:00:04
EMP                            09-FEB-2009 10:00:04
DEPT                           09-FEB-2009 10:00:04
Elapsed: 00:00:00.27

Oracle Log Mining Steps on RAC

*The whole activity must be done on one session.

*Check the number of archive logs for which you want to do log mining.

Check the sequence of archive logs from the both for which you want to mining

Suppose,
10.10.10.1 - node1
Mon Dec 13 12:26:16 2010
Thread 1 advanced to log sequence 15289 (LGWR switch)

Mon Dec 13 16:02:50 2010
Thread 1 advanced to log sequence 15298 (LGWR switch)


10.10.10.2 - node2
Mon Dec 13 12:26:16 2010
Thread 2 advanced to log sequence 15062 (LGWR switch)

Mon Dec 13 16:22:04 2010
Thread 2 advanced to log sequence 15072 (LGWR switch)


Steps:
1)set utl_file_dir
   alter system set utl_file_dir='/oracle/test' scope=spfile  (The path/location where you put the archive logs)
   Bounce the database

2)Extracting the LogMiner Dictionary to a Flat file (flat file is one of the option)
  EXECUTE DBMS_LOGMNR_D.BUILD('dictionary.ora','/oracle/test',DBMS_LOGMNR_D.STORE_IN_FLAT_FILE);

3)Add Logfiles
EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => '/oracle/test/1_15289_66678900.dbf',OPTIONS => DBMS_LOGMNR.new);
EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => '/oracle/test/1_15290_66678900.dbf',OPTIONS => DBMS_LOGMNR.ADDFILE);
EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => '/oracle/test/1_15291_66678900.dbf',OPTIONS => DBMS_LOGMNR.ADDFILE);
EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => '/oracle/test/1_15292_66678900.dbf',OPTIONS => DBMS_LOGMNR.ADDFILE);

4)Filtering Data that is returned (Showing only Committed transactions)
 EXECUTE DBMS_LOGMNR.START_LOGMNR(OPTIONS => DBMS_LOGMNR.COMMITTED_DATA_ONLY);

5)Extract relevant information from the view v$logmnr_contents
  Example:
  select seg_owner,operation,sql_redo,sql_undo from v$logmnr_contents where SEG_owner='SCOTT';

6)To end the Log mining process
  exec dbms_logmnr.end_logmnr;


Note: Check the below doc for proper information
Using LogMiner, How to determine the cause of lots of redo generation. [ID 300395.1]
LogMiner Utility Release 8.1.x - 10g [ID 291686.1]

SMON_SCN_TO_TIME_AUX - CLUSTER

How to Reorg the object SMON_SCN_TO_TIME_AUX . The object type is CLUSTER.

Please test the steps on test environment first.

Take Backup
export ORACLE_SID=dev
export ORACLE_HOME=/ora_DEV/app/oracle/product/11.2.0/prodclone/dbhome_1
rman target /
run {
allocate channel ch1 device type disk;
allocate channel ch2 device type disk;
crosscheck archivelog all;
crosscheck backup of database;
delete expired backup of database ;
backup full tag full_db_bkp_dev format '/ora_DEV/PROD_STAGE/RMAN_DBF_%d_%T_%s_%p_%u.bkp' (database);
sql 'alter system archive log current';
backup format '/ora_DEV/PROD_STAGE/RMAN_ARCH_%d_%T_%s_%p_%u.bkp' archivelog all;
backup format '/ora_DEV/PROD_STAGE/RMAN_CTL_%d_%T_%s_%p_%u.bkp' current controlfile;
release channel ch1;
release channel ch2;
}

shutdown immediate

startup restrict

# Enable event 12500. Setting the 12500 event at system level should stop SMON from updating the SMON_SCN_TIME table.

alter system set events '12500 trace name context forever, level 10';

# Truncate the cluster SMON_SCN_TO_TIME

truncate cluster SMON_SCN_TO_TIME_AUX;

Rebuild the indexes
alter index SMON_SCN_TO_TIME_IDX rebuild;
alter index SMON_SCN_TO_TIME_AUX_IDX rebuild;
alter index SMON_SCN_TIME_TIM_IDX rebuild;
alter index SMON_SCN_TIME_SCN_IDX rebuild;

# Analyze the table to confirm it is clean

analyze table SMON_SCN_TIME validate structure cascade;

# Disable the event 12500 so SMON can resume updating the SMON_SCN_TIME table.

alter system set events '12500 trace name context off';

# Disable restricted session:

alter system disable restricted session;

shutdown immediate

startup

Steps to Add Redo Logs on RAC database

Steps on Instance 1(10.10.10.1) ORCL1 instance

select GROUP#,THREAD#,SEQUENCE#,BYTES/1024/1024,MEMBERS,ARCHIVED,STATUS from v$log where THREAD#=1;

   

GROUP#    THREAD#  SEQUENCE# BYTES/1024/1024    MEMBERS ARC STATUS

---------- ---------- ---------- --------------- ---------- --- ----------------

        11          1       60656             300           3 YES INACTIVE

        12          1       60657             300           3 NO  CURRENT

        13          1       60655             300           3 YES INACTIVE

        14          1       60651             300           3 YES INACTIVE

        15          1       60652             300           3 YES INACTIVE

        16          1       60653             300           3 YES INACTIVE

        17          1       60654             300           3 YES INACTIVE

7 rows selected.

select GROUP#,MEMBER from v$logfile where GROUP# in (11,12,13,14,15,16,17) order by 1;

 GROUP# MEMBER

---------- ----------------------------------------

        11 /data/redo01/ORCL/onlinelog/redo11a.dbf

        11 /data/redo02/ORCL/onlinelog/redo11b.dbf

        11 /data/redo03/ORCL/onlinelog/redo11c.dbf

        12 /data/redo01/ORCL/onlinelog/redo12a.dbf

        12 /data/redo02/ORCL/onlinelog/redo12b.dbf

        12 /data/redo03/ORCL/onlinelog/redo12c.dbf

        13 /data/redo02/ORCL/onlinelog/redo13b.dbf

        13 /data/redo03/ORCL/onlinelog/redo13c.dbf

        13 /data/redo01/ORCL/onlinelog/redo13a.dbf

        14 /data/redo03/ORCL/onlinelog/redo14c.dbf

        14 /data/redo01/ORCL/onlinelog/redo14a.dbf

        14 /data/redo02/ORCL/onlinelog/redo14b.dbf

        15 /data/redo03/ORCL/onlinelog/redo15c.dbf

        15 /data/redo02/ORCL/onlinelog/redo15b.dbf

        15 /data/redo01/ORCL/onlinelog/redo15a.dbf

        16 /data/redo02/ORCL/onlinelog/redo16b.dbf

        16 /data/redo03/ORCL/onlinelog/redo16c.dbf

        16 /data/redo01/ORCL/onlinelog/redo16a.dbf

        17 /data/redo01/ORCL/onlinelog/redo17a.dbf

        17 /data/redo02/ORCL/onlinelog/redo17b.dbf

        17 /data/redo03/ORCL/onlinelog/redo17c.dbf

21 rows selected.

(1)   ALTER DATABASE ADD LOGFILE THREAD 1

       GROUP 1 ('/data/redo01/ORCL/onlinelog/redo01a.dbf','/data/redo02/ORCL/onlinelog/redo01b.dbf','/data/redo03/ORCL/onlinelog/redo01c.dbf') SIZE 1024M,

       GROUP 2 ('/data/redo01/ORCL/onlinelog/redo02a.dbf','/data/redo02/ORCL/onlinelog/redo02b.dbf','/data/redo03/ORCL/onlinelog/redo02c.dbf') SIZE 1024M,

       GROUP 3 ('/data/redo01/ORCL/onlinelog/redo03a.dbf','/data/redo02/ORCL/onlinelog/redo03b.dbf','/data/redo03/ORCL/onlinelog/redo03c.dbf') SIZE 1024M);

(2)  SELECT GROUP#, ARCHIVED, STATUS FROM V$LOG where GROUP#=11;

        GROUP# ARC STATUS

---------- --- ----------------

        11 YES INACTIVE

       (2a) If not above Result than run

             

                 alter system switch logfile;

                 SELECT GROUP#, ARCHIVED, STATUS FROM V$LOG where GROUP#=11;

GROUP# ARC STATUS

---------- --- ----------------

        11 YES INACTIVE

(3)    alter database drop logfile group 11;

(4)  SELECT GROUP#, ARCHIVED, STATUS FROM V$LOG where GROUP#=12;

    

      GROUP# ARC STATUS

---------- --- ----------------

        12 YES INACTIVE  

         (4a) If not above Result than run

alter system switch logfile;

SELECT GROUP#, ARCHIVED, STATUS FROM V$LOG where GROUP#=12;

    

      GROUP# ARC STATUS

---------- --- ----------------

        12 YES INACTIVE  

(5)  alter database drop logfile group 12;

(6)  SELECT GROUP#, ARCHIVED, STATUS FROM V$LOG where GROUP#=13;

    

      GROUP# ARC STATUS

---------- --- ----------------

        13 YES INACTIVE  

         (6a) If not above Result than run

alter system switch logfile;

SELECT GROUP#, ARCHIVED, STATUS FROM V$LOG where GROUP#=13;

    

      GROUP# ARC STATUS

---------- --- ----------------

        13 YES INACTIVE  

(7) alter database drop logfile group 13;

(8) ALTER DATABASE ADD LOGFILE THREAD 1

      GROUP 4 ('/data/redo01/ORCL/onlinelog/redo04a.dbf','/data/redo02/ORCL/onlinelog/redo04b.dbf','/data/redo03/ORCL/onlinelog/redo04c.dbf') SIZE 1024m;

(9) SELECT GROUP#, ARCHIVED, STATUS FROM V$LOG where GROUP#=14;

    

      GROUP# ARC STATUS

---------- --- ----------------

        14 YES INACTIVE  

         (9a) If not above Result than run

alter system switch logfile;

SELECT GROUP#, ARCHIVED, STATUS FROM V$LOG where GROUP#=14;

    

      GROUP# ARC STATUS

---------- --- ----------------

        14 YES INACTIVE

(10)  alter database drop logfile group 14;

(11)  ALTER DATABASE ADD LOGFILE THREAD 1

      GROUP 5 ('/data/redo01/ORCL/onlinelog/redo05a.dbf','/data/redo02/ORCL/onlinelog/redo05b.dbf','/data/redo03/ORCL/onlinelog/redo05c.dbf') SIZE 1024m;

(12) SELECT GROUP#, ARCHIVED, STATUS FROM V$LOG where GROUP#=15;

    

      GROUP# ARC STATUS

---------- --- ----------------

        15 YES INACTIVE  

         (12a) If not above Result than run

alter system switch logfile;

SELECT GROUP#, ARCHIVED, STATUS FROM V$LOG where GROUP#=15;

    

      GROUP# ARC STATUS

---------- --- ----------------

        15 YES INACTIVE

(13)  alter database drop logfile group 15;

(14)  SELECT GROUP#, ARCHIVED, STATUS FROM V$LOG where GROUP#=16;

    

      GROUP# ARC STATUS

---------- --- ----------------

        16 YES INACTIVE  

         (14a) If not above Result than run

alter system switch logfile;

SELECT GROUP#, ARCHIVED, STATUS FROM V$LOG where GROUP#=16;

    

      GROUP# ARC STATUS

---------- --- ----------------

        16 YES INACTIVE

(15) alter database drop logfile group 16;

(16)  SELECT GROUP#, ARCHIVED, STATUS FROM V$LOG where GROUP#=17;

    

      GROUP# ARC STATUS

---------- --- ----------------

        17 YES INACTIVE  

         (16a) If not above Result than run

alter system switch logfile;

SELECT GROUP#, ARCHIVED, STATUS FROM V$LOG where GROUP#=17;

    

      GROUP# ARC STATUS

---------- --- ----------------

        17 YES INACTIVE

(17) alter database drop logfile group 17;

Steps on Instance 2(10.10.10.2) ORCL2 Instance

 

 

select GROUP#,THREAD#,SEQUENCE#,BYTES/1024/1024,MEMBERS,ARCHIVED,STATUS from v$log where THREAD#=2;

 GROUP#    THREAD#  SEQUENCE# BYTES/1024/1024    MEMBERS ARC STATUS

---------- ---------- ---------- --------------- ---------- --- ----------------

        21          2       53168             300           3 YES INACTIVE

        22          2       53170             300           3 YES ACTIVE

        23          2       53169             300           3 YES INACTIVE

        24          2       53171             300           3 NO   CURRENT

        25          2       53165             300           3 YES INACTIVE

        26          2       53166             300           3 YES INACTIVE

        27          2       53167             300           3 YES INACTIVE

7 rows selected.

select GROUP#,MEMBER from v$logfile where GROUP# not in (11,12,13,14,15,16,17) order by 1;

 GROUP# MEMBER

---------- ----------------------------------------

        21 /data/redo04/ORCL/onlinelog/redo21a.dbf

        21 /data/redo05/ORCL/onlinelog/redo21b.dbf

        21 /data/redo06/ORCL/onlinelog/redo21c.dbf

        22 /data/redo04/ORCL/onlinelog/redo22a.dbf

        22 /data/redo05/ORCL/onlinelog/redo22b.dbf

        22 /data/redo06/ORCL/onlinelog/redo22c.dbf

        23 /data/redo04/ORCL/onlinelog/redo23a.dbf

        23 /data/redo05/ORCL/onlinelog/redo23b.dbf

        23 /data/redo06/ORCL/onlinelog/redo23c.dbf

        24 /data/redo04/ORCL/onlinelog/redo24a.dbf

        24 /data/redo05/ORCL/onlinelog/redo24b.dbf

        24 /data/redo06/ORCL/onlinelog/redo24c.dbf

        25 /data/redo04/ORCL/onlinelog/redo25a.dbf

        25 /data/redo05/ORCL/onlinelog/redo25b.dbf

        25 /data/redo06/ORCL/onlinelog/redo25c.dbf

        26 /data/redo04/ORCL/onlinelog/redo26a.dbf

        26 /data/redo05/ORCL/onlinelog/redo26b.dbf

        26 /data/redo06/ORCL/onlinelog/redo26c.dbf

        27 /data/redo04/ORCL/onlinelog/redo27a.dbf

        27 /data/redo05/ORCL/onlinelog/redo27b.dbf

        27 /data/redo06/ORCL/onlinelog/redo27c.dbf

21 rows selected. 

(18)   ALTER DATABASE ADD LOGFILE THREAD 2

      GROUP 6 ('/data/redo04/ORCL/onlinelog/redo06a.dbf','/data/redo05/ORCL/onlinelog/redo06b.dbf','/data/redo06/ORCL/onlinelog/redo06c.dbf') SIZE 1024M,

      GROUP 7 ('/data/redo04/ORCL/onlinelog/redo07a.dbf','/data/redo05/ORCL/onlinelog/redo07b.dbf','/data/redo06/ORCL/onlinelog/redo07c.dbf') SIZE 1024M,

      GROUP 8 ('/data/redo04/ORCL/onlinelog/redo08a.dbf','/data/redo05/ORCL/onlinelog/redo08b.dbf','/data/redo06/ORCL/onlinelog/redo08c.dbf') SIZE 1024M);

 

(19) SELECT GROUP#, ARCHIVED, STATUS FROM V$LOG where GROUP#=21;

GROUP# ARC STATUS

--------------- ---------- --- ----------------

      21 YES INACTIVE

  

                  

                           (19a)   If not above Result than run

                            alter system switch logfile ;

 SELECT GROUP#, ARCHIVED, STATUS FROM V$LOG where GROUP#=21;

GROUP# ARC STATUS

--------------- ---------- --- ----------------

      21 YES INACTIVE

(20) alter database drop logfile group 21;

(21)  SELECT GROUP#, ARCHIVED, STATUS FROM V$LOG where GROUP#=22;

GROUP# ARC STATUS

--------------- ---------- --- ----------------

      22 YES INACTIVE

                (21a)  If not above Result than run

                     alter system switch logfile;

                     SELECT GROUP#, ARCHIVED, STATUS FROM V$LOG where GROUP#=22;

GROUP# ARC STATUS

--------------- ---------- --- ----------------

      22 YES INACTIVE

(22)   alter database drop logfile group 22;

(23)  SELECT GROUP#, ARCHIVED, STATUS FROM V$LOG where GROUP#=23;

GROUP# ARC STATUS

---------------- ---------- --- ----------------

      23 YES INACTIVE

          (23a)  If not above Result than run

                    alter system switch logfile ;

SELECT GROUP#, ARCHIVED, STATUS FROM V$LOG where GROUP#=23;

GROUP# ARC STATUS

--------------- ---------- --- ----------------

      23 YES INACTIVE

(24)     alter database drop logfile group 23;

(25)  ALTER DATABASE ADD LOGFILE THREAD 2

      GROUP 9 ('/data/redo04/ORCL/onlinelog/redo09a.dbf','/data/redo05/ORCL/onlinelog/redo09b.dbf','/data/redo06/ORCL/onlinelog/redo09c.dbf') SIZE 1024M;

(26)   SELECT GROUP#, ARCHIVED, STATUS FROM V$LOG where GROUP#=24;

                   GROUP# ARC STATUS

---------- --- ----------------

      24 YES INACTIVE

                 

                (26a)  If not above Result than run

              

                 alter system switch logfile 24;

 SELECT GROUP#, ARCHIVED, STATUS FROM V$LOG where GROUP#=24;

GROUP# ARC STATUS

---------- --- ----------------

      24 YES INACTIVE

(27) alter database drop logfile group 24;

ALTER DATABASE ADD LOGFILE THREAD 2

      GROUP 10 ('/data/redo04/ORCL/onlinelog/redo10a.dbf','/data/redo05/ORCL/onlinelog/redo10b.dbf','/data/redo06/ORCL/onlinelog/redo10c.dbf') SIZE 1024M;

(28)  SELECT GROUP#, ARCHIVED, STATUS FROM V$LOG where GROUP#=25;

  GROUP# ARC STATUS

---------- --- ----------------

        25 YES INACTIVE

         (28a)  If not above Result than run

          alter system switch logfile ;

 SELECT GROUP#, ARCHIVED, STATUS FROM V$LOG where GROUP#=25;

  GROUP# ARC STATUS

---------- --- ----------------

        25 YES INACTIVE

(29)  alter database drop logfile group 25;

(30)          SELECT GROUP#, ARCHIVED, STATUS FROM V$LOG where GROUP#=26;

GROUP# ARC STATUS

---------- --- ----------------

        26 YES INACTIVE

                 (30a)  If not above Result than run

                      

alter system switch logfile;

 SELECT GROUP#, ARCHIVED, STATUS FROM V$LOG where GROUP#=26;

  GROUP# ARC STATUS

---------- --- ----------------

        26 YES INACTIVE

(31) alter database drop logfile group 26;

(32) SELECT GROUP#, ARCHIVED, STATUS FROM V$LOG where GROUP#=27;

GROUP# ARC STATUS

---------- --- ----------------

        27 YES INACTIVE

         

                (31a) If not above Result than run

               alter system switch logfile;

             SELECT GROUP#, ARCHIVED, STATUS FROM V$LOG where GROUP#=27;

GROUP# ARC STATUS

---------- --- ----------------

        27 YES INACTIVE

(32)    alter database drop logfile group 27;

Note:- Find Out the Output and Confirm with Planed Activity.

For Instance 1

select GROUP#,THREAD#,SEQUENCE#,BYTES/1024/1024,MEMBERS,ARCHIVED,STATUS from v$log where THREAD#=1;

select GROUP#,MEMBER from v$logfile where GROUP# in (1,2,3,4,5) order by 1;

For Instance 2

select GROUP#,THREAD#,SEQUENCE#,BYTES/1024/1024,MEMBERS,ARCHIVED,STATUS from v$log where THREAD#=1;

select GROUP#,MEMBER from v$logfile where GROUP# not  in (1,2,3,4,5) order by 1;

Oracle Inventory does not exists on Application Node

Issue: Oracle Inventory does not exists on Application Node

export PATH=$ORACLE_HOME/OPatch:$PATH

$ opatch lsinventory

Oracle Interim Patch Installer version 1.0.0.0.57

Copyright (c) 2007 Oracle Corporation. All Rights Reserved..

We recommend you refer to the OPatch documentation under

OPatch/docs for usage reference. We also recommend using

the latest OPatch version. For the latest OPatch version

and other support related issues, please refer to document

293369.1 which is viewable from metalink.oracle.com

Cannot open the file: /etc/oraInst.loc

Please use -invPtrLoc option and specify the correct file for OPatch to look for central inventory.

ERROR: OPatch failed because of Inventory problem.

 

 

Solution:

 1)   Check oraInst.loc file exists in /etc

2)   If file doesnot exists then create the file and add inventory location

inventory_loc=/home/applclon/oraInventory

Set Permission to oraInst.loc file

3)   Now Run the Installer to create the inventory

$ cd $ORACLE_HOME/oui/bin

$ pwd

/apps_UAT/prodclone/apps/tech_st/10.1.2/oui/bin

./runInstaller -silent -attachHome -invPtrLoc /etc/oraInst.loc ORACLE_HOME="/apps_UAT/prodclone/apps/tech_st/10.1.2" ORACLE_HOME_NAME="applclon_apps_tech_st_10_1_2"

Send Mail Using Oracle Package

1) Check Package UTL_MAIL is available

desc sys.utl_mail

PROCEDURE SEND

Argument Name                  Type                    In/Out Default?

------------------------------ ----------------------- ------ --------

SENDER                         VARCHAR2                IN

RECIPIENTS                     VARCHAR2                IN

CC                             VARCHAR2                IN     DEFAULT

BCC                            VARCHAR2                IN     DEFAULT

SUBJECT                        VARCHAR2                IN     DEFAULT

MESSAGE                        VARCHAR2                IN     DEFAULT

MIME_TYPE                      VARCHAR2                IN     DEFAULT

PRIORITY                       BINARY_INTEGER          IN     DEFAULT

 Package is available, If Package is not available create it using,

SQL> @?/rdbms/admin/utlmail.sql
Package created
Synonym created

2) Create UTL_MAIL Package Body

SQL> sho user

USER is "SYS"

SQL> @?/rdbms/admin/prvtmail.plb

Package created.

Package body created.

Grant succeeded.

Package body created.

No errors.

3) Edit the host file with Mail Server hostname and IP

10.11.1.1       hostname.domainname   hostname

4) Port must be Open to access Mail Server

$ telnet delhint1 100

Trying...

Connected to delhint1.iffco.nic.in.

Escape character is '^]'.

220 hostname ESMTP Service (Lotus Domino Release 8.5.2) ready at Wed, 7 Sep 2011 17:25:06 +0530

<<Ctrl c>>

[1] + Stopped (SIGTSTP)        telnet hostname 100

5) Create Directory 'MAIL_DIR'

Conn / as sysdba

CREATE OR REPLACE DIRECTORY MAIL_DIR AS '/home/orauat/vivek/scr/sql/';

 

 6) Run the Procedure

@/home/orauat/vivek/scr/sql/dbstatusmail.sql

$ cat dbstatusmail.sql

alter session SET smtp_out_server = 'hostname:100';

declare

v_utl_filehandler       UTL_FILE.FILE_TYPE;

v_rawfile                       RAW(32767);

v_size                               NUMBER;

v_block                              NUMBER;

v_boolean                   BOOLEAN;

v_file_dir                      varchar2(100);

v_transfer_location varchar2(100) := 'reciever@gmail.com';

v_file_name varchar2(100) := 'dbdetail.txt';

begin

v_file_dir := 'MAIL_DIR';

v_utl_filehandler := UTL_FILE.FOPEN(v_file_dir, v_file_name, 'r');

UTL_FILE.fgetattr(v_file_dir, v_file_name, v_boolean, v_size, v_block);

UTL_FILE.get_raw(v_utl_filehandler, v_rawfile, v_size);

UTL_FILE.FCLOSE(v_utl_filehandler);

sys.utl_mail.send_attach_raw( sender            =>      'sender@gmail.com'

, recipients    =>      v_transfer_location

, subject               =>      'Database backup status from Production'

, message               =>      'Dear Sir'||CHR(13)||CHR(13)||

'Please find attached database status file from Production.'||CHR(13)||CHR(13)||

'For any issue reported in it, please check it on server.'||CHR(13)||CHR(13)||

'Thanks'||CHR(13)||

'Vivek Bhutani'||CHR(13)||CHR(13)

, attachment    =>      v_rawfile

, att_inline    =>      FALSE

, att_filename  =>      v_file_name

);

end;

/

exit;

Steps to recreate AWR Repository(Automatic Workload)

We normally do cloning of our databases which sometimes add multiple DBID's  in the repository.
We can check the multiple dbid's while generating the awr report using @?/rdbms/admin/awrrpt
To delete all the unwanted dbid's we have to recreate the repository.

DB_ID           DB_NAME   HOST_PLATFORM                                             INST
-----------------  -----------  --------------------------------------------------------      -----------
826177187   PRODDEV   ifebdbdev - AIX-Based Systems (64-bit)         1
*3309173529  EBSDBPRD  IFEBPRD1 - AIX-Based Systems (64-bit)    1
3309173529  EBSDBPRD  IFEBPRD2 - AIX-Based Systems (64-bit)     2
212389454  PRODDDB1  PRODDB - AIX-Based Systems (64-bit)          1

The doc is based on the RAC 2 node database using pfile.

Steps to recreate the AWR (Automatic Workload Repository):

1) shut down application services
2) shut down node 1 database
$ sqlplus
SQL*Plus: Release 11.2.0.1.0 Production on Thu Aug 4 14:50:28 2011
Copyright (c) 1982, 2009, Oracle.  All rights reserved.
Enter user-name: / as sysdba
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options

SQL> create spfile from pfile;
File created.

SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

3) shut down node 2 database
SQL> shu immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

4) Perform rest of the activites on node 1 database
SQL> startup
SQL> select name from v$database;
SQL> show parameter cluster_database
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
cluster_database                     boolean     TRUE
cluster_database_instances           integer     2

SQL> show parameter statistics_level
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
statistics_level                     string      TYPICAL

SQL> show parameter sga_target
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sga_target                           big integer 4G

SQL> alter system set sga_target=0 scope=spfile;
System altered.

SQL> alter system set cluster_database=false scope=spfile;
System altered.

SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup restrict
ORACLE instance started.
Total System Global Area  509485056 bytes
Fixed Size                  2208088 bytes
Variable Size             444599976 bytes
Database Buffers           50331648 bytes
Redo Buffers               12345344 bytes
Database mounted.
Database opened.

SQL> show parameter cluster_database
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
cluster_database                     boolean     FALSE
cluster_database_instances           integer     1

SQL> show parameter statistics_level
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
statistics_level                     string      TYPICAL

SQL> show parameter sga_target
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sga_target                           big integer 0

SQL> alter system set statistics_level = basic scope=both;
System altered.

SQL> show parameter statistics_level
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
statistics_level                     string      BASIC

SQL> @?/rdbms/admin/catnoawr.sql
SQL> alter system flush shared_pool;
SQL> @?/rdbms/admin/catawr.sql
SQL> @?/rdbms/admin/utlrp.sql
sql> @?/rdbms/admin/execsvrm.sql

Remove the spfile created in the previous step and bounce the database with the existing initebsprod1.ora file
SQL> show parameter sga_target
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sga_target                           big integer 4G

SQL> show parameter statistics_level
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
statistics_level                     string      TYPICAL

SQL> show parameter cluster_database;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
cluster_database                     boolean     TRUE
cluster_database_instances           integer     2

Please check invalid objects exists are not , if exists then please compile it manually
To Create the AWR report, run below script
sql> @$ORACLE_HOME/rdbms/admin/awrrpt.sql

5) startup node2