How To- Under Construction!!


How to take Oracle System State Dump?
How to check Sessions using TEMP Space ?
How to perform Character set conversion?
How to drop & cleanup database?
Q5
Q5
Q5
Q5
Q5



How to take Oracle System State Dump?
Whenever , there is a hang, the first and foremost thing that needs to be done is -- taking system state dump.
To collect systemstate, please run the following on one instance - cluster wide systemstate will be generated:


a- Using SQL*Plus connect as "/ AS SYSDBA"; if its not possible, use "sqlplus -prelim / as sysdba"
b- Execute the following commands:
SQL> oradebug setmypid
SQL> oradebug unlimit
SQL> oradebug setinst all
SQL> oradebug -g def dump systemstate 267
... Wait about 2 minutes to give time to identify process state changes.
SQL> oradebug -g def dump systemstate 267

SQL> oradebug -g def hanganalyze 4


How to check Sessions using TEMP Space ?
SELECT b.TABLESPACE , b.segfile# , b.segblk# , ROUND ( ( ( b.blocks * p.VALUE ) / 1024 / 1024 ), 2 ) size_mb , a.SID , a.serial# , a.username , a.osuser , a.program , a.status FROM v$session a , v$sort_usage b , v$process c , v$parameter p WHERE p.NAME = 'db_block_size' AND a.saddr = b.session_addr AND a.paddr = c.addr ORDER BY b.TABLESPACE , b.segfile# , b.segblk# , b.blocks;



How to perform Character set conversion?
#1Run the scrtip to install csscan utility Install scscan utility by running following script.
set oracle_sid=sqlplus /nologSQL> conn / as sysdbaSQL> set TERMOUT ONSQL> set ECHO ONSQL> spool csminst.logSQL> -- note the drop user SQL> --drop user csmig cascade;SQL> @?/rdbms/admin/csminst.sql
#2Run the csscan
csscan \"SYSTEM/passwd AS SYSDBA\" FULL=y TOCHAR=WE8ISO8859P1 ARRAY=1024000 PROCESS =3
Doc:Changing the NLS_CHARACTERSET to AL32UTF8 / UTF8 (Unicode) [ID 260192.1]
run csscan
#3 take full backup
#4 check invalid objects before running csalter
#5 shutdown and restart in restrict mode
#6 run csalter.
#7shudown and startup


How to drop & cleanup database?
Dropping a database involves removing its datafiles, redo log files, control files, and initialization parameter files. The DROP DATABASE statement deletes all control files and all other database files listed in the control file. To use the DROP DATABASE statement successfully, all of the following conditions must apply:
The database must be mounted and closed.
The database must be mounted exclusively--not in shared mode.
The database must be mounted as RESTRICTED.
An example of this statement is:
The DROP DATABASE statement has no effect on archived log files, nor does it have any effect on copies or backups of the database. It is best to use RMAN to delete such files. If the database is on raw disks, the actual raw disk special files are not deleted.
Reference:http://filibeto.org/sun/lib/nonsun/oracle/10.2.0.1.0/B19306_01/server.102/b14231/create.htm#i1009497
=====================
export ORACLE_SID=TRBISD2export ORACLE_HOME=/oracle/product/10gr2AS/inf
select name from v$database;
shutdown immediate;
startup mount;
alter system enable restricted session;
select name,created from v$database;
drop database;
cd /oraclerm -rf oradata
remove instance entry from /etc/oratab
remove $ORACLE_BASE/admin/TEST2 directory
lsnrctl stop LISTENER
kill the processes running for the dropped instance:
ps -aefgrep TEST2
remove oracle home for the TEST2 database.
###For RAC-ASM database:
Note:251412.1 :How to Drop the Target Database Using RMANHow To Drop A RAC Database Using RMAN [ID 732042.1]
1. Shutdown all nodes.2. Now, startup only one instance after editing below parameter in it's initialization parameter file:cluster_database=false3. Startup mount restrict;4. Make sure the sql returns the following.
SQL> select logins,parallel from v$instance;
LOGINS PARALLEL------------------ -------------------RESTRICTED NO
5.RMAN>drop database including backups;

=====Actual Implementaiont===========
#1:srvctl stop database -d
#2:sqlplus '/as sysdba'
#3:startup nomount;
#4:alter system set cluster_database=false scope=spfile;
#5:shutdown database;
#6: Startup mount restrict;
#7:select logins,parallel from v$instance;
Make sure the sql returns the following.
select logins,parallel from v$instance;
LOGINS PARALLEL
------------------ -------------------
RESTRICTED NO
#8: rman
#9: connect target
#10: drop database including backups;
#11: srvctl remove database -d DBNAME
#12: remove instance entry from /etc/oratab on all the nodes
#13:delete all parameter file or password file etc from $ORALCE_HOME/dbs on all the nodes








No comments:

Post a Comment