OracleTechnical: September 2011

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.

Saturday, September 10, 2011

Identifying Your Oracle Database Software Release

As many as five numbers may be required to fully identify a release.

Release Number Format

To understand the release nomenclature used by Oracle, examine the following example of an Oracle Database server labeled "Release 10.1.0.1.0".

Major Database Release Number

The first digit is the most general identifier. It represents a major new version of the software that contains significant new functionality.

Database Maintenance Release Number

The second digit represents a maintenance release level. Some new features may also be included.

Application Server Release Number

The third digit reflects the release level of the Oracle Application Server (OracleAS).

Component-Specific Release Number

The fourth digit identifies a release level specific to a component. Different components can have different numbers in this position depending upon, for example, component patch sets or interim releases.

Platform-Specific Release Number

The fifth digit identifies a platform-specific release. Usually this is a patch set. When different platforms require the equivalent patch set, this digit will be the same across the affected platforms.

Checking Your Current Release Number

To identify the release of Oracle Database that is currently installed and to see the release levels of other database components you are using, query the data dictionary view PRODUCT_COMPONENT_VERSION. A sample query follows. (You can also query the V$VERSION view to see component-level information.) Other product release levels may increment independent of the database server.

COL PRODUCT FORMAT A35
COL VERSION FORMAT A15
COL STATUS FORMAT A15 
SELECT * FROM PRODUCT_COMPONENT_VERSION;

PRODUCT                                  VERSION     STATUS
---------------------------------------- ----------- -----------
NLSRTL                                   10.2.0.1.0  Production
Oracle Database 10g Enterprise Edition   10.2.0.1.0  Prod
PL/SQL                                   10.2.0.1.0  Production
...

It is important to convey to Oracle the results of this query when you report problems with the software.

Thursday, September 8, 2011

ASM diskgroup creation on windows with dummy disks

Today I needed to setup a small test Oracle database on Windows with Oracle ASM storage. Because I didn't have unused disk partitions that I could spare for Oracle ASM, I decided to use cooked files instead.

Note: Using cooked files instead of raw disks is unsupported by Oracle and should not be used on development and production systems! I needed a quick (and dirty) way to do some testing with ASM, I would never use cooked files in development and/or production

Step 1: Install oracle 10g Software only

1a Configure Cluster Synchronization Servie

C:\>D:\oracle\product\ 10.2.0\db_ 1\BIN\localconfig add

Step 1: stopping local CSS stack
Step 2: deleting OCR repository
Step 3: creating new OCR repository
Successfully accumulated necessary OCR keys.
Creating OCR keys for user 'ap\arogyaa' , privgrp ''..
Operation successful.
Step 4: creating new CSS service
successfully created local CSS service
successfully reset location of CSS setup

Step 2 Creating a dummy disks

D:\>mkdir asmdisks
D:\>cd asmdisks

Creating disk

D:\asmdisks>asmtool -create D:\asmdisks\disk1 700M

D:\asmdisks>asmtool -create D:\asmdisks\disk2 700M

D:\asmdisks>asmtool -create D:\asmdisks\disk3 700M

Now you have 3 disks(dummy) of 700mb each which can be used to create a ASM disk group.

Step3 Create ASM instance

Option 1 you can run a DBCA and select aconfigure Automatic storage management an enter the password for ASM. NOTE: If you choose option 1 then you don’t need do below manual step 3

OR Option 2 MANUALLY

3a Creating folder

<ORACLE_HOME>\admin\+ASM\bdump
<ORACLE_HOME>\admin\+ASM\cdump
<ORACLE_HOME>\admin\+ASM\udump

3b Creating ASM parameter file

Create file with name of INIT+asm.ora at <ORACLE_HOME>\database\ INIT+asm.ora and paste below parameter.

_asm_allow_only_raw_disks=FALSE

asm_diskstring='D:\asmdisks\*'

background_dump_dest='D:\oracle\product\10.2.0\admin\+ASM\bdump'

core_dump_dest='D:\oracle\product\10.2.0\admin\+ASM\cdump'

instance_type='asm'

large_pool_size=12M

remote_login_passwordfile='SHARED'

user_dump_dest='D:\oracle\product\10.2.0\admin\+ASM\udump'

3c Create service and password file

oradim will create an ASM instance and start it automatically.

c:\> orapwd file=C:\oracle\ product\10. 2.0\db_1\ database\ PWD+ASM.ora password=asm

c:\> oradim -NEW -ASMSID +ASM -STARTMODE auto

3d Change PFILE to SPFILE if you setup manually

On command prompt

C:\> set ORACLE_SID=+ASM

SQL> startup

SQL> create spfile from pfile;
SQL> startup force;

Step 4 Add ASM Diskgroup and other parameter
On command prompt
C:\> set ORACLE_SID=+ASM

SQL> alter system set asm_diskgroups=data scope=spfile;

Note: if you create disk group from DBCA then you dont need to add above parameter.

SQL> alter system set "_asm_allow_only_raw_disks"=FALSE scope=spfile

SQL> alter system set asm_diskstring='D:\asmdisks\*'

SQL> startup force;

Total System Global Area 83886080 bytes

Fixed Size 1247420 bytes

Variable Size 57472836 bytes

ASM Cache 25165824 bytes

ORA-15110: no diskgroups mounted

Step 5 Create ASM disk group

First verify that your disk can be seen. As you see below output, this means your disks are discovered and now you can make the diskgroup.

SQL> select path, mount_status from v$asm_disk;
PATH MOUNT_S
------------ --------- --------- --
D:\ASMDISKS\ DISK1 CLOSED
D:\ASMDISKS\ DISK3 CLOSED
D:\ASMDISKS\ DISK2 CLOSED

