how to create a controlfile

CREATE CONTROLFILE SET DATABASE "fsdemo" RESETLOGS FORCE LOGGING NOARCHIVELOG
MAXLOGFILES 50
MAXLOGMEMBERS 5
MAXDATAFILES 100
MAXINSTANCES 1
MAXLOGHISTORY 453
LOGFILE
GROUP 1 'E:\oracle\product\10.2.0\oradata\fsdemo\redo01.log' SIZE 200M,
GROUP 2 'E:\oracle\product\10.2.0\oradata\fsdemo\redo02.log' SIZE 200M,
GROUP 3 'E:\oracle\product\10.2.0\oradata\fsdemo\redo03.log' SIZE 200M
DATAFILE
'E:\oracle\product\10.2.0\oradata\fsdemo\system01.dbf',
'E:\oracle\product\10.2.0\oradata\fsdemo\undotbs01.dbf',
'E:\oracle\product\10.2.0\oradata\fsdemo\users01.dbf'
CHARACTER SET WE8ISO8859P1;

"fsdemo" is the  database name for which u wanna create controlfile

"E:\oracle\product\10.2.0\oradata\fsdemo\" this is the path where your logfile and datafile are store .
just past this file and edit path and name . and save it as "control.sql' in notepad
run this file from sql promt
SQL>@control.sql

remember your database should be in no mount stage  for creating controlfile

any mistake is there or u wanna give any suggestion so please write to me ............

RENAME DATABASE AND INSTACE

RENAME DATABASE AND INSTACE

Let suppose  your db name is TEST and wanna change your db to NEWTEST

C:\> set ORACLE_SID=TEST

C:\> sqlplus  sys/sys as sysdba

SQL>select name from v$database;

Name

----------

TEST

SQL>alter database backup controlfile to trace as 'c:\c.txt' reuse;

SQL> shutdown immediate;

copy and past your old controlfile to another loction and edit your INITTEST.ora in this change your  "db_name=NEWTEST"

SQL>startup nomount pfile='D:\oracle\product\10.2.0\db_1\database\inittest.ora'

(the path where you save your pfile )

now create the new control file ..........

SQL>alter database open resetlogs;

SQL>select name from v$database;

Name

----------

NEWTEST

****this will change only your db name not instance name 

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

TO CHANGE  INSTANCE  INSTANCE NAME 

SQL> show parameter db_name;

NAME                                 TYPE        VALUE

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

db_name                              string           newtest

SQL>show parameter instance_name;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ---------------
instance_name                        string      test 

if we are using spfile  we need to do ........

SQL> alter system reset instance_name scope= spfile sid ='*";

SQL> startup force ; 

BUT If we are using PFILE then....

In this case:
 remove instance_name parameter from PFILE

 shutdown/restart instance 

c:\> set ORACLE_SID=NEWTEST

if instance is  running in LINUX then every thing is done .

But in windows we need to crate through ORADIM

for ORADIM

create new instance -

c:\>E:\oracle\product\10.2.0\db_1\bin\oradim -new -sid NEWTEST -SRVC OracleServiceNEWTEST 

    -STARTMODE auto -SRVCSTART system -PFILE

                 e:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\INITTEST.ORA

         

 "    e:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\INITTEST.ORA"  this is the path where your pfile is saved.

    This will create a new instance .

Now delete your old instance 

c:\>E:\oracle\product\10.2.0\db_1\bin\oradim -DELETE -SID TEST

Until you did not delete your old instance  you always got error so first delete your old instance and then check your db name and instance name ...

any mistake is there or u wanna give any suggestion so please write to me ............

HOT BACKUP

set your archive log on

SQL> shutdown abort ;

SQL> startup mount;

SQL>alter database archivelog;

you got an  error cose  we use shutdown abort so never use shutdown abort if u wanna set archive log
SQL>shutdown immediate ;

SQL>startup mount ;

SQL >alter database archivelog;

SQL>alter database open;

SQL>alter system switch logfile;

SQL>exit

SQL>rman target/

RMAN> backup database plus archivelog;


done

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

Restore database 

SQL >  SHUTDOWN

SQL > EXIT

C:/>RMAN TARGET /

RMAN>   ALTER DATABASE  NOMOUNT ;

RMAN>  RESTORE CONTROLFILE FROM  'D:\BACKUP\C-33354'

** 'D:\BACKUP\C-33354' this is the path where u store ur backup .and C-33354 this the controll file use ur contoll file name .

