Drop TEMP File in the Oracle Database

select FILE_NAME,TABLESPACE_NAME from dba_temp_files;

 select * from v$tempfile';

SQL>  select FILE_NAME,TABLESPACE_NAME from dba_temp_files;

FILE_NAME                                                                                                                                                               TABLESPACE_NAME

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

/u01/database/data/temp01.dbf                                                                                                                                           TEMP1

/u01/database/data/temp02.dbf                                                                                                                                           TEMP2

/u01/database/data/temp0001.dbf                                                                                                                                         TEMP1

/u01/database/data/temp002.dbf                                                                                                                                          TEMP1

SQL>    alter database tempfile '/u01/database/data/temp002.dbf' drop including datafiles;

Database altered.

SQL>

SQL>

SQL>

SQL>  select FILE_NAME,TABLESPACE_NAME from dba_temp_files;

FILE_NAME                                                                                                                                                               TABLESPACE_NAME

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

/u01/database/data/temp01.dbf                                                                                                                                           TEMP1

/u01/database/data/temp02.dbf                                                                                                                                           TEMP2

/u01/database/data/temp0001.dbf                                                                                                                                         TEMP1

SQL>

UTL_HTTP and SSL (HTTPS) using Oracle Wallets

 

1- Install Wallets in Oracle

In order to make connections to a secured resource, we need to get the necessary certificate.

Using the browser, go to the URL you are attempting to access from PL/SQL.

In this case "https://allotaxi.com.lb/".

Click the lock icon in the URL bar to display the certificate menu and click on the "Connection" tab.

Click the "Certificate information" link and click the "Certification Path" tab on the resulting dialog.

For the root node in the "Certification path", highlight the node and click the "View Certificate" button.

On the resulting dialog, click the "Details" tab and click the "Copy to File..." button to save the certificate information.

On the resulting wizard, do the following.

●     Click the "Next" button on the welcome screen.

●     Select the "wallet (.CER)" option and click the "Next" button. Other formats work, but I've found this to be the most consistent.

●     Enter suitable file name and click the "Next" button.

●     Click the "Finish" button.

2- Create an Oracle Wallet Containing the Certificates

Create a new location to hold the wallet.

$ mkdir -p /u01/app/oracle/admin/12c/wallet

Create a new wallet.

$ orapki wallet create -wallet /u01/app/oracle/admin/12c/wallet -pwd WalletPasswd123 -auto_login

Test Secured Connection.

We are now ready to access the secured resource, but we must provide the UTL_HTTP package with the wallet details so it can make the secured connections. This is done using the UTL_HTTP.SET_WALLET procedure.

SET SERVEROUTPUT ON

EXEC UTL_HTTP.set_wallet('file:/u01/app/oracle/admin/12c/wallet', 'WalletPasswd123');

EXEC show_html_from_url('https://allotaxi.com.lb/');

 

... HTML output removed ...

 

PL/SQL procedure successfully completed.

 

SQL>

3- Authentication

If you are accessing a site that requires authentication, you will need to do one of two things depending on the type of authentication used.

If the site uses basic authentication, simply specify the credentials in the call to SHOW_HTOM_FROM_URL, which will use them in the UTL_HTTP.SET_AUTHENTICATION call.

SET SERVEROUTPUT ON

EXEC UTL_HTTP.set_wallet('file:/u01/app/oracle/admin/12c/wallet', 'WalletPasswd123');

EXEC show_html_from_url('https://allotaxi.com.lb/', 'username', 'password');

 

... HTML output removed ...

 

PL/SQL procedure successfully completed.

 

SQL>

If the page uses digest authentication, then you will need to will need to install the digest_auth_api package.

Test code.

SET SERVEROUTPUT ON

EXEC show_html_from_url('https://allotaxi.com.lb/', 'username', 'password', '/u01/app/oracle/admin/12c/wallet', 'WalletPasswd123');

 

... HTML output removed ...

 

PL/SQL procedure successfully completed.

 

SQL>

4- Firewall port 443 needs to be opened for outbound.

Configure WebLogic Server to Capture Client IP Addresses Behind a Load Balancer

 

Customer Recommended How to Configure WebLogic Server to Capture Client IP Addresses Behind a Load Balancer Using Extended Log Format (Doc ID 1602379.1)

1- Log into your WLS machine, and run the $WL_HOME/server/bin/setWLSEnv.sh script to initialize the necessary environment variables, such as location of java, and javac (compiler) that you will need.

In a text editor, create a file called, for example, "GWXFF.java" with the following:

[oracle@test~]$ cd /u01/install/PANSOFT/abd/scripts/
[oracle@test~]$ vi GWXFF.java
[oracle@test scripts]$ cat GWXFF.java

import weblogic.servlet.logging.CustomELFLogger;
import weblogic.servlet.logging.FormatStringBuffer;
import weblogic.servlet.logging.HttpAccountingInfo;
 
/* This example outputs the X-Forwarded-For
 * field into a custom field called GWXFF
 * */
public class GWXFF implements CustomELFLogger{
 public void logField(HttpAccountingInfo metrics, FormatStringBuffer buff) {
  buff.appendValueOrDash(metrics.getHeader("X-Forwarded-For"));
 }
}

2-Save this file. Notice the relation between the class name and the name of the file.

[oracle@test bin]$ cd /u01/install/APPS/fs2/FMW_Home/wlserver_10.3/server/bin

[oracle@test bin]$ . setWLSEnv.sh

CLASSPATH=/u01/install/APPS/fs2/FMW_Home/patch_wls1036/profiles/default/sys_manifest_classpath/weblogic_patch.jar:/u01/install/APPS/fs2/EBSapps/comn/util/jdk64/lib/tools.jar:/u01/install/APPS/fs2/FMW_Home/wlserver_10.3/server/lib/weblogic_sp.jar:/u01/install/APPS/fs2/FMW_Home/wlserver_10.3/server/lib/weblogic.jar:/u01/install/APPS/fs2/FMW_Home/modules/features/weblogic.server.modules_10.3.6.0.jar:/u01/install/APPS/fs2/FMW_Home/wlserver_10.3/server/lib/webservices.jar:/u01/install/APPS/fs2/FMW_Home/modules/org.apache.ant_1.7.1/lib/ant-all.jar:/u01/install/APPS/fs2/FMW_Home/modules/net.sf.antcontrib_1.1.0.0_1-0b2/lib/ant-contrib.jar:/u01/install/APPS/fs2/EBSapps/comn/util/jdk32/lib/dt.jar:/u01/install/APPS/fs2/EBSapps/comn/util/jdk32/lib/tools.jar:/u01/install/APPS/fs2/EBSapps/comn/util/jdk32/jre/lib/rt.jar:/u01/install/APPS/fs2/EBSapps/comn/shared-libs/ebs-3rdparty/WEB-INF/lib/ebs3rdpartyManifest.jar:/u01/install/APPS/fs2/FMW_Home/Oracle_EBS-app1/shared-libs/ebs-fmw/WEB-INF/lib/ebsFMWManifest.jar:/u01/install/APPS/fs2/FMW_Home/Oracle_EBS-app1/shared-libs/ebs-appsborg/WEB-INF/lib/ebsAppsborgManifest.jar:/u01/install/APPS/fs2/FMW_Home/oracle_common/modules/oracle.uix_11.1.1/uix2.jar:/u01/install/APPS/fs2/EBSapps/comn/java/classes:/u01/install/APPS/fs2/EBSapps/10.1.2/forms/java:/u01/install/APPS/fs2/EBSapps/10.1.2/forms/java/frmall.jar:/u01/install/APPS/fs2/EBSapps/10.1.2/jlib/ewt3.jar:/u01/install/APPS/fs2/EBSapps/10.1.2/j2ee/OC4J_BI_Forms/applications/formsapp/formsweb/WEB-INF/lib/frmsrv.jar

 

PATH=/u01/install/APPS/fs2/FMW_Home/wlserver_10.3/server/bin:/u01/install/APPS/fs2/FMW_Home/modules/org.apache.ant_1.7.1/bin:/u01/install/APPS/fs2/EBSapps/comn/util/jdk64/jre/bin:/u01/install/APPS/fs2/EBSapps/comn/util/jdk64/bin:/u01/install/APPS/fs_ne/EBSapps/appl/ad/bin:/u01/install/APPS/fs2/FMW_Home/modules/org.apache.ant_1.7.1/bin:/u01/install/APPS/fs2/inst/apps/QA_ebsqaapp01/admin/scripts:/u01/install/APPS/fs2/FMW_Home/webtier/perl/bin:/u01/install/APPS/fs2/EBSapps/10.1.2/bin:/u01/install/APPS/fs2/EBSapps/appl/fnd/12.0.0/bin:/u01/install/APPS/fs2/EBSapps/appl/ad/12.0.0/bin:/u01/install/APPS/fs2/EBSapps/comn/util/jdk32/jre/bin:/u01/install/APPS/fs2/EBSapps/comn/util/jdk32/bin::/u01/install/APPS/fs2/EBSapps/10.1.2/OPatch:/u01/install/APPS/fs2/EBSapps/10.1.2/bin:/usr/bin:/usr/sbin:/u01/install/APPS/fs2/EBSapps/comn/util/jdk32/jre/bin:/usr/lib64/qt-3.3/bin:/usr/local/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/home/oracle/.local/bin:/home/oracle/bin

 

