Zero Downtime Migration 21c - ORACLEAGENT BLOG

Generate resize datafile script

select ‘alter database datafile’||’ ”’||file_name||””||’ resize ‘||round(highwater+2)||’ ‘||’m’||’;’ from (
select /+ rule */
a.tablespace_name,
a.file_name,
a.bytes/1024/1024 file_size_MB,
(b.maximum+c.blocks-1)d.db_block_size/1024/1024 highwater
from dba_data_files a ,
(select file_id,max(block_id) maximum
from dba_extents
group by file_id) b,
dba_extents c,
(select value db_block_size
from v$parameter
where name=’db_block_size’) d
where a.file_id= b.file_id
and c.file_id = b.file_id
and c.block_id = b.maximum
order by a.tablespace_name,a.file_name);

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

Get database uptime

select to_char(startup_time, ‘DD-MM-YYYY HH24:MI:SS’),floor(sysdate-startup_time) DAYS from v$Instance;

Scn to Timestamp and Timestamp to Scn

— Get current scn value:
select current_scn from v$database;
— Get scn value at particular time:
select timestamp_to_scn(’26-FEB-21:05:10:00′) from dual;
— Get timestamp from scn:
select scn_to_timestamp(298411441)from dual;

Generate Bulk AWR reports in database

select SNAP_ID, BEGIN_INTERVAL_TIME, END_INTERVAL_TIME
from dba_hist_snapshot
where trunc(BEGIN_INTERVAL_TIME)=trunc(sysdate-&no)
order by 1;

CREATE OR REPLACE DIRECTORY awr_reports_dir AS ‘/tmp/awrreports’;

DECLARE
— Adjust before use.
l_snap_start NUMBER := 4884; –Specify Initial Snap ID
l_snap_end NUMBER := 4892; –Specify End Snap ID
l_dir VARCHAR2(50) := ‘AWR_REPORTS_DIR’;

l_last_snap NUMBER := NULL;
l_dbid v$database.dbid%TYPE;
l_instance_number v$instance.instance_number%TYPE;
l_file UTL_FILE.file_type;
l_file_name VARCHAR(50);

BEGIN
SELECT dbid
INTO l_dbid
FROM v$database;

SELECT instance_number
INTO l_instance_number
FROM v$instance;

FOR cur_snap IN (SELECT snap_id
FROM dba_hist_snapshot
WHERE instance_number = l_instance_number
AND snap_id BETWEEN l_snap_start AND l_snap_end
ORDER BY snap_id)
LOOP
IF l_last_snap IS NOT NULL THEN
l_file := UTL_FILE.fopen(l_dir, ‘awr_’ || l_last_snap || ‘_’ || cur_snap.snap_id || ‘.html’, ‘w’, 32767);

FOR cur_rep IN (SELECT output
FROM TABLE(DBMS_WORKLOAD_REPOSITORY.awr_report_html(l_dbid, l_instance_number, l_last_snap, cur_snap.snap_id)))
LOOP
UTL_FILE.put_line(l_file, cur_rep.output);
END LOOP;
UTL_FILE.fclose(l_file);
END IF;
l_last_snap := cur_snap.snap_id;
END LOOP;

EXCEPTION
WHEN OTHERS THEN
IF UTL_FILE.is_open(l_file) THEN
UTL_FILE.fclose(l_file);

How to find current redo log size in database

SELECT le.leseq “Current log sequence No”,
100*cp.cpodr_bno/le.lesiz “Percent Full”,
cp.cpodr_bno “Current Block No”,
le.lesiz “Size of Log in Blocks”
FROM x$kcccp cp, x$kccle le
WHERE le.leseq =CP.cpodr_seq
AND bitand(le.leflg,24) = 8
/

Get database link details in database

set pagesize 200
set lines 200
col db_link for a19
set long 999
SELECT db_link,
owner_id,
logged_on,
heterogeneous,
open_cursors,
in_transaction,
update_sent
FROM gv$dblink
ORDER BY db_link;

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.