11g DBA Certified.

By the Grace of GOD, Today I have passed 1Z0-050 exam Oracle Database 11g: New Features for Administrators. 

I am Oracle 11g Certified DBA Professional now !! 

Thanks.
Rakesh Kumar Soni.

PROCESSES , SESSIONS, TRANSACTIONS

In this post I will show how Oracle automatically changes values of sessions and transactions parameters after you have changed processes parameter. What formula oracle uses to calculate new values for session and transaction parameter. We will first start with some basics of Processes, Sessions, and Transactions.

PROCESSES :
PROCESSES specifies the maximum number of operating system user processes that can simultaneously connect to Oracle. Its value should allow for all background processes such as locks, job queue processes, and parallel execution processes.
The default values of the SESSIONS and TRANSACTIONS parameters are derived from this parameter. Therefore, if you change the value of PROCESSES, you should evaluate whether to adjust the values of those derived parameters.

SESSIONS :
default value (1.1 * PROCESSES) + 5
SESSIONS specifies the maximum number of sessions that can be created in the system. Because every login requires a session, this parameter effectively determines the maximum number of concurrent users in the system. You should always set this parameter explicitly to a value equivalent to your estimate of the maximum number of concurrent users, plus the number of background processes, plus approximately 10% for recursive sessions.

Oracle uses the default value of this parameter as its minimum. Values between 1 and the default do not trigger errors, but Oracle ignores them and uses the default instead.

The default values of the ENQUEUE_RESOURCES and TRANSACTIONS parameters are derived from SESSIONS. Therefore, if you increase the value of SESSIONS, you should consider whether to adjust the values of ENQUEUE_RESOURCES and TRANSACTIONS as well. (Note that ENQUEUE_RESOURCES is obsolete as of Oracle Database 10g release 2 (10.2).)
In a shared server environment, the value of PROCESSES can be quite small. Therefore, Oracle recommends that you adjust the value of SESSIONS to approximately 1.1 * total number of connections.
In database (1.1 * 200) + 5 = 225






TRANSACTIONS :
default value (1.1 * SESSIONS)
TRANSACTIONS specifies the maximum number of concurrent transactions. Greater values increase the size of the SGA and can increase the number of rollback segments allocated. The default value is greater than SESSIONS (and, in turn, PROCESSES) to allow for recursive transactions.
In database value (1.1 * 225) = 247

RMAN Restore using RMAN COLD Backup

I have taken RMAN COLD Backup in my last post, In this post I am going to delete datafile and then I will try to open a database. I will write some steps to recover/restore datafile/database.
---------------------------------------------------------------

Microsoft Windows [Version 5.2.3790](C) Copyright 1985-2003 Microsoft Corp.
C:\Documents and Settings\Administrator>sqlplus
SQL*Plus: Release 10.2.0.1.0 - Production on Mon Feb 6 15:27:19 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Enter user-name: sys as sysdba
Enter password:
Connected to an idle instance.

SQL> startup;
ORACLE instance started.
Total System Global Area 612368384 bytes
Fixed Size 1250428 bytes
Variable Size 226495364 bytes
Database Buffers 377487360 bytes
Redo Buffers 7135232 bytes
Database mounted.

ORA-01157: cannot identify/lock data file 7 - see DBWR trace file
ORA-01110: data file 7: 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\IQRA10G\INDX.DBF'

SQL> shutdown immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine options

C:\Documents and Settings\Administrator>rman
Recovery Manager: Release 10.2.0.1.0 - Production on Mon Feb 6 15:28:58 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.

RMAN> connect target
connected to target database (not started)
RMAN> sql 'alter database mount';
using target database control file instead of recovery catalog
RMAN-00571: ================================================
RMAN-00569:
===== ERROR MESSAGE STACK FOLLOWS =====
RMAN-00571: ================================================
RMAN-03002: failure of sql command at 02/06/2012 15:29:12
RMAN-06403: could not obtain a fully authorized session
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist

