Connection fails with server unexpectedly closed network connection

The scenario is whenever with any third party software like putty using ssh I try to connect to my unix server through windows machine it fails with message "Server unexpectedly closed network connection"

Solution of The Problem
In my machine I got this problem because of the permission setting of my unix server in the /etc/ssh directory. Someone change the permission of this directory to 777. So it was readable for all and everyone could have access to the secure shell private key. And for this reason ssh could not allow access to the server because of security reason.

The solution of this problem is to change the permission of the /etc/ssh and start the sshd service. To do this,

1)Log on as root user.
$su -root

2)Change permission recursively 600 on /etc/ssh
#chmod -R 600 /etc/ssh

3)Start the ssh daemon.
#service sshd start
or,
#service sshd restart

How to find Top Sql through 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;

Related Documents

http://arjudba.blogspot.com/2008/08/how-to-invoke-collect-and-run-awr-and.html
http://arjudba.blogspot.com/2008/08/difference-or-advantage-between-awr-and.html

http://arjudba.blogspot.com/2008/06/ways-of-identify-high-load-ot-top-sql.html

Restrict DDL on a Schema

Suppose 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 faruk/faruk
Connected.

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

SQL>conn system/a
Connected.

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

SQL> conn faruk/faruk
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

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;
/


Related Documents

Drop User in Oracle

ORA-01940: Cannot drop a user that is currently connected

Create user in oracle

A user can do work in his schema with only Create Session Privilege.

In EM connection fails with ERROR: NMO not setuid-root (Unix-only)

Error Description
This problem is notified on Unix system only.
Whenever you try to connect to enterprise manager after giving host username and password as prompted it fails with message
HostPassword - Connection to host as user oracle failed: ERROR: NMO not setuid-root (Unix-only)

Cause of The Problem
There is some permission issue on the enterprise manager binary file. The reason is after installing oracle software you have not run root.sh

Solution of The Problem
1.Stop the dbconsole service
$emctl stop dbconsole
If you face this on grid control then stop the Grid Management Agent
$emctl stop agent

2.Log on as root user and run the root.sh file
bash-3.00$ su
Password:
# cd /oracle/app/oracle/product/10.2.0/db_1/
# ./root.sh
Running Oracle10 root.sh script...

The following environment variables are set as:
ORACLE_OWNER= oracle
ORACLE_HOME= /oracle/app/oracle/product/10.2.0/db_1

Enter the full pathname of the local bin directory: [/usr/local/bin]:
The file "dbhome" already exists in /usr/local/bin. Overwrite it? (y/n) [n]: y
Copying dbhome to /usr/local/bin ...
The file "oraenv" already exists in /usr/local/bin. Overwrite it? (y/n) [n]: y
Copying oraenv to /usr/local/bin ...
The file "coraenv" already exists in /usr/local/bin. Overwrite it? (y/n) [n]: y
Copying coraenv to /usr/local/bin ...

Entries will be added to the /var/opt/oracle/oratab file as needed by
Database Configuration Assistant when a database is created
Finished running generic part of root.sh script.
Now product-specific root actions will be performed.

If you are on Grid Control then also run /root.sh from $ORACLE_HOME.

3.Connect to oracle user and start the dbconsole or grid management agent(if you are in grid control).
$emctl start dbconsole (dbcontrol)
$emctl start agent (grid control)

Recover database Issue after missing spfile or pfile

In order to discover pfile or spfile I have discussed in The different ways to discover initialization parameter (spfile or pfile)

Now you may have the scenario that you yet not discovered your spfile or pfile then the remaining option is to create a new pfile. Suppose that you create your spfile like this.

-bash-3.00$ vi /oradata2/1.pfile
undo_tablespace=UNDOTBS1
undo_management=AUTO
compatible=10.2.0.1.0
db_name=arjudba
sga_target=400M

That is without control_files parameter inside it. It is ok if you don't have any controlfile and you want to create a new one. If you don't give any location inside pfile then oracle will create controlfile in the location of $ORACLE_HOME/dbs/cntrlarjudba.dbf.

However if you specify control_files parameter inside pfile then oracle will attempt to create controlfile in the specified location. Like if you create pfile as
-bash-3.00$ vi /oradata2/1.pfile
undo_tablespace=UNDOTBS1
undo_management=AUTO
compatible=10.2.0.1.0
db_name=arjudba
control_files='/oradata2/arjudba/arjudba/control01.ctl','/oradata2/arjudba/arjud
ba/control02.ctl'
sga_target=400M