controll file name always start with 'c-'

RMAN>ALTER DATABASE MOUNT ;

RMAN>RESTORE DATABASE;

RMAN> RECOVER DATABASE;

RMAN>ALTER DATABASE OPEN RESETLOGS;

any mistake is there or u wanna give any suggestion so please write to me ............

cold backup database

Cold Backup using RMAN

Database should be in archive mode
check the database is up or not

c:\> RMAN TARGET /

RMAN:\> SHOW ALL ;

to check configuration of RMAN  . Configure three things in this configuration

RMAN:\>  CONFIGURE CONTROLFILE AUTOBACKUP ON;

RMAN:\>  CONFIGURE DEVICE TYPE DISK FORMAT   'D:\BACKUP\%F';

** this is the path where u wanna take backup

RMAN:\>  CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT 'D:\BACKUP\%U';

RMAN:\>  SHUTDOWN IMMEDIATE ;

RMAN:\>  STARTUP MOUNT;

RMAN:\>  BACKUP DATABASE;

RMAN:\>  ALTER DATABASE OPEN;

--------------------------------------------------------------------------------------------------------
Restore database 



SQL >  SHUTDOWN

SQL > EXIT

C:/>RMAN TARGET /

RMAN>   ALTER DATABASE  NOMOUNT ;

RMAN>  RESTORE CONTROLFILE FROM  'D:\BACKUP\C-33354'

** 'D:\BACKUP\C-33354' this is the path where u store ur backup .and C-33354 this the controll file use ur contoll file name .

controll file name always start with 'c-'

RMAN>ALTER DATABASE MOUNT ;

RMAN>RESTORE DATABASE;

RMAN> RECOVER DATABASE;

RMAN>ALTER DATABASE OPEN RESETLOGS;





any mistake is there or u wanna give any suggestion so please write to me ............

CHECK POINTS

SOME POINTS ON CHECK POINTS

Incremental Checkpoint: Incremental checkpointing improves the performance of crash and instance recovery (but not media recovery). An incremental checkpoint records the position in the redo thread (log) from which crash/instance recovery needs to begin. This log position is determined by the oldest dirty buffer in the buffer cache. The incremental checkpoint information is maintained periodically with minimal or no overhead during normal processing.

Partial Checkpoint: When backup of a tablespace in an open database begins, a partial checkpoint is performed by every instance for the datafiles in that tablespace

Full Checkpoint: In a full checkpoint, the database server flushes all modified pages in the shared-memory buffer pool to disk. When a full checkpoint completes, all physical operations are complete, the MLRU queue is empty, and the database server is said to be physically consistent.

Fast_Start_MTTR_Target cause database checkpoint to occur whenever the expected time to recover the instance exceeds 5 seconds. So just check out your database for it.

any mistake is there or u wanna give any suggestion so please write to me ............

Background process

10g new background processes

With 10g many new background processes were introduced.
This note highlights those.

MMAN

Memory Manager (MMAN) coordinates the sizing of different memory components within SGA. MMAN keeps a track of sizes of components and allocates/de-allocates memory based on their usage. This process is used only when you enable Automatic Shared Memory Management.

RVWR(Recovery Writer.)
Process responsible for writing flashback logs which stores pre-image of data-blocks.

These logs can be used to flash back database to past point in time. It proves very handy specially in situation of logical data corruptions like an accidental drop/truncate of a table or commit of an unwanted transaction.

CTWR

Change Tracking Writer (CTWR) Process introduced in 10g to track changes to block to assist fast RMAN incremental backups.

MMNL

The Memory Monitor Light (MMNL) process introduced in 10g is responsible for writing out statistics buffer to disk as needed. It works with Automatic Workload Repository (AWR)

MMON

The memory monitor (MMON) process was introduced in 10g is responsible for gathering statistics for Automatic Workload Repository. This helps Oracle doing automatic problem detection and self tuning.


M000

MMON background slave (m000) processes.

RBAL
RBAL (Re-balancer) is the ASM related process that performs rebalancing of disk resources controlled by ASM.

ARBx
These processes are managed by the RBAL process and are used to do the actual rebalancing of ASM controlled disk resources. The number of ARBx processes invoked is directly influenced by the asm_power_limit parameter.

