Delete old .trc, .trm, .aud files older than X days +

Hi Folks,

Some time we need to delete old logs. And at the same time we want to keep files for last 2-3 days. Use below command at linux box.

For example, if  you want to clear audit logs, please use below command.

find /u01/app/11.2.0.4/grid/rdbms/audit/ -mindepth 1 -type f -mtime +2 | xargs rm

Here :

"/u01/app/11.2.0.4/grid/rdbms/audit/ "  is directory where find command will look
"-mtime + 2"    is the current date - 2 days.

System datafile added to filesystem location

Hi Folks,

Many time's this happens. Either due to typo error or accidently we add to filesystem and it was supposed to be in ASM file system.

Solution is to rename the datafile to new (ASM filesystem) location.

Here are steps to fix this and place the datafile back to ASM file system.

In my example, a system datafile was accidently added to dbs location. System datafile is different from non system datafile.



SQL> select file_name,file_id from dba_data_files where tablespace_name='SYSTEM';

FILE_NAME                                       FILE_ID
------------------------------------------------                            ---------------
+ASHISH_DATA/ORCL/datafile/system01.dbf           1
/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/ +ASHISH_DATA          5



Now to fix this:

1.) Start the database is mount mode.
2.) Using RMAN,
rman> copy datfile <datafile#> to '+DISKGROUP';
rman> switch datafile <datafile#> to copy;
3.) open the database;

Example:

You need know the datafile# of the file you want to rename.

Use below two statement to see datafile # and if any extents exists on that datafile


SQL> select count(*) from dba_extents where file_id=5;


COUNT(*)
----------
0


SQL> SQL> select file_id from dba_data_files where file_name='/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/ +ASHISH_DATA';

FILE_ID
----------
5


Now I have my datafile# as 5.

SQL> startup mount
ORACLE instance started.

Total System Global Area  730714112 bytes
Fixed Size                  2256832 bytes
Variable Size             482345024 bytes
Database Buffers          243269632 bytes
Redo Buffers                2842624 bytes
Database mounted.
SQL>


Now database is in mount mode, connect to rman;

$ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Wed Jul 14 06:08:58 2016

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

connected to target database: ORCL (DBID=3487895465, not open)

RMAN> copy datafile 5 to '+ASHISH_DATA';

Starting backup at 14-JUL-16
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=02 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00005 name=/u01/app/oracle/product/11.2.0.4/dbhome_1/                                                                                        dbs/ +ASHISH_DATA
output file name=+ASHISH_DATA/ORCL/datafile/system.273.917057511 tag=TAG20160714T056450                                                                                         RECID=1 STAMP=917057510
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 14-JUL-16

RMAN> switch datafile 5 to copy;

datafile 5 switched to datafile copy "+ASHISH_DATA/ORCL/datafile/system.273.917057511"



Now connect to sql prompt and open the database


SQL> alter database open;

Database altered.

SQL> select file_name,file_id from dba_data_files where tablespace_name='SYSTEM';

FILE_NAME                          FILE_ID
-----------------------------------------------------                         --------------
+ASHISH_DATA/ORCL/datafile/system01.dbf 1
+ASHISH_DATA/ORCL/datafile/system.273.917057511 5

Best of luck !!!!!!!!!!!!!

How to check Database is in restricted mode or not

Hi All,

Users are not allowed to login in restricted mode. You get ORA-01035: ORACLE only available to users with RESTRICTED SESSION privilege

Login to database and

SQL> select logins from v$instance;
.

Example:


SQL> select * from global_name;

GLOBAL_NAME
--------------------------------------------------------------------------------
TEST

SQL> Select logins from v$instance;

LOGINS
----------
RESTRICTED

SQL>


It should be ALLOWED.

How to compare AWR report for two time period

You can do it using awrddrpt.sql

During performance troubleshooting we need to compare the AWR report for good time and bad time. Below the step to compare the awr for two time period


Consider the time you are comparing are::::::::::


First pair of snap id's:
25 April 2016 5:00 AM SNAP ID  19102
25 April 2016 6:00 AM SNAP ID  19103


Second pair of snap id's:
29 April 2016 5:00 AM SNAP ID  19198
29 April 2016 6:00 AM SNAP ID  19199


Example:

connect to sqlplus using sysdba


sql> @?/rdbms/admin/awrddrpt.sql


Specify the First Pair of Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 19102
First Begin Snapshot Id specified: 19102

Enter value for end_snap: 19103
First End   Snapshot Id specified: 19103




Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

   DB Id     Inst Num DB Name      Instance     Host
