High Availability and Flexibility with Standby Database solution. How to create manual Standby Database?

High Availability and Flexibility with Standby Database solution. How to create manual Standby Database?


The purpose of this blog entry is to show you how to create manually a physical standby database.

The first will be a basic primary and standby database setup on 2 different machines. In this case, I used VMWARE.

If we afford to lose few minutes of data then this is very good cheap solution for disaster recovery solution.


The Standby Database

• Oracle supplied disaster recovery (DR) solution.
• Operates in 2 modes –manual and automatic.
• No GUI.
• Available since 7.3.4.
• Available with Std Edition one





Step by step document to create manual Standby Database

1 - Prerequisites

• Same Oracle software version must be used for both primary and standby databases. The operating system running on primary and standby locations must be same, but operating system release may not need to be same.
• The Primary Database must run in ARCHIVELOG mode.
• Each primary and standby database must have its own control file.
• If primary and standby database
• s are placed on the same system, initialization parameters must be adjusted correctly.

Preparing Primary Database for Standby Database creation
Configuration in Primary Database


2 Ensure the primary database in ARCHIVELOG mode

Ensure the primary database in ARCHIVELOG mode using following command.
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination C:\oracle\ora92\database\archive\ORCLC
Oldest online log sequence 9
Next log sequence to archive 11
Current log sequence 11

3 - Identify the primary database Datafiles
SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------

C:\ORACLE\ORADATA\ORCLC\SYSTEM01.DBF
C:\ORACLE\ORADATA\ORCLC\UNDOTBS01.DBF
C:\ORACLE\ORADATA\ORCLC\CWMLITE01.DBF
C:\ORACLE\ORADATA\ORCLC\DRSYS01.DBF
C:\ORACLE\ORADATA\ORCLC\EXAMPLE01.DBF
C:\ORACLE\ORADATA\ORCLC\INDX01.DBF
C:\ORACLE\ORADATA\ORCLC\ODM01.DBF
C:\ORACLE\ORADATA\ORCLC\TOOLS01.DBF
C:\ORACLE\ORADATA\ORCLC\USERS01.DBF
C:\ORACLE\ORADATA\ORCLC\XDB01.DBF

10 rows selected.

4 - Make a cold backup copy of Primary Database
Make a cold backup copy of primary database by performing following steps:

4.1 Shutdown the Primary Database
Issue the following statement to shutdown the primary database.
SQL> shutdown immediate;

4.2 Copy the Datafiles to standby location
Copy the redo log files and Datafiles identified in section 3 to standby location.
Note: Primary Database must be shutdown while copying the files.



5 - Restart the Primary Database

Execute following command to restart the Primary Database.
SQL> startup;


6 - Create Control file for Standby Database
Issue the following command on primary database to create control file for the standby database.
SQL> ALTER DATABASE CREATE STANDBY CONTROLFILE AS 'D:\oracle\oradata\stby\control_sb01.ctl';

Database altered.
The filename for newly created standby control file must be different of current control file of the primary database. Also control file for standby database must be created after the cold backup of Datafiles and redo logs. Copy this standby controlfile to the standby server.




Configuration in Standby Database
7 - Create pfile for standby database from the primary database

Copy the pfile from the primary database to the standby server

8 - Set initialization parameters on physical standby database
Although most of the initialization parameter settings in the text initialization parameter file that you copied from the primary system are also appropriate for the physical standby database, some modifications need to be made. Edit created pfile from primary database.

db_name - Not modified. The same name as the primary database.

compatible - Not modified. The same as the primary database,
control_files - Specify the path name and filename for the standby control file .

log_archive_start - Not modified. The same as the setting for the primary database, TRUE

db_file_name_convert - Specify the location of the primary database datafiles followed by the standby location of the datafiles. This parameter will convert the filename of the primary database datafiles to the filename of the standby datafile filenames. If the standby database is on the same system as the primary database or if the directory structure where the datafiles are located on the standby site is different from the primary site then this parameter is required.

log_file_name_convert - Specify the location of the primary database logs followed by the standby location of the logs. This parameter will convert the filename of the primary database log to the filenames of the standby log. If the standby database is on the same system as the primary database or if the directory structure where the logs are located on the standby site is different from the primary site then this parameter is required.

instance_name - If both primary and standby databases are to reside in the same server, specify a different value for the standby database than the primary database. If standby database will be in a different server, then ignore this.
lock_name_space - - If both primary and standby databases are to reside in the same server, Specify the standby database instance name. Use this parameter when you create the physical standby database on the same system as the primary database. Change the INSTANCE_NAME parameter to a value other than its primary database value, and set this LOCK_NAME_SPACE initialization parameter to the same value that you specified for the standby database INSTANCE_NAME initialization parameter.

Also change the values of the parameters background_dump_dest, core_dump_dest and user_dump_dest to specify location of the standby database.

(Refer Annexure for initialization parameter settings for primary and standby database.)
9 - Create a Window service
If standby database is running on windows system, then oradim utility is used to create windows service. Issue following command from the command prompt window
C:\>oradim -new -sid ssss -intpwd yyyy -startmode manual

10 - Configure tnsnames for standby database in the standby server


11 - Start Physical standby database in standby mode


