Scripts related to TEMP Tablespace

To see Temp Space:
————————-

SELECT a.tablespace_name,ROUND((c.total_blocksb.block_size)/1024/1024/1024,2)
"Total Size [GB]",ROUND((a.used_blocksb.block_size)/1024/1024/1024,2) "Used_size[GB]",
ROUND(((c.total_blocks-a.used_blocks)b.block_size)/1024/1024/1024,2) "Free_size[GB]",
ROUND((a.max_blocksb.block_size)/1024/1024/1024,2) "Max_Size_Ever_Used[GB]",
ROUND((a.max_used_blocksb.block_size)/1024/1024/1024,2) "MaxSize_ever_Used_by_Sorts[GB]" ,
ROUND((a.used_blocks/c.total_blocks)100,2) "Used Percentage"
FROM V$sort_segment a,dba_tablespaces b,(SELECT tablespace_name,SUM(blocks)
total_blocks FROM dba_temp_files GROUP by tablespace_name) c
WHERE a.tablespace_name=b.tablespace_name AND a.tablespace_name=c.tablespace_name;

Query to check TEMP USAGE :
—————————————–

col name for a20
SELECT d.status "Status", d.tablespace_name "Name", d.contents "Type", d.extent_management
"ExtManag",
TO_CHAR(NVL(a.bytes / 1024 / 1024, 0),'99,999,990.900') "Size (M)", TO_CHAR(NVL(t.bytes,
0)/1024/1024,'99999,999.999') ||'/'||TO_CHAR(NVL(a.bytes/1024/1024, 0),'99999,999.999') "Used (M)",
TO_CHAR(NVL(t.bytes / a.bytes * 100, 0), '990.00') "Used %"
FROM sys.dba_tablespaces d, (select tablespace_name, sum(bytes) bytes from dba_temp_files group by
tablespace_name) a,
(select tablespace_name, sum(bytes_cached) bytes from
v$temp_extent_pool group by tablespace_name) t
WHERE d.tablespace_name = a.tablespace_name(+) AND d.tablespace_name = t.tablespace_name(+)
AND d.extent_management like 'LOCAL' AND d.contents like 'TEMPORARY';

To Check the Percentage Usage of Temp Tablespace:
—————————————————————–

select (s.tot_used_blocks/f.total_blocks)*100 as "percent used"
from (select sum(used_blocks) tot_used_blocks
from v$sort_segment where tablespace_name='TEMP') s,
(select sum(blocks) total_blocks
from dba_temp_files where tablespace_name='TEMP') f;

To find Sort Segment Usage by a particular User:
————————————————————-

SELECT s.username,s.sid,s.serial#,u.tablespace, u.contents, u.extents, u.blocks
FROM v$session s, v$sort_usage u
WHERE s.saddr=u.session_addr
order by u.blocks desc;

To find Total Free space in Temp Tablespace :
———————————————————

select tablespace_name, (free_blocks8)/1024/1024  FreeSpaceInGB,
(used_blocks8)/1024/1024  UsedSpaceInGB,
(total_blocks*8)/1024/1024  TotalSpaceInGB
from v$sort_segment where tablespace_name like '%TEMP%'

Get 10 sessions with the largest temp usage :
—————————————————

cursor bigtemp_sids is
select * from (
select s.sid,
s.status,
s.sql_hash_value sesshash,
u.SQLHASH sorthash,
s.username,
u.tablespace,
sum(u.blocks*p.value/1024/1024) mbused ,
sum(u.extents) noexts,
nvl(s.module,s.program) proginfo,
floor(last_call_et/3600)||':'||
floor(mod(last_call_et,3600)/60)||':'||
mod(mod(last_call_et,3600),60) lastcallet
from v$sort_usage u,
v$session s,
v$parameter p
where u.session_addr = s.saddr
and p.name = 'db_block_size'
group by s.sid,s.status,s.sql_hash_value,u.sqlhash,s.username,u.tablespace,
nvl(s.module,s.program),
floor(last_call_et/3600)||':'||
floor(mod(last_call_et,3600)/60)||':'||
mod(mod(last_call_et,3600),60)
order by 7 desc,3)
where rownum < 11;

Identifying WHO is currently using TEMP Segments :
——————————————————————

SELECT sysdate,a.username, a.sid, a.serial#, a.osuser, (b.blocksd.block_size)/1048576 MB_used, c.sql_text
FROM v$session a, v$tempseg_usage b, v$sqlarea c,
(select block_size from dba_tablespaces where tablespace_name='TEMP') d
WHERE b.tablespace = 'TEMP'
and a.saddr = b.session_addr
AND c.address= a.sql_address
AND c.hash_value = a.sql_hash_value
AND (b.blocksd.block_size)/1048576 > 1024
ORDER BY b.tablespace, 6 desc;

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

Calculate the Datafile Growth on a yearly basis

To calculate the Datafile Growth on a yearly basis:

select to_char(CREATION_TIME,’RRRR’) year,to_char(CREATION_TIME,’MM’) month,round(sum(bytes)/1024/1024/1024) gb
from v$datafile group by to_char(CREATION_TIME,’RRRR’),to_char(CREATION_TIME,’MM’) order by 1,2;

Through OEM :

Here are the steps to know the Database growth pattern for the last month/year using OEM

1) log in to OEM and Click on the Reports Tab
2) Navigate to Reports–>Storage–>Oracle Database Space Usage path and Click on the Oracle Database Space Usage link.
3) Select the Target database and here we are getting Oracle Database space usage for the last month.
4) Also we can get one year of Database growth by setting Set Time Period Button.
5) Also we can find Oracle Database Tablespace Monthly Space Usage by Navigating Reports–>Storage–>Oracle Database Space Usage path and clicking on the Oracle Database Tablespace Monthly Space Usage link.

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:

Troubleshoot Long running Concurrent Requests

