How To Re-create Oracle Database 11g Enterprise Control Manager

HOW TO RE-CREATE ORACLE DATABASE 11G ENTERPRISE CONTROL MANAGER

Steps are:

Drop the user and dependent on version a few other objects, like:

SQL>DROP USER sysman CASCADE; SQL>DROP PUBLIC SYNONYM setemviewusercontext; SQL>DROP ROLE mgmt_user; SQL>DROP PUBLIC SYNONYM mgmt_target_blackouts; SQL>DROP USER mgmt_view;

It will take some time for droping the user

and after dropping user and their role drop all the public synonym which are invalid after dropping the above users.

Save the below query result as a text file invalid_synonym.txt

SQL>SELECT 'drop public synonym ' || a.object_name || ';' FROM all_objects a WHERE a.object_type='SYNONYM' AND a.owner = 'PUBLIC' AND a.STATUS = 'INVALID'

And run it in sqlplus command prompt as

SQL> @ADDRESS_OF_FILE/invalid_synonym.txt;

and then issue the below command on linux prompt and follow the instructions

STARTED EMCA at Jul 13, 2008 8:28:48 AM
EM Configuration Assistant, Version 11.1.0.5.0 Production
Copyright (c) 2003, 2005, Oracle.  ALL rights reserved.
 
Enter the following information:
DATABASE SID: orcl
Listener port NUMBER: 1521
Password FOR SYS USER:
Password FOR DBSNMP USER:
Password FOR SYSMAN USER:
Password FOR SYSMAN USER: Email address FOR notifications (optional):
Outgoing Mail (SMTP) server FOR notifications (optional):
You have specified the following settings

DATABASE ORACLE_HOME ................ /u01/app/oracle/product/11.2.0/db_1

LOCAL hostname ................ ora
Listener port NUMBER ................ 1521
DATABASE SID ................ orcl
Email address FOR notifications ...............


Outgoing Mail (SMTP) server FOR notifications ...............

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

Do you wish TO continue? [yes(Y)/no(N)]: y

and it will take some time to create enterprise manager

important commands

for creation of enterprise manager in linux on oracle 11g

emca -config dbcontrol db -repos create

for Dropping of enterprise manager in linux on oracle 11g

emca -config dbcontrol db -repos DROP

for status of enterprise manager in linux on oracle 11g

emca -config dbcontrol db -repos STATUS

Feature of DDL With the WAIT Option OR Ora-00054 Resource busy error solution in Oracle Database 11g

This is usually common that some time when we try to modify some table and 

we got error of "ORA-00054: resource busy" because when we try to modify table 

that table is in use.
For overcoming this error in oracle 11g database introduce a parameter 

DDL_LOCK_TIMEOUT in which you set the time for which your modify command wait 

for showing the error of Ora-00054 . And you can set DDL_LOCK_TIMEOUT parameter 

at session as well as on instance level what ever you want.
Example:-
1. In your First session
CREATE TABLE new (
  no  NUMBER
);

INSERT INTO new VALUES (10);
2. In your second session
open another session and set  DDL_LOCK_TIMEOUT to value say 60 sec
ALTER SESSION SET ddl_lock_timeout=60;
and then issue alter command on created table


ALTER TABLE new ADD (
  name  VARCHAR2(50)
);
Note: Here  DDL_LOCK_TIMEOUT value is 60 if in between these 60 seconds you 

issue commit command in your first session then your alter command run 

successfully in second session and if you didn't issue commit command in 

first session then your second session wait for 60 seconds and on 61st seconds 

it will give the error of  

ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired.
 

First Case: if issue commit in first session 

 1. In your First session 

 CREATE TABLE new (
  no  NUMBER
);

INSERT INTO new VALUES (10);
commit;
2. In your second session 

 ALTER TABLE new ADD (
  name  VARCHAR2(50)
);

Table altered.

SQL>

 Second Case: if commit is not issue in first session 

 1. In your First session
CREATE TABLE new (
  no  NUMBER
);

