Oracle

Tuesday, May 29, 2018

WARNING: NMO not setuid-root (Unix-only) 13c OEM

For the past Few days my OEM jobs on my DEV server are getting SUSPENDED , tried to schedule multiple time after failure and still they are NOT running they are all in SUSPENDED State.

Agent is up and running and I tried stop, start ,clear, secure, upload, reload all sort of agent commands and still my jobs getting SUSPENDED

From the server all looks good.

mydev01:NOTSET:/opt/oracle/product/oemagent/agent13c_dev/agent_inst/bin $ ./emctl status agent

Oracle Enterprise Manager Cloud Control 13c Release 2

---------------------------------------------------------------

Agent Version : 13.2.0.0.0

OMS Version : 13.2.0.0.0

Protocol Version : 12.1.0.1.0

Agent Home : /opt/oracle/product/oemagent/agent13c_dev/agent_inst

Agent Log Directory : /opt/oracle/product/oemagent/agent13c_dev/agent_inst/sysman/log

Agent Binaries : /opt/oracle/product/oemagent/agent13c_dev/agent_13.2.0.0.0

Core JAR Location : /opt/oracle/product/oemagent/agent13c_dev/agent_13.2.0.0.0/jlib

Agent Process ID : 261231

Parent Process ID : 261232

Agent URL : https://mydev01.domain.local:3872/emd/main/

Local Agent URL in NAT : https://mydev01.domain.local:3872/emd/main/

Repository URL : https://mydevoms.domain.local:4903/empbs/upload

Started at : 2018-03-21 12:25:23

Started by user : oracle

Operating System : Linux version 2.6.32-696.23.1.el6.x86_64 (amd64)

Number of Targets : 23

Last Reload : (none)

Last successful upload : 2018-03-21 12:59:32

Last attempted upload : 2018-03-21 12:59:32

Total Megabytes of XML files uploaded so far : 0.37

Number of XML files pending upload : 0

Size of XML files pending upload(MB) : 0

Available disk space on upload filesystem : 38.65%

Collection Status : Collections enabled

Heartbeat Status : Ok

Last attempted heartbeat to OMS : 2018-03-21 13:00:45

Last successful heartbeat to OMS : 2018-03-21 13:00:45

Next scheduled heartbeat to OMS : 2018-03-21 13:01:45

---------------------------------------------------------------

Agent is Running and Ready

mydev01:NOTSET:/opt/oracle/product/oemagent/agent13c_dev/agent_inst/bin $ ./emctl upload agent

Oracle Enterprise Manager Cloud Control 13c Release 2

---------------------------------------------------------------

EMD upload completed successfully

FROM my OEM I see Agent is fine and up and running (but with additional warning)

WARNING:

NMO not setuid-root (Unix-only). Run root.sh script to correct this problem. The next OS Command Job submitted will clear this alert.

According to (Doc ID 1465278.1) this is what I have followed

mydev01:NOTSET:/opt/oracle/product/oemagent/agent13c_dev/agent_13.2.0.0.0/sbin $ ls -lrht

total 33M

-rw-r----- 1 oracle dba 9.4K Aug 1 2016 nmr_macro_list

-rwx--x--x 1 oracle dba 4.6M Sep 30 2016 nmo.0

-rwx--x--x 1 oracle dba 4.5M Sep 30 2016 nmoconf

-rwx--x--x 1 oracle dba 52K Sep 30 2016 nmb.0

-rwx--x--x 1 oracle dba 50K Sep 30 2016 nmgsshe.0

-rwx------ 1 oracle dba 103K Sep 30 2016 nmr.0

-rwx------ 1 oracle dba 34K Sep 30 2016 nmrconf

-rwx--x--x 1 oracle dba 4.5M Sep 30 2016 nmosudo.0

-rwx--x--x 1 oracle dba 4.5M Sep 30 2016 nmopdpx.0

-rwx--x--x 1 oracle dba 104K Sep 30 2016 nmhs.0

-rwxr-x--- 1 oracle dba 103K Feb 23 2017 nmr

-rwxr-xr-x 1 oracle dba 4.5M Feb 23 2017 nmosudo