1. How to Determine Which Manager Ran a Specific Concurrent Request?

col USER_CONCURRENT_QUEUE_NAME for a100
select b.USER_CONCURRENT_QUEUE_NAME from fnd_concurrent_processes a,
fnd_concurrent_queues_vl b, fnd_concurrent_requests c
where a.CONCURRENT_QUEUE_ID = b.CONCURRENT_QUEUE_ID
and a.CONCURRENT_PROCESS_ID = c.controlling_manager
and c.request_id = '&conc_reqid';

2. Concurrent manager status for a given sid?

col MODULE for a20
col OSUSER for a10
col USERNAME for a10
set num 10
col MACHINE for a20
set lines 200
col SCHEMANAME for a10
select s.sid,s.serial#,p.spid os_pid,s.status, s.osuser,s.username, s.MACHINE,s.MODULE,
s.SCHEMANAME,
s.action from gv$session s, gv$process p WHERE s.paddr = p.addr and s.sid = '&oracle_sid';

3. Find out request-id from Oracle_Process Id:

select REQUEST_ID,ORACLE_PROCESS_ID,OS_PROCESS_Id from apps.fnd_concurrent_requests where
ORACLE_PROCESS_ID='&a';

4. To find sid, serial# for a given concurrent request id?

set lines 200
SELECT a.request_id, d.sid, d.serial# ,d.osuser,d.process , c.SPID ,d.inst_id
FROM apps.fnd_concurrent_requests a,
apps.fnd_concurrent_processes b,
gv$process c,
gv$session d
WHERE a.controlling_manager = b.concurrent_process_id
AND c.pid = b.oracle_process_id
AND b.session_id=d.audsid
AND a.request_id = &Request_ID
AND a.phase_code = 'R';

5. To find the concurrent program name, phase code, and status code for a given request id?

