Sunday 30 December 2012

DATABASE BLOCK CORRUPTIONS CAN BE RECOVERED USING RMAN


DATABASE BLOCK CORRUPTIONS CAN BE RECOVERED USING RMAN

cause:- Fractured block found during validation



 rman target /
Recovery Manager: Release 11.2.0.1.0 – Production on Thu Dec 24 11:11:08 2012
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: ARJUN (DBID=3469830310)
RMAN> RECOVER CORRUPTION LIST;
RMAN> recover corruption list;
Starting recover at 24-DEC-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=73 device type=DISK
finished standby search, restored 2 blocks
starting media recovery
media recovery complete, elapsed time: 00:00:01
Finished recover at 24-DEC-12
RMAN>
SQL > SQL> select * from v$database_block_corruption;
no rows selected
SQL> SQL>

Wednesday 19 December 2012

Oracle Database Auditing




Oracle Database Auditing allows to profile user database activities.

Auditing is the monitoring, profiling and recording of selected user database actions.
The relevant records can be stored in the db audit trail or in OS files.
Auditing can be done on some individual actions, such as the type of SQL statement executed, or on combinations of data that can include the user name, application, time, and so on.

It's possible to audit both successful and failed activities.
In Oracle 11g auditing is turned on by default.
In Oracle 10g you have to enable it, but nothing is audited by default once it is active.

