Oracle World: 1/4/09

Saturday, January 10, 2009

Recover database after missing online redo logs and all controlfiles.

This example is based on,
You have lost all your current and backup of controlfiles.
You have avaiable your current data files.
You have lost all your online active , current redo log files.
You have lost your spfile and pfile.

In the scenario, I will show the procedure of how to recover database if you lose your all copies of controlfiles and all copies of redo log groups and members are lost.

1.
Let's start by deleting online redo log files and controlfile of my running database.

SQL> select MEMBER from v$logfile;

MEMBER
--------------------------------------------------------------------------------
/oradata2/shaikdba/shaikdba/redo02.log
/oradata2/shaikdba/shaikdba/redo01.log
/oradata2/shaikdba/shaikdba/redo03.log

SQL> select name from v$controlfile;

NAME
--------------------------------------------------------------------------------
/oradata2/shaikdba/shaikdba/control01.ctl
/oradata2/shaikdba/shaikdba/control02.ctl
/oradata2/shaikdba/shaikdba/control03.ctl

2.Delete all copies of controlfiles and online redo log files. Here with one command I have deleted. But before deleting with one such command be conscious that this command will not delete other important files. You can delete files one by one.
SQL> !rm /oradata2/shaikdba/shaikdba/control0*

SQL> !rm /oradata2/shaikdba/shaikdba/redo0*

3.Now it is task to recover my database. This procedure will begin by creating a new controlfile. If you have previously issued ALTER DATABASE BACKUP CONTROLFILE TO TRACE then use that file and modify. If you don't have any backup of controlfile then you have to create a new one as below.
Note that as you have lost your online redo log files you have to specify RESETLOGS option.

-bash-3.00$ vi /oradata2/ctl.ctl
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "SHAIKDBA" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/oradata2/shaikdba/shaikdba/redo01.log' SIZE 50M,
GROUP 2 '/oradata2/shaikdba/shaikdba/redo02.log' SIZE 50M,
GROUP 3 '/oradata2/shaikdba/shaikdba/redo03.log' SIZE 50M
DATAFILE
'/oradata2/shaikdba/shaikdba/system01.dbf',
'/oradata2/shaikdba/shaikdba/undotbs01.dbf',
'/oradata2/shaikdba/shaikdba/sysaux01.dbf',
'/oradata2/shaikdba/shaikdba/users01.dbf'
CHARACTER SET WE8ISO8859P1
;
5.Save the script and run it inside SQL*plus.
SQL> shutdown abort
ORACLE instance shut down.

6. The recovery state I performed is to create an pfile.
-bash-3.00$ vi /oradata2/1.pfile
compatible=10.2.0.1.0
db_name=shaikdba
_allow_resetlogs_corruption = true
sga_target=400M
control_files='/oradata2/shaikdba/shaikdba/control01.ctl','/oradata2/shaikdba/shaikdba
/control02.ctl'

7.The next step is to create a controlfile.
-bash-3.00$ vi /oradata2/ctl.ctl
STARTUP NOMOUNT pfile='/oradata2/1.pfile'
CREATE CONTROLFILE REUSE DATABASE "SHAIKDBA" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/oradata2/shaikdba/shaikdba/redo01.log' SIZE 50M,
GROUP 2 '/oradata2/shaikdba/shaikdba/redo02.log' SIZE 50M,
GROUP 3 '/oradata2/shaikdba/shaikdba/redo03.log' SIZE 50M
DATAFILE
'/oradata2/shaikdba/shaikdba/system01.dbf',
'/oradata2/shaikdba/shaikdba/undotbs01.dbf',
'/oradata2/shaikdba/shaikdba/sysaux01.dbf',
'/oradata2/shaikdba/shaikdba/users01.dbf'
CHARACTER SET WE8ISO8859P1
;

8.Create controlfile and mount the database.
SQL> @/oradata2/ctl.ctl
ORACLE instance started.

Total System Global Area 419430400 bytes
Fixed Size 2021216 bytes
Variable Size 121637024 bytes
Database Buffers 289406976 bytes
Redo Buffers 6365184 bytes

Control file created.
--Recover database if necessary.This might necessary if you restore backup from a previous. Then recover database using backup controlfile until cancel and then --cancel

9.Normal Shutdown the database.
SQL> shut immediate;
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.

10.Start the database with the pfile.
SQL> startup pfile='/oradata2/1.pfile';
ORACLE instance started.

Total System Global Area 419430400 bytes
Fixed Size 2021216 bytes
Variable Size 121637024 bytes
Database Buffers 289406976 bytes
Redo Buffers 6365184 bytes
Database mounted.
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

11. Open the database with the resetlogs option.
SQL> alter database open resetlogs;
Database altered.

Note that once the database is opened, then we must immediately rebuild the database. Database rebuild means

1) perform a full-database export,
2) create a brand new and separate database, and finally
3) import the export dump.
Before you try this option, ensure that you have a good and valid backup of the current database.

This is necessary to rebuild because after _allow_resetlogs_corruption the dictionary information may be corrupted.

Recover database after missing online redo logs and all controlfiles.

If oracle unable to write Alertlog, Core Dump Or Tracefiles

In this I will show what can happen if Oracle is unable to write to the trace/alert.log to bdump or cdump & udump directories. It can happen if you loss your background_dump_dest or user_dump_dest or core_dump_dest ormay the the partition containing bdump or udump folder is full.

The answer is if oracle is unable to write to the trace/alert.log to bdump & udump directories then the oracle behavior depends on which process is attempting to write to alert.log/trace/coredump file. Based on the process the instance may or may not crash immediately.

