ORA-00214: Controlfile Version Inconsistent

ERROR at line 1:

ORA-00214: controlfile ‘C:\ORACLE\ORADATA\DB01\CONTROL01.CTL’ version 16703

inconsistent with file ‘C:\ORACLE\ORADATA\DB01\CONTROL04.CTL’ version 16648

Its because of the inconsistency between the mirrored copies of the control files.
All copies of the control file must have the same internal sequence number for oracle to start up the database or shut it down in normal or immediate mode.

Causes of the problem ORA-00214 includes,

1. You have restored the control file from backup, but forgot to copy it onto all of the mirrored copies of the control file as listed in the “CONTROL_FILES” parameter in the initialization parameter.

2. Improper Copy paste of the control file to a different location while the database is up and running.

4. The database or the system crashed while the mirrored copies of the control file were being updated, causing them to be out of sync.

5.  Not proper Shut down of the Oracle Machine. Like power failure, disk failure, Memory Failure

Solution

Startup the database with single copy of the Control File. That should be a good copy.

1. Shut Abort

2. If you are using pfile, Edit init.ora file. Remove all the control file Except the Good one. Try One by one. Most probable highest Version Number will work.

3. If  you are using spfile;

startup nomount. Then Set your control_file to the good one.

SQL> alter system set control_files=’c:\oracle\oradata\db01\control02.ctl’ scope=spfile;

System altered

4. Startup restrict [If its working]. Shut down the database. Copy the good control file and Mirror it

Startup.

If instead you get ORA-1122, ORA-1110, and ORA-1207, go back to step 2 and try with another control file.

If you have already tried each and every one of the mirrored copies unsuccessfully, you must create a new control file for the database.

If you get ORA-1113 and ORA-1110 pointing to one of the datafiles, it means the copy of the control file you picked is good, but the referenced datafile must be recovered before the database can be opened.Then RECOVER DATBASE, apply the log it prompt and ALTER DATABASE OPEN.

Oracle®10g Data Guard Concepts and Administration

1. Creating Physical Standby Database

• Preparing the Primary Database for Standby Database Creation
• Step-by-Step Instructions for Creating a Physical Standby Database
• Post-Creation Steps

Preparing the Primary Database for Standby Database Creation

Before  creation of  a standby database we  must first ensure the primary database is properly configured.

Enable Forced Logging

Create Password File

Configure a Standby Redo Log

Set Primary Data Base initialization parameters

Enable Archiving

Enable Forced Login

Place the primary database in FORCE LOGGING mode after database creation using the following SQL statement:

SQL> ALTER DATABASE FORCE LOGGING;

This statement can take a considerable amount of time to complete, because it waits for all unlogged direct write I/O to finish.

Create Password File

Create a password file if one does not already exist. Every database in a Data Guard configuration must use a password file, and the password for the SYS user must be identical on every system for redo data transmission to succeed

Configure a Standby Redo Log

A standby redo log is required for the maximum protection and maximum availability modes and the LGWR ASYNC transport mode is recommended for all databases. Data Guard can recover and apply more redo data from a standby redo log than from archived redo log files alone.

You should plan the standby redo log configuration and create all required log groups and group members when you create the standby database. For increased availability, consider multiplexing the standby redo log files, similar to the way that online redo log files are multiplexed.

Perform the following steps to configure the standby redo log.

Step 1: Ensure log file sizes are identical on the primary and standby databases.

The size of the current standby redo log files must exactly match the size of the current primary database online redo log files

Step 2: Determine the appropriate number of standby redo log file groups.

Minimally, the configuration should have one more standby redo log file group than the number of online redo log file groups on the primary database. However, the recommended number of standby redo log file groups is dependent on the number of threads on the primary database. Use the following equation to determine an appropriate number of standby redo log file groups:

(maximum number of logfiles for each thread + 1) * maximum number of threads

Step 3: Verify related database parameters and settings

Verify the values used for the MAXLOGFILES and MAXLOGMEMBERS clauses on the SQL CREATE DATABASE statement will not limit the number of standby redo log file groups and members that you can add. The only way to override the limits specified by the MAXLOGFILES and MAXLOGMEMBERS clauses is to re-create the primary database or control file

