How to create Oracle pluggable database

 

Step1> Login to the database as sysdba

Step2> Run create pluggable database command from CDB container

SQL> create pluggable database PDB1 admin user admin identified  by password;
Pluggable database created.

SQL> show pdbs
    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1                                MOUNTED

Step3> Open pluggable database in read/write mode

SQL> alter pluggable database PDB1 open;
Pluggable database altered.

SQL> show pdbs
    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1                                READ WRITE NO

Step4> Save the pluggable database state

SQL> alter pluggable database PDB1 save state;

Pluggable database altered.

How to clone an Oracle pluggable database (PDB)

 

Step1> Bring the Source PDB in Read Only Mode

SQL> alter session set container=PDB;
Session altered.

SQL> alter pluggable database close immediate;
Pluggable database altered.

SQL> alter pluggable database open read only;
Pluggable database altered.

SQL> select con_id,name,open_mode,restricted from v$pdbs;
    CON_ID NAME       OPEN_MODE                      RESTRICTE
---------- ---------- ------------------------------ ---------
         2 PDB       READ ONLY                      NO

Step2> Clone the Source PDB

SQL> create pluggable database PDBCL from PDB ;
Pluggable database created.

SQL> select PDB_NAME,STATUS from cdb_pdbs;
PDB_NAME             STATUS
-------------------- ---------------------------
PDB$SEED           NORMAL
PDB                       NORMAL
PDBCL                  NEW

Step3> Open the Clone PDB

SQL> alter pluggable database PDBCL open;
Pluggable database altered.

Step4> Verify the Status of Clone PDB
SQL> select con_id,name,open_mode,restricted from v$pdbs;

Step5> Bring Back the Source PDB in Read Write Mode again
SQL> Alter pluggable database PDB close immediate;
SQL> Alter pluggable database PDB open;

How to configure Auto Login wallet in Oracle

 

Step1> Check the Wallet's Current Status

SQL> set linesize 200

SQL>col WALLET_DIR for a32
SQL>col status for a21

select STATUS,WRL_PARAMETER WALLET_DIR,WALLET_TYPE from V$ENCRYPTION_WALLET;
STATUS                WALLET_DIR                       WALLET_TYPE
--------------------- -------------------------------- ------------------------------------------------------------
OPEN                  +DATA1/CDB/wallet/           PASSWORD

1 row selected.

Step2> Create local Auto Login keyStore from existing keyStore

SQL> ADMINISTER KEY MANAGEMENT CREATE LOCAL AUTO_LOGIN KEYSTORE FROM KEYSTORE  '+DATA1/CDB/wallet/' IDENTIFIED BY "xxxxxx";

keystore altered.

Step3> Close the Password Wallet

SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE CLOSE IDENTIFIED BY "xxxx" CONTAINER=ALL;

keystore altered.

Step4> Check the Status of Auto Login Wallet

select STATUS,WRL_PARAMETER WALLET_DIR,WALLET_TYPE from V$ENCRYPTION_WALLET;

STATUS                WALLET_DIR                       WALLET_TYPE
--------------------- -------------------------------- ------------------------------------------------------------
OPEN                  +DATA1/CDB/wallet/           LOCAL_AUTOLOGIN

1 row selected.

ORA-28365: wallet is not open while starting the Database

$ srvctl start instance -d CDB -i CDB01

PRCR-1013 : Failed to start resource ora.cdb.db
PRCR-1064 : Failed to start resource ora.cdb.db on node node1.oracle.com
CRS-5017: The resource action "ora.cdb.db start" encountered the following error:
ORA-28365: wallet is not open
. For details refer to "(:CLSN00107:)" in "/u01/app/oracle/diag/crs/node1.oracle.com/crs/trace/crsd_oraagent_oracle.trc".
CRS-2674: Start of 'ora.cdb.db' on 'node1.oracle.com' failed

Solution:
Start the instance in mount mode
SQL> startup mount;
ORACLE instance started.
Total System Global Area 2147483648 bytes
Fixed Size                  2926472 bytes
Variable Size            1392511096 bytes
Database Buffers          738197504 bytes
Redo Buffers               13848576 bytes
Database mounted.

Check wallet status

set linesize 200
col WALLET_DIR for a32
col status for a21

select STATUS,WRL_PARAMETER WALLET_DIR,WALLET_TYPE from V$ENCRYPTION_WALLET;
STATUS                WALLET_DIR                       WALLET_TYPE
--------------------- -------------------------------- ------------------------------------------------------------
CLOSED                +DATA1/CDB/wallet/           UNKNOWN

Open the Keystore

SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY "xxxxx";

keystore altered.

Open the database

SQL> alter database open;

Database altered.

