DOYENSYS Knowledge Portal




We Welcome you to visit our DOYENSYS KNOWLEDGE PORTAL : Doyensys Knowledge Portal




Saturday, October 19, 2019

DBA Tip : RMAN restore when backup file are spread across 2 different mount points


Customers whose databases are of huge size and if the RMAN backup size is about 2TB or more and when there is no enough space to keep all of the backup files in a single mountpoint , we spread them in 2 or 3 different mount points.

Question is " How do we restore when the RMAN backup files are in different location" ?

A simple solution to this would be to create softlinks to the backup pieces from location B in the  location A :


In my case,  the database Rman Backup files are spread across 2 different mount points in the Target ( /u02 & /u03)  and  as we cannot pass two locations to the Rman Duplicate command ... I’ve created softlinks in  /u02 to the backup pieces in /u03 and passed /u02 as a single backup location to RMAN script.

Example:

cd  /u02/backup/location1

ln -s /u03/backup/location2/PROD_df_full_143926_1.bak PROD_df_full_143926_1.bak
ln -s /u03/backup/location2/PROD_df_full_143927_1.bak PROD_df_full_143927_1.bak
ln -s /u03/backup/location2/PROD_df_full_143929_1.bak PROD_df_full_143929_1.bak
ln -s /u03/backup/location2/PROD_df_full_143936_1.bak PROD_df_full_143936_1.bak
ln -s /u03/backup/location2/PROD_df_full_143935_1.bak PROD_df_full_143935_1.bak


Now, It would look like :

cd  /u02/backup/location1
ls -lrt

FINP01_df_full_143925_6.bak
FINP01_df_full_143930_6.bak
FINP01_df_full_143931_4.bak
FINP01_df_full_143931_2.bak
FINP01_df_full_143931_3.bak
FINP01_df_full_143926_1.bak -> /u03/backup/rbackup/FINS01/FINP01_df_full_143926_1.bak
FINP01_df_full_143927_1.bak -> /u03/backup/rbackup/FINS01/FINP01_df_full_143927_1.bak
FINP01_df_full_143929_1.bak -> /u03/backup/rbackup/FINS01/FINP01_df_full_143929_1.bak
FINP01_df_full_143936_1.bak -> /u03/backup/rbackup/FINS01/FINP01_df_full_143936_1.bak
FINP01_df_full_143935_1.bak -> /u03/backup/rbackup/FINS01/FINP01_df_full_143935_1.bak

Now, pass '/u02/backup/location1' as the backup location to RMAN duplicate command :

DUPLICATE TARGET DATABASE to <TARGETDB>  BACKUP LOCATION '/u02/backup/location1' NOFILENAMECHECK ;






Thursday, October 17, 2019

OPatch Conflict Checker

When applying OPatches to our Oracle database, we frequently encounter conflicts with the existing patches in our database  and we raise an SR with Oracle support to get a merge patch to resolve the conflict (also, we want to retain the old patch with which conflict arised.)

We all know this cannot be a Sev1 and it takes time to get a solution . To avoid the wait time for a merge patches through an SR , Oracle has come up with this new self help tool called " OPatch Conflict Checker " - using this we can get a merge patch for Opatch conflicts – without raising a request to Oracle Support .

Follow the below instructions on how to get a merge patch using this "OPatch Conflict Checker" :




Attach the Oracle Home inventory text file (generated by the command "opatch lsinventory" found in $ORACLE_HOME/cfgtoollogs/opatch/lsinv )

Click ‘Analyze for Conflict’ button , you will get the below page :











This conflict was already identified by Oracle and the merged/revised patch was already published as shown below , but with a different release version … which we can find from the patch search page .

Since we wouldn’t know which version of the patch is suitable for us, we need to use this Analyze with Opatch option with base release patch (12.1.0.2)



 






Recreation / Rebuild of Reporting database (for the EBS customers using Shareplex replication)


If you have Reporting database configured for your UAT or Pre-Prod instance , you may have to recreate/re-configure the reporting database associated with them after a database refresh.


