CHECK PGA INFORMATION AND CONSUMPTION OF MEMORY IN ORACLE SQL QUERY:

>>>

 SET LINESIZE 145
 SET PAGESIZE 9999
 COLUMN sid FORMAT 999 HEADING 'SID'
 COLUMN oracle_username FORMAT a12 HEADING 'Oracle User' JUSTIFY right
 COLUMN os_username FORMAT a9 HEADING 'O/S User' JUSTIFY right
 COLUMN session_program FORMAT a18 HEADING 'Session Program' TRUNC
 COLUMN session_machine FORMAT a8 HEADING 'Machine' JUSTIFY right TRUNC
 COLUMN session_pga_memory FORMAT 9,999,999,999 HEADING 'PGA Memory'
 COLUMN session_pga_memory_max FORMAT 9,999,999,999 HEADING 'PGA Memory Max'
 COLUMN session_uga_memory FORMAT 9,999,999,999 HEADING 'UGA Memory'
 COLUMN session_uga_memory_max FORMAT 9,999,999,999 HEADING 'UGA Memory MAX'

 SELECT
    s.sid sid
    , lpad(s.username,12) oracle_username
    , lpad(s.osuser,9) os_username
    , s.program session_program
    , lpad(s.machine,8) session_machine
    , (select ss.value from v$sesstat ss, v$statname sn
    where ss.sid = s.sid and
    sn.statistic# = ss.statistic# and
   sn.name = 'session pga memory') session_pga_memory
   , (select ss.value from v$sesstat ss, v$statname sn
   where ss.sid = s.sid and
   sn.statistic# = ss.statistic# and
   sn.name = 'session pga memory max') session_pga_memory_max
   , (select ss.value from v$sesstat ss, v$statname sn
   where ss.sid = s.sid and
   sn.statistic# = ss.statistic# and
   sn.name = 'session uga memory') session_uga_memory
   , (select ss.value from v$sesstat ss, v$statname sn
   where ss.sid = s.sid and
   sn.statistic# = ss.statistic# and
   sn.name = 'session uga memory max') session_uga_memory_max
   FROM
   v$session s
   ORDER BY session_pga_memory DESC
   /

CHECK WAIT CLASS IN ORACLE DATABASE :

>> select event,p1,p2,wait_class from v$session_wait where sid=29;

EXAMPLE :

SQL> select event,p1,p2,wait_class from v$session_wait where sid=29;

EVENT                                                                    P1
---------------------------------------------------------------- ----------
        P2 WAIT_CLASS
---------- ----------------------------------------------------------------
SQL*Net message from client                                      1413697536
         1 Idle

 

INFORMATION ABOUT SGA :

>> select * from v$sgainfo;

CHECK IMPORTANT PARAMETERS IN A DATABASE:

>> show parameter db

>> show parameter _db

>> show parameter database

HOW TO SELECT ALL COLUMNS IN AN ORACLE DATABASE :

select OWNER, TABLE_NAME, COLUMN_NAME from dba_tab_columns;

SGA_MAX_SIZE, SGA_TARGET, MEMORY_TARGET AND MEMORY_MAX_TARGET PLAYS A MAJOR ROLE IN ENHNACING THE PEFORMANCE OF THE DATABASE :

>> INCREASE THE SGA_MAX_SIZE PARAMETER IN ACCORDANCE WITH THE RAM OF THE SERVER MACHINE :

ALTER SYSTEM SET SGA_MAX_SIZE = 20G SCOPE=BOTH;

OR

ALTER SYSTEM SET SGA_MAX_SIZE = 20G SCOPE=SPFILE;

- NOTE : THE RAM OF THE MACHINE SHOULD BE FAR GREATER THAN 20G IN ABOVE CASE (e.g. 80GB ).

 >> INCREASE THE MEMORY_TARGET PARAMETER IN ACCORDANCE WITH THE RAM OF THE SERVER MACHINE :



ALTER SYSTEM SET MEMORY_TARGET = 20G SCOPE=BOTH;

OR

ALTER SYSTEM SET MEMORY_TARGET = 20G SCOPE=SPFILE;


>>  INCREASE THE MEMORY_MAX_TARGET PARAMETER IN ACCORDANCE WITH THE RAM OF THE SERVER MACHINE :


