SYSAUX Tablespace growth because of DB stat history

View current history level

select dbms_stats.get_stats_history_availability from dual;

Applying purge stat from 20 days to 200 days.

begin
for i in reverse 20..200
loop
dbms_stats.purge_stats(sysdate-i);
end loop;
end;
/

Confirm recent analyzed date

select dbms_stats.get_stats_history_availability from dual;

        to reclaim the space,
alter table SYS.WRI$_OPTSTAT_HISTGRM_HISTORY enable row movement;
alter table SYS.WRI$_OPTSTAT_HISTGRM_HISTORY shrink space;
alter table SYS.WRI$_OPTSTAT_HISTGRM_HISTORY deallocate unused;

al    alter table SYS.WRI$_OPTSTAT_HISTHEAD_HISTORY enable row movement;
alter table SYS.WRI$_OPTSTAT_HISTHEAD_HISTORY shrink space;
alter table SYS.WRI$_OPTSTAT_HISTHEAD_HISTORYdeallocate unused;

 
 
Reclaim index stat

alter index SYS.I_WRI$_OPTSTAT_H_ST INDEX shrink space;
alter index SYS.I_WRI$_OPTSTAT_H_ST INDEX deallocate unused;

      alter index SYS.I_WRI$_OPTSTAT_HH_OBJ_ICOL_ST INDEX shrink space;
alter index SYS.I_WRI$_OPTSTAT_HH_OBJ_ICOL_ST INDEX deallocate unused;

Then do

Gathering statistics for all objects in the database:

exec dbms_stats.gather_database_stats( -
cascade => TRUE, -
method_opt => 'FOR ALL COLUMNS SIZE 1' );

 

Recovering Database after manual delete of DBF files

Issue after deleting

When we try to shutdown the DB

ORA-01116: error in opening database file 41
ORA-01110: data file 41: '/data2/dbfname.dbf'
ORA-27041: unable to open file


 Solution

Shutdown abort


exit

sqlplus sys/xxx as sysdba

Alter database mount

alter database datafile '' offline drop;

Alter database open

Data Guard Switch Over Fail

Version Oracle 11.2.0...

DGMGRL > switchover to standby

is hanged and not completed


Errors

Both Database’s became to Standby Status after braking the operation

Data Guard Error > DGM-17017: unable to determine configuration status

Error: ORA-16782: instance not open for read and write access,

This service status is getting offline > ora.XX.XXX.svc :

Multiple errors on DG status

Scenerio

Both Primary and Stand by database's became to standby db role and Data Guard configuration is not shoowing success message.Requirment is to solve the issue and switch to Standby Site.

DGMGRL> show configuration

Configuration - dataguard
 Protection Mode: MaxPerformance
  Databases:
   Primary       - Primary database
      Error: ORA-16782: instance not open for read and write access

    Standby - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
ERROR

SQL> select database_role from gv$database;

DATABASE_ROLE
----------------
PHYSICAL STANDBY

PHYSICAL STANDBY

Solution 

First requrment is to make Primary database status as Primary (Now both servers are in standby mode and read only)

srvctl stop database -d primary

srvctl start database -d primary -o mount \

SQL> alter database recover managed standby database cancel;
SQL> alter database recover managed standby database finish;
SQL> ALTER database commit to switchover to primary with session shutdown;
SQL> alter database open;
SQL> select database_role from v$database;

DATABASE_ROLE
----------------
PRIMARY

Manualt start the failed crs service

crs_start ora.XX.XXX.svc

Use crs_stat and ensure all relevent crs services are running

Perform a Manual Log switching and check whether they are applied in standyby

SQL> aler system switch logfile

SQL> alter system switch logfile;

System altered.

SQL> select THREAD#, max(SEQUENCE#) from v$archived_log where APPLIED='YES' group by THREAD#;

   THREAD# MAX(SEQUENCE#)
---------- --------------
         1          22598
         2          22009

SQL> select THREAD#, max(SEQUENCE#) from v$archived_log group by THREAD#;

   THREAD# MAX(SEQUENCE#)
---------- --------------
         1          22598
         2          22010

SQL> alter system switch logfile;

System altered.

SQL>  select THREAD#, max(SEQUENCE#) from v$archived_log group by THREAD#;

   THREAD# MAX(SEQUENCE#)
---------- --------------
         1          22599
         2          22010

 If every thing is ok (

1.       Check DB Roles, Log Sequence, DG Status ,Alert Logs, Open Mode on both standby and Primary)

switch over to standby again

SQL> select open_mode from gv$database;

OPEN_MODE
--------------------
READ WRITE
READ WRITE

DGMGRL> switch over to standby

Then make Previous primary to mount mode

 srvctl stop database -d previousprimary

srvctl start database -d previousprimary -o mount

Confirm new DG status

 DGMGRL> show configuration

Configuration - NewPrimary

  Protection Mode: MaxPerformance
  Databases:
    imalstdby - NewPrimary
    imal      - EarlierPrimary(Now standby)

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

Cheers !!

 

Applying Oracle Critical Patch - CPU

Follow these steps:

Assume that we are in a single instance (Not RAC), shut down all instances and listeners associated with the Oracle home that you
are updating.

Ensure that the $PATH has the following executables: make, ar, ld, and nm.
The location of these executables depends on your operating system. On many operating systems, they are
located in /usr/ccs/bin, in which case you can set your PATH as follows:

export PATH=$PATH:/usr/ccs/bin
or
export PATH=$PATH:$ORACLE_HOME/OPatch


Set your current directory to the directory where the patch is located and then run the OPatch utility by entering
the following commands:
unzip p14390377_112020_.zip
cd 14390377

Then run

opatch napply -skip_subset -skip_duplicate

After installing succefully check

opatch lsinventory

You can see the latest patch versions updated

connect to the database using SQL*Plus. Connect as SYSDBA and run the catbundle.sql script as follows:
cd $ORACLE_HOME/rdbms/admin
sqlplus /nolog
SQL> CONNECT / AS SYSDBA
SQL> STARTUP
SQL> @catbundle.sql cpu apply
SQL> QUIT

If catbundle is not recognizing run it with the path

SQL > @?/rdbms/admin/catbundle.sql cpu apply

Finally check the below path for any errors

catbundle_CPU__APPLY_.log
catbundle_CPU__GENERATE_.log

References
https://updates.oracle.com/Orion/Services/download?type=readme&aru=15433726#CHDCBBFE

Recovering from Missed /Deleted dbf file in Oracle

Scenerio- one or more datafiles are deleted from OS level and databae is not geeting open. Issue SQL> startup ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance ORACLE instance started. Total System Global Area 534462464 bytes Fixed Size 2215064 bytes Variable Size 390071144 bytes Database Buffers 134217728 bytes Redo Buffers 7958528 bytes Database mounted. ORA-01157: cannot identify/lock data file 8 - see DBWR trace file ORA-01110: data file 8: '/u01/app4/oracle/oradata/uatdb3/rman_tbs02.dbf' Solution SQL> alter database datafile '/u01/app4/oracle/oradata/uatdb3/rman_tbs02.dbf' offline drop; Database altered. SQL> alter database backup controlfile to trace as '/u01/app/test.ctl'; Database altered. SQL> alter database open 2 ; Database altered.

YouLikeHits.com

Recovering from corrupted control file

SQL> startup;
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.

Total System Global Area 3340451840 bytes
Fixed Size                  2231088 bytes
Variable Size            2516583632 bytes
Database Buffers          805306368 bytes
Redo Buffers               16330752 bytes
ORA-00205: error in identifying control file, check alert log for more info

Connect to RMAN Target and try to recover from backup control file


RMAN> restore controlfile from '/u02/backups/cfc-4050291476-20120814-01';

Starting restore at 14-AUG-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=63 device type=DISK

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/u01/app/oracle/oradata/imalmock/control01.ctl
output file name=/u01/app/oracle/fast_recovery_area/imalmock/control02.ctl
Finished restore at 14-AUG-12

RMAN> alter database mount;

database mounted
released channel: ORA_DISK_1

RMAN> show all;

RMAN configuration parameters for database with db_unique_name IMALMOCK are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 3;
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/u02/backups/cf%F';
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT   '/u02/backups/rmancits1_%U';
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/snapcf_imalmock.f'; # default

RMAN> alter database open;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of alter db command at 08/14/2012 14:28:51
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open


RMAN> alter database open resetlogs;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of alter db command at 08/14/2012 14:29:17
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/u01/app/oracle/oradata/imalmock/system01.dbf'

RMAN> restore database;

Starting restore at 14-AUG-12
Starting implicit crosscheck backup at 14-AUG-12
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=63 device type=DISK
Crosschecked 10 objects
Finished implicit crosscheck backup at 14-AUG-12

Starting implicit crosscheck copy at 14-AUG-12
using channel ORA_DISK_1
Finished implicit crosscheck copy at 14-AUG-12

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 00001 to /u01/app/oracle/oradata/imalmock/system01.dbf
channel ORA_DISK_1: restoring datafile 00002 to /u01/app/oracle/oradata/imalmock/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/imalmock/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/imalmock/users01.dbf
channel ORA_DISK_1: restoring datafile 00005 to /u01/app/oracle/oradata/imalmock/example01.dbf

[oracle@imalmock bin]$ rman target /

Recovery Manager: Release 11.2.0.2.0 - Production on Tue Aug 14 14:50:25 2012

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

connected to target database: IMALMOCK (DBID=4050291476, not open)

RMAN> recover database;

Starting recover at 14-AUG-12

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=63 device type=DISK

starting media recovery

archived log for thread 1 with sequence 1120 is already on disk as file /u01/app/oracle/oradata/imalmock/redo8.log

archived log file name=/u01/app/oracle/oradata/imalmock/redo8.log thread=1 sequence=1120

media recovery complete, elapsed time: 00:00:04

Finished recover at 14-AUG-12

RMAN> exit

Recovery Manager complete.

[oracle@imalmock bin]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Tue Aug 14 14:50:44 2012

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

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> startup;

ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance

ORA-01081: cannot start already-running ORACLE - shut it down first

SQL> shutdown immediate;

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

SQL> startup;

ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance

ORACLE instance started.

Total System Global Area 3340451840 bytes

Fixed Size                  2231088 bytes

Variable Size            2516583632 bytes

Database Buffers          805306368 bytes

Redo Buffers               16330752 bytes

Database mounted.

ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

SQL>

RMAN> alter database open resetlogs;

using target database control file instead of recovery catalog

database opened

RMAN>

Now the restore process is finish