-rw-r----- 1 oracle dba 188 Feb 23 2017 nmo_public_key.txt

-rwxr-xr-x 1 oracle dba 4.5M Feb 23 2017 nmopdpx

-rwxr-x--- 1 oracle dba 4.6M Feb 23 2017 nmo

-rwxr-x--- 1 oracle dba 104K Feb 23 2017 nmhs

-rwxr-xr-x 1 oracle dba 50K Feb 23 2017 nmgsshe

-rwxr-x--- 1 oracle dba 52K Feb 23 2017 nmb

mydev01:NOTSET:/opt/oracle/product/oemagent/agent13c_dev/agent_13.2.0.0.0 $ ls -lrth root*

-rwxr-xr-x 1 oracle dba 4.7K Feb 23 2017 root.sh.template

-rwxr-xr-x 1 oracle dba 4.8K Feb 23 2017 root.sh

[root@mydev01 root] # /opt/oracle/product/oemagent/agent13c_dev/agent_13.2.0.0.0/root.sh

Finished product-specific root actions.

/etc exist

Once Root.sh script ran, I see permissions now changed.

mydev01:NOTSET:/opt/oracle/product/oemagent/agent13c_dev/agent_13.2.0.0.0 $ cd sbin/

mydev01:NOTSET:/opt/oracle/product/oemagent/agent13c_dev/agent_13.2.0.0.0/sbin $ ls -lrht

total 37M

-rw-r----- 1 root dba 9.4K Aug 1 2016 nmr_macro_list

-rwx--x--x 1 oracle dba 4.6M Sep 30 2016 nmo.0

-rwx--x--x 1 oracle dba 4.5M Sep 30 2016 nmoconf

-rwx--x--x 1 oracle dba 52K Sep 30 2016 nmb.0

-rwx--x--x 1 oracle dba 50K Sep 30 2016 nmgsshe.0

-rwx------ 1 oracle dba 103K Sep 30 2016 nmr.0

-rwx------ 1 oracle dba 34K Sep 30 2016 nmrconf

-rwx--x--x 1 oracle dba 4.5M Sep 30 2016 nmosudo.0

-rwx--x--x 1 oracle dba 4.5M Sep 30 2016 nmopdpx.0

-rwx--x--x 1 oracle dba 104K Sep 30 2016 nmhs.0

-rwx--x--x 1 root root 4.6M Mar 21 13:20 nmo.new.bak

-rw-r----- 1 root dba 188 Mar 21 13:20 nmo_public_key.txt

-rwsr-x--- 1 root dba 4.6M Mar 21 13:20 nmo

-rwsr-x--- 1 root dba 104K Mar 21 13:20 nmhs

-rwsr-x--- 1 root dba 52K Mar 21 13:20 nmb

-rwx------ 1 root root 103K Mar 21 13:20 nmr.new.bak

-rwsr-x--- 1 root dba 103K Mar 21 13:20 nmr

-rwxr-xr-x 1 root dba 4.5M Mar 21 13:20 nmosudo

-rwxr-xr-x 1 root dba 50K Mar 21 13:20 nmgsshe

-rwxr-xr-x 1 root dba 4.5M Mar 21 13:20 nmopdpx

mydev01:NOTSET:/opt/oracle/product/oemagent/agent13c_dev/agent_13.2.0.0.0/sbin $ cd ../bin

mydev01:NOTSET:/opt/oracle/product/oemagent/agent13c_dev/agent_13.2.0.0.0/bin $ ./emctl status agent

Oracle Enterprise Manager Cloud Control 13c Release 2

---------------------------------------------------------------

Agent Version : 13.2.0.0.0

OMS Version : 13.2.0.0.0

Protocol Version : 12.1.0.1.0

Agent Home : /opt/oracle/product/oemagent/agent13c_dev/agent_inst

Agent Log Directory : /opt/oracle/product/oemagent/agent13c_dev/agent_inst/sysman/log

Agent Binaries : /opt/oracle/product/oemagent/agent13c_dev/agent_13.2.0.0.0

Core JAR Location : /opt/oracle/product/oemagent/agent13c_dev/agent_13.2.0.0.0/jlib

Agent Process ID : 261231

