OracleTechnical

Thursday, September 22, 2011

Grant specific privileges for a Flashback Query

This is an OCM EXAM part.

GRANT FLASHBACK ON SCOTT.BONUS_DROP TO SCOTT

grant FLASHBACK ANY TABLE to scott

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

http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14251/adfns_flashback.htm

Grant flashback privileges to users, roles, or applications that need to use flashback features as follows:

For the DBMS_FLASHBACK package, grant the EXECUTE privilege on DBMS_FLASHBACK to provide access to the features in this package.

For Flashback Query and Flashback Version Query, grant FLASHBACK and SELECT privileges on specific objects to be accessed during queries or grant the FLASHBACK ANY TABLE privilege to allow queries on all tables.

For Flashback Transaction Query, grant the SELECT ANY TRANSACTION privilege.

For Execution of undo SQL code, grant SELECT, UPDATE, DELETE, and INSERT privileges for specific tables, as appropriate, to permit execution of undo SQL code retrieved by a Flashback Transaction Query.

Wednesday, September 21, 2011

The Hardest Game. For Adults.

I have come to realize that, for adults, Minecraft is among the most difficult of all video games.

Bear with me.

Minecraft is amazing for kids. All of the children in our social circle have been completely absorbed into the Minecraft collective. They make huge towns. They discuss the relative merits of the Adventure Update. They install mods. They set up LANs and explore each others' worlds. Whatever its merits as an educational game, it's certainly made our kids learn what an IP address is.

When we took our nine-year old daughter to PAX, she insisted on waiting in line to have her picture taken with Notch, Minecraft's creator. I spoke with him and mentioned that I run Spiderweb Software. He said that he'd played Avernum. This one fact did more to elevate me in her view than any single event since her creation.

The kids are mad about the game, making spiraling towers, roller-coasters, water slides, and mad dreamscapes.

But what about the adults?

What Adults Frequently Say About Minecraft

Many of the guardians of these children tried Minecraft themselves. Few of them played more than an hour or two. Now note, these are nerd grown-ups, gamers, people who actually finish games. Grown-ups rarely have time to play games. These are people who make time. But I played for more than any of them, logging a mere ten hours.

But that's not what got my attention. What I found so fascinating was that, when I asked my fellow olds what they thought of it, I always got a similar response:

"I didn't know what to do."
"I didn't see the point of it."
"I didn't have a purpose."

And then they looked at me as if all this made perfect, self-evident sense. And the thing is, it did. I always nodded with genuine sympathy.

And then I realized how depressing that is.

A Thought Experiment

Suppose you asked an adult, "What do you think of Legos?" and got the response, "I don't like them. I just look at them, and don't know what to do. What is the purpose? What is their point?"

What would you think of this response? Would you find that person to be Awesome? And not, maybe, I don't know, just a wee bit depressing?

Tell me, what do you think of paper? "It's blank. That stresses me out." What about clay? "I don't know what to make, so it's pointless." Look. You can make things with this. "But creativity makes me tired."

I'm not pretending I'm not the same way. I don't think I'm any better than my peers. I'm just the same.

Look What I Did With It, Because I'm So Great

I had a blast playing Minecraft! I built a little house, so that I could be safe from monsters. I made a mine. And then you know what I created?

I built a two story house for a family of four. Bedroom for the parents. Bedroom for two children. Kitchen. Workshop. Field of crops so we could eat. A big wall to keep us safe. I even baby-safed the damn thing, to keep my non-existant Minecraft four-year old from falling down the stairs and into a lava pool.

And then I was done. I recreated the world I see around me every day, to the maximum fidelity cube-world would allow. And then I stopped playing. Success.

Isn't that extra-depressing? Even in my fantasy world, I had to have a mortgage.

Why Minecraft Is the Hardest Of Games. For Adults.