If a foreground process corresponding to user process wants to write, but is unable to do so, the process may hang/terminate but there will not be any impact on database.

But if the background process wants to write it may hang and eventually crash the instance if that background process terminates.

In the following section I demonstrate this behavior on 10.2g

1.Start database with spfile Create one bdump directory
SQL> !mkdir /oradata2/bdump

2.Set the background_dump_dest to this location.
SQL> alter system set background_dump_dest='/oradata2/bdump';
System altered.

SQL> show parameter background_dump_dest
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
background_dump_dest string /oradata2/bdump

3.Switch logfile. It will generate one trace file in the background_dump_dest.
SQL> alter system switch logfile;
System altered.

SQL> !ls /oradata2/bdump
shaikdba_arc0_10688.trc

4.Move the bdump location to a new location. Here bdump_bak.
SQL> !mv /oradata2/bdump /oradata2/bdump_bak

SQL> !ls /oradata2/bdump
/oradata2/bdump: No such file or directory

5.Now you may face one of two scenario a) or b).

a)If database is open then Instance will crash
SQL> alter system switch logfile;
System altered.

SQL> alter system switch logfile;
alter system switch logfile
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel

b)If database is shutdown startup will fail with ORA-00444 and ORA-07446.
SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup
ORA-00444: background process "MMAN" failed while starting
ORA-07446: sdnfy: bad value '' for parameter .

So if you try to write alert log file while instance is up and running it may fail and eventually crush the instance.

However, for a foreground process the database will not crash, if it is unable to write to alert.log.

If oracle unable to write Alertlog, Core Dump Or Tracefiles

Database Startup fails with ORA-00444, ORA-07446

Problem Description
When I start my database my instace fails with ORA-00444 and ORA-07446 as follows.
SQL> startup
ORA-00444: background process "MMAN" failed while starting
ORA-07446: sdnfy: bad value '' for parameter .

Cause of The Problem
Database startup event is logged into alert log file. And alert log file is defined by the background_dump_dest parameter inside the initialization parameter. While starting up the instance if instance fail to write into the alert log file then instance crushes and fail to startup.

Solution of The Problem
There may be different scenario whenever oracle fails to write alert log contents. Suppose the partition reside alert log file is full. In that case delete/move unnecessary file from the alert log partition directory.

There may be the case the the directory defined by the background_dump_dest does not exist in the OS.

In both case you may also like to change the background_dump_dest location inside the pfile.

If you have spfile then,
1.create pfile='/oradata2/1.pfile' from spfile;
2.vi /oradata2/1.pfile
3.Modify the background_dump_dest to a valid location and in a patition where there is sufficient space available.
4.start the database isntance.
startup pfile= /oradata2/1.pfile';
5.create spfile again.
create spfile from pfile= /oradata2/1.pfile';

Database Startup fails with ORA-00444, ORA-07446

Create Read only user for a Schema

One thing you need to remember before read this post is there is no easy or shortcut way to make a read only user of another schema. Like grant select on username to another_username- there is no such single command like this. However you may have several alternatives to make read only user for a schema.

I will demonstrate the procedure with examples to make a read only user for a schema. In the example I will make devels user which will have read only permission on prod schema.
Let's start by creating PROD user.
SQL> CREATE USER PROD IDENTIFIED BY P;
User created.

SQL> GRANT DBA TO PROD;
Grant succeeded.

SQL> CONN PROD/P;
Connected.

SQL> CREATE TABLE PROD_TAB1 ( A NUMBER PRIMARY KEY, B NUMBER);
Table created.

SQL> INSERT INTO PROD_TAB1 VALUES(1,2);
1 row created.

SQL> CREATE TABLE PROD_TAB2(DATE_COL DATE);
Table created.

SQL> CREATE OR REPLACE TRIGGER PROD_TAB2_T AFTER INSERT ON PROD_TAB1
BEGIN
INSERT INTO PROD_TAB2 VALUES(SYSDATE);
END;
/
Trigger created.

SQL>CREATE VIEW A AS SELECT * FROM PROD_TAB2;
View created.

Method 1: Granting Privilege Manually

Step 1: Create devels user
SQL> CREATE USER DEVELS IDENTIFIED BY D;
User created.

Step 2: Grant only select session and create synonym privilege to devels user.
SQL> GRANT CREATE SESSION ,CREATE SYNONYM TO DEVELS;
Grant succeeded.

Step 3:Make script to grant select privilege.
$vi /oradata2/script.sql
SET PAGESIZE 0
SET LINESIZE 200
SET HEADING OFF
SET FEEDBACK OFF
SET ECHO OFF
SPOOL /oradata2/select_only_to_prod.sql
@@/oradata2/select_only_script.sql
SPOOL OFF

This script will run the /oradata2/select_only_script.sql and generate a output script /oradata2/select_only_to_prod.sql which need to be run in fact.

Step 4:
Prepare the /oradata2/select_only_script.sql script which will work as input for /oradata2/script.sql file.

$vi /oradata2/select_only_script.sql

SELECT 'GRANT SELECT ON PROD.' ||TABLE_NAME || ' TO DEVELS;' FROM DBA_TABLES WHERE OWNER='PROD';
SELECT 'GRANT SELECT ON PROD.' ||VIEW_NAME || ' TO DEVELS;' FROM DBA_VIEWS WHERE OWNER='PROD';

Step 5:
Now execute the /oradata2/script.sql which will in fact generate scipt /oradata2/select_only_to_prod.sql.
SQL> @/oradata2/script.sql