SELECT request_id, user_concurrent_program_name, DECODE(phase_code,'C','Completed',phase_code)
phase_code, DECODE(status_code,'D', 'Cancelled' ,
'E', 'Error' , 'G', 'Warning', 'H','On Hold' , 'T', 'Terminating', 'M', 'No Manager' , 'X',
'Terminated', 'C', 'Normal', status_code) status_code, to_char(actual_start_date,'dd-mon-
yy:hh24:mi:ss') Start_Date, to_char(actual_completion_date,'dd-mon-yy:hh24:mi:ss'),
completion_text FROM apps.fnd_conc_req_summary_v WHERE request_id = '&req_id' ORDER BY 6 DESC;

6. To find the SQL query for a given concurrent request sid?

select sid,sql_text from gv$session ses, gv$sqlarea sql where
ses.sql_hash_value = sql.hash_value(+) and ses.sql_address = sql.address(+) and
ses.sid='&oracle_sid'
/

7. To find child requests

set lines 200
col USER_CONCURRENT_PROGRAM_NAME for a40
col PHASE_CODE for a10
col STATUS_CODE for a10
col COMPLETION_TEXT for a20
SELECT sum.request_id,req.PARENT_REQUEST_ID,sum.user_concurrent_program_name, DECODE
(sum.phase_code,'C','Completed',sum.phase_code) phase_code, DECODE(sum.status_code,'D',
'Cancelled' ,
'E', 'Error' , 'G', 'Warning', 'H','On Hold' , 'T', 'Terminating', 'M', 'No Manager' , 'X',
'Terminated', 'C', 'Normal', sum.status_code) status_code, sum.actual_start_date,
sum.actual_completion_date, sum.completion_text FROM apps.fnd_conc_req_summary_v sum,
apps.fnd_concurrent_requests req where req.request_id=sum.request_id and req.PARENT_REQUEST_ID =
'&parent_concurrent_request_id';

8. Cancelling Concurrent requests:

update fnd_concurrent_requests
set status_code='D', phase_code='C'
where request_id=&req_id;

9. Kill sessions program-wise

select 'ALTER SYSTEM KILL SESSION '''||sid||','||serial#||''' immediate;' from v$session where
MODULE like '';

10 . Concurrent Request running by SID

SELECT a.request_id,
d.sid as Oracle_SID,
d.serial#,
d.osuser,
d.process,
c.SPID as OS_Process_ID
FROM apps.fnd_concurrent_requests a,
apps.fnd_concurrent_processes b,
gv$process c,
gv$session d
WHERE a.controlling_manager = b.concurrent_process_id
AND c.pid = b.oracle_process_id
AND b.session_id=d.audsid
AND d.sid = &SID;

11. Find out request-id from Oracle_Process Id:

select REQUEST_ID,ORACLE_PROCESS_ID,OS_PROCESS_Id from fnd_concurrent_requests where
ORACLE_PROCESS_ID='&a';

12. Oracle Concurrent Request Error Script (requests which were errored out)

SELECT a.request_id "Req Id"
,a.phase_code,a.status_code
, actual_start_date
, actual_completion_date
,c.concurrent_program_name || ': ' || ctl.user_concurrent_program_name "program"
FROM APPLSYS.fnd_Concurrent_requests a,APPLSYS.fnd_concurrent_processes b
,applsys.fnd_concurrent_queues q
,APPLSYS.fnd_concurrent_programs c
,APPLSYS.fnd_concurrent_programs_tl ctl
WHERE a.controlling_manager = b.concurrent_process_id
AND a.concurrent_program_id = c.concurrent_program_id
AND a.program_application_id = c.application_id
AND a.status_code = 'E'
AND a.phase_code = 'C'
AND actual_start_date > sysdate - 2
AND b.queue_application_id = q.application_id
AND b.concurrent_queue_id = q.concurrent_queue_id
AND ctl.concurrent_program_id = c.concurrent_program_id
AND ctl.LANGUAGE = 'US'
ORDER BY 5 DESC;

13. Request submitted by User

SELECT
user_concurrent_program_name,
request_date,
request_id,
phase_code,
status_code
FROM
fnd_concurrent_requests fcr,
fnd_concurrent_programs_tl fcp,
fnd_responsibility_tl fr,
fnd_user fu
WHERE
fcr.CONCURRENT_PROGRAM_ID = fcp.concurrent_program_id
and fcr.responsibility_id = fr.responsibility_id
and fcr.requested_by = fu.user_id
and user_name = '&user'
AND actual_start_date > sysdate - 1
ORDER BY REQUEST_DATE Asc;

14. Concurrent Program enables trace

col User_Program_Name for a40
col Last_Updated_By for a30
col DESCRIPTION for a30
SELECT A.CONCURRENT_PROGRAM_NAME "Program_Name",
SUBSTR(A.USER_CONCURRENT_PROGRAM_NAME,1,40) "User_Program_Name",
SUBSTR(B.USER_NAME,1,15) "Last_Updated_By",
SUBSTR(B.DESCRIPTION,1,25) DESCRIPTION
FROM APPS.FND_CONCURRENT_PROGRAMS_VL A, APPLSYS.FND_USER B
WHERE A.ENABLE_TRACE='Y'
AND A.LAST_UPDATED_BY=B.USER_ID;

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:

EXTERNAL TABLE

INTRODUCTION:

In this blog, we are going to create the external table

DESIGNATION:

This is easy to make and easy to insert the data quickly.
This is much faster than SQL * LOADER.
In this type of table, we cannot use a DML statement.
Bulk data can be easily inserted into the table.

OVERALL STEPS:

Step 1: Go to make a directory at the OS level

Step 2: After making the directory

Go to the directory, we have to create a text file and add the necessary values.

Step 3: We have to notify the location of the directory.

Step 4: Go to database, conn sys user

We have to permit for creation of a directory in that user by using the sys

Step 5: Create the directory and mention the location of the OS.

Step 6: Conn sys user,

We have to permit reading and writing to create a directory by using sys.

Step 7: Create the table and give the external format

Step 8: View the table

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:

Running fnd_conc_clone.setup_clean manually is unsupported

Oracle Concurrent Processing – Version 12.2 and later

Running fnd_conc_clone.setup_clean manually is unsupported.

This API was designed for use internally and although Development has documented running this API in some setup notes it is only where it has been explicitly tested and for that specific setup.

For example, you will find some references to FND_CONC_CLONE.SETUP_CLEAN in the upgrade guide and other development-created documents. However, these are strategically placed.

Running this API outside of the specific places found in the official Oracle documentation (upgrade guide and other development documents) will cause problems and will break the functionality of your Oracle E-Business Suite system.

If fnd_conc_clone.setup_clean has been run, the only option to bring back the system to a stable and safe state will be to restore from a backup taken previously to run the API.

R12.2: When To Run fnd_conc_clone.setup_clean?

Ref:Doc ID 2130750.1

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:

SQL LOADER

DESIGNATION:

SQL * LOADER loads data from external files into tables of an oracle database.
We do not insert the data into the table using SQL * LOADER directly.
We need a control file for inserting the data.
SQL * LOADER is used to just execute the commands of the control file.
The flatfile saving process is normally we will save .txt files.
The Control file saving process is normally we will save .ctl files.

BAD FILE:

A bad file is used to store the rejected records.
The rejected records will store in a bad file automatically.
If you wrongly type the data into a flat file, you will surely get some errors. So that error during SQL * LOADER process which stores in bad file.

TYPES OF FLAT FILE:

Variable record flatfile

This is with delimeter

eg., 101, abc, 2000

Fixed record flatfile

This is without delimeter

eg., 101abc2000

**The following picture illustrates the SQL * LOADER process:**

SYNTAX:

$sqlldr <space> userid = username / password

control = ‘path of the controlfile’ <space> log = test.log

Creating a controlfile for variable record flatfile:

BY USING INSERT LOAD DATA PROCESS:

load data

infile ‘path of the flatfile’

insert / append / truncate / replace

into table <table_name>

fields terminated by ‘delimeter_symbol’

optionally enclosed by ‘delimeter_symbol’

trailing nullcols

(col1, col2, col3,……)

OVERALL STEPS:

To execute the SQL * LOADER tool, you need at least three files.

The input data files store delimited data
The parameter file stores the location of the input and output files.
The control file contains the specification on how data is loaded.

Step 1: Create the table at the database level

View the table :

Step 2: Create the text document by using the vi editor in os level

View the text file

Step 3: Create the control file by using the vi editor in the os level

View the control file

Step 4: Execute the SQL * LOADER syntax

Step 5: View the table

BY USING APPEND LOAD DATA PROCESS:

load data

infile ‘path of the flatfile’

append

into table <table_name>

fields terminated by ‘delimeter_symbol’

optionally enclosed by ‘delimeter_symbol’

trailing nullcols

(col1, col2, col3,……)

Load data in the control file:

View the APPEND output:

BY USING TRUNCATE LOAD DATA PROCESS:

load data

infile ‘path of the flatfile’

truncate

into table <table_name>

fields terminated by ‘delimeter_symbol’

optionally enclosed by ‘delimeter_symbol’

trailing nullcols

(col1, col2, col3,……)

Load data in the control file:

View the TRUNCATE output:

BY USING REPLACE LOAD DATA PROCESS:

load data

infile ‘path of the flatfile’

replace

into table <table_name>

fields terminated by ‘delimeter_symbol’

optionally enclosed by ‘delimeter_symbol’

trailing nullcols

(col1, col2, col3,……)

Load data in the control file:

View the REPLACE output:

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:

DATABASE SWITCHOVER ORACLE 19C USING DGMGRL

Description:
In this blog, we are going to see data guard switch over by using data guard broker DGMGRL.

Data Guard Switchover:

A database can be in one of two mutually exclusive modes (primary or standby). These roles can be altered at runtime without loss of data or resetting of redo logs. A switchover is a role reversal between the primary database and one of its standby databases. A switchover guarantees no data loss.
This is typically done for planned maintenance of the primary system.
During a switchover, the primary database transitions to a standby role, and the standby database transitions to the primary role.
The transition occurs without having to re-enable either database.

Overall steps:

1. Connect the primary database with DGMGRL Data Guard broker.
2. Switch over primary to standby database.
3. Connect Standby database.
4. Switch over standby to primary.

Step 1: Connect the primary database with the DGMGRL Data Guard broker.

[oratest@oracle admin]$ dgmgrl sys/oracle@orcl

DGMGRL for Linux: Release 19.0.0.0.0 - Production on 
Fri Nov 12 13:19:44 2021 Version 19.9.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates.
All rights reserved.Welcome to DGMGRL, type "help" for information.

Connected to "orcl"

Connected as SYSDBA.

Step 2: Switch over primary to the standby database.

DGMGRL> switchover to orcl_stby;
Performing switchover NOW, please wait...

Operation requires a connection to instance "orcl" on database 
"orcl_stby"

Connecting to instance "orcl"...

Connected as SYSDBA.

New primary database "orcl_stby" is opening...

Operation requires start up of instance "orcl" on database "orcl"

Starting instance "orcl"...ORACLE instance started.

Database mounted.

Switchover succeeded, new primary is "orcl_stby"

DGMGRL>

Let's switch back to the original primary. Connect to the 
new primary (orcl_stby) and switchover to the new standby database (orcl).

Step 3: Connect the Standby database.

[oratest@oracle admin]$ dgmgrl sys/oracle@orcl_stby

DGMGRL for Linux: Release 19.0.0.0.0 - Production on 
Fri Nov 12 13:31:56 2021Version 19.9.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates. 
All rights reserved.
Welcome to DGMGRL, type "help" for information.

Connected to "orcl_stby"
Connected as SYSDBA.

Step 4: Switch over standby to primary

DGMGRL> switchover to orcl ;

Performing switchover NOW, please wait...

Operation requires a connection to instance "orcl" on 
database "orcl"

Connecting to instance "orcl"...

Connected as SYSDBA.

New primary database "orcl" is opening...

Operation requires start up of instance "orcl" on database 
"orcl_stby"

Starting instance "orcl"...

ORACLE instance started.

Database mounted.

Switchover succeeded, new primary is "orcl"

DGMGRL>

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:

RMAN-06183: datafile or datafile copy xyz.dbf larger than MAXSETSIZE

Below error i got while taking RMAN backup:

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of backup plus archivelog command at 07/12/2021 03:29:07
RMAN-06183: datafile or datafile copy +ORA_DATA/wwibetest_bosqeumcsdb/datafile/undotbs1.779.1077535587 (file number 3) larger than MAXSETSIZE

Finding and solution:

RMAN> show all;

using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name WWIBEMCS_BOSQEUMCSDB201 are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1;
CONFIGURE BACKUP OPTIMIZATION ON;
CONFIGURE DEFAULT DEVICE TYPE TO DISK;
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO ‘/backup/oracle/WWIBEMCS/rman/WWIBEMCS_autobcf_%F’;
CONFIGURE DEVICE TYPE DISK PARALLELISM 4 BACKUP TYPE TO BACKUPSET;
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1;
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1;
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT ‘/bck/oracle/%d/rman/%d_%Y%M%D_%u_s%s_p%p’;
CONFIGURE MAXSETSIZE TO 30 G;
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM ‘AES128’; # default
CONFIGURE COMPRESSION ALGORITHM ‘BASIC’ AS OF RELEASE ‘DEFAULT’ OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE;
CONFIGURE SNAPSHOT CONTROLFILE NAME TO ‘/wwi/wwdb/db/oracle/product/11.2.0.4/db_1/dbs/snapcf_WWIBEMCS.f’; # default

RMAN> CONFIGURE MAXSETSIZE TO 35 G;

old RMAN configuration parameters:
CONFIGURE MAXSETSIZE TO 30 G;
new RMAN configuration parameters:
CONFIGURE MAXSETSIZE TO 35 G;
new RMAN configuration parameters are successfully stored

RMAN> show all;

RMAN configuration parameters for database with db_unique_name WWIBEMCS_BOSQEUMCSDB201 are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1;
CONFIGURE BACKUP OPTIMIZATION ON;
CONFIGURE DEFAULT DEVICE TYPE TO DISK;
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO ‘/backup/oracle/WWIBEMCS/rman/WWIBEMCS_autobcf_%F’;
CONFIGURE DEVICE TYPE DISK PARALLELISM 4 BACKUP TYPE TO BACKUPSET;
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1;
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1;
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT ‘/bck/oracle/%d/rman/%d_%Y%M%D_%u_s%s_p%p’;
CONFIGURE MAXSETSIZE TO 35 G;

Now I ran the RMAN backup it went successful.

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:

RMAN Database Restore ASM

RMAN Database Restore ASM

Description:
In this blog, we are going to see take RMAN backup in asm storage and restore the database to another server.

RMAN Restore ASM:
Take the backup using RMAN in source host copy the backup into destination host, using that backup files to restore the database.

Overall Steps:

Source server:
1. Backup Database using RMAN
2. Create pfile from spfile
3. Copy the backup files to the destination server

Destination server:
1. Edit pfile
2. Create Required Folders in destination server
3. Add the database entry in oratab
4. Startup using pfile Nomount stage
5. Restore control file
6. Mount database
7. Catalog backup pieces
8. Restore and recover the database
9. Change dbname/DBID uisng NID
10. verify the database name and id.

Source Server:

Step 1: Take backup using RMAN:

Create backup directory:

Backup script:
run {
allocate channel t1 type disk;
allocate channel t1 type disk;
allocate channel t1 type disk;
backup incremental level 0 database format ‘/u01/share/backup/database_%d_%u_%s’;
release channel t1;
}
sql ‘alter system archive log current’;
run {
allocate channel a1 type disk;
backup archivelog all format ‘/u01/share/backup/arch_%d_%u_%s’;
release channel a1;
}
run {
allocate channel c1 type disk;
backup current controlfile format ‘/u01/share/backup/Control_%d_%u_%s’;
release channel c1;
}
exit

Connect RMAN and execute the script:

[oracle@asm ~]$ rman target /

Recovery Manager: Release 19.0.0.0.0 – Production on Fri Jan 21 08:01:54 2022
Version 19.3.0.0.0

connected to target database: ORAASM (DBID=1977555372)

RMAN> @backup.rcv

RMAN> run {
2> allocate channel t1 type disk;
3> allocate channel t2 type disk;
4> allocate channel t3 type disk;
5> backup incremental level 0 database format ‘/u01/share/backup/database_%d_%u_%s’;
6> release channel t1;
7> release channel t2;
8> release channel t3;
9> }
using target database control file instead of recovery catalog
allocated channel: t1
channel t1: SID=78 device type=DISK

allocated channel: t2
channel t2: SID=89 device type=DISK

allocated channel: t3
channel t3: SID=88 device type=DISK

Starting backup at 21-JAN-22
channel t1: starting incremental level 0 datafile backup set
channel t1: specifying datafile(s) in backup set
input datafile file number=00001 name=+DATA/ORAASM/DATAFILE/system.257.1092813819
channel t1: starting piece 1 at 21-JAN-22
channel t2: starting incremental level 0 datafile backup set
channel t2: specifying datafile(s) in backup set
input datafile file number=00003 name=+DATA/ORAASM/DATAFILE/sysaux.258.1092813853
input datafile file number=00014 name=+DATA/ORAASM/DATAFILE/tblspace.281.1093044531
input datafile file number=00007 name=+DATA/ORAASM/DATAFILE/users.260.1092813869
channel t2: starting piece 1 at 21-JAN-22
channel t3: starting incremental level 0 datafile backup set
channel t3: specifying datafile(s) in backup set
input datafile file number=00004 name=+DATA/ORAASM/DATAFILE/undotbs1.259.1092813869
input datafile file number=00015 name=+DATA/ORAASM/DATAFILE/test.282.1094538795
input datafile file number=00013 name=+DATA/ORAASM/DATAFILE/tblspace.280.1093044407
channel t3: starting piece 1 at 21-JAN-22
channel t3: finished piece 1 at 21-JAN-22
piece handle=/u01/share/backup/database_ORAASM_080jqqo1_8 tag=TAG20220121T080204 comment=NONE
channel t3: backup set complete, elapsed time: 00:00:16
channel t3: starting incremental level 0 datafile backup set
channel t3: specifying datafile(s) in backup set
input datafile file number=00010 name=+DATA/ORAASM/D47CEAB72C2F2513E053867EA8C0538D/DATAFILE/sysaux.277.1092815375
input datafile file number=00011 name=+DATA/ORAASM/D47CEAB72C2F2513E053867EA8C0538D/DATAFILE/undotbs1.275.1092815375
channel t3: starting piece 1 at 21-JAN-22
channel t1: finished piece 1 at 21-JAN-22
piece handle=/u01/share/backup/database_ORAASM_060jqqns_6 tag=TAG20220121T080204 comment=NONE
channel t1: backup set complete, elapsed time: 00:01:10
channel t1: starting incremental level 0 datafile backup set
channel t1: specifying datafile(s) in backup set
input datafile file number=00006 name=+DATA/ORAASM/86B637B62FE07A65E053F706E80A27CA/DATAFILE/sysaux.271.1092814421
channel t1: starting piece 1 at 21-JAN-22
channel t2: finished piece 1 at 21-JAN-22
piece handle=/u01/share/backup/database_ORAASM_070jqqns_7 tag=TAG20220121T080204 comment=NONE
channel t2: backup set complete, elapsed time: 00:01:13
channel t2: starting incremental level 0 datafile backup set
channel t2: specifying datafile(s) in backup set
input datafile file number=00009 name=+DATA/ORAASM/D47CEAB72C2F2513E053867EA8C0538D/DATAFILE/system.276.1092815375
input datafile file number=00012 name=+DATA/ORAASM/D47CEAB72C2F2513E053867EA8C0538D/DATAFILE/users.279.1092815459
channel t2: starting piece 1 at 21-JAN-22
channel t3: finished piece 1 at 21-JAN-22
piece handle=/u01/share/backup/database_ORAASM_090jqqop_9 tag=TAG20220121T080204 comment=NONE
channel t3: backup set complete, elapsed time: 00:00:49
channel t3: starting incremental level 0 datafile backup set
channel t3: specifying datafile(s) in backup set
input datafile file number=00005 name=+DATA/ORAASM/86B637B62FE07A65E053F706E80A27CA/DATAFILE/system.270.1092814419
channel t3: starting piece 1 at 21-JAN-22
channel t1: finished piece 1 at 21-JAN-22
piece handle=/u01/share/backup/database_ORAASM_0a0jqqq2_10 tag=TAG20220121T080204 comment=NONE
channel t1: backup set complete, elapsed time: 00:00:12
channel t1: starting incremental level 0 datafile backup set
channel t1: specifying datafile(s) in backup set
input datafile file number=00008 name=+DATA/ORAASM/86B637B62FE07A65E053F706E80A27CA/DATAFILE/undotbs1.272.1092814421
channel t1: starting piece 1 at 21-JAN-22
channel t1: finished piece 1 at 21-JAN-22
piece handle=/u01/share/backup/database_ORAASM_0d0jqqqg_13 tag=TAG20220121T080204 comment=NONE
channel t1: backup set complete, elapsed time: 00:00:26
channel t2: finished piece 1 at 21-JAN-22
piece handle=/u01/share/backup/database_ORAASM_0b0jqqq9_11 tag=TAG20220121T080204 comment=NONE
channel t2: backup set complete, elapsed time: 00:00:27
channel t3: finished piece 1 at 21-JAN-22
piece handle=/u01/share/backup/database_ORAASM_0c0jqqqf_12 tag=TAG20220121T080204 comment=NONE
channel t3: backup set complete, elapsed time: 00:00:26
Finished backup at 21-JAN-22

Starting Control File and SPFILE Autobackup at 21-JAN-22
piece handle=+DATA/ORAASM/AUTOBACKUP/2022_01_21/s_1094544235.283.1094544237 comment=NONE
Finished Control File and SPFILE Autobackup at 21-JAN-22

released channel: t1

released channel: t2

released channel: t3

RMAN> sql ‘alter system archive log current’;
sql statement: alter system archive log current

RMAN> run {
2> allocate channel a1 type disk;
3> backup archivelog all format ‘/u01/share/backup/arch_%d_%u_%s’;
4> release channel a1;
5> }
allocated channel: a1
channel a1: SID=78 device type=DISK

Starting backup at 21-JAN-22
current log archived
channel a1: starting archived log backup set
channel a1: specifying archived log(s) in backup set
input archived log thread=1 sequence=16 RECID=1 STAMP=1094544242
input archived log thread=1 sequence=17 RECID=2 STAMP=1094544243
channel a1: starting piece 1 at 21-JAN-22
channel a1: finished piece 1 at 21-JAN-22
piece handle=/u01/share/backup/arch_ORAASM_0f0jqqrk_15 tag=TAG20220121T080403 comment=NONE
channel a1: backup set complete, elapsed time: 00:00:03
Finished backup at 21-JAN-22

Starting Control File and SPFILE Autobackup at 21-JAN-22
piece handle=+DATA/ORAASM/AUTOBACKUP/2022_01_21/s_1094544247.289.1094544249 comment=NONE
Finished Control File and SPFILE Autobackup at 21-JAN-22

released channel: a1

RMAN> run {
2> allocate channel c1 type disk;
3> backup current controlfile format ‘/u01/share/backup/Control_%d_%u_%s’;
4> release channel c1;
5> }
allocated channel: c1
channel c1: SID=78 device type=DISK

Starting backup at 21-JAN-22
channel c1: starting full datafile backup set
channel c1: specifying datafile(s) in backup set
including current control file in backup set
channel c1: starting piece 1 at 21-JAN-22
channel c1: finished piece 1 at 21-JAN-22
piece handle=/u01/share/backup/Control_ORAASM_0h0jqqrs_17 tag=TAG20220121T080412 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:01
Finished backup at 21-JAN-22

Starting Control File and SPFILE Autobackup at 21-JAN-22
piece handle=+DATA/ORAASM/AUTOBACKUP/2022_01_21/s_1094544256.290.1094544257 comment=NONE
Finished Control File and SPFILE Autobackup at 21-JAN-22

released channel: c1

RMAN> exit

Recovery Manager complete.

Check the backup file location:

Step 2: Create pfile from spfile

create pfile=’/home/oracle/initasmora.ora’ from spfile;
Step 3: Copy the backup file into destination server

[oracle@asm backup]$ scp * [email protected]:/u01/backup/

Destination server:

Step 1: Edit pfile
Change DB_UNIQUE_NAME in the pfile:

*.audit_file_dest=’/u01/app/oracle/admin/oraasm/adump’
*.audit_trail=’db’
*.compatible=’19.0.0′
*.control_files=’+DATA/ASMORA/CONTROLFILE/current.262.1092813933′,’+DATA/ASMORA/CONTROLFILE/current.261.1092813933′
*.db_block_size=8192
*.db_create_file_dest=’+DATA’
*.db_name=’oraasm’
*.db_recovery_file_dest=’+DATA’
*.db_recovery_file_dest_size=12732m
*.db_unique_name=’asmora’ —–>change db unique name
*.diagnostic_dest=’/u01/app/oracle’
*.dispatchers='(PROTOCOL=TCP) (SERVICE=oraasmXDB)’
*.enable_pluggable_database=true
*.local_listener=’LISTENER_ORAASM’
*.open_cursors=300
*.pga_aggregate_target=629m
*.processes=300
*.remote_login_passwordfile=’EXCLUSIVE’
*.sga_target=1886m
*.undo_tablespace=’UNDOTBS1′
[oracle@asm dbs]$

Step 2: Create Required Folders

Create required folders in the destination server
[oracle@asmnew ~]$ mkdir -p /u01/app/oracle/admin/ASMORA/adump/

Step 3: Add the Database entry in oratab file.

ASMORA:/u01/app/oracle/product/19.0.0/dbhome_1:N

Step 4: startup database using pfile nomount stage

[oracle@asmnew asmora]$ export ORACLE_SID=asmora
[oracle@asmnew asmora]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 – Production on Fri Jan 21 08:18:06 2022
Version 19.3.0.0.0

Connected to an idle instance.

SQL> startup nomount pfile=’/u01/asmora/initasmora.ora’;
ORACLE instance started.

Total System Global Area 1979709696 bytes
Fixed Size 9136384 bytes
Variable Size 452984832 bytes
Database Buffers 1509949440 bytes
Redo Buffers 7639040 bytes

Step 5: restore control file from backup location

[oracle@asmnew asmora]$ rman target /

Recovery Manager: Release 19.0.0.0.0 – Production on Fri Jan 21 08:22:04 2022
Version 19.3.0.0.0

connected to target database: ORAASM (not mounted)

RMAN> restore controlfile from ‘/u01/share/backup/Control_ORAASM_0h0jqqrs_17’;

Starting restore at 21-JAN-22
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=44 device type=DISK

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:05
output file name=+DATA/ORAASM/CONTROLFILE/current.262.1092813933
output file name=+DATA/ORAASM/CONTROLFILE/current.261.1092813933
Finished restore at 21-JAN-22

RMAN>

Step 6: Mount the database.

SQL> alter database mount;

Database altered.

SQL> select name, open_mode, db_unique_name from v$database;

NAME OPEN_MODE DB_UNIQUE_NAME

ORAASM MOUNTED asmora

Step 7: Catalog backup files

CATALOG BACKUPPIECE ‘/u01/asmora/arch_ORAASM_0f0jqqrk_15’;
CATALOG BACKUPPIECE ‘/u01/asmora/Control_ORAASM_0h0jqqrs_17’;
CATALOG BACKUPPIECE ‘/u01/asmora/database_ORAASM_030jqo9c_3’;
CATALOG BACKUPPIECE ‘/u01/asmora/database_ORAASM_060jqqns_6’;
CATALOG BACKUPPIECE ‘/u01/asmora/database_ORAASM_070jqqns_7’;
CATALOG BACKUPPIECE ‘/u01/asmora/database_ORAASM_080jqqo1_8’;
CATALOG BACKUPPIECE ‘/u01/asmora/database_ORAASM_090jqqop_9’;
CATALOG BACKUPPIECE ‘/u01/asmora/database_ORAASM_0a0jqqq2_10’;
CATALOG BACKUPPIECE ‘/u01/asmora/database_ORAASM_0b0jqqq9_11’;
CATALOG BACKUPPIECE ‘/u01/asmora/database_ORAASM_0c0jqqqf_12’;
CATALOG BACKUPPIECE ‘/u01/asmora/database_ORAASM_0d0jqqqg_13’;

Step 8: Restore and recover database

List backup archivelogs

RMAN> list backup of archivelog all;

recover database using rman

run
{
ALLOCATE CHANNEL d1 DEVICE TYPE disk;
ALLOCATE CHANNEL d2 DEVICE TYPE disk;
set newname for datafile 1 to ‘+DATA’;
set newname for datafile 2 to ‘+DATA’;
set newname for datafile 3 to ‘+DATA’;
set newname for datafile 4 to ‘+DATA’;
set newname for datafile 5 to ‘+DATA’;
SQL “ALTER DATABASE RENAME FILE ”+DATA/oraasm/onlinelog/group_3.268.1092813965”
to ”+DATA”” ;
SQL “ALTER DATABASE RENAME FILE ”+DATA/oraasm/onlinelog/group_2.266.1092813951”
to ”+DATA”” ;
SQL “ALTER DATABASE RENAME FILE ”+DATA/oraasm/onlinelog/group_1.265.1092813949”
to ”+DATA”” ;
SET UNTIL SEQUENCE 12; <— 11+1
RESTORE DATABASE;
SWITCH DATAFILE ALL;
RECOVER DATABASE;
}

check logfile and database role

Step 9:change database name and ID

modify pfile
SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>

add this controlfile entry to pfile

SQL> startup nomount pfile='initasmora.ora';
ORACLE instance started.

Total System Global Area 1269366784 bytes
Fixed Size                  2227984 bytes
Variable Size             838861040 bytes
Database Buffers          419430400 bytes
Redo Buffers                8847360 bytes
SQL> alter database mount;

Database altered.

SQL>

set db name and id:

[oracle@asm asmora]$ . oraenv
ORACLE_SID = [asmora] ?
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@asm asmora]$ nid target=sys dbname=asmora