then create controlfile statement will create controlfile in the location of /oradata2/arjudba/arjudba/control01.ctl, and /oradata2/arjudba/arjudba/control02.ctl

If you don't give any location of control_file parameter inside pfile then further startup will fails with ORA-00205
Like,

SQL> startup
ORACLE instance started.

Total System Global Area 209715200 bytes
Fixed Size 2019608 bytes
Variable Size 83889896 bytes
Database Buffers 117440512 bytes
Redo Buffers 6365184 bytes
ORA-00205: error in identifying control file, check alert log for more info

On Solaris 64 bit Rman fails with libclntsh.so.10.1, ld.so.1, Segmentation Fault (core dumped)

Problem Description and Solution Step by Step
1.
Whenever you try to connect to rman then it fails with ld.so.1: rman: fatal: libclntsh.so.10.1: open failed: No such file or directory like,
bash-3.00$ rman target /
ld.so.1: rman: fatal: libclntsh.so.10.1: open failed: No such file or directory
Killed

2.
But you browse the directory $ORACLE_HOME/lib32 and see libclntsh.so.10.1 is present and also has the correct permission on it.
bash-3.00$ ls -l $ORACLE_HOME/lib32/libclntsh.so.10.1
-rwxr-xr-x 1 oracle oinstall 24094536 Apr 17 12:35 /oracle/app/oracle/product/10.2.0/db_1/lib32/libclntsh.so.10.1

3.
The missing thing is your LD_LIBRARY_PATH is not set. So I have set it below on my bash shell. If you use another shell then use shell specific command to set the environmental variable. On C shell use setenv LD_LIBRARY_PATH=$ORACLE_HOME/lib32. If you are on windows machine then use
set LD_LIBRARY_PATH=$ORACLE_HOME\lib32
As I am on bash shell on solaris so I set it as,
bash-3.00$ export LD_LIBRARY_PATH=$ORACLE_HOME/lib32

4.
Now try to connect rman and see it fails with rong ELF class: ELFCLASS32.
bash-3.00$ rman target /
ld.so.1: rman: fatal: /oracle/app/oracle/product/10.2.0/db_1/lib32/libclntsh.so.10.1: wrong ELF class: ELFCLASS32
Killed

5.
As my Solaris is 64 bit so I need to set LD_LIBRARY_PATH_64 environmental variable also.
bash-3.00$ export LD_LIBRARY_PATH_64=$ORACLE_HOME/lib

6.
You can easily avoid every time setting of these export or setenv or set command on your system each time by editing the profile or setting environmental variable on windows and make it permanent.

7.
On Solaris machine it is .profile
If you use linux bash shell then it is .bash_profile on home directory.

8.
I modified .profile file and added two lines in it like below
-bash-3.00$ vi .profile
export LD_LIBRARY_PATH=$ORACLE_HOME/lib32
export LD_LIBRARY_PATH_64=$ORACLE_HOME/lib

9.
To make the immediate affect of these changes use source command like,
-bash-3.00$ source .profile

10.
Now try to connect to your database.
bash-3.00$ rman target /

Recovery Manager: Release 10.2.0.1.0 - Production on Tue Sep 2 02:25:13 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Segmentation Fault (core dumped)

11.
Oh, you still fails. However I restarted the server machine and try to connect to rman and then see errors goes away and connect to rman.
I did it

bash-3.00$ su -
Password:
Sun Microsystems Inc. SunOS 5.10 Generic January 2005
# reboot

12.
Then after starting up I issued rman command.
bash-3.00$ rman target /

Recovery Manager: Release 10.2.0.1.0 - Production on Tue Sep 2 03:46:31 2008

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

connected to target database: ARJU (DBID=2871324595)

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';

What will happen 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
arjudba_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.

On Solaris putty login fails with Using keyboard-interactive authentication

While connecting through putty from windows vmware machine to solaris machine after giving username and password it fails to logon to database bu displaying message 'Using keyboard-interactive authentication' and 'Access denied'.
Here is the sample output:
login as: oracle
Using keyboard-interactive authentication.
Password:
Access denied
Using keyboard-interactive authentication.

Solution of The Problem
You can solve the problem through several ways. The method I use is,
Way 1: With help of one linux machine.
1.Using putty log on to a linux machine that works.
2.With the linux mahine console I use ssh solaris_machine_name -Y
3.After providing username and password I am connected to the solaris shell.

This method is easy if you have a linux machine.