GRANT SELECT ON PROD.PROD_TAB1 TO DEVELS;
GRANT SELECT ON PROD.PROD_TAB2 TO DEVELS;

Step 6:
Execute the output script select_only_to_prod.sql which will be used to grant read only permission of devels user to prod schema.
SQL> @/oradata2/select_only_to_prod.sql

Step 7:
Log on devels user and create synonym so that the devels user can access prod's table without any dot(.). Like to access prod_tab2 of prod schema he need to write prod.prod_tab2. But after creating synonym he simply can use prod_tab2 to access devels table and views.
To create synonym do the following,

SQL>CONN DEVELS/D;

SQL>host vi /oradata2/script_synonym.sql

SET PAGESIZE 0
SET LINESIZE 200
SET HEADING OFF
SET FEEDBACK OFF
SET ECHO OFF
SPOOL /oradata2/synonym_to_prod.sql
@@/oradata2/synonym_script.sql
SPOOL OFF

SQL>host vi /oradata2/synonym_script.sql
SELECT 'CREATE SYNONYM ' ||TABLE_NAME|| ' FOR PROD.' ||TABLE_NAME||';' FROM ALL_TABLES WHERE OWNER='PROD';
SELECT 'CREATE SYNONYM ' ||VIEW_NAME|| ' FOR PROD.' ||VIEW_NAME||';' FROM ALL_VIEWS WHERE OWNER='PROD';
SQL>@/oradata2/script_synonym.sql
SQL>@/oradata2/synonym_to_prod.sql

Step 8: At this stage you have completed your job. Log on as devels schema and see,
SQL> select * from prod_tab1;
1 2

SQL> show user
USER is "DEVELS"

Only select privilege is there. So DML will throw error. Like,

SQL> insert into prod_tab1 values(4,3);
insert into prod_tab1 values(4,3)
*
ERROR at line 1:
ORA-01031: insufficient privileges

Method 2: Writing PL/SQL Code
This is script for table :

set serveroutput on
DECLARE
sql_txt VARCHAR2(300);
CURSOR tables_cur IS
SELECT table_name FROM dba_tables where owner='PROD';
BEGIN
dbms_output.enable(10000000);
FOR tables IN tables_cur LOOP
sql_txt:='GRANT SELECT ON PROD.'||tables.table_name||' TO devels';
execute immediate sql_txt;
END LOOP;
END;
/

This is the script for grant select permission for views.

DECLARE
sql_txt VARCHAR2(300);
CURSOR tables_cur IS
SELECT view_name FROM dba_views where owner='PROD';
BEGIN dbms_output.enable(10000000);
FOR tables IN tables_cur LOOP
sql_txt:='GRANT SELECT ON PROD.'||tables.view_name||' TO devels';
--dbms_output.put_line(sql_txt);
execute immediate sql_txt;
END LOOP;
END;
/

To create synonym on prod schema,
Log on as devels and execute the following procedure.

SQL>CONN DEVELS/D
SQL>
DECLARE
sql_txt VARCHAR2(300);
CURSOR syn_cur IS
SELECT table_name name FROM all_tables where owner='PROD'
UNION SELECT VIEW_NAME name from all_views where owner='PROD' ;
BEGIN dbms_output.enable(10000000);
FOR syn IN syn_cur LOOP
sql_txt:='CREATE SYNONYM '||syn.name|| ' FOR PROD.'||syn.name ;
dbms_output.put_line(sql_txt);
execute immediate sql_txt;
END LOOP;
END;
/

Method 3: Writing a Trigger

After granting select permission in either of two ways above you can avoid creating synonym by simply creating a trigger.

Create a log on trigger that eventually set current_schema to prod just after log in DEVELS user.

create or replace trigger log_on_after_devels
after logon ON DEVELS.SCHEMA
BEGIN
EXECUTE IMMEDIATE 'alter session set CURRENT_SCHEMA = prod';
END;
/

Create Read only user for a Schema

SELECT 'GRANT SELECT ON PROD.' ||TABLE_NAME || ' TO DEVELS;' FROM DBA_TABLES WHERE OWNER='PROD';
SELECT 'GRANT SELECT ON PROD.' ||VIEW_NAME || ' TO DEVELS;' FROM DBA_VIEWS WHERE OWNER='PROD';

Step 5:
Now execute the /oradata2/script.sql which will in fact generate scipt /oradata2/select_only_to_prod.sql.
SQL> @/oradata2/script.sql

GRANT SELECT ON PROD.PROD_TAB1 TO DEVELS;
GRANT SELECT ON PROD.PROD_TAB2 TO DEVELS;

SET PAGESIZE 0
SET LINESIZE 200
SET HEADING OFF
SET FEEDBACK OFF
SET ECHO OFF
SPOOL /oradata2/synonym_to_prod.sql
@@/oradata2/synonym_script.sql
SPOOL OFF

set serveroutput on
DECLARE
sql_txt VARCHAR2(300);
CURSOR tables_cur IS
SELECT table_name FROM dba_tables where owner='PROD';
BEGIN
dbms_output.enable(10000000);
FOR tables IN tables_cur LOOP
sql_txt:='GRANT SELECT ON PROD.'||tables.table_name||' TO devels';
execute immediate sql_txt;
END LOOP;
END;
/

This is the script for grant select permission for views.