Oracle audit important parameters:
[oracle@localhost ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.1.0.6.0 -
Copyright (c) 1982, 2007, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> show parameter audit
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest                      string      /u01/app/oracle/admin/MYDBINST/a
                                                 dump
audit_sys_operations                 boolean     FALSE
audit_syslog_level                   string
audit_trail                          string      DB
SQL>
audit_file_dest - specifies the OS directory where the audit trail is written, when the AUDIT_TRAIL initialization parameter is set to os, xml, or xml,extended
audit_trail - enables or disables database auditing.
Values (for parameter audit_trail):  
none - database auditing will be disabled.

os - will enable db auditing and directs all audit records to the operating system's audit trail.
db - enables database auditing and directs all audit records to the database audit trail (the SYS.AUD$ table).
db,extended - enables database auditing and directs all audit records to the database audit trail (the SYS.AUD$ table). In addition, populates the SQLBIND and SQLTEXT CLOB columns of the SYS.AUD$ table.
xml - enables database auditing and writes all audit records to XML format OS files.
xml,extended - enables database auditing and prints all columns of the audit trail, including SqlText and SqlBind values.
In case audit_trail=db or audit_trail=db,extended is enabled, the audit trail is stored in the SYS.AUD$ table.

You can query it directly or via related dictionary views:
DBA_AUDIT_EXISTS
DBA_AUDIT_OBJECT
DBA_AUDIT_POLICIES
DBA_AUDIT_POLICY_COLUMNS
DBA_AUDIT_SESSION
DBA_AUDIT_STATEMENT
DBA_AUDIT_TRAIL
DBA_COMMON_AUDIT_TRAIL
DBA_FGA_AUDIT_TRAIL
DBA_OBJ_AUDIT_OPTS
DBA_PRIV_AUDIT_OPTS
DBA_REPAUDIT_ATTRIBUTE
DBA_REPAUDIT_COLUMN
DBA_STMT_AUDIT_OPTS
Example:
-- The db,extend setting will populate the SYS.AUD$ and DBA_AUDIT_TRAIL sql_text field.
-- Note, in Oracle 10g release 1, the db_extended was used in place of db,extended.
SQL> ALTER SYSTEM SET audit_trail=db,extended SCOPE=SPFILE; 
System altered.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 1887350784 bytes
Fixed Size                  2145504 bytes
Variable Size            1241514784 bytes
Database Buffers          419430400 bytes
Redo Buffers              224260096 bytes
Database mounted.
Database opened.
SQL> AUDIT SELECT TABLE, UPDATE TABLE BY myuser;
Audit succeeded.
SQL> conn myuser/mypass
Connected.
SQL> select * from sometab;
       AAA
----------
       123
SQL>
select * from dba_audit_trail where username = 'MYUSER';
select *
  from dba_audit_trail
where username = 'MYUSER'
   and owner = 'MYUSER';
Audit command format:
AUDIT {statement_option|privilege_option} [by user] [by {session|access}] [ whenever {successful|unsuccessful}];
The difference between BY SESSION and BY ACCESS is that when you specify BY SESSION Oracle will try to merge multiple audit entries into one record when the session and the action audited match. BY SESSION causes Oracle to write a single record for all SQL statements of the same type issued in the same session. BY ACCESS causes
Oracle to write one record for each access.

Audit Oracle commands examples:
AUDIT SESSION; - will audit all successful and unsuccessful connections to and disconnections from the database, regardless of user.
AUDIT SESSION by myuser; - the same, like a previous option, but set selectively, for individual user 'myuser'.
AUDIT INSERT TABLE, UPDATE TABLE, DELETE TABLE by myuser by ACCESS; - will audit 'myuser' Oracle user all insert, update, delete activities.


AUDIT SELECT TABLE BY myuser by ACCESS; - will audit selects issued by 'myuser' Oracle user.
AUDIT SELECT TABLE, INSERT TABLE, DELETE TABLE, UPDATE TABLE, EXECUTE PROCEDURE BY ACCESS WHENEVER NOT SUCCESSFUL; - will audit all unsuccessful SELECT, INSERT,
DELETE and UPDATE statements on all tables and unsuccessful uses of the EXECUTE PROCEDURE system privilege, by all database users, and by individual audited statement.
AUDIT SELECT TABLE, INSERT TABLE, DELETE TABLE, UPDATE TABLE, EXECUTE PROCEDURE by myuser BY ACCESS WHENEVER NOT SUCCESSFUL; - will audit all unsuccessful SELECT,
INSERT, DELETE and UPDATE statements on all tables and unsuccessful uses of the EXECUTE PROCEDURE system privilege, by <myuser> Oracle database user, and by individual audited statement.
AUDIT ALL BY myuser BY ACCESS; - will audit all activities of the 'myuser' Oracle user. This could generate a lot of audit trace information and could cause overhead.

NOAUDIT ALL; - remove all auditing.

After audit_trail functionality is enabled for some time, the audit_trail related rows will grow quickly in the SYSTEM tablespace, causing performance and/or lack of space issues. The DBA may want to delete audit trail records to free space and to facilitate audit trail management.

There are a few options to perform it:
DELETE FROM SYS.AUD$;
DELETE FROM SYS.AUD$ WHERE obj$name='SOME_OBJECT';
TRUNCATE TABLE SYS.AUD$;
(SYS.AUD$ is the only SYS object that should ever be directly modified.)

Example:
[oracle@localhost ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.1.0.6.0
Copyright (c) 1982, 2007, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> NOAUDIT ALL;
Noaudit succeeded.
SQL> TRUNCATE TABLE SYS.AUD$;
Table truncated.

Sunday 16 December 2012

Physical Database Limits in oracle 11g



Physical Database Limits

ItemType of LimitLimit Value
Database Block SizeMinimum2048 bytes; must be a multiple of operating system physical block size
Database Block SizeMaximumOperating system dependent; never more than 32 KB
Database BlocksMinimum in initial extent of a segment2 blocks
Database BlocksMaximum per datafilePlatform dependent; typically 222 - 1 blocks
ControlfilesNumber of control files1 minimum; 2 or more (on separate devices) strongly recommended
ControlfilesSize of a control fileDependent on operating system and database creation options; maximum of20,000 x (database block size)
Database filesMaximum per tablespaceOperating system dependent; usually 1022
Database filesMaximum per database65533
May be less on some operating systems
Limited also by size of database blocks and by the DB_FILES initialization parameter for a particular instance
Database extentsMaximum per dictionary managed tablespace4 GB * physical block size (with K/M modifier); 4 GB (without K/M modifier)
Database extentsMaximum per locally managed (uniform) tablespace2 GB * physical block size (with K/M modifier); 2 GB (without K/M modifier)
Database file sizeMaximumOperating system dependent. Limited by maximum operating system file size; typically 222 or 4 MB blocks
MAXEXTENTSDefault valueDerived from tablespace default storage or DB_BLOCK_SIZE initialization parameter
MAXEXTENTSMaximumUnlimited
Redo Log FilesMaximum number of logfilesLimited by value of MAXLOGFILES parameter in the CREATE DATABASEstatement
Control file can be resized to allow more entries; ultimately an operating system limit
Redo Log FilesMaximum number of logfiles per groupUnlimited
Redo Log File SizeMinimum size4 MB
Redo Log File SizeMaximum SizeOperating system limit; typically 2 GB
TablespacesMaximum number per database64 K
Number of tablespaces cannot exceed the number of database files because each tablespace must include at least one file
Bigfile TablespacesNumber of blocksA bigfile tablespace contains only one datafile or tempfile, which can contain up to approximately 4 billion ( 232 ) blocks. The maximum size of the single datafile or tempfile is 128 terabytes (TB) for a tablespace with 32 K blocks and 32 TB for a tablespace with 8 K blocks.
Smallfile (traditional) TablespacesNumber of blocksA smallfile tablespace is a traditional Oracle tablespace, which can contain 1022 datafiles or tempfiles, each of which can contain up to approximately 4 million (222) blocks.
External Tables fileMaximum sizeDependent on the operating system.
An external table can be composed of multiple files.

Tuesday 11 December 2012

ORA-00257: archiver error



ORA-00257: archiver error


ORA-16014: log 4 sequence# 4614 not archived, no available destinations
ORA-00312: online log 4 thread 1: '/u01/oracle/oracle/oradata//redo04a.log'



This is usually because the archive log drive has become full and needs to be cleared down. The first step is to make some more space on the archivelog drive. You can find out where you are archiving your logs to by looking at the init.ora or spfile and checking the log_archive_dest_n parameter. Normally log_archive_dest_1 is the local archiving location.

If you are having trouble actually logging on to the database to check the value for the archiving location just open up the SPFILE or INIT.ora file in a notepad and search for the parameter in there.

You also have the option to change the location of where the archivelogs are being written to, using the following command:

ALTER SYSTEM SET log_archive_dest_1 = 'LOCATION=/Disk2/oraclelogs/ArchLogs' SCOPE=both;
If you are having issues logging onto the DB check out the strategy below which should get you logged on if your normal way of logging on doesn't work.

Once you have cleared down some space for new archive logs, you will need to log on to the database and clear the archive logs. You may not be able to log in to the database as you normally would. That being the case, this time you need log on using the following sequence:

set oracle_sid = TESTDB
sqlplus /nolog
conn sys/ as sysdba
archive log all;
Once complete, you should see the archive logs being created on the archive log drive and operation of your database should continue as normal, hopefully... If not, check the alert log and see what other errors you have.

It should be noted that if you need to keep the archive logs and they haven't been backed up you should perhaps move them somewhere temporarily until you can be sure they have been backed up before you delete them.

Alternate solution

Connect with rman and delete some old archivelogs,


[oracle@ip*********** ~]$ rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Tue Dec 11 15:30:37 2012

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

connected to target database: ORCL1 (DBID=!!!!!!!!!!!!)

RMAN> delete noprompt archivelog all completed before 'sysdate-7';

RMAN>crosscheck ARCHIVELOG ALL;




Monday 10 December 2012

ORA-00376: file 13 cannot be read at this time



ORA-00376: file 3 cannot be read at this time


The other day going through my daily logs, I noticed after an unsuccessful disk move before the weekend, - seemed to have some issues.


ORA-00376: file 3 cannot be read at this time
ORA-01110: data file 3: "/my_path_to/ORACLE_SID/filename.dbf!

i logged myself onto LINUX at the same time to have a look inside v$datafile.

[] sqlplus / as sysdba

SQL> set pages 500

SQL> select * from v$datafile;

My interest in v$datafile is of course status, and status for file 3 was in RECOVER - so its a simple case right ? We need to recover the database.

[] sqlplus / as sysdba

SQL> RECOVER DATABASE;

ORA-00279: change 1306769 generated at 06/11/2007 11:57:12 needed for thread 1
ORA-00289: suggestion : "/my_path_to/ORACLE_SID/filename.arc"
ORA-00280: change 1306769 for thread 1 is in sequence #2245

SQL>

SQL>

sqlplus / as sysdba

SQL> startup mount;

SQL> alter database datafile "/my_path_to/ORACLE_SID/filename.dbs" offline;

SQL> recover datafile "/my_path_toil/ORACLE_SID/filename.dbs";

ORA-00279: change 1306769 generated at 06/11/2007 12:05:42 needed for thread 1
ORA-00289: suggestion : "/my_path_to/ORACLE_SID/filename.arc"
ORA-00280: change 1306769 for thread 1 is in sequence #2245

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

Log applied.
Media recovery complete.
SQL> alter database datafile "/my_path_to/ORACLE_SID/filename.dbs" online;

SQL> alter database open;
  


ORA-39700:


ORA-39700:

database must be opened with UPGRADE option
Cause:A normal database open was attempted, but the database has not been upgraded to the current server version.
Action:Use the UPGRADE option when opening the database to run catupgrd.sql (for database upgrade), or to run catalog.sql and catproc.sql (after initial database creation).

Tuesday 4 December 2012

ORA-01511: error in renaming log/data files ORA-01121: cannot rename data file – file is in use or recovery ORA-01110: data file : ‘datafile.dbf’



ORA-01511: error in renaming log/data files
ORA-01121: cannot rename 
data file – file is in use or recovery
ORA-01110: data file <string>: ‘datafile.dbf’


To Move the datafile or rename the datafile, follow this Steps And also related with the above mentioned Errors
1.    Login to SQLPlus.
2.    Connect as SYS DBA with 
CONNECT / AS SYSDBA 
3.    Shutdown database with
 SHUTDOWN 
4.    Rename or/and move the datafiles at operating system level.
5.    Start Oracle database in mount state with 
STARTUP MOUNT 
6.    Modify the name or location of datafiles in Oracle dictionary using following command syntax:
ALTER DATABASE RENAME FILE ‘<fully qualified path to original data file name>’ TO ‘<new or original fully qualified path to new or original data file name>’;
7.    Open Oracle database instance completely with 
ALTER DATABASE OPEN 
If the datafiles that need to be changed or moved do not belong to SYSTEM tablespaces, and do not contain active rollback segments or temporary segments, there is another workaround that does not require database instance to be shutdown. Instead, only the particular tablespace that contains the date files is taken offline.
1.    Login to SQLPlus.
2.    Connect as SYS DBA with 
CONNECT / AS SYSDBA .
3.    Make offline the affected tablespace with 
ALTER TABLESPACE <tablespace name> OFFLINE;
4.    Modify the name or location of datafiles in Oracle data dictionary using following  syntax:
ALTER TABLESPACE <tablespace name> RENAME DATAFILE ‘<fully qualified path to original data file name>’ TO ‘<new or original fully qualified path to new or original data file name>’;
5.    Bring the tablespace online again with
 ALTER TABLESPACE alter tablespace <tablespace name> ONLINE; 

Monday 3 December 2012

How to Drop Tablespace and Recover Database When Accidentally Delete Datafile





 You may get also some missing datafiles after database recovery process. In any case, missing a or a few datafiles will cause Oracle database to fail to startup, rendering the whole DB inaccessible. The solution to the missing datafiles is to drop the affected tablespace where has incomplete datafiles, and then recreate the tablespace and import the data into the tablespace from backup



1.    Run SQL*Plus.
2.    Connect to database as SYSDBA with this query:
CONNECT / AS SYSDBA
3.    Mount the database instead of starting it up:
STARTUP MOUNT;
4.    Issue the following command to bring the missing datafile offline so that Oracle won’t trying to connect and access the datafile anymore:
ALTER DATABASE DATAFILE ‘<datafile name with complete path>’ OFFLINE DROP;
Repeat the command for every datafiles that unaccounted for.
5.    Now start the database proper:
ALTER DATABASE OPEN;
6.    As the tablespace has damaged, drop it to recreate from fresh backup.
DROP TABLESPACE <tablespace name> INCLUDING CONTENTS;
7.    Ensure the other datafiles for the tablespace has been deleted, if not, remove them manually from the operating system.
=========================================================================
To determine and identify all datafiles that link to a tablespace, use the following query, with tablespace name in capital letter:
SELECT file_name, tablespace_name
FROM dba_data_files
WHERE tablespace_name =’<tablespace name>’;
If a tablespace contains multiple datafiles, and you just want to drop one or some of the datafiles and keep the remaining datafiles together with the objects and contents, the objects or data must be exported for the affected table space. Once exported, the tablespace can be dropped with above “DROP TABLESPACE” command. Then, recreate the tablespace with the datafile(s) required (that you initially want to keep), and then import the objects into the recreated tablespace.
If one or more datafiles is missing after a recovery process or accidental deletion, you can use ALTER DATABASE DATAFILE <datafile name> OFFLINE DROP command to make the datafile offline so that database can starts up after which the troubled tablespace can be dropped.
====================================================================



8.    Continue with the recovery process.


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