Way 2: With PuttyGen
1.Download PuttyGen.exe from http://www.chiark.greenend.org.uk/~sgtatham/putty/download.html.

2.Run the puttygen program and hit the 'generate' button.

3.After some moments public key that is displayed in the tool. Copy the key and paste it into the ~/.ssh/authorized_keys file on the Solaris machine.

4.Save the private key on the Windows machine by using the 'Save Private Key' button. While saving it give a name.

5.In the PuTTY configuration, choose Connection->SSH->Auth, and select the file (the private key) that you saved in the step 4.

6.Open the connection, and it should work.

X11 connection rejected because of wrong authentication

On my Solaris Operating System I have entered into ssh -Y option and now try to run X-windows /Graphical windows and it returned,
-bash-3.00$ X11 connection rejected because of wrong authentication.
X connection to localhost:11.0 broken (explicit kill or server shutdown).

Cause of The Problem
The error happened because ssh was unable to write my ~/.Xauthority file.

Solution of The problem
1.The most possible reason is your disk is full. Check the disk space by using df -h. I have seen that my home directory is full. After that I
deleted/move unnecessary files from my Solaris home partition and then
exit
Log on again using ssh ... -Y
And run graphical windows like dbca&

2.It may be because of the permission settings of the ~/.Xauthority file or existence of the file.
To fix it
rm .Xauthority
touch .Xauthority
chmod 600 .Xauthority

Oracle RAC Software Components

An Oracle RAC database has the same processes and memory structures as a single-instance Oracle database as well as additional process and memory structures that are specific to Oracle RAC.
The list of Oracle RAC processes are:

1)LMS—Global Cache Service Process

2)LMD—Global Enqueue Service Daemon

3)LMON—Global Enqueue Service Monitor

4)LCK0—Instance Enqueue Process

After one instance caches data, any other instance within the same cluster database can acquire a block image from another instance in the same database faster than by reading the block from disk. This is called cache fusion, any instance need data that is cached by other instance, is passed to it by using private interconnect.

The Global Cache Service(GCS) and Global Enqueue Service(GES) maintain records of the statuses of each data file and each cached block using a Global Resource Directory (GRD). Oracle RAC processes and the GRD collaborate to enable Cache Fusion.

Related Documents

Pre-Installation RAC environement setup check with CVU

Configure Network for oracle RAC installation

List of Parameters that must have identical in RAC database

Various Tools for Installing, Configuring Oracle RAC

Overview of Oracle Clusterware and Oracle RAC

Configure Shared Storage in Oracle RAC installation

Configure Raw Devices for ASM in RAC.

Requirements to check before install Oracle RAC

Configure server to install Oracle RAC

Oracle Clusterware Processes on UNIX-Based Systems

To support clusterware on unix system several processes must run on each node in the cluster.
The Oracle Clusterware processes on UNIX-based systems are:

1)crsd: This is Cluster Ready Services Daemon. This process runs as root user. It performs high availability recovery and management operations such as maintaining the OCR and managing application resources. This process restarts automatically upon failure.

2)evmd: This is EVent Manager Daemon. This process runs as root user. It starts the racgevt process to manage FAN server callouts.

3)ocssd: This is Oracle Cluster Synchronization Services Daemon. This process runs as oracle user. It manages cluster node membership and failure of this process results in cluster restart.

4)oprocd: This is Oracle PROcess monitor Daemon. This process runs as root user. Note that this process only appears on platforms that do not use vendor clusterware with Oracle Clusterware.
Related Documents
http://arjudba.blogspot.com/2010/03/cluvfy-fails-with-prvf-5436-prvf-9652.html
http://arjudba.blogspot.com/2010/03/in-11gr2-grid-rootsh-fails-with-crs.html
http://arjudba.blogspot.com/2010/03/what-to-do-after-failure-of-oracle.html
http://arjudba.blogspot.com/2009/12/enable-archive-log-mode-for-rac.html
http://arjudba.blogspot.com/2008/09/list-of-parameters-that-must-have.html
http://arjudba.blogspot.com/2008/08/oracle-rac-software-components.html
http://arjudba.blogspot.com/2008/08/oracle-clusterware-processes-on-unix.html
http://arjudba.blogspot.com/2008/08/configure-raw-devices-for-asm-in-rac.html
http://arjudba.blogspot.com/2008/08/crs-stack-fails-to-start-after-reboot.html
http://arjudba.blogspot.com/2008/08/configure-network-for-oracle-rac.html
http://arjudba.blogspot.com/2008/08/pre-installation-rac-environement-setup.html
http://arjudba.blogspot.com/2008/08/configure-server-to-install-oracle-rac.html

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/arjudba/arjudba/redo02.log
/oradata2/arjudba/arjudba/redo01.log
/oradata2/arjudba/arjudba/redo03.log