DECLARE
sql_txt VARCHAR2(300);
CURSOR tables_cur IS
SELECT view_name FROM dba_views where owner='PROD';
BEGIN dbms_output.enable(10000000);
FOR tables IN tables_cur LOOP
sql_txt:='GRANT SELECT ON PROD.'||tables.view_name||' TO devels';
--dbms_output.put_line(sql_txt);
execute immediate sql_txt;
END LOOP;
END;
/

To create synonym on prod schema,
Log on as devels and execute the following procedure.

SQL>CONN DEVELS/D
SQL>
DECLARE
sql_txt VARCHAR2(300);
CURSOR syn_cur IS
SELECT table_name name FROM all_tables where owner='PROD'
UNION SELECT VIEW_NAME name from all_views where owner='PROD' ;
BEGIN dbms_output.enable(10000000);
FOR syn IN syn_cur LOOP
sql_txt:='CREATE SYNONYM '||syn.name|| ' FOR PROD.'||syn.name ;
dbms_output.put_line(sql_txt);
execute immediate sql_txt;
END LOOP;
END;
/

Restrict DDL on a Schema

DBA wants that in the database certain user will not be able structural change. That is no DDL operation can be performed by the user. In that case DBA can achieve his goal simply by making a trigger on the schema.

Suppose we want user faruk will not be able to perform any DDL. Then create trigger as below.

SQL> conn shaik/abdul
Connected.

SQL> create table before_trigger(a number);
Table created.

SQL>conn system/manager
Connected.

SQL> CREATE OR REPLACE
2 TRIGGER BEFORE_DDL_SHAIK
3 BEFORE DDL
4 ON SHAIK.SCHEMA
5 BEGIN
6 RAISE_APPLICATION_ERROR(-30900,'DDL Operation is not Permitted.' );
7 END;
8 /
Trigger created.

SQL> conn shaik/abdul
Connected.

SQL> create table after_trigger(a number);
create table after_trigger(a number)
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-21000: error number argument to raise_application_error of -30900 is out of
range
ORA-06512: at line 2

Restrict DDL on a Schema

How to find Top Sql Queries

If someone ask for top sql they from enterprise manager he can find some expensive queries. But the result may vary based on the metrics or criteria of expensive. Measurement may vary on,
a) Logical IO's per execution?
b) Physical IO's per execution?
c) Cpu usage?
d) Based on number of parse calls?
e) Elapsed time used?
f) Number of executions?
g) Size consumed in shared pool?
h) Number of child versions found?
i) Based on Wait time?

However from DBA_HIST_SQLSTAT we can get a complete picture of historical SQL statistics.
AWR does a pretty job. Between two snapshot it displays the top sql statements in the enterprise manager. We can also see it from sql queries.

In fact Enterprise Manager displays information of SQL text from DBA_HIST_SQLTEXT view which captures information from V$SQL and is used with the DBA_HIST_SQLSTAT view.

Along with the DBA_HIST_SQLSTAT and DBA_HIST_SQLSTAT we can use the view DBA_HIST_SNAPSHOT in order to specify the range of snapshots in between analysis will be done.

We can specify the BEGIN_INTERVAL_TIME and END_INTERVAL_TIME column of the view DBA_HIST_SNAPSHOT instead of specifying SNAP_ID if we are determined to calculate the top sql query between date range.

From the combination of the three above views here is one query while calculates the top sql from date 09/01/2008 to 09/09/2008 based on the CPU time.

SELECT SQL_TEXT,X.CPU_TIME FROM DBA_HIST_SQLTEXT DHST,
(SELECT DHSS.SQL_ID SQL_ID,SUM(DHSS.CPU_TIME_DELTA) CPU_TIME
FROM DBA_HIST_SQLSTAT DHSS
WHERE DHSS.SNAP_ID IN(SELECT SNAP_ID FROM DBA_HIST_SNAPSHOT
WHERE BEGIN_INTERVAL_TIME>=TO_DATE('09/01/2008','MM/DD/YYYY')
AND END_INTERVAL_TIME<=TO_DATE('09/09/2008','MM/DD/YYYY'))
GROUP BY DHSS.SQL_ID) X WHERE X.SQL_ID=DHST.SQL_ID
ORDER BY X.CPU_TIME DESC;

How to find Top Sql Queries

ORA-01295: DB_ID mismatch between dictionary USE_ONLINE_CATALOG and logfiles

Problem Description
While doing remote mining in the mining database through logminer START_LOGMNR procedure fails with ORA-01295. May be you want to analysis redo log files of the production database in the testing environment.
SQL> BEGIN
DBMS_LOGMNR.START_LOGMNR
(options =>
dbms_logmnr.dict_from_online_catalog);
END;
/ 2 3 4 5 6
BEGIN
*
ERROR at line 1:
ORA-01295: DB_ID mismatch between dictionary USE_ONLINE_CATALOG and logfiles
ORA-06512: at "SYS.DBMS_LOGMNR", line 58
ORA-06512: at line 2

Cause of The Problem
The error occurs because the dictionary file produced by a database (testing) is different from the one that produced the logfiles (production). In order to use logminer you need to extract dictionary information from the source database(production) to the mining database(testing).

Solution of The Problem

Step 1.
Extract the dictionary to the redo logs in the production database by executing following command,
SQL> EXECUTE DBMS_LOGMNR_D.BUILD(OPTIONS=> DBMS_LOGMNR_D.STORE_IN_REDO_LOGS);

Step 2
After executing commnad in step 1 the dictionary is extract into the redo logs and in fact they become archived instantly. Then check in which redo logs the dictionary is located by running this on production,
SQL> SELECT NAME FROM V$ARCHIVED_LOG WHERE DICTIONARY_BEGIN='YES';
SQL> SELECT NAME FROM V$ARCHIVED_LOG WHERE DICTIONARY_END='YES';

