ORA-15001: diskgroup "DATAC1" does not exist or is not mounted | encountered while duplicate command for standby

     I am creating standby database using duplicate command. i encountered ORA-15001 error for password file modification, but error saying DATAC1 disk group does not exists, this error message is misleading and not recognizable.

After contacting oracle, its identified that its an issue with password file update by oracle.  

RMAN command used:

duplicate target database for standby from active database nofilenamecheck;

Error message:

sql statement: alter system set  control_files =   ''+DATAC1/XYZ/CONTROLFILE/current.274.1174401739'' comment= ''Set by RMAN'' scope=spfile
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
released channel: prmy1
released channel: prmy2
released channel: prmy3
released channel: prmy4
released channel: prmy5
released channel: prmy6
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 07/17/2024 12:37:28
RMAN-05501: aborting duplication of target database
RMAN-03015: error occurred in stored script Memory Script
RMAN-04006: error from auxiliary database: ORA-01993: error writing password file '+DATAC1/XYZ/PASSWORD/pwdxyz.267.1174401509'
ORA-15001: diskgroup "DATAC1" does not exist or is not mounted
Recovery Manager complete.

Solution:

1. Remove existing password file.

2. Recreate password file or copy password file from primary.

3. Run the duplicate command.

Reference to recreate password file is here

No SRLs available for T-1

 

 No SRLs available for T-1     

   This message in standby database alertlog indicates no standby redo logs using by standby database for syncing with primary.

        There will be some situations where standby redo logs will not be used even standby redo logs created in standby side. This causes the sync will happen using archivelogs implies the lag in apply.

This can be due to below:

1.Primary thread number and standby thread number is different.

Example:  

  select group#,THREAD#,sum(bytes/1024/1024),status from v$log group by group#,THREAD#,status;

    GROUP#    THREAD# SUM(BYTES/1024/1024) STATUS

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

         9          1                16384 INACTIVE

        10          1                16384 INACTIVE

         8          1                16384 INACTIVE

        12          1                16384 CURRENT

select a.group#,b.THREAD#, b.bytes/1024/1024/1024, b.status FROM v$logfile a, v$standby_log b WHERE a.group# = b.group#;

    GROUP#    THREAD# B.BYTES/1024/1024/1024 STATUS

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

        20          0                     16 UNASSIGNED

        21          0                     16 UNASSIGNED

        22          0                     16 UNASSIGNED

        23          0                     16 UNASSIGNED

        24          0                     16 UNASSIGNED

you can see primary thread 1 and standby thread 0. Drop the standby redologs in standby side and recreate with correct thread number matching with primary.

2. Standby redo logs might not be available in physical level, you can check in location whether the standby redo logs available in location or not.

    

3.Standby redo logs size is less than the primary redo logs size.

Primary:

select group#,THREAD#,sum(bytes/1024/1024),status from v$log group by group#,THREAD#,status;

    GROUP#    THREAD# SUM(BYTES/1024/1024) STATUS

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

         9          1                16384 INACTIVE

        10          1                16384 INACTIVE

         8          1                16384 INACTIVE

        12          1                16384 CURRENT

Standby:

select a.group#,b.THREAD#, a.member, b.bytes/1024/1024/1024, b.status FROM v$logfile a, v$standby_log b WHERE a.group# = b.group#;

    GROUP#    THREAD# MEMBER                                                       B.BYTES/1024/1024/1024 STATUS

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

         4          1 +RECO/PSTRPRD_XS6_AMS/ONLINELOG/group_4.259.1160134445                            1 UNASSIGNED

         5          1 +RECO/PSTRPRD_XS6_AMS/ONLINELOG/group_5.260.1160134447                            1 UNASSIGNED

         6          1 +RECO/PSTRPRD_XS6_AMS/ONLINELOG/group_6.261.1160134451                            1 UNASSIGNED

         7          1 +RECO/PSTRPRD_XS6_AMS/ONLINELOG/group_7.262.1160134455                            1 UNASSIGNED

    In primary redo size is 16G but in standby redo logs in standby side is 1G, you can create new standby redologs with correct size and drop old standby redo.

