bash_profile setting for different oracle versions installed in a single server.

vi /home/oracle/.bash_profile

umask 022
export ORACLE_BASE=/u01/app/oracle
export TNS_ADMIN=$ORACLE_BASE/product/11.2.0/db_1/network/admin

10g()
{       export ORACLE_SID=prim10g
        export ORACLE_HOME=$ORACLE_BASE/product/10.1.0/db_1
        export LD_LIBRARY_PATH=$ORACLE_HOME/lib
        export PATH=$ORACLE_HOME/bin:/bin:/usr/bin:/usr/sbin:/usr/local/bin:/usr/X11R6/bin
        export PS1='[\u@\h\W]\$ '
}

11g()
{       export ORACLE_SID=prim11g
        export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1
        export LD_LIBRARY_PATH=$ORACLE_HOME/lib
        export PATH=$ORACLE_HOME/bin:/bin:/usr/bin:/usr/sbin:/usr/local/bin:/usr/X11R6/bin
        export PS1='[\u@\h\W]\$ '
}

12c()
{       export ORACLE_SID=prim12c
        export ORACLE_HOME=$ORACLE_BASE/product/12.1.0.2/db_1
        export LD_LIBRARY_PATH=$ORACLE_HOME/lib
        export PATH=$ORACLE_HOME/bin:/bin:/usr/bin:/usr/sbin:/usr/local/bin:/usr/X11R6/bin
        export PS1='[\u@\h\W]\$ '
}

--save and quit (:wq)

[oracle@server1 ~]$ . .bash_profile

Simply type "10g" to set 10g environment, "11g" for 12c, and so on.

Table replication using Materialized View in oracle 11g

Source Database:prim
Target Database:-stand


Step 1.create the USERS table and add some sample records inside.

At source database:-
[oracle@server1 ~]$ sqlplus sys/system as sysdba@prim
SQL> create user appuser identified by appuser;
SQL> GRANT CONNECT,RESOURCE TO appuser;
SQL> conn appuser/appuser
Connected.

SQL> insert into users values (1 ,'RAM','DAS','REDHAT');
SQL> insert into users values (2, 'SHYAM','SINGH','SUSE');
SQL> insert into users values (3, 'ROHAN','SHARMA','UBUNTU');
SQL> COMMIT;
Commit complete.

At target database:-
Step 2.Now before creating database link we need to add entry inside tnsnames.ora file
so that the target database can connect to the source database.

[oracle@server1 ~]$ vi /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora
STAND =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = server2.soumya.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = stand)
    )
  )

prim =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = server1.soumya.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = PRIM)
    )
  )
 
 :wq
 so we added the tnsentry for source database i.e. prim inside tnsnames.ora file

Step 3.Now connect to the local database i.e. target and create a user appuser2

[oracle@server2 ~]$ sqlplus sys/system as sysdba@stand
SQL> create user appuser2 identified by appuser2;
SQL> grant connect , resource , create database link , create materialized view to appuser2;
SQL> conn appuser2/appuser2
Connected.

At target database:-
Step 3.Now create a database link to connect source database from appuser2
[oracle@server2 ~]$ sqlplus appuser2/appuser2@stand
SQL> CREATE DATABASE LINK stand CONNECT TO appuser IDENTIFIED BY appuser USING 'prim';
CREATE DATABASE LINK stand CONNECT TO appuser IDENTIFIED BY appuser USING 'prim'
                           *
ERROR at line 1:
ORA-02082: a loopback database link must have a connection qualifier

While creating the database link i was facing the above error and i fixed it using the below steps
SQL>select * from global_name;
STAND
-- write down the current name;

SQL> Alter database rename global_name to tttt.xxx;

SQL> CREATE DATABASE LINK mylink CONNECT TO appuser IDENTIFIED BY appuser USING 'prim';

Rename global name back to original from the query above.

SQL> Alter database rename global_name to STAND.soumya.com;

Now You must be able to Select any table from user APP1 On Source Database from target database.
At target database:-
[oracle@server2 ~]$ sqlplus appuser/appuser@prim
SQL> select * from users;

   USER_ID FIRST_NAME LAST_NAME                      PASSWORD