Your environment has been set.

3- Compile this file as follows

[oracle@test scripts]$ javac GWXFF.java

You will now have a file called GWXFF.class.

[oracle@test scripts]$ ll

-rw-r-----. 1 oracle oinstall  675 Jun 11 11:29 GWXFF.class

-rw-r-----. 1 oracle oinstall  441 Jun 11 11:29 GWXFF.java

4- Next, create a jar package out of the GWXFF.class file.

[oracle@test scripts]$ jar cvf GWXFF.jar GWXFF.class

added manifest

adding: GWXFF.class(in = 675) (out= 370)(deflated 45%)

[oracle@test scripts]$ ll

total 28

-rwxr-xr-x. 1 oracle oinstall 4866 May 29 09:58 cmclean.sql

-rw-r-----. 1 oracle oinstall  675 Jun 11 11:29 GWXFF.class

-rw-r-----. 1 oracle oinstall  825 Jun 11 13:49 GWXFF.jar

-rw-r-----. 1 oracle oinstall  441 Jun 11 11:29 GWXFF.java

-rwxr-xr-x. 1 oracle oinstall  266 May 29 10:12 startprimary.sh

-rwxr-xr-x. 1 oracle oinstall  445 May 29 10:09 stopprimary.sh

Place this jar file at a location that is visible to the managed servers.

/u01/install/PANSOFT/abd/scripts/GWXFF.jar

5- Add the Custom jar File to the CLASSPATH.

If you are starting managed servers from the admin console, and you do NOT have start scripts enabled for Node Manager, follow these steps:

1. Log into the Admin Console as the WebLogic Admin user. Navigate to Environment -> Servers -> Click on name of managed server. Then select Configuration and Server Start.
2. Under "Class Path", add the absolute path to the GWXFF.jar file from above.
3. Click Save, you will be prompted to restart the server. But do not restart it yet.

If you are starting managed servers separately from the admin console or have start scripts enabled, add the absolute path to the GWXFF.jar to the classpath in the managed server start script.

6- Configuring Extended Log Format.

1. While still logged into the Admin console, browse to Servers -> Managed Server -> Logging -> HTTP -> Advanced and select Format.
2. Change from "Common" to "Extended."
3. In the Extended Logging Fields, add "x-GWXFF" to the fields already there.
4. Click save.
5. You may be informed that these changes do not require a restart, when in fact they do.
6. Restart the managed server.

7- Bounce the managed server to which changes have made and kindly test if Ip tracing is enabled.

When attempting to clone E-Business Suite script adcfgclone.pl fails with the following error

Error:- 

The JDK wasn't found in directory /u01/oracle/proderp/fs2/EBSapps/comn/util/jdk64.

Please edit the startWebLogic.sh script so that the JAVA_HOME

variable points to the location of your JDK.

 

CLASSPATH=/u01/oracle/proderp/fs1/FMW_Home/Oracle_EBS-app1/shared-libs/ebs-appsborg/WEB-INF/lib/ebsAppsborgManifest.jar:/u01/oracle/proderp/fs1/FMW_Home/Oracle_EBS-app1/shared-libs/ebs-fmw/WEB-INF/lib/ebsFMWManifest.jar:/u01/oracle/proderp/fs1/EBSapps/comn/shared-libs/ebs-3rdparty/WEB-INF/lib/ebs3rdpartyManifest.jar:/u01/oracle/proderp/fs1/FMW_Home/oracle_common/modules/oracle.uix_11.1.1/uix2.jar:/u01/oracle/proderp/fs1/EBSapps/comn/java/classes:/u01/oracle/proderp/fs1/EBSapps/10.1.2/forms/java/frmall.jar:/u01/oracle/proderp/fs1/EBSapps/10.1.2/jlib/ewt3.jar:/u01/oracle/proderp/fs1/EBSapps/10.1.2/j2ee/OC4J_BI_Forms/applications/formsapp/WEB-INF/lib/frmall.jar:/u01/oracle/proderp/fs1/FMW_Home/utils/config/10.3/config-launch.jar::/u01/oracle/proderp/fs1/FMW_Home/wlserver_10.3/common/derby/lib/derbynet.jar:/u01/oracle/proderp/fs1/FMW_Home/wlserver_10.3/common/derby/lib/derbyclient.jar:/u01/oracle/proderp/fs1/FMW_Home/wlserver_10.3/common/derby/lib/derbytools.jar::