reference: https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=34273136522105&id=2747195.1&_afrWindowMode=0&_adf.ctrl-state=1cxt9fg1sq_151

4. Miss configuration of DEST_1 parameter.

 No need to specify db_unique_name and valid_for for local archiving, you can use below command.

 Alter system set log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST'; 

reference: https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=33554000415773&id=2864452.1&_afrWindowMode=0&_adf.ctrl-state=1cxt9fg1sq_102

Error During active duplicate command RMAN-06136: Oracle error from auxiliary database: ORA-00600: internal error code, arguments: [krbi_init_fra_metadata_not_backup_ctl]

 Hi,

I got error while running active duplicate command from physical standby database.

RMAN-05501: aborting duplication of target database
RMAN-06136: Oracle error from auxiliary database: ORA-00600: internal error code, arguments: [krbi_init_fra_metadata_not_backup_ctl], [], [], [], [], [], [], [], [],

Fix:

This is some issue( I DONT KNOW) with recovery area, it could be a bug since oracle 11g.

To fix it, just need to disable the recovery area in auxiliary instance before starting the duplicate command, means remove the db_recovery_file_dest and db_recovery_file_dest_size parameters from pfile and start in no mount and start duplicate command.

Oracle 23c New features for DBAs

 Lets see new features introduced in 23c which helpful for DBAs.

1. Schema Privileges: 

I am waiting for this feature since i started working as DBA, Thanks to oracle, finally its added in 23c.

Prior to 23c, if you want to grant access on all objects in a schema to another schema, its a long route, we have to generate grant commands on all objects using dynamic sql  and run the grant commands. if new object created in the schema, we need to grant the privs again on new object.

Now with this Schema privileges allow us to simplify grant a user or role needs privileges on all objects in a schema.

If any new object added in the schema, no need to add privs again for that object. it will have access by default

Ex: 

GRANT SELECT ANY TABLE ON SCHEMA appusr TO devusr;

Dictionary views to see schema privileges are DBA_SCHEMA_PRIVS,ROLE_SCHEMA_PRIVS, USER_SCHEMA_PRIVS, SESSION_SCHEMA_PRIVS

 

2. Read-Only PDB Users:  

Oracle database 23c allows us to make PDB users read-only, which makes a connected session act like the database is opened in read-only mode, preventing the session from performing write operations.

3. DB_DEVELOPER_ROLE Role: 

Oracle database 23c introduced a new DB_DEVELOPER_ROLE role, to provide the basic roles and privileges Oracle believe are necessary for a database developer.

So now we don't need to create a role assign bulk of privs to that role and grant that role to the user, simply DBA can grant DB_DEVELOPER_ROLE role to developer after user creation. 

ORA-19809:limit exceeded for recovery files | while recovering database to another server

I dont know how many DBAs will face this issue, but i wanted to write this in my blog.

If you use source pfile/spfile for creating new database, you will not get this issue.

I wanted to create a database in a server using backup of a database that running in another server.

Restore completed successfully but while starting recover database its failed saying  " limit exceeded for recovery files" as below.

Oracle Error:
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1: '+DATA/TEST/DATAFILE/system.1271.1160540667'
released channel: c1
released channel: c2
released channel: c3
released channel: c4
released channel: c5
released channel: c6
released channel: c7
released channel: c8
released channel: c9
released channel: c10
released channel: c11
released channel: c12
released channel: c13
released channel: c14
released channel: c15
released channel: c16
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 02/11/2024 07:40:15
ORA-19870: error while restoring backup piece AL_AUTO_21_12_2023_080100_arc_TEST_115137294_iondfe_131900_1_1_20231221_1156147280_set131900
ORA-19809: limit exceeded for recovery files
ORA-19804: cannot reclaim 3999268864 bytes disk space from 2190433320960 bytes limit