Parent Process ID : 261232

Agent URL : https://mydev01.domain.local:3872/emd/main/

Local Agent URL in NAT : https://mydev01.domain.local:3872/emd/main/

Repository URL : https://mydevoms.domain.local:4903/empbs/upload

Started at : 2018-03-21 12:25:23

Started by user : oracle

Operating System : Linux version 2.6.32-696.23.1.el6.x86_64 (amd64)

Number of Targets : 23

Last Reload : (none)

Last successful upload : 2018-03-21 13:19:32

Last attempted upload : 2018-03-21 13:19:32

Total Megabytes of XML files uploaded so far : 0.41

Number of XML files pending upload : 0

Size of XML files pending upload(MB) : 0

Available disk space on upload filesystem : 38.65%

Collection Status : Collections enabled

Heartbeat Status : Ok

Last attempted heartbeat to OMS : 2018-03-21 13:20:46

Last successful heartbeat to OMS : 2018-03-21 13:20:46

Next scheduled heartbeat to OMS : 2018-03-21 13:21:47

---------------------------------------------------------------

Agent is Running and Ready

mydev01:NOTSET:/opt/oracle/product/oemagent/agent13c_dev/agent_13.2.0.0.0/bin $ ./emctl stop agent

Oracle Enterprise Manager Cloud Control 13c Release 2

Stopping agent ... stopped.

mydev01:NOTSET:/opt/oracle/product/oemagent/agent13c_dev/agent_13.2.0.0.0/bin $ ./emctl start agent

Oracle Enterprise Manager Cloud Control 13c Release 2

Starting agent ............. started.

Now I see Warning message is Gone in OEM.

Sunday, April 1, 2018

Fully qualified table name is not found in the Oracle GoldenGate configuration

Today on one of my Goldengate server i want to see the transaction statistics for a particular table on source side and when I queried below using ggsci stats command I see “Fully qualified table name MYCUSTOMER.CUST_CLEAR_HIST is not found in the Oracle GoldenGate configuration“ in the output

Reason:

Yes this is NOT an issue and it is expected when there are no transactions performed on this table ever since on source side then the output prints this message.

According to (Doc ID 1941158.1), If no transaction log records for the specific table have been read and processed by the extract process, the internal mapping table has not been built.

myproddemo:custdb1:/goldengate/home $ ./ggsci

Oracle GoldenGate Command Interpreter for Oracle

Version 12.2.0.1.1 OGGCORE_12.2.0.1.0_PLATFORMS_151211.1401_FBO

Linux, x64, 64bit (optimized), Oracle 12c on Dec 12 2015 02:56:48

Operating system character set identified as UTF-8.

GGSCI (myproddemo) 1> stats ECUSTDBP totalsonly MYCUSTOMER.CUST_CLR_HIST

Sending STATS request to EXTRACT ECUSTDBP ...

Start of Statistics at 2018-03-30 10:19:28.

DDL replication statistics (for all trails):

*** Total statistics since extract started ***

Operations 9.00

Output to /goldengate/home/dirdat/SRC/custdb/trgdb/ct:

Cumulative totals for specified table(s):

*** Total statistics since 2018-03-05 06:47:25 ***

No database operations have been performed.

*** Daily statistics since 2018-03-30 00:00:00 ***

No database operations have been performed.

*** Hourly statistics since 2018-03-30 10:00:00 ***

No database operations have been performed.

*** Latest statistics since 2018-03-05 06:47:25 ***

No database operations have been performed.

Fully qualified table name MYCUSTOMER.CUST_CLR_HIST is not found in the Oracle GoldenGate configuration.

End of Statistics.

Saturday, March 31, 2018

Convert 12c STANDBY database to SNAPSHOT Standby

Hello Everyone, today I’m going to write a post on how to convert a 12c standalone standby database to SNAPSHOT STANDBY database (which is Read/Write Mode)

Before Converting lets check the current status of the standby database

STANDBY database:

orastandby:myprod:/export/myprod $ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Fri Jan 13 15:20:02 2017

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> Show parameter db_recovery_file_dest

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

db_recovery_file_dest string /pkg/myprod/u200

db_recovery_file_dest_size big integer 5G