Start up the stand by database using following commands
C:\>set oracle_sid=ssss

C:\>sqlplus /nolog

SQL*Plus: Release 9.2.0.1.0 - Production on Mon Apr 25 17:13:26 2005

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

SQL> conn / as sysdba
Connected to an idle instance.
SQL> startup pfile='C:\oracle\ora92\database\initstby.ora' nomount;
ORACLE instance started.
Total System Global Area 135338868 bytes
Fixed Size 453492 bytes
Variable Size 109051904 bytes
Database Buffers 25165824 bytes
Redo Buffers 667648 bytes

SQL> alter database mount standby database;
Database altered.


12. Now you have to transfer archivelogs from Primary database and apply them to standby database

12.1 create a script in primary server to run under windows task at desired intervals (15 mins or 30 mins etc.), to transfer archivelogs from primary to standby:-

move _arch.cmd
==================
pushd C:\oracle\admin\scripts
set ORACLE_SID=xxx
set ORACLE_HOME=C:\oracle\product\10.2.0\db_1

%ORACLE_HOME%\bin\sqlplus dbo/dbo @move_archivelog.sql

robocopy D:\oracle\backup\arch\roc8 \\172.30.36.13\oracle\backup\arch\roc8 /MIR /COPY:DT /FFT /log:robocopystatus.log



The sql below is to force a new archivelog from primary database:

Apply_archivelog.sql
==================
ALTER SYSTEM SWITCH LOGFILE;
exit;



**** Windows robocopy utility is used above to send archivelogs from arch folder in primary server to arch folder in standby server ****


12.2 apply the archivelogs arriving from primary server to the standby database every 15 mins or 30 mins under a windows task in standby server as follows:-

apply_arch.cmd
==============
pushd C:\oracle\admin\scripts
set ORACLE_SID=ssss
set ORACLE_HOME=C:\oracle\product\10.2.0\db_1
%ORACLE_HOME%\bin\sqlplus /nolog @ Apply_archivelog.sql




Apply_archivelog.sql
===============
connect sys/oracle as sysdba;
set timing on
set echo on
spool apply_archp.log
recover standby database;
AUTO
spool off
exit



License requirements by Oracle:

Standby database doesn’t need Oracle license for 10 days in calendar year.

Failover – In this type of recovery, nodes are arranged in a cluster and share
one disk array. A Failover cluster is a group of systems, bound together into a
common resource pool. In this type of recovery method, the Production node
acts as the primary node. When the primary node fails, one of the surviving
nodes in the cluster acts as the primary node. Solutions like Oracle Failsafe
(included with Oracle Database EE or SE, SE1), or third party vendor solutions
(e.g. Veritas, HP Service Guard, HACMP, Linux HA - Heartbeat) are used to
manage Failover environments. In this type of environment, Oracle permits
licensed Oracle customers to run some Technology Programs on an
unlicensed spare computer for up to a total of ten separate days in any given
calendar year. Once the primary node is repaired, you must switch back to the
primary node. Once the failover period has exceeded ten days, the failover
node must be licensed.

Source: Oracle SOFTWARE INVESTMENT GUIDE


Testing the environment

Once primary and Standby database is configured and transportation log is configured to transfer we need to test it thoroughly.

We can execute some DML and DDL in primary database. After archive logs are applied in Standby database we should able to see those DLL and DML changes in standby database.

To check it Open the database on read only mode.

SQL> alter database open read only;

After the test re-start the database in mount mode. If you OPEN the database you have to recreate the standby database again.
1. SQL> shutdown immediate;
2. SQL>startup mount;



Checking log file in Standby database:

If you check log file you might see error and warning like below.


ORCL: SQL> recover standby database;
ORA-00279: change 239868 generated at 10/20/2010 12:50:02 needed for thread 1
ORA-00289: suggestion :
D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2010_10_20\O1_MF_1_1
00_%U_.ARC
ORA-00280: change 239868 for thread 1 is in sequence #100


Specify log: {=suggested | filename | AUTO | CANCEL}
AUTO
ORA-00279: change 240264 generated at 10/20/2010 13:05:02 needed for thread 1
ORA-00289: suggestion :
D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2010_10_20\O1_MF_1_1
01_%U_.ARC
ORA-00280: change 240264 for thread 1 is in sequence #101
ORA-00278: log file
'D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2010_10_20\O1_MF_1_
100_6CY89YTC_.ARC' no longer needed for this recovery

ORA-00308: cannot open archived log
'D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2010_10_20\O1_MF_1_
101_%U_.ARC'
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) The system cannot find the file specified.
TOM: SQL> spool off

If you see error like ORA-00308: cannot open archived log.

Check the thread number in your Flash recovery area of Primary database to see whether that archive log file exist. If the archive log doesn’t exist just ignore it as recovery database look for next archive log which might not generated at that time.

Good luck!

How to Reclaim disk space from database/ How to reduce data file size to free disk?

My tablespace is fragmented and I want to reduce the size of my dbf files to free up disk. How do I reclaim disk space in Oracle?

Oracle maps the physical files (customer.dbf) to their logical "tablespace" construct and Oracle places objects (tables & indexes) into the tablespace.
Within the tablespace, objects are scattered throughout the tablespace and corresponding datafiles.