I checked, recovery area does not have any data in it, but if I use below query to check recovery area usage it shows 100% used. 

And if i check list archivelog all, it lists many archivelogs.

So its obvious that this info is taken from restored control file.

Now why its showing recovery area is filled, because in source database recovery area was 25TB, but for new database i am creating i have given only 2TB. So oracle calculating databased on info available in controlfile, it has list of archivelogs and its size even though physically does not exists.

As a fix, to make oracle to consider there is space in reco, i modified db_recovery_file_dest_size to 25TB( though RECO disk group does not have that much space).

alter system set db_recovery_file_dest_size=25TB scope=both;

Recover successful.

ORA-02085: Database Link LINK_NAME Connects To TARGET_DB

 This issue may come in multitenant database even GLOBAL_NAMES=false in container level.

we can fix this issue by setting the same parameter in PDB level, login to the PDB and set as below.

sqlplus / as sysdba

alter session set container=<pdb name>;

alter system set GLOBAL_NAMES=false;

create clone database from standby database using Duplicate command

 

If we have a method to create clone database from standby database, its bliss for DBAs, they can perform production database clone without impacting production database.

 

In this article we can see how we can create a clone database from standby database using DUPLICATE command.

Source is standby - PRODDB

target is new clone server - TESTDB

 

Steps:

1.     Network configuration between standby server and new clone server

• configure static listener in target so that database will connect as sysdba in nomount state without any blocking

 SID_LIST_LISTENER=

  (SID_LIST=

    (SID_DESC=

      (SID_NAME= TESTDB)

      (GLOBAL_DBNAME = TESTDB)

      (ORACLE_HOME= /u01/app/oracle/product/19c/db1)

      )

     )

 

·         Enter source and target tns entries in both sides.

TESTDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 9.9.9.9)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = TESTDB)
      (UR=A) 
    )
  )

 

PRODDB =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 1.1.1.1)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = PRODDB )
      (UR=A)
    )
  )

2.     Create pfile for TESTDB  by taking reference from standby pfile and start in nomount.

3.     Test connectivity.

$rman target sys/passwd@proddb auxiliary sys/passwd@testdb

4.     Stop arch deletion scripts in standby side if configured any,  this is required for retaining archivelogs required for recovery in target side.

 

5.     Use below script to create clone database. In this script we are using FS /oradata, where as source database uses ASM

RUN

{

SET NEWNAME FOR DATABASE TO '/oradata/RMVL/datafiles/%b';

DUPLICATE TARGET DATABASE TO 'RMVL' from active database

  LOGFILE

    GROUP 1 ('/oradata/RMVL/datafiles/redo01a.log') SIZE 4096M REUSE,

    GROUP 2 ('/oradata/RMVL/datafiles/redo02a.log') SIZE 4096M REUSE,

        GROUP 3 ('/oradata/RMVL/datafiles/redo03a.log') SIZE 4096M REUSE;

}

 

 

That’s it, database will open in read write mode after this scripts completed successfully.

 

Happy Cloning 😊

How to apply OJVM patch without down time of database in RAC

In RAC environment, we can do any patch without down time if we follow rolling method(node by node).

We dont required to bring down database for running datapatch, so no down time here as well.

But for OJVM patch, its required to run in upgrade mode, hence DB restart required which is a bad news for RAC environments.

But we have a workaround to run datapatch without putting database in upgrade mode, i.e using   -skip_upgrade_check

./datapatch -verbose -skip_upgrade_check

DBAs will face challenge to apply OJVM patch as it required to put database in upgrade mode to run datapatch.

This way DBA can apply all patches with Zero down time in RAC environments.

Finding OEM urls including weblogic console url.

 OEM urls will be stored in setupinfo.txt file under OMS_HOME/install.

cat /u01/app/oracle/middleware/install/setupinfo.txt

The following is output shows the contents of the file.