ALTER SYSTEM SET MEMORY_MAX_TARGET = 20G SCOPE=BOTH;

OR

ALTER SYSTEM SET MEMORY_MAX_TARGET  = 20G SCOPE=SPFILE;


NOTE : KEEP THESE 3 PARAMETERS VALUE SAME(SGA_MAX_SIZE = 20G,MEMORY_TARGET = 20G, MEMORY_MAX_TARGET = 20G) FOR ENHANCED PERFORMANCE OF THE DATABASE AND FOR AMM AND ASMM TO BE ENABLED.

HOW TO ENABLE AUTOMATIC MEMORY MANAGEMENT IN ORACLE - PARAMETERS AND CONDITIONS TO FULFILL FOR AMM AND ASMM :

>> SGA_TARGET SHOULD BE SET TO '0' FOR AUTOMATIC MEMORY MANAGEMENT AND ASMM TO BE ENABLED IN ORACLE DATABASE.

ALTER SYSTEM SET SGA_TARGET=0 SCOPE=BOTH;

OR

ALTER SYSTEM SET SGA_TARGET=0 SCOPE=SPFILE;

>>  THIS CONDITION NEEDS TO BE FULFILLED FOR AUTOMATIC MEMORY MANAGEMENT AND ASMM TO BE ENABLED.

SETTING VALUES FOR PARAMETERS SGA_MAX_SIZE, MEMORY_TARGET AND MEMORY_MAX_TARGET :

SGA_MAX_SIZE is dependent on MEMORY_MAX_TARGET and cannot be greater than it. So first increase the MEMORY_MAX_TARGET and then sga_max_size.

ALTER SYSTEM SET MEMORY_MAX_TARGET = nM SCOPE = SPFILE;
--------------------------------

select * from v$sga_target_advice order by sga_size;

--------------------------------

Run the following query to obtain a value for SGA_TARGET:

SELECT (
   (SELECT SUM(value) FROM V$SGA) -
   (SELECT CURRENT_SIZE FROM V$SGA_DYNAMIC_FREE_MEMORY)
   ) "SGA_TARGET"
FROM DUAL;

---------------------------

Set the value of SGA_TARGET, either by editing the text initialization parameter file and restarting the database, or by issuing the following statement:

---------------------------

ALTER SYSTEM SET SGA_TARGET=value [SCOPE={SPFILE|MEMORY|BOTH}]

---------------------------

---SGA_TARGET should never be greater than Maximum SGA Size. It can be equal to but not greater than.

---------------------------

You can take advantage of automatic shared memory management by setting Total SGA Size to 992M in Oracle Enterprise Manager, or by issuing the following statements:

ALTER SYSTEM SET SGA_TARGET = 992M;
ALTER SYSTEM SET SHARED_POOL_SIZE = 0;
ALTER SYSTEM SET LARGE_POOL_SIZE = 0;
ALTER SYSTEM SET JAVA_POOL_SIZE = 0;
ALTER SYSTEM SET DB_CACHE_SIZE = 0;
ALTER SYSTEM SET STREAMS_POOL_SIZE = 0;
where 992M = 1200M minus 208M.


SELECT SUM(value) FROM V$SGA
SELECT CURRENT_SIZE FROM V$SGA_DYNAMIC_FREE_MEMORY

------------------------------

-- Increasing the Maximum SGA Size and greater than SGA_TARGET :

show sga;

create pfile from spfile;

alter system set sga_max_size=20m scope=spfile;

shut immediate;

sqlplus /nolog

connect 'sys/sys@orclnew' as sysdba

Enter password:(Just Hit Enter.Donot provide password)
Connected to an idle instance.

startup mount

alter database open;


-----------------------------


To check SGA currenly allocated I would use:

select sum(value) from v$sga;

or

select sga_size from v$sga_target_advice where sga_size_factor=1;


To check PGA currently allocated I would use:

select value from v$pgastat where name like 'total%alloc%';


------------------------------

COLUMN name FORMAT A30
COLUMN value FORMAT A10
SELECT name, value
FROM   v$parameter
WHERE  name IN ('pga_aggregate_target', 'sga_target')
UNION
SELECT 'maximum PGA allocated' AS name, TO_CHAR(value) AS value
FROM   v$pgastat
WHERE  name = 'maximum PGA allocated';