ASMB
The ASMB process is used to provide information to and from the Cluster Synchronization Services used by ASM to manage the disk resources. It is also used to update statistics and provide a heartbeat mechanism.----------------------------------------------------------------------------------------------------------
11g:

The following process are added in 11g as new background processes.

1 dbrm DB resource manager
2 dia0 Diagnosability process
3 fbda Flashback data archiver process
4 vktm Virtual Timekeeper
5 w000 Space Management Co-ordination process
6 smc0 Space Manager process

NOTE : The above six are mandatory processes.

But 11g has 56 new processes added which can be queried using

select name,description from V$bgprocess;

10g background processes:

MMAN

Memory Manager (MMAN) coordinates the sizing of different memory components within SGA. MMAN keeps a track of sizes of components and allocates/de-allocates memory based on their usage. This process is used only when you enable Automatic Shared Memory Management.


RVWR

Process responsible for writing flashback logs which stores pre-image of data-blocks.
These logs can be used to flash back database to past point in time. It proves very handy specially in situation of logical data corruptions like an accidental drop/truncate of a table or commit of an unwanted transaction.

CTWR

Change Tracking Writer (CTWR) Process introduced in 10g to track changes to block to assist fast RMAN incremental backups.

MMNL

The Memory Monitor Light (MMNL) process introduced in 10g is responsible for writing out statistics buffer to disk as needed. It works with Automatic Workload Repository (AWR)

MMON

The memory monitor (MMON) process was introduced in 10g is responsible for gathering statistics for Automatic Workload Repository. This helps Oracle doing automatic problem detection and self tuning.

M000

MMON background slave (m000) processes.

RBAL

RBAL (Re-balancer) is the ASM related process that performs rebalancing of disk resources controlled by ASM.

ARBx

These processes are managed by the RBAL process and are used to do the actual rebalancing of ASM controlled disk resources. The number of ARBx processes invoked is directly influenced by the asm_power_limit parameter.

ASMB

The ASMB process is used to provide information to and from the Cluster Synchronization Services used by ASM to manage the disk resources. It is also used to update statistics and provide a heartbeat mechanism.


 use link to see how architecture work
http://www.youtube.com/watch?v=meWIB3yt96U

Errors

GETTING THIS ERROR
ORA-01157:cannot identify/lock data file 1 - see DBWR trace file
ORA-01110:data file 1: 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\HRMSD91\SYSTEM01.DBF'

__________________________________________________________________________________


ERROR due to flash recovery area  and solution for it :



SQL> conn sys as sysdba
Enter password:
Connected to an idle instance.
SQL> startup
ORACLE instance started.

Total System Global Area  608174080 bytes
Fixed Size                  1292036 bytes
Variable Size             167774460 bytes
Database Buffers          436207616 bytes
Redo Buffers                2899968 bytes
Database mounted.
ORA-16038: log 3 sequence# 430 cannot be archived
ORA-19809: limit exceeded for recovery files
ORA-00312: online log 3 thread 1:
'D:\ORACLE\PRODUCT\10.2.0\ORADATA\HRM\REDO03.LOG'


SQL> alter system switch logfile
  2  ;
alter system switch logfile
*
ERROR at line 1:
ORA-01109: database not open


SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     430
Next log sequence to archive   430
Current log sequence           432

SQL> show parameter db_recovery_file_dest

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      D:\oracle\product\10.2.0\flash
                                                 _recovery_area
db_recovery_file_dest_size           big integer 3G
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Pr
oduction
With the Partitioning, OLAP and Data Mining options

C:\Documents and Settings\Sundaresh>set oracle_sid=hrm

C:\Documents and Settings\Sundaresh>sqlplus

SQL*Plus: Release 10.2.0.3.0 - Production on Tue Sep 28 12:00:44 2010

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

Enter user-name: sys as sysdba
Enter password:

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> select name from v$database;

NAME
---------
HRM


SQL> alter system set db_recovery_file_dest_size=2G scope=both;

System altered.

SQL> ALTER DATABASE OPEN;
ALTER DATABASE OPEN
*
ERROR at line 1:
ORA-16014: log 3 sequence# 430 not archived, no available destinations
ORA-00312: online log 3 thread 1:
'D:\ORACLE\PRODUCT\10.2.0\ORADATA\HRM\REDO03.LOG'


SQL> alter system set db_recovery_file_dest_size=5g
  2  ;

System altered.

SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01139: RESETLOGS option only valid after an incomplete database recovery


SQL> alter database open ;

Database altered.