Use the following URL to access:

        1. Enterprise Manager Cloud Control URL: https://abcdhost1:7803/em

        2. Admin Server URL: https://abcdhost1:7102/console

        3. BI Publisher URL: https://abcdhost1:9803/xmlpserver/servlet/home

The following details need to be provided while installing an additional OMS:

        1. Admin Server Host Name: abcdhost1

        2. Admin Server Port: 7102

You can find the details on ports used by this deployment at : /u01/app/oracle/middleware/install/portlist.ini

How to find weblogic password in OEM

 Finding Existing weblogic password in OEM

    - To get the existing weblogic's password, please do the below:

1. Weblogic's password is stored in encrypted form in 'password' field in the file -

  /u01/app/oracle/gc_inst/user_projects/domains/GCDomain/servers/EMGC_ADMINSERVER/security/boot.properties. Get the encrypted password from it.

cat boot.properties

# Generated by Configuration Wizard on Sun Mar 29 13:21:08 CEST 2020

username={AES}i3XAY7iKtI9kZ2sd6jqu9nKnUoiBAXXYnua7qQaQYWMUxQ=

password={AES}A1W4HlGanBCEFqLyNpdzgRR16V/ls6EIdptRjg5Kn2/YU0=

2. Run the below commands to get the plain password from encrypted format :

cd /opt/oracle/gc_inst/user_projects/domains/GCDomain/bin

. ./setDomainEnv.sh

3.  Now you will be under /u01/app/oracle/gc_inst/user_projects/domains/GCDomain 

Create a java file to convert the password to text format.

vi recoverpassword.java

public class recoverpassword {

 public static void main(String[] args)

 {

  System.out.println(

  new weblogic.security.internal.encryption.ClearOrEncryptedService(

  weblogic.security.internal.SerializedSystemIni.getEncryptionService(args[0]

   )).decrypt(args[1]));

  }

}

4. compile the Java script.

javac recoverpassword.java

5. Replace the encrypted password find in step 1 in below command and run, it displays the password in text format.

java -cp $CLASSPATH:. recoverpassword /orasoft/oracle/gc_inst/user_projects/domains/GCDomain {AES}A1W4HlGanBCEFqLyNpdzgRR16V/ls6EIdptRjg5Kn2/YU0=

Opoldrgx123

Above is the password in text format.

same method you can follow if you want to know the username.

ORA-28759: failure to open file

If any program or query is failed with error ORA-28759, its should be access/permission issue to the wallets location in database server.

Ex:  In my database there is DB link which link to other schema, if i check the db link test i got the below error.

SQL> select count(*) from tab@TEST_DBL;

select count(*) from tab@TEST_DBL

                         *

ERROR at line 1:

ORA-28759: failure to open file

I find the tnsping is not working and got below error.

TNS-12560: TNS:protocol adapter error

i find there is permission issue to wallet location even directory owner is oracle

oracle@labserver:[DB->TEST]$cd /home/oracle/wallet

-bash: cd: /home/oracle/wallet: Permission denied

i check the permission is not sufficient.

drw-r----- 2 oracle oinstall  113 Nov  5 02:00 wallet

Changed to 755 to oracle and got access to directory and issue resolved. 

Like this for any reason if wallet is configured in listener/sqlnet.ora and does not have access wallet directory for oracle user then this issue arise.

Fix by fixing access issue to wallet location.

How to disable Transparent HugePages

Disabling Transparent HugePages. 

1. check the status

In RHEL

cat /sys/kernel/mm/redhat_transparent_hugepage/enabled

in other OS

cat /sys/kernel/mm/transparent_hugepage/enabled

Output:

[always] madvise never

Its highlighted for always, so Transparent HugePages are enabled.

2. disable the transparent_hugepage.

To disable this modify/add line transparent_hugepage=never

as below:

#cat /etc/default/grub

GRUB_TIMEOUT=5