-- Calculate MEMORY_TARGET

SELECT sga.value + GREATEST(pga.value, max_pga.value) AS memory_target
FROM (SELECT TO_NUMBER(value) AS value FROM v$parameter WHERE name = 'sga_target') sga,(SELECT TO_NUMBER(value) AS value FROM v$parameter WHERE name = 'pga_aggregate_target') pga,(SELECT value FROM v$pgastat WHERE name = 'maximum PGA allocated') max_pga;

CHECK TABLESPACE SIZE,PATH AND TABLESPACE_NAME IN ORACLE:


>> select file_name,status,tablespace_name,bytes/1024/1024 from dba_data_files;

HOW TO CHECK SGA_MAX_SIZE , MEMORY_TARGET, MEMORY_MAX_TARGET IN ORACLE DATABASE :

>> show parameter sga_max_size

>> show parameter memory_target

>> show parameter memory_max_target;

HOW TO CHECK FULL SIZE OF AN ORACLE DATABASE (APPROXIMATE) :

select (select sum(bytes)/1024/1024 from dba_data_files)+
(select sum(bytes)/1024/1024 from dba_temp_files) "Size in MB" from dual;

STEP BY STEP PROCESS TO DUPLICATE AN ORACLE DATABASE & SIMPLE STEPS, EASY TO UNDERSTAND WITH SQL QUERIES :

1. connect to TEST database

2. create pfile from spfile

3. modify the pfile according the newdatabase and copy  to location D:\app\admin\product\11.2.0\dbhome_1\database\INITTESTpilot.ORA

4. create database structure (adump,dpdump and flash recovery area)

5. optional "alter database backup controlfile to trace;" 

6. shut down the TEST database

7. copy oradata file to new location and control

8. create services :
    a. C:\> oradim -new -sid nps01

    b. Make entries in listner.ora 

    c. Make entry in tnsnames.ora

9. sqlplus sys/USERPRO@nps01 as sysdba

   a) Open the database in nomount

10.run this script on new database sql promopt>>

sqlplus>

CREATE CONTROLFILE REUSE set DATABASE "TEST2" RESETLOGS  NOARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 1168
LOGFILE
  GROUP 1 'D:\app\USERPRO\oradata\TEST2\REDO01.LOG' size 50M ,
  GROUP 2 'D:\app\USERPRO\oradata\TEST2\REDO02.LOG' size 50M ,
  GROUP 3 'D:\app\USERPRO\oradata\TEST2\REDO03.LOG' size 50M ,
-- STANDBY LOGFILE
DATAFILE
  'D:\app\USERPRO\oradata\TEST2\SYSTEM01.DBF' ,
  'D:\app\USERPRO\oradata\TEST2\SYSAUX01.DBF' ,
  'D:\app\USERPRO\oradata\TEST2\UNDOTBS01.DBF' ,
  'D:\app\USERPRO\oradata\TEST2\USERS01.DBF'
CHARACTER SET WE8MSWIN1252;

11. startup pfile='D:\app\Admin\product\11.2.0\dbhome_1\database\initNPS01.ora';

12. ALTER DATABASE OPEN RESETLOGS;

13. ALTER TABLESPACE temp ADD TEMPFILE 'D:\app\USERPRO\oradata\TEST2\temp01.dbf' REUSE;

14. Shut down and startup again.

15. run command to chage data_pump_dir path-sqlplus> create or replace directory data_pump_dir as 'D:\app\USERPRO\admin\TEST2\dpdump';

HOW TO CHECK ALL TABLES FOR A PARTICULAR SCHEMA :

SELECT DISTINCT OWNER, OBJECT_NAME
  FROM DBA_OBJECTS
 WHERE OBJECT_TYPE = 'TABLE'
   AND OWNER = 'SCHEMA_NAME';

FIND ALL INDEXES ON A SPECIFIC TABLE :

select table_name,index_name from dba_indexes where table_name = 'PROJECT_JOB';

CREATE TEST TABLE OF 100000 RECORDS :