Step 4: Create standby redo log file groups

To create new standby redo log file groups and members, you must have the ALTER DATABASE system privilege. The standby database begins using the newly created standby redo data the next time there is a log switch on the primary database.

How to add a  Standby Redo Log File Group to a Specific Thread

SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 5

2> (‘/oracle/dbs/log1c.rdo’,'/oracle/dbs/log2c.rdo’) SIZE 500M;

How to add  a Standby Redo Log File Group to a Specific Group Number

You can also specify a number that identifies the group using the GROUP clause:

SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 10
  2> ('/oracle/dbs/log1c.rdo','/oracle/dbs/log2c.rdo') SIZE 500M;

Step 5: Verify the standby redo log file groups were created

To verify the standby redo log file groups are created and running correctly, invoke a log switch on the primary database, and then query either the V$STANDBY_LOG view or the V$LOGFILE view on the standby database once it has been created.

SELECT GROUP#,THREAD#,SEQUENCE#,ARCHIVED,STATUS FROM V$STANDBY_LOG;

Set Primary Database Initialization Parameters

On the primary database, you define initialization parameters that control redo transport services while the database is in the primary role. There are additional parameters you need to add that control the receipt of the redo data and log apply services when the primary database is transitioned to the standby role.

DB_NAME=chicago
DB_UNIQUE_NAME=chicago
LOG_ARCHIVE_CONFIG='DG_CONFIG=(chicago,boston)'
CONTROL_FILES='/arch1/chicago/control1.ctl', '/arch2/chicago/control2.ctl'
LOG_ARCHIVE_DEST_1=
 'LOCATION=/arch1/chicago/
  VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
  DB_UNIQUE_NAME=chicago'
LOG_ARCHIVE_DEST_2=
 'SERVICE=boston LGWR ASYNC
  VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
  DB_UNIQUE_NAME=boston'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
LOG_ARCHIVE_MAX_PROCESSES=30

Primary Database: Standby Role Initialization Parameters

FAL_SERVER=boston
FAL_CLIENT=chicago
DB_FILE_NAME_CONVERT='boston','chicago'
LOG_FILE_NAME_CONVERT=  '/arch1/boston/','/arch1/chicago/','/arch2/boston/','/arch2/chicago/'
STANDBY_FILE_MANAGEMENT=AUTO

Enable Archiving

If archiving is not enabled, issue the following statements to put the primary database in ARCHIVELOG mode and enable automatic archiving:

SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT;
SQL> ALTER DATABASE ARCHIVELOG;
SQL> ALTER DATABASE OPEN;

Step-by-Step Instructions for Creating a Physical Standby Database

1. Create a Backup Copy of the Primary Database Datafile

You can use any backup copy of the primary database to create the physical standby database, as long as you have the necessary archived redo log files to completely recover the database. Oracle recommends that you use the Recovery Manager utility (RMAN).

2. Create a Control File for the Standby Database

If the backup procedure required you to shut down the primary database, issue the following SQL*Plus statement to start the primary database:

SQL> STARTUP MOUNT;

Then, create the control file for the standby database, and open the primary database to user access, as shown in the following example:

SQL> ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/tmp/boston.ctl';
SQL> ALTER DATABASE OPEN;

3. Prepare an Initialization Parameter File for the Standby Database

Create a text initialization parameter file (PFILE) from the server parameter file (SPFILE) used by the primary database; a text initialization parameter file can be copied to the standby location and modified. For example:

SQL> CREATE PFILE='/tmp/initboston.ora' FROM SPFILE;

Modifying Initialization Parameters for a Physical Standby Database
.
DB_NAME=chicago 
DB_UNIQUE_NAME=boston
LOG_ARCHIVE_CONFIG='DG_CONFIG=(chicago,boston)'
CONTROL_FILES='/arch1/boston/control1.ctl', '/arch2/boston/control2.ctl' 
DB_FILE_NAME_CONVERT='chicago','boston'
LOG_FILE_NAME_CONVERT= '/arch1/chicago/','/arch1/boston/','/arch2/chicago/','/arch2/boston/'
LOG_ARCHIVE_FORMAT=log%t_%s_%r.arc
LOG_ARCHIVE_DEST_1=  'LOCATION=/arch1/boston/ 
VALID_FOR=(ALL_LOGFILES,ALL_ROLES)  
DB_UNIQUE_NAME=boston' 
LOG_ARCHIVE_DEST_2=  'SERVICE=chicago LGWR ASYNC  VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)   DB_UNIQUE_NAME=chicago'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
STANDBY_FILE_MANAGEMENT=AUTO 
FAL_SERVER=chicago 
FAL_CLIENT=boston

4. Copy Files from the Primary System to the Standby System

Use an operating system copy utility to copy the following binary files from the primary system to the standby system:

Backup DataFile
Standby Control File
Initialization Parameter

5. Set Up the Environment to Support the Standby Database
   1. Create Window Based Service
   2. WINNT> oradim -NEW -SID boston -INTPWD password -STARTMODE manual
   3. Create a password file.
   4. On both the primary and standby sites, use Oracle Net Manager to configure a listener for the respective databases.
   5. % lsnrctl stop
   6. % lsnrctl start
   7. Create Oracle Net service names.
   8. Create a server parameter file for the standby database.

   9. CREATE SPFILE FROM PFILE='initboston.ora';

   • Perform the following steps to create a Windows-based service, create a password file, set up the Oracle Net environment, and create a SPFILE.
6. Start the Physical Standby Database

SQL> STARTUP MOUNT;
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

The statement includes the DISCONNECT FROM SESSION option so that Redo Apply runs in a background session

Test archival operations to the physical standby database.

SQL> ALTER SYSTEM SWITCH LOGFILE;

Verify the Physical Standby Database Is Performing Properly

SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME   2  FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

Force a log switch to archive the current online redo log file.
SQL> ALTER SYSTEM SWITCH LOGFILE;

Verify the new redo data was archived on the standby database.
SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME
  2>  FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

Verify new archived redo log files were applied.
SQL> SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG   2  ORDER BY SEQUENCE#;

Post-Creation Steps

At this point, the physical standby database is running and can provide the maximum performance level of data protection. The following list describes additional preparations you can take on the physical standby database:

• Upgrade the data protection modeThe Data Guard configuration is initially set up in the maximum performance mode (the default).
• Enable Flashback DatabaseFlashback Database removes the need to re-create the primary database after a failover. Flashback Database enables you to return a database to its state at a time in the recent past much faster than traditional point-in-time recovery, because it does not require restoring datafiles from backup nor the extensive application of redo data. You can enable Flashback Database on the primary database, the standby database, or both.

Data Guard for Oracle Database11g

If you have Oracle Enterprise Database11g installed on your operating system then you can be benefited with the best disaster recovery solution, data protection and comprehensive data availability in the industry.

Data Guard helps to safeguard your precious database from any kind of corruptions, disasters, errors and failures. It not only manages but also provides an automation software infrastructure to create, monitor and maintain enterprise standby databases as synchronized copies from the production database that may be located at a remote location. Data guard would help to prevent any kind of data loss if your production database is corrupted or destroyed due to any unforeseen reason.

Data Guard is a better alternative than older Oracle Real Application Clusters or Oracle RAC. Unlike Oracle RAC, Data Guard is capable to provide you with Disaster Recovery and High Availability requirements. Moreover, Data Guard is designed with such features that it would not let the corruptions to propagate to the stored standby database in Data Guard storage from your primary database. You can use both logical and physical standby databases for productive purposes but would keep them in standby role.

With Data Guard, you can assure of reliable and optimum data availability and protection. You will not have to incur hefty amounts to maintain this level of data security. Moreover, using Data Guard is not complex as its features are very much integrated with Oracle Enterprise Database11g. What is more? You can also use your standby databases for production purposes. It can also be used with various other Oracle Solutions such as Oracle Flashback, Oracle RAC, RMAN etc. to add up to its capabilities. It is also compatible with other Oracle Database11g options such as Oracle Advanced Compression and Oracle Active Data Guard.

Various advantages of Data Guard include its

• Capability to deal with quick switching between primary databases and standby databases. This helps to minimize the downtime you would have wasted when primary database fails to perform.
• Resolve problems like user errors or logical corruptions in primary database. The redo data is always validated before it is applied to the standby database to ensure more security.
• The workload of primary database can also be reduced as all the functions that require read only feature can utilize standby database.

Many people are confused what would happen if the network connection disconnects for sometime between the primary and standby database. Now you need not to worry as all the redo data that was not sent during the disconnection phase is detected and updated by the Data Guard automatically. No manual intervention or support is required in this case. With its simple and centralized management framework or design you can easily monitor and manage various tasks of multiple databases once, you configure your Data Guard for the same.

A Data Guard can support up to nine standby databases and one primary database. It also utilizes feature that compare incoming redo stream and standby database to detect any type of firmware or hardware storage fault. Data Guard also supports for Oracle Active Data Guard and helps to create Snapshot Standby database.

Thanks To anne

Recreate Control Files; How? and When?

Recreate Control Files; How? and When?

When?

The control files of a database store the status of the physical structure of the database. The control file is absolutely crucial to database operation.
Control File contains…

> Database information (RESETLOGS SCN and their time stamp)
> Archive log history
> Tablespace and datafile records
(filenames, datafile checkpoints, read/write status, offline or not)
> Redo Logs (current online redo log)
> Database’s creation date
> database name
> current archive log mode
> Log records (sequence numbers, SCN range in each log)
> RMAN catalog
> Database block corruption information
> Database ID, which is unique to each DB

If the controlfile is lost, it is somewhat difficult to do a recovery because the database cannot be mounted for a recovery. The controlfile must be recreated
When you want to rename your database at that time also u need to recreate control files

How?
You need a create controlfile script for recreating control files.

Enter user-name: sys as sysdba
Enter password:

Connected to:
Oracle9i Enterprise Edition Release 9.0.1.1.1 – Production
With the Partitioning, Oracle Label Security, OLAP and Oracle Data Mining options
JServer Release 9.0.1.1.1- Production

#this is for creating Control File Creation script
SQL> alter database backup controlfile to trace;
Database altered.
SQL>

This will create a trace file in the udump directory.
Im my case it was

C:\oracle\test\udump\ORA03620.TRC
Edit the file to point the path of the datafiles and redologfiles.

CREATE CONTROLFILE REUSE DATABASE “TEST” RESETLOGS ARCHIVELOG
MAXLOGFILES 32
MAXLOGMEMBERS 2
MAXDATAFILES 32
MAXINSTANCES 16
MAXLOGHISTORY 1696
LOGFILE
GROUP 1 ‘C:\ORACLE\TEST\REDO\LOG1.LOG’ SIZE 100M,
GROUP 2 ‘C:\ORACLE\TEST\REDO\LOG2.LOG’ SIZE 100M
DATAFILE
‘C:\ORACLE\TEST\DATA\DATA.DBF’,
‘C:\ORACLE\TEST\TS_TEST1.DBF’,
‘C:\ORACLE\TEST\UNDO.DBF’,
‘C:\ORACLE\TEST\DATA\USER_1.DBF’,
‘C:\ORACLE\TEST\USER_2.DBF’,
‘C:\ORACLE\TEST\USER_UNDO.DBF’
‘C:\ORACLE\TEST\USER_TEMP.TMP’
CHARACTER SET WE8MSWIN1252
;

# Recovery is required if any of the datafiles are restored backups,
# or if the last shutdown was not normal or immediate.
RECOVER DATABASE
# Database can now be opened normally.
ALTER DATABASE OPEN;
# Commands to add tempfiles to temporary tablespaces.
# Online tempfiles have complete space information.
# Other tempfiles may require adjustment.
ALTER TABLESPACE TEMP_TEST ADD TEMPFILE ‘C:\ORACLE\TEST\TMP_TEST.DBF’ REUSE;
# End of tempfile additions.
#

ALTER DATABASE OPEN RESETLOGS;

and rename it to control_trace.sql
Then startup the database in nomount mode and run the control_trace.sql file as sys as sysdba user
SQL*Plus: Release 9.0.1.1.1 – Production on Tue Dec 30 10:45:47 2008

Enter user-name: sys as sysdba
Enter password:
Connected to an idle instance.

SQL> startup nomount
ORACLE instance started.

Total System Global Area 80812648 bytes
Fixed Size 453224 bytes
Variable Size 54525952 bytes
Database Buffers 25165824 bytes
Redo Buffers 667648 bytes
SQL> @ C:\oracle\test\udump\ control_trace.sql

Control file created.

Database altered.

Check the status from v$instance
Check the name from v$controlfile

Create Oracle 9i database in windows manually

Create Oracle 9i database in windows manually

“dbtest” is the database that i am going to create.
create one folder called dbtest (Example: c:\oracle\dbtest)

1. Open the console(Run –> cmd)
2. Set oracle SID as ’set oracle_sid=dbtest’
3. If dbtest windows service is not created then execute this
c:\>oradim -new -sid dbtest -intpwd dbpassword
4. Create a directory called dbtest. In this case I created it in c:\oracle\dbtest
( you can change the location according to yours )
5. Create your initdbtest.ora that I saved it in c:\oracle\Ora91\database.
This is the default location that oracle will check for pfile. Rather you can save it any where but u should mention the file

during startup; startup pfile=path

Following is the static parameter file that I have used in my database creation
Note: All paths that i had given depends on my configuration. Make sure paths which you had given is correct
(This is the simple way to create a parameter File. You can add whichever parameters you want)

##########################################
db_name=dbtest
instance_name=dbtest
control_files=c:\oracle\dbtest\control\c.ctl
background_dump_dest=c:\oracle\dbtest\bdump
core_dump_dest=c:\oracle\dbtest\cdump
user_dump_dest=c:\oracle\dbtest\udump
db_block_size=4096
db_cache_size=33554432
compatible=9.0.1.1.1
undo_management=AUTO
undo_retention=1
undo_tablespace=UNDOTBS
pga_aggregate_target=25165824
sort_area_size=524288
############################################

6. Open Another command prompt
7. Execute this command set oracle_sid=dbtest
8. Execute this command “sqlplus/nolog” to get the sql prompt
9. Conn sys as sysdba ;
SQL> conn sys as sysdba
10. Now Start the instance in nomount mode
SQL> startup nomount
Why are you starting in nomount mode?
Bcoz still we are not created control files. An instance should be started in NOMOUNT mode only
during the database creation and recreation of control files
11. Create a sql script file to create a database.
(Here i m giving you an example )
######################################
create database dbtest
LOGFILE GROUP 1(‘c:/oracle/dbtest/redo/log1.log’) SIZE 100M,
GROUP 2(‘c:/oracle/dbtest/redo/log2.log’) SIZE 100M
DATAFILE ‘c:/oracle/dbtest/data/data.dbf’ SIZE 325M
MAXLOGFILES 3
MAXLOGMEMBERS 2
MAXLOGHISTORY 1
MAXDATAFILES 100
MAXINSTANCES 1
######################################

12. Run the script in sql prompt
SQL>@ “path of the sql file”
once completed this script you can see the control files, redo log file, Alert log file and .dbf files
files are created in c:\oracle\dbtest\…….

13. Now you can shutdown the database
SQL>SHUT
and restart it
c:\>set oracle_sid=dbtest
If dbtest windows service is not yet started start it manually or execute this
c:\>oradim -startup -sid dbtest -intpwd dbpassword
(Note: Here we don’t need to start the database in nomount mode because we have already created control files)
SQL>conn sys as sysdba
password: sys(sys password)
( or you can simply change the mount state rather restarting the database
SQL>alter database open)
14. Execute catalog.sql
SQL>@c:\ora91\rdbms\admin\catalog.sql
15. Execute catproc.sql
SQL>@d:\ora91\rdbms\admin\catproc.sql

Note: if the password file is corrupted you can recreate
the password file, make sure to delete the existing password file.
c:\>orapwd file=d:\ora91\database\PWDdbtest.ora password=dbpassw