DBNEWID: Release 19.0.0.0.0 – Production on Fri Jan 21 09:09:50 2022

Password:
Connected to database ORAASM (DBID=1977555372)

Connected to server version 19.3.0

Control Files in database:
+DATA/ASMORA/CONTROLFILE/current.262.1092813933
+DATA/ASMORA/CONTROLFILE/current.261.1092813933

Change database ID and database name ORAASM to ASMORA? (Y/[N]) => y

Proceeding with operation
Changing database ID from 1977555372 to 65466533
Changing database name from ORAASM to ASMORA
Control File +DATA/ASMORA/CONTROLFILE/current.262.1092813933 – modified
Control File +DATA/ASMORA/CONTROLFILE/current.261.1092813933 – modified
Datafile +DATA/ORAASM/DATAFILE/system.257.109281381 – dbid changed, wrote new name
Datafile +DATA/ORAASM/DATAFILE/sysaux.258.109281385 – dbid changed, wrote new name
Datafile +DATA/ORAASM/DATAFILE/undotbs1.259.109281386 – dbid changed, wrote new name
Datafile +DATA/ORAASM/86B637B62FE07A65E053F706E80A27CA/DATAFILE/system.270.109281441 – dbid changed, wrote new name
Datafile +DATA/ORAASM/86B637B62FE07A65E053F706E80A27CA/DATAFILE/sysaux.271.109281442 – dbid changed, wrote new name
Datafile +DATA/ORAASM/DATAFILE/users.260.109281386 – dbid changed, wrote new name
Datafile +DATA/ORAASM/86B637B62FE07A65E053F706E80A27CA/DATAFILE/undotbs1.272.109281442 – dbid changed, wrote new name
Datafile +DATA/ORAASM/D47CEAB72C2F2513E053867EA8C0538D/DATAFILE/system.276.109281537 – dbid changed, wrote new name
Datafile +DATA/ORAASM/D47CEAB72C2F2513E053867EA8C0538D/DATAFILE/sysaux.277.109281537 – dbid changed, wrote new name
Datafile +DATA/ORAASM/D47CEAB72C2F2513E053867EA8C0538D/DATAFILE/undotbs1.275.109281537 – dbid changed, wrote new name
Datafile +DATA/ORAASM/D47CEAB72C2F2513E053867EA8C0538D/DATAFILE/users.279.109281545 – dbid changed, wrote new name
Datafile +DATA/ORAASM/DATAFILE/tblspace.280.109304440 – dbid changed, wrote new name
Datafile +DATA/ORAASM/DATAFILE/tblspace.281.109304453 – dbid changed, wrote new name
Datafile +DATA/ORAASM/DATAFILE/test.282.109453879 – dbid changed, wrote new name
Datafile +DATA/ORAASM/TEMPFILE/temp.269.109281404 – dbid changed, wrote new name
Datafile +DATA/ORAASM/D47CB418C2B91B66E053867EA8C0C5A0/TEMPFILE/temp.273.109281445 – dbid changed, wrote new name
Datafile +DATA/ORAASM/D47CEAB72C2F2513E053867EA8C0538D/TEMPFILE/temp.278.109281541 – dbid changed, wrote new name
Control File +DATA/ASMORA/CONTROLFILE/current.262.1092813933 – dbid changed, wrote new name
Control File +DATA/ASMORA/CONTROLFILE/current.261.1092813933 – dbid changed, wrote new name
Instance shut down