------------ -------- ------------ ------------ ------------
* 98999865678  1 TSTPRD      TSTPRD      ashish.mydomain.com
                                             
|
|
|
|
Database Id and Instance Number for the Second Pair of Snapshots
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Using 98999865678 for Database Id for the second pair of snapshots
Using          1 for Instance Number for the second pair of snapshots


Specify the number of days of snapshots to choose from
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Entering the number of days (n) will result in the most recent
(n) days of snapshots being listed.  Pressing <return> without
specifying a number lists all completed snapshots.


Enter value for num_days2: 19198

Listing the last 19198 days of Completed Snapshots



                              19275 02 May 2016 10:00      1



Specify the Second Pair of Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap2: 19199
Second Begin Snapshot Id specified: 19199

Enter value for end_snap2: 19199
Second End   Snapshot Id specified: 19199



And then you got the report to compare.

How to Drop existing undo tablespace ?

You cannot drop an undo tablespace unless you have another undo tablespace. I am demonstrating the scenario to drop the existing undo table and create a new one.

Purpose of dropping an existing may differ. I my case, I wanted to move undo tablespace to different mountpoint.


select FILE_NAME,TABLESPACE_NAME,BYTES/1024/1024/1024 from dba_data_files;


FILE_NAME                                      TABLESPACE_NAME                BYTES/1024/1024/1024
------------------------------                --------------------            ---------------------
/u01/app/oracle/oradata/TEST/users01.dbf        USERS                        .008544922

/u01/app/oracle/oradata/TEST/undotbs01.dbf     UNDOTBS1                      15.7519531

/u01/app/oracle/oradata/TEST/sysaux01.dbf       SYSAUX


Thats my existing undo tablespace, Now I will create a new undo tablespace.


create undo tablespace undo2 datafile  '/u02/oradata/TEST/undotbs_01.dbf' size 12G;

Tablespace created.


Now, make the new undo tablespace as default tablespace.


SQL> alter system set undo_tablespace=undo2 scope=both;

System altered.

Now we can go ahead and drop the old undo tablespace i.e. UNDOTBS1

SQL> drop tablespace UNDOTBS1 including contents and datafiles;

Tablespace dropped.



Good Luck!!!!

How to know when table was modified

Being a DBA.....many times we get a question. Can you please tell when this table was last updated. Or may be if any DML operations happened.

Here is the answer for your question.

select * from dba_tab_modifications
select * from all_tab_modifications


Example:


select TABLE_OWNER,TABLE_NAME,INSERTS,UPDATES,DELETES,TIMESTAMP from dba_tab_modifications where rownum<10 and table_owner='SYSMAN';

Sheet Name :- Sheet1

---

TABLE_OWNER	TABLE_NAME	INSERTS	UPDATES	DELETES	TIMESTAMP
SYSMAN	AQ$_EM_EVENT_BUS_TABLE_L	176	0	175	7-Apr-16
SYSMAN	BIN$LKnxq0szBVrgU7QDAwoS5A==$0	1	1	0	26-Feb-16
SYSMAN	DB_GI_OH_TARGET_DETAILS	0	1	0	7-Apr-16
SYSMAN	DB_INSTANCE_CAGING_ECM	11	17	12	7-Apr-16
SYSMAN	EM_AVAILABILITY	16	0	0	7-Apr-16
SYSMAN	EM_COMPOSITE_KEYS	32	0	0	15-Mar-16
SYSMAN	EM_CONSOLE_PERF_STATS_LOG	0	0	2	7-Apr-16
SYSMAN	EM_CPUACT_STATS	0	0	0	7-Apr-16
SYSMAN	EM_CREDENTIAL_SET_TYPES	8	0	0	29-Feb-16

---

Sheet Name :- Sheet2

---

---

Sheet Name :- Sheet3

---

---

ORA-19622: archived log thread 1 sequence 0001 not restored due to missing data while restore

Very recently I got stuck with my db restore. The error which occurred and failed my restore with ORA-19622. 

Things to observe:

1.) We had a valid backup to restore
2.) The archive log sequence which gave error while restore was physically available on the backup server.


I tried multiple restore but no success.

If you guys stuck in such situation. Please dont waste your time.



This is an oracle bug. 


Bug 18674170 : RMAN RESTORE FOR AN ARCHIVED LOG FAILS WITH ORA-19622 MISSING DATA

Unable to restore Archive logs size > 32 GB and fails with ORA-19622: archived log thread 1 sequence 0001 not restored due to missing data.

This is confirmed bug for versions 11.2.02, 11.2.0.3 and 11.2.04

 There is a one off patch available for this bug.

I would recommend contact Oracle support for this issue.