Database opening with RESETLOGS

2 comments
Whenever we perform incomplete recovery or recovery with a backup control file, you must reset the online logs when you open the database. The new version of the reset database is called a new incarnation.
 

Action performed while opening database base with RESETLOGS options

1.Archives the current online redo logs (if they are accessible) and then erases the contents of the online redo logs and resets the log sequence number to 1. For example, if the current online redo logs are sequence 1000 and 1001 when you open RESETLOGS, then the database archives logs 1000 and 1001 and then resets the online logs to sequence 1 and 2.

2.Creates the online redo log files if they do not currently exist.

3.Reinitializes the control file metadata about online redo logs and redo threads.

4.Updates all current datafiles and online redo logs and all subsequent archived redo logs with a new RESETLOGS SCN and time stamp.

Because the database will not apply an archived log to a datafile unless the RESETLOGS SCN and time stamps match, the RESETLOGS prevents you from corrupting datafiles with archived logs that are not from direct parent incarnations of the current incarnation.
 

2 comments :

Post a Comment

Granting select access to v$ Tables

No comments
To provide grant select access on v$ tables (like v$session,V$ASM_DISK etc) error will encountered as these are synonyms.

The grant has to be provided on its views like (v_$session,V_$ASM_DISK etc).
After this grant the user can view details from these tables.

Example:

grant select on V_$ASM_DISK to hsingh;
grant select on V_$SESSION to hsingh;

connect hsingh/******
select * from v$session;

Now the details will be visible.

No comments :

Post a Comment

Oracle Grid Home Clone Guide

No comments
Oracle GRID home cloning can be done using  below steps


Steps:

1.Copy source GRID Home to target server.
2.Make sure grid home user and group are properly setup.
3.Set proper .profile or .bash_profile to setup environment variable.
4.Make sure proper inventory location is pointed in /etc/oraInst.loc file.
5.As a grid osuser run below to detach the grid home from oracle inventory, ORACLE_HOME mentioned is the path of grid home itself.
ORACLE_HOME_NAME can be obtained from oracle inventory <$PATH>/oraInventory/ContentsXML/inventory.xml

cd $GRID_HOME/oui/bin or cd $ORACLE_HOME/oui/bin
./runInstaller -silent -detachHome ORACLE_HOME=/u01/app/grid/product/11.2.0/grid ORACLE_HOME_NAME="Ora11g_prod_grid_home"

6.As a grid osuser run below script

cd $GRID_HOME/clone/bin or cd $ORACLE_HOME/clone/bin
perl clone.pl -silent ORACLE_BASE=/u01/app/grid ORACLE_HOME=/u01/app/grid/product/11.2.0/grid ORACLE_HOME_NAME="Ora11g_dev_grid_home" INVENTORY_LOCATION=/u01/app/oraInventory


7.Execute root.sh as a root or sudo

/u01/app/grid/product/11.2.0/root.sh

8.De-configure the current HA Service Settings as root by force as below from root or sudo

cd $GRID_HOME/crs/install
./roothas.pl -deconfig -force


9.Execute below as root or sudo.The below command has to be executed in single line.

/u01/app/grid/product/11.2.0/grid/perl/bin/perl -I /u01/app/grid/product/11.2.0/grid/perl/lib -I /u01/app/grid/product/11.2.0/grid/crs/install /u01/app/grid/product/11.2.0/grid/crs/install/roothas.pl

10.Update the inventory, run it from grid. This will be required for nodes that are going to remain part of your cluster.In standalone system its not required.

./runInstaller -updateNodelist ORACLE_HOME=/u01/app/grid/product/11.2.0/grid defaultHomeName CLUSTER_NODES= CRS=TRUE

Value of defaultHomeName='Ora11g_dev_grid_home' in my case.

10.Modify host names in listener.ora of GRID & ORACLE_HOME and verify other details like Oracle home path in listener.ora,sqlnet.ora and tnsnames.ora.

11.Add asm,listener and database(optional else we can see a harmless error in Database alert log.)

srvctl add asm
srvctl add listener
srvctl add database -d DEV -o /u01/app/ora11g/product/11.2.0/dbhome_1

12.Start listener,asm services

srvctl start listener
srvctl start asm

13.Make sure ASMoracle cluster synchronization service domain are running

ps -ef| grep -i css

orgrid   17069     1  0 May04 ?        00:07:00 /u01/app/grid/product/11.2.0/grid/bin/cssdagent
orgrid   17086     1  0 May04 ?        00:01:30 /u01/app/grid/product/11.2.0/grid/bin/ocssd.bin

14. ASM instance would be started up without any disk groups mounted.


Status Check: Run below command
cd $GRID_HOME/bin
./crs_stat -t

Mount Diskgroup
Connect as sysasm from grid osuser
alter system set asm_diskstring='/dev/oracleasm';
alter diskgroup Data01 mount;
alter diskgroup Redo01 mount;
alter diskgroup Redo02 mount;
show parameter disk
select * from v$asm_disk;

15.Modify Oracle database pfile as per environment and correct path for control file, db name, dump locations utl_dir_loc etc...
Perform database recovery and & start DB.

 

No comments :

Post a Comment

Finding session using UNDO tablespace

1 comment
The below query can be used to find the session which is consuming more UNDO tablespace.

Query:

SELECT s.sid, s.serial#, s.username, s.program,
t.used_ublk, t.used_urec
FROM v$session s, v$transaction t
WHERE s.taddr = t.addr
ORDER BY 5 desc;



1 comment :

Post a Comment