INSERT INTO new VALUES (10);
2. In your second session
ALTER TABLE new ADD (
            *
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

How To Create Restore Point In Oracle 11g With Illustration

HOW TO CREATE RESTORE POINT IN ORACLE 11G WITH EXAMPLE

SQL> CREATE RESTORE POINT before_update GUARANTEE FLASHBACK DATABASE;

Restore point created.

SQL> !rman

Recovery Manager: Release 11.2.0.1.0 - Production on Sat Oct 20 16:20:27 2012

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

RMAN> connect target

connected to target database: DB (DBID=2351874955)

RMAN> list restore point all

2> ;

using target database control file instead of recovery catalog

SCN RSP Time Type Time Name

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

3280671 GUARANTEED 12-OCT-12 BEFORE_UPDATE

RMAN> exit

Recovery Manager complete.

SQL> create table before_restore_point(ename varchar2(40));

Table created.

SQL> !rman

Recovery Manager: Release 11.2.0.1.0 - Production on Sat Oct 20 16:21:35 2012

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

RMAN> shutdown immediate

using target database control file instead of recovery catalog

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of shutdown command at 19/20/2012 16:21:40

RMAN-06171: not connected to target database

RMAN> connect target

connected to target database: DB (DBID=2351874955)

using target database control file instead of recovery catalog

RMAN> FLASHBACK DATABASE TO RESTORE POINT 'BEFORE_UPDATE';

Starting flashback at 20-OCT-12

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=241 device type=DISK

starting media recovery

media recovery failed

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of flashback command at 10/20/2012 16:22:21

ORA-38757: Database must be mounted and not open to FLASHBACK.

RMAN> exit

Recovery Manager complete.

SQL> !rman

Recovery Manager: Release 11.2.0.1.0 - Production on Sat Oct 20 16:22:35 2012

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

RMAN> connect target

connected to target database: DB (DBID=2351874955)

RMAN> shutdown immediate

using target database control file instead of recovery catalog

RMAN> exit

Recovery Manager complete.

SQL> shutdown abort

ORA-03135: connection lost contact

SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 32bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing

[oracle@oracleoel test]$ sqlplus "/ as sysdba"

SQL*Plus: Release 11.2.0.1.0 Production on Sat Oct 20 16:25:21 2012

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

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 32bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select status from v$instance;

STATUS

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

OPEN

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> !rman

Recovery Manager: Release 11.2.0.1.0 - Production on Sat Oct 20 16:25:46 2012

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

RMAN> connect target

connected to target database (not started)

RMAN> startup mount

Oracle instance started

database mounted

Total System Global Area 1837244416 bytes

Fixed Size 2214336 bytes

Variable Size 1207961152 bytes

Database Buffers 603979776 bytes

Redo Buffers 23089152 bytes

RMAN> FLASHBACK DATABASE TO RESTORE POINT 'BEFORE_UPDATE';

Starting flashback at 20-OCT-12

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=201 device type=DISK

starting media recovery

media recovery complete, elapsed time: 00:00:03

Finished flashback at 20-OCT-12

RMAN> alter database open resetlogs;

database opened

RMAN> exit

Recovery Manager complete.

SQL> select instance_name,status from v$instance;

select instance_name,status from v$instance

*

ERROR at line 1:

ORA-01034: ORACLE not available

Process ID: 22495

Session ID: 153 Serial number: 3

SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 32bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

[oracle@oracleoel test]$ sqlplus "/ as sysdba"

SQL*Plus: Release 11.2.0.1.0 Production on Sat Oct 20 16:27:10 2012

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

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 32bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select instance_name,status from v$instance;

INSTANCE_NAME STATUS

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

DB OPEN

SQL> select * from before_restore;

select * from before_restore

*

ERROR at line 1:

ORA-00942: table or view does not exist

SQL> select * from before_restore_point;

select * from before_restore_point

*

ERROR at line 1:

ORA-00942: table or view does not exist

How To Make Tables Read Only in oracle 11g Database with illustration

How To Make Tables Read Only in oracle 11g Database

there is new feature of making tables read only in oracle 11g database .prior to oracle 11g there ,you can make tables read only to other users by only give SELECT grant to the user.

For making tables read only in oracle 11g

alter table table_name read only;

example:-

let us we have to make a table named TEST (no number(10),name varchar2(30)) as read only

1. SQL> select * from test;
   SQL> insert into test values (2,'kumar');
   SQL> commit;
2. SQL> alter table test read only;
   SQL>insert into test values (3,'john');
   ORA-12081: update operation not allowed on table "TEST"
   SQL> update test set no=1 where no=2
   ORA-12081: update operation not allowed on table "TEST"

Now TEST table is read only no one can alter ,update or delete operation on TEST.

For making tables read only to read write oracle 11g

3. SQL> alter table test read write;

SQL> insert into test values (3,'john');
SQL> commit;

How To Enable Archivlelog Mode In Oracle Datbase 11g

How To Enable Archivlelog Mode In Oracle Datbase 11g

for enabling archive log mode in oracle 11g

steps are:-

1. SQL> ARCHIVE LOG LIST;
   Database log mode NoArchive Mode
   Automatic archival Disabled
   Archive destination /Database/archivelog/
   Oldest online log sequence 1129
   Next log sequence to archive 1131
   Current log sequence 1131
2. Database is in noarchive log mode to change it to archivelog mode the first requirement is that database must be mounted not opened for mounting the database shutdown the database and then mount it for this issue the following command
   SQL> SHUTDOWN IMMEDIATE
   SQL> STARTUP MOUNT

after putting the database to mount stage issue the following command

ALTER DATABASE ARCHIVELOG;
ALTER DATABASE OPEN;
now issue the command to check whether database is in archivelog mode or not
2. SQL> ARCHIVE LOG LIST;
   Database log mode NoArchive Mode
   Automatic archival Disabled
   Archive destination /Database/archivelog/
   Oldest online log sequence 1132
   Next log sequence to archive 1133
   Current log sequence 1134

Feature of Case sensitive passwords in 11g with illustration

Feature of Case sensitive passwords in 11g with illustration

In Oracle 11g database you can use case sensitive security feature with parameter

SEC_CASE_SENSITIVE_LOGON

How to enable case sensitive password feature

SQL> SHOW PARAMETER SEC_CASE_SENSITIVE_LOGON

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sec_case_sensitive_logon             boolean     FALSE
SQL> 


SQL> ALTER SYSTEM SET SEC_CASE_SENSITIVE_LOGON = TRUE;

System altered.


CONN / AS SYSDBA
CREATE USER test IDENTIFIED BY Test;
GRANT CONNECT TO test;
SQL> CONN test/Test
Connected.
SQL> CONN test2/test2
ERROR:
ORA-01017: invalid username/password; logon denied


Warning: You are no longer connected to ORACLE.

Here you can easily see that if SEC_CASE_SENSITIVE_LOGON to TRUE then you have to enter password in exact case in which you assign the password to user and in case when you change the case of password it is not connected .

How to Disable case sensitive password feature

SQL> SHOW PARAMETER SEC_CASE_SENSITIVE_LOGON

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sec_case_sensitive_logon             boolean     TRUE
SQL> 


SQL> ALTER SYSTEM SET SEC_CASE_SENSITIVE_LOGON = FALSE;



System altered.

CONN / AS SYSDBA
CREATE USER test_2 IDENTIFIED BY Test_2;
GRANT CONNECT TO test;
SQL> CONN test_2/Test_2
Connected.
SQL> CONN test_2/test_2

Connected.


Here you can easily see that if SEC_CASE_SENSITIVE_LOGON to FALSE then you have to enter password in any case you connected .

Temporary Tablespace enhancements in oracle 11g with example

Temporary Tablespace enhancements in oracle 11g with example

oracle 11g introduce new view DBA_TEMP_FREE_SPACE for information about temprary tablespace

SQL> SELECT * FROM dba_temp_free_space;

TABLESPACE_NAME                TABLESPACE_SIZE ALLOCATED_SPACE FREE_SPACE
------------------------------ --------------- --------------- ----------
TEMP                                1965031424      916455424  1048576000

1 row selected.


For shrinking temporary tablespace

SQL> ALTER TABLESPACE temp SHRINK SPACE;
Tablespace altered.

SQL> SELECT * FROM dba_temp_free_space;

TABLESPACE_NAME                TABLESPACE_SIZE ALLOCATED_SPACE FREE_SPACE
------------------------------ --------------- --------------- ----------
TEMP                                916455424        916455424   0

1 row selected.

SQL> ALTER TABLESPACE temp SHRINK TEMPFILE '/u01/app/oracle/oradata/DB11G/temp01.dbf' KEEP 30M;

Tablespace altered.

If you omit the KEEP clause database will shrink the tablespace or tempfile to the smallest possible size.

How To Enable Automatic Memory Management in Oracle 11G Database

How To Enable Automatic Memory Management in Oracle 11G Database

This is the new feature introduce in oracle 11g database for automatically managing the SGA and PGA

which is known as automatic memory management(AMM) .prior to oracle 11g database there is Automatich shared memory management (ASMM) in oracle 10g database which only manage SGA automatically .

For enabling and using there are two parameters in oracle 11g database which are:-

MEMORY_MAX_SIZE

MEMORY_TARGET

in which you can set MEMORY_TARGET upto the value of MEMORY_MAX_SIZE.

For enabling AMM

1. set memory_max_size to some value say 2gb

SQL>alter system set memory_max_size=2g scope=spfile;

2. set memory_target upto the value of memory_max_size say 1600m

SQL> alter system set memory_target=1600m scope=spfile;

3. set sga and pga equals to 0

SQL> alter system set pga_aggregate_target=0 scope=spfile;

SQL> alter system sga_max_size=0 scope=spfile;

4. now shutdown database

shutdown immediate

startup

now you don't need to set sga or pga manually now its oracle responsibility to set it according to the requirement

For Disabling AMM

1. set memory_target=0

SQL> alter system set memory_target=0 scope=spfile;

2. set sga and pga to some value say sga_aggregate_target to 1700m and pga_target =300m SQL> alter system sga_max_size=1700m scope=spfile;

SQL> alter system set pga_aggregate_target=300m scope=spfile;

3. now restart the database

shutdown immediate;

startup

now you have to manage and set PGA manually SGA is automatically set and all the caches are automatically set by oracle

How to solve ORA-04044: procedure, function, package, or type is not allowed here

How to solve ORA-04044: procedure, function, package, or type is not allowed here

While creating shell script some time we face error

ORA-04044: procedure, function, package, or type is not allowed here

NOTE:

this is because we are using tables containing '$' SYMBOL and when ever we use tables like v$session,v$log,v$logfile etc shell script take '$' SYMBOL as shell variables and then while reading script it gives error

ORA-04044: procedure, function, package, or type is not allowed here

SOLUTION:-

for overcome this error always use escape '\' SYMBOL before '$' SYMBOL in table name

like we are use

select * from v$session;

select * from v$log;

in shell script we have to use

select * from v\$session;

select * from v\$log;

and this will solve our error and sucessfully run our shell script.

Shell Script For Changing RedoLog File Size

Shell Script For Changing RedoLog File Size 

here we are changing redolog file size from 100m to 150m
database name =test
present redologs are 2
always remember

1.always use escape '\' before table table including '$' symbol
2.you can not drop one redo log if there are two because minimum redologs required for a instance are 2. 
3.thats why when in this script when we try to drop second group it gives error thats why there is need of third group
 4.if you want to drop a group it should be inactive , in active mode you can not drop it because active mode redo log required for recovery if database crash.
5.In this script after dropping a redolog it is also required that it is deleted from physical system also.

export ORACLE_SID=test
sqlplus "/ as sysdba" << EOI
spool /oracle/s.txt;
select * from v\$logfile;
select * from v\$log;
alter database drop logfile group 2;
!rm /oracle/test/redo03.log;
alter database add logfile group 3 '/oracle/test/redo03.log' size 150m;
select * from v\$logfile;
select * from v\$log;
alter database drop logfile group 2;
!rm /oracle/test/redo2.log;
alter database add logfile group 2 '/oracle/test/redo02.log' size 150m;
select * from v\$logfile;
select * from v\$log;
alter system switch logfile;
select * from v\$logfile;
select * from v\$log;
alter system checkpoint;
select * from v\$logfile;
select * from v\$log;
alter database drop logfile group 1;
!rm /oracle/test/redo1.log;
select * from v\$logfile;
select * from v\$log;
alter database add logfile group 1 '/oracle/test/redo01.log' size 150m;
select * from v\$logfile;
select * from v\$log;
alter database drop logfile group 3;
! rm /oracle/test/redo3.log;
spool off;
EOI

How to open standby database for read only

Steps for opening a Standby Database for Read Only access when it is shutdown

Standby Database sid is sid1 here

1. Start the Oracle instance for the standby database without mounting it:

SQL> startup nomount pfile='/u01/app/oracle/product/9.2.0/dbs/

initsid1.ora';

2. Now Mount the standby database:

SQL> ALTER DATABASE MOUNT STANDBY DATABASE;

3. Now Open  database for read-only access:

SQL> ALTER DATABASE OPEN READ ONLY;

Steps for standby database for read only access when it is mounted and performing

managed recovery(MRP):

1. Firstly Cancel log apply services by:

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

2. Now Open the database for read-only access:

SQL> ALTER DATABASE OPEN READ ONLY;

Steps for converting Standby database from Read only to start performing managed

recovery(MRP):

1. Check all the connected sessions if there is any connected session then Terminate all

active user sessions on the standby database.

2. Now Restart log apply services:

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE

 2> DISCONNECT FROM SESSION;

example of converting standby database for Read Only access

Steps for opening a Standby Database for Read Only access when it is shutdown

Standby Database sid is sid1 here

SQL> shutdown immediate;

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

SQL> startup nomount pfile='/u01/app/oracle/product/9.2.0/dbs/initpri16.ora';

ORACLE instance started.

Total System Global Area  236000356 bytes

Fixed Size                    451684 bytes

Variable Size                201326592 bytes

Database Buffers               33554432 bytes

Redo Buffers                     667648 bytes

SQL> alter database mount standby database;

Database altered.

SQL> alter database open read only;

Database altered.

SQL> select * from emp;

select * from emp

              *

ERROR at line 1:

ORA-00942: table or view does not exist

SQL> conn

Enter user-name: schema/pwd@sid1

Connected.

SQL> select * from emp;

NAME                                                                           

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

john                                                                         

SQL> create table destination(location varchar2(100));

create table destination(location varchar2(100))

*

ERROR at line 1:

ORA-00604: error occurred at recursive SQL level 1

ORA-16000: database open for read-only access

SQL> alter database recover managed standby database disconnect from session;

alter database recover managed standby database disconnect from session

*

ERROR at line 1:

ORA-01031: insufficient privileges

SQL> conn

Enter user-name: sys/pwd@sid1

ERROR:

ORA-28009: connection to sys should be as sysdba or sysoper

Warning: You are no longer connected to ORACLE.

SQL> conn

Enter user-name: sys/pwd@sid1

Connected.

SQL> alter database recover managed standby database disconnect from session;

Database altered.

SQL> conn

Enter user-name: schema/pwd@sid1

ERROR:

ORA-00604: error occurred at recursive SQL level 1

ORA-01219: database not open: queries allowed on fixed tables/views only

Warning: You are no longer connected to ORACLE.

SQL> show user

USER is ""

SQL> conn

Enter user-name: sys/pwd@sid1 as sysdba

Connected.

SQL> show user

USER is "SYS"

Steps for standby database for read only access when it is mounted and

performing managed recovery(MRP):

SQL> alter database recover managed standby database cancel;

Database altered.

SQL> alter database open read only;

Database altered.

SQL> conn

Enter user-name: schema/pwd@sid1

Connected.

SQL> select * from emp;

NAME                                                                           

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

john                                                                         

Steps for converting Standby database from Read only to start performing

managed recovery(MRP):

SQL> alter database recover managed standby database disconnect from session;

alter database recover managed standby database disconnect from session

*

ERROR at line 1:

ORA-01031: insufficient privileges

SQL> conn

Enter user-name: sys/pwd@sid1 as sysdba

Connected.

SQL> alter database recover managed standby database disconnect from session;

Database altered.

SQL> conn

Enter user-name: schema/pwd@sid

ERROR:

ORA-00604: error occurred at recursive SQL level 1

ORA-01219: database not open: queries allowed on fixed tables/views only

SQL> conn

Enter user-name: sys/pwd@sid1 as sysdba

Connected.

Primary Database to Standby Database switchover

Database Switchover Steps Are:

1. Firstly Convert primary database to standby

CONNECT sys/pwd@sidname AS SYSDBA

ALTER DATABASE COMMIT TO SWITCHOVER TO STANDBY;

2. Shutdown primary database

SHUTDOWN IMMEDIATE;

3. Mount old primary database as standby database

STARTUP NOMOUNT  PFILE=/u01/app/oracle/product/9.2.0/dbs/

initsidname.ora

ALTER DATABASE MOUNT STANDBY DATABASE;

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM

SESSION;

4. Convert standby database to primary database

CONNECT sys/pwd@sidname AS SYSDBA

ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;

5. Shutdown standby database with immediate

SHUTDOWN IMMEDIATE;

6. Now Open old standby database as primary database.

STARTUP PFILE=/u01/app/oracle/product/9.2.0/dbs/initsidname.ora

example of primary database to standby database switchover

At Primary Database with sid=sid

SQL> alter database commit to switchover to standby;

Database altered.

SQL> shutdown immediate;

ORA-01507: database not mounted

ORACLE instance shut down.

SQL> startup nomount pfile=/u01/app/oracle/product/9.2.0/dbs/initsid.ora

ORACLE instance started.

Total System Global Area  236000356 bytes

Fixed Size                    451684 bytes

Variable Size                201326592 bytes

Database Buffers               33554432 bytes

Redo Buffers                     667648 bytes

SQL> alter database mount standby database;

Database altered.

SQL> alter database recover managed standby database disconnect from session;

Database altered.

SQL> archive log list;

Database log mode               Archive Mode

Automatic archival             Enabled

Archive destination             /archive/sid1

Oldest online log sequence        17

Next log sequence to archive    0

Current log sequence                20

At Standby Database with sid=sid1

SQL> alter database commit to switchover primary;

alter database commit to switchover primary

ERROR at line 1:

ORA-00946: missing TO keyword

SQL> alter database commit to switchover to primary;

Database altered.

                                    *

SQL> shutdown immediate;

ORA-01507: database not mounted

ORACLE instance shut down.

SQL> startup pfile=/u01/app/oracle/product/9.2.0/dbs/initsid1.ora ;

ORACLE instance started.

Total System Global Area  236000356 bytes

Fixed Size                    451684 bytes

Variable Size                201326592 bytes

Database Buffers               33554432 bytes

Redo Buffers                     667648 bytes

Database mounted.

Database opened.

SQL> conn

Enter user-name: schema/pwd@sid1

Connected.

SQL> select * from emp;

NAME

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

john

Linux Shell Scripting For Automatic Oracle Statspack report generation

 

This is the Linux Shell Scripting For Oracle Staspack report generation

statspack report  is  the picture of database between two points of time.thats why this is divided into two steps

 

These Steps Are:

 

1.            Create a shell script named

      first_step.sh at location from whereever you want it to run

 

      conn

sqlplus Perfstat/perfstat@db_name

 

                   exec statspack.snap;

           

                   and assign it in cronjob at time when you want to start taking the picture of of your database

 

2.      Create another shell script named

second_step.sh  at location from whereever you want it to run

                       

                        set head off

                        set timing off

                        sqlplus Perfstat/perfstat@db_name  <<EOF

                        exec statspack.snap;

                        spool /address/first_snap.lst;

                        select max(snap_id) from stats\$snapshot where snap_id not in (select max(snap_id) from        stats\$snapshot);

                        spool off;

                        spool /address/Second_snap.lst;

                         select max(snap_id) from stats\$snapshot;

                        spool off;

                        exit;

                        !

                        EOF

                        START_SNAP=$(cat /address/first_snap.lst  | tail +5 | head -1)

                        END_SNAP=$(cat /address/Second_snap.lst  | tail +5 | head -1)

                        REP_NAME=/address/sp'date'_ac

                        sqlplus Perfstat/perfstat@db_name  <<EOF

                        define start_snap=$ START_SNAP

                        define end_snap=$ END_SNAP

                        define rep_name=$ REP_NAME

                        @?/rdbms/admin/spreport.sql;

                        EOF

                        and also assign it in cronjob at time when you want to end the picture of of your database.

           

                        This two shell scripts assign in cronjob at two point of time whenever you want to view the                         picture of your DB .

Oracle Optimal Passes (i.e. memory gain in PGA auto Target)

 

Oracle Optimal Passes (i.e. memory gain in PGA auto Target)

 

OPTIMAL PASS :-

In Oracle if sort,hash join or group by suddenly gain memory in pga auto target it is said optimal pass.

 

ONE PASS:-

If the memory acquisition requires a single pass through pga_aggregate_target, then memory allocation is marked

as one pass.

 

MULTI PASS:-

 If all memory is in use, Oracle may have to make multiple passes through pga_aggregate_target

to acquire the memory. Multipass executions indicate a memory

shortage, and you should always allocate enough PGA memory to ensure that at least 90-95 percent of

connected tasks can acquire their memory optimally.

 

WHEN TO INCREASE  PGA_AGGREGATE_TARGET:-

Whenever the value of the v$sysstat statistic estimated PGA memory for one-pass

exceeds pga_aggregate_target, then you'll want to increase pga_aggregate_target.

Whenever the value of the v$sysstat statistic workarea executions-multipass is

 greater than 1 percent, the database may benefit from additional PGA memory.

 

To evaluate  this:-

 consider the following query:

 

select name c1,count c2,decode(total, 0, 0, round(count*100/total)) c3

from

(

select name,value count,(sum(value) over ()) total

from

v$sysstat

where

name like 'workarea ex%'

);

 

 

Oracle Server Tuning

 

ORACLE SERVER TUNING

 

Server Tuning:- server tuning refers that the server on which database resides is proper tuned i.e.

 

Here I am using a example of how a server is tuned properly and how input/output response from server should be at best performance rate.

 

Example:- let us suppose a database named TESTDB and there are 7 tablespaces are associated with that database.

 

These 7 Tablespaces has 7 data files there  roles in the database are:-

 

1.      testtbl_1.dbf  for previous year data( for read )

2.      testtbl_2.dbf  for current year data (read and write both)

3.      testtbl_3.dbf for index data(for read)

4.      system.dbf for database information.

5.      Testtemp_1.dbf for temporary data

6.      testundo_1.dbf for undo tablespace

7.      testtbl_7.dbf for history data more then one year

 

here some tablespace are for previous year data which is now using as history tablespaces and they are only for read purpose some tablespaces are for indexes and some for writable purpose and some for read and write both.

 

 

Just remember these main points while creating tablespaces

 

1.      tablespaces for read purpose are always on different disk

 

2.      tablespace for write purpose are on different disk

 

3.      indexes tablespaces are on different disk you can also create them with read only tablespace disk.

 

4.      Always trying to maintain the input/output management

because if read and write tablespace are on same disk I.e if input and output are performing on same disk in a database then it will degrade the system performance because input output are running on same disk if they are on different disks then you will notice the big improvement in speed.

 

5.      Always create objects on their respectively tablespace not on SYSTEM tablespace if system tablespace contains users objects like tables,triggers etc. then it create lack of performance.

Oracle Database Paramater file

 

Oracle Database Paramater file

 

 

Paramter file :- Parameter file is the file which contains the intialiazation parameters and value associated with each parameter

 

Oracle has  two types of parameter files:

 

Initialization Parameter Files:-this parameter file is in text format which contains the initialization parameters of database.

 

Which contains entries like

                        sessions

                        processes

                        user_dump_dest location

background_dump_dest location

core_dump_dest location

db_cache_size

java_pool_size

control_files  (which contains the control file location)

etc.

 

Server Parameter File :- this is also a parametr file but this is in binary format which is unreadable .the main advantage of using this file for database startup is

                                    when you change any parameter of database then that paramter new value directly applicable to server parameter file while database running.

 

Note:

1. if you want to know which type of parameter file is currently in used for startup then you issue

 

SQL> Show  parameter spfile;

 

If value is present under VALUE column that means spfile (server parameter file is used in starting the database.if VALUE cloumn show no value then initalization parameter file is used.

 

2.      if spfile is currently in used and you want to create the initparameter file then issue

 

SQL> Create pfile from spfile;

OR

SQL> Create pfile=ADDRESS_OF_FILE From spfile;