>> CREATE TABLE test_table AS
SELECT LEVEL id, SYSDATE+DBMS_RANDOM.VALUE(-1000, 1000) date_value, DBMS_RANDOM.string('A', 20) text_value
FROM dual
CONNECT BY LEVEL <= 100000

RENAMING AN ORACLE DATABASE STEP BY STEP PROCEDURE :

If the database instance runs on same node when nid.exe runs you don't need to use @:

C:\>sqlplus / as sysdba

SQL*Plus: Release 10.2.0.5.0 - Production on Fri Dec 14 19:51:42 2012

Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area 1610612736 bytes
Fixed Size                  2077904 bytes
Variable Size             385878832 bytes
Database Buffers         1207959552 bytes
Redo Buffers               14696448 bytes
Database mounted.
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

C:\>nid target=sys/oracle dbname=test

DBNEWID: Release 10.2.0.5.0 - Production on Fri Dec 14 19:52:24 2012

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

Connected to database 10GR2 (DBID=1672979762)

Connected to server version 10.2.0

Control Files in database:
    C:\ORACLE\PRODUCT\10.2.0\ORADATA\10GR2\CONTROL01.CTL
    C:\ORACLE\PRODUCT\10.2.0\ORADATA\10GR2\CONTROL02.CTL
    C:\ORACLE\PRODUCT\10.2.0\ORADATA\10GR2\CONTROL03.CTL

Change database ID and database name 10GR2 to TEST? (Y/[N]) => y

Proceeding with operation
Changing database ID from 1672979762 to 2100191993
Changing database name from 10GR2 to TEST
    Control File C:\ORACLE\PRODUCT\10.2.0\ORADATA\10GR2\CONTROL01.CTL - modified
    Control File C:\ORACLE\PRODUCT\10.2.0\ORADATA\10GR2\CONTROL02.CTL - modified
    Control File C:\ORACLE\PRODUCT\10.2.0\ORADATA\10GR2\CONTROL03.CTL - modified
    Datafile C:\ORACLE\PRODUCT\10.2.0\ORADATA\10GR2\SYSTEM01.DBF - dbid changed, wrote new name
    Datafile C:\ORACLE\PRODUCT\10.2.0\ORADATA\10GR2\UNDOTBS01.DBF - dbid changed, wrote new name
    Datafile C:\ORACLE\PRODUCT\10.2.0\ORADATA\10GR2\SYSAUX01.DBF - dbid changed, wrote new name
    Datafile C:\ORACLE\PRODUCT\10.2.0\ORADATA\10GR2\USERS01.DBF - dbid changed, wrote new name
    Datafile C:\ORACLE\PRODUCT\10.2.0\ORADATA\10GR2\TEMP01.DBF - dbid changed, wrote new name
    Control File C:\ORACLE\PRODUCT\10.2.0\ORADATA\10GR2\CONTROL01.CTL - dbid changed, wrote new name
    Control File C:\ORACLE\PRODUCT\10.2.0\ORADATA\10GR2\CONTROL02.CTL - dbid changed, wrote new name
    Control File C:\ORACLE\PRODUCT\10.2.0\ORADATA\10GR2\CONTROL03.CTL - dbid changed, wrote new name
    Instance shut down

Database name changed to TEST.
Modify parameter file and generate a new password file before restarting.
Database ID for database TEST changed to 2100191993.
All previous backups and archived redo logs for this database are unusable.
Database has been shutdown, open database with RESETLOGS option.
Succesfully changed database name and ID.
DBNEWID - Completed succesfully.

But you need additional steps (ORADIM is only for Windows):
- changing ORACLE_SID
- renaming init. file
- changing DB_NAME instance parameter
- opening database with RESETLOGS option.

C:>oradim -new -sid test
Instance created.

C:\>set ORACLE_SID=TEST

C:\>cd c:\oracle\product\10.2.0\db_1\database

C:\oracle\product\10.2.0\db_1\database>rename init10GR2.ora initTEST.ora

C:\>sqlplus / as sysdba

SQL*Plus: Release 10.2.0.5.0 - Production on Fri Dec 14 20:00:24 2012

Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> startup nomount
ORACLE instance started.