---------- ---------- ------------------------------ --------------------
         1 RAM        DAS                            REDHAT
         2 SHYAM      SINGH                          SUSE
         3 ROHAN      SHARMA                         UBUNTU


Step 4.Now we will create materialized view On target Database:-
SQL> CREATE MATERIALIZED VIEW V_USERS REFRESH NEXT
  SYSDATE+INTERVAL '10' MINUTE WITH ROWID AS SELECT * FROM users@mylink;

Step 5. Now test the scenario:-
[oracle@server2 ~]$ sqlplus appuser2/appuser2@stand
At target database:-
SQL> select count(*) from V_USERS;
It Must Give you same Number Of Row In appuser@prim , If you want to test synchronization
All you have to do is

At source database:-
SQL> conn appuser/appuser
SQL> INSERT INTO USERS VALUES (4, 'ROBERT', 'ASPRIN', 'PASS4')

And then check at target database after 10mins.It would be in sync.

Error in invoking target 'rat_on part_on dm_on olap_on sdo_on' while installing oracle 11g

Today while i was browsing on OTN i found a thread related to oracle 11gR2 Installation , the OP faced an error like the below :-


The above error related to Space On Temp File system all you have to do make sure that there is enough space on temp ( tmp ) or use the below command :-


export TMP=/u01/Foldername
export TMPDIR=/u01/Foldername

Try again now.It should resolve the error

Error in invoking target 'agent nmb nmo nmhs tclexec' in oracle 11g installation

When installing Oracle 11g  you may get the following error :
 Error in invoking target 'agent nmb nmo nmhs tclexec' of makefile '/u01/app/oracle/product/11.1.0/db_1/sysman/lib/ins_emagent.mk

Solution

Within the file detailed in the error edit it and find the line that reads '$(MK_EMAGENT_NMECTL)' change this to '$(MK_EMAGENT_NMECTL) -lnnz11'

Note : edit this file while you still have the installer open, once you have changed it just click retry.

expdp throwing ORA-04031 (stream pool) error

Summary:-
Today while taking backup of a schema i faced a problem in expdp and the job was not done
successfully.Here is the error
UDE-04031: operation generated ORACLE error 4031
ORA-04031: unable to allocate 32 bytes of shared memory ("shared pool","select obj#,type#,ctime,mtim...","SQLA","tmp")
ORA-06512: at "SYS.KUPV$FT_INT", line 2904
ORA-06512: at "SYS.KUPC$QUE_INT", line 572
ORA-25254: time-out in LISTEN while waiting for a message
ORA-06512: at "SYS.DBMS_DATAPUMP", line 3263
ORA-06512: at "SYS.DBMS_DATAPUMP", line 4488
ORA-06512: at line 1

Solution:-
Datapump jobs (either expdp/impdp), when initiated, will use buffer queues in order to
transfer the data directly from the master table to the dump file. Instead of creating new
buffer queue, datapump operations will try to use the existing queues in stream pool memory
area.

So i increased the memory_max_size and resolved the error.
Previously it was having sga_max_target=396M and hence was not under Automatic memory management
feature(11g).
so i created a pfile from the spfile.
And change the value in pfile like this
SQL> create pfile from spfile;
$ vi initprim.ora
#*.sga_max_size=629145600
*.memory_target=629145600


#Here we enabled memory_target parameter by removing sga_max_size from the pfile.This will let
oracle automatically tune the sga and pga size.

SQL> startup mount pfile='/u01/app/oracle/product/11.2.0/db_1/dbs/initprim.ora';
SQL> alter database open;
SQL> create spfile from pfile;

MEMORY_TARGET AND SGA_MAX_SIZE PARAMETERS in oracle 11g

SGA_% parameters are enabled when AMM(Automatic memory management) is disable.

AMM enabled = SGA and PGA sizes are automatically tuned by oracle. We just set Memory_MAX_TARGET
and MEMORY_TARGET params and oracle give enough memory to the SGA and PGA whatever they need.
For ex: afternoon SGA has no empty memory space but PGA has a lot of them, oracle takes spaces
from PGA and give it to the SGA.

while AMM is enbled sga_target and sga_max_target are not considered (except lower bound limit,
if you set sga_target like 10G, then oracle can not takes too much space and cause to SGA has
less then 10G).