Tables, indexes and tablespaces will naturally fragment as a function of update activity and Oracle has many methods for reclaiming disk space and a segment advisor which will recommend when tables and indexes will benefit from a reorganization to free up disk space.

Segments that undergo significant data manipulation language (DML) activity, such as UPDATE and DELETE operations, can become sparsely populated, with chunks of free space within their data blocks. Besides simply wasting space, sparsely populated segments can also lead to poor performance, because operations such as a full table scan will need to scan more blocks than necessary to retrieve data.


Before Oracle Database 10g, you could reclaim the free segment space by dropping the table, re-creating it, and then reloading the data. You could also use the ALTER TABLE MOVE command to move the table to a different tablespace. Both of these processes, however, must occur with the table offline. Online table reorganization is another way to reclaim space, but it requires significant disk space.

So how much DISK space you can claim from your data file (Calculate)?

Let run this script:

SELECT substr(FILE_NAME,1,55),
CEIL( (NVL(HWM,1)*&&BLKSIZE)/1024/1024 ) SMALLEST,
CEIL( BLOCKS*&&BLKSIZE/1024/1024) CURRSIZE,
CEIL( BLOCKS*&&BLKSIZE/1024/1024) - CEIL( (NVL(HWM,1)*&&BLKSIZE)/1024/1024 ) SAVINGS
FROM DBA_DATA_FILES DBADF, ( SELECT FILE_ID, MAX(BLOCK_ID+BLOCKS-1) HWM FROM DBA_EXTENTS GROUP BY FILE_ID ) DBAFS
WHERE DBADF.FILE_ID = DBAFS.FILE_ID(+) Order by SAVINGS

You will ask for BOCKSIZE.

You can find database block size from intXXX.ora file.
db_block_size=8192

So in this case enter 8192 as parameter.

The output will be like this:

ORCL: SQL> SELECT substr(FILE_NAME,1,55),
2 CEIL( (NVL(HWM,1)*&&BLKSIZE)/1024/1024 ) SMALLEST,
3 CEIL( BLOCKS*&&BLKSIZE/1024/1024) CURRSIZE,
4 CEIL( BLOCKS*&&BLKSIZE/1024/1024) - CEIL( (NVL(HWM,1)*&&BLKSIZE)/1024/1024 ) SAVINGS
5 FROM DBA_DATA_FILES DBADF, ( SELECT FILE_ID, MAX(BLOCK_ID+BLOCKS-1) HWM FROM DBA_EXTENTS GROUP BY FILE_ID ) DBAFS
6 WHERE DBADF.FILE_ID = DBAFS.FILE_ID(+) Order by SAVINGS
7 /
old 2: CEIL( (NVL(HWM,1)*&&BLKSIZE)/1024/1024 ) SMALLEST,
new 2: CEIL( (NVL(HWM,1)*8192)/1024/1024 ) SMALLEST,
old 3: CEIL( BLOCKS*&&BLKSIZE/1024/1024) CURRSIZE,
new 3: CEIL( BLOCKS*8192/1024/1024) CURRSIZE,
old 4: CEIL( BLOCKS*&&BLKSIZE/1024/1024) - CEIL( (NVL(HWM,1)*&&BLKSIZE)/1024/1024 ) SAVINGS
new 4: CEIL( BLOCKS*8192/1024/1024) - CEIL( (NVL(HWM,1)*8192)/1024/1024 ) SAVINGS

SUBSTR(FILE_NAME,1,55) SMALLEST CURRSIZE SAVINGS
------------------------------------------------------- ---------- ---------- ----------
D:\ ORADATA\ORCL\DATA_AUD_02.DBF 80 80 0
D:\ ORADATA\ORCL\DATA6_02.DBF 200 200 0
D:\ ORADATA\ORCL\DATA1_02.DBF 520 520 0
D:\ ORADATA\ORCL\DATA_S_03.DBF 20 20 0
D:\ ORADATA\ORCL\DATA_AUD_01.DBF 60 60 0
D:\ ORADATA\ORCL\DATA_02.DBF 120 120 0
D:\ ORADATA\ORCL\DATA4_01.DBF 20 20 0
D:\ ORADATA\ORCL\DATA5_02.DBF 100 100 0
D:\ ORADATA\ORCL\DATA_AUDI_01.DBF 17 20 3
D:\ ORADATA\ORCL\SYSAUX01.DBF 197 200 3
D:\ ORADATA\ORCL\DATA_AUDI_02.DBF 16 20 4

SUBSTR(FILE_NAME,1,55) SMALLEST CURRSIZE
------------------------------------------------------- ---------- ---------- ----------
D:\ ORADATA\ORCL\DATA_01.DBF 135 140 5
D:\ ORADATA\ORCL\DATA_SI_01.DBF 13 20 7
D:\ ORADATA\ORCL\DATA6_01.DBF 213 220 7
D:\ ORADATA\ORCL\DATA2_02.DBF 9 20 11
D:\ ORADATA\ORCL\DATA2_01.DBF 8 20 12
D:\ ORADATA\ORCL\DATA4_02.DBF 27 40 13
D:\ ORADATA\ORCL\DATA_S_02.DBF 26 40 14
D:\ ORADATA\ORCL\DATA1_01.DBF 545 560 15
D:\ ORADATA\ORCL\USERS01.DBF 2 19 17
D:\ ORADATA\ORCL\DATA5_01.DBF 102 120 18
D:\ ORADATA\ORCL\DATA_HL7_02.DBF 1 20 19