Total System Global Area 1610612736 bytes
Fixed Size                  2077904 bytes
Variable Size             385878832 bytes
Database Buffers         1207959552 bytes
Redo Buffers               14696448 bytes
SQL> alter system set db_name=TEST scope=spfile;

System altered.

SQL> startup force
ORACLE instance started.

Total System Global Area 1610612736 bytes
Fixed Size                  2077904 bytes
Variable Size             385878832 bytes
Database Buffers         1207959552 bytes
Redo Buffers               14696448 bytes
Database mounted.
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

SQL> alter database open resetlogs;

Database altered.

SQL> startup force
ORACLE instance started.

Total System Global Area 1610612736 bytes
Fixed Size                  2077904 bytes
Variable Size             385878832 bytes
Database Buffers         1207959552 bytes
Redo Buffers               14696448 bytes
Database mounted.
Database opened.

SQL> select name from v$database;

NAME
---------
TEST

SQL>

CHECK DATABASE CHARACTER SETS :


SQL> SHOW PARAMETER NLS_LANGUAGE;


SQL> SELECT name, value$ from SYS.PROPS$ WHERE name='NLS_CHARACTERSET';



SQL> SELECT * from NLS_DATABASE_PARAMETERS;



SQL> SELECT * FROM NLS_INSTANCE_PARAMETERS;

ALTERNATING ANALYZE QUERIES FOR PERFORMANCE ENHANCEMENT :