I have not made any big, unique discovery here. It's been common knowledge for a long time that a kid can have just as much fun with a toy as with the box it came in. We take this loss of creativity as we age for granted. It's only when it happens with a video game, the sort of thing adults can play, with weapons and monsters and gold, that it comes into much sharper relief. Of course adults don't color with crayons. That activity is in a box in our brains labeled "Kid Stuff," and we can ignore it safely.

But Minecraft is new, so we have to evaluate it with fresh eyes.

To play Minecraft (before the release of promised updates with boss fight and goals and other dreadful things), you have to play. Not in the linear way, walking down a hall and shoot guys in the face on the one course the designer created for you.

To play Minecraft, you have to Play. Playground-style, without fear or hesitation or second-guessing. You have to, without self-consciousness, be creative. My old, withered, linear, fight-or-flight, calculate-reward-for-effort brains just can't do that anymore, not without great strain. To play Minecraft for more than a few minutes, you have to act like a kid again, in the good way. And that is HARD.

I don't know if I can do it. One of these days, some rare, free afternoon, I really need to sit down for an hour, just an hour, and try.

Saturday, September 17, 2011

Transportable Tablespace by Datapump from windows to linux 32 bit OS with same enddian format

SOURCE DATABASE ON WINDOWS

STEP 1 check compatible paramter value

SQL> show parameter compat

NAME TYPE VALUE

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

compatible string 10.2.0.1.0

STEP 2 check Database Character Set value (this must same on target DB)

SQL> select * from database_properties where property_name like '%SET%';

PROPERTY_NAME PROPERTY_VALUE

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

NLS_CHARACTERSET US7ASCII

NLS_NCHAR_CHARACTERSET AL16UTF16

STEP 3 check current Database platform value

SQL> select platform_name from v$database;

PLATFORM_NAME

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

Microsoft Windows IA (32-bit)

STEP 4 check platform value and ENDIAN_FORMAT value

SQL> column platform_name format A32

SQL> select * from v$transportable_platform where platform_name='Microsoft Windo

ws IA (32-bit)';

PLATFORM_ID PLATFORM_NAME ENDIAN_FORMAT

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

7 Microsoft Windows IA (32-bit) Little

Step 5 Check referential integrity constraints taken into consideration

The following statement can be used to determine whether tablespaces USERS and EXAMPLE

are self-contained, with referential integrity constraints taken into consideration

(indicated by TRUE).

SQL> exec dbms_tts.TRANSPORT_SET_CHECK('USERS,EXAMPLE',TRUE);

PL/SQL procedure successfully completed.

SQL> select * from transport_set_violations;

no rows selected

After invoking this PL/SQL package, you can see all violations by selecting from the

TRANSPORT_SET_VIOLATIONS view. If the set of tablespaces is self-contained, this

view is empty.

Step 6 Make required tablespaces in read-only.

SQL> ALTER TABLESPACE users READ ONLY;

Tablespace altered.

SQL> ALTER TABLESPACE example READ ONLY;

Tablespace altered.

TEST STEP (I do this only for understanding, if you dont put tablespace in readonly you will get the error)

C:\>expdp system/oracle TRANSPORT_TABLESPACES=users,example DUMPFILE=DATA_PUMP_DIR:user_example.DMP LOGFILE=DATA_PUMP_DIR:user_example.LOG

Export: Release 10.2.0.1.0 - Production on Sunday, 18 September, 2011 1:27:15

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Produc

tion

With the Partitioning, OLAP and Data Mining options