RMAN> startup nomount;
Oracle instance started
Total System Global Area 612368384 bytes
Fixed Size 1250428 bytes
Variable Size 226495364 bytes
Database Buffers 377487360 bytes
Redo Buffers 7135232 bytes

RMAN> sql 'alter database mount';
sql statement: alter database mount

RMAN> restore tablespace indx;
Starting restore at 06-FEB-12
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK

channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00007 to D:\ORACLE\PRODUCT\10.2.0\ORADATA\IQRA10G\INDX.DBF

channel ORA_DISK_1: reading from backup piece
D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\IQRA10G\BACKUPSET\2012_02_03\O1
channel ORA_DISK_1: restored backup piece 1
piece
handle=D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\IQRA10G\BACKUPSET\2012_02_03\O1_MF_NNNDF_TAG20120203T173426_7LQO

channel ORA_DISK_1: restore complete, elapsed time: 00:00:56
Finished restore at 06-FEB-12

RMAN> alter databse open;
RMAN-00571: ================================================
RMAN-00569:
===== ERROR MESSAGE STACK FOLLOWS =====
RMAN-00571: ================================================
RMAN-00558: error encountered while parsing input commands
RMAN-01009: syntax error: found "identifier": expecting one of: "clone, database"
RMAN-01008: the bad identifier was: databse
RMAN-01007: at line 1 column 7 file: standard input

RMAN> recover tablespace indx;
Starting recover at 06-FEB-12
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:03
Finished recover at 06-FEB-12

RMAN> alter databse open;
RMAN-00571: ================================================
RMAN-00569:
===== ERROR MESSAGE STACK FOLLOWS =====
RMAN-00571: ================================================
RMAN-00558: error encountered while parsing input commands
RMAN-01009: syntax error: found "identifier": expecting one of: "clone, database"
RMAN-01008: the bad identifier was: databse
RMAN-01007: at line 1 column 7 file: standard input

RMAN> alter database open resetlogs;
RMAN-00571: ================================================
RMAN-00569:
===== ERROR MESSAGE STACK FOLLOWS =====
RMAN-00571: ================================================
RMAN-03002: failure of alter db command at 02/06/2012 15:37:12
ORA-01139: RESETLOGS option only valid after an incomplete database recovery

RMAN> recover database;
Starting recover at 06-FEB-12
using channel ORA_DISK_1
starting media recoverymedia recovery complete, elapsed time: 00:00:00
Finished recover at 06-FEB-12

RMAN> alter database open;
database opened
RMAN>

RMAN Cold Backup - NO Archive Log Mode

Microsoft Windows [Version 5.2.3790](C) Copyright 1985-2003 Microsoft Corp.
C:\Documents and Settings\Administrator>sqlplus
SQL*Plus: Release 10.2.0.1.0 - Production on Fri Feb 3 13:14:42 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Enter user-name: sys as sysdba
Enter password:

Connected to:Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine options

SQL> archive log list
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 54
Current log sequence 56
SQL>
SQL> Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine options

C:\Documents and Settings\Administrator>rman
Recovery Manager: Release 10.2.0.1.0 - Production on Fri Feb 3 13:17:45 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.

RMAN> connect target
connected to target database: STAND(DBID=183739979)
RMAN>
RMAN> show all;
using target database control file instead of recovery catalog
RMAN configuration parameters are:CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO 'D:\ORACLE\PRODUCT\10.2.0\DB_2\DATABASE\SNCFSTAND.ORA'; # default

RMAN> list backup;

RMAN> list backup summary;