>> EXEC DBMS_UTILITY.analyze_schema('SCHEMA_NAME,'COMPUTE');

>> exec DBMS_UTILITY.ANALYZE_SCHEMA('SCHEMA_NAME','COMPUTE');

>>EXEC DBMS_UTILITY.ANALYZE_SCHEMA('SCHEMA_NAME','ESTIMATE', estimate_rows => 1000);

>> exec DBMS_UTILITY.ANALYZE_SCHEMA('SCHEMA_NAME','ESTIMATE', estimate_percent => 25);

>> exec dbms_utility.analyze_schema(‘SCHEMA_NAME’,'ESTIMATE’, NULL, 50, ‘FOR ALL INDEXES’);

>> exec dbms_utility.analyze_schema(‘SCOTT’,'ESTIMATE’, NULL, 50, ‘FOR TABLE’);

GATHER STATISTICS FOR A SPECIFIC TABLE  AND SPECIFIC USER TO ENHANCE PERFORMANCE OF RUNNING SQL QUERIES :


>>  EXECUTE DBMS_STATS.GATHER_TABLE_STATS('SCHEMA_NAME', 'TABLE_NAME',estimate_percent => dbms_stats.auto_sample_size)

BEST QUERY TO CHECK TABLESPACE SIZE IN ORACLE AND % FREE :

select df.tablespace_name "Tablespace",
totalusedspace "Used MB",
(df.totalspace - tu.totalusedspace) "Free MB",
df.totalspace "Total MB",
round(100 * ( (df.totalspace - tu.totalusedspace)/ df.totalspace))
"Pct. Free"
from
(select tablespace_name,
round(sum(bytes) / 1048576) TotalSpace
from dba_data_files
group by tablespace_name) df,
(select round(sum(bytes)/(1024*1024)) totalusedspace, tablespace_name
from dba_segments
group by tablespace_name) tu
where df.tablespace_name = tu.tablespace_name ;

CHECK ALL INVALID OBJECTS IN A SCHEMA :

COLUMN object_name FORMAT A30
SELECT owner,
       object_type,
       object_name,
       status
FROM   dba_objects
WHERE  status = 'INVALID'
ORDER BY owner, object_type, object_name;

BEST COMMANDS FOR GATHER STATISTICS IN ORACLE AND PERFORMANCE TUNING OF THE DATABASE STEP BY STEP PROCEDURE:

- To enhance the performance of the database, it is necessary to analyze the ADDM and AWR reports and take the necessary action.

- Apart from these reports it is necessary to run the following analyze commands and gather statistics for performance improvement of the running processes.

You may run the following commands in order :


>> EXECUTE DBMS_STATS.GATHER_SCHEMA_STATS('SCHEMA_NAME',DBMS_STATS.AUTO_SAMPLE_SIZE);

                                                         
OR


EXECUTE DBMS_STATS.GATHER_SCHEMA_STATS(OWNNAME=>'SCHEMA_NAME', OPTIONS=>'GATHER AUTO');


>> EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;

>> EXEC DBMS_STATS.GATHER_SYSTEM_STATS;

>>  EXEC DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;

>> EXEC DBMS_UTILITY.analyze_schema('SCHEMA_NAME','COMPUTE');


Compilation of schema also helps in enhancing the performance of the database and running queries as it compiles all invalid objects in a particular schema. If possible, you may run the following command :

>>  EXEC DBMS_UTILITY.compile_schema(schema => 'SCHEMA_NAME');

RESTORE A PARTICULAR TABLESPACE USING RMAN :

C:\Documents and Settings\USERPRO>set oracle_sid=orcl

C:\Documents and Settings\USERPRO>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Sat Mar 21 16:14:47 2015

Copyright (c) 1982, 2010, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

C:\Documents and Settings\USERPRO>set oracle_sid=orcl

C:\Documents and Settings\USERPRO>set oracle_sid=orcl

C:\Documents and Settings\USERPRO>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Sat Mar 21 16:15:47 2015

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area  778387456 bytes
Fixed Size                  1374808 bytes
Variable Size             343934376 bytes
Database Buffers          427819008 bytes
Redo Buffers                5259264 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 4 - see DBWR trace file
ORA-01110: data file 4: 'D:\APP\USERPRO\ORADATA\ORCL\USERS01.DBF'


SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

C:\Documents and Settings\USERPRO>rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Sat Mar 21 16:16:30 2015

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORCL (DBID=1402635108, not open)

RMAN> restore tablespace users;

Starting restore at 21-MAR-15
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=133 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00004 to D:\APP\USERPRO\ORADATA\ORCL\USERS01.DBF
channel ORA_DISK_1: reading from backup piece D:\APP\USERPRO\PRODUCT\11.2.0\DBHOME_1\DATABASE\02Q2D3R1_1_1
channel ORA_DISK_1: piece handle=D:\APP\USERPRO\PRODUCT\11.2.0\DBHOME_1\DATABASE\02Q2D3R1_1_1 tag=TAG20150321T153143
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 21-MAR-15

C:\Documents and Settings\USERPRO>sqlplus sys/sys@orcl as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Sat Mar 21 16:20:45 2015

Copyright (c) 1982, 2010, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> alter database open;

Database altered.

RMAN STEP BY STEP RESTORE AND RECOVER DATABASE IN ORACLE :


--Take a backup of database from RMAN before proceeding :

=====================

SQL> shut immediate
SQL> startup mount
SQL> alter database archivelog;
SQL> alter database open;
C:\Documents and Settings\USERPRO>rman target 'sys/sys@orcl_new as sysdba'
RMAN> backup database plus archivelog;

After the backup, few rman files(4 OR many) will be created at location 'D:\app\USERPRO\product\11.2.0\dbhome_1\database\' like '03Q2D3UN_1_1' which consists of a control file.

=====================

1)

C:\Documents and Settings\USERPRO>sqlplus sys/sys@orcl as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Sat Mar 21 15:45:46 2015

Copyright (c) 1982, 2010, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

2) Now, take a backup of control files and drop the control files from oradata.Now the database would not connect.

3) 

C:\Documents and Settings\USERPRO>rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Sat Mar 21 15:48:04 2015

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database (not started)

RMAN> set DBID 1402635108

executing command: SET DBID

RMAN> startup nomount

Oracle instance started

Total System Global Area     778387456 bytes

Fixed Size                     1374808 bytes
Variable Size                343934376 bytes
Database Buffers             427819008 bytes
Redo Buffers                   5259264 bytes

RMAN> RESTORE CONTROLFILE FROM "D:\app\USERPRO\product\11.2.0\dbhome_1\database\03Q2D3UN_1_1";

Starting restore at 21-MAR-15
using channel ORA_DISK_1

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
output file name=D:\APP\USERPRO\ORADATA\ORCL\CONTROL01.CTL
output file name=D:\APP\USERPRO\ORADATA\ORCL\CONTROL02.CTL
Finished restore at 21-MAR-15


RMAN> alter database mount
2> ;

database mounted
released channel: ORA_DISK_1