Step 3
Tranfer the output of step 3 into the testing database. You can use scp or telnet to do this.

Step 4
Now in the mining database(testing) provide all the redo log files (including the one from which the transactions need to be mined) and start Log Miner (do not specify dictFileName):

SQL> EXECUTE DBMS_LOGMNR.START_LOGMNR(OPTIONS => DBMS_LOGMNR.DICT_FROM_REDO_LOGS);

ORA-01295: DB_ID mismatch between dictionary USE_ONLINE_CATALOG and logfiles

ORA-39212: installation error: XSL stylesheets not loaded correctly

Problem Description
SQL> select dbms_metadata.get_ddl('TABLE','TEST_EXT') from dual;
ERROR:
ORA-39212: installation error: XSL stylesheets not loaded correctly
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79
ORA-06512: at "SYS.DBMS_METADATA_INT", line 7398
ORA-06512: at "SYS.DBMS_METADATA_INT", line 7447
ORA-06512: at "SYS.DBMS_METADATA_INT", line 9453
ORA-06512: at "SYS.DBMS_METADATA", line 1919
ORA-06512: at "SYS.DBMS_METADATA", line 2792
ORA-06512: at "SYS.DBMS_METADATA", line 4333
ORA-06512: at line 1

no rows selected

Cause of The Problem
The XSL stylesheets used by the Data Pump Metadata API were not loaded correctly into the Oracle dictionary table "sys.metastylesheet."

It may be the cause that the stylesheets were not loaded at all in the database, or they were not converted to the database character set.

Suppose if you convert your database character set using ALTER DATABASE statement then likely you will face this problem.

Solution of The Problem
Note that never use ALTER DATABASE statement to migrate your characterset.

1)Connect as sysdba.
SQL> conn / as sysdba
Connected.

2)Execute dbms_metadata_util.load_stylesheets to reload the stylesheets.
SQL> exec dbms_metadata_util.load_stylesheets;
PL/SQL procedure successfully completed.

3)Exit and run the above query,
SQL>exit
$>conn SHAIK/ABDUL
SQL>select dbms_metadata.get_ddl('TABLE','TEST_EXT') from dual;

Restore fails with ORA-19870,ORA-19587,ORA-27091,ORA-27067

Problem Symptoms
From the backuppiece restore spfile commnad fails with ORA-19870,ORA-19587,ORA-27091,ORA-27067
RMAN> restore spfile from '/backup1/snap/june/8rji9vrq_1_1';

Starting restore at 27-AUG-08
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=36 devtype=DISK

channel ORA_DISK_1: autobackup found: /backup1/snap/june/8rji9vrq_1_1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 08/27/2008 01:05:07
ORA-19870: error reading backup piece /backup1/snap/june/8rji9vrq_1_1
ORA-19587: error occurred reading 0 bytes at block number 1
ORA-27091: unable to queue I/O
ORA-27067: size of I/O buffer is invalid
Additional information: 2

Cause of the Problem
RMAN could not read the backuppiece. There may be several reasons behind it. Either it is not the file what is expected in the RMAN command. Suppose my command was restore spfile and this backuppiece may be backups of archived logs. Another reason may be the file in OS level is made read only status. As RMAN opens the backuppiece in read write mode so error may come.

Solution of the Problem
1)Be sure the command you are using is valid one in terms of backuppiece. Suppose here I used restore spfile then this piece must be backup of controlfile.

2)Make sure that backuppiece is read write mode on OS. You can check it on unix by,
$ls -l backupiece_name
In order to make read write mode.
$chmod 666 backupiece_name

ORA-39212: installation error: XSL stylesheets not loaded correctly

Restore fails with ORA-19870,ORA-19587,ORA-27091,ORA-27067

How to restore an Spfile from autobackup older than 7 days

Whenever we issue in RMAN, RMAN> restore spfile from autobackup; then restoring from autobackup stops at seven days and then it arises message channel ORA_DISK_1: no autobackup in 7 days found. For example,

RMAN> restore spfile from autobackup;
Starting restore at 31-AUG-08
using channel ORA_DISK_1

channel ORA_DISK_1: looking for autobackup on day: 20080831
channel ORA_DISK_1: looking for autobackup on day: 20080830
channel ORA_DISK_1: looking for autobackup on day: 20080829
channel ORA_DISK_1: looking for autobackup on day: 20080828
channel ORA_DISK_1: looking for autobackup on day: 20080827
channel ORA_DISK_1: looking for autobackup on day: 20080826
channel ORA_DISK_1: looking for autobackup on day: 20080825
channel ORA_DISK_1: no autobackup in 7 days found
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 08/31/2008 01:31:12
RMAN-06172: no autobackup found or specified handle is not a valid copy or piece

Whenever you give RMAN to RESTORE SPFILE FROM AUTOBACKUP or RESTORE CONTROLFILE FROM AUTOBACKUP, RMAN begins the search on the current day or on the day specified with the SET UNTIL caluse. If no autobackup is found in the current or SET UNTIL day, RMAN checks the previous day. In this way RMAN by default checks for 7 days autobackup from the current or SET UNTIL day.

However if you want to extend your searching of autobackup more than 7 then you have to use MAXDAYS option with the RESTORE command.

For example,
RMAN>restore spfile from autobackup maxdays 30;
or
RMAN>restore controlfile from autobackup maxdays 30;

In these cases autobackup searches will be performed up to 30 days from the current or SET UNTIL day.