GRUB_DISTRIBUTOR="$(sed 's, release .*$,,g' /etc/system-release)"

GRUB_DEFAULT=saved

GRUB_DISABLE_SUBMENU=true

GRUB_TERMINAL_OUTPUT="console"

GRUB_CMDLINE_LINUX="console=tty1 console=ttyS0 earlyprintk=ttyS0 rootdelay=300 transparent_hugepage=never"

GRUB_DISABLE_RECOVERY="true"

3. Run below command to make recreate file grub

 #grub2-mkconfig -o /boot/grub2/grub.cfg

4. Restart the server.

5. verification after restart the server.

After modification output will be as below, never  is highlighted, so its disabled.

#cat /sys/kernel/mm/transparent_hugepage/enabled

always madvise [never]

ORA-27102: out of memory Linux-x86_64 Error: 28: No space left on device

 ORA-27102: out of memory Linux-x86_64 Error: 28: No space left on device

When i try to increase sga_target from 50GB to 60GB i got above error.

the reason is shmall value is not configured accordingly for the SGA size.

[root@test_db_LAB ~]$  cat /etc/sysctl.d/97-oracle-database-sysctl.conf|grep  kernel.shmmni

kernel.shmmni = 4096

[root@test_db_LAB ~]$  cat /etc/sysctl.d/97-oracle-database-sysctl.conf|grep  kernel.shmall

kernel.shmall = 23592960

[root@test_db_LAB ~]$  cat /etc/sysctl.d/97-oracle-database-sysctl.conf|grep  kernel.shmmax

kernel.shmmax = 105374182400

Calculation for kernel.shmall  should be (SGA in bytes)/4096(kernel.shmmni)  = 15728640

as root user, set value 15728640 for shmall in file /etc/sysctl.d/97-oracle-database-sysctl.conf.

as root user, apply the changes. 

/sbin/sysctl -p /etc/sysctl.d/97-oracle-database-sysctl.conf

start the database.

How to generate trace file for a query in oracle database- tkprof creation for a query

 Most of the situation we may need to create a trace file for a query or a session which include PL/SQL block.

We can use below method to create trace file for the sql statements or PL/SQL blocks.

1. Enable tracing in a session where you are going to run a query with high level tracing 12.

ALTER SESSION SET EVENTS '10046 trace name context forever, level 12';

2. we will have number of tracefiles in database diag locaions, to identify the trace file generated by this session, you can set the identifier as below, so that file name includes identifier.

alter session set tracefile_identifier = 'mysessiontrace';

3.  run the query or PL/SQL block.

4. turn off the tracing.

alter session set events '10046 trace name context off';

you can find the trace file generated with name mysessiontrace, search as below.

ls -lrth *mysessiontrace*

If you want to generate tkprof of this trace file. use below 

tkprof orcl_mysessiontrace.90222_53078.trc orcl_mysessiontrace.90222_53078.txt SYS=NO

ORA-39083: Object type TYPE: failed to create with error, ORA-02304: invalid object identifier literal

 Error in import in logfile.

ORA-39083: Object type TYPE:"SCHEMA"."T_DKEG" failed to create with error:

ORA-02304: invalid object identifier literal

Failing sql is:

CREATE EDITIONABLE TYPE "SCHEMA"."T_DKEG"   OID '94C6DE8D33F322F1E0539417F30AF613' force as table of varchar2(32767);

Solution:

Your database have another object with OID already.

select owner,type_oid from dba_types where TYPE_OID='94C6DE8D33F322F1E0539417F30AF613';

 

OWNER           TYPE_OID

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

SCHEMA          94C6DE8D33F322F1E0539417F30AF613

so , while import you have to provide below parameter to avoid this issue. and type will generate now OID whiel import.

TRANSFORM=oid:n

Why in dataguard standby side RFS, LNS, LGWR or ARCn generate large trace file without any errors or issues

Why in dataguard standby side RFS, LNS, LGWR or ARCn generate large trace file without any errors or issues?