RMAN> restore database
2> ;

Starting restore at 21-MAR-15
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=134 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to D:\APP\USERPRO\ORADATA\ORCL\SYSTEM01.DBF
channel ORA_DISK_1: restoring datafile 00002 to D:\APP\USERPRO\ORADATA\ORCL\SYSAUX01.DBF
channel ORA_DISK_1: restoring datafile 00003 to D:\APP\USERPRO\ORADATA\ORCL\UNDOTBS01.DBF
channel ORA_DISK_1: restoring datafile 00004 to D:\APP\USERPRO\ORADATA\ORCL\USERS01.DBF
channel ORA_DISK_1: reading from backup piece D:\APP\USERPRO\PRODUCT\11.2.0\DBHOME_1\DATABASE\02Q2D3R1_1_1
channel ORA_DISK_1: piece handle=D:\APP\USERPRO\PRODUCT\11.2.0\DBHOME_1\DATABASE\02Q2D3R1_1_1 tag=TAG20150321T153143
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:55
Finished restore at 21-MAR-15

RMAN> recover database;

Starting recover at 21-MAR-15
using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 10 is already on disk as file D:\APP\USERPRO\ORADATA\ORCL\REDO01.LOG
archived log for thread 1 with sequence 11 is already on disk as file D:\APP\USERPRO\ORADATA\ORCL\REDO02.LOG
archived log file name=D:\APP\USERPRO\ORADATA\ORCL\REDO01.LOG thread=1 sequence=10
archived log file name=D:\APP\USERPRO\ORADATA\ORCL\REDO02.LOG thread=1 sequence=11
media recovery complete, elapsed time: 00:00:04
Finished recover at 21-MAR-15

RMAN> alter database open resetlogs;

database opened

RMAN> exit

FIND DATUM,  PROJECTION  IN ORACLE :


select DATUM_NAME, INFORMATION_SOURCE from sdo_datums;


select DATUM_NAME, INFORMATION_SOURCE from sdo_datums where DATUM_NAME='CANQ-M8';

 UPDATE SEQUENCES IN ORACLE :


C:\Users\Administrator>sqlplus sys/USERPASS@gip02 as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Mon Apr 6 18:15:45 2015

Copyright (c) 1982, 2010, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> conn GIP_PKT002_0404/USERPASS@gip02
Connected.
SQL> select * from user_sequences where sequence_name like '%IP%';

SEQUENCE_NAME                   MIN_VALUE  MAX_VALUE INCREMENT_BY C O CACHE_SIZE
------------------------------ ---------- ---------- ------------ - - ----------
LAST_NUMBER
-----------
TELUS_IPID_SEQ                   95000000 1.0000E+11            1 N N         20
   95000420


SQL> select TELUS_IPID_SEQ.nextval from dual;

   NEXTVAL
----------
  95000402

SQL> alter sequence TELUS_IPID_SEQ increment by 200000;

Sequence altered.

SQL> select TELUS_IPID_SEQ.nextval from dual;

   NEXTVAL
----------
  95200402

SQL> alter sequence TELUS_IPID_SEQ increment by 1;

Sequence altered.

SQL> select TELUS_IPID_SEQ.nextval from dual;

   NEXTVAL
----------
  95200403

SQL> select TELUS_IPID_SEQ.nextval from dual;

   NEXTVAL
----------
  95200404

SQL> select TELUS_IPID_SEQ.nextval from dual;

   NEXTVAL
----------
  95200405

SQL> select TELUS_IPID_SEQ.nextval from dual;

   NEXTVAL
----------
  95200406

RMAN STEP BY STEP BACKUP :

RMAN :

SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

sqlplus / as sysdba

SQL> startup mount
ORACLE instance started.

Total System Global Area  778387456 bytes
Fixed Size                  1374808 bytes
Variable Size             260048296 bytes
Database Buffers          511705088 bytes
Redo Buffers                5259264 bytes
Database mounted.

SQL> alter database archivelog;

Database altered.

SQL> alter database open;

Database altered.

C:\Documents and Settings\USERPROFILE>rman target 'sys/sys@orcl_new as sysdba'

Recovery Manager: Release 11.2.0.1.0 - Production on Fri Jan 30 17:52:07 2015

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORCL_NEW (DBID=2985633894)