Droping the database in silent mode using DBCA

 

$ export ORACLE_HOME=/u01/oracle/12.2.0.1/dbhome_1

$ export PATH=$PATH:$ORACLE_HOME/bin

$dbca -silent -deleteDatabase -sourceDB CDBNAME -forceArchiveLogDeletion -sysDBAPassword xxxxxxx -sysDBAUserName sys

Displaying Top consuming process in Linux

Displaying top CPU consuming processes:

ps aux | head -1; ps aux | sort -rnk 3 | head -10

Displaying top memory-consuming processes:

ps aux | head -1; ps aux | sort -rnk 4 | head -10

How to create directory in oracle database

SQL> Create directory PAP_HOME_DIR    as '/userdata/applications/cusap/data/gs_data';
SQL> grant read, write on directory PAP_HOME_DIR to APPS;

To query the privileges given to directory execute the following:

SQL> SET lines 80
SQL> COL grantee FORMAT a20
SQL> COL privilege FORMAT a10
SQL> SELECT directory_name, grantee, privilege  FROM user_tab_privs t, all_directories d 
 WHERE t.table_name(+)=d.directory_name ORDER BY 1,2,3;

How to remove and install Oracle XDB

XDB Removal

SQL> spool xdb_removal.log
SQL> set echo on;
SQL> connect / as sysdba
SQL> shutdown immediate;
SQL> startup
SQL> @?/rdbms/admin/catnoqm.sql
SQL> spool off;


XDB Install

SQL> spool xdb_install.log
SQL> set echo on;
SQL> connect / as sysdba
SQL> shutdown immediate;
SQL> startup;
SQL> @?/rdbms/admin/catqm.sql XDB XDB TEMP YES
SQL> @?/rdbms/admin/utlrp.sql
SQL> spool off

Oracle Applications Cloning - EBS R12.2

MOS Note: Cloning Oracle E-Business Suite Release 12.2 with Rapid Clone (Doc ID 1383621.1)

Pre-requisite Tasks
Run AutoConfig on the application tier
sh $ADMIN_SCRIPTS_HOME/adautocfg.sh

Run AutoConfig on the database tier
cd $ORACLE_HOME/appsutil/scripts
sh adautocfg.sh


Prepare the Source System database tier for cloning
1. Log on to the Source System as the oracle user.
2. Source the Database tier environment file.
3. Execute the following commands:

$ cd /appsutil/scripts/
$ perl adpreclone.pl dbTier

Prepare the Source System application tier for cloning
1. Log on to the primary node of the Source System as the applmgr user.
2. Source the environment file of the Run Edition File system.
You can use the following command to confirm that the environment variable FILE_EDITION points to the Run Edition File System:

$ echo $FILE_EDITION
It should return the value: run

3. Execute the following commands:
$ cd /admin/scripts
$ perl adpreclone.pl appsTier

Database Tier Clone 

Copy the database node file system
1. Copy the database (.dbf) files from the Source System to the Target System.
2. Copy the redo log files from the Source System to the Target System
3. Copy the Source System database ORACLE_HOME to the Target System

Configure the Target System database server
Log on to the Target System as the oracle user and enter the following commands:

$ cd /appsutil/clone/bin
$ perl adcfgclone.pl dbTier


Application Tier Clone
Copy the application tier file system from the Source to the Target "Run Edition File System"

Log on to Run Edition File System in the Source System application tier nodes as the applmgr user.
Copy the following application tier directories from the Source Node to the Target Run Edition File System application tier node:





cd $COMMON_TOP/clone/bin  or if it doesn’t exit go to the FS1.

mv FMW_Home FMW_Home_bak on both FS.
Verify /etc/oraInst.loc  points to correct folder

Configure the Target System application tier server nodes
Log on to the Run Edition File System in the Target System as the applmgr user and enter the following commands:
$ cd /clone/bin
$ perl adcfgclone.pl appsTier dualfs

Select No for starting up the services.

Finishing Tasks

update the profile values. replace source information with target details.

Change apps/sysadmin password
FNDCPASS apps/apps 0 Y system/manager SYSTEM APPLSYS newpsswd
FNDCPASS apps/apps 0 Y system/manager USER SYSADMIN newpsswd

Note: if you change apps password, then perform the below:

Start AdminServer using the $INST_TOP/admin/scripts/adadminsrvctl.sh script. Do not start any other application tier services.

Change the “apps” password in WLS Datasource as follows:
Log in to WLS Administration Console.

Click Lock & Edit in Change Center.
In the Domain Structure tree, expand Services, then select Data Sources
On the “Summary of JDBC Data Sources” page, select EBSDataSource
On the “Settings for EBSDataSource” page, select the Connection Pool tab
Enter the new password in the “Password” field.
Enter the new password in the “Confirm Password” field.
Click Save.
Click Activate Changes in Change Center.