My standby database side i observed trace files for rfs process are growing in GBs always.
its due to parameter log_archive_trace value set in instance level in standby side.

if log_archive_trace value set for non zero, the trace files size will grow. resolution is to set it to  zero value.

In my case, i can see the value is 255.

SQL> show parameter log_archive_trace

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_trace                    integer     255

i set it to 0, its fixed the proble,.

SQL> alter system set log_archive_trace=0 scope=both;

System altered.

if you are using DB brocker. set this value also to consistent in instance level and DG brocker level.

DGMGRL> show instance 'ORCL' 'LogArchiveTrace';

  LogArchiveTrace = '255'

DGMGRL> edit instance 'ORCL' SET PROPERTY 'LogArchiveTrace'=0;

Property "LogArchiveTrace" updated

DGMGRL> show instance 'ORCL' 'LogArchiveTrace';

  LogArchiveTrace = '0'

OPatch failed with error code 2

When i check the patch conflict for a patch, got below error.

]$opatch prereq CheckConflictAgainstOHWithDetail -ph ./
Oracle Interim Patch Installer version 12.2.0.1.6
Copyright (c) 2020, Oracle Corporation.  All rights reserved.

PREREQ session

Oracle Home       : /u01/oracle/product/12.2.0.1/dbhome_1
Central Inventory : /u01/oracle/oraInventory
   from           : /u01/oracle/product/12.2.0.1/dbhome_1/oraInst.loc
OPatch version    : 12.2.0.1.6
OUI version       : 12.2.0.1.4
Log file location : /u01oracle/product/12.2.0.1/dbhome_1/cfgtoollogs/opatch/opatch2020-07-11_14-10-25PM_1.log

Invoking prereq "checkconflictagainstohwithdetail"

Prereq "checkConflictAgainstOHWithDetail" is not executed.

The details are:
Unable to create Patch Object.
Exception occured : /u01/oracle/psu/30783641/30886680/etc/config/actions.xml with Version field of the component "delete" in actions file cannot be <null> or empty. Please check patch metadata.

OPatch failed with error code 2

Solution:

its due to OPatch version is not compatible for the patch you are trying to apply. download latest opatch utility from metalink and unzip in ORACLE_HOME location. you can download latest OPatch  here

In my case, Opatch version is 12.2.0.1.6 but in patch read me file mentioned it required 12.2.0.1.19 or later 

after adding new OPatch version issue is resolved.

OMS 13.4 silent installation

Below process describe how to install OEM 13.4

Download OEM software for linux 64bit from OTN  here  download all the files and copy to the server.


Step 1: install  12c or 19c database in the server before going to install OEM software.

step 2: install OEM software software only using below rsp file.

RESPONSEFILE_VERSION=2.2.1.0.0
UNIX_GROUP_NAME=linstall
INVENTORY_LOCATION=/u01/app/oracle
SECURITY_UPDATES_VIA_MYORACLESUPPORT=false
DECLINE_SECURITY_UPDATES=true
INSTALL_UPDATES_SELECTION=skip
ORACLE_MIDDLEWARE_HOME_LOCATION=/u01/app/oracle/middleware
ORACLE_HOSTNAME=omsserver
AGENT_BASE_DIR=/u01/app/oracle/agent
b_upgrade=false
EM_INSTALL_TYPE=NOSEED
CONFIGURATION_TYPE=LATER
CONFIGURE_SHARED_LOCATION_BIP=false

Execute the command after creating rsp file.

./em13300_linux64.bin -silent -responseFile /tmp/install.rsp

If you dont have /tmp space sufficient, use temporary location in another FS as below.

./em13300_linux64.bin -silent -responseFile /tmp/install.rsp -J-Djava.io.tmpdir=/u01/tmp/

run root script.

$MW_HOME/allroot.sh

step 3: configure OEM, create rsp file.