SQL> select name from v$controlfile;

NAME
--------------------------------------------------------------------------------
/oradata2/arjudba/arjudba/control01.ctl
/oradata2/arjudba/arjudba/control02.ctl
/oradata2/arjudba/arjudba/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/arjudba/arjudba/control0*

SQL> !rm /oradata2/arjudba/arjudba/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 "ARJUDBA" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/oradata2/arjudba/arjudba/redo01.log' SIZE 50M,
GROUP 2 '/oradata2/arjudba/arjudba/redo02.log' SIZE 50M,
GROUP 3 '/oradata2/arjudba/arjudba/redo03.log' SIZE 50M
DATAFILE
'/oradata2/arjudba/arjudba/system01.dbf',
'/oradata2/arjudba/arjudba/undotbs01.dbf',
'/oradata2/arjudba/arjudba/sysaux01.dbf',
'/oradata2/arjudba/arjudba/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=arjudba
_allow_resetlogs_corruption = true
sga_target=400M
control_files='/oradata2/arjudba/arjudba/control01.ctl','/oradata2/arjudba/arjud
ba/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 "ARJUDBA" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/oradata2/arjudba/arjudba/redo01.log' SIZE 50M,
GROUP 2 '/oradata2/arjudba/arjudba/redo02.log' SIZE 50M,
GROUP 3 '/oradata2/arjudba/arjudba/redo03.log' SIZE 50M
DATAFILE
'/oradata2/arjudba/arjudba/system01.dbf',
'/oradata2/arjudba/arjudba/undotbs01.dbf',
'/oradata2/arjudba/arjudba/sysaux01.dbf',
'/oradata2/arjudba/arjudba/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.

Related Documents
Recover database after only lose of all controlfiles

Recover database after only lose of all controlfiles

This example is based on,
You have lost all your current and backup of controlfile.
You have avaiable your current data files.
You have available your online redo log files.

In the scenario, I will show the procedure of how to recover database if you lose your all copies of controlfiles and you don't have any backup of your controlfile.

Let's start by deleting controlfile of my running database.

1.In order to know the controlfiles of my database issue,
SQL> select name from v$controlfile;

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

2.Delete all copies of controlfile. 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/arjudba/arjudba/control0*

3.Now let's see whether controlfile is available or not by issuing following command.
SQL> shutdown abort
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 314572800 bytes
Fixed Size 2020416 bytes
Variable Size 121637824 bytes
Database Buffers 184549376 bytes
Redo Buffers 6365184 bytes
ORA-00205: error in identifying control file, check alert log for more info

As it can't read controlfile so ORA-00205 arises.

4.From this state is your situation what you need to do. 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 you have to remember the name of datafile and online redo log file.
-bash-3.00$ vi /oradata2/ctl.ctl
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "ARJUDBA" NORESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/oradata2/arjudba/arjudba/redo01.log' SIZE 50M,
GROUP 2 '/oradata2/arjudba/arjudba/redo02.log' SIZE 50M,
GROUP 3 '/oradata2/arjudba/arjudba/redo03.log' SIZE 50M
DATAFILE
'/oradata2/arjudba/arjudba/system01.dbf',
'/oradata2/arjudba/arjudba/undotbs01.dbf',
'/oradata2/arjudba/arjudba/sysaux01.dbf',
'/oradata2/arjudba/arjudba/users01.dbf'
CHARACTER SET WE8ISO8859P1
;

5.Save the script and run it inside SQL*plus.
SQL> @/oradata2/ctl.ctl
ORA-01081: cannot start already-running ORACLE - shut it down first

Control file created.

6. At this stage your database is mounted. You need to recover it.
SQL> recover database;
Media recovery complete.

7.Open the database after recovery completes.
SQL> alter database open;
Database altered.

8.As Create controlfile statement does not include Temp tablespace you may need to add it.
SQL>ALTER TABLESPACE TEMP ADD TEMPFILE '/oradata2/arjudba/arjudba/temp01.dbf'
SIZE 20971520 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;


Related Documents
Recover database after missing online redo logs and all controlfiles.
Purpose and Restriction of Recover Command in Oracle
Restore and Recover database in Noarchivelog Mode