Check whether you are able to connect to db using apps
sqlplus apps/ from application tier.

Before Starting all the services. Run Autoconfig.

cd $ADMIN_SCRIPTS_HOME
sh adautocfg.sh

Now start the services

Perform Sanity check

List oracle tables with stale stats

SQL> set pagesize 500 linesize 200
SQL> select owner, table_name, num_rows from dba_tab_statistics where stale_stats = 'YES' order by owner, table_name;

Tuning Concurrent Manager

Tuning the Concurrent Manager

• 1359612.1 Webcast 'E-Business Suite - RAC & Parallel Concurrent Processing
• 1367676.1 Webcast 'E-Business Suite - Concurrent Manager Performance
• 164085.1 Enhancing and Automating Oracle Applications Concurrent Processing
• 1057802.1 Best Practices for Performance for Concurrent Managers
• 1304305.1 E-Business Concurrent Processing Information Center

How to disable OCM scheduled job if you are not using this product

Error:
ORA-12012: error on auto execute of job "ORACLE_OCM"."MGMT_CONFIG_JOB_2_1"

SQL> exec dbms_scheduler.disable(‘ORACLE_OCM.MGMT_CONFIG_JOB’);

PL/SQL procedure successfully completed.

SQL> exec dbms_scheduler.disable(‘ORACLE_OCM.MGMT_STATS_CONFIG_JOB’)

PL/SQL procedure successfully completed.

Oracle CPU patches applied list using opatch

$ORACLE_HOME/OPatch/opatch lsinventory -bugs_fixed | grep MOLECULE

20506699   20449729  Tue Jul 07 12:26:29 GMT 2015   DB-11.2.0.4-MOLECULE-020-CPUAPR2015
20506706   20449729  Tue Jul 07 12:26:29 GMT 2015   DB-11.2.0.4-MOLECULE-021-CPUAPR2015

Identify EBS profile option settings that may be related to performance-degrading debug/trace activity