SUBSTR(FILE_NAME,1,55) SMALLEST CURRSIZE SAVINGS
------------------------------------------------------- ---------- ---------- ----------
D:\ ORADATA\ORCL\DATA_HL7I_02.DBF 1 20 19
D:\ ORADATA\ORCL\DATA3_01.DBF 1 20 19
D:\ ORADATA\ORCL\DATA_HL7_01.DBF 1 20 19
D:\ ORADATA\ORCL\DATA_HL7I_01.DBF 1 20 19
D:\ ORADATA\ORCL\DATA3_02.DBF 1 20 19
D:\ ORADATA\ORCL\DATA_S_01.DBF 20 40 20
D:\ ORADATA\ORCL\DATA_SI_02.DBF 16 40 24

SUBSTR(FILE_NAME,1,55) SMALLEST CURRSIZE SAVINGS
------------------------------------------------------- ---------- ---------- ----------
D:\ ORADATA\ORCL\SYSTEM01.DBF 265 300 35
D:\ ORADATA\ORCL\UNDOTBS01.DBF 34 85 51

Let’s write a dynamic script to resize data file


set pagesize 0
set verify off
set heading off
set feedback off
set linesize 300

prompt Running Shrink_data_&&1..dbsql
spool Shrink_data_&&1..dbsql

SELECT 'ALTER DATABASE DATAFILE '|| ' '''|| substr(FILE_NAME,1,65) ||' '''|| ' RESIZE ' || CEIL(
(NVL(HWM,1)*&&BLKSIZE)/1024/1024 ) ||'M; ' FROM DBA_DATA_FILES DBADF, ( SELECT FILE_ID,MAX(BLOCK_ID+BLOCKS-1) HWM FROM DBA_EXTENTS GROUP BY FILE_ID ) DBAFS WHERE DBADF.FILE_ID = DBAFS.FILE_ID(+) and CEIL(
BLOCKS*&&BLKSIZE/1024/1024) - CEIL( (NVL(HWM,1)*&&BLKSIZE)/1024/1024 )>1

spool off
set echo on
spool Shrink_data_&&1..log

rem @Shrink_data_&&1..dbsql
spool

spool off

exit

You will see all entries of data files in spool output (Shrink_data.log).

For example:
ALTER DATABASE DATAFILE 'D:\ ORADATA\ORCL\SYSTEM01.DBF ' RESIZE 265M;

This is a dynamic script and tested in several databases.

Please be sure to retest again in your development box before you ran it in production environment.

How to calculate the size of Oracle Database

What script do I run to get the size of an Oracle Database?

The following script calculates the storage allocated to an Oracle database, i.e., its overall size. You will need DBA privilege to run this script.


select ROUND( ( a.data_size + b.temp_size +
c.redo_size + d.cf_size +
e.bct_size)
/1024/1024/1024
) "total_GB_size"
from
( select SUM(bytes) data_size from v$datafile) a,
( select NVL(sum(bytes),0) temp_size from v$tempfile) b,
( select SUM(bytes) redo_size from v$log) c,
( select SUM(block_size*file_size_blks) cf_size from v$controlfile) d,
( select NVL(bytes,0) bct_size from v$block_change_tracking) e

select a.data_size+b.temp_size+c.redo_size "total_size"
from ( select sum(bytes)/1024/1024 data_size
from dba_data_files )a,
( select nvl(sum(bytes),0)/1024/1024 temp_size
from dba_temp_files ) b,
( select sum(bytes)/1024/1024 redo_size
from sys.v_$log ) c

Manually configure the Oracle 10g EM dbconsole

Manually configure the Oracle 10g EM dbconsole
Overview
When you choose to create a preconfigured database during the Oracle 10g installation, you can select the Oracle Enterprise Manager (OEM) interface that you want to use to manage the database. The following options are available:

Database Grid Control

This option is available only if an Oracle Management Agent is installed on the system. When the Installer detects an Oracle Management Agent on the system, it allows you to choose this option and specify the Oracle Management Service that you want to use to manage the database.

If an Oracle Management Agent is not installed, you must choose to use Database Control to manage the database. However, if you install Oracle Management Agent after you install Oracle Database, you can then use Grid Control to manage this database.

Database Control
This option is selected by default if an Oracle Management Agent is not installed on the system. However, even if a Management Agent is installed, you can still choose to configure Database Control to manage the database.


Custom installation

If you choose the Custom installation type or the Advanced database configuration option during the installation, the Installer does not display the OEM setup screens. Instead, it runs the Database Configuration Assistant (DBCA) in interactive mode, which enables you to create a custom database.

DBCA also enables you to specify the Oracle Enterprise Manager interface that you want to use. Furthermore, you can also use DBCA after the installation to configure Database Control for a database that was not previously configured to use it.
However, if you decide to setup your own Database, you must install the Database Control manually.



Setup your own Database and manually install the Database Control