RMAN> backup full database;
Starting backup at 03-FEB-12using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 02/03/2012 13:43:08
ORA-19602: cannot backup or copy active file in NOARCHIVELOG mode
continuing other job steps, job failed will not be re-run
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including current control file in backupset
including current SPFILE in backupset
channel ORA_DISK_1: starting piece 1 at 03-FEB-12
channel ORA_DISK_1: finished piece 1 at 03-FEB-12
piece handle=D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\STAND\BACKUPSET\2012_02_03\O1_MF_NCSNF_TAG20120203T134307_7LQ7JXY6_.BKP tag=TAG20120203T134307 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
RMAN-00571: ===========================================================
RMAN-00569:
===============
ERROR MESSAGE STACK FOLLOWS
===============
RMAN-00571: ===========================================================
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 02/03/2012 13:43:08
ORA-19602: cannot backup or copy active file in NOARCHIVELOG mode

RMAN> shutdown immediate;
database closed
database dismounted
Oracle instance shut down

RMAN> startup nomount;
connected to target database (not started)
Oracle instance started
Total System Global Area 612368384 bytes
Fixed Size 1250428 bytes
Variable Size 222301060 bytes
Database Buffers 381681664 bytes
Redo Buffers 7135232 bytes

RMAN> backup full database;
Starting backup at 03-FEB-12
allocated channel: ORA_DISK_1channel ORA_DISK_1: sid=157 devtype=DISK
RMAN-00571: ===========================================================
RMAN-00569:
===============
ERROR MESSAGE STACK FOLLOWS
===============
RMAN-00571: ===========================================================
RMAN-03002: failure of backup command at 02/03/2012 13:44:36
ORA-01507: database not mounted

RMAN> alter database mount;
database mounted
released channel: ORA_DISK_1

RMAN> backup full database;
Starting backup at 03-FEB-12allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00006 name=D:\ORACLE\PRODUCT\10.2.0\ORADATA\STAND\STAND.DBF
input datafile fno=00007 name=D:\ORACLE\PRODUCT\10.2.0\ORADATA\STAND\INDX.DBF
input datafile fno=00001 name=D:\ORACLE\PRODUCT\10.2.0\ORADATA\STAND\SYSTEM01.DBF
input datafile fno=00003 name=D:\ORACLE\PRODUCT\10.2.0\ORADATA\STAND\SYSAUX01.DBF
input datafile fno=00002 name=D:\ORACLE\PRODUCT\10.2.0\ORADATA\STAND\UNDOTBS01.DBF
input datafile fno=00005 name=D:\ORACLE\PRODUCT\10.2.0\ORADATA\STAND\EXAMPLE01.DBF
input datafile fno=00004 name=D:\ORACLE\PRODUCT\10.2.0\ORADATA\STAND\USERS01.DBF
channel ORA_DISK_1: starting piece 1 at 03-FEB-12
channel ORA_DISK_1: finished piece 1 at 03-FEB-12
piece handle=D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\STAND\BACKUPSET\2012_02_03\O1_MF_NNNDF_TAG20120203T134503_7LQ7NJHZ_.BKP tag=TAG20120203T134503 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:03:35
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including current control file in backupset
including current SPFILE in backupset
channel ORA_DISK_1: starting piece 1 at 03-FEB-12
channel ORA_DISK_1: finished piece 1 at 03-FEB-12
piece handle=D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\STAND\BACKUPSET\2012_02_03\O1_MF_NCSNF_TAG20120203T134503_7LQ7V9WJ_.BKP tag=TAG20120203T134503 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 03-FEB-12

RMAN> backup current controlfile;
Starting backup at 03-FEB-12
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including current control file in backupset
channel ORA_DISK_1: starting piece 1 at 03-FEB-12
channel ORA_DISK_1: finished piece 1 at 03-FEB-12
piece handle=D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\STAND\BACKUPSET\2012_02_03\O1_MF_NCNNF_TAG20120203T140027_7LQ8KDTF_.BKP tag=TAG20120203T140027 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01Finished backup at 03-FEB-12

RMAN> sql 'alter database backup controlfile to trace';
sql statement: alter database backup controlfile to trace

RMAN> sql 'create pfile from spfile';
sql statement: create pfile from spfile
RMAN>