/u01/oracle/proderp/fs1/FMW_Home/wlserver_10.3/common/bin/wlst.sh: line 24: /u01/oracle/proderp/fs2/EBSapps/comn/util/jdk64/bin/java: No such file or directory

FINE : Oct 18, 2020 14:12:13 - [WLSTCommandUtil:executePyScript] Error while executing the wlst script /tmp/CLONINGCLIENT-3172440716856000683/tmp1603019533838.py

FINE : Oct 18, 2020 14:12:13 - [CloneLogger:unSetSystemOutErrToCloneLogger] Invoking unsetSystemOutErrToCloneLogger 1

FINE : Oct 18, 2020 14:12:13 - [CloneLogger:unSetSystemOutErrToCloneLogger] Invoking unsetSystemOutErrToCloneLogger 2

FINE : Oct 18, 2020 14:12:13 - CLONE-22264   Time taken to restore sourceid "J2EECOMPONENT@EBS_domain" is "1,763" milliseconds.

FINE : Oct 18, 2020 14:12:13 - [ApplyCloneRequest:applyArchive] One cloner object was not executed properly, so rest were not executed.

INFO : Oct 18, 2020 14:12:13 - CLONE-21037   Time taken to restore all cloners from archive was "1" seconds.

INFO : Oct 18, 2020 14:12:13 - CLONE-21006   Total time taken by T2P process was 1 seconds.

 

INFO : Oct 18, 2020 14:12:13 - CLONE-21276 

 

Solution/Workaround:

 

1) Recreate OraInventory.

2) extract EBSapps tar under run and patch system(Fs1 & Fs2).

3) run adcfgclone.

 

Clone Obiee 12c

Follow these steps:

1) Restore database. 

2) Install OBIEE 12c on new environment.

3) Import a clean state or Empty BAR file.

4) Ensure that all the services are up and running.

5) Export BAR file from existing OBIEE 12c environment.

6) Import the BAR file (of the existing OBIEE 12c environment) into the new OBIEE 12c environment. 

Backup the source database instance -cold backup

Backup on-premise database and Oracle home 

 

-           SQL> show pdbs

 

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED

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

         2 PDB$SEED                       READ ONLY NO

         3 ORCLPDB                        MOUNTED

         4 PDBOBIEE                       MOUNTED

         5 PDBOBIEE11G                    MOUNTED

         6 PDBOBIEEP12                    MOUNTED

-           SQL> alter pluggable database all close;

 

Pluggable database altered.

 

Transfer Datafiles and Oracle-home backup to Backup folder

[obiee@pan backup]$ ls

dbhome_1.tar.gz  oradata

[obiee@pan backup]$ ls -ltr

total 3549404

-rw-rw-r-- 1 obiee obiee 3634586360 May 12  2019 dbhome_1.tar.gz

drwxr-x--- 3 obiee obiee         18 May 12  2019 oradata

[obiee@pan backup]$

Transfer Datafiles and Oracle-home backup to Oracle OBIEE Cloud server

 

 

[obiee@pan backup]$scp -rp backup obiee@hostname:/DATA

Export BAR file from existing OBIEE 12c environment.

 

Export BI Application Archive file

[obiee@pan bin]$ pwd

/OBIEE/middleware/oracle/oracle_common/common/bin

 [obiee@pan bin]$ ./wlst.sh

Initializing WebLogic Scripting Tool (WLST) ...

Welcome to WebLogic Server Administration Scripting Shell

Type help() for help on available commands

 

wls:/offline> exportServiceInstance('/OBIEE/middleware/oracle/user_projects/domains/bi12','ssi','/DATA/data_backup12/workDir','/DATA/data_backup12/exportDir')

 

Output  BI Application Archive file

 

[obiee@pan 20190720_150429]$ pwd

/DATA/data_backup12/exportDir/20190720_150429

[obiee@pan 20190720_150429]$ ls

ssi.bar

[obiee@pan 20190720_150429]$

 

Transfer BAR files to Oracle OBIEE Cloud server

[obiee@pan backup]$scp -rp /DATA/data_backup12 obiee@hostname:/OBIEE

 Restore Oracle Home and database on Oracle OBIEE cloud

 Create Directories

 

