oracle 12c - ORACLEAGENT BLOG

How to Cleanup orphaned datapump jobs

— Find the orphaned Data Pump jobs:

SELECT owner_name, job_name, rtrim(operation) “OPERATION”,
rtrim(job_mode) “JOB_MODE”, state, attached_sessions
FROM dba_datapump_jobs
WHERE job_name NOT LIKE ‘BIN$%’ and state=’NOT RUNNING’
ORDER BY 1,2;

— Drop the tables

SELECT ‘drop table ‘ || owner_name || ‘.’ || job_name || ‘;’
FROM dba_datapump_jobs WHERE state=’NOT RUNNING’ and job_name NOT LIKE ‘BIN$%’

Thank you for giving your valuable time to read the above information.
If you want to be updated with all our articles send us the Invitation or Follow us:
Ramkumar’s LinkedIn: https://www.linkedin.com/in/ramkumardba/
LinkedIn Group: https://www.linkedin.com/in/ramkumar-m-0061a0204/
Facebook Page: https://www.facebook.com/Oracleagent-344577549964301
Ramkumar’s Twitter : https://twitter.com/ramkuma02877110
Ramkumar’s Telegram: https://t.me/oracleageant
Ramkumar’s Facebook: https://www.facebook.com/ramkumarram8

How to Cleanup orphaned datapump jobs

— Find the orphaned Data Pump jobs:

— Drop the tables

SELECT ‘drop table ‘ || owner_name || ‘.’ || job_name || ‘;’
FROM dba_datapump_jobs WHERE state=’NOT RUNNING’ and job_name NOT LIKE ‘BIN$%’

Get installed sqlpatches in database

— From 12c onward

set lines 2000
select patch_id,status,description from dba_registry_sqlpatch;
(or)
SET LINESIZE 400
COLUMN action_time FORMAT A20
COLUMN action FORMAT A10
COLUMN status FORMAT A10
COLUMN description FORMAT A40
COLUMN version FORMAT A10
COLUMN bundle_series FORMAT A10
SELECT TO_CHAR(action_time, ‘DD-MON-YYYY HH24:MI:SS’) AS action_time,action, status, description,version,patch_id, bundle_series FROM sys.dba_registry_sqlpatch;

— For 11g and below:
set lines 2000
select * from dba_registry_history;

GET INDEX USAGE

—Index monitoring is required, to find whether indexes are really in use or not. Unused can be dropped to avoid overhead.
— First enable monitoring usage for the indexes.

alter index INDEX_NAME.S_ASSET_TEST monitoring usage;

–Below query to find the index usage:

select * from v$object_usage;

Get sql query to html format

— We can spool output of an sql query to html format:

set pages 5000
SET MARKUP HTML ON SPOOL ON PREFORMAT OFF ENTMAP ON –
HEAD “EMPLOYEE REPORT –

–
–
” –
BODY “TEXT=’#FF00Ff'” –
TABLE “WIDTH=’90%’ BORDER=’5′”
spool report.html
Select * from scott.emp;
spool off
exit

row_count of all the tables of a schema

select table_name,
to_number(extractvalue(dbms_xmlgen.getXMLtype(‘select /+ PARALLEL(8) */ count() cnt from “&&SCHEMA_NAME”.’||table_name),’/ROWSET/ROW/CNT’))
rows_in_table from dba_TABLES
where owner=’&&SCHEMA_NAME’;

Disable or enable all triggers of schema

BEGIN
FOR j IN (SELECT trig_name
FROM user_triggers) LOOP

EXECUTE IMMEDIATE ‘ALTER TRIGGER ‘ || i.trig_name || ‘ DISABLE’;
END LOOP;
END;
/

Using RMAN Incremental Backups to Refresh Standby Database

In this article we are going to see about how to refresh the standby database using RMAN Incremental Backups.

My scenario is , I have faced the log difference between the primary and standby databases and then I sync the primary and standby databases by taking the incremental backup. Let us see in brief about here.

Overview steps:

Step:1 I have verified the log sequence on both the primary and standby databases then i found that there is a log difference between them.

Step:2 we can resolve this issue by taking the incremental backup on primary database and then restoring it in the standby database.

Step:3 First of all stop the redo transfer from primary to standby database by setting the log_archive_dest_2=DEFER.

Step:4 Find out the current_scn from v$database view in standby database.

Step:5 Then on primary, connect to the RMAN and take the incremental backup from the scn (which we noted in the standby database)

Step:6 Create the standby control file in primary database.

Step:7 By using server copy transfer the backup files, standby control file to the standby database.

Step:8 In standby database, replace the control file from backup.

Step:9 Stop the MRP(Managed Recovery Process) in standby database and shut down the database.

Step:10 Mount the standby database and connect to the RMAN, then catalog the backup pieces.

Step:11 Recover the standby database by using the incremental backup taken from the primary database.

Step:12 Enable log_archive_dest_state_2 parameter on primary and enable the MRP process on standby database.

Step:13 Then verify the log sequence sync between primary and standby databases.

Step:14 Finally we have refreshed/sync the standby database by using the incremental backup.

Step:1 Verify the log sequence on both primary and secondary database and found that there is a log difference between them.