-- check_ebs_trace_profs.sql
--  Author: John Piwowar
-- Purpose: Identify E-Business Suite system profile option settings that may
--          be related to performance-degrading debug/trace activity
--   Notes: Prompts for a cutoff date for when profile options were set
--          May need additional tweaking for multi-language installations 
set pagesize 9999
set linesize 120
set verify off
 col "Profile Option" for a25
 col "Option Level" for a13
 col "set for" for a20
 col "Value" for a20
 col "Set On" for a11
 col "Blame" for a20
 PROMPT Enter date value in form DD-MON-YYYY for check_since
    select tl.user_profile_option_name "Profile Option"
         , decode( val.level_id
                 , 10001, 'Site'
                 , 10002, 'Application'
                 , 10003, 'Responsibility'
                 , 10004, 'User'
                 , 10005, 'Server'
                 , 10006, 'Organization'
                 , 10007, 'Server+Resp'
                 , 'No idea, boss') "Option Level"
         , decode( val.level_id
                 , 10001
                 , 'EVERYWHERE!'
                 , 10002
                 , (select application_name
                      from fnd_application_tl
                     where application_id = val.level_value)
                 , 10003
                 , (select responsibility_name
                      from fnd_responsibility_tl
                     where responsibility_id = val.level_value
                       and application_id = val.level_value_application_id)
                 , 10004
                 , (select user_name
                      from fnd_user
                     where user_id = val.level_value)
                 , 10005
                 , (select host || '.' || domain
                      from fnd_nodes
                     where node_id = val.level_value)
                 , 10006
                 , (select name
                      from hr_all_organization_units
                     where organization_id = val.level_value)
                 , 10007
                 , 'Look it up' --per specification El-Ay-Zed-why
                 , '''Tis a mystery') "Set for"
         , val.profile_option_value "Value"
         , val.last_update_date "Set on"
         , usr.user_name "Set By"
      from fnd_profile_options opt,
           fnd_profile_option_values val,
           fnd_profile_options_tl tl,
           fnd_user usr
     where opt.profile_option_id = val.profile_option_id
       and opt.profile_option_name = tl.profile_option_name
       and regexp_like( tl.user_profile_option_name
                      , '(trace|log|debug|audit|diag|sql)'
                      , 'i'
                      )
       and not(regexp_like( tl.user_profile_option_name
                          , '(catalog|file|login|utilities)'
                          , 'i'
                          )
              )
       and usr.user_id = val.last_updated_by
       and usr.user_name  not in ( 'AUTOINSTALL'
                                 , 'INITIAL SETUP'
                                 , 'ANONYMOUS')
       and val.last_update_date > '&check_since'
     order by val.last_update_date desc
 ;

oracle RAC diagnostics command

use the below commands to gather information on RAC for troubleshooting

From the grid user account do the following:

1)check the whole CRS installation as it is now:
cluvfy stage -post crsinst -n all -verbose

2) check the whole RAC db install as it is now
cluvfy stage -pre dbinst -n all -verbose

3) compare nodes
cluvfy comp peer -n all -verbose

4) check crs nodes integrity
cluvfy comp crs -n all -verbose

5) check if nodes have been removed previously registered in crs
cluvfy stage -post nodedel

6) validate installation deviations to best practices and mandatory reqs for crs/db
cluvfy comp healthcheck -deviations

Oracle CPU Patches applied list

SQL Query

SQL> col action_time format a30
SQL> select substr(action_time,1,30) action_time,substr(id,1,8) id,substr(action,1,10) action,
substr(version,1,8) version,substr(bundle_series,1,6) bundle,substr(comments,1,20) comments from registry$history;

or

you can also use

SQL> set serverout on
SQL> exec dbms_qopatch.get_sqlpatch_status;

How to change hostname in Linux

[root ~]# hostname
old-name

[root ~]# hostname new-name
[root@ ~]# hostname
new-name

[root ~]# vi /etc/sysconfig/network
[root ~]# vi /etc/hosts

[root ~]# pwd
/root
[root ~]# service network restart
Shutting down interface eth650_1.989:                      [  OK  ]
Shutting down interface eth1290_2.989:                     [  OK  ]

Using flashback on oracle RAC database

Here are steps to enable flashback in sit:

To Enable flashback

check make sure archive log is enabled.

alter system set db_recovery_file_dest_size=50G scope=both sid='*';
alter system set DB_RECOVERY_FILE_DEST='+RECOC1' scope=both sid='*';
alter system set db_flashback_retention_target=10080 scope=both sid='*';

srvctl stop database -d
srvctl start instance -d -i -o nomount;

alter system set cluster_database=false scope=spfile;

srvctl stop database -d
srvctl start instance -d -i -o mount;

alter database flashback on;
alter system set cluster_database=true scope=spfile;

srvctl stop database -d
srvctl start database -d


To create restore point

To create a restore point(db snapshot)
sqlplus "/as sysdba"
create restore point ;

To restore the flashback

srvctl stop database -d
srvctl start instance -d -i -o mount

flashback database to restore point 1_07APR15;
alter database open resetlogs;


SET PAUSE ON
SET PAUSE 'Press Return to Continue'
SET PAGESIZE 60
SET LINESIZE 300
SET VERIFY OFF

COLUMN scn FOR 999,999,999,999,999
COLUMN Incar FOR 99
COLUMN name FOR A25
COLUMN storage_size FOR 999,999,999,999
COLUMN guarantee_flashback_database FOR A3

SELECT
      database_incarnation# as Incar,
      scn,
      name,
      time,
      storage_size,
      guarantee_flashback_database
FROM
      v$restore_point
/

Oracle SQL queries for temporary tablespace

useful temporary tablespace commands:

Resize a Temporary Tablespace:
alter tablespace temp resize 1000M;
alter database tempfile 'tempfile_name' resize 3000M;
alter database tempfile 'tempfile_name' autoextend on maxsize 30G;


Shrink a Temporary Tablespace:
alter tablespace temp shrink space;
alter tablespace temp shrink tempfile 'tempfile_name' keep 2000M;

Rename a Temporary Tablespace:
alter tablespace temp rename to temp_x;

To view information about Temporary Tablespace:
select * from dba_temp_files;
select * from v$tempfile;
select * from v$temp_space_header;

Monitor the tempoary tablspace usage:
select * from v$sort_usage;
select *  from v$sort_segment;

Using ETCC in EBS R12.2

Refer Support Note 1594274.1

EBS Technology Codelevel Checker (ETCC) utility provides two scripts you can run to help ensure you have the necessary database and middle tier bugfixes installed on your Oracle E-Business Suite Release 12.2 system.

ETCC can be downloaded via Patch 17537119 from My Oracle Support.

Note: Always use the latest version of ETCC. New bugfixes will not be checked by older versions of the utility.

The two scripts that make up ETCC are:

checkDBpatch.sh (checkDBpatch.cmd on Microsoft Windows). This is the Database EBS Technology Codelevel Checker (DB-ETCC), which determines if all the needed bugfixes exist in the specified database ORACLE_HOME.

checkMTpatch.sh (checkMTpatch.cmd on Microsoft Windows). This is the Middle Tier EBS Technology Codelevel Checker (MT-ETCC), which determines if all the needed bugfixes exist in the middle tier file system.