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

How to maintain Auditing housekeeping in oracle 11g

If you enable auditing then the auditing information, (stored in table SYS.AUD$) will start
growing and because is relies on SYSTEM tablespace you might have performance problems in the
future. Auditing housekeeping must be setup.

1. Create a SYSTEM.AUD$_BU table stored in a different tablespace(AUDIT_DATA) where you will
ove all you auditing produced.

[oracle@server1]$ sqlplus /  as sysdba

SQL> CREATE TABLESPACE AUDIT_DATA DATAFILE '/u01/app/oracle/oradata/prim/audit_data_001.dbf' SIZE 100M AUTOEXTEND ON
LOGGING
PERMANENT
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT Auto;

2.Create a procedure(Keep_Size_Aud_Log) that moves the rows from SYS.AUD$ to SYSTEM.AUD$_BU
SQL> CREATE OR REPLACE PROCEDURE Keep_Size_Aud_Log
IS
  rowCount NUMBER;
BEGIN
  SELECT COUNT(*) INTO rowCount FROM sys.aud$ ;
  IF rowCount > 0
  THEN
    COMMIT;
    INSERT /*+ APPEND */ INTO SYSTEM.aud$_bu (SELECT * FROM sys.aud$);
 COMMIT;
     EXECUTE IMMEDIATE 'truncate table sys.aud$';
    sys.Dbms_System.ksdwrt (3,'ORA-AUDIT TRAIL: rows moved from SYS.AUD$ to SYSTEM.AUD$_BU');
  END IF;
END Keep_Size_Aud_Log;
/

3. Execute the procedure every day at midnight with a job

SQL> DECLARE
  X NUMBER;
BEGIN
  SYS.DBMS_JOB.SUBMIT
    (
      job        => X
     ,what       => 'SYS.KEEP_SIZE_AUD_LOG;'
     ,next_date  => TO_DATE('23/02/2015 01:00:00','dd/mm/yyyy hh24:mi:ss')
     ,INTERVAL   => 'TRUNC(SYSDATE+1)'
     ,no_parse   => FALSE
    );
END;