SQL> select flashback_on from v$database;

FLASHBACK_ON

------------------

YES

SQL> SELECT FILE_TYPE "Type",PERCENT_SPACE_USED "% Used",PERCENT_SPACE_RECLAIMABLE "% Reclaim",

NUMBER_OF_FILES "# Files" FROM V$FLASH_RECOVERY_AREA_USAGE;

select space_limit/1024/1024/1024 maxGB,space_used/1024/1024/1024 usedGB from v$recovery_file_dest;

SQL> select NAME, OPEN_MODE, GUARD_STATUS, DATABASE_ROLE from v$database;

NAME OPEN_MODE GUARD_S DATABASE_ROLE

--------- -------------------- ------- ----------------

MYPROD READ ONLY WITH APPLY NONE PHYSICAL STANDBY

SQL> select thread#,max(sequence#) from v$archived_log where applied='YES' group by thread#;

THREAD# MAX(SEQUENCE#)

---------- --------------

1 72036

SQL> set line 280

SQL> SELECT name "Database name", STATUS "MRP process",ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference" FROM (select name from v$database) name,(SELECT STATUS FROM V$MANAGED_STANDBY where process like '%MRP%') process,(SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH, (SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL WHERE ARCH.THREAD# = APPL.THREAD# ORDER BY 1;

Database MRP process Thread Last Sequence Received Last Sequence Applied Difference

--------- ------------ ---------- ---------------------- --------------------- ----------

MYPROD WAIT_FOR_LOG 1 72036 72036 0

SQL> alter system set db_recovery_file_dest_size=30G scope=both;

System altered.

SQL> Show parameter db_recovery_file_dest

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

db_recovery_file_dest string /pkg/myprod/u200

db_recovery_file_dest_size big integer 30G

Check Filesystem space………

SQL> !df -h /pkg/myprod/u200

Filesystem Size Used Avail Use% Mounted on

/dev/mapper/vg01130-lvol11

473G 204G 245G 46% /pkg/myprod/u200

SQL> select protection_mode, protection_level from v$database;

PROTECTION_MODE PROTECTION_LEVEL

-------------------- --------------------

MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE

STOP MRP:

SQL> set time on

15:27:39 SQL> alter database recover managed standby database cancel;

Database altered.

Check any Restore points

15:29:52 SQL> select * from v$restore_point;

SCN DATABASE_INCARNATION# GUA STORAGE_SIZE TIME RESTORE_POINT_TIME PRE NAME CON_ID

---------- --------------------- --- ------------ ------------------------- --------------------------------------------------------------------------- --- ---------------------- ----------

2.4753E+11 1 NO 0 23-AUG-14 01.52.56.000000000 AM NO DR_BR_TO_RN_082214 0

15:32:26 SQL> ALTER DATABASE CONVERT TO SNAPSHOT STANDBY;

Database altered.

15:32:42 SQL> shut immediate

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

15:33:15 SQL> Startup

ORACLE instance started.

Total System Global Area 5368709120 bytes

Fixed Size 3722144 bytes

Variable Size 2919236704 bytes

Database Buffers 2147483648 bytes

Redo Buffers 298266624 bytes

Database mounted.

Database opened.

15:33:26 SQL> select NAME, OPEN_MODE, GUARD_STATUS, DATABASE_ROLE from v$database;

NAME OPEN_MODE GUARD_S DATABASE_ROLE

---------------------- -------------------- ------- ----------------

MYPROD READ WRITE NONE SNAPSHOT STANDBY

15:33:34 SQL> col TIME for a25

15:33:47 SQL> col NAME for a22

15:33:49 SQL> set line 280

15:33:53 SQL> SELECT NAME, SCN, TIME, DATABASE_INCARNATION#, GUARANTEE_FLASHBACK_DATABASE, STORAGE_SIZE FROM V$RESTORE_POINT;

NAME SCN TIME DATABASE_INCARNATION# GUA STORAGE_SIZE

---------------------- ---------- ------------------------- --------------------- --- ------------

SNAPSHOT_STANDBY_REQUI 3.1991E+11 13-JAN-17 03.32.39.000000 1 YES 134217728