Database name changed to ASMORA.
Modify parameter file and generate a new password file before restarting.
Database ID for database ASMORA changed to 65466533.
All previous backups and archived redo logs for this database are unusable.
Database is not aware of previous backups and archived logs in Recovery Area.
Database has been shutdown, open database with RESETLOGS option.
Succesfully changed database name and ID.
DBNEWID – Completed succesfully.

[oracle@asm asmora]$

Step 10: check the DB name and id

SQL> alter database open resetlogs;

Database altered.

SQL> select name, open_mode, db_unique_name, dbid from v$database;

NAME OPEN_MODE DB_UNIQUE_NAME DBID

ASMORA READ WRITE asmora 65466533

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:

HOME / TROUBLESHOOTING / PREREQUISITE CHECK “CHECKACTIVEFILESANDEXECUTABLES” FAILED Prerequisite Check “CheckActiveFilesAndExecutables” Failed

CHECKACTIVEFILESANDEXECUTABLES” FAILED Prerequisite Check “CheckActiveFilesAndExecutables” Failed

Oracle Home : /u01/app/oracle/product/12.1.0
Central Inventory : /u01/app/oraInventory
from : /u01/app/oracle/product/12.1.0/oraInst.loc
OPatch version : 12.2.0.1.27
OUI version : 12.1.0.2.0
Logfile location: /u01/app/oracle/product/12.1.0/cfgtoollogs/opatch/opatch2022-01-21_08-04-36AM_1.log

