HOW OPEN RESETLOGS WITHOUT RECOVERY

Online redo logs are re-created . The log sequence is reset to 1.
If the databaseis running in archive log mode, the archived redo logs should then be deleted. Otherwise, chances are, that Oracle will eventually try to create an archived redo log whose file name already exists.
First i will show its Normal Database this mean not in recovery mode :
sqlplus / as sysdba
Connected to an idle instance.

SQL> ORACLE instance started.

Total System Global Area  838860800 bytes
Fixed Size                  2087672 bytes
Variable Size             213910792 bytes
Database Buffers          616562688 bytes
Redo Buffers                6299648 bytes
Database mounted.
Database opened.
SQL> shutdown
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit
Now Lets Open it :
sqlplus / as sysdba

Connected to an idle instance.

SQL> startup mount
ORACLE instance started.

Total System Global Area  838860800 bytes
Fixed Size                  2087672 bytes
Variable Size             213910792 bytes
Database Buffers          616562688 bytes
Redo Buffers                6299648 bytes
Database mounted.
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> recover database;
ORA-00283: recovery session canceled due to errors
ORA-00264: no recovery required


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> recover database until cancel;
Media recovery complete.
SQL> alter database open resetlogs;

Database altered.

Now the database is open, please do a clear database bounce.

Thanks!

SGA and PGA overview

Memory Parameter Structure in Oracle 11g:

What’s New?

à Prior to 11g the memory parameters in Oracle are individual per each in pfile.ora OR spfile.ora. But Oracle introduced new features in 11g version with parameters so called memory_target and memory_max-target.

à We can use either memory_target OR memory_max_target parameter in the initialization files.

à This feature helps DBA to allocate chunk of memory to an instance without worrying about the all sub-categories of the pfile.ora OR spfile.ora.

How this mechanism Functions?

à We have few parameters in Oracle called sga_target, sga_max_size and pga_aggregate_target etc. The memory for these parameters is individual till 10g

à We can make it centralization using memory_target or memory_max_target from 11g version.

à Oracle takes up memory equal to Memory_Target from OS RAM and manages its resource within itself. So RAM also acts a crucial role here.

à If we set sga_target, sga_max_size and pga_aggegate_target to ‘0’ and set Memory_target to non zero value, then Oracle auto tune the memory for both SGA and PGA with in the limit specified for Memory_Target.

à The memory sharing from memory_Target is 60% to SGA and 40% to PGA from Memory_Target.

à For an instance, I have given 1GB for Memory_Target and Oracle will distribute 60% (614 M) to SGA components and 40% (410 M) to PGA components.

à If we set Sga_Target and Pga_Aggregate_Target to a non-zero value, then Oracle consider these as minimum values for the particular parameter.

à If Sga_Target is set to non-zero value and Pga_Aggregate_Target not set, then Oracle still auto tune the parameter for Pga_Aggregate_target will be the initialization values of

(Memory_Target-Sga_Target).

à It works vies versa. When ISga_Target is not set and Pga_Aggregate_Target set to non-zero values then memory for Sga_Target will be (Memory_Target-Pga_Aggregate_Target.)

Errors and Reasons:

à Error..The most Irritated and Scary word for a DBA.

à If we get error on Initialization parameters i.e pfile.ora or spfile.ora,

Then entire application works on the particular database will get stops and people all around will hit on us to resolve.

à One fine day I have faced the same situation in office. And all developers stopped working due to that.

à I have faced ORA-04031: unable to allocate bytes of data error which means shared memory parameters are over limited on hits.

à So, here am explaining one instance which I faced on one of the development system in BLR (192.168.89.249)

à My server is Oracle11g XE version with Linux and RAM size is 3GB.

Solutions:

à So, I got that error on the database!! What need to do, it is not a big deal.

à Collect the information first:

SQL> show parameter sga;

NAME                                 TYPE        VALUE

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

lock_sga                             boolean     FALSE

pre_page_sga                   boolean     FALSE

sga_max_size                    big integer 412 M

sga_target                          big integer 0

SQL>show parameter memory_target;

NAME                                 TYPE        VALUE

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

memory_target                        big integer 412M

à By observing above details we can see that, less memory allocation is there for memory_target which is not sufficient when huge number of hits are there on database.

à We can simple re-boot the database or slush all the cache, but that is temporary solution.

à So, to fix the permanent solution on it, make sure we have sufficient RAM else database can not start even if we increase the space. The fixing process as follows:

SQL> alter system set memory_target=1G;

SQL>show parameter memory_target scope=pfile;

NAME                                 TYPE        VALUE

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

memory_target                        big integer 412M

à Still showing same?!! Re-boot the database with pfile as below:

SQL> startup pfile='/u01//app/oracle/product/11.2.0/xe/dbs/initXE.ora'

ORACLE instance started.

à Now create spfile from pfile to alter the changes of pfile to spfile.

SQL> create spfile from pfile;

File created.

à Now check the parameters:

SQL> show parameter sga;

NAME                                 TYPE        VALUE

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

sga_max_size                   big integer 1G

sga_target                         big integer 0

SQL> show parameter memory_target;

NAME                                 TYPE        VALUE

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

memory_target                        big integer 1G

SQL> show parameter spfile;

NAME                                 TYPE        VALUE

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

spfile                               string      /u01/app/oracle/product/11.2.0

                                                 /xe/dbs/spfileXE.ora

SQL> show parameter pfile;

NAME                                 TYPE        VALUE

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

spfile                               string      /u01/app/oracle/product/11.2.0

                                                 /xe/dbs/spfileXE.ora

à So issue solved now. Developers can start working on database now!!!

That’s it!!
Thanks!

Recovering datafile from backup RMAN

I have lost or corrupt one of my datafile and  have backup of that datafile.
Lets see how we can do it.
assume that i missed datafile 5.

RMAN>  sql'alter database datafile 5 offline';

RMAN> restore datafile 5;

RMAN> recover datafile 5;

RAMN> sql'alter database datafile 5 online';

That's it!!
Thanks!