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

How to find out users with deadly roles assigned to them

SQL>select grantee, granted_role, admin_option
from   sys.dba_role_privs
where  granted_role in ('DBA', 'AQ_ADMINISTRATOR_ROLE',
                       'EXP_FULL_DATABASE', 'IMP_FULL_DATABASE',
                       'OEM_MONITOR')
  and  grantee not in ('SYS', 'SYSTEM', 'OUTLN', 'AQ_ADMINISTRATOR_ROLE',
                       'DBA', 'EXP_FULL_DATABASE', 'IMP_FULL_DATABASE',
                       'OEM_MONITOR', 'CTXSYS', 'DBSNMP', 'IFSSYS',
                       'IFSSYS$CM', 'MDSYS', 'ORDPLUGINS', 'ORDSYS',
                       'TIMESERIES_DBA');
                      
GRANTEE                        GRANTED_ROLE                                                     ADM
------------------------------ ------------------------------                                              ---
IX                                     AQ_ADMINISTRATOR_ROLE                                 NO
GHHCWS                        DBA                                                                             NO
BRIAN                             DBA                                                                             NO
DATAPUMP_EXP_FULL_DATABASE     EXP_FULL_DATABASE              NO
DATAPUMP_IMP_FULL_DATABASE     EXP_FULL_DATABASE              NO
DATAPUMP_IMP_FULL_DATABASE     IMP_FULL_DATABASE              NO
OWBSYS                         AQ_ADMINISTRATOR_ROLE                               NO                      

How to change user's password in oracle 11g

Summary :-
As a DBA you may need to log in as another user, for example to test an application after
doing some workarounds to solve a problem. But you don't know the password and you don't want
to wait. You can change the password, as dba, but how to change it back to previous?

The right way to proceed is:
    * Note down the current (old) password, as found in data dictionary.
    * modify the password with the command ALTER USER IDENTIFIED BY
    * connect using the new password
    * do what you wanted to do
    * reset the password with the clause IDENTIFIED BY VALUES

To get the current password :-
SQL> SET LONG 100000
SQL> SELECT dbms_metadata.get_ddl('USER','SOUMYA') FROM dual;

   CREATE USER "SOUMYA" IDENTIFIED BY VALUES 'S:25204F11525CA54FAC51C7A06E8BF5D2
47BAB12D3C68B3FEAAA688FFF92F;6673D7515E467AA4'
      DEFAULT TABLESPACE "USERS"
      TEMPORARY TABLESPACE "TEMP"

Yes we can only get the password as encrypted format.

Now change the password temporarity:-
SQL > alter user soumya identified by das;

Once done, Login to the user a/c:-
SQL> conn soumya/das
Connected.

Now once the job is done we can again change back the password into previous one:-
SQL> alter user soumya identified by values 'S:25204F11525CA54FAC51C7A06E8BF5D2
47BAB12D3C68B3FEAAA688FFF92F;6673D7515E467AA4' ;


Another method:-

SQL> select spare4 from user$ where name='SOUMYA';

SPARE4
--------------------------------------------------------------------------------
S:C3C54AC4597D90A7CE643D6874F8348167D20E624FC49D981A2DE2BB116F

SQL>  alter user "SOUMYA" identified by values 'S:25204F11525CA54FAC51C7A06E8BF5D2
47BAB12D3C68B3FEAAA688FFF92F';

User altered.

Thats it.

How to find out Rman backup time in oracle 11g

SQL> SELECT * FROM (SELECT END.dt, 'Incremental' BACKUP, STAR.TIME started, END.TIME END,
DECODE(TRUNC(END.TIME - star.TIME), 0, NULL, TRUNC(END.TIME - star.TIME) || ' Days' || ' + ') ||
TO_CHAR(TO_DATE(TRUNC(MOD(END.TIME - star.TIME,1) * 86400), 'SSSSS'), 'HH24:MI:SS') TIME
FROM
(SELECT TRUNC(start_time) dt , MAX(COMPLETION_TIME) TIME FROM V$BACKUP_SET WHERE BACKUP_TYPE='I'
GROUP BY TRUNC(start_time)) END,
(SELECT TRUNC(start_time) dt, MIN(START_TIME) TIME FROM V$BACKUP_SET WHERE BACKUP_TYPE='I'
GROUP BY TRUNC(start_time)) STAR
WHERE END.dt = STAR.dt
UNION ALL
SELECT END.dt, 'Full Database' BACKUP, STAR.TIME started, END.TIME END,
DECODE(TRUNC(END.TIME - star.TIME), 0, NULL, TRUNC(END.TIME - star.TIME) || ' Days' || ' + ') ||
TO_CHAR(TO_DATE(TRUNC(MOD(END.TIME - star.TIME,1) * 86400), 'SSSSS'), 'HH24:MI:SS') TIME
FROM
(SELECT TRUNC(start_time) dt , MAX(COMPLETION_TIME) TIME FROM V$BACKUP_SET WHERE BACKUP_TYPE='D'
GROUP BY TRUNC(start_time)) END,
(SELECT TRUNC(start_time) dt, MIN(START_TIME) TIME FROM V$BACKUP_SET WHERE BACKUP_TYPE='D'
GROUP BY TRUNC(start_time)) STAR
WHERE END.dt = STAR.dt)
ORDER BY dt ASC;

DT        BACKUP        STARTED   END       TIME
--------- ------------- --------- --------- --------------------------------------------------------
18-FEB-15 Full Database 18-FEB-15 18-FEB-15 00:01:07
18-FEB-15 Incremental   18-FEB-15 18-FEB-15 00:00:16