if you disable AMM, this means Memory_MAX_TARGET and MEMORY_TARGET are 0 any more and oracle
will not transfer memory spaced between SGA and PGA! from now, you need SGA_TARGET and
SGA_MAX_TARGET params to be set. so oracle will know how much space SGA will use and also
components of SGA can be auto-tuned (buffer cache, large pool etc).

SGA_MAX_SIZE:-
sga_max_size sets the maximum value for sga_target If sga_max_size is less than the sum of db_cache_size +
log_buffer + shared_pool_size + large_pool_size at initialization time, then the value of sga_max_size is ignored.

SGA_TARGET:-
It specifies the total amaount of SGA memory available to an instance. Setting this parameter makes Oracle distribute the available memory among various components - such as shared pool (for SQL and PL/SQL), Java pool, large_pool and buffer cache - as required.
This new feature is called Automatic Shared Memory Management. With ASMM, the parameters java_pool_size, shared_pool_size, large_pool_size and db_cache_size need not be specified explicitely anymore.
sga_target cannot be higher than sga_max_size.


SGA_TARGET is a database initialization parameter (introduced in Oracle 10g) that can be used for automatic SGA memory sizing.
+++++++++++++++++++++++++++++++++++++++++++++++++

SGA_MAX_SIZE & SGA_TARGET / MEMORY_TARGET & MEMORY_MAX_TARGET

SGA_MAX_SIZE sets the overall amount of memory the SGA can consume but is not dynamic.

The SGA_MAX_SIZE parameter is the max allowable size to resize the SGA Memory area parameters. If the SGA_TARGET is set to some value then the Automatic Shared Memory Management (ASMM) is enabled, the SGA_TARGET value can be adjusted up to the SGA_MAX_SIZE parameter, not more than SGA_MAX_SIZE parameter value.

MEMORY_TARGET & MEMORY_MAX_TARGET 

you can manage SGA and PGA together rather than managing them separately.

If you set SGA_TARGET, SGA_MAX_SIZE and PGA_AGGREGATE_TARGET to 0 and set MEMORY_TARGET (and optionally MEMORY_MAX_TARGET) to non zero value, Oracle will manage both SGA components and PGA together within the limit specified by you.

If MEMORY_TARGET is set to 1024MB, Oracle will manage SGA and PGA components within itself.

If MEMORY_TARGET is set to non zero value:

SGA_TARGET, SGA_MAX_SIZE and PGA_AGGREGATE_TARGET are set to 0, 60% of memory mentioned in MEMORY_TARGET
is allocated to SGA and rest 40% is kept for PGA.
SGA_TARGET and PGA_AGGREGATE_TARGET are set to non-zero values, these values will be considered minimum
values.
SGA_TARGET is set to non zero value and PGA_AGGREGATE_TARGET is not set. Still these values will be
autotuned and PGA_AGGREGATE_TARGET will be initialized with value of (MEMORY_TARGET-SGA_TARGET).
PGA_AGGREGATE_TARGET is set and SGA_TARGET is not set. Still both parameters will be autotunes.
SGA_TARGET will be initialized to a value of (MEMORY_TARGET-PGA_AGGREGATE_TARGET).

How to find out when did a user changed his password in oracle 11g

Summary :-
The table sys.user$ contains the field ptime, which keeps the time when the password was
changed the last time over. Do not confound it with ctime, which is the "creation time", nor with
ltime, which is the time the account has been locked (if any).

SQL>SELECT NAME, ptime AS "LAST TIME CHANGED", ctime "CREATION TIME", ltime "LOCKED"
FROM USER$
WHERE ptime IS NOT NULL
ORDER BY ptime DESC;


NAME                           LAST TIME CREATION  LOCKED
------------------------------ --------- --------- ---------
APPUSER                        27-FEB-15 16-FEB-15
HELPDESK                       27-FEB-15 27-FEB-15
SOUMYA                         27-FEB-15 14-FEB-15
SYS                            25-FEB-15 15-AUG-09 15-AUG-09
SYSTEM                         25-FEB-15 15-AUG-09 15-AUG-09
BRIAN                          16-FEB-15 16-FEB-15