Tip: To speed up searching on SYSTEM.AUD$_BU you can create 2 indexes (one on timestamp# and
the other to userid)
SQL> CREATE INDEX SYSTEM.AUD$_BU_TIME_IDX ON SYSTEM.AUD$_BU (TIMESTAMP#) NOLOGGING TABLESPACE
AUDIT_DATA;
Index created.

SQL> CREATE INDEX SYSTEM.AUD$_BU_USERID_IDX ON SYSTEM.AUD$_BU (USERID) NOLOGGING TABLESPACE
AUDIT_DATA;
Index created.

How to find out table fragmentation and fix it in oracle 11g


When you start doing DML operations such as deletes and updates constantly in a table,
you may end up having a fragmented table in your database.As we know the high water mark of
table actually defines the border line between used and unused space for tables. While performing
full table scan, oracle will always read the data up to the high water mark (used block).
HWM is an indicator of USED BLOCKS in the database .
The following tests have been done in oracle 11g Rhel 6.

An example follows how to make a table full of holes and start wasting space and of course
performance when doing full table scans on it.

1. Create the table OBJECTS.
[oracle@server1 ~]$ sqlplus appuser/appuser
SQL> create table test as select * from all_objects;
Table created
SQL> create index IDXT1 on test(owner);
Index created

SQL> select count(*) from test;

  COUNT(*)
----------
     57995

Gather table stats :-
SQL>Begin DBMS_STATS.GATHER_TABLE_STATS (
     ownname => 'APPUSER',
     tabname => 'TEST',
     degree => 2,
     cascade => TRUE,
     METHOD_OPT => 'FOR COLUMNS SIZE AUTO',
     estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE);
     END;
     /

Check high water mark on db block:-
SQL>select blocks "Ever Used", empty_blocks "Never Used", num_rows "Total rows"
From user_tables where table_name='TEST';
 Ever Used Never Used Total rows
---------- ---------- ----------
       854          0      57986

2. Lets check the size of the table( with fragmented):-
SQL> select table_name,round((blocks*8),2)||'kb' "size"
 From user_tables where table_name = 'TEST';

TABLE_NAME                     size
------------------------------ ------------------------------------------
TEST                           6832kb

3. Make a huge hole deleting all the rows and leaving just one!
SQL>DELETE FROM TEST WHERE ROWNUM < 57985;
SQL> commit;

Now again check the table size after deletion:-
SQL> select table_name,round((blocks*8),2)||'kb' "size"
 From user_tables where table_name = 'TEST';

TABLE_NAME                     size
------------------------------ ------------------------------------------
TEST                           6832kb

We see after the deletions of the rows the tablesize is same.

4.But the actual data size is:-
SQL>select table_name,round((num_rows*avg_row_len/1024),2)||'kb' "size"
from user_tables where table_name = 'TEST';

TABLE_NAME                     size
------------------------------ ------------------------------------------
TEST                           .17kb


So the difference in size is like (6832-.17)KB = 6829.83KB


5.The solution to win back the wasted table space is reseting HighWaterMark.
For that we need to reorganize the fragmented table. We have 4 options to reorganize
fragmented tables:
1. alter table ... move + rebuild indexes
2. create table as select ( CTAS)
3. export / truncate / import
4. By shrinking the  Table


Option 1:-
Before going ahead with this option first take a fresh statistics for the table
SQL>Begin DBMS_STATS.GATHER_TABLE_STATS (
     ownname => 'APPUSER',
     tabname => 'TEST',
     degree => 2,
     cascade => TRUE,
     METHOD_OPT => 'FOR COLUMNS SIZE AUTO',
     estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE);
     END;
     /


[oracle@server1 ~]$ sqlplus /  as sysdba
SQL> alter table appuser.test move;
Table altered.

SQL> select status,index_name from dba_indexes
where table_name like 'TEST%';

STATUS   INDEX_NAME
-------- ------------------------------
UNUSABLE IDXT1

SQL> ALTER INDEX APPUSER.IDXT1 REBUILD online;
SQL> select status,index_name from dba_indexes
where table_name like 'TEST%';;

STATUS   INDEX_NAME
-------- ------------------------------
VALID    IDXT1

SQL> Begin DBMS_STATS.GATHER_TABLE_STATS (
     ownname => 'APPUSER',
     tabname => 'TEST',
     degree => 2,
     cascade => TRUE,
     METHOD_OPT => 'FOR COLUMNS SIZE AUTO',
     estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE);
     END;
     /


Now check the original table size after moving the table:-
SQL> select table_name,owner,round((blocks*8),2)||'kb' "size"
from dba_tables where table_name like 'TEST%';
TABLE_NAME                     OWNER                          size
------------------------------ -----------------              ----------------
TEST                           APPUSER                        32kb

We see the table size has reduces and we recovered the wasted space.

Option 2:-create table as select ( CTAS)

P.S. in this procedure keep a track of created index on the table.Cause after dropping the
table we need to recreate those indexes.

[oracle@server1 ~]$ sqlplus /  as sysdba
SQL> select index_name,table_owner ,table_name from dba_ind_columns where table_name like 'TEST%';

INDEX_NAME                     TABLE_OWNER                    TABLE_NAME
------------------------------ ------------------------------ ------------------------------
IDXT1                          APPUSER                        TEST
SQL> create table test1 as select * from test;

Table created.

SQL> drop table test purge;

Table dropped.

SQL> rename test1 to test;(This query if you are the table owner)
OR
SQL> alter table appuser.test1 rename to test;(Use this query if you are doing the activity as sys user)

Table renamed.

SQL> Begin DBMS_STATS.GATHER_TABLE_STATS (
     ownname => 'APPUSER',
     tabname => 'TEST',
     degree => 2,
     cascade => TRUE,
     METHOD_OPT => 'FOR COLUMNS SIZE AUTO',
     estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE);
     END;
     /

PL/SQL procedure successfully completed.

SQL> select table_name,round((blocks*8),2)||'kb' "size"
 from dba_tables
 where table_name = 'TEST';

TABLE_NAME size
------------------------------ ------------------------------------------
TEST 32kb

SQL> select table_name,round((num_rows*avg_row_len/1024),2)||'kb' "size"
 from user_tables
 where table_name = 'TEST';

TABLE_NAME size
------------------------------ ------------------------------------------
TEST .17kb

SQL> select status from DBA_INDEXES
 where table_name = 'TEST';

no rows selected

SQL>create index IDXT1 on APPUSER.TEST ( OWNER ) ;




Option 3.
SQL> select table_name,round((blocks*8),2)||'kb' "size"
  2   from user_tables
  3   where table_name = 'TEST';

TABLE_NAME size
---------- ------------------------------------------
TEST       6832kb


SQL> select table_name,round((num_rows*avg_row_len/1024),2)||'kb' "size"
  2   from user_tables
  3   where table_name = 'TEST';

TABLE_NAME size
---------- ------------------------------------------
TEST       .17kb

SQL>  select status from user_indexes where table_name = 'TEST';

STATUS
--------
VALID

Now take an export backup of the Test table:-
[oracle@server1 ~]$ expdp system/system dumpfile=test.dmp directory=DATA_PUMP_DIR tables=appuser.test
Now truncate the table
[oracle@server1 ~]$ sqlplus /  as sysdba
SQL> truncate table appuser.test;

Now lets import the table from the expdp dump.
[oracle@server1 ~]$ impdp system/system dumpfile=test.dmp tables=appuser.test directory=DATA_PUMP_DIR ignore=y

SQL>EXEC dbms_redefinition.can_redef_table('APPUSER','TEST', dbms_redefinition.cons_use_pk);

Option 4.By shrinking the  Table

SQL>Begin DBMS_STATS.GATHER_TABLE_STATS (
     ownname => 'APPUSER',
     tabname => 'TEST',
     degree => 2,
     cascade => TRUE,
     METHOD_OPT => 'FOR COLUMNS SIZE AUTO',
     estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE);
     END;
     /

SQL> alter table TEST enable row movement;
Table altered.
#In first part re-arrange rows and in second part reset the HWM.
SQL> alter table TEST shrink space compact;
Table altered.
SQL> alter table TEST shrink space;
Table altered.

For this method the whole tablespace has to be in ASSM(automatic segment space manament).

Benefit for using Shrink command Method:-
Unlike "alter table move", indexes are not in UNUSABLE state. After shrink command, indexes are updated also.
It is an online operation, so you do not need downtime to do re-organization.
It does not require any extra space for the process to complete.
Related Posts Plugin for WordPress, Blogger...