mkdir -p /DATA/oradb/product/12.2.0

mkdir -p /DATA/oradb/oraInventory

mkdir -p /DATA/oradata/orcl

mkdir -p /DATA/orcl/datafile

vi  /etc/oraInst.loc

inventory_loc=/DATA/oradb/oraInventory

inst_group=dba

Copy Datafiles from backup to /DATA/oradata/orcl and /DATA/orcl/datafile

[obiee@pan backup]$tar -xvzf oradata.tar.gz /Data

[obiee@pan backup]$ tar -xvzf oracl.tar.gz /Data

 

Unzip Oracle Home

[obiee@pan 12.2.0]$ pwd

/DATA/oradb/product/12.2.0

[obiee@pan 12.2.0]$tar -xvzf dbhome_1.tar.gz

Startup database using PFILE

 

[obiee@pan dbs]$ sqlplus / as  sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Fri Aug 2 12:40:03 2019

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

Connected to an idle instance.

SQL> !ls

hc_orcl.dat  init.ora  initorcl.ora  lkORCL  orapworcl  spfileorcl.ora

 

SQL> !pwd

/DATA/oradb/product/12.2.0/dbhome_1/dbs

SQL> startup pfile='/DATA/oradb/product/12.2.0/dbhome_1/dbs/initorcl.ora';

ORACLE instance started.

 

Total System Global Area 1.9663E+10 bytes

Fixed Size                  8802784 bytes

Variable Size            3154120224 bytes

Database Buffers         1.6442E+10 bytes

Redo Buffers               58302464 bytes

Database mounted.

Database opened.

SQL> alter pluggable database all open;

 

Pluggable database altered.

 

SQL> show pdbs

 

    CON_ID CON_NAME                       OPEN MODE RESTRICTED

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

         2 PDB$SEED                       READ ONLY NO

         3 ORCLPDB                        READ WRITE NO

         4 PDBOBIEE                       READ WRITE NO

         5 PDBOBIEE11G                    READ WRITE NO

         6 PDBOBIEEP12                    READ WRITE NO

SQL>

 

Reload listener

 lsnrctl status LISTENER

Oracle Business Intelligence 12c (12.2.1.3.0) Installation

 

Pre-Requisite

• Oracle Enterprise Edition Database installation
• JDK 8 Update 51 Installation
• Fusion Middleware 12.2.1 Installation
• Following are the series of steps covered in this section
• Download & Unzip OBIEE 12.2.1
• OBIEE 12.2.1 Installation
• Create BI Schemas using RCU utility
• Set Environment Variable for BI
• Create a BI Domain
• Verify Oracle Business Intelligence URL’s
• Start & Stop BI Server

 

 Import the BAR file into the new OBIEE 12c environment.

Import the BAR file (of the existing OBIEE 12c environment) into the new OBIEE 12c environment. 

 

[obiee@pan2 ~]$ . middleware.env

[obiee@pan2~]$ more middleware.env

export MW_HOME=/OBIEE/middleware/oracle

export WLS_HOME=$MW_HOME/wlserver

export WL_HOME=$WLS_HOME

export DOMAIN_HOME=$MW_HOME/user_projects/domains/bi12

# Set to the appropriate JAVA_HOME.

export JAVA_HOME=/OBIEE/JAVA/jdk1.8.0_221

export PATH=$JAVA_HOME/bin:$PATH

[obiee@pan2 ~]$ cd $MW_HOME

[obiee@pan2 oracle]$ cd oracle_common/common/bin

[obiee@pan2 bin]$ ls

[obiee@pan2 bin]$ ./wlst.sh

Initializing WebLogic Scripting Tool (WLST) ...

Welcome to WebLogic Server Administration Scripting Shell

Type help() for help on available commands

wls:/offline> importServiceInstance('/OBIEE/middleware/oracle/user_projects/domains/bi12','ssi','/OBIEE/importDir/ssi.bar')

 

 Dweblogic.security.allowCryptoJDefaultPRNG=true.>

 

Imported private key demokey.pem and certificate democert.pem

into a new keystore DemoIdentity.jks of type jks under alias demoidentity

[obiee@pan2 bin]

 

Stop OBIEE service and Start

• Obiee clone completed.

How to find out currently running databases in RAC setup

You can query for these background processes to tell if you are using RAC.

select
   name,
   value
from
   v$parameter
where
   name='cluster_database';
NAME                  VALUE
--------------------- ---------------------
cluster_database      TRUE