Verifying environment and performing prerequisite checks…
Prerequisite check “CheckActiveFilesAndExecutables” failed.
The details are:

Following active executables are not used by opatch process :
/u01/app/oracle/product/12.1.0/lib/libclntsh.so.12.1

Following active executables are used by opatch process :

/u01/app/oracle/product/12.1.0/lib/libasmclntsh12.so
UtilSession failed: Prerequisite check “CheckActiveFilesAndExecutables” failed.
Logfile location: /u01/app/oracle/product/12.1.0/cfgtoollogs/opatch/opatch2022-01-21_08-04-36AM_1.log

OPatch failed with error code 73

Solution:

[oracle@oracleagent:Database] /sbin/fuser /u01/app/oracle/product/12.1.0/lib/libclntsh.so.12.1
/u01/app/oracle/product/12.1.0/lib/libclntsh.so.12.1: 22235m 24173m
[oracle@oracleagent:Database] kill -9 22235 24173
[oracle@oracleagent:Database] opatch apply
Oracle Interim Patch Installer version 12.2.0.1.27
Copyright (c) 2022, Oracle Corporation. All rights reserved.

Verifying environment and performing prerequisite checks…
OPatch continues with these patches: 32768233 33128590

Do you want to proceed? [y|n]
y
User Responded with Y
All checks passed.

