Error in invoking target ‘agent nmhs’ of makefile

The following errors occurred while install 11.2.0.4 on Linux 7.4.  when relink the binary, the same error will be reported as well.

INFO: collect2: error: ld returned 1 exit status

INFO: make[1]:
INFO: *** [/u01/app/oracle/product/11.2.0/dbhome_1/sysman/lib/emdctl] Error 1

INFO: make[1]: Leaving directory `/u01/app/oracle/product/11.2.0/dbhome_1/sysman/lib'

INFO: make: *** [emdctl] Error 2

INFO: End output from spawned process.
INFO: ----------------------------------
SEVERE: oracle.sysman.oii.oiil.OiilActionException: Error in invoking target 'agent nmhs' of makefile '/u01/app/oracle/product/11.2.0/dbhome_1/sysman/lib/ins_emagent.mk'. See '/u01/app/oracle/product/11.2.0/dbhome_1/install/relinkActions2018-05-25_09-48-55-AM.log' for details.
at oracle.sysman.oii.oiis.OiisMakeDeps.invokeMakefile(OiisMakeDeps.java:537)
at oracle.sysman.oii.oiis.OiisMakeDeps.doRelink(OiisMakeDeps.java:614)
at oracle.sysman.oii.oiis.OiisMakeDeps.doOperation(OiisMakeDeps.java:799)
at oracle.sysman.oii.oiis.OiisMakeDeps.main(OiisMakeDeps.java:809)

WORKAROUND

In $ORACLE_HOME/sysman/lib/ins_emagent.mk file :

Change “$(MK_EMAGENT_NMECTL) “

into

$(MK_EMAGENT_NMECTL) -lnnz11

Asmcmd Pwcopy With PRCD-1163 PRCR-1071 CRS-0245 ASMCMD-9453

After standby database rebuilt, then trying to copy database password file from primary to standby ASM disk, the below errors occurred:

ASMCMD> pwcopy --dbuniquename RACTEST '/tmp/orapwractest' '+DATA/RACTEST/orapwractest'
copying /tmp/orapwractest -> +DATA/RACTEST/orapwractest
PRCD-1163 : Failed to modify database RACTEST
PRCR-1071 : Failed to register or update resource ora.ractest.db
CRS-0245: User doesn't have enough privilege to perform the operation
ASMCMD-9453: failed to register password file as a CRS resource

The password file was copied to ASM diskgroup ‘+DATA’ successfully, but the configuring  of database failed .

WORKAROUND

Rerun the same command without “–dbuniquename RACTEST” option.

ASMCMD> pwcopy /tmp/orapwractest +DATA/RACTEST/orapwractest
copying /tmp/orapwractest -> +DATA/RACTEST/orapwractest

Then use ‘srvctl’ to re-configure database as user ‘oracle’ if needed:

$srvctl modify database -d RACTEST -pwfile '+DATA/RACTEST/orapwractest';

$srvctl config database -d RACTEST

How to Migrate SQL Plan Baselines Between Oracle Databases

After database upgrading / Patching, or database is migrated onto new infrastructure, it is common to see database performance is degrading due to SOL execution plans changed.

What we can do is by creating  SQL plan baselines from old database, then copied them on to new database.

1) Identify the SQL plan baseline(s) to be migrated from source database:

SQL> select sql_handle, plan_name, sql_text, enabled, accepted 
     from   dba_sql_plan_baselines 
     where enabled='YES' 
       and accepted='YES' 
       and PLAN_NAME='SQL_PLAN_7ns0utmam6vc64a63fd18';

SQL_HANDLE           PLAN_NAME                      SQL_TEXT ENA ACC
-------------------- ------------------------------ -------- --- ---
SQL_7a601accd5336d86 SQL_PLAN_7ns0utmam6vc64a63fd18 UPDATE . YES YES

2) Create a staging table on source database:

SQL> BEGIN
        DBMS_SPM.CREATE_STGTAB_BASELINE(
        table_name => 'spm_tab',
        table_owner => 'testuser',
        tablespace_name => 'USERS');
END; 

/

PL/SQL procedure successfully completed.


SQL> select table_name,owner from dba_tables where owner='TETSUSER';

TABLE_NAME   OWNER
------------ ---------
SPM_TAB      TETSUSER

3) Pack  SQL plan baseline(s) into above staging table ( spm_tab):

var xx number;
begin
:xx := DBMS_SPM.PACK_STGTAB_BASELINE('spm_tab', 'testuser', -
      sql_handle => 'SQL_7a601accd5336d86',  -
      plan_name =>  'SQL_PLAN_7ns0utmam6vc64a63fd18' ); 
end;

/

PL/SQL procedure successfully completed.

4) Export staging table with datapump:

$ expdp directory=exp_dir dumpfile=testuser_spm_tab.dmp 
  tables=testuser.spm_tab

5) Transfer the datapump file onto target database server.

6) Import staging table onto target database:

$ impdp directory=imp_dir dumpfile=testuser_spm_tab.dmp 
tables=testuser.spm_tab remap_schema=... remap_tablespace=...

7) Unpack the SQL plan baselines in staging table into target database:

SQL> var xx number;
     begin
      :xx := DBMS_SPM.UNPACK_STGTAB_BASELINE('SPM_TAB', 'TESTUSER');
     end;

     /

PL/SQL procedure successfully completed.

8) Verify the SQL plan baselines have been migrated onto target database:

SQL>select sql_handle, plan_name, sql_text, enabled, accepted, fixed 
      from dba_sql_plan_baselines
      where SQL_HANDLE='SQL_7a601accd5336d86';

SQL_HANDLE           PLAN_NAME                    SQL_TEXT  ENA ACC FIX
-------------------- ------------------------------ ------- --- --- ---
SQL_7a601accd5336d86 SQL_PLAN_7ns0utmam6vc64a63fd18 UPDATE  YES YES NO

9) Display execution plans for this SQL handle of a SQL plan baseline:

SQL> set linesize 120
SQL> set pagesize 2000
SQL> select * from TABLE(DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE( -
     'SQL_7a601accd5336d86','SQL_PLAN_7ns0utmam6vc64a63fd18'));

Syntax:

DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE (
   sql_handle      IN VARCHAR2 := NULL,
   plan_name       IN VARCHAR2 := NULL,
   format          IN VARCHAR2 := 'TYPICAL')
 RETURN dbms_xplan_type_table;
ParameterDescription
sql_handleSQL statement handle. It identifies a SQL statement whose plan(s) are to be displayed.
plan_namePlan name. It identifies a specific plan. Default NULL means all plans associated with identified SQL statement are explained and displayed.
formatFormat string determines what information stored in the plan displayed. One of three format values (‘BASIC’, ‘TYPICAL’, ‘ALL’) can be used, each representing a common use case.