Sunday, June 26, 2022

protect accidental termination of OCI Compute instance and secure your data

 It is very important for any organizations to protect its resources against accidental termination and especially when it comes to compute instance. You not only secure your data but you are also ensuring that the services which are running on the compute instance are always up. In the cloud world, creation and maintenance of the resources have been made much easier. Within a click of button, one can create the resources and even terminate them easily. What if, unintentionally someone terminate the instance which was not supposed to be? All Data Gone????

Normally, when we create users in OCI, we give them some privileges through which they perform some tasks. These privileges comes through Policies. As a best practice, we should always follow the principle of giving least privileges. If the privileges are wide open, then in that case users can easily go and terminate an instance accidentally.



In the above case, user having the privileges can easily terminate, stop, reboot the instance. Basically it can perform all the instance management operations. The instance management operations are basically categorized as 


In this post, the user should not have privileges to terminate an instance. In my instance, i have a group REST_USERS and all the users except Tenancy Administrator are part of this group. Thus my policy will be like


Allow group REST_USERS to manage instance-family in tenancy

 where request.permission!='INSTANCE_DELETE'

Allow group REST_USERS to use volume-family in tenancy

Allow group REST_USERS to use virtual-network-family in tenancy


Now if the user tries to terminate the instance, he/she will get the error as below



This is how we can protect our compute instance from getting accidentally terminated. In this case, only the tenancy administrator can terminate an instance The other instance management operations such as Start, Stop, Reboot can be performed by other users and thus it is not going to hamper the daily operations.


I hope this post will help someone. Till, then happy learning cloud





Monday, June 20, 2022

Golden gate Extract is not starting because of ORA-28374: typed master key not found in wallet

We had an issue where all of a sudden after the DB bounce, extract was not coming up with errors ORA-28374: typed master key not found in wallet. This GG environment was acting a downstream mining and redo logs were coming from Source SaaS(Oracle Fusion). The first thing i did, is to go through the alert log. Found the trace file from the alert log and got the below warnings being reported.



kcrf_decrypt_redokey: dumping redo key 1344d0d94ad0a8347de7dda9e709dbff ======>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> DB is looking for this key as per trace file

kcrf_decrypt_redokey: dumping mkloc 1 mkid in log header 2f4219da61094fefbfc613ba4b51407a ======>>>>>>>>>> >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> DB is looking for this key as per trace file

kcrf_decrypt_redokey: dumping active kcbtsemkloc 1, kcbtsemkid 8655dd60aa2c4f6ebfd407dbecfd5e5b ======>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> DB is looking for this key as per trace file


Now from the information above, how can we know which keys does the DB is referring to


How we can decrypt it:-


SQL> select utl_raw.cast_to_varchar2( utl_encode.base64_encode('01'||'2f4219da61094fefbfc613ba4b51407a')) from dual;