Please shutdown Oracle instances running out of this ORACLE_HOME on the local system.
(Oracle Home = ‘/u01/app/oracle/product/12.1.0’)

Is the local system ready for patching? [y|n]
y
User Responded with Y
Backing up files…
Applying sub-patch ‘32768233’ to OH ‘/u01/app/oracle/product/12.1.0’
ApplySession: Optional component(s) [ oracle.network.cman, 12.1.0.2.0 ] not present in the Oracle Home or a higher version is found.

…..

….

…..

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:

Tag Archives: oracle 20c

To see Temp Space: ————————-

Query to check TEMP USAGE : —————————————–

To Check the Percentage Usage of Temp Tablespace: —————————————————————–

To find Sort Segment Usage by a particular User: ————————————————————-

To find Total Free space in Temp Tablespace : ———————————————————

Get 10 sessions with the largest temp usage : —————————————————

Identifying WHO is currently using TEMP Segments : ——————————————————————

To calculate the Datafile Growth on a yearly basis:

Through OEM :

INTRODUCTION:

DESIGNATION:

OVERALL STEPS:

DESIGNATION:

BAD FILE:

TYPES OF FLAT FILE:

The following picture illustrates the SQL * LOADER process:

SYNTAX:

Creating a controlfile for variable record flatfile:

OVERALL STEPS:

DATABASE SWITCHOVER ORACLE 19C USING DGMGRL

CHECKACTIVEFILESANDEXECUTABLES” FAILED Prerequisite Check “CheckActiveFilesAndExecutables” Failed

Solution:

To see Temp Space:
————————-

Query to check TEMP USAGE :
—————————————–

To Check the Percentage Usage of Temp Tablespace:
—————————————————————–

To find Sort Segment Usage by a particular User:
————————————————————-

To find Total Free space in Temp Tablespace :
———————————————————

Get 10 sessions with the largest temp usage :
—————————————————

Identifying WHO is currently using TEMP Segments :
——————————————————————

**The following picture illustrates the SQL * LOADER process:**