ÿþ<html> <head> <title>Albert van der Sel - Overview of some often used Oracle SQL Statements</title> </head> <body bgcolor="#FFFFFF" link="blue" alink="blue" vlink="blue"> <h1>Overview of some often used Oracle 9i/10g/11g SQL Statements</h1> <B>Version</B> : 2.4<br> <B>Date</B> : 16/06/2011<br> <B>By</B> : Albert van der Sel<br> <hr/> <br> <ul> <li><B>Type of doc</B>: It's just a list of some often used Oracle SQL Statements. Most will work for 9i, 10g, 11g, but some statements will only work for 10g and later.</li> <li><B>For who</B> : For anyone who likes a list for easy reference.</li> <li><B>Best Usage </B>: Just browse around, or use "find/search" to look for a keyword or identifier.</li> <li><B>Important</B>: This note is not about "best practices". It's only a listing of statements. Also, there is No EM or grid stuff here.</li> </ul> <font face="arial" size=2 color="blue"> <B>Main Contents:</B><br> <br> <B> <A href="#section1"> 1. ENABLE AND DISABLE A CONSTRAINT</A><br> <A href="#section2"> 2. DISABLE AND ENABLE TRIGGER</A><br> <A href="#section3"> 3. PROCESSES AND LOCKS</A><br> <A href="#section4"> 4. QUICK CHECK DATABASE NAME AND INSTANCE NAME</A><br> <A href="#section5"> 5. QUICK CHECK ON DATABASE FILES</A><br> <A href="#section6"> 6. QUICK CHECK ON BACKUP/RECOVERY RELATED EVENTS</A><br> <A href="#section7"> 7. EXAMPLES OF SOME COMMON CREATE STATEMENTS</A><br> <A href="#section8"> 8. GET THE SQL ISSUED AGAINST THE DATABASE</A><br> <A href="#section9"> 9. GET THE SGA PROPERTIES</A><br> <A href="#section10">10. CREATE AN "SPFILE.ORA" FROM AN "INIT.ORA" AND THE OTHER WAY AROUND</A><br> <A href="#section11">11. CREATE A COPY TABLE WITH ALL DATA</A><br> <A href="#section12">12. A FEW SIMPLE WAYS TO TRACE A SESSION</A><br> <A href="#section13">13. A FEW SIMPLE WAYS TO DETECT WAITS</A><br> <A href="#section14">14. CREATE A DATABASE USER</A><br> <A href="#section15">15. FINDING INVALID OBJECTS AND REPAIR</A><br> <A href="#section16">16. CREATING AND REBUILDING INDEXES</A><br> <A href="#section17">17. GETTING PRODUCT/PARAMETER INFORMATION</A><br> <A href="#section18">18. KILLING AN ORACLE SESSION</A><br> <A href="#section19">19. 9i,10g,11g INIT.ORA/SPFILE.ORA initialization parameters</A><br> <A href="#section20">20. DIAGNOSTIC TOOLS IN 9i: A FEW WORDS ON STATSPACK</A><br> <A href="#section21">21. USING A CURSOR IN PL/SQL LOOPS</A><br> <A href="#section22">22. EXECUTING SCRIPTS FROM THE "SQL>" PROMPT</A><br> <A href="#section23">23. USING CONTROLS, AND "FOR.." AND "WHILE.." LOOPS IN PL/SQL</A><br> <A href="#section24">24. HOW TO PUT SQLPLUS OUTPUT IN A SHELL VARIABLE</A><br> <A href="#section25">25. A FEW 9i,10g,11g RMAN NOTES</A><br> <A href="#section26">26. HOW TO VIEW IF THE DATABASE IS DOING A LARGE ROLLBACK</A><br> <A href="#section27">27. A SIMPLE WAY TO CLONE A 9i/10g/11g DATABASE</A><br> </B> <br> <br> <font face="arial" size=2 color="blue"> <h3 id="section1">1. ENABLE AND DISABLE A CONSTRAINT:</h3><br> <font face="courier" size=2 color="black"> -- Disable and enable one Constraint:<br> <br> ALTER TABLE table_name enable CONSTRAINT constraint_name;<br> <br> ALTER TABLE table_name disable CONSTRAINT constraint_name;<br> <br> -- Make a list of statements: Disable and enable ALL Foreign Key (type=R) constraints in one schema (like e.g. HARRY):<br> <br> SELECT 'ALTER TABLE HARRY.'||table_name||' enable constraint '||constraint_name||';'<br> FROM DBA_CONSTRAINTS<br> WHERE owner='HARRY' AND constraint_type='R';<br> <br> SELECT 'ALTER TABLE HARRY.'||table_name||' disable constraint '||constraint_name||';'<br> FROM DBA_CONSTRAINTS<br> WHERE owner='HARRY' AND constraint_type='R';<br> <br> <br> <br> <br> <font face="arial" size=2 color="blue"> <h3 id="section2">2. DISABLE AND ENABLE TRIGGER:</h3><br> <font face="courier" size=2 color="black"> -- Disable and enable one trigger:<br> <br> ALTER TRIGGER trigger_name DISABLE;<br> ALTER TRIGGER trigger_name ENABLE;<br> <br> -- Or in 1 time for all triggers on a table:<br> <br> ALTER TABLE table_name DISABLE ALL TRIGGERS;<br> <br> ALTER TABLE table_name ENABLE ALL TRIGGERS;<br> <br> -- Drop a trigger:<br> <br> DROP TRIGGER trigger_name; <br> <br> <br> <br> <br> <font face="arial" size=2 color="blue"> <h3 id="section3">3. PROCESSES AND LOCKS:</h3><br> <font face="courier" size=2 color="blue"> <B>3.1 QUICK CHECK ON PROCESSES:</B><br> <font face="courier" size=2 color="black"> <br> set linesize=1000<br> set pagesize=1000<br> <br> -- v$session mainly shows characteristics of Oracle Sessions, v$process is more oriented<br> -- to OS processes.<br> -- The below two (similar) queries "connects" the Oracle Session ID (sid) to the OS process (spid):<br> <br> SELECT<br> p.spid AS OS_PID, <br> p.pid,<br> s.sid AS ORACLE_SID, <br> p.addr,s.paddr,<br> substr(s.username, 1, 15) AS DBUSER, <br> substr(s.schemaname, 1, 15), <br> s.command,<br> substr(s.osuser, 1, 15) AS OSUSER, <br> substr(s.machine, 1, 15) AS MACHINE,<br> substr(s.program,1,15) AS PROGRAM <br> FROM v$session s, v$process p<br> WHERE s.paddr=p.addr <br> <br> SELECT <br> p.spid AS OS_PID,<br> s.sid AS ORACLE_SID, <br> substr(s.osuser, 1, 15) AS OSUSER, <br> substr(s.program,1,55) AS PROGRAM,<br> substr(s.module,1,55) AS MODULE<br> FROM v$session s, v$process p<br> WHERE s.paddr=p.addr;<br> <br> -- Short version:<br> select p.spid, s.sid, s.osuser, s.program from<br> v$process p, v$session s where p.addr=s.paddr;<br> <br> -- Listing characteristics of Oracle Sessions (v$session):<br> <br> SELECT sid, serial#, substr(username,1,15), substr(osuser,1,15), LOCKWAIT, substr(program,1,30), substr(module,1,30) <br> FROM v$session;<br> <br> SELECT <br> sid, serial#, command,substr(username, 1, 15), substr(osuser,1,15), sql_address,LOCKWAIT, <br> to_char(logon_time, 'DD-MM-YYYY;HH24:MI'), substr(program, 1, 30)<br> FROM v$session;<br> <br> -- background processes: <br> SQL> SELECT paddr, name, substr(description,1,40) FROM v$bgprocess;<br> <br> SQL> SELECT pid, spid, program, background FROM v$process WHERE BACKGROUND=1;<br> <br> <font face="courier" size=2 color="blue"> <B>3.2 QUICK CHECK ON LOCKS:</B><br> <font face="courier" size=2 color="black"> <br> <br> SELECT d.OBJECT_ID, substr(OBJECT_NAME,1,20), l.SESSION_ID, l.ORACLE_USERNAME, l.LOCKED_MODE<br> FROM v$locked_object l, dba_objects d<br> WHERE d.OBJECT_ID=l.OBJECT_ID;<br> <br> SELECT * FROM DBA_WAITERS;<br> <br> SELECT waiting_session, holding_session, lock_type, mode_held<br> FROM dba_waiters;<br> <br> <font face="courier" size=2 color="blue"> <B>3.3 QUICK CHECK ON TEMP:</B><br> <font face="courier" size=2 color="black"> <br> select total_extents, used_extents, total_extents, current_users, tablespace_name<br> from v$sort_segment;<br> <br> select username, user, sqladdr, extents, tablespace from v$sort_usage;<br> <br> <font face="courier" size=2 color="blue"> <B>3.4 QUICK CHECK ON ACTIVITY UNDO:</B><br> <font face="courier" size=2 color="black"> <br> SELECT a.sid, a.saddr, b.ses_addr, a.username, b.xidusn, b.used_urec, b.used_ublk<br> FROM v$session a, v$transaction b<br> WHERE a.saddr = b.ses_addr;<br> <br> <br> <br> <br> <font face="arial" size=2 color="blue"> <h3 id="section4">4. QUICK CHECK DATABASE NAME AND INSTANCE NAME:</h3><br> <font face="courier" size=2 color="black"> <font face="courier" size=2 color="black"> set linesize=1000<br> set pagesize=1000<br> <br> SELECT * FROM v$database;<br> <br> SELECT DBID, NAME, CREATED, LOG_MODE, OPEN_MODE FROM v$database;<br> <br> SELECT * FROM v$instance;<br> <br> SELECT INSTANCE_NAME, HOST_NAME,VERSION, STARTUP_TIME, STATUS FROM v$instance;<br> <br> <br> <br> <br> <font face="arial" size=2 color="blue"> <h3 id="section5">5. QUICK CHECKS ON DATABASE FILES:</h3><br> <font face="courier" size=2 color="black"> <font face="courier" size=2 color="blue"> <B>5.1 CONTROLFILES:</B><br> <font face="courier" size=2 color="black"> <br> select * from v$controlfile;<br> <br> <font face="courier" size=2 color="blue"> <B>5.2 REDO LOG FILES:</B><br> <font face="courier" size=2 color="black"> <br> select * from v$log;<br> <br> select * from v$logfile;<br> <br> <font face="courier" size=2 color="blue"> <B>5.3 DATA FILES:</B><br> <font face="courier" size=2 color="black"> <br> SELECT file_id, substr(file_name, 1, 70), substr(tablespace_name,1,50), status FROM dba_data_files;<br> <br> SELECT file_id, substr(file_name, 1, 70), bytes, blocks, autoextensible FROM dba_data_files;<br> <br> SELECT file#, status, substr(name, 1, 70) FROM V$DATAFILE;<br> <br> <font face="courier" size=2 color="blue"> <B>5.4 FREE/USED SPACE IN TABLESPACES:</B><br> <font face="courier" size=2 color="black"> <br> SELECT Total.name "Tablespace Name",<br> Free_space, (total_space-Free_space) Used_space, total_space<br> FROM<br> (SELECT tablespace_name, sum(bytes/1024/1024) Free_Space<br> FROM sys.dba_free_space<br> GROUP BY tablespace_name<br> ) Free,<br> (SELECT b.name, sum(bytes/1024/1024) TOTAL_SPACE<br> FROM sys.v_$datafile a, sys.v_$tablespace B<br> WHERE a.ts# = b.ts#<br> GROUP BY b.name<br> ) Total<br> WHERE Free.Tablespace_name = Total.name;<br> <br> <br> <br> <br> <font face="arial" size=2 color="blue"> <h3 id="section6">6. QUICK CHECK ON BACKUP/RECOVERY RELATED EVENTS:</h3><br> <font face="courier" size=2 color="black"> SELECT * FROM v$backup;<br> <br> SELECT file#, status, substr(name, 1, 70), checkpoint_change# FROM v$datafile;<br> <br> SELECT file#, status, checkpoint_change# FROM v$datafile_header;<br> <br> SELECT substr(name,1,60), recover, fuzzy, checkpoint_change#, resetlogs_change#, resetlogs_time<br> FROM v$datafile_header;<br> <br> SELECT name, open_mode, checkpoint_change#, ARCHIVE_CHANGE# FROM v$database;<br> <br> SELECT GROUP#,THREAD#,SEQUENCE#,MEMBERS,ARCHIVED,STATUS,FIRST_CHANGE# FROM v$log;<br> <br> SELECT GROUP#,substr(member,1,70) FROM v$logfile;<br> <br> SELECT * FROM v$log_history;<br> SELECT * FROM v$recover_file;<br> SELECT * FROM v$recovery_log;<br> <br> SELECT first_change#, next_change#, sequence#, archived, substr(name, 1, 50) <br> FROM V$ARCHIVED_LOG;<br> <br> SELECT status,resetlogs_change#,resetlogs_time,checkpoint_change#,<br> to_char(checkpoint_time, 'DD-MON-YYYY HH24:MI:SS') as checkpoint_time,count(*) <br> FROM v$datafile_header<br> group by status, resetlogs_change#, resetlogs_time, checkpoint_change#, checkpoint_time<br> order by status, checkpoint_change#, checkpoint_time ;<br> <br> SELECT LF.member, L.group#, L.thread#, L.sequence#, L.status, <br> L.first_change#, L.first_time, DF.min_checkpoint_change# <br> FROM v$log L, v$logfile LF, <br> (select min(checkpoint_change#) min_checkpoint_change# <br> from v$datafile_header <br> where status='ONLINE') DF <br> WHERE LF.group# = L.group# <br> AND L.first_change# >= DF.min_checkpoint_change#; <br> <br> SELECT * FROM V$RECOVERY_FILE_DEST;<br> <br> SELECT * FROM V$FLASH_RECOVERY_AREA_USAGE;<br> <br> SELECT V1.GROUP#, MEMBER, SEQUENCE#, FIRST_CHANGE# <br> FROM V$LOG V1, V$LOGFILE V2 <br> WHERE V1.GROUP# = V2.GROUP# ; <br> <br> SELECT FILE#, CHANGE# FROM V$RECOVER_FILE; <br> <br> select al.sequence#<br> from v$archived_log al, v$log rl<br> where al.sequence# = rl.sequence# (+)<br> and al.thread# = rl.thread# (+)<br> and ( rl.status = 'INACTIVE'<br> or rl.status is null<br> )<br> and al.deleted = 'NO'<br> order by al.sequence#<br> <br> SELECT RECOVERY_ESTIMATED_IOS FROM V$INSTANCE_RECOVERY;<br> <br> <br> <br> <br> <font face="arial" size=2 color="blue"> <h3 id="section7">7. EXAMPLES OF SOME COMMON CREATE STATEMENTS :</h3> <font face="courier" size=2 color="black"> <font face="courier" size=2 color="blue"> <B>7.1 CREATE TABLESPACE:</B><br> <font face="courier" size=2 color="black"> <br> CREATE TABLESPACE STAGING DATAFILE 'C:\ORADATA\TEST11G\STAGING.DBF' SIZE 5000M<br> EXTENT MANAGEMENT LOCAL AUTOALLOCATE<br> SEGMENT SPACE MANAGEMENT AUTO;<br> <br> CREATE TABLESPACE CISTS_01 DATAFILE '/u07/oradata/spldevp/cists_01.dbf' SIZE 1200M<br> EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K;<br> <br> <font face="courier" size=2 color="blue"> <B>7.2 CREATE TABLE (heap organized):</B><br> <font face="courier" size=2 color="black"> <br> CREATE TABLE employees<br> ( <br> employee_id NUMBER(6),<br> first_name VARCHAR2(20),<br> last_name VARCHAR2(25) NOT NULL,<br> email VARCHAR2(25) NOT NULL,<br> phone_number VARCHAR2(20),<br> hire_date DATE CONSTRAINT emp_hire_date_nn NOT NULL,<br> job_id VARCHAR2(10) CONSTRAINT emp_job_nn NOT NULL,<br> salary NUMBER(8,2),<br> commission_pct NUMBER(2,2),<br> manager_id NUMBER(6),<br> department_id NUMBER(4),<br> CONSTRAINT emp_salary_min CHECK (salary > 0), CONSTRAINT emp_email_uk UNIQUE (email)<br> ) TABLESPACE USERS;<br> <br> ALTER TABLE employees<br> ADD ( <br> CONSTRAINT emp_emp_id_pk PRIMARY KEY (employee_id),<br> CONSTRAINT emp_dept_fk FOREIGN KEY (department_id) REFERENCES departments (department_id),<br> CONSTRAINT emp_job_fk FOREIGN KEY (job_id) REFERENCES jobs (job_id),<br> CONSTRAINT emp_manager_fk FOREIGN KEY (manager_id) REFERENCES employees (manager_id)<br> ) ;<br> <br> CREATE TABLE hr.admin_emp (<br> empno NUMBER(5) PRIMARY KEY,<br> ename VARCHAR2(15) NOT NULL,<br> ssn NUMBER(9) ENCRYPT,<br> job VARCHAR2(10),<br> mgr NUMBER(5),<br> hiredate DATE DEFAULT (sysdate),<br> photo BLOB,<br> sal NUMBER(7,2),<br> hrly_rate NUMBER(7,2) GENERATED ALWAYS AS (sal/2080),<br> comm NUMBER(7,2),<br> deptno NUMBER(3) NOT NULL, <br> CONSTRAINT admin_dept_fkey REFERENCES hr.departments<br> (department_id)) TABLESPACE admin_tbs<br> STORAGE ( INITIAL 50K); <br> <br> <font face="courier" size=2 color="blue"> <B>7.3 OBJECT TABLE:</B><br> <font face="courier" size=2 color="black"> <br> CREATE TYPE department_typ AS OBJECT<br> ( d_name VARCHAR2(100), <br> d_address VARCHAR2(200) );<br> <br> CREATE TABLE departments_obj_t OF department_typ;<br> INSERT INTO departments_obj_t<br> VALUES ('hr', '10 Main St, Sometown, CA');<br> <br> <font face="courier" size=2 color="blue"> <B>7.4 GLOBAL TEMPORARY TABLE:</B><br> <font face="courier" size=2 color="black"> <br> CREATE GLOBAL TEMPORARY TABLE my_temp_table (<br> column1 NUMBER,<br> column2 NUMBER<br> ) ON COMMIT DELETE ROWS;<br> <br> CREATE GLOBAL TEMPORARY TABLE my_temp_table (<br> column1 NUMBER,<br><br> column2 NUMBER ) ON COMMIT PRESERVE ROWS;<br> <br> <font face="courier" size=2 color="blue"> <B>7.5 EXTERNAL TABLE:</B><br> <font face="courier" size=2 color="black"> <br> CREATE OR REPLACE DIRECTORY ext AS 'c:\external';<br> GRANT READ ON DIRECTORY ext TO public;<br> <br> CREATE TABLE ext_tab (<br> empno CHAR(4),<br> ename CHAR(20),<br> job CHAR(20),<br> deptno CHAR(2))<br> ORGANIZATION EXTERNAL (<br> TYPE oracle_loader<br> DEFAULT DIRECTORY ext<br> ACCESS PARAMETERS (<br> RECORDS DELIMITED BY NEWLINE<br> BADFILE 'bad_%a_%p.bad'<br> LOGFILE 'log_%a_%p.log'<br> FIELDS TERMINATED BY ','<br> MISSING FIELD VALUES ARE NULL<br> REJECT ROWS WITH ALL NULL FIELDS<br> (empno, ename, job, deptno))<br> LOCATION ('demo1.dat')<br> )<br> <br> <font face="courier" size=2 color="blue"> <B>7.6 CREATE CLUSTER:</B><br> <font face="courier" size=2 color="black"> <br> Index Cluster:<br> CREATE CLUSTER employees_departments_cluster<br> (department_id NUMBER(4))<br> SIZE 512;<br> <br> CREATE INDEX idx_emp_dept_cluster ON CLUSTER employees_departments_cluster;<br> <br> -- Now, "add" tables to the cluster like for example:<br> <br> CREATE TABLE employees ( ... ) <br> CLUSTER employees_departments_cluster (department_id);<br> <br> CREATE TABLE departments ( ... )<br> CLUSTER employees_departments_cluster (department_id);<br> <br> <font face="courier" size=2 color="blue"> <B>7.7 INDEX-ORGANIZED TABLE:</B><br> <font face="courier" size=2 color="black"> <br> -- Index Organized Tables are tables that, unlike heap tables, are organized like B*Tree indexes. <br> CREATE TABLE labor_hour (<br> WORK_DATE DATE,<br> EMPLOYEE_NO VARCHAR2(8),<br> CONSTRAINT pk_labor_hour <br> PRIMARY KEY (work_date, employee_no))<br> ORGANIZATION INDEX;<br> <br> <font face="courier" size=2 color="blue"> <B>7.8 DATABASE LINK:</B><br> <font face="courier" size=2 color="black"> <br> -- To run queries against remote tables in another database, you can create a "database link": <br> CREATE Public Database Link MYLINK <br> Connect To scott Identified By tiger<br> Using sales;<br> <br> SELECT * from table@MYLINK;<br> <br> Here, "sales" in the upper create statement is the alias as used in your "tnsnames.ora" file.<br> <br> <font face="courier" size=2 color="blue"> <B>7.9 SEQUENCE:</B><br> <font face="courier" size=2 color="black"> <br> CREATE SEQUENCE sequence name<br> INCREMENT BY increment number<br> START WITH start number<br> MAXVALUE maximum value<br> CYCLE ;<br> <br> CREATE SEQUENCE SEQ_SOURCE<br> INCREMENT BY 1<br> START WITH 1<br> MAXVALUE 9999999<br> NOCYCLE;<br> <br> create table SOURCE<br> (<br> id number(10) not null, <br> longrecord varchar2(128));<br> <br> CREATE OR REPLACE TRIGGER tr_source <br> BEFORE INSERT ON SOURCE FOR EACH ROW <br> BEGIN <br> SELECT seq_source.NEXTVAL INTO :NEW.id FROM dual;<br> END;<br> /<br> <br> <font face="courier" size=2 color="blue"> <B>7.10 Partitioned Table:</B><br> <font face="courier" size=2 color="black"> <br> -- RANGE PARTITIONED:<br> <br> CREATE TABLE sales<br> ( invoice_no NUMBER, <br> sale_year INT NOT NULL,<br> sale_month INT NOT NULL,<br> sale_day INT NOT NULL )<br> PARTITION BY RANGE (sale_year, sale_month, sale_day)<br> ( PARTITION sales_q1 VALUES LESS THAN (1999, 04, 01) <br> TABLESPACE tsa,<br> PARTITION sales_q2 VALUES LESS THAN (1999, 07, 01) <br> TABLESPACE tsb,<br> PARTITION sales_q3 VALUES LESS THAN (1999, 10, 01) <br> TABLESPACE tsc,<br> PARTITION sales_q4 VALUES LESS THAN (2000, 01, 01) <br> TABLESPACE tsd ); <br> <br> -- A row with SALE_YEAR=1999, SALE_MONTH=8, and SALE_DAY=1 has a partitioning key of (1999, 8, 1)<br> -- and would be stored in partition SALES_Q3 in Tablespace tsc. <br> <br> -- HASH PARTITIONED:<br> <br> CREATE TABLE scubagear<br> (id NUMBER,<br> name VARCHAR2 (60))<br> PARTITION BY HASH (id)<br> PARTITIONS 4 <br> STORE IN (gear1, gear2, gear3, gear4);<br> <br> -- LIST PARTITIONED:<br> <br> CREATE TABLE q1_sales_by_region<br> (deptno number, <br> deptname varchar2(20),<br> quarterly_sales number(10, 2),<br> state varchar2(2))<br> PARTITION BY LIST (state)<br> (PARTITION q1_northwest VALUES ('OR', 'WA'),<br> PARTITION q1_southwest VALUES ('AZ', 'UT', 'NM'),<br> PARTITION q1_northeast VALUES ('NY', 'VM', 'NJ'),<br> PARTITION q1_southeast VALUES ('FL', 'GA'),<br> PARTITION q1_northcentral VALUES ('SD', 'WI'),<br> PARTITION q1_southcentral VALUES ('OK', 'TX'));<br> <br> -- Composite Range-Hash Partitioning:<br> <br> CREATE TABLE scubagear (equipno NUMBER, equipname VARCHAR(32), price NUMBER) <br> PARTITION BY RANGE (equipno) SUBPARTITION BY HASH(equipname) <br> SUBPARTITIONS 8 STORE IN (ts1, ts2, ts3, ts4) -- tablespaces (PARTITION p1 VALUES LESS THAN (1000),<br> PARTITION p2 VALUES LESS THAN (2000),<br> PARTITION p3 VALUES LESS THAN (MAXVALUE));<br> <br> <br> <br> <br> <font face="arial" size=2 color="blue"> <h3 id="section8">8. GET THE SQL ISSUED AGAINST THE DATABASE:</h3> <font face="courier" size=2 color="black"> -- Could take a lot of performance, depending on uptime and activity.<br> -- Sort of auditing. You get the sql statements.<br> -- Important: First try this query on a test system.<br> <br> -- set linesize 70<br> -- set pagesize 100<br> -- set trimspool on<br> -- spool /tmp/sql.log<br> <br> select v.sql_text, v.FIRST_LOAD_TIME, v.PARSING_SCHEMA_ID, v.DISK_READS, v.ROWS_PROCESSED, v.CPU_TIME,<br> b.username from<br> v$sqlarea v, dba_users b<br> where v.FIRST_LOAD_TIME > '2010-03-15'<br> and v.PARSING_SCHEMA_ID=b.user_id<br> order by v.FIRST_LOAD_TIME ;<br> <br> <br> <br> <br> <font face="arial" size=2 color="blue"> <h3 id="section9">9. GET THE SGA PROPERTIES:</h3> <font face="courier" size=2 color="black"> -- Oracle background processes:<br> <br> SQL> SELECT paddr, name, substr(description,1,40) FROM v$bgprocess;<br> <br> SQL> SELECT pid, spid, program, background FROM v$process WHERE BACKGROUND=1;<br> <br> -- All processes:<br> <br> SQL> SELECT SID,SERIAL#,USERNAME,COMMAND,PROCESS,MODULE,PROGRAM FROM v$session;<br> <br> -- SGA properties:<br> <br> SELECT * FROM v$sga;<br> <br> SELECT * FROM v$sgastat;<br> <br> SELECT * FROM v$pgastat; -- PGA properties<br> <br> SELECT * FROM v$memory_target_advice ORDER BY memory_size;<br> <br> SELECT SUBSTR(COMPONENT,1,20), CURRENT_SIZE, MIN_SIZE, MAX_SIZE, USER_SPECIFIED_SIZE from V$MEMORY_DYNAMIC_COMPONENTS;<br> <br> SELECT sum(bytes) FROM v$sgastat WHERE pool in ('shared pool', 'java pool', 'large pool');<br> <br> SELECT (1-(pr.value/(dbg.value+cg.value)))*100<br> FROM v$sysstat pr, v$sysstat dbg, v$sysstat cg<br> WHERE pr.name = 'physical reads'<br> AND dbg.name = 'db block gets'<br> AND cg.name = 'consistent gets';<br> <br> SELECT * FROM v$sgastat<br> WHERE name = 'free memory';<br> <br> SELECT gethits,gets,gethitratio FROM v$librarycache<br> WHERE namespace = 'SQL AREA';<br> <br> SELECT substr(sql_text,1,40) "SQL", <br> count(*) , <br> sum(executions) "TotExecs"<br> FROM v$sqlarea<br> WHERE executions < 5<br> GROUP BY substr(sql_text,1,40)<br> HAVING count(*) > 30<br> ORDER BY 2;<br> <br> SELECT SUM(PINS) "EXECUTIONS", SUM(RELOADS) "CACHE MISSES WHILE EXECUTING"<br> FROM V$LIBRARYCACHE;<br> <br> <br> <br> <br> <font face="arial" size=2 color="blue"> <h3 id="section10">10. CREATE AN "SPFILE.ORA" FROM AN "INIT.ORA" AND THE OTHER WAY AROUND:</h3> <font face="courier" size=2 color="black"> -- init.ora: traditional ascii format startup configuration file.<br> -- spfile.ora: binary format startup configuration file.<br> -- Both can be used to start the instance. However, preferred is to use the spfile.ora.<br> -- The actual (default) init.ora/spfile.ora file, will use the instance name in it's filename, like initSALES.ora<br> <br> CREATE SPFILE='/opt/oracle/product/10.2/dbs/spfileSALES.ora' <br> FROM PFILE='/opt/oracle/product/10.2/admin/scripts/init.ora';<br> <br> CREATE SPFILE='/opt/app/oracle/product/9.2/dbs/spfilePEGACC.ora' <br> FROM PFILE='/opt/app/oracle/admin/PEGACC/scripts/init.ora';<br> <br> CREATE PFILE='/opt/oracle/product/10.2/admin/scripts/init.ora' <br> FROM SPFILE='/opt/oracle/product/10.2/dbs/spfileSALES.ora';<br> <br> In addition, in Oracle 11g, you can create a pfile (init.ora) file, just from the current settings from memory:<br> <br> CREATE PFILE='/apps/oracle/product/11.1/dbs/init_prod.ora' FROM MEMORY;<br> <br> <br> <br> <br> <font face="arial" size=2 color="blue"> <h3 id="section11">11. CREATE A COPY TABLE WITH ALL DATA:</h3> <font face="courier" size=2 color="black"> -- The CTAS method or "CREATE TABLE AS SELECT" method, allows you to create an exact copy table<br> -- from an original table, with the same columns and datatypes, and all rows,<br> -- but excluding the indexes and constraints.<br> -- This new table will be created "on the fly", so it should not exist beforehand. <br> -- Example CTAS method:<br> <br> CREATE TABLE EMPLOYEE2<br> AS SELECT * FROM EMPLOYEE;<br> <br> -- One alternative method is:<br> -- Obtain the create script of the original table (e.g. using toad).<br> -- Create the new empty table using an other tablename, using that script. -- Then use:<br> <br> INSERT INTO NEW_TABLE SELECT * FROM SOURCE_TABLE; <br> <br> <br> <br> <font face="arial" size=2 color="blue"> <h3 id="section12">12. A FEW SIMPLE WAYS TO TRACE A SESSION:</h3> <font face="courier" size=2 color="black"> -- This section ONLY lists the very very basics on tracing.<br> <br> -- "Old fashion" traces in 9i/10g/11g, uses the Session Identifier (SID) and optionally other parameters like <br> -- the Serial number (serial#), to distinguish between all sessions in the database, and accordingly trace <br> -- that specific session.<br> <br> -- Modern methods allows you to establish a client identifier, or let's you monitor on a module,<br> -- so you are not perse "tied" to the SID anymore.<br> <br> -- From v$session you can find the username, osuser, SID, SERIAL#, program, and module (if needed).<br> -- Like for example (see section 3 for more info):<br> -- <br> -- select sid, serial#, username from v$session;<br> -- select sid, serial#, username, module from v$session;<br> -- <br> -- This will identify the Oracle SID with the username (and optionally the OS user and other).<br> -- If you know a characteristic program, or module (like sqlplus), or user, the SID (Session ID) can be found.<br> -- Especially the "module" field in v$session identifies a certain client program like "sqlplus.exe" or "nav.exe" etc..<br> -- But maybe, already a unique username is sufficient to identify the SID and SERIAL# in your situation.<br> <br> -- In most methods, the 9i, 10g traces will be stored in the USER_DUMP_DEST directory (udump). <br> <br> <br> <br> <font face="courier" size=2 color="blue"> <B>12.1 TRACING ON SESSION ID (SID)</B><br> <font face="courier" size=2 color="black"> <br> <br> <U>1. Using the DBMS_MONITOR.SESSION_TRACE_ENABLE() procedure:</U><br> <br> Example: Suppose you want to trace Session 75 with serial# 4421:<br> <br> exec DBMS_MONITOR.SESSION_TRACE_ENABLE(75,4421);<br> <br> To disable tracing specified in the previous step:<br> <br> exec DBMS_MONITOR.SESSION_TRACE_DISABLE(75,4421);<br> <br> Since almost always the session is qualified "enough" by the SID alone, you can use this as well:<br> <br> exec DBMS_MONITOR.SESSION_TRACE_ENABLE(75);<br> <br> Tracing your session can be done using: <br> exec DBMS_MONITOR.SESSION_TRACE_ENABLE();<br> or<br> exec DBMS_MONITOR.SESSION_TRACE_ENABLE(NULL, NULL);<br> <br> <br> <U>2. Using the DBMS_SYSTEM.SET_EV() procedure:</U><br> <br> Here, You need to know which "event" number you want to trace on, and the level thereoff.<br> For a performance related trace, event 10046 with level 8 (or 12) might be a good choice.<br> Be aware that these traces produce quick growing trace files (especially level 12).<br> Next, you need the SID and SERIAL# of the session you want to trace.<br> The trace information will be written to user_dump_dest.<br> <br> Example:<br> <br> Start the trace on Session 177 (with serial# 55235):<br> <br> exec sys.dbms_system.set_ev(177,55235,10046,8,'');<br> <br> Stop the trace on Session 177 (with serial# 55235):<br> <br> exec sys.dbms_system.set_ev(177,55235,10046,0,'');<br> <br> <br> <U>3. Using the DBMS_System.Set_Sql_Trace_In_Session() procedure:</U><br> <br> A "quite old" and well known other procedure that can be used to trace a Session, is the<br> DBMS_System.Set_Sql_Trace_In_Session(SID, SERIAL#,true|false) procedure.<br> <br> Example:<br> <br> Turn SQL tracing on in session 448. The trace information will get written to user_dump_dest.<br> <br> exec dbms_system.set_sql_trace_in_session(448,2288,TRUE); <br> <br> Turn SQL tracing off in session 448<br> <br> exec dbms_system.set_sql_trace_in_session(448,2288,FALSE); <br> <br> <br> <font face="courier" size=2 color="blue"> <B>12.2 TRACING ON OTHER IDENTIFIERS:</B><br> <font face="courier" size=2 color="black"> <br> Instead of tracing on a known SID, to be able to trace on other "identifiers" is a much wanted feature.<br> As of 10g, the tracing facility has been greatly expanded.<br> New v$ "views" were added, and existing v$ "views" has been expanded to facilitate the new tracing methods.<br> <br> This sub section will hold for 10g/11g.<br> <br> Again let's take a look at DBMS_MONITOR again.<br> Suppose we want to track a program that connects to a RAC cluster. Now, the discussion is not much different<br> in using a standallone instance. Only, you probably know that the v$ views are specific for an instance,<br> while the gv$ views are "global" for all the instances in RAC.<br> <br> The DBMS_MONITOR.serv_mod_act_trace_enable() method allows you to set the tracing on for sessions matching a<br> module, action, or other usable field in gv$session (or v$session).<br> So, suppose we want to generate traces for all SQL*plus sessions that connect to the cluster (RACDEV1) from any instance, <br> we could issue the following command: <br> <br> BEGIN<br> DBMS_MONITOR.serv_mod_act_trace_enable<br> (service_name => 'RACDEV1',<br> module_name => 'SQL*Plus',<br> action_name => DBMS_MONITOR.all_actions,<br> waits => TRUE,<br> binds => FALSE,<br> instance_name => NULL<br> );<br> END;<br> /<br> <br> <br> <br> <br> <font face="arial" size=2 color="blue"> <h3 id="section13">13. A FEW SIMPLE WAYS TO DETECT WAITS:</h3> <font face="courier" size=2 color="black"> -- This section ONLY lists the very very basics on waits.<br> <br> -- set linesize 1000<br> -- set pagesize 1000<br> -- see for yourself where to use substr(field,start,lenght)<br> <br> <font face="courier" size=2 color="blue"> <B>13.1 HOW TO IDENTIFY THE FILES WITH HIGHEST ACTIVITY:</B><br> <font face="courier" size=2 color="black"> <br> -- query on v$filestat, dba_data_files:<br> <br> SELECT v.PHYRDS, v.PHYWRTS, d.TABLESPACE_NAME, d.FILE_NAME<br> FROM V$FILESTAT v, DBA_DATA_FILES d<br> WHERE v.FILE#=d.FILE_ID;<br> <br> <font face="courier" size=2 color="blue"> <B>13.2 HOW TO IDENTIFY ACTIVITY ON CONTROLFILES:</B><br> <font face="courier" size=2 color="black"> <br> SELECT * FROM v$system_event<br> WHERE event LIKE '%control%' ;<br> <br> <font face="courier" size=2 color="blue"> <B>13.3 HOW TO IDENTIFY WAITS OF SESSIONS OR WITH HIGH IO:</B><br> <font face="courier" size=2 color="black"> <br> SELECT s.SID,v.username,v.osuser,v.command,s.BLOCK_GETS,s.PHYSICAL_READS,s.BLOCK_CHANGES,substr(v.module,1,30)<br> FROM v$sess_io s, v$session v<br> where v.sid=s.sid;<br> <br> SELECT SID,OPNAME,SOFAR,TOTALWORK,START_TIME,LAST_UPDATE_TIME,TIME_REMAINING,MESSAGE<br> FROM v$session_longops;<br> <br> -- identify SID's and the objects (or file#, block#) which are involved in waits.<br> <br> SELECT SID, event,p1text,p1,p2text,p2<br> FROM v$session_wait<br> WHERE event LIKE 'db file%'<br> AND state = 'WAITING';<br> <br> -- The p1 and p2 might identify the file# and block# of the object(s).<br> -- Determine the object as follows: suppose you found p1=5 and p2=1178<br> <br> select segment_name, segment_type<br> from dba_extents<br> where file_id = 5 and 1178 between (block_id and block_id + blocks  1);<br> <br> <font face="courier" size=2 color="blue"> <B>13.4 OVERALL WAITS:</B><br> <font face="courier" size=2 color="black"> <br> select<br> event,<br> total_waits,<br> time_waited / 100,<br> total_timeouts,<br> average_wait/100<br> from <br> v$system_event<br> where -- list of not too interresting events<br> event not in ( <br> 'dispatcher timer',<br> 'lock element cleanup', <br> 'Null event',<br> 'parallel query dequeue wait',<br> 'parallel query idle wait - Slaves',<br> 'pipe get',<br> 'PL/SQL lock timer',<br> 'pmon timer', <br> 'rdbms ipc message', <br> 'slave wait',<br> 'smon timer', <br> 'SQL*Net break/reset to client',<br> 'SQL*Net message from client', <br> 'SQL*Net message to client',<br> 'SQL*Net more data to client',<br> 'virtual circuit status',<br> 'WMON goes to sleep'<br> ); <br> <br> SELECT event, total_waits, total_timeouts, time_waited, average_wait <br> FROM v$system_event order by time_waited;<br> <br> SELECT NAME, VALUE from v$sysstat<br> where name like '%db%' or name like '%block%' <br> or name like '%log%' or name like '%cons%' <br> or name like '%undo%' or name like '%write%' or name like '%read%';<br> <br> <br> <br> <br> <font face="arial" size=2 color="blue"> <h3 id="section14">14. CREATE A DATABASE USER:</h3> <font face="courier" size=2 color="black"> <B>-- EXAMPLE STANDARD DATABASE USER:</B><br> <br> CREATE USER albert identified by albert<br> DEFAULT TABLESPACE SALESDATA -- salesdata is a tablespace<br> TEMPORARY TABLESPACE TEMP<br> QUOTA 100M ON SALESDATA<br> QUOTA 20M ON USERS<br> ;<br> <br> -- GRANT standard roles:<br> <br> GRANT connect TO albert;<br> GRANT resource TO albert;<br> <br> -- GRANT specific privileges:<br> <br> GRANT create trigger TO albert;<br> GRANT create sequence TO albert;<br> GRANT create procedure TO albert;<br> <br> -- DROP the user:<br> <br> DROP USER albert cascade;<br> <br> <B>-- EXAMPLE DIRECTORY SERVICE (external) USER:</B><br> <br> CREATE USER global_user<br> IDENTIFIED GLOBALLY AS 'CN=jjones, OU=sales, O=antapex, C=NL'<br> DEFAULT TABLESPACE users<br> QUOTA 500M ON users;<br> <br> <br> <br> <br> <font face="arial" size=2 color="blue"> <h3 id="section15">15. FINDING INVALID OBJECTS AND REPAIR:</h3> <font face="courier" size=2 color="black"> -- Finding invalid objects:<br> <br> SELECT owner, substr(object_name, 1, 30), object_type, created, <br> last_ddl_time, status<br> FROM dba_objects <br> WHERE status='INVALID';<br> <br> -- Recompile packages:<br> <br> SELECT 'ALTER '||decode( object_type,<br> 'PACKAGE SPECIFICATION'<br> ,'PACKAGE'<br> ,'PACKAGE BODY'<br> ,'PACKAGE'<br> ,object_type)<br> ||' '||owner<br> ||'.'|| object_name ||' COMPILE '<br> ||decode( object_type,<br> 'PACKAGE SPECIFICATION'<br> ,'SPECIFACTION'<br> ,'PACKAGE BODY'<br> ,'BODY'<br> , NULL) ||';'<br> FROM dba_objects WHERE status = 'INVALID';<br> <br> -- Using DBMS_UTILITY.compile_schema to compile all objects in a schema:<br> <br> Example:<br> <br> exec DBMS_UTILITY.compile_schema('HARRY');<br> <br> -- Manually recompile objects like views, triggers etc..:<br> <br> ALTER PACKAGE my_package COMPILE;<br> ALTER PACKAGE my_package COMPILE BODY;<br> ALTER PROCEDURE my_procedure COMPILE;<br> ALTER FUNCTION my_function COMPILE;<br> ALTER TRIGGER my_trigger COMPILE;<br> ALTER VIEW my_view COMPILE;<br> <br> <br> <br> <br> <font face="arial" size=2 color="blue"> <h3 id="section16">16. CREATING AND REBUILDING INDEXES:</h3> <font face="courier" size=2 color="black"> <font face="courier" size=2 color="blue"> <B>16.1 EXAMPLES ON HOW TO CREATE INDEXES:</B><br> <font face="courier" size=2 color="black"> <br> -- Examples of ordinary index:<br> <br> CREATE INDEX indx_cust_id ON CUSTOMERS(cust_id);<br> CREATE INDEX indx_cust_id ON CUSTOMERS(cust_id) nologging;<br> CREATE INDEX indx_cust_id ON CUSTOMERS(cust_id) TABLESPACE SALES_INDEX01;<br> CREATE INDEX index_employees ON EMPLOYEES(last_name, job_id, salary); -- multiple columns<br> <br> -- Some special types of indexes:<br> <br> -- reverse key:<br> CREATE INDEX indx_r_name ON RESTAURANTS(r_name) REVERSE;<br> <br> -- bitmap index:<br> CREATE BITMAP INDEX indx_gender ON EMPLOYEE (gender) TABLESPACE EMPDATA;<br> <br> -- function based index:<br> CREATE INDEX emp_total_sal_idx ON employees (12 * salary * commission_pct, salary, commission_pct);<br> <br> <font face="courier" size=2 color="blue"> <B>16.2 EXAMPLES ON HOW TO REBUILD INDEXES:</B><br> <font face="courier" size=2 color="black"> <br> -- Note that rebuilding large, or many, indexes, will generate, or add, to redo logging as well.<br> -- Therefore, in some cases the NOLOGGING keyword maybe of help.<br> -- Also, in case of very large, or a very large number of big indexes, rebuilding will be a major task.<br> -- This note is not about the best practises on when to rebuild indexes.<br> <br> Examples:<br> <br> alter index HARRY.EMPNO_INDEX rebuild;<br> alter index HARRY.EMPNO_INDEX rebuild nologging;<br> alter index HARRY.EMPNO_INDEX rebuild tablespace SALES_INDEX_02; -- rebuild to another tablespace<br> <br> -- Create a list of rebuild index statements:<br> <br> SELECT 'ALTER INDEX HARRY.'||index_name||' REBUILD;' from dba_indexes<br> where owner='HARRY';<br> <br> <br> <br> <br> <font face="arial" size=2 color="blue"> <h3 id="section17">17. GETTING PRODUCT/PARAMETER INFORMATION:</h3> <font face="courier" size=2 color="black"> <font face="courier" size=2 color="blue"> <B>17.1 OPTIONS, VERSION, FEATURES:</B><br> <font face="courier" size=2 color="black"> <br> SELECT * FROM V$VERSION;<br> SELECT * FROM V$OPTION;<br> SELECT * FROM V$LICENSE;<br> SELECT * FROM PRODUCT_COMPONENT_VERSION;<br> <br> <font face="courier" size=2 color="blue"> <B>17.2 COLLATION:</B><br> <font face="courier" size=2 color="black"> <br> SELECT * FROM NLS_DATABASE_PARAMETERS;<br> SELECT * FROM NLS_SESSION_PARAMETERS;<br> SELECT * FROM NLS_INSTANCE_PARAMETERS;<br> <br> <font face="courier" size=2 color="blue"> <B>17.3 PARAMETERS/OPTIONS:</B><br> <font face="courier" size=2 color="black"> <br> SELECT * FROM DBA_REGISTRY;<br> SELECT * FROM v$parameter;<br> <br> <br> <br> <br> <font face="arial" size=2 color="blue"> <h3 id="section18">18. KILLING AN ORACLE SESSION:</h3> <font face="courier" size=2 color="black"> <font face="courier" size=2 color="blue"> <B>18.1 Single instance:</B><br> <font face="courier" size=2 color="black"> <br> From v$session, you can obtain the Oracle Session ID (sid) and serial#.<br> If a Oracle session must be "killed", you can use the following ALTER SYSTEM command:<br> <br> ALTER SYSTEM KILL SESSION 'sid, serial#';<br> <br> Example:<br> <br> ALTER SYSTEM KILL SESSION '77,285';<br> <br> The above statement does not use brutal force to end the session, if currectly transactions are<br> associated with that session. So, it might show up as having a status of "marked for kill".<br> <br> <font face="courier" size=2 color="blue"> <B>18.2 Cluster:</B><br> <font face="courier" size=2 color="black"> <br> In a RAC cluster environment, a third parameter parameter should be added, which is the instance ID:<br> <br> ALTER SYSTEM KILL SESSION 'sid, serial#,@inst_id';<br> <br> <font face="courier" size=2 color="blue"> <B>18.3 Additional clauses:</B><br> <font face="courier" size=2 color="black"> <br> A few additional clauses can be used with the ALTER SYSTEM KILL SESSION statement, like for example:<br> <br> ALTER SYSTEM KILL SESSION 'sid, serial#' IMMEDIATE;<br> <br> The above command, will terminate the session and possible ongoing transactions will roll back.<br> <br> <font face="courier" size=2 color="blue"> <B>18.4 Killing OS processes:</B><br> <font face="courier" size=2 color="black"> <br> If you have identified the "process id" of the OS process which is associated to the Oracle session,<br> you might consider "killing" the process from the OS prompt.<br> <br> Example on Windows:<br> <br> C:\> orakill SALES 22389<br> <br> Example on Unix/Linux:<br> <br> % kill -9 55827<br> <br> <br> <br> <br> <font face="arial" size=2 color="blue"> <h3 id="section19">19. 9i,10g,11g INIT.ORA/SPFILE.ORA parameters:</h3> <font face="courier" size=2 color="black"> <font face="courier" size=2 color="blue"> <B>19.1 ABOUT THE SPFILE/INIT.ORA FILES:</B><br> <font face="courier" size=2 color="black"> <br> The "spfile.ora" (or "init.ora") instance startup configuration file, determines in a large way,<br> how the instance will be configured. Think of the size of SGA memory, how many processes are allowed,<br> location of controlfiles, location of archived redologs etc.. etc..<br> <br> As of 9i, a (binary) spfile.ora is used. However, it's still possible to start a 9i/10g/11g instance<br> using a tradional (ascii) init.ora file.<br> <br> If at a closed instance, you would do this: <br> SQL> connect / as sysdba <br> SQL> startup<br> <br> Then the default spfile.ora would be used.<br> But, at a closed instance, if you would do this:<br> <br> SQL> connect / as sysdba <br> SQL> startup mount pfile=/apps/oracle/product/10.2/admin/test10g/pfile/init.ora<br> SQL> alter database open;<br> <br> Then that specific init.ora would be used to start and configure the instance.<br> <br> Since the spfile.ora is not ascii, it's not easy to view the file <I>directly</I><br>. In section 10, we showed how to create an ascii init.ora file from an spfile.<br> One small advantage from an init.ora, is that it is easy to view it with any editor, or just with shell commands.<br> <br> Here are a few examples again:<br> <br> CREATE SPFILE='/opt/oracle/product/10.2/dbs/spfileSALES.ora' <br> FROM PFILE='/opt/oracle/product/10.2/admin/scripts/init.ora';<br> <br> CREATE SPFILE='/opt/app/oracle/product/9.2/dbs/spfilePEGACC.ora' <br> FROM PFILE='/opt/app/oracle/admin/PEGACC/scripts/init.ora';<br> <br> For viewing settings from the SQL> prompt, you can use the "show parameter" command, like so:<br> <br> <B>SQL> show parameter spfile</B><br> <br> spfile string C:\ORACLE\PRODUCT\10.2\DB_1\DATABASE\SPFILETEST10G.ORA<br> <br> So, that shows you the location of the spfile itself.<br> <br> <B>SQL> show parameter sga</B><br> <br> NAME TYPE VALUE<br> <br> lock_sga boolean FALSE<br> pre_page_sga boolean FALSE<br> sga_max_size big integer 280M<br> sga_target big integer 280M<br> <br> So, the upper command shows you SGA (shared memory) related settings.<br> <br> <br> <font face="courier" size=2 color="blue"> <B>19.2 CLUSTER AND INSTANCE SPECIFIC PARAMETERS:</B><br> <font face="courier" size=2 color="black"> <br> On a simple 10g test database, I created a init.ora file (see section 10), and a small partial section is shown below:<br> <br> *.db_recovery_file_dest_size=2147483648<br> *.dispatchers='(PROTOCOL=TCP) (SERVICE=test10gXDB)'<br> *.job_queue_processes=10<br> *.open_cursors=300<br> *.pga_aggregate_target=96468992<br> *.processes=150<br> test10g.__db_cache_size=167772160<br> test10g.__java_pool_size=16777216<br> test10g.__large_pool_size=4194304<br> <br> What's typical for 10g/11g, is that for the "*." settings, it means it's in effect for all instances in a cluster.<br> It;s there <I>even if you just use</I> a stand allone instance.<br> The records that are like "instance_name.setting", it means that it is in effect for that instance only.<br> <br> So, If an initialization parameter applies to all instances, use *.parameter notation, otherwise<br> prefix the parameter with the name of the instance.<br> For example:<br> Assume that you start the instance "prod1" (in a cluster) with an SPFILE containing the following entries:<br> <br> *.OPEN_CURSORS=500<br> prod1.OPEN_CURSORS=1000<br> <br> Then OPEN_CURSORS=1000 is in effect only for the instance prod1.<br> <br> <br> <font face="courier" size=2 color="blue"> <B>19.3 EXAMPLE 10g/11g INIT.ORA, OR SPFILE.ORA:</B><br> <font face="courier" size=2 color="black"> <br> <U>-Example 1:</U><br> <br> ###########################################<br> # Cache and I/O<br> ###########################################<br> db_block_size=8192<br> db_file_multiblock_read_count=16<br> <br> ###########################################<br> # Cursors and Library Cache<br> ###########################################<br> open_cursors=300<br> <br> ###########################################<br> # Database Identification<br> ###########################################<br> db_domain=antapex.org<br> db_name=test10g<br> <br> ###########################################<br> # Diagnostics and Statistics<br> ###########################################<br> <br> <font face="courier" size=2 color="blue"> # Diagnostic locations (logfiles etc..) 10g parameters:<br> background_dump_dest=C:\oracle/admin/test10g/bdump<br> core_dump_dest=C:\oracle/admin/test10g/cdump<br> user_dump_dest=C:\oracle/admin/test10g/udump<br> <br> <font face="courier" size=2 color="red"> # Diagnostic locations (logfiles etc..) 11g parameters:<br> DIAGNOSTIC_DEST=C:\oracle\<br> <font face="courier" size=2 color="black"> <br> ###########################################<br> # File Configuration<br> ###########################################<br> control_files=("C:\oracle\oradata\test10g\control01.ctl", "C:\oracle\oradata\test10g\control02.ctl", "C:\oracle\oradata\test10g\control03.ctl")<br> db_recovery_file_dest=C:\oracle/flash_recovery_area<br> db_recovery_file_dest_size=2147483648<br> <br> ###########################################<br> # Job Queues<br> ###########################################<br> job_queue_processes=10<br> <br> ###########################################<br> # Miscellaneous<br> ###########################################<br> # 10g example:<br> # compatible=10.2.0.1.0<br> <br> # 11g example:<br> compatible=11.1.0.0.0<br> <br> ###########################################<br> # Processes and Sessions<br> ###########################################<br> processes=350<br> <br> ###########################################<br> # Memory<br> ###########################################<br> <font face="courier" size=2 color="blue"> # Example 10g setting:<br> sga_target=287309824<br> <br> <font face="courier" size=2 color="red"> # Example 11g setting:<br> memory_target=287309824<br> <font face="courier" size=2 color="black"> ###########################################<br> # Security and Auditing<br> ###########################################<br> audit_file_dest=C:\oracle/admin/test10g/adump<br> remote_login_passwordfile=EXCLUSIVE<br> <br> ###########################################<br> # Shared Server<br> ###########################################<br> dispatchers="(PROTOCOL=TCP) (SERVICE=test10gXDB)"<br> <br> ###########################################<br> # Sort, Hash Joins, Bitmap Indexes<br> ###########################################<br> pga_aggregate_target=95420416<br> <br> ###########################################<br> # System Managed Undo and Rollback Segments<br> ###########################################<br> undo_management=AUTO<br> undo_tablespace=UNDOTBS1<br> <br> ###########################################<br> # Archive Mode:<br> ###########################################<br> LOG_ARCHIVE_DEST_1=c:\oracle\oradata\archlog<br> LOG_ARCHIVE_FORMAT='arch_%t_%s_%r.dbf'<br> <br> <U>- Example 2: Exported 11g spfile to an ascii init file</U><br> <br> test11g.__db_cache_size=281018368<br> test11g.__java_pool_size=12582912<br> test11g.__large_pool_size=4194304<br> test11g.__oracle_base='c:\oracle' #ORACLE_BASE set from environment<br> test11g.__pga_aggregate_target=322961408<br> test11g.__sga_target=536870912<br> test11g.__shared_io_pool_size=0<br> test11g.__shared_pool_size=230686720<br> test11g.__streams_pool_size=0<br> *.audit_file_dest='c:\oracle\admin\test11g\adump'<br> *.audit_trail='db'<br> *.compatible='11.1.0.0.0'<br> *.control_files='c:\oradata\test11g\control01.ctl','c:\oradata\test11g\control02.ctl','c:\oradata\test11g\control03.ctl'<br> *.db_block_size=8192<br> *.db_domain='antapex.nl'<br> *.db_name='test11g'<br><br> *.db_recovery_file_dest='c:\oracle\flash_recovery_area'<br> *.db_recovery_file_dest_size=2147483648<br> *.diagnostic_dest='c:\oracle'<br> *.dispatchers='(PROTOCOL=TCP) (SERVICE=test11gXDB)'<br> *.memory_target=857735168<br> *.open_cursors=300<br> *.processes=350<br> *.remote_login_passwordfile='EXCLUSIVE'<br> *.undo_tablespace='UNDOTBS1'<br> <br> <br> <font face="courier" size=2 color="blue"> <B>19.4 IMPORTANT SPFILE/INIT PARAMETERS:</B><br> <font face="courier" size=2 color="black"> <br> Let's review some of the most important init.ora parameters.<br> <br> <br> <font face="courier" size=2 color="red"> <B>19.4.1. Parameters related to Oracle Managed Files OMF:</B><br> <font face="courier" size=2 color="black"> <br> DB_CREATE_FILE_DEST = directory | ASM disk group <br> DB_CREATE_ONLINE_LOG_DEST_n = directory | ASM disk group <br> <br> DB_CREATE_FILE_DEST specifies the default location for Oracle-managed datafiles. <br> This location is also used as the default location for Oracle-managed control files <br> and online redo logs if none of the DB_CREATE_ONLINE_LOG_DEST_n initialization parameters are specified.<br> <br> DB_CREATE_ONLINE_LOG_DEST_n (where n = 1, 2, 3, ... 5) specifies the default location <br> for Oracle-managed control files and online redo logs. <br> If more than one DB_CREATE_ONLINE_LOG_DEST_n parameter is specified, then the control file and <br> online redo log is multiplexed across the locations of the other DB_CREATE_ONLINE_LOG_DEST_n parameters. <br> One member of each online redo log is created in each location, and one control file is created in each location.<br> <br> Example:<br> DB_CREATE_FILE_DEST = '/u01/oracle/test10g'<br> DB_CREATE_ONLINE_LOG_DEST_1= '/u02/oracle/test10g'<br> DB_CREATE_ONLINE_LOG_DEST_2= '/u03/oracle/test10g'<br> <br> <br> <font face="courier" size=2 color="red"> <B>19.4.2. Parameters related to the FLASH RECOVERY AREA (10g / 11gR1) or FAST RECOVERY AREA (11gR2)</B><br> <font face="courier" size=2 color="black"> <br> In 11gR2, the "FLASH RECOVERY AREA" is renamed to "FAST RECOVERY AREA".<br> <br> A flash recovery area is a location in which Oracle Database can store and manage files<br> related to backup and recovery. It is distinct from the database area.<br> <br> Two parameters define the "FLASH RECOVERY AREA" or "FAST RECOVERY AREA":<br> <br> You specify a flash recovery area with the following initialization parameters:<br> DB_RECOVERY_FILE_DEST (= location on filesystem or ASM)<br> DB_RECOVERY_FILE_DEST_SIZE (size reserved for DB_RECOVERY_FILE_DEST)<br> <br> DB_RECOVERY_FILE_DEST specifies the default location for the flash recovery area. The flash recovery area contains <br> archived redo logs, flashback logs, and RMAN backups.<br> <br> The DB_RECOVERY_FILE_DEST parameter makes sure that all flashback logs, RMAN backups, archived logs,<br> are under the control of the Instance.<br> Specifying this parameter without also specifying the DB_RECOVERY_FILE_DEST_SIZE initialization parameter is not allowed.<br> <br> DB_RECOVERY_FILE_DEST_SIZE specifies (in bytes) the hard limit on the total space to be used <br> by target database recovery files created in the flash recovery area.<br> <br> You cannot enable these parameters if you have set values for the LOG_ARCHIVE_DEST and <br> LOG_ARCHIVE_DUPLEX_DEST parameters. You must disable those parameters before setting up <br> the flash recovery area. You can instead set values for the<br> LOG_ARCHIVE_DEST_n parameters. If you do not set values for local LOG_ARCHIVE_DEST_n, <br> then setting up the flash recovery area will implicitly set LOG_ARCHIVE_DEST_10 to the flash recovery area.<br> Oracle recommends using a flash recovery area, because it can simplify backup and recovery operations for your database.<br> <br> You may also set the DB_FLASHBACK_RETENTION_TARGET parameter.<br> <br> This specifies in minutes how far back you can "flashback" the database, using the socalled "Flashback" framework.<br> How far back one can actually "flashback" the database, depends on how much flashback data <br> Oracle has kept in the recovery area.<br> <br> Example:<br> db_recovery_file_dest='c:\oracle\flash_recovery_area'<br> db_recovery_file_dest_size=2147483648<br> <br> <br> <font face="courier" size=2 color="red"> <B> 19.4.3. Parameters related to Automatic Diagnostic Repository ADR:</B><br> <font face="courier" size=2 color="black"> <br> This is for 11g only. <br> Oracle 9i and 10g uses the well known locations for the alert.log and trace files, which are specified by:<br> BACKGROUND_DUMP_DESTINATION, USER_DUMP_DESTINATION and CORE_DUMP_DESTINATION.<br> <br> ADR is defined by the DIAGNOSTIC_DEST parameter, which specifies a location on the filesystem.<br> <br> ADR is new in 11g, and is partly XML based. The logging (alert.log) and traces are part of ADR.<br> <br> DIAGNOSTIC_DEST = { pathname | directory }<br> As of Oracle 11g Release 1, the diagnostics for each database instance are located in a dedicated directory, <br> which can be specified through the DIAGNOSTIC_DEST initialization parameter.<br> <br> This location is known as the Automatic Diagnostic Repository (ADR) Home. For example, if the database name is proddb <br> and the instance name is proddb1, the ADR home directory would be "$DIAGNOSTIC_DEST/diag/rdbms/proddb/proddb1".<br> <br> So, if the DIAGNOSTIC_DEST was placed to "C:\ORACLE", you would find the new style XML alert.log "log.xml" in, for example,<br> "C:\oracle\diag\rdbms\test11g\test11g\alert\log.xml" for the test11g instance.<br> <br> The old plain text alert.log is still available in:<br> "C:\oracle\diag\rdbms\test11g\test11g\trace\alert_test11g.log"<br> <br> <br> <font face="courier" size=2 color="red"> <B> 19.4.4. Parameters related to DATABASE NAME AND DOMAIN:</B><br> <font face="courier" size=2 color="black"> <br> The databasename, and the domain where it "resides", are defined by the parameters:<br> DB_NAME<br> DB_DOMAIN<br> <br> The DB_NAME initialization parameter determines the local name component of the database name,<br> the DB_DOMAIN parameter, which is optional, indicates the domain (logical location) within a<br> network structure. The combination of the settings for these two parameters must<br> form a database name that is unique within a network.<br> For example, a database with a global database name of "test10g.antapex.org", <br> you would have the parameters like so:<br> <br> DB_NAME = test10g<br> DB_DOMAIN = antapex.org<br> <br> <br> <font face="courier" size=2 color="red"> <B> 19.4.5. Parameters related to PROCESSES AND SESSIONS:</B><br> <font face="courier" size=2 color="black"> <br> PROCESSES=max number of concurrent OS processes which can connect to the database.<br> SESSIONS=specifies the maximum number of sessions that can be created in the database. <br> <br> Example:<br> PROCESSES=500<br> <br> The PROCESSES initialization parameter determines the maximum number of<br> operating system processes that can be connected to Oracle Database concurrently. <br> The value of this parameter must be a minimum of one for each background process plus<br> one for each user process. The number of background processes will vary according<br> the database features that you are using. For example, if you are using Advanced<br> Queuing or the file mapping feature, you will have additional background processes.<br> If you are using Automatic Storage Management, then add three additional processes<br> for the database instance.<br> <br> SESSIONS specifies the maximum number of sessions that can be created in the system. <br> Because every login requires a session, this parameter effectively determines the maximum number of <br> concurrent users in the system. You should always set this parameter explicitly to a value equivalent <br> to your estimate of the maximum number of concurrent users, plus the number of background processes,<br> plus approximately 10% for recursive sessions.<br> <br> Note: it would not be a very good idea to specify the SESSION= parameter<br> which is lower than the PROCESSES parameter.<br> <br> The default no of sessions: (1.1 * PROCESSES) + 5<br> <br> <br> <font face="courier" size=2 color="red"> <B> 19.4.6. Parameters related to MEMORY and SGA:</B><br> <font face="courier" size=2 color="black"> <br> Memory = SGA memory (like buffer cache + all pools) + All server processes and background processes PGA's<br> <br> - 11g: (Full) Automatic Memory Management = AMM -> by using parameter "MEMORY_TARGET="<br> - 10g/11g: Automatic Shared Memory Management = ASMM -> by using parameter"SGA_TARGET="<br> or<br> - 11g/10g/9i: Manual Memory Management, where you can specify all the individual buffers and poolsizes + pga's.<br> <br> So, 11g AMM (is Total memory management) "is more" automatic than 11g/10g ASMM (auto SGA management) which is more automatic<br> than manual configuration of 11g/10g/9i cache, pools and pga's.<br> <br> <font face="courier" size=2 color="green"> <B>=> For 11g and 10g, it is possible to use the SGA_TARGET parameter.</B><br> <font face="courier" size=2 color="black"> <br> "SGA_TARGET=amount_of_memory" is actually a single parameter for the total SGA size under Oracle control, where<br> automatically all SGA components (buffer cache and all pools) are sized <I>as needed</I><br>. When using SGA_TARGET=, then you do not need to specify all individual area's like:<br> <br> DB_CACHE_SIZE (DEFAULT buffer pool) <br> SHARED_POOL_SIZE (Shared Pool) <br> LARGE_POOL_SIZE (Large Pool) <br> JAVA_POOL_SIZE (Java Pool) <br> <br> <font face="courier" size=2 color="green"> <B>=> For 11g, it goes a step further, and it is possible to use the MEMORY_TARGET parameter.</B><br> <font face="courier" size=2 color="black"> <br> "MEMORY_TARGET=amount_of_memory" controls all memory automatically (SGA with all pools and buffers, and all pga's).<br> <br> While it is better to use MEMORY_TARGET in 11g, you can still use the SGA_TARGET parameter to control the SGA only.<br> Also, it's still possible to manually configure all memory as you see fit, by using all individual parameters<br> like "SHARED_POOL_SIZE=", "DB_CACHE_SIZE=" etc..<br> <br> MEMORY_TARGET and SGA_TARGET will perform automatic memory management (memory_target for all memory, sga_target for the SGA)<br> Both parameters can be used in conjunction with a hard upper limit for the total memory that can be used.<br> These are: memory_max_target and sga_max_size.<br> So, the following parameters might be seen as being set in an spfile.ora/init.ora:<br> <br> 11g:<br> MEMORY_TARGET=<br> MEMORY_MAX_TARGET=<br> <br> 10g and 11g:<br> SGA_TARGET=<br> SGA_MAX_SIZE=<br> <br> Prior to Oracle 11g, you could set the sga_target and sga_max_size parameters, allowing Oracle to allocate<br> and re-allocate RAM within the SGA. The PGA was independent from this, and was set by the pga_aggregate_target parameter.<br> <br> In Oracle 11g you may use the memory_max_target parameter which determines the total maximum RAM for both the PGA and SGA area's<br> The new MEMORY-TARGET parameter, "targets" for the set size, and even allows RAM to be "stealed" from the SGA<br> and transferred to the PGA, or the other way around.<br> <br> <br> <font face="courier" size=2 color="red"> <B> 19.4.7. Parameters related to ADDM and AWR:</B><br> <font face="courier" size=2 color="black"> <br> Oracle Diagnostics Pack 11g and 10g includes a self-diagnostic engine built right into the<br> Oracle Database 11g kernel, called the <B>"Automatic Database Diagnostic Monitor"</B>, or ADDM.<br> <br> To enable ADDM to accurately diagnose performance problems, it is important that<br> it has detailed knowledge of database activities and the workload the database is<br> supporting. Oracle Diagnostics Pack 11g (and 10g), therefore, includes a built in repository<br> within every Oracle 11g (and 10g) Database, called <B>"Automatic Workload Repository (AWR)"</B>,<br> which contains operational statistics about that particular database and other relevant<br> information. At regular intervals (once an hour by default), the Database takes a<br> snapshot of all its vital statistics and workload information and stores them in AWR, <br> and retains the statistics in the workload repository for 8 days.<br> Also, by default, ADDM runs every hour to analyze snapshots taken by AWR during that period. <br> <br> Note: for people familiar with older Oracle versions: ADDM and AWR resembles an strongly enhanced<br> and automatically implemented "STATSPACK".<br> <br> So, ADDM examines and analyzes data captured in the Automatic Workload Repository (AWR) to determine <br> possible performance problems in Oracle Database. ADDM then locates the root causes of the performance problems, <br> provides recommendations for correcting them, and quantifies the expected benefits. <br> <br> A key component of AWR, is "Active Session History (ASH)". ASH samples the<br> current state of all active sessions periodically and stores it in memory. The data<br> collected in memory can be accessed by system views. This sampled data is also<br> pushed into AWR every hour for the purposes of performance diagnostics.<br> <br> For 11g, ADDM and AWR/ASH are part of the "Server Manageability Packs". In fact, the components are the following:<br> <br> - The DIAGNOSTIC pack includes AWR and ADDM.<br> - The TUNING pack includes SQL Tuning Advisor, SQLAccess Advisor, and so on.<br> <br> <B>CONTROL_MANAGEMENT_PACK_ACCESS parameter:</B><br> <br> The "CONTROL_MANAGEMENT_PACK_ACCESS" parameter determines which of the above components are "switched on".<br> <br> CONTROL_MANAGEMENT_PACK_ACCESS = { NONE | DIAGNOSTIC | DIAGNOSTIC+TUNING }<br> Default: DIAGNOSTIC+TUNING<br> <br> If set to NONE, the ADDM & AWR and TUNING pack, are switched off.<br> <br> <B>STATISTICS_LEVEL parameter:</B><br> <br> STATISTICS_LEVEL specifies the level of collection for database and operating system statistics. <br> <br> STATISTICS_LEVEL = { ALL | TYPICAL | BASIC }<br> Default: TYPICAL<br> <br> Gathering database statistics using AWR is enabled by default and is controlled by the STATISTICS_LEVEL <br> initialization parameter. <br> The STATISTICS_LEVEL parameter should be set to TYPICAL or ALL to enable statistics gathering by AWR. <br> The default setting is TYPICAL.<br> Setting the STATISTICS_LEVEL parameter to BASIC disables many Oracle Database gathering statistics, <br> including AWR/ASH, and is not recommended unless you want to reserve as much as possible performance for<br> the applicative database processes.<br> <br> <br> <br> <br> <font face="arial" size=2 color="blue"> <h3 id="section20">20. DIAGNOSTIC TOOLS IN 9i: A FEW WORDS ON STATSPACK:</h3> <font face="courier" size=2 color="black"> Although statspack is a typical 8i/9i Diagnostic tool, I still like to spend a few words on it.<br> For 10g and later, you are recommended to use the newer frameworks.<br> <br> STATSPACK is a performance diagnostic tool, which is available since Oracle8i.<br> It's widely used in Oracle 8i and 9i environments. But, from 10g onwards, a renewed framework<br> was introduced (like for example ADDM). So, in 10g and 11g, ofcourse the newer Diagnostic tools are recommended.<br> Still, it's usefull to spend a few words on statspack, since it's so incredably easy to use.<br> And who knows.. maybe you want to use it on 10g as well.<br> However, correctly <I>interpreting</I> the reports, still requires a reasonable system and Oracle knowledge.<br>. <br> <br> <font face="courier" size=2 color="blue"> <B>20.1 INSTALLING:</B><br> <font face="courier" size=2 color="black"> <br> It's recommended to create a tablespace "PERFSTAT" first. Since the schema (owner) of the new<br> diagnostic tables, is the new user "perfstat", it's nice to keep all objects together in a easy<br> to indentify tablespace.<br> <br> Next, from sqlplus, run the create script <br> <br> "$ORACLE_HOME/rdbms/admin/statscre.sql" (Unix) or "%ORACLE_HOME%\rdbms\admin\statscre.sql" (Windows).<br> <br> This script will ask a few simple questions like who should be the owner (the suggested owner is "perfstat")<br> and which tablespace you want to use to store perfstat's tables.<br> <br> Although some additional configuration (after installation) can be done,like altering the "Collection Level",<br> you are now "basically" setup to create "snapshots" and create "reports".<br> <br> <br> <font face="courier" size=2 color="blue"> <B>20.2 A FEW WORDS ON HOW IT WORKS:</B><br> <font face="courier" size=2 color="black"> <br> You know that there are quite a few dynamic system "views" (v$), which collect database wide statistics,<br> many of which are related to performance and wait events<br> Statspack will use a number of true permanent tables which has a one to one correspondence to that<br> set of v$ views (for example v$sysstat will have a corresponding stats$sysstat table)<br> Well it's <I>almost</I> one to one, because the statspack table will have some additional columns<br> of which (in this note) the "snap_id" column is the most interresting one.<br> <br> When you "activate" statspack, you will create a "snapshot" of the database, meaning<br> that the set of v$ views are queried, and the results are stored in the "stats$" tables, where these<br> specific results are identified by a specific "SNAP_ID".<br> The next time you run statspack, a new SNAP_ID will identify these new measurements.<br> And so on.. and so on.<br> <br> It is quite critical to your understanding of the STATSPACK utility that you realize that the information<br> captured by a STATSPACK snapshot are accumulated values, since most of the v$ views contain accumulated values.<br> This automatically means, that you only can compare <B>two</B> snapshots, to get meaningfull results.<br> Examples:<br> <br> <ol> <li>You could create a snapshot early in the morning, and one late in the afternoon, and then analyze the results to see how the database has performed this day.</li> <li>You could create a snapshot before a certain batch (or other program) runs, and then one when that batch has finished.</li> </ol> <br> Note:<br> Since the v$ views are cumulative, the stats$ are thus too. But an instance shutdown will "clear" many v$ views,<br> thus creating a report using snapshots before and after a shutdown, will not generate valid results.<br> <br> <br> <font face="courier" size=2 color="blue"> <B>20.3 A FEW WORDS ON USING STATSPACK:</B><br> <font face="courier" size=2 color="black"> <br> <B>- Creating a snapshot:</B><br> <br> Logon (using sqlplus) as perfstat and execute the 'statspack.snap' package.<br> <br> SQL> execute statspack.snap;<br> <br> <B>- Creating a report:</B><br> <br> The report will just be an ascii file, so it's easy to view the contents.<br> <br> Log on as perfstat using sqlplus:<br> <br> % sqlplus perfstat/perfstat<br> <br> Then run the 'spreport.sql' script that lives in the "ORACLE_HOME/rdbms/admin" directory.<br> <br> SQL> @?/rdbms/admin/spreport.sql<br> <br> This script essentially asks three important questions:<br> <br> - The begin SNAP_ID<br> - The end SNAP_ID<br> - And where you want to store the report with what name.<br> <br> A short while later, you can study that report. The report will show you much information like<br> general database statistics, top consuming SQL statements, %cpu and duration per SQL etc..<br> <br> <br> <font face="courier" size=2 color="blue"> <B>20.4 OTHER NOTES:</B><br> <font face="courier" size=2 color="black"> <br> - Since "execute statspack.snap" will create a snapshot, it's easy to schedule it from an OS scheduler.<br> But don't schedule it like "every ten minutes". That will not add any value. Once an hour, or a longer interval,<br> is recommended.<br> Also, if you need to analyze batches, it's more sane to schedule it before and after those batch(es).<br> <br> - If you need to view the snap_id's and at which time they have run, use a query like:<br> <br> select name,snap_id,to_char(snap_time,'DD.MM.YYYY:HH24:MI:SS')<br> "Date/Time" from stats$snapshot,v$database;<br> <br> That information can be used to select the correct start snap_id and end snap_id for your report.<br> <br> <br> <br> <font face="arial" size=2 color="blue"> <h3 id="section21">21. USING A CURSOR IN PL/SQL LOOPS:</h3> <font face="courier" size=2 color="black"> We are going to illustrate the use of a "cursor" in PLSQL loops.<br> PLSQL has more "ways" to create loops, like for example using "FOR.." or "WHILE.." loops<br> But this section wants to give a few examples using a <I>cursor</I>.<br> <br> In many occasions, you might view a cursor as a <I>virtual table</I>, because you often "declare" the cursor<br> as being a resultset from a query on one or more tables.<br> <br> Assuming you work on a testsystem, let's create a testuser first, who will perform<br> a couple of examples.<br> <br> CREATE USER albert identified by albert<br> DEFAULT TABLESPACE USERS <br> TEMPORARY TABLESPACE TEMP<br> ;<br> <br> GRANT connect TO albert;<br> GRANT resource TO albert;<br> GRANT DBA TO albert;<br> <br> <br> <B><U>EXAMPLE 1:</U></B><br> <br> Let's use a cursor to output the contents of a table to your screen.<br> <br> - Lets connect as albert:<br> <br> SQL> connect albert/albert<br> <br> - Now albert will create a simple table:<br> <br> CREATE TABLE EMPLOYEE<br> (<br> EMP_ID NUMBER,<br> EMP_NAME VARCHAR2(20),<br> SALARY NUMBER(7,2)<br> );<br> <br> - Albert now performs a few inserts:<br> <br> INSERT INTO EMPLOYEE VALUES (1,'Harry',2000);<br> INSERT INTO EMPLOYEE VALUES (2,'John',3150);<br> INSERT INTO EMPLOYEE VALUES (3,'Mary',4000);<br> INSERT INTO EMPLOYEE VALUES (4,'Arnold',2900);<br> <br> commit;<br> <br> - Now we want to output the contents of the table (ofcourse it's easier using a select statement, but we want<br> to demonstrate the use of a cursor).<br> Let's try the following code:<br> <br> SQL> set serveroutput on; -- in order to make sure sqlplus shows output.<br> <br> -- here is the code:<br> <font face="courier" size=2 color="blue"> <br> DECLARE cursor CURTEST IS<br> &nbsp&nbsp SELECT emp_id, emp_name FROM EMPLOYEE;<br> <br> cur_rec curtest%rowtype;<br> <br> begin<br> &nbsp for cur_rec IN CURTEST <B>loop</B><br> &nbsp dbms_output.put_line(TO_CHAR(cur_rec.emp_id)||' '||cur_rec.emp_name);<br> &nbsp <B>end loop</B>;<br> end;<br> /<br> <br> <font face="courier" size=2 color="black"> The first block is the cursor declaration. You notice that "CURTEST" is declared as being the<br> resultset of a query?<br> Here we also declare the variable "cur_rec", which (when assigned values) contains a whole "row"<br> from the cursor. You see? This is a <I>very easy</I> way to assign a whole row to variable in one time!<br> <br> The second block is the "body" of the code. Here we open (implicitly) the cursor "CURTEST", and define a loop<br> where "cur_rec" in each cycle of the loop, attains the values from the next row in "CURTEST".<br> What we then actually do in such a cycle in the loop, is just output two values in "cur_rec" to your screen<br> using the standard Oracle procedure "dbms_output.put_line()".<br> <br> Did you notice that we did not explicitly "open" and later (after the loop is done), "close" the cursor?<br> In the "for var in cursor_name loop .. end loop" structure, the open and close of the cursor is<br> implicitly done.<br> <br> <br> <B><U>EXAMPLE 2:</U></B><br> <br> This time, we use a cursor to update a certain table, with new values and certain values from a second table.<br> <br> First, albert creates the EMPBONUS table, like so:<br> <br> CREATE TABLE EMPBONUS<br> (<br> EMP_ID NUMBER,<br> EMP_BONUS NUMBER(7,2)<br> );<br> <br> What we want to do now, is fill the EMPBONUS table with emp_id's from EMPLOYEE,<br> and a calculated bonus amount which is 10% of the employee's salary.<br> Again, using a simple query, works much faster, but again we want to demonstrate the use of a cursor.<br> <br> Let's try the following code:<br> <br> SQL> set serveroutput on; -- in order to make sure sqlplus shows output.<br> <br> -- here is the code:<br> <font face="courier" size=2 color="blue"> <br> DECLARE cursor CURTEST IS<br> &nbsp&nbsp SELECT * FROM EMPLOYEE;<br> <br> cur_rec curtest%rowtype;<br> <br> begin<br> &nbsp for cur_rec IN CURTEST <B>loop</B><br> &nbsp INSERT INTO EMPBONUS<br> &nbsp VALUES<br> &nbsp (cur_rec.emp_id,0.1*cur_rec.salary);<br> &nbsp commit;<br> &nbsp <B>end loop</B>;<br> end;<br> /<br> <br> <font face="courier" size=2 color="black"> Let's see what's in the EMPBONUS table<br> <br> SQL> select * from EMPBONUS;<br> <br> EMP_ID EMP_BONUS<br> <br> &nbsp 1 &nbsp&nbsp 200<br> &nbsp 2 &nbsp&nbsp 315<br> &nbsp 3 &nbsp&nbsp 400<br> &nbsp 4 &nbsp&nbsp 290<br> <br> So, it worked !<br> <font face="courier" size=2 color="black"> <br> <br> <B><U>MORE ON USING CURSORS:</U></B><br> <br> There are two <B>types</B> of cursors with respect on how the cursor is opened and closed<br> and whether you need to explicitly FETCH the next row from the cursor.<br> <br> <ol> <li>The "CURSOR FOR LOOP.." structure, which we already have seen above. This one does not use FETCH<br> in order to fetch the next row from the cursor. This is implicitly done in the "for..loop".<br> Also, here you do not need to open and close the cursor.</li><br> <li>The "OPEN cursor, FETCH next, CLOSE cursor" structure.</li> </ol> <br> The first type is really easy to use. The second type gives you a little more control in your code.<br> <br> The following "cursor attributes" can be used (among others):<br> <br> %notfound : did we just have fetched the last row in the cursor?<br> %rowcount : how many rows are already processed or done?<br> %isopen : is the cursor still open?<br> <br> As an example of the second type, let's take a look at the next example.<br> We are going to use a more extended EMPLOYEE table in this example. So we could either add a column, or<br> just drop and re-create the new EMPLOYEE table again. Let's do the latter.<br> <br> SQL> connect albert/albert<br> <br> DROP TABLE EMPLOYEE;<br> <br> CREATE TABLE EMPLOYEE<br> (<br> EMP_ID NUMBER,<br> EMP_NAME VARCHAR2(20),<br> SALARY NUMBER(7,2),<br> JOB VARCHAR2(20)<br> );<br> <br> - Albert now performs a few inserts:<br> <br> INSERT INTO EMPLOYEE VALUES (1,'Harry',2000,'CLERK');<br> INSERT INTO EMPLOYEE VALUES (2,'John',3150,'DIRECTOR');<br> INSERT INTO EMPLOYEE VALUES (3,'Mary',4000,'SCIENTIST');<br> INSERT INTO EMPLOYEE VALUES (4,'Arnold',2900,'CLERK');<br> <br> commit;<br> <br> Now, we want to produce a script that will update the SALARY column of the EMPLOYEE table,<br> with a percentage that <I>depends</I> on the JOB of the employee, that is, a "CLERCK" gets another<br> salary update than a "DIRECTOR".<br> <br> -- Here is the code. Also note it uses a type 2 cursor.<br> <br> <font face="courier" size=2 color="blue"> DECLARE cursor CUR_EMP IS<br> &nbsp SELECT * FROM employee;<br> <br> emp_rec CUR_EMP%rowtype;<br> <br> begin<br> <br> open CUR_EMP;<br> <br> &nbsp loop<br> &nbsp fetch CUR_EMP into emp_rec;<br> <br> &nbsp exit when CUR_EMP%notfound;<br> <br> &nbsp&nbsp if emp_rec.job='CLERK' then emp_rec.salary:=emp_rec.salary*1.2;<br> &nbsp&nbsp elsif emp_rec.job='SCIENTIST' then emp_rec.salary:=emp_rec.salary*1.5;<br> &nbsp&nbsp elsif emp_rec.job='DIRECTOR' then emp_rec.salary:=emp_rec.salary*1.7;<br> &nbsp&nbsp end if;<br> <br> &nbsp&nbsp update EMPLOYEE set salary=emp_rec.salary<br> &nbsp&nbsp WHERE emp_id=emp_rec.emp_id;<br> <br> &nbsp end loop;<br> &nbsp commit;<br> <br> close cur_emp;<br> end;<br> /<br> <br> <font face="courier" size=2 color="black"> Notes:<br> <br> If you want to use loops in your scripts, you might also take a look at section 22.<br> Here we will show some examples of the regular "FOR.." and "WHILE.." loops, which in many cases<br> are much easier to use.<br> But I just wanted to touch the subject of an explicit cursor in this document.<br> Also, if you must process very large tables, then a cursor may not be the most effective way to do that.<br> <br> <br> <br> <font face="arial" size=2 color="blue"> <h3 id="section22">22. EXECUTING SCRIPTS FROM THE "SQL>" PROMPT:</h3> <font face="courier" size=2 color="black"> If you are at the "SQL>" prompt, you may wonder how to execute your blocks of code, or your scripts.<br> There are several ways to do that.<br> <br> <B>- If you have created a procedure, function, or package:</B><br> <br> We have not done that yet in this document. In section 26 we are going to illustrate that.<br> <br> <B>- If you have "just" a block of code (like we have seen in section 21):</B><br> <br> A: If you have that code created in your favourite editor, then just copy it, and paste it at<br> the "SQL>" prompt. This really works.<br> <br> B: Suppose you have created the block of code using your favourite editor. Suppose you have saved it to a file.<br> Then you can run it from the "SQL>" prompt using the syntax:<br> <br> SQL> @path_to_the_file/file_name<br> <br> Note the use of the "@" symbol here.<br> <br> Example:<br> <br> Suppose in "C:\test" I have the file "update_customer.sql". If I want to run it from the "SQL>" prompt,<br> I can use this statement:<br> <br> SQL> @c:\test\update_customer.sql<br> <br> If I already 'was' in the c:\test directory, I can simply use "SQL> @update_customer.sql" because the prompt<br> tools will per default look in the current directory for the file.<br> <br> <br> <br> <font face="arial" size=2 color="blue"> <h3 id="section23">23. USING CONTROLS, AND "FOR.." AND "WHILE.." LOOPS IN PL/SQL:</h3> <font face="courier" size=2 color="black"> Here we will present a few representative examples of "looping" structures in PL/SQL.<br> <br> <font face="courier" size=2 color="blue"> <B>23.1 USING THE "WHILE..LOOP" STRUCTURE:</B><br> <font face="courier" size=2 color="black"> <br> <B><U>Example:</U></B><br> <br> I presume you are working on a testsystem, so let's work as user albert again (see section 21).<br> <br> let's logon as albert and create the EMPLOYEE table:<br> <br> CREATE TABLE EMPLOYEE --<I>If that table already exists, use "drop table employee;"</I><br> (<br> EMP_ID NUMBER(6) NOT NULL,<br> EMP_NAME VARCHAR2(20) NOT NULL,<br> SALARY NUMBER(7,2)<br> );<br> <br> Suppose albert wants to insert 9999 dummy records into that table, he might use the following script.<br> Ofcourse, it's a silly example, but it nicely demonstrates the use of a "while [condition is true] loop" construct.<br> <br> <font face="courier" size=2 color="blue"> declare<br> i number := 1;<br> begin<br> <B>while i<10000 loop</B><br> &nbsp insert into EMPLOYEE<br> &nbsp values (i,'harry',2500);<br> <br> &nbsp i := i + 1;<br> end loop;<br> commit;<br> end;<br> /<br> <br> <font face="courier" size=2 color="black"> The first piece of code is a "variable declaration". We want to use the number "i" in our code, that starts out with<br> the value "1", and increases during each cycle of the loop, until it gets to the value of "10000".<br> Then the loop exits because the condition "while i<10000" is no longer true.<br> <br> Here is another example using "WHILE [condition is true] LOOP"<br> <br> <B><U>Example:</U></B><br> <br> <font face="courier" size=2 color="blue"> declare <I>-- again we start with a variable declaration</I><br> x number:=5;<br> begin <I>-- here the "body" starts:</I><br> <br> &nbsp while x>0 loop<br> &nbsp dbms_output.put_line('I did this '||TO_CHAR(x)||' times.');<br> &nbsp x:=x-1;<br> &nbsp end loop;<br> end;<br> /<br> <font face="courier" size=2 color="black"> <br> <font face="courier" size=2 color="blue"> <B>23.2 USING THE "FOR..LOOP" STRUCTURE:</B><br> <font face="courier" size=2 color="black"> <br> You typically use the "while" loop if you have an expression that evaluates to "true" for a certain time. As long as the loop cycles,<br> each time the expression is evaluated <B>again and again</B> to see if it's true or false. If it's false, then the loop exits.<br> You can clearly see that in the examples above, like in "while x>0" (here the "exprsession is x>0).<br> But you may not even know beforehand when the expression exactly evaluates to "true" or "false".<br> Suppose somewhere else in your code, you have a variable "v_proceed". Inside your while loop, there can be<br> all sorts of statements that may affect the value of "v_proceed". At a certain cycle, it may attain the value "false".<br> If you created your loop like this:<br> <br> <B> while v_proceed loop<br> &nbsp <I> do all sorts of statements.. also statements that alter the value of v_proceed</I><br> </B> <br> Then the loop exits when "v_proceed" is "false".<br> <br> So, typically, you use "while" when a certain expression evaluates to "true" (or "false") and you know, or<br> dont know, when the expression evaluates to the inverse value.<br> <br> Typically, using the "FOR.. LOOP", you already know beforehand the "range" for which the loop must run.<br> Here is a very simple example that demonstates the "FOR.. LOOP" structure:<br> <br> <font face="courier" size=2 color="blue"> set serveroutput on<br> <br> declare<br> &nbsp -- nothing to declare<br> begin<br> &nbsp <B>for x in 0..4 loop</B><br> &nbsp dbms_output.put_line('I did this '||TO_CHAR(x)||' times.');<br> &nbsp end loop;<br> end;<br> /<br> <br> <font face="courier" size=2 color="blue"> <B>23.3 USING THE "LOOP.. END LOOP" STRUCTURE:</B><br> <font face="courier" size=2 color="black"> <br> Maybe the following looping control is the "King of all Oracle loops". It's very simple to use.<br> It always has the following structure:<br> <br> <B>loop</B><br> &nbsp statements (which must be repeated)<br> <I>way to evaluate if the loop must exit or not</I><br> <B>end loop;</B><br> <br> So, you always start simply with "loop", and you end the code with "end loop". <br> But, as with all loops, you need to have a way that evaluates whether the loop must exit or not.<br> There are a few variants here, and the most used are:<br> <br> loop<br> &nbsp statements;<br> <I>if condition then exit;</I><br> end loop;<br> <br> and<br> <br> loop<br> &nbsp statements;<br> <I>exit when condition;</I><br> end loop;<br> <br> A few examples will make it clear.<br> <br> <B><U>Example 1:</U></B><br> <br> declare<br> &nbsp x number:=5;<br> <br> begin<br> &nbsp <B>loop</B><br> &nbsp dbms_output.put_line('I did this '||TO_CHAR(x)||' times.');<br> &nbsp x:=x-1;<br> &nbsp if x=0 then exit;<br> &nbsp end if;<br> &nbsp <B>end loop;</B><br> end;<br> /<br> <br> <br> <B><U>Example 2:</U></B><br> <br> declare<br> &nbsp x number:=5;<br> <br> begin<br> &nbsp <B>loop</B><br> &nbsp dbms_output.put_line('I did this '||TO_CHAR(x)||' times.');<br> &nbsp x:=x-1;<br> &nbsp exit when x=0; <I>-- here we don't have the "if" test to evaluate if x=0</I><br> &nbsp <B>end loop;</B><br> end;<br> /<br> <br> <br> <br> <font face="arial" size=2 color="blue"> <h3 id="section24">24. HOW TO PUT SQLPLUS OUTPUT IN A SHELL VARIABLE:</h3> <font face="courier" size=2 color="black"> Suppose you want to place some sqlplus output into a shell variable.<br> Here, you should think of a normal sh or ksh shell variable of UNIX or Linux.<br> The following example shows a script that will do just that.<br> Ofcourse, the method works best if only one value is returned into that variable.<br> <br> <B>Example: a sh, ksh or bash script:</B><br> <br> login='dbuser/password@DBNAME'<br> <br> code=`sqlplus -s $login << EOF<br> SELECT name from PERSON where id=1; <I># suppose we have the table PERSON, and we only want the name of id=1</I><br> exit<br> EOF`<br> echo $code <I># now the variable code should contain that name</I><br> <br> <br> <br> <font face="arial" size=2 color="blue"> <h3 id="section25">25. A FEW 9i,10g,11g RMAN NOTES:</h3> <font face="courier" size=2 color="black"> You might consider this section only as a very "light" and simple step-up to RMAN.<br> <br> <font face="courier" size=2 color="black"> <font face="courier" size=2 color="blue"> <B>25.1 OVERVIEW:</B><br> <font face="courier" size=2 color="black"> <br> RMAN is considered to be <B>the</B> most important backup/recovery tool for Oracle databases.<br> <br> With rman, you can make full, or incremental, backups of an open and online database, if that database<br> is running in <B>archive mode</B><br> Alongside to creating a backup of the database, you can backup the "archived redologs" as well (often done in<br> the same script).<br> <br> Although graphical interfaces exists to "RMAN" (like the the "Enterprise Manager"), many DBA's just use<br> it from the OS prompt, or use it from (scheduled) OS shell scripts.<br> <br> You can use the "rman" prompt tool, just from the OS commandline, like<br> <br> <B> % rman (on unix/linux)<br> C:\> rman (on Windows)<br> </B> <br> This will then bring you to the RMAN> prompt. <br> RMAN><br> <br> From here, you can issue a whole range of commands, like BACKUP, RESTORE, <br> RECOVER, and reporting commands like LIST, REPORT etc..<br> <br> But <I>before you can do anything</I> with the "target" database (the database you want to backup),<br> you need to connect (authenticate) to the target database (and optionally to the catalog, on which more later)<br> <br> RMAN maintains a record of database files and backups for each database on which it performs operations. <br> This metadata is called the <B>RMAN repository</B>.<br> -- The controlfile(s) of the target database, <B>always</B> contains the RMAN backup METADATA (repository).<br> -- Optionally, you can have a separate dedicated (rman) database, which is called "the catalog".<br> <br> Having a seperate, dedicated RMAN catalog database, can be handy of you have a lot of databases,<br> and you want some "central storage" for all metadata (which you can query for all sorts of admin info)<br> But, the controlfile of each target database, will also hold it's metadata, and even is leading information.<br> Note: the catalog does not even be a dedicated database: even a dedicated "tablespace" in some database,<br> might be sufficient.<br> <br> So, before you can "work" with RMAN, or planning to do "something" with the target, you need to connect<br> to the target, and optionally also to the "catalog" (if you use one).<br> So, to connect to the target database (and optionally, the catalog) here are a few examples:<br> Here we assume that database "PROD" is the target database.<br> <br> <B> $ export ORACLE_SID=PROD<br> $ rman<br> <br> RMAN> connect target /<br> RMAN> connect target system/password@SID<br> RMAN> connect target system/password@SID catalog rman/rman@RCAT<br> </B> <br> In the first example, we just connect (using os authentication) to the target (and we do not have<br> a separate catalog database).<br> In the second example, we connect as user "system" to the target (and we do not have<br> a separate catalog database).<br> In the last example, we connect to the target as system, and apparantly we connect to the catalog database (RCAT)<br> using the account "rman".<br> <br> Or you can also call rman from the prompt, and pass connect parameters on the same commandline, like:<br> <br> <b>$ rman target sys/password@PROD catalog rman/rman@RCAT</b> (unix)<br> <br> or<br> <br> <b>C:\> rman target sys/password@PROD catalog rman/rman@RCAT</b> (windows)<br> <br> Once connected, you could use commands like:<br> <br> RMAN> backup database;<br> <br> or<br> <br> RMAN> backup incremental level 0 database;<br> <br> Both commands do the same thing: we used the backup command, to make a full database backup<br> (full is equivalent to "incremental level 0").<br> An "rman script" could look like this:<br> <br> <B> run {<br> &nbsp allocate channel t1 device type disk FORMAT '/backups/ora_df%t_s%s_s%p'';<br> &nbsp backup incremental level 0 database;<br> &nbsp release channel t1;<br> &nbsp }<br> <br> </B> As you can see, an rman script starts with "run", followed by related statements between { }.<br> First, you might define one ore more disk (or tape) "channels", which are server sessions.<br> In the allocate channel command, you also see the "format" (or location) as to where the backups should be stored.<br> However, it's much more common to have that configured as a socalled persistent setting, like in the following command:<br> <br> <B>RMAN> CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/backups/ora_df%t_s%s_s%p';</B><br> <br> The "body" of the script are ofcourse the backup statements (database, or archive logs, or other objects).<br> Then, if you have opened channels, you also close the channels, so the server sessions will exit.<br> <br> Such a script as shown above, you could type in from the rman prompt, and after you have entered the last<br> "}", it will execute.<br> Ofcourse, you can place such a script in a shell script as well.<br> <br> <br> <font face="courier" size=2 color="blue"> <B>25.2 SOME PERSISTENT RMAN CONFIGURATION SETTINGS:</B><br> <font face="courier" size=2 color="black"> <br> The script shown above, is for certain situations, quite <I>incomplete</I><br> You know that you need to backup the archived redologs as well. And having a "controlfile" backup, is critical.<br> <br> You can configure RMAN to use certain settings, which are then persistent over it's sessions.<br> If you want to view the current settings, use the "SHOW ALL" command, as in:<br> <br> <B>RMAN> show all;</B><br> <br> This will show you a list of all current settings.<br> One important setting is the "controlfile autobackup" setting. You might find from the list, that it's "OFF".<br> If you switch it to "ON", then every RMAN backup, will also include the most current controlfile as a backup.<br> Normally, that would be great. As an alternative, you can put a controlfile backup as the last statement<br> in your script, but that's quite cumbersome.<br> <br> Here are a few examples on how to set the persistent configuration (stored in the metadata repository):<br> <br> <B> RMAN> configure controlfile autobackup on;<br> RMAN> configure default device type to disk;<br> RMAN> configure channel device type disk format '/dumps/orabackups/backup%d_DB_%u_%s_%p';<br> </B> <br> But if you want, you can always overide some setting, in some of your scripts, like for example the<br> where on disk the backups are stored:<br> <br> <B> RUN<br> { <br> &nbsp ALLOCATE CHANNEL disk1 DEVICE TYPE DISK FORMAT '/disk1/%d_backups/%U';<br> &nbsp ALLOCATE CHANNEL disk2 DEVICE TYPE DISK FORMAT '/disk2/%d_backups/%U';<br> &nbsp ALLOCATE CHANNEL disk3 DEVICE TYPE DISK FORMAT '/disk3/%d_backups/%U';<br> &nbsp BACKUP DATABASE; <br> &nbsp RELEASE CHANNEL disk1;<br> &nbsp RELEASE CHANNEL disk2;<br> &nbsp RELEASE CHANNEL disk3;<br> } <br> </B> <br> Note that here the backup is "spreaded" along several backup disks, shortening overall runtime<br> <br> <B>More on channels:</B><br> Manually allocated channels (allocated by using ALLOCATE) should be distinguished from automatically <br> allocated channels (specified by using CONFIGURE). Manually allocated channels apply only to the RUN job<br> in which you issue the command. Automatic channels apply to <B>any RMAN job</B> in which you do not manually <br> allocate channels. You can always override automatic channel configurations by manually allocating channels within a RUN command.<br> <br> This explains why you will <B>not always</B>see an "allocate channel" command in a run job.<br> <br> <br> <font face="courier" size=2 color="blue"> <B>25.3 SOME SIMPLE RMAN BACKUP EXAMPLES:</B><br> <font face="courier" size=2 color="black"> <br> Here are just a few RMAN backup script examples.<br> <br> Examples 1 and 2 are old, traditional examples, not using <B>incremental backups</B>.<br> Here, we just make a full backup of the database, and backup all archived redologs.<br> This works, but most will agree that using incremental backups might be very beneficial.<br> <br> <B>Traditional 9i style rman backup scripts:</B><br> <br> Example 1: rman script<br> <br> <B> RMAN> run {<br> &nbsp allocate channel t1 type disk FORMAT '/disk1/backups/%U';<br> &nbsp backup database tag full_251109 ; <I>You may also "tag" (or name) a backup</I><br> &nbsp sql 'alter system archive log current'; <I># Just before backup, let's archive the current redolog</I><br> &nbsp backup archivelog all delete input ; <I># Are you sure to delete all archives after backup ?</I><br> &nbsp release channel t1;<br> &nbsp }<br> </B> <br> Example 2: rman script (using sbt, or that is,"tape")<br> <br> <B> RMAN> run {<br> &nbsp allocate channel t1 type 'sbt_tape' parms 'ENV=(tdpo_optfile=/usr/tivoli/tsm/client/oracle/bin64/tdpo.opt)';<br> &nbsp backup database tag full_251109 ;<br> &nbsp sql 'alter system archive log current'; <br> &nbsp backup archivelog all; </I><br> &nbsp release channel t1;<br> &nbsp }<br> </B> <br> <br> <B>10g style rman backup scripts:</B><br> <br> On 10g/11g, you can still use backups scripts as shown in examples 1 and 2.<br> But, examples 3 and 4 are a bit more "modern", and might be a better solution in many situations.<br> It uses <B>incremental backups</B>.<br> <br> An incremental backup, contains all changed blocks, with respect to the former backup.<br> So, if you make a full backup (level 0), at time=t0, and some time later an incremental backup (level 1) at time=t1,<br> the latter backup will contain all changes that a possible large number of archived redologs will hold as well,<br> during the interval t1-t0.<br> It means you do not have to have so much "attention" to those archived redologs (they are still important ofcourse).<br> Only the archived redologs that were created after the level 1 backup, are important for full recovery.<br> <br> Example 3: level 0 rman script (full backup)<br> <br> <B> RMAN> run {<br> &nbsp backup incremental level 0 as compressed backupset database;<br> &nbsp }<br> </B> <br> Note that the basic command is "BACKUP DATABASE", but all sorts of options can be placed in between<br> those keywords (BACKUP and DATABASE). Here, we have specified to compress the full backup.<br> <br> Example 4: level 1 rman script (incremental backup)<br> <br> <B> RMAN> run {<br> &nbsp backup incremental level 1 as compressed backupset database;<br> &nbsp }<br> </B> <br> A full database backup, corresponds to an INCREMENTAL LEVEL 0 backup.<br> An incremental backup, corresponds to an INCREMENTAL LEVEL 1 backup.<br> <br> An incremental LEVEL 1 backup, will only backup the changed database blocks that have been changed since<br> <B>the former</B> LEVEL 0 or LEVEL 1 backup.<br> So, in fact with an incremental level 1, you only capture all changes compared <br> to the former backup (level 0 or level 1).<br> <br> RMAN will always chooses incremental backups over archived logs, <br> as applying changes at a block level is faster than reapplying individual changes.<br> <br> <B>Specifying the default backup locations for disk backups:</B><br> <br> --> Not using a Flash Recovery Area (10g) or Fast recovery Area (11g):<br> <br> You might wonder where the disk based backups will be stored, since the allocate channel statement<br> usually does not refer to a disk location.<br> It's usually handled by setting a persistent rman setting like for example:<br> <br> RMAN> CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/backup/ora_df%t_s%s_s%p';<br> <br> Here you see the location sepcified as "/backup", while some additional file parameters<br> determine the format of a 'backup piece', like %t is replaced with a four byte time stamp, %s <br> with the backup set number, and %p with the backup piece number.<br> <br> You can also configure an ASM disk group as your destination, as in the following example:<br> <br> RMAN> CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '+dgroup1';<br> <br> <br> --> Using a "Flash Recovery Area" (10g) or "Fast recovery Area" (11g):<br> <br> Then this will be the default location for all your RMAN backups, unless you override it.<br> <br> <br> Example 5: unix shell script using rman in the traditional way.<br> <br> #!/usr/bin/ksh<br> <br> export ORACLE_SID=PROD<br> export ORACLE_HOME=/opt/oracle/product/10.2<br> <br> # Add date to be used in logfile<br> export TDAY=`date +%a`<br> export backup_dir = /dumps/oracle/backup<br> export LOGFILE=$backup_dir/prod.log<br> <br> echo "Backup Started at `date` \n" >$LOGFILE<br> <br> $ORACLE_HOME/bin/rman <<'!' 1>> $LOGFILE 2>&1<br> <br> # Connect to the database. Change this to Sys logon if not using /<br> <br> connect target /<br> <br> # Allocate Disk channels. You might allocate more if you have sufficient resources: <br> run {<br> allocate channel t1 type disk;<br> allocate channel t2 type disk;<br> <br> #backup the whole source database.<br> # Use tags for easy identification from other backups<br> backup tag whole_database_open format '$backup_dir/df_%u' database;<br> <br> # archive the current redologfile<br> sql 'alter system archive log current';<br> <br> #backup the archived logs<br> backup archivelog all format '$backup_dir/al_%u';<br> <br> # backup a copy of the controlfile that contains records for the backups just made<br> backup current controlfile format '$backup_dir/cf_%u';<br> }<br> exit<br> <br> echo "Backup Finished at `date` \n" >>$LOGFILE<br> <br> <br> <font face="courier" size=2 color="blue"> <B>25.4 SOME SIMPLE RMAN RESTORE AND RECOVERY EXAMPLES:</B><br> <font face="courier" size=2 color="black"> <br> RMAN has it's metadata, and thus it "knows" what "to do" when you need to restore and recover a database.<br> <br> Especially in 11g, the graphical Enterprise Manager (EM), has some really nifty features to get you<br> out of trouble when a restore and recovery is needed. But working from the EM is not a subject in this document.<br> Again, if restore and recovery subjects are important for you (which is true if you are a DBA),<br> and you use 11g, then you should investigate all options that are available in 11G EM.<br> <br> Here we will only show <B>a few simple and traditional</B> examples using RMAN for a restore and recovery.<br> <br> <B><U>PART 1. COMPLETE RECOVERY:</U></B><br> <br> Example 1: Restore and Recovey of an entire database (database files only):<br> <br> Suppose you have crash of some sort, like a diskcrash. If the controlfiles and online redo logs are still present,<br> a whole database recovery can be achieved by running the script below.<br> If you have also lost all controlfiles and online redologs, the restore will be a little more involved,<br> than what is shown below.<br> <br> <B> run {<br> &nbsp startup mount;<br> &nbsp restore database;<br> &nbsp recover database;<br> &nbsp alter database open;<br> }<br> </B> <br> Explanation:<br> Since the database is damaged (like missing dbf files), you cannot open the database.<br> Anyway, it should not be openened (and ofcourse it cannot be openened). Suppose you want to restore<br> over corrupt files, then those files should not be active in anyway.<br> But we still need the "Instance" to be up, and that will be achieved using <B>"startup mount"</B>.<br> Note that starting the instance using "startup mount", implies that a good controlfile is available,<br> and at the same time, that command will <B> not open</B> the database.<br> Next, we use the <B> "restore database"</B> command. Since RMAN knows where to find all backups,<br> it will apply the last full backup, and if present, it then applies the differential backups.<br> After all of the above is ready, the database is very likely to be in an inconsistent state, and the last<br> archived redologs needs to be applied. This is called "recovery".<br> <br> In some cases, it might also be that after the full backup restore, and optionally the differential backup restore,<br> that no archived redologs need to be applied, and the last step will then be that the recovery process<br> uses the "online" redologs for the last steps of recovery.<br> Obviously then, in this case, the last differential backup that was performed, already contained almost<br> all change vectors, so for the last step, rman uses the online logs for the latest transactions.<br> <br> <br> Example 2: Restore and Recovery of a tablespace (except system or undo):<br> <br> Suppose you have a problem with the SALES tablespace. If you need to restore it, you can use a script<br> similar to what is shown below.<br> If it is just an ordinary user tablespace, the database itself can stay online and open.<br> But, that particular tablespace needs to be in a closed state, before you can restore it.<br> <br> <B> run {<br> &nbsp sql 'ALTER TABLESPACE SALES OFFLINE IMMEDIATE';<br> &nbsp restore tablespace SALES;<br> &nbsp recover tablespace SALES;<br> &nbsp sql 'ALTER TABLESPACE SALES ONLINE'; <br> }<br> </B> <br> <br> <B><U>PART 2. INCOMPLETE RECOVERY:</U></B><br> <br> As you would expect, RMAN allows incomplete recovery to a specified date/time, SCN or sequence number.<br> <br> In this case, the database gets restored to a time prior to changes that are stored in the online redologs.<br> RMAN will make the database consistent, after restore and recovery, but the restored databases lives with<br> a max System Change Number (SCN) that is lower than the SCN at the time of crash.<br> Since in all restore scenario's sofar in this note, we have assumed that the "online redologs" were not affected<br> by the crash, and only database files are corrupt or missing (possibly due to a diskcrash).<br> So, the online redologs are "more in the future" than the restored database files.<br> This situation must be resolved by clearing, or resetting, the online redologs.<br> <br> <B> run { <br> &nbsp startup mount;<br> &nbsp set until time 'Nov 15 2008 09:00:00';<br> &nbsp # set until scn 1000; # alternatively, you can specify SCN<br> &nbsp # set until sequence 9923; # alternatively, you can specify log sequence number<br> &nbsp restore database;<br> &nbsp recover database;<br> &nbsp alter database open resetlogs;<br> }<br> </B> <br> <br> <font face="courier" size=2 color="blue"> <B>25.5 SOME NOTES ON "BACKUP SET" AND "COPY":</B><br> <font face="courier" size=2 color="black"> <br> Format of the backup:<br> <br> Usually, what you will create for backups using RMAN, will be "backup sets".<br> <br> But, actually, you can create either "backup sets" or "image copies".<br> <br> <ul> <li> Backup sets are logical entities produced by the RMAN BACKUP command. It is stored in an rman specific format,<br> and will only contain the used blocks of the datafiles, thereby saving space.</li> <li>Image copies are exact byte-for-byte copies of files. It does not save in space the way a backup set does.<br> RMAN will create "image copies", when you use the "AS COPY" option with the BACKUP command.</li> </ul> <br> Both Backup sets and image copies are recorded in the RMAN repository.<br> As said before, usually the default type of "backup set" will be used by rman.<br> Image copy backups, can only be created on disk.<br> <br> If you want to change the "default" type, you can use commands like:<br> <br> RMAN> CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO COPY; # image copies<br> RMAN> CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO BACKUPSET; # uncompressed backupset<br> <br> Some examples:<br> <br> You do not need to use the rman script structure (<I>run { statements }</I>), but you can use direct commands as well.<br> Returning to the differences of creating "image copies" or "backup sets", here are a few examples:<br> <br> RMAN> BACKUP AS COPY DATABASE; #image copies<br> <br> RMAN> BACKUP DATABASE; # backup set<br> <br> <br> <br> <font face="courier" size=2 color="blue"> <B>25.6 SPECIFYING THE BACKUP LOCATIONS:</B><br> <font face="courier" size=2 color="black"> <br> <B>Using Persistent settings:</B><br> <br> We already have seen that using 'persistent' configuration settings, you can determine where<br> per default the backups will be stored, like for example:<br> <br> <B> RMAN> CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/backups/%U';<br> RMAN> CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT 'c:\rbkup\data_%s_%p_%U.bak';<br> <br> RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;<br> RMAN> CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/controlbck/cf%F';<br> <br> </B> If you have set similar settings as above, you do not need to specify locations in the backup commands and scripts.<br> Specifically, if you specify explicitly a channel in the run block (allocate channel t1 DEVICE type DISK;)<br> without the FORMAT specification, then your scripts might fail.<br> <br> So, or use FORMAT in the persistent settings, or use a complete FORMAT in the ALLOCATE/CONFIGURE channel, or<br> use a complete FORMAT in the BACKUP command.<br> <br> <B>Using the FORMAT clause:</B><br> <br> You can also specify a FORMAT clause with the individual BACKUP command to direct the output<br> to a specific location of your choice, like for example:<br> <br> BACKUP DATABASE FORMAT="/backups/backup_%U";<br> <br> Backups in this case are stored with generated unique filenames in the location /backups/.<br> That notice that the %U, used to generate a unique string at that point in the filename, is required.<br> <br> You can also use the FORMAT clause to backup to an ASM diskgroup. like so:<br> <br> RMAN> BACKUP DATABASE FORMAT '+dgroup1'; <br> <br> Because ASM controls all filenames, you do not need to specify names any further.<br> <br> <br> <br> <font face="arial" size=2 color="blue"> <h3 id="section26">26. HOW TO SEE IF THE DATABASE IS DOING A LARGE ROLLBACK:</h3> <font face="courier" size=2 color="black"> <br> Remember, this note is about SQL only, and does not say anything about using Graphical tools like the EM.<br> <br> If you have a large transaction, that gets interrupted in some way, and the database will then<br> rollback from the UNDO tablespace, it would be nice to see where it "is now" and how much<br> undo blocks "are still to go".<br> One way to see that is using the following query. You will see the number of undo blocks gets lower<br> and lower as time pass by.<br> <br> <B> SELECT a.sid, a.saddr, b.ses_addr, a.username, b.xidusn, b.used_urec, b.used_ublk<br> FROM v$session a, v$transaction b<br> WHERE a.saddr = b.ses_addr;<br> </B> <br> This is the same query as was already shown in section 3.4<br> <br> <br> <br> <font face="arial" size=2 color="blue"> <h3 id="section27">27. A SIMPLE WAY TO CLONE A 9i/10g/11g DATABASE:</h3> <font face="courier" size=2 color="black"> The procedure for 11g is only very slightly different from 9i or 10g, so lets discuss the method<br> for 9i/10g first.<br> <br> <br> <B>Cloning a 9i or 10g database to another Server:</B><br> <br> <font face="courier" size=2 color="blue"> <B>Problem:</B><br> <br> Suppose on Server "A" you have the database "PROD10G" running. This is the "source" database.<br> <br> Now, you want to quicly clone that database to Server "B",<br> where it will use the databasename "TEST10G".<br> Here we assume that Server B also has Oracle installed, which is on the same level as on Server A.<br> <font face="courier" size=2 color="black"> <br> We are not using RMAN, or exp, or expdp, to clone the database. We "simply" use scp (or other copy tool),<br> to copy all database files from ServerA to ServerB.<br> Then, we use a script, containing the CREATE CONTROLFILE command, to "revive" the database on another Server.<br> <br> The method is way simpler if the source database can be shutdown in a consistent way. Then we know for sure<br> that all database files are consistent, and basically we only have to do this:<br> <br> - On Server B, create a similar directory structure to hold the clone's database files, and logfiles.<br> For example, that could be something like "/data/oradata/test10g" for the database files,<br> and something like "/data/oradata/admin/test10g" to hold the bdump, cdump, pfile, adump directories.<br></li> <br> - Create an init.ora file for TEST10G, using the init.ora of PROD10G. Ofcourse, you need to edit<br> that init.ora later on, so that it correctly has the right databasename, and filesystem paths.<br> If you don't have an init.ora, then you surely have the spfile.ora of PROD10G.<br> Then, simply create an init.ora, using a similar statement as was shown in section 10, like for example:<br> <br> CREATE PFILE='/tmp/init.ora' <br> FROM SPFILE='/data/oradata/admin/prod10g/pfile/spfile.ora';<br> <br> - copy the init.ora file over to Server B, using "scp" (or similar command),<br> to for example "/data/oradata/admin/test10g/pfile".<br></li> <br> scp init.ora oracle@ServerB:/data/oradata/admin/test10g/pfile<br> <br> - On ServerB, edit the init.ora and change stuff like the database name, the filepaths of the (to be created), controlefiles, the location of the "background_dump_dest" etc..<br> <br> - On ServerA, the source machine, now let's create the "CREATE CONTROLFILE.." script.<br> Start a sqlplus session, logon, and use the statement:<br> <br> ALTER DATABASE BACKUP CONTROLFILE TO TRACE AS '/tmp/cc.sql';<br> <br> This will produce an ascii file, which is editable.<br> <br> - copy the newly created script over to ServerB, for example to the directory<br> where the databasefiles will also be stored:<br> <br> scp cc.sql oracle@ServerB:/data/oradata/test10g<br> <br> - On ServerB, edit the cc.sql script:<br> <br> Usually, the script has two large similar sections, which almost are identical.<br> If you do not know what a mean, then just browse through the script. But maybe you only have one section.<br> Anyway, just leave one block and get rid of all comments.<br> Also, change all filepaths to resemble the new situation at ServerB.<br> <br> Especially, you need to DELETE the last lines which should resemble this:<br> <br> <font face="courier" size=2 color="red"> RECOVER DATABASE USING BACKUP CONTROLFILE<br> <br> ALTER DATABASE OPEN RESETLOGS;<br> <font face="courier" size=2 color="black"> <br> Make sure that those two lines are gone. Also, don't have any blank lines in the script.<br> Get rid of the STARTUP NOMOUNT command too.<br> <br> Your create controlfile script should resemble the one that's shown below:<br> <br> <font face="courier" size=2 color="blue"> CREATE CONTROLFILE SET DATABASE "TEST10G" RESETLOGS NOARCHIVELOG<br> MAXLOGFILES 16<br> MAXLOGMEMBERS 3<br> MAXDATAFILES 100<br> MAXINSTANCES 8<br> MAXLOGHISTORY 292<br> LOGFILE<br> GROUP 1 '/data/oradata/test10g/REDO01.LOG' SIZE 50M,<br> GROUP 2 '/data/oradata/test10g/REDO02.LOG' SIZE 50M,<br> GROUP 3 '/data/oradata/test10g/REDO03.LOG' SIZE 50M<br> DATAFILE<br> '/data/oradata/test10g/SYSTEM01.DBF',<br> '/data/oradata/test10g/UNDOTBS01.DBF',<br> '/data/oradata/test10g/SYSAUX01.DBF',<br> '/data/oradata/test10g/USERS01.DBF',<br> '/data/oradata/test10g/EXAMPLE01.DBF'<br> CHARACTER SET WE8MSWIN1252<br> ;<br> <font face="courier" size=2 color="black"> <br> Ofcourse, you may have a lot more datafiles and redologs than my simple example. But make sure, the "structure"<br> really resembles the structure as shown above, and that there are no blank lines, and that the script just starts<br> with "CREATE CONTROLFILE..", and that the "RECOVER DATABASE USING BACKUP CONTROLFILE" and "ALTER DATABASE OPEN RESETLOGS"<br> lines are NOT there.<br> <br> Note the "SET DATABASE" keywords, because that's how you can change a Database name.<br> <br> - Now its time to copy the databasefiles of PROD10G on ServerA.<br> Start a sqlplus session and logon. Shutdown the database cleanly using:<br> <br> SQL> shutdown immediate;<br> <br> After the database has been closed, copy all databasefiles (except controlfiles) to ServerB.<br> <br> scp * oracle@ServerB:/data/oradata/test10g/<br> <br> Here I copied all files (database, redolog, and controlfiles) to SeverB.<br> At ServerB, I can just delete the controlfiles after the copy is ready.<br> <br> As a last check, check the permissions on the files and directories.<br> If that's all OK, and the init.ora is OK as well, we can execute the CREATE CONTROLFILE script.<br> <br> On ServerB:<br> <br> - Go to the database data directory<br> <br> cd /data/oradata/test10g<br> <br> - Set the SID to point to TEST10G:<br> <br> export ORACLE_SID=TEST10G<br> <br> - Start sqlplus.<br> <br> sqlplus /nolog<br> <br> SQL> connect / as sysdba<br> <br> - Start the instance using the new init.ora file, but do not mount the database:<br> <br> SQL> startup nomount pfile='/data/oradata/admin/test10g/pfile/init.ora'<br> <br> - One the instance has started, let's create the controlfile:<br> <br> SQL> @cc.txt<br> <br> Control file created.<br> <br> Were done ! The TEST10G database is created and opened.<br> <br> If you have errors, it's usually due to wrong directories and names in the init.ora file, or in the script.<br> <br> Note: notice that I first went to the datadirectory, where I stored the "cc.sql" script as well as the databasefiles.<br> This avoids a possible error with terminal/character settings, because sometimes it is seen that executing<br> a script in the form of "@\path\script" where the "@\" works not properly.<br> <br> <br> <B>Windows:</B><br> <br> The above example works not only in Unix environments, but for example on Windows as well.<br> Only, in Windows you also need to create an Instance first, before you execute any script.<br> This is so, because in Windows, any instance is equivalent to a Windows "service".<br> In Windows, use a command similar to the example below, first.<br> <br> C:\> oradim -new -sid TEST10G -INTPWD mypassword -STARTMODE AUTO<br> <br> <br> <B>Cloning a 11g database to another Server:</B><br> <br> The procedure is almost identical to what we have seen above.<br> However, there are a few things to take into account.<br> <br> 1. ADR:<br> <br> Only, you need to take the new DIAGNOSTICS paradigm into account.<br> Instead of the 9i or 10g like "bdump", "cdump" etc.. log/trace directories, Oracle 11g uses "ADR",<br> which is short for Automatic Diagnostic Repository.<br> <br> The alert log and all trace files for background and server processes are written to the<br> Automatic Diagnostic Repository (ADR), the location of which is specified by the DIAGNOSTIC_DEST <br> initialization parameter. <br> <br> 2. Fast Recovery Area:<br> <br> The Flash Recovery area, is now called the Fast recovery Area.<br> <br> Ofcourse, if you create an init.ora from an 11g spfile.ora, you would have seen those differences,<br> and you automatically would have created the neccessary structures on the target machine.<br> <br> <br> Note that in this procedure, we had the "luxury" to shutdown the source database, so that<br> we have a fully consistent set of files, which we copied over to ServerB.<br> After the copy to ServerB is ready, the original database can be immediately brought online again.<br> <br> So, in this case, we do not need to RECOVER anything.<br> <br> If you cannot close the source database, because it's strictly 24 x 7, or the filecopy is expected<br> to take too much time, then you can consider an RMAN, or an expdp, based cloning procedure.<br> <br> Now, you may also ask: What about a source on ASM and a target that uses plain filesystems? Or the other way around?<br> Sure, sophisticated cloning techniques do exist, but my note is simple and humble.<br> <br> <br> <br> <br> <br> <br> <br> <br> <br> </body> </html>