UTL_RAW.CAST_TO_VARCHAR2(UTL_ENCODE.BASE64_ENCODE('01'||'2F4219DA61094FEFBFC613B

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

AS9CGdphCU/vv8YTuktRQHo=


Run the below command to check existing keys present in wallet

SQL> select key_id from v$encryption_keys;

KEY_ID
------------------------------------------------------------------------------
ARBCnkv3lU/qv7MNJhC2AeAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AYZV3WCqLE9uv9QH2+z9XlsAAAAAAAAAAAAAAAAAAAAAAAAAAAAA

Thus, we can see that one key is missing and this is the reason why extract is not coming up. So, we got the keys from Source and imported it to our target database using the below command

ADMINISTER KEY MANAGEMENT IMPORT KEYS WITH SECRET "***" FROM 'path where key file have been placed' FORCE KEYSTORE IDENTIFIED BY "YOUR DB WALLET PASSWORD" with backup;


Upon importing the key to our wallet, we were able to successfully start the extract


Hope it helps someone

Tuesday, June 7, 2022

Monitor oci compute instance using free audit service and perform analysis on a systematic way

There is a service in OCI known as Audit service which records calls to all supported Oracle Cloud Infrastructure public application programming interface (API) endpoints as log events. By default, Audit logs are retained for 365 days. Retention period is a tenancy-level setting. The value of the retention period setting affects all regions and all compartments. The retention period cannot be changed. Currently, all services support logging by Audit.


Information in the logs includes the following:


Time the API activity occurred

Source of the activity

Target of the activity

Type of action

Type of response


In this post, we will stop the instance and through audit service, we will find the details on who did what. 


We will open the compute instance and choose the Audit option






Click on filter options



Once we click on apply, the results will be displayed in below format



Now filter with "POST" 






Click on the drop down option on the right most side,




Expand the "StatusChange" option




Now to know who did it, click on the identity option




Thus in an environment, where you have many users accessing console and doing the activities, the OCI Audit Service helps us to know the details in a much granular manner. I hope this post will be helpful for someone. Till then, happy learning cloud








Wednesday, May 18, 2022

ebs 12.2 clone fails in the FMW pre-requisite section

The goal of this post is to give details on the issue which we had encountered when we were trying to do a clone for EBS 12.2.6 to target running on OEL 8.  Source was running on OEL7. As part of cloning steps, we need to run adcfgclone.pl appsTier dualfs for configuring the file system. As part of the process, the steps were failing on the pre checks for FMW. It was erroring out with


=====================================


Expected result: One of oracle-7,redhat-7,redhat-6,oracle-6,oracle-5,enterprise-5.4,enterprise-4,enterprise-5,redhat-5.4,redhat-4,redhat-5,SuSE-10,SuSE-11


Actual Result: oracle-8.5


Check complete. The overall result of this check is: Failed <<<<


=======================================


OS being used was OEL8 and thus it shouldn't have been an issue. Upon deep dive into the configuration files, i found that the pre checks for FMW use the file refhost.xml which basically resides under EBSapps/comn/clone/prereq/webtier/Scripts/prereq/linux64. So as a workaround, i added the copied the contents for oracle-7 and made it look like as oracle-8 and pasted it at the top of the file.


Then i reran the adcfgclone again and now it passed the prechecks for FMW. The intention of the post is just a way for workaround and it should not be a solution.



Saturday, January 15, 2022

IMPDP for one single table in multitenant database in a different custom schema

Few days back, we got a request to import one table to our PDB. The main point to note is import will run through sys user and it has to be imported under a custom schema. The source was 19c and target DB version was 12c. The dump file along with log files were shared with us. Now, the environment where we need to do the import consists of the DB running on VM DB system on OCI. It consists of one root and one pluggable container. 



                Image source:https://docs.oracle.com/database/121/CNCPT/cdbovrvw.htm#CNCPT89236



SQL*Plus: Release 12.1.0.2.0 Production on Sat Jan 15 11:36:36 2022


Copyright (c) 1982, 2014, Oracle.  All rights reserved.



Connected to:

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

With the Partitioning, Real Application Clusters, Automatic Storage Management and Real Application Testing options


SQL> show pdbs;


    CON_ID CON_NAME                       OPEN MODE  RESTRICTED

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

         2 PDB$SEED                       READ ONLY  NO

         3 RPPDB                       READ WRITE NO

SQL>


The table to be imported already exist in our system and it was owned by an another schema FUSION. We created a par file, the contents of which are given below


cat impdp.par

tables=FUSION."table name"

logfile=impdp_tables_expdp.log

parallel=4

encryption_password=****

dumpfile=tables-expdp-%U.dmp

directory=IMPORT

job_name=job_NDE

table_exists_action=truncate


Now using the par file, run the IMPDP command


impdp \"sys/*****@RPPDB as sysdba\" parfile=impdp.par


The post was an small example on how we can do a import of one single table in a multitenant Database. Hope you find it useful. Till then happy learning.

 




Friday, January 7, 2022

FAILED: enableSSH adop ebs 12.2

We recently upgraded our EBS 12.1.3 to EBS 12.2.10 and multi node deployment was done. One was secondary internal nodes and 2 were external DMZ nodes. SSH was manually enabled using native authentication by using the command ssh-keygen. And then configurations were done by copying the primary application tier backups.  For details on how to setup the SSH, you can follow the EBS 12.2 documentation https://docs.oracle.com/cd/E26401_01/doc.122/e22954/T202991T531065.htm#adsshsetup

Now, in order to sync the patch file system and run file system for all the nodes, we ran the command adop phase=fs_clone in the primary node. Ideally it should have done the ssh to all the nodes and perform the sync. But in our case, inspite of having the manual SSH setup done earlier, adop was not working. It gave an error during validation phase:-SEVERE: com.jcraft.jsch.JSchException: Algorithm negotiation fail.

  at com.jcraft.jsch.Session.receive_kexinit(Session.java:510)

Now in EBS 12.2, either you can setup manual SSH or use the perl script.

 perl $AD_TOP/patch/115/bin/txkRunSSHSetup.pl enablessh -contextfile=<Context_file> -hosts=<node1>,<node2>.

We cannot club both of them together. If manual setup has been done, then no need to run the perl script. For all the other application nodes, the bash profile was pointing to EBS 12.1.3 environment and thus adop SSH validation was failing. We removed that part first from the bash profile and repointed it to use EBS 12.2. Adop still failed. Then we remember, someone changed the application OS password after the SSH keys generation. The EBS 12.2 maintenance guide suggests that if the OS password is changed, we need to regenerate the keys again. Then copy the .pub to the authorized_keys file in all the other nodes. Upon doing the changes again, adop phase=fs_clone validation worked fine and rest patching cycle went through.

Through this post, i wanted to highlight my experience. Hope it helps someone.

References:-https://docs.oracle.com/cd/E26401_01/doc.122/e22954/T202991T531065.htm#adsshsetup

Sunday, December 19, 2021

important considerations for EBS database upgrade to 19C

 Nowadays, there is a huge demand for EBS DB upgrade to 19c because 19C database is the latest long term release. You can refer the below diagram on the Database releases and Support Timelines.




I did back to back EBS DB upgrade to 19c which you can refer in my previous posts https://samappsdba.blogspot.com/2020/12/upgrade-EBS-12.2-database-12c12.1.0.2-to-19c19.3-Part-1.html. Now, the purpose of this post is to make everyone aware of the different sort of issues which you can encounter while doing the upgrade and also, i have tried my best to provide the solutions. Also, i have  highlighted some basic terminologies which if known, will ease up the upgrade.


1. Source DB will become the PDB.

Your source Database will be converted into PDB and which then will be attached to CDB. So if your source DB sid is PROD, then your PDB will also be PROD.

2. Running hcheck.sql

This mainly reports the data dictionary issues and thus if you see any errors, you should seek an assistance from Oracle Support. This usually takes time, thus it is required to do this step in advance.

3. CDB listener

As part of 19c upgrade and multitenant conversion, the listener should be started with the CDB name.

4. Characterset Issue

While doing the upgrade, please note down the source characterset and make sure while creating the CDB, the same characterset options are choosen. If there are character sets mismatch, then you will get an error while doing the conversion to multitenant DB.

You can use the below query:-

select * from database_properties where PROPERTY_NAME in ('NLS_CHARACTERSET', 'NLS_NCHAR_CHARACTERSET');


 In order to know more about the various issues and their solutions, you can through the  metalink note 19c Database with Oracle E-Business Suite R12 Known Issues & Solutions (Doc ID 2662860.1)
 
I hope you find this note useful. Happy upgrade to 19c.