Starting "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01": system/******** TRANSPORT_TABL

ESPACES=users,example DUMPFILE=DATA_PUMP_DIR:user_example.DMP LOGFILE=DATA_PUMP_

DIR:user_example.LOG

ORA-39123: Data Pump transportable tablespace job aborted

ORA-29335: tablespace 'EXAMPLE' is not read only

Job "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" stopped due to fatal error at 01:27:2

sqlplus / as sysdba

SQL> alter tablespace example read only;

Step 7 Invoke the Data Pump export utility and specify which tablespaces

are in the transportable set.

Note:

If any of the tablespaces have XMLTypes, you must use EXP instead of Data Pump. Ensure that the CONSTRAINTS and TRIGGERS parameters are set to Y (the default).

C:\>expdp system/oracle TRANSPORT_TABLESPACES=users,example DUMPFILE=DATA_PUMP_DIR:user_example.DMP LOGFILE=DATA_PUMP_DIR:user_example.LOG

Export: Release 10.2.0.1.0 - Production on Sunday, 18 September, 2011 1:27:52

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Produc

tion

With the Partitioning, OLAP and Data Mining options

Starting "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01": system/******** TRANSPORT_TABL

ESPACES=users,example DUMPFILE=DATA_PUMP_DIR:user_example.DMP LOGFILE=DATA_PUMP_

DIR:user_example.LOG

Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK

Processing object type TRANSPORTABLE_EXPORT/TYPE/TYPE_SPEC

Processing object type TRANSPORTABLE_EXPORT/TABLE

Processing object type TRANSPORTABLE_EXPORT/GRANT/OWNER_GRANT/OBJECT_GRANT

Processing object type TRANSPORTABLE_EXPORT/INDEX

Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/CONSTRAINT

Processing object type TRANSPORTABLE_EXPORT/INDEX_STATISTICS

Processing object type TRANSPORTABLE_EXPORT/COMMENT

Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/REF_CONSTRAINT

Processing object type TRANSPORTABLE_EXPORT/TRIGGER

Processing object type TRANSPORTABLE_EXPORT/INDEX/FUNCTIONAL_AND_BITMAP/INDEX

Processing object type TRANSPORTABLE_EXPORT/INDEX/STATISTICS/FUNCTIONAL_AND_BITM

AP/INDEX_STATISTICS

Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS

Processing object type TRANSPORTABLE_EXPORT/DOMAIN_INDEX/INDEX

Processing object type TRANSPORTABLE_EXPORT/MATERIALIZED_VIEW

Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PROCACT_INSTANCE

Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PROCDEPOBJ

Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK

Master table "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully loaded/unloaded

******************************************************************************

Dump file set for SYSTEM.SYS_EXPORT_TRANSPORTABLE_01 is:

E:\ORACLE\PRODUCT\10.2.0\ADMIN\ORCL1\DPDUMP\USER_EXAMPLE.DMP

Job "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at 01:29:17

Step 8 copy the datafile belongs to tablespace to another location;

C:\>e:

E:\oracle\product\10.2.0\oradata\orcl1>copy EXAMPLE01.DBF c:\example01.dbf

1 file(s) copied.

E:\oracle\product\10.2.0\oradata\orcl1>copy users01.DBF c:\users01.dbf

1 file(s) copied.

Step 9 make tablespace back to read write mode

SQL> alter tablespace example read write;

Tablespace altered.

SQL> alter tablespace users read write;

Tablespace altered.

All step done here on source system. Now move datafiles and export dmp to target system

TARGET DATABASE

Fetch datafile and dmp file from source and place it on your desire location.

SQL> show parameter compat

NAME TYPE VALUE

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

compatible string 10.2.0

plsql_v2_compatibility boolean FALSE

SQL> alter system set compatible='10.2.0.1.0' scope=spfile;

System altered.

SQL> startup force;

ORACLE instance started.

Total System Global Area 264241152 bytes

Fixed Size 1218892 bytes

Variable Size 100665012 bytes

Database Buffers 159383552 bytes

Redo Buffers 2973696 bytes

Database mounted.

Database opened.

SQL> select * from database_properties where property_name like '%SET%';

PROPERTY_NAME PROPERTY_VALUE

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

NLS_CHARACTERSET US7ASCII

NLS_NCHAR_CHARACTERSET AL16UTF16

SQL> select platform_name from v$database;

PLATFORM_NAME

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

Linux IA (32-bit)

SQL> column platform_name format A32

Note: IF ENDIAN_FORMAT is not same as source database then you need to convert the datafile by RMAN convert command. Other wise you just copy your files to target database.

SQL> select * from v$transportable_platform where platform_name like '%Linux IA (32-bit)%';

PLATFORM_ID PLATFORM_NAME ENDIAN_FORMAT

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

10 Linux IA (32-bit) Little

SQL> select name from v$tablespace;

NAME

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

SYSTEM

UNDOTBS

SYSAUX

TEMP

ZAMANTS

TEMP2

Create Desire user that have objects in both tablespace

create user scott identified by tiger ;

create user TSMSYS identified by tiger ;

create user BI identified by tiger ;

create user PM identified by tiger ;

create user MDDATA identified by tiger ;

create user IX identified by tiger ;

create user SH identified by tiger ;

create user DIP identified by tiger ;

create user HR identified by tiger ;

SQL> grant connect, resource to scott,tsmsys,bi,pm,mddata,IX,SH,DIP,OE,HR;

Grant succeeded.

Now check the datapump directory

SQL> select * from dba_directories;

OWNER DIRECTORY_NAME

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

DIRECTORY_PATH

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

SYS DATA_PUMP_DIR

/u01/oracle/product/10.2.0/db_1/rdbms/log/

SQL> create or replace directory data_pump_dir as '/u01/oracle/admin/orcl/dpdump';

Directory created.

SQL> select * from dba_directories;

OWNER DIRECTORY_NAME

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

DIRECTORY_PATH

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

SYS DATA_PUMP_DIR

/u01/oracle/admin/orcl/dpdump

SQL> grant read on directory data_pump_dir to system;

Grant succeeded.

IMPORT DP COMAND

[oracle@ocs orcl]$ impdp system/oracle directory=data_pump_dir dumpfile=USER_EXAMPLE.DMP transport_datafiles=/u04/orcl/USERS01.DBF,/u04/orcl/example01.dbf

Import: Release 10.2.0.1.0 - Production on Sunday, 18 September, 2011 2:49:48

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

With the Partitioning, OLAP and Data Mining options

Master table "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded

Starting "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01": system/******** directory=data_pump_dir dumpfile=USER_EXAMPLE.DMP transport_datafiles=/u04/orcl/USERS01.DBF,/u04/orcl/example01.dbf

Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK

Processing object type TRANSPORTABLE_EXPORT/TYPE/TYPE_SPEC

Processing object type TRANSPORTABLE_EXPORT/TABLE

ORA-39083: Object type TABLE:"PM"."ONLINE_MEDIA" failed to create with error:

ORA-39218: type check on object type "ORDSYS"."ORDIMAGE" failed

ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95

ORA-06512: at "SYS.DBMS_METADATA_UTIL", line 1915

ORA-01403: no data found

Failing sql is:

BEGIN SYS.DBMS_METADATA.CHECK_TYPE('ORDSYS','ORDIMAGE','1','21D055AB9B0A9AB4B1B44B1BE854B870BB',''); SYS.DBMS_METADATA.CHECK_TYPE('ORDSYS','ORDIMAGESIGNATURE','1','21434CBB7CFC39C0B4850F15D8A163B529',''); SYS.DBMS_METADATA.CHECK_TYPE

Processing object type TRANSPORTABLE_EXPORT/GRANT/OWNER_GRANT/OBJECT_GRANT

Processing object type TRANSPORTABLE_EXPORT/INDEX

Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/CONSTRAINT

Processing object type TRANSPORTABLE_EXPORT/INDEX_STATISTICS

Processing object type TRANSPORTABLE_EXPORT/COMMENT

Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/REF_CONSTRAINT

Processing object type TRANSPORTABLE_EXPORT/TRIGGER

ORA-39082: Object type TRIGGER:"HR"."SECURE_EMPLOYEES" created with compilation warnings

ORA-39082: Object type TRIGGER:"HR"."UPDATE_JOB_HISTORY" created with compilation warnings

Processing object type TRANSPORTABLE_EXPORT/INDEX/FUNCTIONAL_AND_BITMAP/INDEX

Processing object type TRANSPORTABLE_EXPORT/INDEX/STATISTICS/FUNCTIONAL_AND_BITMAP/INDEX_STATISTICS

Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS

Processing object type TRANSPORTABLE_EXPORT/DOMAIN_INDEX/INDEX

ORA-39083: Object type INDEX failed to create with error:

ORA-06550: line 2, column 1:

PLS-00201: identifier 'CTXSYS.DRIIMP' must be declared

ORA-06550: line 2, column 1:

PL/SQL: Statement ignored

ORA-06550: line 8, column 1:

PLS-00201: identifier 'CTXSYS.DRIIMP' must be declared

ORA-06550: line 8, column 1:

PL/SQL: Statement ignored

ORA-06550: line 10, column 1:

PLS-00201: identifier 'CTXSYS.DRIIMP' must be declared

ORA-06550: line 10, column 1:

PL/SQL: Statement ignored

ORA-06550: line 12, column 1:

PLS

Processing object type TRANSPORTABLE_EXPORT/MATERIALIZED_VIEW

ORA-31685: Object type MATERIALIZED_VIEW:"SH"."CAL_MONTH_SALES_MV" failed due to insufficient privileges. Failing sql is:

CREATE MATERIALIZED VIEW "SH"."CAL_MONTH_SALES_MV" USING ("CAL_MONTH_SALES_MV", (8, 'ORCL1.REGRESS.RDBMS.DEV.US.ORACLE.COM', 2, 0, 0, "SH", "TIMES", '2011-09-17 23:21:50', 8, 52100, '2011-09-17 23:21:51', '', 1, '0208', 561986, 0, NULL, 1, "SH", "SALES", '2011-09-17 23:21:50', 33032, 52042, '2011-09-17 23:21:51', '', 1, '88', 561986, 0, NULL), 1183809, 8, ('1950-01-01 12:00:00', 1, 0,

ORA-31685: Object type MATERIALIZED_VIEW:"SH"."FWEEK_PSCAT_SALES_MV" failed due to insufficient privileges. Failing sql is:

CREATE MATERIALIZED VIEW "SH"."FWEEK_PSCAT_SALES_MV" USING ("FWEEK_PSCAT_SALES_MV", (8, 'ORCL1.REGRESS.RDBMS.DEV.US.ORACLE.COM', 3, 0, 0, "SH", "PRODUCTS", '2011-09-17 23:21:51', 8, 52102, '2011-09-17 23:21:52', '', 1, '12', 562005, 0, NULL, 1, "SH", "TIMES", '2011-09-17 23:21:51', 8, 52100, '2011-09-17 23:21:52', '', 1, '82', 562005, 0, NULL, 2, "SH", "SALES", '2011-09-17 23:21:51',

Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PROCACT_INSTANCE

Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PROCDEPOBJ

Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK

Job "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" completed with 8 error(s) at 02:51:26

DATA VERIFICATION

SQL> select name from v$tablespace;

NAME

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

SYSTEM

UNDOTBS

SYSAUX

TEMP

ZAMANTS

TEMP2

EXAMPLE

USERS

8 rows selected.

SQL> select count(*) from hr.employees;

COUNT(*)

----------

107

Friday, September 16, 2011

Oracle PL/SQL: TABLESPACE: Dropping Tablespaces - Example syntax and procedures for dropping tablespaces, including tablespaces where there are refere

Oracle: TABLESPACE: Dropping Tablespaces - Example syntax and procedures for dropping tablespaces, including tablespaces where there are refere

Tuesday, September 13, 2011

Shutting Down Specific Dispatcher Processes

With the ALTER SYSTEM statement, you leave it up to the database to determine which dispatchers to shut down to reduce the number of dispatchers. Alternatively, it is possible to shut down specific dispatcher processes. To identify the name of the specific dispatcher process to shut down, use the V$DISPATCHER dynamic performance view.

SELECT NAME, NETWORK FROM V$DISPATCHER;

NAME  NETWORK
----- ---------------------------------------------------------------------------
D000  (ADDRESS=(PROTOCOL=tcp)(HOST=ocs.com)(PORT=37348))

D001  (ADDRESS=(PROTOCOL=tcp)(HOST=ocs.com)(PORT=37350))

Each dispatcher is uniquely identified by a name of the form Dnnn.
To shut down dispatcher D002, issue the following statement:

ALTER SYSTEM SHUTDOWN IMMEDIATE 'D001';

The IMMEDIATE keyword stops the dispatcher from accepting new connections and the database immediately terminates all existing connections through that dispatcher. After all sessions are cleaned up, the dispatcher process shuts down. If IMMEDIATE were not specified, the dispatcher would wait until all of its users disconnected and all of its connections terminated before shutting down.

Monday, September 12, 2011

Registering Information with a Nondefault Listener

If you want PMON to register with a local listener that does not use TCP/IP, port 1521, configure the LOCAL_LISTENER parameter in the initialization parameter file to locate the local listener.

For a shared server environment, you can alternatively use the LISTENER attribute of the DISPATCHERS parameter in the initialization parameter file to register the dispatchers with a nondefault local listener. Because both the LOCAL_LISTENER parameter and the LISTENER attribute enable PMON to register dispatcher information with the listener, it is not necessary to specify both the parameter and the attribute if the listener values are the same.

STEPS

For example, if the listener is configured to listen on port 1421 rather than port 1521, you can set the LOCAL_LISTENER parameter in the initialization parameter file as follows:

Step 1 Listener.ora file

LISTENER=
       (Description_list=
       (Description=
        (Address= (protocol=tcp)(host=server)(port=1421) )

        (Address= (protocol=ipc)(Key=extproc) )
       )

       )

SID_LIST_LISTENER =
(SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = D:\oracle\product\10.2.0\db_1)
      (PROGRAM = extproc)
    )
)

Step 2

You can then resolve listener1 in the local tnsnames.ora as follows:
listeneralias is then resolved to the listener protocol addresses through a naming method, such as a tnsnames.ora file on the database server.

listener1=   (DESCRIPTION=    (ADDRESS=(PROTOCOL=tcp)(HOST=servername)(PORT=1421)))

Step 3

Set the LOCAL_LISTENER parameter in parameter file as follows:

alter system set LOCAL_LISTENER='listener1' OR
If you dont use tnsnames.ora entry 
alter system set LOCAL_LISTENER='(ADDRESS=(PROTOCOL=tcp)(HOST=servername)(PORT=1421))'

Step 4 (if Dispatcher configure to non default listener)

To register Dispatcher to non default listener as follows:

alter system set DISPATCHERS="(PROTOCOL=tcp)(DISPATCHERS =2)(LISTENER=listener1)"

Listener Control Utility STATUS Command

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=net)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.0.2
Start Date                15-NOV-2009 20:22:00
Uptime                    0 days 0 hr. 5 min. 22 sec
Trace Level               support
Security                  OFF
SNMP                      OFF
Listener Parameter File   /oracle/admin/listener.ora
Listener Log File         /oracle/network/log/listener.log
Listener Trace File       /oracle/network/trace/listener.trc
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=net)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=sales-server)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=sales-server)(PORT=2484)))
 
Services Summary...
Service "sales.us.example.com" has 1 instance(s).
  Instance "sales", status READY, has 3 handler(s) for this service...
Service "hr.us.example.com" has 1 instance(s).
  Instance "hr", status READY, has 2 handler(s) for this service...
The command completed successfully

The name of the instance associated with the service along with its status and number of service handlers associated with the service
Status can be one of the following:

A READY status means that the instance can accept connections.
A BLOCKED status means that the instance cannot accept connections.
A READY/SECONDARY status means that this is a secondary instance in an Oracle Real Application Clusters primary/secondary configuration and is ready to accept connections.
An UNKNOWN status means that the instance is registered statically in the listener.ora file rather than dynamically with service registration. Therefore, the status is not known.

Sunday, September 11, 2011

How to enabled Shared Server Environment

Initialization Parameters for Shared Server

The following initialization parameters control shared server operation:

SHARED_SERVERS: Specifies the initial number of shared servers to start and the minimum number of shared servers to keep. This is the only required parameter for using shared servers.
MAX_SHARED_SERVERS: Specifies the maximum number of shared servers that can run simultaneously.
SHARED_SERVER_SESSIONS: Specifies the total number of shared server user sessions that can run simultaneously. Setting this parameter enables you to reserve user sessions for dedicated servers.
DISPATCHERS: Configures dispatcher processes in the shared server architecture.
MAX_DISPATCHERS: Specifies the maximum number of dispatcher processes that can run simultaneously. This parameter can be ignored for now. It will only be useful in a future release when the number of dispatchers is auto-tuned according to the number of concurrent connections.
CIRCUITS: Specifies the total number of virtual circuits that are available for inbound and outbound network sessions.

You need to set two parameter

dispatchers
shared_servers

This command will create three dispatchers and each dispatcher can established 10 sessions.
alter system set dispatchers= "(protocol=tcp)(dispatchers=3)(connections=10)"

This command will create three pre initialized server process for shared server connections.
alter system set shared_servers= 3

By this parameter you can established max 20 sessions for shared servers connections.
alter system set shared_server_sessions= 20

other parameters are:
1. max_dispatchers
MAX_DISPATCHERS specifies the maximum number of dispatcher processes allowed to be running simultaneously. It can be overridden by the DISPATCHERS parameter and is maintained for backward compatibility with older releases.

2. max_shared_servers
MAX_SHARED_SERVERS specifies the maximum number of shared server processes allowed to be running simultaneously. Setting this parameter enables you to reserve process slots for other processes, such as dedicated servers.

When you want to reduce the range of shared servers, you can reduce MAX_SHARED_SERVERS before reducing SHARED_SERVERS. If MAX_SHARED_SERVERS is lower than SHARED_SERVERS, then the number of shared servers will not vary but will remain at the constant level specified by SHARED_SERVERS. If MAX_SHARED_SERVERS is not specified, then a shared server process may be spawned as long as the number of free process slots is greater than 1 / 8 the maximum number of processes, or 2 if PROCESSES is less than 24.

How to established client shared server connections to the database

make entry in tnsnames.ora at client side.

test =
(description=
    (address_list=
   (address =(protocol=tcp)(host=urhostname)(port=1521))
    )
    (connect_data=
        (service_name=orcl) (server=shared)
    )
)

Check Listener have dispatcher service register.

lsnrctl services

Example output

Instance "orcl", status READY, has 3 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         LOCAL SERVER
      "D000" established:0 refused:0 current:0 max:10 state:ready
         DISPATCHER <machine: sales-server, pid: 1689>
         (ADDRESS=(PROTOCOL=tcp)(HOST=sales-server)(PORT=52414))
      "D001" established:0 refused:0 current:0 max:10 state:ready
         DISPATCHER <machine: sales-server, pid: 1691>
         (ADDRESS=(PROTOCOL=tcp)(HOST=sales-server)(PORT=52415))

Listener Control Utility SERVICES Command

The name of the service handler. Dispatchers are named D000 through D999. Dedicated servers have the name of DEDICATED.
This section also identifies the following about the service handler:

established: The number of client connections this service handler has established.
refused: The number of client connections it has refused.
current: The number of client connections it is handling, that is, its current load.
max: The maximum number of connections for the service handler, that is, its maximum load.
state: The state of the handler:
- A READY state means that the service handler can accept new connections.
- A BLOCKED state means that the service handler cannot accept new connections.