RESPONSEFILE_VERSION=2.2.1.0.0
UNIX_GROUP_NAME=oinstall
INVENTORY_LOCATION=/u01/app/oracle/oraInstall
SECURITY_UPDATES_VIA_MYORACLESUPPORT=false
DECLINE_SECURITY_UPDATES=true
INSTALL_UPDATES_SELECTION=skip
ORACLE_MIDDLEWARE_HOME_LOCATION=/u01/app/oracle/middleware
ORACLE_HOSTNAME=omsserver
AGENT_BASE_DIR=/u01/app/oracle/agent
WLS_ADMIN_SERVER_USERNAME=weblogic
WLS_ADMIN_SERVER_PASSWORD=weblogic123
WLS_ADMIN_SERVER_CONFIRM_PASSWORD=weblogic1
NODE_MANAGER_PASSWORD=nodemgr987
NODE_MANAGER_CONFIRM_PASSWORD=nodemgr987
ORACLE_INSTANCE_HOME_LOCATION=/u01/app/oracle/gc_inst
CONFIGURE_ORACLE_SOFTWARE_LIBRARY=true
SOFTWARE_LIBRARY_LOCATION=/u01/app/oracle/swlib
DATABASE_HOSTNAME=omsserver
LISTENER_PORT=1521
SERVICENAME_OR_SID=EMDB
SYS_PASSWORD=oradbpwd123
SYSMAN_PASSWORD=orapwd123
SYSMAN_CONFIRM_PASSWORD=orapwd123
DEPLOYMENT_SIZE=MEDIUM
AGENT_REGISTRATION_PASSWORD=orapwd123
AGENT_REGISTRATION_CONFIRM_PASSWORD=orapwd123
PLUGIN_SELECTION={}
b_upgrade=false
EM_INSTALL_TYPE=NOSEED
CONFIGURATION_TYPE=ADVANCED
CONFIGURE_SHARED_LOCATION_BIP=false
MANAGEMENT_TABLESPACE_LOCATION=/u01/app/oracle/oradata/mgmt.dbf
CONFIGURATION_DATA_TABLESPACE_LOCATION=/u01/app/oracle/oradata/mgmt_ecm_depot1.dbf
JVM_DIAGNOSTICS_TABLESPACE_LOCATION=/u01/app/oracle/oradata/mgmt_deepdive.dbf

Run below command to configure OEM.

$MW_HOME/sysman/install/ConfigureGC.sh -silent -responseFile /tmp/config.rsp

It take time around 30 min, at the end you will get the links to OEM cloud console.

steps to change sysman password in OEM 13.4

Below are the process to change sysman password in for OEM, in oms server.

$OMS_HOME/emctl stop oms

$OMS_HOME/emctl start oms -admin_only

$OMS_HOME/emctl config oms -change_repos_pwd -use_sys_pwd -sys_pwd <sys password> -new_pwd <new sysman passowd>

$OMS_HOME/emctl stop oms

$OMS_HOME/emctl start oms


No need to change sysman password in database level.

finding queries executed between two timings in oracle 12.2

As DBA we always come accross situation when request comes for queries executed or activities done between two timings. below query works only in enterprise edition and with in awr retention time. modify the timings in the query according to your requirement.


set long 900000
col sql_text for a32767 word_wrapped
COLUMN Sample_Time FOR A25
COLUMN username FOR A20
col MACHINE for a10
COLUMN program FOR A40
COLUMN module FOR A40
SELECT
   sample_time,
   h.MACHINE,
   u.username,
   h.program,
   h.module,
   s.sql_text
FROM
   DBA_HIST_ACTIVE_SESS_HISTORY h,
   DBA_USERS u,
   DBA_HIST_SQLTEXT s
WHERE  sample_time
BETWEEN '10/JUN/2020 08:20:00.000' and '10/JUN/2020 08:30:00.000'
AND
   INSTANCE_NUMBER=1
   AND h.user_id=u.user_id
   AND h.sql_id = s.sql_iD
ORDER BY 1
/