RMAN-06172: no autobackup found

Error Description:
While performing disaster recovery I get the error RMAN-06172 as below.

RMAN> restore spfile to pfile '/oracle/app/oracle/product/10.2.0/db_1/dbs/initSHAIK.ora' from '/backup1/snap/june/ctl_sp_bak_c-448149146-20080607-00';

Starting restore at 27-AUG-08
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=36 devtype=DISK

RMAN-00571: ====================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ====================================================
RMAN-03002: failure of restore command at 08/27/2008 00:42:41
RMAN-06172: no autobackup found or specified handle is not a valid copy or piece

Cause of The Error
To perform restore operation to a new host you must have at least one autobackup of the controlfile. If you don't have autobackup of the controlfile then you would not be able to perform disaster recovery. For example if you have snapshot of controlfile backup then you will not be able to restore spfile or controlfile.

Solution of The Problem
1)To perform a disaster recovery, the minimum required set of backups is backups of some datafiles, some archived redo logs generated after the time of the backup, and at least one autobackup of the control file. So find autobackup of the controlfile and use it for restore operation.

Also ensure that you have autobackup intact and your autobackup is not corrupted. If you have corrupted backup then you will get above error.

My autobackup piece is /export/home/oracle/8rji9vrq_1_1 and so I used,
RMAN> restore spfile from '/export/home/oracle/8rji9vrq_1_1';

2)In situation you have autobackup of controlfile and it is not corrupted then still you can get the error. It is because of platform difference in endian format. It was because of BIG endian or LITTLE endian format. If you take backup in BIG endian platform and want to restore it to LITTLE endian format then above error will appear. You can check the endian format by below query.


SQL> set linesize 100
SQL> col PLATFORM_NAME format a80
SQL> select PLATFORM_NAME, ENDIAN_FORMAT from V$TRANSPORTABLE_PLATFORM;

PLATFORM_NAME                                                                    ENDIAN_FORMAT
-------------------------------------------------------------------------------- --------------
Solaris[tm] OE (32-bit)                                                          Big
Solaris[tm] OE (64-bit)                                                          Big
Microsoft Windows IA (32-bit)                                                    Little
Linux IA (32-bit)                                                                Little
AIX-Based Systems (64-bit)                                                       Big
HP-UX (64-bit)                                                                   Big
HP Tru64 UNIX                                                                    Little
HP-UX IA (64-bit)                                                                Big
Linux IA (64-bit)                                                                Little
HP Open VMS                                                                      Little
Microsoft Windows IA (64-bit)                                                    Little
IBM zSeries Based Linux                                                          Big
Linux 64-bit for AMD                                                             Little
Apple Mac OS                                                                     Big
Microsoft Windows 64-bit for AMD                                                 Little
Solaris Operating System (x86)                                                   Little
IBM Power Based Linux                                                            Big

17 rows selected.

We see Solaris[tm] OE (32-bit) or Solaris[tm] OE (32-bit) has big endian format but Solaris Operating System (x86) has little endian format. So we can't perform any restore operation in Microsoft Windows 64-bit for AMD or Solaris Operating System (x86) or Microsoft Windows IA (32-bit) (All these has Little endian format) from backup that was taken from Big endian format like Solaris[tm] OE (32-bit) or Solaris[tm] OE (32-bit) or IBM Power Based Linux or IBM zSeries Based Linux or HP-UX (64-bit) or AIX-Based Systems (64-bit).

If you try to restore in Little edian format, from backup taken in Big endian format platform RMAN can't read the backuppiece and will return,
RMAN-00571: ====================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ====================================================
RMAN-03002: failure of restore command at 08/27/2008 00:42:41
RMAN-06172: no autobackup found or specified handle is not a valid copy or piece

The solution is to use same Indian format.

How to restore an Spfile from autobackup older than 7 days

RMAN-06172: no autobackup found


SQL> set linesize 100
SQL> col PLATFORM_NAME format a80
SQL> select PLATFORM_NAME, ENDIAN_FORMAT from V$TRANSPORTABLE_PLATFORM;

PLATFORM_NAME                                                                    ENDIAN_FORMAT
-------------------------------------------------------------------------------- --------------
Solaris[tm] OE (32-bit)                                                          Big
Solaris[tm] OE (64-bit)                                                          Big
Microsoft Windows IA (32-bit)                                                    Little
Linux IA (32-bit)                                                                Little
AIX-Based Systems (64-bit)                                                       Big
HP-UX (64-bit)                                                                   Big
HP Tru64 UNIX                                                                    Little
HP-UX IA (64-bit)                                                                Big
Linux IA (64-bit)                                                                Little
HP Open VMS                                                                      Little
Microsoft Windows IA (64-bit)                                                    Little
IBM zSeries Based Linux                                                          Big
Linux 64-bit for AMD                                                             Little
Apple Mac OS                                                                     Big
Microsoft Windows 64-bit for AMD                                                 Little
Solaris Operating System (x86)                                                   Little
IBM Power Based Linux                                                            Big

17 rows selected.

ORA-09925: Unable to create audit trail file

Problem Description
Whenever you try to startup the database with pfile then it fails with error RA-09925: Unable to create audit trail file along with Linux Error: 2: No such file or directory.

RMAN> startup force pfile='/oradata2/shaikdbapfile.ora';

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of startup command at 08/28/2008 15:02:29
RMAN-04014: startup failed: ORA-09925: Unable to create audit trail file
Linux Error: 2: No such file or directory
Additional information: 9925