RED_01/13/2017 15:32:39 000 PM

By DEFAULT Oracle will create a Guarantee Restore point and it uses this during reverting back to standby.

15:34:12 SQL> select space_limit/1024/1024/1024 maxGB,space_used/1024/1024/1024 usedGB from v$recovery_file_dest;

MAXGB USEDGB

---------- ----------

30 5

Now my Standby database is now in Read/Write mode

REVERT BACK TO PHYSICAL STANDBY:

15:49:53 SQL> shut immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

15:50:28 SQL> startup mount

ORACLE instance started.

Total System Global Area 5368709120 bytes

Fixed Size 3722144 bytes

Variable Size 2919236704 bytes

Database Buffers 2147483648 bytes

Redo Buffers 298266624 bytes

Database mounted.

15:50:59 SQL> ALTER DATABASE CONVERT TO PHYSICAL STANDBY; (watch the alert log)

Database altered.

15:51:16 SQL> shut immediate

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

15:51:57 SQL> startup nomount

ORACLE instance started.

Total System Global Area 5368709120 bytes

Fixed Size 3722144 bytes

Variable Size 2919236704 bytes

Database Buffers 2147483648 bytes

Redo Buffers 298266624 bytes

15:52:24 SQL> alter database mount standby database;

Database altered.

Check wallet and OPEN if it is closed:

SQL> select * from v$encryption_wallet;

WRL_TYPE

--------------------

WRL_PARAMETER

--------------------------------------------------------------------------------

STATUS WALLET_TYPE WALLET_OR FULLY_BAC

------------------------------ -------------------- --------- ---------

CON_ID

----------

FILE

/opt/oracle/admin/myprod/wallet

OPEN UNKNOWN SINGLE NO

SQL> alter database recover managed standby database disconnect from session;

NOTE: If you use just “alter database recover managed standby database” from sql then recovery starts and the sql session doesn’t release until all recovery is completed so always use “disconnect from session” and it will run in the back ground

If active standby:

SQL> alter database recover managed standby database using current logfile disconnect ; (starts mrp )

orastandby:myprod:/opt/oracle/scripts $ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Fri Jan 13 15:54:44 2017

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> col TIME for a25

SQL> col name for a44

SQL> set line 280

SQL> SELECT NAME, SCN, TIME, DATABASE_INCARNATION#, GUARANTEE_FLASHBACK_DATABASE, STORAGE_SIZE FROM V$RESTORE_POINT;

No rows selected.

Restore point has been dropped.

SQL> select space_limit/1024/1024/1024 maxGB,space_used/1024/1024/1024 usedGB from v$recovery_file_dest;

MAXGB USEDGB

---------- ----------

30 3.375

Database MRP process Thread Last Sequence Received Last Sequence Applied Difference

--------- ------------ ---------- ---------------------- --------------------- ----------

MYPROD WAIT_FOR_LOG 1 1 1 0

SQL> select NAME, OPEN_MODE, GUARD_STATUS, DATABASE_ROLE from v$database;

NAME OPEN_MODE GUARD_S DATABASE_ROLE

-------------------------------------------- -------------------- ------- ----------------

MYPROD READ ONLY WITH APPLY NONE PHYSICAL STANDBY

SQL> select thread#,max(sequence#) from v$archived_log where applied='YES' group by thread#;

THREAD# MAX(SEQUENCE#)

---------- --------------

1 72036

After few log switches at primary then I see everything is normal

Database MRP process Thread Last Sequence Received Last Sequence Applied Difference

--------- ------------ ---------- ---------------------- --------------------- ----------

MYPROD WAIT_FOR_LOG 1 72041 72041 0

Now "MYPROD" database is back to standby mode and working fine, Thanks

About this blog

The contents in my blog are uploaded purely according to my convenience & based on my results and should not be considered professional advice, official documentation, or a substitute for vendor‑supported guidance, viewers may or may not get same results ............Any actions you take based on the content of this blog are strictly at your own risk. I am not responsible for any losses, damages, outages, or issues arising from the use or misuse of the information provided here. All product names, trademarks, and registered trademarks are the property of their respective owners. I thank you everyone for visiting my blog