SQL> create diskgroup data external redundancy disk
'D:\ASMDISKS\ DISK1', 'D:\ASMDISKS\ DISK2', 'D:\ASMDISKS\ DISK3';

Diskgroup created.

Step 6 Create Database on ASM

Now here you task is completed and now you can run dbca to create database.

screen1

create a database --> click next

screen2

general Purpose --> click next

screen3

global database name = orcl

SID = orcl --> click next

screen4

--> click next

screen5

password = oracle1 --> click next

screen6

Select ASM --> click next

screen7

select diskgroup --> click next

screen8

Select Oracle Managed Files--> click next

screen9

if you need Flash recover area selected --> click next

now

--> click next

Another method is also from oracle metalink

ASM Using OS Files Instead of Real Raw Devices On Windows. [ID 602620.1]

1) If the ASM instance was not created, then create an ASM instance thru the DBCA .

2) Update the initialization parameter file with the next hidden parameter:

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining options

INSTANCE_NAME
----------------
+asm

SQL> alter system set "_asm_allow_only_raw_disks"=false scope=spfile;
System altered.

3) If you have real raw devices you will see them after create the ASM instance:

SQL> select path from v$asm_disk;

PATH
-------------------------------------------------------------------
\\.\ORCLDISKDG0
\\.\ORCLDISKDG1
\\.\ORCLDISKDATA0
\\.\ORCLDISKDATA1
\\.\ORCLDISKDATA2
\\.\ORCLDISKDATA3
\\.\ORCLDISKFRA0
\\.\ORCLDISKFRA1
8 rows selected.

4) Create the new directory to allocate the fake files/disks:

SQL> host mkdir d:\asmfake

5) Update the ASM instance with the new path associated with the fake directory:

SQL> alter system set asm_diskstring='\\.\ORCLDISK*','d:\asmfake\*' scope=both;

System altered.

6) Shutdown and startup the ASM instance to apply the changes:

SQL> shutdown
ASM diskgroups dismounted
ASM instance shutdown

SQL> startup
ASM instance started

Total System Global Area   83886080 bytes
Fixed Size                  1289028 bytes
Variable Size              57431228 bytes
ASM Cache                  25165824 bytes
ASM diskgroups mounted

SQL> show parameter asm

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
_asm_allow_only_raw_disks            boolean     FALSE
asm_diskgroups                       string      DATADG, FRADG
asm_diskstring                       string      \\.\ORCLDISK*, d:\asmfake\*
asm_power_limit                      integer     1

7) In order to create fake files to be used as ASM disks you will need to create them thru the dd command, therefore please install the dd command for windows, you can download it from:

http://www.chrysocome.net/dd

8) Then create the desired fake disks/files on the fake directory:

SQL> host dd if=/dev/zero of=d:\asmfake\fakeasm1 bs=1k count=1000000

1000000+0 records in
1000000+0 records out

SQL> host dd if=/dev/zero of=d:\asmfake\fakeasm2 bs=1k count=1000000

1000000+0 records in
1000000+0 records out

SQL> host dd if=/dev/zero of=d:\asmfake\fakeasm3 bs=1k count=1000000

1000000+0 records in
1000000+0 records out

SQL> host dd if=/dev/zero of=d:\asmfake\fakeasm4 bs=1k count=1000000

1000000+0 records in
1000000+0 records out

SQL> host dir d:\asmfake\*
Volume in drive D is New Volume
Volume Serial Number is 94B6-1FC7

Directory of d:\asmfake

06/11/2008  03:00 PM    <DIR>          .
06/11/2008  03:00 PM    <DIR>          ..
06/11/2008  02:58 PM     1,024,000,000 fakeasm1
06/11/2008  02:59 PM     1,024,000,000 fakeasm2
06/11/2008  03:00 PM     1,024,000,000 fakeasm3
06/11/2008  03:01 PM     1,024,000,000 fakeasm4
               4 File(s)  4,096,000,000 bytes
               2 Dir(s)  19,148,734,464 bytes free

9) Connect to the ASM instance and make sure the fake disks are visible:

SQL> select path from v$asm_disk;

PATH
----------------------------------------------------------
D:\ASMFAKE\FAKEASM1
D:\ASMFAKE\FAKEASM2
D:\ASMFAKE\FAKEASM3
D:\ASMFAKE\FAKEASM4
\\.\ORCLDISKDG0
\\.\ORCLDISKDG1
\\.\ORCLDISKDATA0
\\.\ORCLDISKDATA1
\\.\ORCLDISKDATA2
\\.\ORCLDISKDATA3
\\.\ORCLDISKFRA0
\\.\ORCLDISKFRA1

12 rows selected.

10) Then create diskgroup(s) using the fake disks:

SQL> create diskgroup TEST external redundancy disk 'D:\ASMFAKE\FAKEASM1',
  2  'D:\ASMFAKE\FAKEASM2',
  3  'D:\ASMFAKE\FAKEASM3',
  4  'D:\ASMFAKE\FAKEASM4';

Diskgroup created.

SQL> select name, state from v$asm_diskgroup;
NAME                           STATE
------------------------------ -----------
DATADG                         MOUNTED
FRADG                          MOUNTED
TEST                           MOUNTED

SQL> select name, path from v$asm_disk where group_number =3;
NAME                           PATH
------------------------------ ----------------------------------
TEST_0000                      D:\ASMFAKE\FAKEASM1
TEST_0001                      D:\ASMFAKE\FAKEASM2
TEST_0002                      D:\ASMFAKE\FAKEASM3
TEST_0003                      D:\ASMFAKE\FAKEASM4