How to take Oracle System State Dump?
a- Using SQL*Plus connect as "/ AS SYSDBA"; if its not possible, use "sqlplus -prelim / as sysdba"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:
To collect systemstate, please run the following on one instance - cluster wide systemstate will be generated:
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=
#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:
=====================
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
How to find cluster name for RAC database?
CRS_HOME/bin/cemutlo -n
How to disable/enable automatic statistics gathering job?
BEGIN DBMS_SCHEDULER.DISABLE('GATHER_STATS_JOB');END;/
BEGIN DBMS_SCHEDULER.enable('GATHER_STATS_JOB');END;/
How to modify snapshot interval and retention setting for AWR in 11g?
--=>Check the existing setting of AWR snapshot interval and retentionselect * from DBA_HIST_WR_CONTROL;
BEGIN DBMS_WORKLOAD_REPOSITORY.modify_snapshot_settings( retention => 21600, -- Minutes (= 15 Days). Current value retained if NULL. interval => 30); -- Minutes. Current value retained if NULL.END;/
--=>Check the AWR snapshot interval and retention after modificationselect * from DBA_HIST_WR_CONTROL;
How to Extract DDL for Tablespaces in SQL*Plus?
set long 99999 head off pagesize 0 linesize 144
select dbms_metadata.get_ddl('TABLESPACE',tablespace_name)';' from dba_tablespaces;
set long 200000 pages 0 lines 131 doc off 2 column txt format a121 word_wrapped
set long 200000 pages 0 lines 131 doc off 2 column txt format a121 word_wrapped
select dbms_metadata.get_ddl('MATERIALIZED_VIEW','EFF_ASSES_MV','CIHMS') from dual;
How to enable Active Data Guard in 11g?
Enabling Active Data Guard The general process of enabling Active Data Guard is as simple as opening the standby database in read-only mode and starting Redo Apply. This section describes how to enable Active Data Guard on a standby database.
1. If the standby instance and redo apply have been cleanly shut down
a. Using SQL*Plus
i. Start the standby instance in read-only mode.
SQL> startup
ii. Once the database is open, start Redo Apply:
SQL> recover managed standby database disconnect using current logfile;
2. If the standby database is mounted and redo apply is running using the following steps
a. Using SQL*Plus
i. Stop redo apply
SQL> recover managed standby database cancel;
ii. Open the database read only
SQL> alter database open read only;
iii. Once the database is open, start redo apply:
SQL> recover managed standby database disconnect using current logfile;
How to enable Active Data Guard in 11g?
Enabling Active Data Guard The general process of enabling Active Data Guard is as simple as opening the standby database in read-only mode and starting Redo Apply. This section describes how to enable Active Data Guard on a standby database.
1. If the standby instance and redo apply have been cleanly shut down
a. Using SQL*Plus
i. Start the standby instance in read-only mode.
SQL> startup
ii. Once the database is open, start Redo Apply:
SQL> recover managed standby database disconnect using current logfile;
2. If the standby database is mounted and redo apply is running using the following steps
a. Using SQL*Plus
i. Stop redo apply
SQL> recover managed standby database cancel;
ii. Open the database read only
SQL> alter database open read only;
iii. Once the database is open, start redo apply:
SQL> recover managed standby database disconnect using current logfile;
How to findout the gap in Standby database?
Run below query:
SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied",(ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference" FROM (SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH, (SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL WHERE ARCH.THREAD# = APPL.THREAD# ORDER BY 1;
How to speed up the Recovery process in Standby database?
run the Recovery managed process with parallel, use the DEGREE=cpu*2
recover managed standby database parallel 4 disconnect using current logfile; You can also increase the PARALLEL_EXECUTION_MESSAGE_SIZE to 4096, I think default will be 2K.
alter system set PARALLEL_EXECUTION_MESSAGE_SIZE=4096 scope=spfile;
to make the above command effective you require db reboot
How to validate_Oracle_Database_Packages_Types.?
SQL> select comp_name, version, status,comp_id from dba_registry;
COMP_NAME VERSION STATUS COMP_ID----------------------------------- ------------------------------ -------------------------------------------- ------------------------------Oracle Workspace Manager 10.2.0.4.3 VALID OWMOracle Database Catalog Views 10.2.0.4.0 VALID CATALOGOracle Database Packages and Types 10.2.0.4.0 INVALID CATPROCOracle Real Application Clusters 10.2.0.4.0 VALID RAC
exec DBMS_REGISTRY.valid('CATPROC');
PL/SQL procedure successfully completed.
SQL> SQL> select comp_name, version, status,comp_id from dba_registry;
COMP_NAME VERSION STATUS COMP_ID----------------------------------- ------------------------------ -------------------------------------------- ------------------------------Oracle Workspace Manager 10.2.0.4.3 VALID OWMOracle Database Catalog Views 10.2.0.4.0 VALID CATALOGOracle Database Packages and Types 10.2.0.4.0 VALID CATPROCOracle Real Application Clusters 10.2.0.4.0 VALID RAC
How to get explain plan in SQL*plus?
EXPLAIN PLAN FOR select * from emp;
select * from table(dbms_xplan.display);
How to find Top 10 SQLs?
set markup html on;set long 1000
spool c:\top_sql.htm
select executions, ROUND((cpu_time/1000000),2) total_cpu, ROUND((elapsed_time/1000000),2) total_elapsed, ROUND((cpu_time/1000000)/executions,2) cpu_per_e, ROUND((elapsed_time/1000000)/executions,2) elapsed_per_e, length(sql_fulltext), sql_fulltext from v$sql where executions > 0 and parsing_Schema_name = 'TREASURY_DBA' order by elapsed_time desc
spool off;set markup html off;
Hot to check if RMAN backup is compressed from catalog?
select name, compressed from rc_database a,rc_backup_piece bwhere a.db_key=b.db_key and media is null and handle not like '%_cf%'and handle not like '%autobackup%'and name in ('DB_Name') --db_name list
group by name,compressedorder by 1,2/
How to restore archive logs from RMAN/tape?
CATALOG_DATABASE=CATDB # Name of RMAN Catalog databaseCATALOG_USERNAME=CatUsr# Name of RMAN account for Catalog databaseCATALOG_PASSWORD=CatPass# Password for RMAN account in Catalog db
connect catalog CatUsr/CatPass@CatDb target /
restore archivelog sequence between 193795 and 193797 thread 1;
OR
connect catalog CatUsr/CatPass@CatDb target /
run{
configure channel device type disk format '/oracle/admin/TRBISP/backup/rman/%d_S%s_P%p_%T';
restore archivelog sequence between 193795 and 193797 thread 1;}
run { allocate channel t1 type disk;restore archivelog sequence between 193795 and 193797 thread 1;}
run { allocate channel t1 type disk;restore archivelog sequence between 145694 and 145696 thread 1;}
Hot to register 10g database in 11gR cluster grid?
When I tried to restore 10g database controlfile from newly created 10g home on 11gR2 Grid cluster, received below error:
run{
configure channel device type disk format '/oracle/admin/TRBISP/backup/rman/%d_S%s_P%p_%T';
restore archivelog sequence between 193795 and 193797 thread 1;}
run { allocate channel t1 type disk;restore archivelog sequence between 193795 and 193797 thread 1;}
run { allocate channel t1 type disk;restore archivelog sequence between 145694 and 145696 thread 1;}
Hot to register 10g database in 11gR cluster grid?
When I tried to restore 10g database controlfile from newly created 10g home on 11gR2 Grid cluster, received below error:
RMAN-00571: ===========================================
RMAN-00569: ======== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ==================================================
RMAN-03002: failure of restore command at 11/14/2011 17:00:45
ORA-19504: failed to create file "+SHARED_DATA_DG01/test_db/controlfile/Current.284.716125593"
ORA-17502: ksfdcre:3 Failed to create file +SHARED_DATA_DG01/test_db/controlfile/Current.284.716125593
ORA-15001: diskgroup "SHARED_DATA_DG01" does not exist or is not mounted
ORA-15077: could not locate ASM instance serving a required diskgroup
ORA-29701: unable to connect to Cluster Manager
ORA-19600: input file is control file (/ora01/rman_restore/test_db_c-535817617-20101002-01_cf)
Below are the Cause/Action for ORA-15077 & ORA-29701
$ oerr ora 1507715077, 00000, "could not locate ASM instance serving a required diskgroup"
// *Cause: The instance failed to perform the specified operation because it
// could not locate a required ASM instance.
// *Action: Start an ASM instance and mount the required diskgroup.
Below are the Cause/Action for ORA-15077 & ORA-29701
$ oerr ora 1507715077, 00000, "could not locate ASM instance serving a required diskgroup"
// *Cause: The instance failed to perform the specified operation because it
// could not locate a required ASM instance.
// *Action: Start an ASM instance and mount the required diskgroup.
$ oerr ora 2970129701, 00000, "unable to connect to Cluster Manager"
// *Cause: Connect to CM failed or timed out.
// *Action: Verify that the CM was started. If the CM was not started,
// start it and then retry the database startup. If the CM died
// or is not responding, check the Oracle and CM trace files for
// errors.
// *Cause: Connect to CM failed or timed out.
// *Action: Verify that the CM was started. If the CM was not started,
// start it and then retry the database startup. If the CM died
// or is not responding, check the Oracle and CM trace files for
// errors.
However real fix for the issue as mentioned below:
You need to pin your nodes to 11gR2 clusterware.
->Verify the the node is unppined.
./olsnodes -t -nnod1 1 Unpinned
->Pin the node.
./crsctl pin css -n node1
->Verify the node is pinned.
./olsnodes -t -nnode1 1 Pinned
Note: I have 1 node RAC cluster having only 1 node.
How to get TablesSpace_info?
SET LINE 1000col file_name for a70
select u.tablespace_name,TOTAL_space,free_space,(TOTAL_SPACE-FREE_SPACE)*100/TOTAL_SPACE USED_PCT ,free_space*100/TOTAL_space FREE_PCTfrom(select tablespace_name,sum(bytes)/1024/1024/1024 TOTAL_space from dba_data_files group by tablespace_name) U,(select tablespace_name,sum(bytes)/1024/1024/1024 free_space from dba_free_space group by tablespace_name) Fwhere u.tablespace_name=f.tablespace_name and u.tablespace_name like UPPER('%&TS_NAME%');
select file_name,bytes/1024/1024/1024 size_GB,AUTOEXTENSIBLE from dba_data_files where tablespace_name like UPPER('%&TS_NAME%');
How to generate AWR report?
Oracle provide two scripts to produce workload repository reports (awrrpt.sql and awrrpti.sql). They are similar in format to the statspack reports and give the option of HTML or plain text formats. The two reports give essential the same output but the awrrpti.sql allows you to select a single instance. The reports can be generated as follows.
@$ORACLE_HOME/rdbms/admin/awrrpt.sql
@$ORACLE_HOME/rdbms/admin/awrrpti.sql
You need to pin your nodes to 11gR2 clusterware.
->Verify the the node is unppined.
./olsnodes -t -nnod1 1 Unpinned
->Pin the node.
./crsctl pin css -n node1
->Verify the node is pinned.
./olsnodes -t -nnode1 1 Pinned
Note: I have 1 node RAC cluster having only 1 node.
How to get TablesSpace_info?
SET LINE 1000col file_name for a70
select u.tablespace_name,TOTAL_space,free_space,(TOTAL_SPACE-FREE_SPACE)*100/TOTAL_SPACE USED_PCT ,free_space*100/TOTAL_space FREE_PCTfrom(select tablespace_name,sum(bytes)/1024/1024/1024 TOTAL_space from dba_data_files group by tablespace_name) U,(select tablespace_name,sum(bytes)/1024/1024/1024 free_space from dba_free_space group by tablespace_name) Fwhere u.tablespace_name=f.tablespace_name and u.tablespace_name like UPPER('%&TS_NAME%');
select file_name,bytes/1024/1024/1024 size_GB,AUTOEXTENSIBLE from dba_data_files where tablespace_name like UPPER('%&TS_NAME%');
How to generate AWR report?
Oracle provide two scripts to produce workload repository reports (awrrpt.sql and awrrpti.sql). They are similar in format to the statspack reports and give the option of HTML or plain text formats. The two reports give essential the same output but the awrrpti.sql allows you to select a single instance. The reports can be generated as follows.
@$ORACLE_HOME/rdbms/admin/awrrpt.sql
@$ORACLE_HOME/rdbms/admin/awrrpti.sql
How to find which process locked your id?
SELECT * FROM dba_AUDIT_SESSION ORDER BY TIMESTAMP;
check for the RETURNCODE 1017 & 28000.
ORA-01017 - "invalid username/password; logon denied"
ORA-2800 - "the account is locked"
After analyzing the data returned by the above query, you would be able to figure out, which process and what time locked the id.
SELECT * FROM dba_AUDIT_SESSION ORDER BY TIMESTAMP;
check for the RETURNCODE 1017 & 28000.
ORA-01017 - "invalid username/password; logon denied"
ORA-2800 - "the account is locked"
After analyzing the data returned by the above query, you would be able to figure out, which process and what time locked the id.
Good Post
ReplyDelete