Here I have given a detailed list of Steps to re-build your reporting database (replication database) and configure shareplex replication after Primary database refresh (UAT/Pre-Prod) .

Non-EBS users , please follow only the Database related steps .


1
Shutdown shareplex on both SOURCEDB & REPORTING databases.
2
Take a backup of $vardir of Shareplex in both SOURCEDB & REPORTING
3
Take a backup of Existing TNS Entries of SOURCEDB & REPORTING on both the DB nodes before SOURCEDB clone/refresh.
4
Take a backup of existing Pfile along with the complete DBS Folder.
5
Take a backup of existing XML file of REPORTING . (Very important)
6
Comment (#) the Apps RSYNC cronjob on SOURCEDB Apps server.
7
Once the SOURCEDB refresh is complete, Create REPORTING DB as a standby of SOURCEDB and make sure it is in sync until we decide on downtime of SOURCEDB to configure REPORTING & shareplex.
8
Copy the Application Filesystem from SOURCEDB to REPORTING
9
Once the downtime is decided, Shutdown SOURCEDB Apps + DB and do not start until REPORTING & Shareplex are configured.
10
Break the sync and Activate the Standby Database as Read/Write mode.
11
Open the Standby database.
12
Change the Standby database name to REPORTING using NID.
13
Start REPORTING as RAC on src_01 & tgrt_01
14
Make sure only 2 redo log Threads are enabled
15
Perform rest of the DB post clone and Apps clone steps. (as part of post clone do not mask email addresses & do not do any WF steps)
16
Start SOURCEDB DB
17
Run ora_cleanup (oracle@src_01/tgrt_01: cd $proddir/bin # ./ora_cleansp) in both SOURCEDB & REPORTING
18
Drop & Recreate SPLEX schema with SPLEX & SPLEX_TEMP as default Tablespace & Temp tablespace respectively.
19
Cleanup & recreate SPLEX_TEMP tablespace
20
Disable Triggers in REPORTING database (Create a script to disable them from the schemas that are part of replication config)
21
Disable Constraints in REPORTING  database (Create a script to disable them from the schemas that are part of replication config)

Note : Collect Dictionary stats before disabling constraints , to get better performance during disabling
SQL> exec dbms_stats.gather_dictionary_stats(options => 'GATHER');
22
Run ora_setup (exactly as per the attached notes)
23
Create Config using the existing config file itself on SP Source  
24
on SP Source, Disable the Sequence replication : set param  SP_OCT_REPLICATE_SEQUENCES 0
25
on SP Source , Enable DDL replication : set param SP_OCT_REPLICATE_ALL_DDL 1
26
on SP Source , increase Thread count : set param SP_OCF_THREAD_COUNT 9
27
Start Shareplex services on both Source & Target
28
Verify Config :  verify config REP_CONFIG_12JUL19 (config name may vary)
29
Activate Config : activate config REP_CONFIG_12JUL19 (config name may vary)
30
Start SOURCEDB Apps
31
Run Autoconfig on REPORTING Apps
32
Start REPORTING Apps
33
Enable Apps Rsync cronjob on SOURCEDB
34
Convert REPORTING Apps to Read Only



                 



Shareplex Setup/Configuration for Oracle Replication

Steps contributed by  Hemanth & Rao:
----------------------------------------------


Prerequisites for Shareplex Setup on Oracle database :

1.     Create tablespace splex with 2GB space with autoextend-on in both source & Target    databases.
2.       Ensure Archive log is enabled in Source and target databases.
3.    Enable supplemental Logging at Database Level in the Source database :
 <  ALTER DATABASE ADD SUPPLEMENTAL LOG DATA > - This enables Minimal Database Level Logging
NoteSupplemental logging places additional column data into the redo log file whenever an UPDATE operation is performed. At the least, minimal database-level supplemental logging must be enabled for any Change Data Capture in the source database.


Source Setup :


1. Change Directory to <shareplex proddir>/bin
    cd /u03/db/shareplex__01/proddir
2. Ensure to set source database env 
3. Run ./ora_setup

a. When prompted asking ,Will the SharePlex install be using a BEQUEATH connection?, Enter n
b. When prompted asking , Are you configuring SharePlex for an AWS RDS Database?, Enter n
c.   When Prompted to enter Enter the TNS alias for which SharePlex should be installed , Enter the Tns name entry of source database
d. When prompted to enter DBA user for source database , enter system user
e. When prompted to enter password for DBA Account , Enter system user password
f. When prompted asking , Would you like to create a new SharePlex user ? , Ensure it is Y and press enter
g.   When prompted to enter username for new shareplex user , enter splex/<shareplex password>
h.   When prompted asking Do you want to enable replication of tables with TDE?, ensure n and press enter
i. When prompted to enter the default tablespace, enter splex
j. When prompted to enter the temporary tablespace , enter temp
k. When prompted to enter the index tablespace , enter splex
l.   When prompted asking Will the current setup for sid: [<source SID>] be used as source (including cases as source for failover or master-master setups)? , Ensure it is Y and press enter
m.   When prompted asking, Do you wish to connect to ASM using BEQUEATH connection?  , Ensure it is Y and press enter
n  . When prompted to enter the asm SID , ensure asm sid is correct, else enter the correct sid
Source Setup stage completed. 


Target Setup :


1. Change Directory to <shareplex proddrr>/bin
        cd  /u03/db/shareplex__01/proddir
2. Ensure to source the target database .env 
3. Run ./ora_setup

a. When prompted asking ,Will the SharePlex install be using a BEQUEATH connection?, Enter n
b. When prompted asking , Are you configuring SharePlex for an AWS RDS Database?, Enter n
c. When Prompted to enter Enter the TNS alias for which SharePlex should be installed , Enter the Tns name entry of target database
d. When prompted to enter DBA user for source database , enter system user
e. When prompted to enter password for DBA Account , Enter system user password
f. When prompted asking , Would you like to create a new SharePlex user ? , Ensure it is Y and press enter
g. When prompted to enter username for new shareplex user , enter splex/<shareplex password>
h. When prompted asking Do you want to enable replication of tables with TDE?, ensure n and press enter
i. When prompted to enter the default tablespace, enter splex
j. When prompted to enter the temporary tablespace , enter SPLEX_TEMP
k. When prompted to enter the index tablespace , enter splex
l. When prompted asking Will the current setup for sid: [<target SID>] be used as source (including cases as source for failover or master-master setups)? , enter n and Hit enter
Target Setup stage completed
                               
4. Disable the Check & Referential Constraints in the Target Database.
5. Disable the Triggers in the Target Database 


Shareplex Startup and shutdown :

Steps to start the shareplex service 
1. Start screen session
2. Change Directory to <shareplex proddrr>/bin
cd /u03/db/shareplex/proddir
3. Ensure to set source database env or target database env 
4. Run ./sp_cop -u2100 &

Steps to stop the shareplex service
1. Start screen session
2. Change Directory to <shareplex proddrr>/bin
cd /u03/db/shareplex/proddir
3. Ensure to set source database env or target database env 
4. Run ./sp_ctrl
5. Enter shutdown command in sp_ctrl prompt : sp_ctrl > shutdown





Shareplex Installation for Oracle Replication

Steps contributed by  Hemanth & Rao:
----------------------------------------------

Installation on Source Server :

oracle@src_01:~ $ . ./SOURCEDB_12c.env

oracle@src_01:/u03/db $ ls -lrt
-rwxr-xr-x 1 oracle oinstall 151278802 Sep 10 12:50 SharePlex-9.1.2-b18-oracle120-rh-40-amd64-m64.tpm

oracle@src_01:/u03/db $ ./SharePlex-9.1.2-b18-oracle120-rh-40-amd64-m64.tpm
Unpacking..................................................................
  ..........................................................................
  ..........................................................................
  .....

SharePlex installation program:
    SharePlex Version: 9.1.2
    Build platform: rh-40-amd64
    Target platform: rh-40-amd64

Please enter the product directory location? /u03/db/shareplex_src_01/proddir
Please enter the variable data directory location? /u02/db/shareplex_src_01/vardir2100
Please specify the SharePlex Admin group (select a number):
1. [oinstall]
2. ebsprod
3. dba
?  1
Please wait while the installer obtains Oracle information ..
Please enter the ORACLE_SID that corresponds to this installation? [SOURCEDB]
Please enter the ORACLE_HOME directory that corresponds to this ORACLE_SID? [/u01/app/db_1/XXXS01/12.1.0.2]
Please enter the TCP/IP port number for SharePlex communications? [2100]

Preparing to install SharePlex for Oracle v.9.1.2:
    User:                     oracle
    Admin Group:              oinstall
    Product Directory:        /u03/db/shareplex_src_01/proddir
    Variable Data Directory:  /u02/db/shareplex_src_01/vardir2100
    ORACLE_SID:               SOURCEDB
    ORACLE_HOME:              /u01/app/db_1/xxxS01/12.1.0.2

Proceed with installation? [yes]
Installing ................................................................
  .........................................................................
  .........................................................................
  ......................................................................
Setting file ownerships ...................................................
  .........................................................................
  .........................................................................
  .........................................................................
  ..........
Setting file permissions ..................................................
  .........................................................................
  .........................................................................
  .........................................................................
  ...........
Do you have a valid SharePlex v. 9.1.2 license? [yes]
Please enter the License key? XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
Please enter the customer name associated with this license key? XYZ Inc.

SharePlex v. 9.1.2 license validation successful:
    Customer Name:      XYZ Inc.
    License Key:        XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
    Product Name:       SharePlex for Oracle - RAC
    License Key Type:   "Term Key"
    Expires:            at Midnight of Jul 31, 2023

NOTE: You can upgrade this license key or add license keys for additional machines
      by executing utility /u03/db/shareplex_src_01/proddir/install/splex_add_key.

Installation log saved to: /home/oracle/.shareplex/INSTALL-SharePlex-9.1.2-1810011844.log
SharePlex for Oracle v.9.1.2 installation successful.






Installation on Target Server :


oracle@trgt_01:~ $ . ./TARGETDB_trgt_01.env

oracle@trgt_01:/u03/db $ ./SharePlex-9.1.2-b18-oracle120-rh-40-amd64-m64.tpm
Unpacking ..................................................................
  ..........................................................................
  ..........................................................................
  ......


SharePlex installation program:
    SharePlex Version: 9.1.2
    Build platform: rh-40-amd64
    Target platform: rh-40-amd64

Please enter the product directory location? /u03/db/shareplex_trgt_01/proddir
Please enter the variable data directory location? /u02/db/shareplex_trgt_01/vardir2100
Please specify the SharePlex Admin group (select a number):
1. [oinstall]
2. ebsprod
3. dba
?  1
Please wait while the installer obtains Oracle information ..
Please enter the ORACLE_SID that corresponds to this installation? [TARGETDB]
Please enter the ORACLE_HOME directory that corresponds to this ORACLE_SID? [/u01/app/db_1/12.1.0.2]
Please enter the TCP/IP port number for SharePlex communications? [2100]

Preparing to install SharePlex for Oracle v.9.1.2:
    User:                     oracle
    Admin Group:              oinstall
    Product Directory:        /u03/db/shareplex_trgt_01/proddir
    Variable Data Directory:  /u02/db/shareplex_trgt_01/vardir2100
    ORACLE_SID:               TARGETDB
    ORACLE_HOME:              /u01/app/db_1/12.1.0.2

Proceed with installation? [yes]
Installing ................................................................
  .........................................................................
  .........................................................................
Setting file ownerships ...................................................
  .........................................................................
  .......
Setting file permissions ..................................................
  .........................................................................
  .........................................................................
  ...........
Do you have a valid SharePlex v. 9.1.2 license? [yes]
Please enter the License key? XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
Please enter the customer name associated with this license key? XYZ Inc.

SharePlex v. 9.1.2 license validation successful:
    Customer Name:      XYZ Inc.
    License Key:        XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
    Product Name:       SharePlex for Oracle - RAC
    License Key Type:   "Term Key"
    Expires:            at Midnight of Jul 31, 2021

NOTE: You can upgrade this license key or add license keys for additional machines
      by executing utility /u03/db/shareplex_trgt_01/proddir/install/splex_add_key.

Installation log saved to: /home/oracle/.shareplex/INSTALL-SharePlex-9.1.2-1810011859.log
SharePlex for Oracle v.9.1.2 installation successful.

Wednesday, October 16, 2019

Oracle Critical Patch Update for October 2019



Oracle has released the Critical Patch Update for October 2019. Please refer to below link . DB and EBS Patch do available.

 

Tuesday, October 15, 2019

Open Ebiz R12 in Google Chrome

Scenario:

In order to open oracle e-business in google chrome, there is an option of installing the IE Tab Extension in chrome.


Installation of IE Tab Extension for Google Chrome.

Step 1 : Open Google Chrome Browser.
Step 2 : Check IE Extension is present .
Step 3 : Download IE Tab Extension.
Step 4 : Install the IE Tab Extension in google chrome.
Step 5 : Allow the url in IE Tab extensions using settings.
Step 6 : Now verify Oracle is working in IE Tab in Chrome.

1.  Open Google Chrome Browser.





1 2.       Check IE Extension is present .
-          Navigate to Settings - > Extension . By default it wont be there.



3.       Download IE Tab Extension.

-          Type in IE Tab for Chrome from the Chrome Browser, or copy and paste the following URL in the browser.
-          You will be navigated to the following page where we can see Add to chrome Tab as highlighted.

-          Click on Add Extension

4.       Install the IE Tab Extension in google chrome.
 If required  for first time run the installer. Once done close the Chrome browser and open a new window, You will find the tab for IE like below 



5.       Allow the url in IE Tab extensions using settings.
-          Navigate to setting -> Extensions

-          By default the below mentioned 2 options will be disabled, Just enable as shown below.

6.       Now verify Oracle is working in IE Tab in Chrome.

-          Now Open new Chrome browser and enter the oracle url 





WF Mailer Not able to start with the following error : FRAMEWORK_USER=Unable to make a connection with the user and responsibility

Scenario:

I was trying to restart the WF Container using the following Navigation in 12.1.3

Oracle Application Manager -> Workflow ->Service Components ->Workflow Notification Mailer

When we go inside the container and restarts it failed and shows the following error when we click on the Workflow Notification Mailer.


Error :

Could not handle the control event -> oracle.apps.fnd.cp.gsc.SvcComponentContainerException: Could not start component; performing rollback -> oracle.apps.fnd.cp.gsc.SvcComponentException: Validation failed for the following parameters -> {FRAMEWORK_USER=Unable to make a connection with the user and responsibility}. Parameters were -> {RESET_NLS=N, FROM=Workflow Mailer Production, INBOUND_MAX_RET_EMAIL_SIZE=100, CANCELED=WFMAIL:CANCELED, SEND_UNSOLICITED_WARNING

Solution:

The Error indicates connectivity problem, and the notification mailer will attempt to make a connection as SYSADMIN with the system administrator responsibility. So verify the following:
1. SYSADMIN userid is active and the responsibility System Administrator is Active.
2. Also check whether sysadmin has the default value by :

select * from fnd_user_a  where USER_ID='0' order by 1 desc;

In my case SYSADMIN user was end dated by mistake. Because of that it fails to start the WF mailer. So we removed the enddate and tried restarting and it worked.


Reference:

Workflow Mailer Is Not Coming Up And Agent Listeners In Unavailable State FRAMEWORK_USER=Unable to make a connection with the user and responsibility (Doc ID 2448179.1)

Friday, October 11, 2019

Oracle Open World 2019 presentations are now available for download


Team,

Using below link , you can download Oracle Open World 2019 presentations. Please make use of them and share with your team/friends/colleagues.


 


Cheers,
Narasimharao Karanam
#OOW19 #Oracle #oraclecloud #openworld #presentations