The Database Control relies on various underlying technologies to discover, monitor, and administer the Oracle Database environment.
From the Database Control, you can monitor and administer a single Oracle Database instance.

The Database Control Framework consists of the Database Control and its underlying technologies:
A local version of the Oracle Management Service designed to work with the local database or clustered database.
A local Oracle Management Repository installed in the local database and designed to store management data for the Database Control.

The following steps have to be performed.

Create your own Database


Create the Database Control Repository and setup the OC4J Application Server
Make sure, that you can connect to the Repository Database (Test it with SQL*Plus). Examples for Setup Files can be found here:
Windows
Linux

Now start the Oracle EM dbconsole Build Script

Windows Environment

Here Database DBPORD is using listener port number 1526 but OEM uses listener port number 1521.


d:\PROD\ADMIN\SCRIPTS\com>emca -repos create

STARTED EMCA at Oct 10, 2008 5:34:08 PM
EM Configuration Assistant, Version 10.2.0.1.0 Production
Copyright (c) 2003, 2005, Oracle. All rights reserved.

Enter the following information:
Database SID: DBPORD
Listener port number: 1526
Password for SYS user:
Password for SYSMAN user:

Do you wish to continue? [yes(Y)/no(N)]: y
Oct 10, 2008 5:34:59 PM oracle.sysman.emcp.EMConfig perform
INFO: This operation is being logged at D:\oracle\product\10.2.0\db_1\cfgtoollog
s\emca\DBPORD\emca_2008-10-10_05-34-08-PM.log.
Oct 10, 2008 5:35:01 PM oracle.sysman.emcp.EMReposConfig createRepository
INFO: Creating the EM repository (this may take a while) ...
Oct 10, 2008 5:36:50 PM oracle.sysman.emcp.EMReposConfig invoke
INFO: Repository successfully created
Enterprise Manager configuration completed successfully
FINISHED EMCA at Oct 10, 2008 5:36:50 PM




D:\PROD\ADMIN\SCRIPTS\COM>emca -config dbcontrol db

STARTED EMCA at Oct 10, 2008 5:43:43 PM
EM Configuration Assistant, Version 10.2.0.1.0 Production
Copyright (c) 2003, 2005, Oracle. All rights reserved.

Enter the following information:
Database SID: DBPORD
Listener port number: 1526
Password for SYS user:
Password for DBSNMP user:
Password for SYSMAN user:
Email address for notifications (optional):
Outgoing Mail (SMTP) server for notifications (optional):
-----------------------------------------------------------------

You have specified the following settings

Database ORACLE_HOME ................ D:\oracle\product\10.2.0\db_1

Database hostname ................ lhopis01.myCompany.ca
Listener port number ................ 1526
Database SID ................ DBPORD
Email address for notifications ...............
Outgoing Mail (SMTP) server for notifications ...............

-----------------------------------------------------------------
Do you wish to continue? [yes(Y)/no(N)]: y
Oct 10, 2008 5:45:04 PM oracle.sysman.emcp.EMConfig perform
INFO: This operation is being logged at D:\oracle\product\10.2.0\db_1\cfgtoollog
s\emca\DBPORD\emca_2008-10-10_05-43-43-PM.log.
Oct 10, 2008 5:45:21 PM oracle.sysman.emcp.util.DBControlUtil startOMS
INFO: Starting Database Control (this may take a while) ...
Oct 10, 2008 5:46:01 PM oracle.sysman.emcp.EMDBPostConfig performConfiguration
INFO: Database Control started successfully
Oct 10, 2008 5:46:01 PM oracle.sysman.emcp.EMDBPostConfig performConfiguration
INFO: >>>>>>>>>>> The Database Control URL is http://lhopis01.lh.myCompany.ca:1158/em
<<<<<<<<<<<
Enterprise Manager configuration completed successfully
FINISHED EMCA at Oct 10, 2008 5:46:01 PM



Automatically start and stop the DB-Console

D:\PROD\ADMIN\SCRIPTS\COM> emctl start dbconsole
D:\PROD\ADMIN\SCRIPTS\COM> emctl stop dbconsole
D:\PROD\ADMIN\SCRIPTS\COM> emctl status dbconsole

Troubleshooting

Check \oracle\product\10.2.0\db_1\sysman\config
Check listener is up and running

All Control file lost Recover database from RMAN Backup (ORA-00205: error in identifying control file)

All Control file lost Recover database from RMAN Backup (ORA-00205: error in identifying control file)

During media failure (single disk crash) the online redo log is usually available so it is possible to recover up to the point of failure. Quite often ,Windows Oracle instance can be started in nomount mode (i.e. spfile is also available).

In this scenario all Control files are lost and it’s not multiplexed. In that case you have no option but to use a backup control file. The recovery needs to be performed from within RMAN, and requires that all logs (archived and current online logs) since the last backup are available. The logs are required because all datafiles must also be restored from backup. The database will then have to be recovered up to the time the control files went missing. This can only be done if all intervening logs are available.

In this example RMAN is not using catalog.

Try to start database and found following error.