Cause of the Problem
The error ORA-09925 indicates that oracle was unable to write the audit information into the audit directory. The audit directory is specified by audit_trail parameter in the initialization parameter. Just after the ORA-09925 if there is some OS error then that is notified. The linux error clearly identified that which is No such file or directory.

Solution of the Problem
There may be several solutions to this problem.
1.Create the approprite directory/location specified by audit_trail initialization parameter from operating system and grant appropriate permission to that directory.

2.Change the location of audit_trail initialization parameter to a valid location in the OS.

If you start your database with pfile then you can easily do this by editing your pfile with an editor and modify the audit_trail parameter.

However if you start your database with an spfile then,
-Create pfile='1.ora' from spfile;
-Edit pfile 1.ora and the location of audit_trail parameter.
-Create spfile from pfile='1.ora'
-Start your database with the spfile.

Message file RMAN.msb not found

Error Description
I give the full path of rman executable file location and I am getting error RMAN<>.msb not found as below.
-bash-3.1$ /oradata2/bin/rman target /
Message file RMAN<>.msb not found

Verify that ORACLE_HOME is set properly

Solution of The problem
Believe me, as I still got you have not set ORACLE_HOME properly. So set it.

To know your current settings of ORACLE_HOME, issue,
-bash-3.1$ echo $ORACLE_HOME
/oradata2/bin/

Here we see it is set, it may either unset. Though it is set wrong. The ORACLE_HOME path is before the bin directory. So here ORACLE_HOME will be /oradata2 instead of /oradata2/bin/

On unix set the value for the current session by,
-bash-3.1$ export ORACLE_HOME=/oradata2/

On Windows environment you have to set by
set ORACLE_HOME=C:\oracle or like that.

In order to set it permanently edit your profile. On linux like, ~/.bash_profile or on unix edit the file .profile on home directory and make an entry of ORACLE_HOME.

After setting correct ORACLE_HOME now try to connect to rman.
-bash-3.1$ /oradata2/bin/rman target /

Recovery Manager: Release 10.2.0.1.0 - Production on Thu Aug 28 14:37:53 2008

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

connected to target database (not started)

ORA-09925: Unable to create audit trail file

Message file RMAN.msb not found

RMAN-04014: startup failed: ORA-07446: sdnfy: bad value

Problem Description
While performing disaster recovery to a new host, you need to start the database in nomount sate(in fact to restore control file) but database could not start in nomount state because it fails with RMAN-04014, ORA-07446.

RMAN> startup force pfile='/oradata2/shaikdbapfile.ora';

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of startup command at 08/28/2008 15:03:44
RMAN-04014: startup failed: ORA-07446: sdnfy: bad value '/oracle/app/oracle/product/10.2.0/db_1/admin/shaikdba/udump' for parameter user_dump_dest.

Cause of The Problem
The path /oracle/app/oracle/product/10.2.0/db_1/admin/shaikdba/udump as shown in output does not exist. Oracle itself does not create any path if a path does not exist. So, you have to change the value of user_dump_dest in the initialize parameter.

Solution of The Problem
If you use pfile to start your database then edit the pfile with any editor (for example vi on unix) and either change the location of user_dump_dest or remove the parameter user_dump_dest from pfile. And then perform startup.

If you like to use spfile then first create pfile from spfile, then edit the pfile and then create spfile from the pfile, like.

1.Create pfile from spfile.
SQL> create pfile='1.pfile' from spfile='/oracle/app/oracle/product/10.2.0/db_1/dbs/spfileshaik.ora';

2.Edit the pfile.
SQL>!vi 1.pfile

Remove the entry of user_dump_dest or add valid entry.

3.Create spfile from pfile.
SQL>create spfile from pfile='1.pfile';

4.start the database.
SQL>STARTUP NOMOUNT

RMAN-04014: startup failed: ORA-07446: sdnfy: bad value

Startup fails with ORA-01261: Parameter db_recovery_file_dest destination string cannot be translated

Problem Description
While I start my database with my initialization parameter it fails with oracle error ORA-01261 and ORA-01262 as below:
RMAN> startup force pfile='/oradata2/shaikdbapfile.ora';

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of startup command at 08/28/2008 15:04:27
RMAN-04014: startup failed: ORA-01261: Parameter db_recovery_file_dest destination string cannot be translated
ORA-01262: Stat failed on a file destination directory
Linux Error: 2: No such file or directory

Cause of The Problem
The linux error No such file or directory indicates all what you need to do. It may be the case that,

-You might not start your database with proper or recently updated pfile/spfile. May be you changed your location of your db_recovery_file_dest and that initialization parameter that you used to startup the database has not reflected yet.

-The directory location specified by the db_recovery_file_dest parameter is not on your system.
Solution of The Problem
If you start your database with pfile edit it and change the location of db_recovery_file_dest parameter to a valid one. Be sure oracle has proper permission on the directory from OS.

However if you start your database with an spfile then,
-Create pfile='1.ora' from spfile;
-Edit pfile 1.ora and change the location of db_recovery_file_dest parameter.
-Create spfile from pfile='1.ora';
-Start your database.

Startup fails with ORA-01261: Parameter db_recovery_file_dest destination string cannot be translated

Friday, January 9, 2009

How to Resize a Datafile

There may be situations when you need to increase or decrease your datafile size. Prior to oracle 7.2 there was no easy way to resize the datafile. Before 7.2 the solutions was to drop and recreate the tablespace with different sized datafiles, or to add more datafiles to a tablespace whenever you need more space in the tablespace.

Before 7.2 the RESIZE command will raise error,
ORA-00923: FROM keyword not found where expected