RMAN> backup database plus archivelog;

Starting backup at 30-JAN-15
current log archived
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=16 device type=DISK
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=3 RECID=1 STAMP=870371596
channel ORA_DISK_1: starting piece 1 at 30-JAN-15
channel ORA_DISK_1: finished piece 1 at 30-JAN-15
piece handle=D:\APP\USERPROFILE\FLASH_RECOVERY_AREA\ORCL_NEW\BACKUPSET\2015_01_30\O1_MF_ANNNN_TAG20150130T175339_BDPXYN9R_.BKP tag=TAG20150130T175339 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 30-JAN-15

Starting backup at 30-JAN-15
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=D:\APP\USERPROFILE\ORADATA\ORCL_NEW\SYSTEM01.DBF
input datafile file number=00002 name=D:\APP\USERPROFILE\ORADATA\ORCL_NEW\SYSAUX01.DBF
input datafile file number=00003 name=D:\APP\USERPROFILE\ORADATA\ORCL_NEW\UNDOTBS01.DBF
input datafile file number=00004 name=D:\APP\USERPROFILE\ORADATA\ORCL_NEW\USERS01.DBF
channel ORA_DISK_1: starting piece 1 at 30-JAN-15
channel ORA_DISK_1: finished piece 1 at 30-JAN-15
piece handle=D:\APP\USERPROFILE\FLASH_RECOVERY_AREA\ORCL_NEW\BACKUPSET\2015_01_30\O1_MF_NNNDF_TAG20150130T175351_BDPXYRG5_.BKP tag=TAG20150130T175351 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:05
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 30-JAN-15
channel ORA_DISK_1: finished piece 1 at 30-JAN-15
piece handle=D:\APP\USERPROFILE\FLASH_RECOVERY_AREA\ORCL_NEW\BACKUPSET\2015_01_30\O1_MF_NCSNF_TAG20150130T175351_BDPY0TQX_.BKP tag=TAG20150130T175351 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 30-JAN-15

Starting backup at 30-JAN-15
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=4 RECID=2 STAMP=870371700
channel ORA_DISK_1: starting piece 1 at 30-JAN-15
channel ORA_DISK_1: finished piece 1 at 30-JAN-15
piece handle=D:\APP\USERPROFILE\FLASH_RECOVERY_AREA\ORCL_NEW\BACKUPSET\2015_01_30\O1_MF_ANNNN_TAG20150130T175500_BDPY0WXS_.BKP tag=TAG20150130T175500 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 30-JAN-15

KILL Specific Sessions in Oracle :

column machine format a21
column username format a8
select user#,machine,username,SID,serial#,status from v$session;
ALTER SYSTEM KILL SESSION '9,141';

where '9' is the SID and '141' is the serial#...

Script to find the logons on to the db:


rem session.sql
set echo off;
set termout on;
set linesize 80;
set pagesize 60;
set newpage 0;
select
rpad(c.name||':',11)||rpad(' current logons='||
(to_number(b.sessions_current)),20)||'cumulative logons='||
rpad(substr(a.value,1,10),10)||'highwater mark='||
b.sessions_highwater Information
from
v$sysstat a,
v$license b,
v$database c
where
a.name = 'logons cumulative'
;
ttitle "dbname Database|UNIX/Oracle Sessions";
set heading off;
select 'Sessions on database '||substr(name,1,8) from v$database;
set heading on;
select
substr(a.spid,1,9) pid,
substr(b.sid,1,5) sid,
substr(b.serial#,1,5) ser#,
substr(b.machine,1,6) box,
substr(b.username,1,10) username,
-- b.server,
substr(b.osuser,1,8) os_user,
substr(b.program,1,30) program
from v$session b, v$process a
where
b.paddr = a.addr
and type='USER'
order by spid;
ttitle off;
set heading off;

Collecting Statistics for System Component Schemas :

The best way to enhance the performance of

running processes is to gather statistics on a schema. The following command is best collectively gather statistics for a schema.

EXECUTE DBMS_STATS.GATHER_SCHEMA_STATS('SCHEMA_NAME',DBMS_STATS.AUTO_SAMPLE_SIZE);