on primary:

on standby:

Step:2 set the log_archive_dest_2=defer in primary database.

Step:3 Look for the current scn from the standby database.

Step:4 Take the incremental backup starting from that scn because after this scn only our primary and standby databases are not in sync.

Step:5 Create the standby controlfile in primary database.

Step:6 Move the incremental backup, standby controlfile from primary database to standby database through scp(server copy) and in standby database replace the controlfile from backup .

Step:7 Stop the MRP process in standby database.

Step:8 Recover the standby database by using the incremental backup taken from the primary database.

Step:9 Set the log_archive_dest_2=enable on primary database and start the MRP process in standby database.

Step:10 Start the MRP process on standby database.

Step:11 Verify the log sequence on both primary database and standby database, now both are in sync.

On primary:

On standby:

Get size of the database

col “Database Size” format a20
col “Free space” format a20
col “Used space” format a20
select round(sum(used.bytes) / 1024 / 1024 / 1024 ) || ‘ GB’ “Database Size”
, round(sum(used.bytes) / 1024 / 1024 / 1024 ) –
round(free.p / 1024 / 1024 / 1024) || ‘ GB’ “Used space”
, round(free.p / 1024 / 1024 / 1024) || ‘ GB’ “Free space”
from (select bytes
from v$datafile
union all
select bytes
from v$tempfile
union all
select bytes
from v$log) used
, (select sum(bytes) as p
from dba_free_space) free
group by free.p
/

Oracle E-Business Suite Installation EBS 12.2.0

Oracle E-Business Suite Installation (EBS 12.2.0).

Now i am going to install Oracle E-Business Suite Installation (EBS),For this I have already created an instance in my cloud account.

I have created an instance in the following specificaions as mentioned below,

OS Version: Oracle Linux 7.9

Shape: VM Standard E 2.4, 4 core CPU and 32 GB Memory.(depends of the CPU, our EBS installation software running time is decided)

Instance name: ANDHRA

Once the instance is created we can proceed with the installation procedures.

Then connect with putty login connect as root user.

Better install the software using Vnc ,it is more advantage because while we are installing the software if any power outage occurs it doesn’t disturbs the installation.

if you are going to use vnc install the following packages,

yum install *gnome*
yum install *vnc*
yum install *desktop*

Stop the firewall using the below command systemctl stop firewall.

Then set the selinux parameter as permissive which is in the vi /etc/selinux/config location.

After that you have to reset the system using init 6 or setenforce 0

Add the following set of lines to the files.

vi /etc/sysctl.conf

vi /etc/security/limits.conf

Next add the groups and users,here i am adding oracle and applmgr users to the newly created dba group.

set the passwords for the oracle and applmgr user.

Examine the locations of ora inventory and change the mod and group of orainst.loc

Create the below mentioned directories that is needed to install the software.

Add and attach the block volumes(here i am adding block volume size of 700GB) after attaching the block volume,view the block volume details and click ISCSI commands and information,once you click that there will be the informations for connecting and disconnecting.

Do the partition for the newly added block volume.Simply just attaching the block volume will not be partitioned.

Download the ebs software version that we are going to install as wget file.And move the file to the EBS directory in the /u01/stage mountpoint where we are going to unzip the software.

Run the given pre install,

yum install oracle-ebs-server-R12-preinstall     --------------------------it is for ebs.
yum install oracle-database-server-12cR2-preinstall ------------------it is for database.

unlink /usr/lib/libXtst.so.6 and link libXtst.so.6.1 with /usr/lib.it is important because it is helpful to load the frontend page.

we are going to create the stage area,then specify the directory which contains the zipped installation media.

Move the downloaded software (ie. wget.sh file) to the stage area location to perform installation.

Here starts the rapid installation process.

In this choose Install Oracle E-business Suite Release 12.2.0

Here provides you a option to receive security updates via My Oracle Support if you want enable the check Box and provide the mail Email ID.

in this window it opts for configuration choice either we can create a new configuration or Load the saved configuration.

Here we can see Global System Settings leave it by default as it is don’t change any values in this window.

Now only we are going to configure the Database Node Configuration,set up those parameters such as database sid,database host name,db operating system.

choose the Licensing Type , suite Licensing or Component Licensing.I have choosen suite licensing.

It shows the License Additional Products,don’t do any change,click Next.

In this choose country out of the available regions which is displayed.

select the character set by specifying the language you are going to use.I choose the language as American_English – US.

Next we have to configure the Primary Applications Node Configuration,such as setting the primary host name,operating system,Apps OS User,Apps OS Group,Apps Base Dir,Apps Instance Dir

Provide the Application User Information such as WLS Admin User,Password and proceed further for Installation.

Continue with the Node Information.Database node,primary apps node information should be given here.

Rapid Install Wizard is performing the system checks,once it is fine we can move further.

It shows the list of pre-install tests if and only it satisfies all the condition we can go with the installation process.For us the instance passed all of the pre-install tests.

we are set with the process,installation is in progress it takes some time to install the software.

Depending upon the CPU core that we have specified in the instance creation the installation time may differ.

Tag Archives: oracle 12c

Oracle E-Business Suite Installation (EBS 12.2.0).