d:> sqlplus sys/**** as sysdba;

SQL*Plus: Release 10.2.0.3.0 - Production on Tue Sep 29 11:05:12 2009

Copyright (c) 1982, 2006, Oracle. All Rights Reserved.

Connected to an idle instance.

ORCL: SQL> startup;
ORACLE instance started.

Total System Global Area 524288000 bytes
Fixed Size 1291576 bytes
Variable Size 306186952 bytes
Database Buffers 213909504 bytes
Redo Buffers 2899968 bytes
ORA-00205: error in identifying control file, check alert log for more info


In Alter log file I found following error:

Tue Sep 29 11:05:18 2009
ALTER DATABASE MOUNT
Tue Sep 29 11:05:18 2009
ORA-00202: control file: 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\CONTROL01.CTL'
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) The system cannot find the file specified.


Complete Recovery by last night RMAN BACKUP set

1. d:\> rman target /

Recovery Manager: Release 10.2.0.3.0 - Production on Tue Sep 29 10:19:36 2009

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

connected to target database: ORCL (not mounted)



2. RMAN> set dbid=1096578125

executing command: SET DBID

****set DBID - get this from the name of the controlfile autobackup.
For example, if autobackup name is CTL_SP_BAK_C-1507972899-20050124-00 the the DBID is 1507972899. This step will not be required if the instance is



3. RMAN> set controlfile autobackup format for device type disk to 'D:\ABC\BACKUP\RMAN\ORCL\cf_%d_%F';

executing command: SET CONTROLFILE AUTOBACKUP FORMAT
using target database control file instead of recovery catalog

**** Verify the format of your backup set



4. RMAN> restore controlfile from autobackup;

Starting restore at 29-SEP-09
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK

recovery area destination: D:\oracle\product\10.2.0\flash_recovery_area
database name (or database unique name) used for search: ORCL
channel ORA_DISK_1: no autobackups found in the recovery area
channel ORA_DISK_1: looking for autobackup on day: 20090929
channel ORA_DISK_1: looking for autobackup on day: 20090928
channel ORA_DISK_1: autobackup found: D:\ABC\BACKUP\RMAN\ORCL\cf_ORCL_c-10965
78125-20090928-03
channel ORA_DISK_1: control file restore from autobackup complete
output filename=D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\CONTROL01.CTL
output filename=D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\CONTROL02.CTL
output filename=D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\CONTROL03.CTL
Finished restore at 29-SEP-09

*** Now that control files have been restored, the instance can mount the


5. RMAN> alter database mount;

database mounted
released channel: ORA_DISK_1


6. RMAN> restore database;

Starting restore at 29-SEP-09
Starting implicit crosscheck backup at 29-SEP-09
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK
Crosschecked 7 objects
Finished implicit crosscheck backup at 29-SEP-09

Starting implicit crosscheck copy at 29-SEP-09
using channel ORA_DISK_1
Finished implicit crosscheck copy at 29-SEP-09

searching for all files in the recovery area
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2009_09_28\O1_MF_1_48_5D1QDPFJ_.ARC
File Name: D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2009_09_28\O1_MF_1_49_5D21CNOB_.ARC
File Name: D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2009_09_28\O1_MF_1_50_5D21CPS7_.ARC
File Name: D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2009_09_29\O1_MF_1_51_5D38N85L_.ARC
File Name: D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2009_09_29\O1_MF_1_52_5D45J1YK_.ARC

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM01.DBF
restoring datafile 00002 to D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\UNDOTBS01.DBF
restoring datafile 00003 to D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSAUX01.DBF
restoring datafile 00004 to D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\USERS01.DBF
restoring datafile 00005 to D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\OPIS_01.DBF
restoring datafile 00006 to D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\OPIS_02.DBF
restoring datafile 00007 to D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\OPIS1_01.DBF
restoring datafile 00008 to D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\OPIS1_02.DBF
restoring datafile 00009 to D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\OPIS2_01.DBF
restoring datafile 00010 to D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\OPIS2_02.DBF
restoring datafile 00011 to D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\OPIS3_01.DBF
restoring datafile 00012 to D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\OPIS3_02.DBF
restoring datafile 00013 to D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\OPIS4_01.DBF
restoring datafile 00014 to D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\OPIS4_02.DBF
restoring datafile 00015 to D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\OPIS5_01.DBF
restoring datafile 00016 to D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\OPIS5_02.DBF
restoring datafile 00017 to D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\OPIS6_01.DBF
restoring datafile 00018 to D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\OPIS6_02.DBF
restoring datafile 00019 to D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\OPIS_S_01.DBF
restoring datafile 00020 to D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\OPIS_S_02.DBF
restoring datafile 00021 to D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\OPIS_SI_01.DBF
restoring datafile 00022 to D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\OPIS_SI_02.DBF
restoring datafile 00023 to D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\OPIS_HL7_01.DBF

restoring datafile 00024 to D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\OPIS_HL7_02.DBF

restoring datafile 00025 to D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\OPIS_HL7I_01.DB
F
restoring datafile 00026 to D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\OPIS_HL7I_02.DB
F
restoring datafile 00027 to D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\OPIS_AUD_01.DBF

restoring datafile 00028 to D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\OPIS_AUD_02.DBF

restoring datafile 00029 to D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\OPIS_AUDI_01.DB
F
restoring datafile 00030 to D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\OPIS_AUDI_02.DB
F
restoring datafile 00031 to D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\OPIS_RHAPSODY_0
1.DBF
restoring datafile 00032 to D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\OPIS_RHAPSODY_0
2.DBF
restoring datafile 00033 to D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\OPIS_OPIS_RPT_0
1.DBF
restoring datafile 00034 to D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\OPIS_OPIS_RPT_0
2.DBF
channel ORA_DISK_1: reading from backup piece D:\ABC\BACKUP\RMAN\ORCL\DB_ORCL
_T698759044_S731_P1
channel ORA_DISK_1: restored backup piece 1
piece handle=D:\ABC\BACKUP\RMAN\ORCL\DB_ORCL_T698759044_S731_P1 tag=FULL BACK
UP
channel ORA_DISK_1: restore complete, elapsed time: 00:01:39
Finished restore at 29-SEP-09

** Database must be recovered because all datafiles have been restored from backup




7. RMAN> recover database;

Starting recover at 29-SEP-09
using channel ORA_DISK_1

starting media recovery

archive log thread 1 sequence 48 is already on disk as file D:\ORACLE\PRODUCT\10
.2.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2009_09_28\O1_MF_1_48_5D1QDPFJ_.ARC
archive log thread 1 sequence 49 is already on disk as file D:\ORACLE\PRODUCT\10
.2.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2009_09_28\O1_MF_1_49_5D21CNOB_.ARC
archive log thread 1 sequence 50 is already on disk as file D:\ORACLE\PRODUCT\10
.2.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2009_09_28\O1_MF_1_50_5D21CPS7_.ARC
archive log thread 1 sequence 51 is already on disk as file D:\ORACLE\PRODUCT\10
.2.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2009_09_29\O1_MF_1_51_5D38N85L_.ARC
archive log thread 1 sequence 52 is already on disk as file D:\ORACLE\PRODUCT\10
.2.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2009_09_29\O1_MF_1_52_5D45J1YK_.ARC
archive log thread 1 sequence 53 is already on disk as file D:\ORACLE\PRODUCT\10
.2.0\ORADATA\ORCL\REDO02.LOG
channel ORA_DISK_1: starting archive log restore to default destination
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=46
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=47
channel ORA_DISK_1: reading from backup piece D:\ABC\BACKUP\RMAN\ORCL\ARCH_P
OM_1_T698759096_S734_P1
channel ORA_DISK_1: restored backup piece 1
piece handle=D:\ABC\BACKUP\RMAN\ORCL\ARCH_ORCL_1_T698759096_S734_P1 tag=TAG20
090928T114456
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
archive log filename=D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG
\2009_09_29\O1_MF_1_46_5D475JO8_.ARC thread=1 sequence=46
channel default: deleting archive log(s)
archive log filename=D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG
\2009_09_29\O1_MF_1_46_5D475JO8_.ARC recid=641 stamp=698841715
archive log filename=D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG
\2009_09_29\O1_MF_1_47_5D475K17_.ARC thread=1 sequence=47
channel default: deleting archive log(s)
archive log filename=D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG
\2009_09_29\O1_MF_1_47_5D475K17_.ARC recid=640 stamp=698841713
archive log filename=D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG
\2009_09_28\O1_MF_1_48_5D1QDPFJ_.ARC thread=1 sequence=48
archive log filename=D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG
\2009_09_28\O1_MF_1_49_5D21CNOB_.ARC thread=1 sequence=49
archive log filename=D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG
\2009_09_28\O1_MF_1_50_5D21CPS7_.ARC thread=1 sequence=50
archive log filename=D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG
\2009_09_29\O1_MF_1_51_5D38N85L_.ARC thread=1 sequence=51
archive log filename=D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG
\2009_09_29\O1_MF_1_52_5D45J1YK_.ARC thread=1 sequence=52
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO02.LOG thread=1 se
quence=53
media recovery complete, elapsed time: 00:00:18
Finished recover at 29-SEP-09



8. RMAN> alter database open;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of alter db command at 09/29/2009 10:42:28
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

-- Recovery completed. The database must be opened with RESETLOGS
-- because a backup control file was used. Can also use
-- "alter database open resetlogs" instead.




9. RMAN> alter database open resetlogs;

database opened




**** Several points are worth emphasizing.*****
1. Recovery using a backup controlfile should be done only if a current control file is
unavailable.
2. All datafiles must be restored from backup. This means the database will need to be
recovered using archived and online redo logs. These MUST be available for recovery until the time of failure.

3. As with any database recovery involving RESETLOGS, take a fresh backup immediately.

4. Technically the above is an example of complete recovery - since all committed
transactions were recovered. However, some references consider this to be incomplete
recovery because the database log sequence had to be reset.
After recovery using a backup controlfile, all temporary files associated with locallymanaged tablespaces are no longer available. You can check that this is so by querying the view V$TEMPFILE - no rows will be returned. Therefore tempfiles must be added (or recreated) before the database is made available for general use. In the case at hand, the tempfile already exists so we merely add it to the temporary tablespace. This can be done using SQLPlus or any tool of your choice:

SQL> alter tablespace temp add tempfile
'D:\oracle_data\datafiles\ORCL\TEMP01.DBF';
Tablespace altered.

RMAN-00554: initialization of internal recovery manager package failed/RMAN-04005: error from target database:/ORA-01031: insufficient privileges

Platform: Oracle 10.2.0.3.0 on Windows 2003 server

*** Logged onto Windows 2003 server Local Windows user which is a member of Administrator and Ora_DBA group. Same database user is created into database and which is authenticated by OS.

Problem:
When try to connect RMAN for database backup following error happen.

Microsoft Windows [Version 5.2.3790]
(C) Copyright 1985-2003 Microsoft Corp.

d:\OPIS2005\ADMIN\SCRIPTS\com>set oracle_sid=POM

d:\OPIS2005\ADMIN\SCRIPTS\com>rman target /

Recovery Manager: Release 10.2.0.4.0 - Production on Tue Jul 28 15:05:33 2009

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

RMAN-00571: =================================================
RMAN-00569: =========== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: =================================================
RMAN-00554: initialization of internal recovery manager package failed
RMAN-04005: error from target database:
ORA-01031: insufficient privileges


Logged on to the box with a local user that is a member of both thelocal Administrators group and the local ora_dba group.Connection to db with sqlplus, using local credentials:

d:\OPIS2005\ADMIN\SCRIPTS\com>sqlplus /

SQL*Plus: Release 10.2.0.3.0 - Production on Tue Jul 28 15:06:51 2009

Copyright (c) 1982, 2006, Oracle. All Rights Reserved.


Connected to:
Oracle Database 10g Release 10.2.0.3.0 - Production

POM: SQL>


But connecting to rman requires me to spell it out ...d:\OPIS2005\ADMIN\SCRIPTS\com>sqlplus /

SQL*Plus: Release 10.2.0.3.0 - Production on Tue Jul 28 15:06:51 2009

Copyright (c) 1982, 2006, Oracle. All Rights Reserved.


Connected to:
Oracle Database 10g Release 10.2.0.3.0 - Production

POM: SQL>
Microsoft Windows [Version 5.2.3790]
(C) Copyright 1985-2003 Microsoft Corp.

d:\OPIS2005\ADMIN\SCRIPTS\COM>rman target sys/manager1@lom.lhsc.world

Recovery Manager: Release 10.2.0.3.0 - Production on Tue Jul 28 15:10:13 2009

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

connected to target database: LOM (DBID=2640997403)

RMAN>

*** Local Windows user is a member of Administrator and Ora_DBA group. Same database user is created into database and which is authenticated by OS.

Tried to connect as SYSDBA:
d:\opis2005\ADMIN\SCRIPTS\COM ( POM )sqlplus "/ as sysdba"

SQL*Plus: Release 10.2.0.3.0 - Production on Tue Jul 28 15:23:28 2009

Copyright (c) 1982, 2006, Oracle. All Rights Reserved.


Connected to:
Oracle Database 10g Release 10.2.0.3.0 - Production

POM: SQL>

So, things are correctly set up for connecting with local sysdbacredentials

SOLUTION:

We found 10g oem agent installed on same box in a different HOME.

The box had two Oracle homes on it, one for a single 10g datbase and one for the 10g oem agent. In the PATH environment variable, the agent home preceded the db home.

Example: C:\OracleHomes\agent10g\jlib;C:\OracleHomes\agent10g\bin;D:\oracle\product\10.2.0\db_1\bin;C:\Program Files\HP\NCU;%SystemRoot%\system32;%SystemRoot%;%SystemRoot%\System32\Wbem;C:\Program Files\OmniBack\bin\

Flipping that -- putting the db home first -- solved the problem and allowedd:\OPIS2005\ADMIN\SCRIPTS\com>rman target /' to connect as it is supposed to.

Why to use /3GB Switch in Windows Oracle

Why to use /3GB Switch in Windows Oracle

Windows 32-bit servers have a problem due to the 32 bit limitation of 2 to the 32nd power (2**32 ~= 2 gigabytes), where Oracle cannot use "above the line" RAM resources.

The 3GB switch only give you an additional two gig, while AWE can go higher


How to Set the /3GB Startup Switch in Windows

Windows Server 2003 includes support for a startup switch that lets you tune the allocation of use of memory and memory address space. Regardless of the amount of physical memory in your system, Windows uses a virtual address space of 4 GB, with 2 GB allocated to user-mode processes (for example, applications) and 2 GB allocated to kernel-mode processes (for example, the operating system and kernel-mode drivers). On systems that have 1 GB or more of physical memory, these two startup switches can be used to allocate more memory to applications (3 GB) and less memory to the operating system (1 GB). This additional virtual address space helps reduce the amount of memory fragmentation in the virtual address space of the Exchange information store process.

Procedure
To Set the /3GB Startup Switch in Windows Server 2003

1. Right-click My Computer and select Properties. The System Properties dialog box will appear.
2. Click the Advanced tab.
3. In the Startup and Recovery area, click Settings. The Startup and Recovery dialog box will appear.
4. In the System startup area, click Edit. This will open the Windows boot.ini file in Notepad.
5. In the [Operating Systems] section, add the following switches to the end of the startup line that includes the /fastdetect switch: /3GB
6. Save the changes and close Notepad.
7. Click OK two times to close the open dialog boxes, and then restart the computer for the change to take effect.