How to check that deployed database setup is the RAC(Cluster Database) or NON-RAC(Single Instance) Database?
- Attributes of RAC Database
su – oracle
OS# sqlplus / as sysdba
SQL> show parameter cluster_database
TRUE
SQL> show parameter cluster_database_instances
2
- Attributes of NON-RAC Database
SQL> show parameter cluster_database
NULL
SQL> show parameter cluster_database_instances
NULL
Note:
If you don’t have DB sys password the you can check from the tnsnames.ora or oratab
$ cat $ORACLE_HOME/network/admin/tnsnames.ora
$ cat /etc/oratab or cat /var/opt/oracle/oratab
How to generate the HTML Report of any SQL Query.
Generate HTML Report from Database
SET SERVEROUTPUT ON
SQL>SPOOL ON;
SQL>SPOOL ‘REPORTNAME.HTML’; --Report Name
SQL>SET PAGESIZE 10000;
SQL>SET MARKUP HTML ON ENTMAP OFF;
SQL>SELECT * FROM DUAL; --Copy SQL Query
SQL>SPOOL OFF;
Find the Specified Report from Home Location
OS PROMPT:
ls -ltr
ftp mget
How to display the SQL Query result in single line?
clear columns set colsep "|" set linesize 32767 set pagesize 0 COLUMN MYUSER_IDENTITY FORMAT A20 COLUMN MYCUI FORMAT A20 COLUMN MYRADIUSPOLICY FORMAT A30 COLUMN MYCUSTOMERREPLYITEM FORMAT A50 SELECT MYUSER_IDENTITY,MYCUI,MYRADIUSPOLICY,MYCUSTOMERREPLYITEM FROM MYRADIUSCUSTOMER; Reference http://www.adp-gmbh.ch/ora/sqlplus/column.html http://barrymcgillin.blogspot.in/2011/09/sqlplus-formatting-commands.html
Advertisements
REPORT THIS AD
Find the Database Sessions by schema name wise.
SQL> SELECT USERNAME,STATUS,COUNT(1) FROM V$SESSION GROUP BY USERNAME,STATUS; ---RAC Database SQL> SELECT USERNAME,STATUS,COUNT(1) FROM GV$SESSION GROUP BY USERNAME,STATUS;
Find the OSUSER and MACHINE name of sessions
SELECT USERNAME,STATUS,OSUSER,MACHINE,COUNT(1) FROM GV$SESSION GROUP BY USERNAME,STATUS,OSUSER,MACHINE; Note: desc V$SESSION --RAC Database SELECT USERNAME,STATUS,OSUSER,MACHINE,COUNT(1) FROM GV$SESSION GROUP BY USERNAME,STATUS,OSUSER,MACHINE; Note: desc GV$SESSION
How to check the Database sessions,process and open cursor parameters?
OS# sqlplus / as sysdba SQL> show parameter sessions SQL> show parameter processes SQL> show parameter open_cursors --check the database parameters SQL> show parameter
How to check the Database Alert logs?
Syntax for Oracle10g tail -100 $ORACLE_BASE/admin/$ORACLE_SID/bdump/alert_$ORACLE_SID.log Example tail -100 /u01/app/oracle/admin/eliteaaa/bdump/alert_eliteaaa.log Syntax for Oracle11g tail -100f $ORACLE_BASE/diag/rdbms/$ORACLE_SID/$ORACLE_SID/trace/alert_$ORACLE_SID.log Example tail -100f /u01/app/oracle/diag/rdbms/aaadb/aaadb1/trace/alert_airchn1.log
Verify the size of datafiles
sqlplus / as sysdba SQL> SELECT (BYTES/1024)/1024,NAME FROM V$DATAFILE; SQL> SELECT FILE_NAME,TABLESPACE_NAME,(BYTES/1024)/1024,STATUS,AUTOEXTENSIBLE FROM DBA_DATA_FILES;
How to find the DDL of any Specific Database Object (e.g TABLE,VIEW,SEQUENCE etc …?)
set long 10000
set pagesize 5000
select dbms_metadata.get_ddl('TABLE','EMP') from dual;
How to find the DDL of INDEX of Specific TABLE?
set long 10000
set pagesize 5000
select dbms_metadata.get_dependent_ddl('INDEX','EMP') from dual;
Check the Database Server Status
Check the Database Mode
OS#sqlplus / as sysdba
SQL>select open_mode from v$database;
SQL>exit;
Check Oracle Listener Status
OS#lsnrctl status
#Check Oracle BG Process
OS# ps -ef | grep ora_
OR
OS# ps -ef | grep -i xe_
How to find the Database Name from any Server?
Linux/Solaris .bash_profile OR .profile echo $ORACLE_SID Solaris cat /var/opt/oracle/oratab Linux cat /etc/oratab
Tablespace and User Administration
---drop the user
DROP USER MYUSER CASCADE;
--drop the tablespace
DROP TABLESPACE MYUSER INCLUDING CONTENTS AND DATAFILES;
---Verify the path of the datafile
SELECT NAME FROM V$DATAFILE;
echo $ORACLE_BASE/oradata/$ORACLE_SID/
--Create the tablespace for single instance database
--This is not for RAC Database
CREATE TABLESPACE MYUSER DATAFILE '$ORACLE_BASE/oradata/$ORACLE_SID/MYUSER.dbf' size 100M AUTOEXTEND ON;
---Create the User
CREATE USER MYUSER IDENTIFIED BY MYUSER DEFAULT TABLESPACE MYUSER TEMPORARY TABLESPACE temp
QUOTA UNLIMITED ON MYUSER;
--Give the privileges to user.
GRANT CONNECT,RESOURCE, CREATE ANY VIEW TO MYUSER;
How to find the location of Tablespace’s Datafile
SELECT NAME FROM V$DATAFILE;
How to check that Tablespace is AutoExtend or not?
SELECT TABLESPACE_NAME,AUTOEXTENSIBLE FROM DBA_DATA_FILES;
How to Add New Datafile to Existing Tablespace?
ALTER TABLESPACE MYUSER
ADD DATAFILE '$ORACLE_BASE/oradata/$ORACLE_SID/MYUSER02.dbf' SIZE 100M AUTOEXTEND ON;
How to find the Specific Oracle Data Dictionary ?
SELECT * FROM DICTIONARY; SQL> desc DICTIONARY Name Null? Type ----------------------------------------- -------- ---------------------------- TABLE_NAME VARCHAR2(30) COMMENTS VARCHAR2(4000)
Identifying Open Uncommitted Transactions
Note: RAC Database select s.INST_ID, s.SID, s.SERIAL#,s.USERNAME,s.MACHINE from gv$transaction t , gv$session s where t.INST_ID = s.INST_ID and t.ses_addr = s.SADDR;
To find database redo load per day.
select trunc(completion_time) rundate ,count(*) logswitch ,round((sum(blocks*block_size)/1024/1024)) "REDO PER DAY (MB)" from v$archived_log group by trunc(completion_time) order by 1 desc;
How to monitor the DataPump.
SQL> select sid, serial#, sofar, totalwork,dp.owner_name, dp.state, dp.job_mode
from gv$session_longops sl, gv$datapump_job dp
where sl.opname = dp.job_name and sofar != totalwork;
How to monitor the rman backup.
SQL>alter session set nls_date_format='dd/mm/yy hh24:mi:ss'; SQL> select SID, START_TIME,TOTALWORK, sofar, (sofar/totalwork) * 100 done, sysdate + TIME_REMAINING/3600/24 end_at from v$session_longops where totalwork > sofar AND opname NOT LIKE '%aggregate%' AND opname like 'RMAN%';
How to Kill the app_username sessions:-
SELECT ‘ALTER SYSTEM KILL SESSION ‘ ||chr(39) ||s.sid||chr(44)||s.serial#||chr(39)||’ immediate;’
FROM v$session s JOIN v$process p ON p.addr = s.paddr
WHERE s.type != ‘BACKGROUND’
AND s.username = ‘&app_username’;
SQL> SELECT 'ALTER SYSTEM KILL SESSION ' ||chr(39) ||s.sid||chr(44)||s.serial#||chr(39)||' immediate;' 2 FROM v$session s 3 JOIN v$process p ON p.addr = s.paddr 4 WHERE s.type != 'BACKGROUND' 5 AND s.username = '&app_username'; Enter value for app_username: TESTING old 5: AND s.username = '&app_username' new 5: AND s.username = 'TESTING' 'ALTERSYSTEMKILLSESSION'||CHR(39)||S.SID||CHR(44)||S.SERIAL#||CHR(39)||'IMMEDIAT -------------------------------------------------------------------------------- ALTER SYSTEM KILL SESSION '1093,186' immediate; ALTER SYSTEM KILL SESSION '1067,260' immediate;