Before entering into resize datafile let's be familiar with several views related to datafile.
From V$DATAFILE.CREATION_TIME we can see the timestamp of the datafile creation time.
From V$DATAFILE.BYTES we can see the current datafile size in bytes. 0 in this fields indicate the datafile is inaccessible.
From V$DATAFILE.CREATE_BYTES we can the datafile size when it was created.
From V$DBA_DATA_FILES.MAXBYTES we can see the maximum size of the datafile.

Before going into resize I just create one tablespace containing one datafile of size 10M which can be extended up to 100M.

SQL> create tablespace test_tbs datafile 'E:\ORACLE\PRODUCT\10.2.0\ORADATA\SHAIK\test_tbs01.dbf' size 10M autoextend on maxsize 100M;
Tablespace created.

Now have a look at the current size of maximum size of this data file from dba_data_files view.
SQL> select bytes/1024/1024, maxbytes/1024/1024 from dba_data_files where file_id=6;
BYTES/1024/1024 MAXBYTES/1024/1024
--------------- ------------------
10 100

Increase datafile size
To see the current settings of the datafile query from v$datafile view. The BYTES column shows the current size of the datafile, and the CREATE_BYTES column shows what the size was
specified when the file was created.

Current size is 10M we can increase it upto 15M by,

SQL> alter database datafile 6 resize 15M;
Database altered.

SQL> select bytes/1024/1024, maxbytes/1024/1024 from dba_data_files where file_id=6;

BYTES/1024/1024 MAXBYTES/1024/1024
--------------- ------------------
15 100

SQL> select bytes/1024/1024, create_bytes/1024/1024 from v$datafile where file#=6;
BYTES/1024/1024 CREATE_BYTES/1024/1024
--------------- ----------------------
15 10

Here 12 is current size and 10 is creation time size.

Decrease Datafile size
Downsizing a datafile is more complicated than increasing the size of a datafile. Because you cannot deallocate space from a datafile that is currently being used by database objects. To remove space from a datafile, you have to have contiguous space at the END of the datafile. From DBA_FREE_SPACE we can see the free space in the datafile.

To resize our datafile to 2M issue,
SQL> alter database datafile 6 resize 2M;

If you try to resize a datafile to a size smaller than is needed to contain all the database objects in that datafile, the following error is returned,

ORA-03297: file contains blocks of data beyond requested RESIZE value
Or
ORA-03297: file contains used data beyond requested RESIZE value

CRS Stack Fails to Start After Reboot ORA-29702 CRS-0184

Problem Description
An attempt to manually start the instance fails with,

ORA-29702: error occurred in Cluster Group Service operation

crs_stat produces the error,

/oracle/crs/bin/crs_stat -t
CRS-0184: Cannot communicate with the CRS daemon.

CSS stack did not come up as the following command issued out of init.cssd
crsctl check boot
failed with error message
Failed 3 to bind listening endpoint: (ADDRESS=(PROTOCOL=tcp)(HOST=node2priv))
and return code 11.
The error message was also written to /var/log/messages on this node.

Note that in 10gR2 the message no longer gets written to /var/log/messages, instead it will be written to /tmp/crsctl.

Cause of the Problem
Error message 'Failed 3 to bind listening endpoint: (ADDRESS=(PROTOCOL=tcp)(HOST=node2priv))' indicated that CSS had a problem creating the listening endpoint for hostname node2

In node1 within the /etc/hosts file the name of the private interconnect exist. But on node2 somehow it is missing the private interconnect in the /etc/hosts file. That's why the problem occurs.

Solution of The Problem

1. Add the missing hostnames to /etc/hosts

2. Stop and start CRS stack

MAXDATAFILES , DB_FILES parameters and ORA-00059

Problem Description
Whenever I try to create tablespace it fails with error message, ORA-00059.

SQL> create tablespace test_tbs;
create tablespace test_tbs
*
ERROR at line 1:
ORA-00059: maximum number of DB_FILES exceeded

Cause of The Problem
You have reached the limit of DB_FILES parameter. Before entering into solution part let's have an idea about DB_FILES and MAXDATAFILES parameter.

The DB_FILES parameter limits the maximum number of datafile can exist in oracle database. We can't change this parameter dynamically. We have to change it spfile by using ALTER SYSTEM .... SCOPE=SPFILE or in the pfile.

And the MAXDATAFILES parameter you can find with the CREATE DATABASE command or in CREATE CONTROLFILE command. It is also a limitation of maximum number of datafiles can be in the datafile. But starting from oracle 8 this hard limit parameter can be easily expanded up to DB_FILES parameter. So, if you attempt to add a new file whose number is greater than MAXDATAFILES, but less than or equal to DB_FILES, the MAXDATAFILES parameter of the control file will expand automatically to accommodate more files.

So, if you are after oracle 8i then we should just forget about MAXDATAFILES parameter. We should rather think about DB_FILES parameter.

Solution of The Problem
If you use pfile to startup the database then edit pfile and add/modify the DB_FILES parameter to a greater value so that it can accommodate higher number of datafiles. Then start the database using that pfile.

If you use spfile to startup the database then issue,

SQL> alter system set db_files=300 scope=spfile;
System altered.

Then start your database and either create tablespace or add datafile to an existing tablespace. Hopefully it will work.

SQL> conn / as sysdba
SQL> startup force
SQL> CREATE TABLESPACE or ALTER TABLESPACE command to add datafile.

Saturday, January 10, 2009

Friday, January 9, 2009

Blog Archive