Add datafile to temporary tablespace

Add datafile to temporary tablespace:

ALTER TABLESPACE TEMP1 ADD TEMPFILE '<datafile.dbf>' SIZE 2G AUTOEXTEND ON NEXT 10M MAXSIZE 2G;

Remove datafile from temporary tablespace:

alter tablespace TEMP1 drop tempfile '<datafile.dbf>';

Table size queries

Table size queries:

select segment_name,segment_type,bytes/1024/1024 MB
from dba_segments where segment_type='TABLE' and segment_name=upper('FND_LOG_MESSAGES');

SELECT owner,
segment_name,
segment_type,
tablespace_name,
bytes/1048576 MB,
initial_extent,
next_extent,
extents,
pct_increase
FROM
DBA_SEGMENTS
WHERE
OWNER = 'table owner' AND
SEGMENT_NAME = 'table name' AND
SEGMENT_TYPE = 'TABLE'
/

List tablespaces and datafiles

List tablespaces and datafiles:

-- list all tablespaces with their associated files, the
-- tablespace's allocated space, free space, and the
-- next free extent:

clear breaks
SET linesize 130
SET pagesize 60
break ON tablespace_name skip 1
col tablespace_name format a15
col file_name format a50
col tablespace_kb heading 'TABLESPACE|TOTAL KB'
col kbytes_free heading 'TOTAL FREE|KBYTES'

SELECT dd.tablespace_name tablespace_name, dd.file_name file_name, dd.bytes/1024 TABLESPACE_KB, SUM(fs.bytes)/1024 KBYTES_FREE, MAX(fs.bytes)/1024 NEXT_FREE
FROM sys.dba_free_space fs, sys.dba_data_files dd
WHERE dd.tablespace_name = fs.tablespace_name
AND dd.file_id = fs.file_id
GROUP BY dd.tablespace_name, dd.file_name, dd.bytes/1024
ORDER BY dd.tablespace_name, dd.file_name;

-- list datafiles, tablespace names, and size in MB:

col file_name format a50
col tablespace_name format a10

SELECT file_name, tablespace_name, ROUND(bytes/1024000) MB
FROM dba_data_files
ORDER BY 1;

-- list tablespaces, size, free space, and percent free
-- query originally developed by Michael Lehmann

SELECT df.tablespace_name TABLESPACE, df.total_space TOTAL_SPACE,
fs.free_space FREE_SPACE, df.total_space_mb TOTAL_SPACE_MB,
(df.total_space_mb - fs.free_space_mb) USED_SPACE_MB,
fs.free_space_mb FREE_SPACE_MB,
ROUND(100 * (fs.free_space / df.total_space),2) PCT_FREE
FROM (SELECT tablespace_name, SUM(bytes) TOTAL_SPACE,
      ROUND(SUM(bytes) / 1048576) TOTAL_SPACE_MB
      FROM dba_data_files
      GROUP BY tablespace_name) df,
     (SELECT tablespace_name, SUM(bytes) FREE_SPACE,
       ROUND(SUM(bytes) / 1048576) FREE_SPACE_MB
       FROM dba_free_space
       GROUP BY tablespace_name) fs
WHERE df.tablespace_name = fs.tablespace_name(+)
ORDER BY fs.tablespace_name;

Concurrent request SQL query

Show all running concurrent requests with the column that includes SQL query:

set pages 9999 feed on lines 150
col user_concurrent_program_name format a40 head PROGRAM trunc
col elapsed format 9999
col request_id format 9999999 head REQUEST
col user_name format a12
col oracle_process_id format a5 head OSPID
col inst_name format a10
col sql_text format a30
col outfile_tmp format a30
col logfile_tmp format a30
select /*+ ordered */
       fcp.user_concurrent_program_name
,      fcr.request_id
,      round(24*60*( sysdate - actual_start_date )) elapsed
,      fu.user_name
,      fcr.oracle_process_id
,      sess.sid
,      sess.serial#
,      inst.inst_name
,      sa.sql_text
,      cp.plsql_dir || '/' || cp.plsql_out outfile_tmp
,      cp.plsql_dir || '/' || cp.plsql_log logfile_tmp
from   apps.fnd_concurrent_requests fcr
,      apps.fnd_concurrent_programs_tl fcp
,      apps.fnd_concurrent_processes cp
,      apps.fnd_user fu
,      gv$process pro
,      gv$session sess
,      gv$sqlarea sa
,      sys.v_$active_instances inst
where  fcp.concurrent_program_id = fcr.concurrent_program_id
and    fcp.application_id = fcr.program_application_id
and    fcr.controlling_manager = cp.concurrent_process_id
and    fcr.requested_by = fu.user_id (+)
and    fcr.oracle_process_id = pro.spid (+)
and    pro.addr = sess.paddr (+)
and    pro.inst_id = sess.inst_id (+)
and    sess.sql_address = sa.address (+)
and    sess.sql_hash_value = sa.hash_value (+)
and    sess.inst_id = inst.inst_number (+)
and    fcr.phase_code = 'R' /* only running requests */

Dektop environment on Oracle Linux 6

Install GUI desktop in Oracle Linux 6:

yum groupinstall "GNOME Desktop Environment" "X Window System" "Desktop"

Delete all user objects script

SET SERVEROUTPUT ON SIZE 1000000
BEGIN
  FOR cur_rec IN (SELECT object_name, object_type
                  FROM   user_objects
                  WHERE  object_type IN ('TABLE', 'VIEW', 'PACKAGE', 'PROCEDURE', 'FUNCTION', 'SEQUENCE')) LOOP
    BEGIN
      IF cur_rec.object_type = 'TABLE' THEN
        EXECUTE IMMEDIATE 'DROP ' || cur_rec.object_type || ' "' || cur_rec.object_name || '" CASCADE CONSTRAINTS';
      ELSE
        EXECUTE IMMEDIATE 'DROP ' || cur_rec.object_type || ' "' || cur_rec.object_name || '"';
      END IF;
    EXCEPTION
      WHEN OTHERS THEN
        DBMS_OUTPUT.put_line('FAILED: DROP ' || cur_rec.object_type || ' "' || cur_rec.object_name || '"');
    END;
  END LOOP;
END;
/

DB Memory/SGA queries

Memory usage and utilization

Query 1:

select round(used.bytes /1024/1024 ,2) used_mb
,round(free.bytes /1024/1024 ,2) free_mb
,round(tot.bytes /1024/1024 ,2) total_mb
from (select sum(bytes) bytes
from v$sgastat
where name != 'free memory') used
,(select sum(bytes) bytes
from v$sgastat
where name = 'free memory') free
,(select sum(bytes) bytes
from v$sgastat) tot

Query 2:

select sum(bytes)/1024/1024 " SGA size used in MB" from v$sgastat where name!='free memory'; 

Query 3:

-- |----------------------------------------------------------------------------|
-- | DATABASE : Oracle                                                          |
-- | FILE     : perf_sga_usage.sql                                              |
-- | CLASS    : Tuning                                                          |
-- | PURPOSE  : Report on all components within the SGA.                        |
-- | NOTE     : As with any code, ensure to test this script in a development   |
-- |            environment before attempting to run it in production.          |
-- +----------------------------------------------------------------------------+
SET LINESIZE 145
SET PAGESIZE 9999
SET FEEDBACK off
SET VERIFY   off
COLUMN bytes   FORMAT  999,999,999
COLUMN percent FORMAT  999.99999
break on report
compute sum of bytes on report
compute sum of percent on report
SELECT
    a.name
  , a.bytes
  , a.bytes/(b.sum_bytes*100)  Percent
FROM sys.v_$sgastat a
   , (SELECT SUM(value)sum_bytes FROM sys.v_$sga) b
ORDER BY bytes DESC
/

Unzip multiple zip filkes - linux

How to unzip multiple zip files at once:

unzip '*.zip';

or

for z in *.zip; do unzip $z; done

Unable to open swap file for fstab , recovery impossible

In order to make it able to edit fstab file, type next as root:

mount -w -o remount

or

mount -o remount,rw /

Oracle Ebs R12 components versions

Oracle AS10g version

cat $CONTEXT_FILE | grep ias_version

Clear cache EBS 11i and R12

Clear cache in Apache/iAS, Cabo, browser, Java, Jinitiator, portal, webadi

Apache / iAS 
For 11i and earlier versions:
- shutdown iAS server
- go to $OA_HTML (for 11.5.9) or $COMMON_TOP (for 11.5.10.x) directory
- backup the directory _pages and delete its contents by running for instance:

rm -rf $COMMON_TOP/_pages/*

- for modplsql caches remove contents of $IAS_ORACLE_HOME/Apache/modplsql/cache directory
- restart iAS server

To clear middle tier cache in release 12:
(please review Note 759038.1 for details)

- go to "Functional Administrator" responsibility
- select Core Services => Caching Framework => Global Configuration => Clear cache

In case you have login issue after accidentally cleared the _pages instead of using the method above for r12
please review Note 433386.1 to recompile jsp files.

Cabo
Images and style sheets can be corrupted or out of sync in the cabo caches,
you may need to clear the related directories after backup:

$OA_HTML/cabo/images/cache
$OA_HTML/cabo/styles/cache
Web Browser

for Internet Explorer:
- go to menu Tools => Internet Options,
- select 'General' tab,
- click on button 'Delete Files' in 'Temporary Internet files' area
or
- click on button 'Delete...' then in 'Delete Browsing History' pop-up window click on 'Delete Files...'
- close all IE windows and restart new browser session.

for Mozilla Firefox:
- go to menu Tools
- select 'Clear Private Data...' or 'Clear Recent History...' then check 'Cache'
or
- go to menu Edit or Tools
- select Preferences or Options.
- expand the 'Advance' options and choose 'Cache' or Privacy
- click the button called 'Clear Cache'.

Jinitiator 

Two possibilities depending of the Jinitiator version:

for 1.1.8.x versions:
- delete all files in directory:

C:\Program Files\Oracle\Jinitiator \jcache\

for 1.3.1.x versions:
- go to Start => Parameters => Control Panel
- double-click on "Jinitiator " icon
- in the new pop-up window, click on "Cache" tab
- click on "Clear Jar Cache " button. On prompt, click Yes.

(you can also delete directly all files under directory:
C:\Documents and Settings\\Oracle Jar Cache)


Java
Java/JRE plug-in (Windows)
- go to Start => Parameters => Control Panel
- double-click on 'Java' icon
- in the new pop-up window, click on 'General' tab
- click on 'Setting...' button in 'Temporary Internet files' area then click on 'Delete Files...' button
(you can select Applets, Applications or other files)

JVM

- go to responsibility "Functional Administrator"
- click on "Core Services" tab then the "Caching Framework" sub-tab
- click on "Global Configuration" link then click on "Clear all cache" button to clear all of the Java Cache's


Portal
- go to the url: http://:/pls/admin_/gateway.htm or cache.htm
- click on "Cache Settings" option and note the "Cache Directory"
- go to this directory and delete all the cache files in the directory and sub directories

See also modplsql caches in Apache/iAS section above.


WebADI / BNE cache
For instance when enabling BNE log
- go to url: http://:/oa_servlets/oracle.apps.bne.framework.BneAdminServlet
- click on the "clear-cache" link
- at the bottom of the page you should see 'Cache Cleared'

Script to check DB performance

Script to check DB performace:

set serveroutput on
declare
cursor c1 is select version
from v$instance;
cursor c2 is
    select
          host_name
       ,  instance_name
       ,  to_char(sysdate, 'HH24:MI:SS DD-MON-YY') currtime
       ,  to_char(startup_time, 'HH24:MI:SS DD-MON-YY') starttime
     from v$instance;
cursor c4 is
select * from (SELECT count(*) cnt, substr(event,1,50) event
FROM v$session_wait
WHERE wait_time = 0
AND event NOT IN ('smon timer','pipe get','wakeup time manager','pmon timer','rdbms ipc message',
'SQL*Net message from client')
GROUP BY event
ORDER BY 1 DESC) where rownum <6;
cursor c5 is
select round(sum(value)/1048576) as sgasize from v$sga;
cursor c6 is select round(sum(bytes)/1048576) as dbsize
from v$datafile;
cursor c7 is select 'top physical i/o process' category, sid,
       username, total_user_io amt_used,
       round(100 * total_user_io/total_io,2) pct_used
from (select b.sid sid, nvl(b.username, p.name) username,
             sum(value) total_user_io
      from v$statname c, v$sesstat a,
           v$session b, v$bgprocess p
      where a.statistic# = c.statistic#
      and p.paddr (+) = b.paddr
      and b.sid = a.sid
      and c.name in ('physical reads', 'physical writes',
                     'physical reads direct',
                     'physical reads direct (lob)',
                     'physical writes direct',
                     'physical writes direct (lob)')
      and b.username not in ('SYS', 'SYSTEM', 'SYSMAN', 'DBSNMP')
      group by b.sid, nvl(b.username, p.name)
      order by 3 desc),
     (select sum(value) total_io
      from v$statname c, v$sesstat a
      where a.statistic# = c.statistic#
      and c.name in ('physical reads', 'physical writes',
                       'physical reads direct',
                       'physical reads direct (lob)',
                       'physical writes direct',
                       'physical writes direct (lob)'))
where rownum < 2
union all
select 'top logical i/o process', sid, username,
       total_user_io amt_used,
       round(100 * total_user_io/total_io,2) pct_used
from (select b.sid sid, nvl(b.username, p.name) username,
             sum(value) total_user_io
      from v$statname c, v$sesstat a,
           v$session b, v$bgprocess p
      where a.statistic# = c.statistic#
      and p.paddr (+) = b.paddr
      and b.sid = a.sid
      and c.name in ('consistent gets', 'db block gets')
      and b.username not in ('SYS', 'SYSTEM', 'SYSMAN', 'DBSNMP')
      group by b.sid, nvl(b.username, p.name)
      order by 3 desc),
     (select sum(value) total_io
      from v$statname c, v$sesstat a,
           v$session b, v$bgprocess p
      where a.statistic# = c.statistic#
      and p.paddr (+) = b.paddr
 and b.sid = a.sid
      and c.name in ('consistent gets', 'db block gets'))
where rownum < 2
union all
select 'top memory process', sid,
       username, total_user_mem,
       round(100 * total_user_mem/total_mem,2)
from (select b.sid sid, nvl(b.username, p.name) username,
             sum(value) total_user_mem
      from v$statname c, v$sesstat a,
           v$session b, v$bgprocess p
      where a.statistic# = c.statistic#
      and p.paddr (+) = b.paddr
      and b.sid = a.sid
      and c.name in ('session pga memory', 'session uga memory')
      and b.username not in ('SYS', 'SYSTEM', 'SYSMAN', 'DBSNMP')
      group by b.sid, nvl(b.username, p.name)
      order by 3 desc),
     (select sum(value) total_mem
      from v$statname c, v$sesstat a
      where a.statistic# = c.statistic#
      and c.name in ('session pga memory', 'session uga memory'))
where rownum < 2
union all
select 'top cpu process', sid, username,
       total_user_cpu,
       round(100 * total_user_cpu/greatest(total_cpu,1),2)
from (select b.sid sid, nvl(b.username, p.name) username,
             sum(value) total_user_cpu
      from v$statname c, v$sesstat a,
           v$session b, v$bgprocess p
      where a.statistic# = c.statistic#
      and p.paddr (+) = b.paddr
      and b.sid = a.sid
      and c.name = 'CPU used by this session'
      and b.username not in ('SYS', 'SYSTEM', 'SYSMAN', 'DBSNMP')
      group by b.sid, nvl(b.username, p.name)
      order by 3 desc),
     (select sum(value) total_cpu
      from v$statname c, v$sesstat a,
           v$session b, v$bgprocess p
      where a.statistic# = c.statistic#
      and p.paddr (+) = b.paddr
      and b.sid = a.sid
      and c.name = 'CPU used by this session')
where rownum < 2;

cursor c8 is select username, sum(VALUE/100) cpu_usage_sec
from v$session ss, v$sesstat se, v$statname sn
where se.statistic# = sn.statistic#
and name like '%CPU used by this session%'
and se.sid = ss.sid
and username is not null
and username not in ('SYS', 'SYSTEM', 'SYSMAN', 'DBSNMP')
group by username
order by 2 desc;
begin
dbms_output.put_line ('Database Version');
dbms_output.put_line ('-----------------');
for rec in c1
loop
dbms_output.put_line(rec.version);
end loop;
dbms_output.put_line( chr(13) );
dbms_output.put_line('Hostname');
dbms_output.put_line ('----------');
for rec in c2
loop
     dbms_output.put_line(rec.host_name);
end loop;
dbms_output.put_line( chr(13) );
dbms_output.put_line('SGA Size (MB)');
dbms_output.put_line ('-------------');
for rec in c5
loop
     dbms_output.put_line(rec.sgasize);
end loop;
dbms_output.put_line( chr(13) );
dbms_output.put_line('Database Size (MB)');
dbms_output.put_line ('-----------------');
for rec in c6
loop
     dbms_output.put_line(rec.dbsize);
end loop;
dbms_output.put_line( chr(13) );
dbms_output.put_line('Instance start-up time');
dbms_output.put_line ('-----------------------');
for rec in c2 loop
 dbms_output.put_line( rec.starttime );
  end loop;
dbms_output.put_line( chr(13) );
  for b in
    (select total, active, inactive, system, killed
    from
       (select count(*) total from v$session)
     , (select count(*) system from v$session where username is null)
     , (select count(*) active from v$session where status = 'ACTIVE' and username is not null)

     , (select count(*) inactive from v$session where status = 'INACTIVE')
     , (select count(*) killed from v$session where status = 'KILLED')) loop
dbms_output.put_line('Active Sessions');
dbms_output.put_line ('---------------');
dbms_output.put_line(b.total || ' sessions: ' || b.inactive || ' inactive,' || b.active || ' active, ' || b.system || ' system, ' || b.killed || ' killed ');
  end loop;
  dbms_output.put_line( chr(13) );
 dbms_output.put_line( 'Sessions Waiting' );
  dbms_output.put_line( chr(13) );
dbms_output.put_line('Count      Event Name');
dbms_output.put_line('-----      -----------------------------------------------------');
for rec in c4
loop
dbms_output.put_line(rec.cnt||'          '||rec.event);
end loop;
dbms_output.put_line( chr(13) );

dbms_output.put_line('-----      -----------------------------------------------------');

dbms_output.put_line('TOP Physical i/o, logical i/o, memory and CPU processes');
dbms_output.put_line ('---------------');
for rec in c7
loop
dbms_output.put_line (rec.category||': SID '||rec.sid||' User : '||rec.username||': Amount used : '||rec.amt_used||': Percent used: '||rec.pct_used);
end loop;

dbms_output.put_line('------------------------------------------------------------------');

dbms_output.put_line('TOP CPU users by usage');
dbms_output.put_line ('---------------');
for rec in c8
loop

dbms_output.put_line (rec.username||'--'||rec.cpu_usage_sec);
dbms_output.put_line ('---------------');
end loop;

end;

Find UNUSABLE indexes in Oracle DB 11g

Find all unusable indexes in db

select owner, index_name from dba_indexes where status='UNUSABLE';

Rebuild index

alter index <INDEX_NAME> rebuild;

Oracle Linux add ip adress - by editing files

How to edit network card settings through configuration files:

Find and edit /etc/sysconfig/network-scripts/ifcfg-eth0 or /etc/sysconfig/network-scripts/ifcfg-eth1(secondary network card) file(s).

Append or modify:

DEVICE=eth0
BOOTPROTO=static
DHCPCLASS=
HWADDR=00:31:48:56:A6:2E
IPADDR=192.168.13.25
NETMASK=255.255.255.0
ONBOOT=yes

Then /etc/sysconfig/network file append or modify:

NETWORKING=yes
HOSTNAME=sir.acid.com
GATEWAY=10.10.1.10

Finally DNS servers in /etc/resolv.conf

nameserver 10.10.1.10
nameserver 10.10.1.11
nameserver 10.10.11.12

/etc/init.d/network restart

or

service network restart

Check if DB is using pfile or spfile

Check if DB is using pfile or spfile:

SELECT DECODE(value, NULL, 'PFILE', 'SPFILE') "Init File Type"
FROM sys.v_$parameter WHERE name = 'spfile';

Session lock history

Around 2hrs of recent data 

WITH ash_query AS (
SELECT substr(event,6,2) lock_type,program,
h.module, h.action, object_name, h.session_id, h.session_serial#, h.sample_time,
SUM(time_waited)/1000 time_ms, COUNT( * ) waits,
username, sql_text,
RANK() OVER (ORDER BY SUM(time_waited) DESC) AS time_rank,
ROUND(SUM(time_waited) * 100 / SUM(SUM(time_waited))
OVER (), 2) pct_of_time
FROM v$active_session_history h
JOIN dba_users u USING (user_id)
LEFT OUTER JOIN dba_objects o
ON (o.object_id = h.current_obj#)
LEFT OUTER JOIN v$sql s USING (sql_id)
WHERE event LIKE 'enq: %'
GROUP BY substr(event,6,2) ,program, h.module, h.action,
object_name, h.session_id, h.session_serial#, h.sample_time, sql_text, username)
SELECT lock_type,module, username, object_name, session_id, session_serial#, sample_time, sql_text, time_ms,pct_of_time
FROM ash_query
WHERE time_rank < 11
ORDER BY time_rank;

Weeks...

WITH ash_query AS (
SELECT substr(event,6,2) lock_type,program,
h.module, h.action, object_name, h.session_id, h.session_serial#, h.sample_time,
SUM(time_waited)/1000 time_ms, COUNT( * ) waits,
username, sql_text,
RANK() OVER (ORDER BY SUM(time_waited) DESC) AS time_rank,
ROUND(SUM(time_waited) * 100 / SUM(SUM(time_waited))
OVER (), 2) pct_of_time
FROM dba_hist_active_sess_history h
JOIN dba_users u USING (user_id)
LEFT OUTER JOIN dba_objects o
ON (o.object_id = h.current_obj#)
LEFT OUTER JOIN v$sql s USING (sql_id)
WHERE event LIKE 'enq: %'
GROUP BY substr(event,6,2) ,program, h.module, h.action,
object_name, h.session_id, h.session_serial#, h.sample_time, sql_text, username)
SELECT lock_type,module, username, object_name, session_id, session_serial#, sample_time, sql_text, time_ms,pct_of_time
FROM ash_query
WHERE time_rank < 11
ORDER BY time_rank;

Install Apex+Glassfish+ApexListener

Installation will be performed on Oracle Linux 6u3 x64 with Oracle database 11.2.0.3 installed and configured.

1. Download latest versions of Apex, Glassfish server and ApexListener
2. Create tablespace in database(this is optional as you can install apex in any tablespace, for example SYSAUX)

CREATE TABLESPACE APEX_TS DATAFILE
  '/u01/app/oracle/oradata/fdapex/apex01.dbf' SIZE 2048M AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED
LOGGING
ONLINE
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON;

2. Unzip Apex installation, go to unpacked Apex folder and as SYS user connectand run the installation:

SQL>@apexins APEX_TS APEX_TS TEMP /i/

 3. When installation is finished change the admin user password. Be aware that admin password should contain one upper letter and one of the punctation marks.
SQL>@apxchpwd

4. As SYS user define password for APEX_PUBLIC_USER

SQL>alter user APEX_PUBLIC_USER identified by <new_password>

5. Install JDK latest version
6. Install Glassfish server

sh Glassfish-4.0-unix.sh

Follow the screens:

7. Enable secure admin

asadmin enable-secure-admin

If there is a problem with this then define admin user password:

asadmin change-admin-password

here current password is blank, just press enter and then define new password.

8. Create service for automatic start with computer boot:

asadmin create-service

9. Install apex-listener

-Copy images folder from Apex unzipped folder to specific location, for example /home/oracle/apex/images

-Unzip apex listener archive

Define configuration directory

java -jar apex.war configdir /home/oracle/apex

then configure database communication 

java -jar apex.war setup

then configure images folder

java -jar apex.war static /home/oracle/apex/images

10. Deploy apex.war and i.war

 Login to Glasswish admin console

localhost:4848

To install the deployment:

On the navigation tree, click the Application node.

The Applications page displays.

Click the Deploy button.

The Deploy Applications or Modules page displays.

Select Packaged File to be Uploaded to the Server and click Browse.

Navigate to the location of the apex.war file, select the file, and click Open.

The Deploy Applications or Modules page displays.

On the Deploy Applications or Modules page, specify the following:

Type: Web Application

Context Root: apex

Application Name: apex

Status: Enabled

Description: Application Express Listener

Accept all other default settings and click OK.

Repeat the previous steps to deploy the i.war file. Clear the Context Root field so that the context root set in the sun-web.xml is used.

Start Apex:

http://localhost:8080/apex

If for some reason you get blank screen when trying to access this page, please check and apply patch number 16760897 to Apex installation. Check README.txt

Thats all!

Vncserver on Oracle Linux 6u3

How to install vncserver on Oracle linux 6u3 default installation(without x server)

yum install tigervnc-server
yum install xorg-x11-twm
yum install xterm
yum install xsetroot

Thats it!

Find & Kill Linux processes

Recently i wanted to use umount command in order to unmount some drive. I stopped all apps working on that drive with kill -9 <PID>, all user processes pkill -u <user>, but no luck when i use command umount <mount_point> i get the following error..

umount2: Device or resource busy

So i found some smart people that gave me some interesting command:

This one lists processes that is holding he mount point...

fuser -vm <mount_point>

an this one kills...

fuser -km <mount_point>

And then umount <mount_point> works...

Regards!

System Hold, Fix Manager before resetting counters

Concurrent Manager showing status “System Hold, Fix Manager before resetting counters”.

Solution:
To implement the solution, please execute the following steps:

1. Stop all middle tier services including the concurrent managers.
Please make sure that no FNDLIBR, FNDSM, or any dead process is
running.

2. Go to cd $FND_TOP/bin

$ adrelink.sh force=y link_debug=y "fnd FNDLIBR"
$ adrelink.sh force=y link_debug=y "fnd FNDFS"
$ adrelink.sh force=y link_debug=y "fnd FNDCRM"
$ adrelink.sh force=y link_debug=y "fnd FNDSM"

3. Run the CMCLEAN.SQL script from the referenced note below (don’t forget to commit).
Note 134007.1 CMCLEAN.SQL – Non Destructive Script to Clean Concurrent Manager Tables

4. Start the concurrent manager.

5. Retest the issue.

Reference :
SCHEDULE/PRERELEASER MANAGER STATUS : SYSTEM HOLD, FIX MANAGER BEFORE RESETTING [ID 985835.1]

If this does not help and you recently updated on version 12.1.3 then please apply patch from note:

Upgrade 12.1.3: PO Document Approval POXCON, Receiving Transaction RCVOLTM and Inventory Remote INCTM Managers Do Not Start [ID 1413393.1]

Kill concurrent request through database

Find the concurrent request:

SELECT ses.sid,
ses.serial#
FROM v$session ses,
v$process pro
WHERE ses.paddr = pro.addr
AND pro.spid IN (SELECT oracle_process_id
FROM fnd_concurrent_requests
WHERE request_id =<request_id>); 

Kill the database session

SQL> ALTER SYSTEM KILL SESSION ' <sid>, <serial>' IMMEDIATE;

Terminate the request

update fnd_concurrent_requests set status_code='X', phase_code='C' where request_id=<request_id>;
commit;

Change the status to Completed

update fnd_concurrent_requests set status_code='E', phase_code='C' where request_id=<request_id>;
commit;

Status Codes
E -  Error
X -  Terminate
G -  Warning


cancel all scheduled concurrent programs

sqlplus apps/apps
UPDATE fnd_concurrent_requests
SET phase_code = 'C', status_code = 'X'
WHERE status_code IN ('Q','I')
AND requested_start_date > SYSDATE
AND hold_flag = 'N';
COMMIT;

Cancel all running concurrent programs.

sqlplus apps/apps
UPDATE fnd_concurrent_requests
SET phase_code = 'C', status_code = 'X'
WHERE status_code IN ('R','I');
commit;

To Cancel Specific request:

sqlplus apps/apps
update fnd_concurrent_requests
set status_code='D', phase_code='C'
where request_id=<request id>;
commit;

Check if tar and tar.gz is currupted or not

To test the gzip file is not corrupt:

gunzip -t file.tar.gz

To test the tar file inside is not corrupt:

gunzip -c file.tar.gz | tar t > /dev/null

Vncserver error Oracle Linux 6u3

Recently i encountred an error after tryinfg to start vncserver on Oracle linux 6 update 3:

/usr/bin/Xvnc: symbol lookup error: /usr/bin/Xvnc: undefined symbol: pixman_composite_trapezoids
/usr/bin/Xvnc: symbol lookup error: /usr/bin/Xvnc: undefined symbol: pixman_composite_trapezoids

Solution to this problem is to run and install:

yum install pixman pixman-devel libXfont

and the problem solved :)

Linux Runlevels

Linux Standard Base specification

ID	Name	Description
0	Halt	Shuts down the system.
1	Single-user Mode	Mode for administrative tasks.
2	Multi-user Mode	Does not configure network interfaces and does not export networks services.
3	Multi-user Mode with Networking	Starts the system normally.
4	Not used/User-definable	For special purposes.
5	Start the system normally with appropriate display manager. ( with GUI )	As runlevel 3 + display manager.
6	Reboot	Reboots the system.

Debian GNU/Linux

ID	Description
S	Only run on boot (replaces /etc/rc.boot)
0	Halt
1	Single-user mode
2-5	Full Multi-user with console logins and display manager if installed
6	Reboot

Red Hat Linux and Fedora

Code	Information
0	Halt
1	Single-user mode
2	Multi-user mode console logins only (without networking)
3	Multi-user mode, console logins only
4	Not used/User-definable
5	Multi-user mode, with display manager as well as console logins (X11)
6	Reboot

SUSE Linux

ID	Description
0	Halt
1 or S	Single-user mode
2	Multi-user mode without networking
3	Multi-user mode, console logins only
4	Not used/User-definable
5	Multi-user mode with display manager
6	Reboot

Slackware Linux

ID	Description
0	Halt
1	Single-user mode
2	Unused but configured the same as runlevel 3
3	Multi-user mode without display manager
4	Multi-user mode with display manager
5	Unused but configured the same as runlevel 3
6	Reboot

Arch Linux

ID	Description
0	Halt
1	Single-user (Maintenance Mode)
2	Not used
3	Multi-user
4	Not used
5	Multi-user with X11
6	Reboot

Gentoo Linux

ID	Description
0	Halt
1 or S	Single-user mode
2	Multi-user mode without networking
3	Multi-user mode
4	Aliased for runlevel 3
5	Aliased for runlevel 3
6	Reboot

Unix:

System V Releases 3 and 4

ID	Description
0	Shut down system, power-off if hardware supports it (only available from the console)
1	Single-user mode, all filesystems unmounted but root, all processes except console processes killed
2	Multi-user mode
3	Multi-user mode with RFS (and NFS in Release 4) filesystems exported
4	Multi-user, User-definable
5	Halt the operating system, go to firmware
6	Reboot
s, S	Identical to 1, except current terminal acts as the system console

Solaris

ID	Description
0	Operating system halted; (SPARC only) drop to OpenBoot prompt
S	Single-user mode with only root filesystem mounted (as read-only) -- Solaris 10+: svc:/milestone/single-user
1	Single-user mode with all local filesystems mounted (read-write)
2	Multi-user mode with most daemons started – Solaris 10+: svc:/milestone/multi-user
3	Multi-user mode; identical to 2 (runlevel 3 runs both /sbin/rc2 and /sbin/rc3), with filesystems exported, plus some other network services started. -- Solaris 10+: svc:/milestone/multi-user-server
4	Alternative Multi-user mode, User-definable
5	Shut down, power-off if hardware supports it
6	Reboot

HP-UX

ID	Description
0	System halted
S	Single-user mode, booted to system console only, with only root filesystem mounted (as read-only)
s	Single-user mode, identical to S except the current terminal acts as the system console
1	Single-user mode with local filesystems mounted (read-write)
2	Multi-user mode with most daemons started and Common Desktop Environment launched
3	Identical to runlevel 2 with NFS exported
4	Multi-user mode with VUE started instead of CDE
5, 6	Not used/User-definable

AIX

ID	Name	Description
0		reserved
1		reserved
2	Normal Multi-user mode	default mode

Oracle 11g DB preparation of installation on Oracle linux

I assume that Oracle linux with SELINUX=permissive(/etc/selinux/config) is already installed and that Oracle DB software package is already downloaded.

1. Edit hosts file
<IP-address>  <fully-qualified-machine-name>  <machine-name>

192.168.138.125 ocpserver.com ocpserver

2.Add public YUM repo(For Oracle Linux 6)

cd /etc/yum.repos.d
wget http://public-yum.oracle.com/public-yum-ol6.repo

3.Setup Oracle preriquisites automatically

yum install oracle-rdbms-server-11gR2-preinstall

This package is installing packages, create users and change parameters

4. Or manually...
Oracle recommend the following minimum parameter settings.

fs.suid_dumpable = 1
fs.aio-max-nr = 1048576
fs.file-max = 6815744
kernel.shmall = 2097152
kernel.shmmax = 536870912
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
net.ipv4.ip_local_port_range = 9000 65500
net.core.rmem_default = 262144
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 1048586

The current values can be tested using the following command.
/sbin/sysctl -a | grep <param-name>

Add or amend the following lines in the "/etc/sysctl.conf" file.

fs.suid_dumpable = 1
fs.aio-max-nr = 1048576
fs.file-max = 6815744
kernel.shmall = 2097152
kernel.shmmax = 536870912
kernel.shmmni = 4096
# semaphores: semmsl, semmns, semopm, semmni
kernel.sem = 250 32000 100 128
net.ipv4.ip_local_port_range = 9000 65500
net.core.rmem_default=4194304
net.core.rmem_max=4194304
net.core.wmem_default=262144
net.core.wmem_max=1048586

Run the following command to change the current kernel parameters.

/sbin/sysctl -p

Add the following lines to the "/etc/security/limits.conf" file.

oracle              soft    nproc   2047
oracle              hard    nproc   16384
oracle              soft    nofile  4096
oracle              hard    nofile  65536
oracle              soft    stack   10240

5. Check if the following packages are present, if not install them

rpm -Uvh binutils-2.*
rpm -Uvh compat-libstdc++-33*
rpm -Uvh compat-libstdc++-33*.i386.rpm
rpm -Uvh elfutils-libelf*
rpm -Uvh gcc-4.*
rpm -Uvh gcc-c++-4.*
rpm -Uvh glibc-2.*
rpm -Uvh glibc-common-2.*
rpm -Uvh glibc-devel-2.*
rpm -Uvh glibc-headers-2.*
rpm -Uvh ksh*
rpm -Uvh libaio-0.*
rpm -Uvh libaio-devel-0.*
rpm -Uvh libgomp-4.*
rpm -Uvh libgcc-4.*
rpm -Uvh libstdc++-4.*
rpm -Uvh libstdc++-devel-4.*
rpm -Uvh make-3.*
rpm -Uvh sysstat-7.*
rpm -Uvh unixODBC-2.*
rpm -Uvh unixODBC-devel-2.*
rpm -Uvh numactl-devel-*

6. Create groups and users

groupadd oinstall
groupadd dba
groupadd oper
groupadd asmadmin
useradd -g oinstall -G dba,oper,asmadmin oracle
passwd oracle

7. Create software directories

mkdir -p /u01/app/oracle/product/11.2.0/db_1
chown -R oracle:oinstall /u01
chmod -R 775 /u01

8. As oracle user add to .bash_profile.scr. Of course adapt to your configuration.

# Oracle Settings
TMP=/tmp; export TMP
TMPDIR=$TMP; export TMPDIR
ORACLE_HOSTNAME=ocpserver.com; export ORACLE_HOSTNAME
ORACLE_UNQNAME=ocp; export ORACLE_UNQNAME
ORACLE_BASE=/u01/app/oracle; export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1; export ORACLE_HOME
ORACLE_SID=ocp; export ORACLE_SID
PATH=/usr/sbin:$PATH; export PATH
PATH=$ORACLE_HOME/bin:$PATH; export PATH
LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib; export LD_LIBRARY_PATH
CLASSPATH=$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib; export CLASSPATH

 9. As root user run

xhost +

10. Login as oracle user and start installation

./runInstaller

11. Install Oracle DB!

12. Edit /etc/oratab file
OCP:/u01/app/oracle/product/11.2.0/db_1:Y

Enable Database EM Console in Oracle EBS R12

Everythin explained in How to Enable Enterprise Manager on the Oracle E-Business Suite Release 12 [ID 458533.1]

in short....

1. Source db env variable
2.Create a password file(check the [Note 358201.1])

orapwd file=$ORACLE_HOME/dbs/orapw$ORACLE_SID password=passw0rd entries=5

3.Create the Enterprise Manager repository

emca -config dbcontrol db -repos create

note: If for some reason you want to drop repository(check the [Note 278100.1])

emca -deconfig dbcontrol db -repos drop

regards,
S

Change host name in xenserver

Command to change host name in xenserver

xe host-set-hostname-live host-uuid=INSERTUUIDHERE host-name=INSERTHOSTNAMEHERE

Citrix Xen 6 add new disk

First check the new added disk with command

fdisk -l

Disk /dev/sda: 250.0 GB, 250059350016 bytes
255 heads, 63 sectors/track, 30401 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes

Device Boot Start End Blocks Id System
/dev/sda1 * 1 499 4008186 83 Linux
/dev/sda2 500 998 4008217+ 83 Linux
/dev/sda3 999 30401 236179597+ 83 Linux

Disk /dev/sdb: 250.0 GB, 250059350016 bytes
255 heads, 63 sectors/track, 30401 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Disk /dev/sdb doesn't contain a valid partition table

So you see wee have one raw disk /dev/sdb.

Now you have to check the ID of the host:

xe host-list

uuid ( RO) : ba3d140c-3de5-499b-b831-7c40d82958a8
name-label ( RW): xen_test
name-description ( RO): Default install of XenServer

You have 3 options to create new disk in Citrix Xen
1. Create normal LVHD storage repository:

xe sr-create host-uuid=ba3d140c-3de5-499b-b831-7c40d82958a8
content-type=user type=lvm device-config:device=/dev/sdb
shared=false name-label="Local storage 2"

2.Create a LVHD storage repository with thin provisioning support.

xe sr-create host-uuid=ba3d140c-3de5-499b-b831-7c40d82958a8
content-type=user type=lvm device-config:device=/dev/sdb
shared=false name-label="Local storage 2" smconfig:allocation=thin

3.Create a EXT storage repository with thin provisioning support and direct access to

the vhd files.

xe sr-create host-uuid=ba3d140c-3de5-499b-b831-7c40d82958a8
content-type=user type=ext device-config:device=/dev/sdb
shared=false name-label="Local storage 2"

Remove Disk from XEN server 

Find the SR uuid of the disk:

xe sr-list name-label=<NAME OF DISK>

Find the PBD of the disk(search by UUID)

xe pbd-list sr-uuid=<SR UUID>

Unplug the disk by PBD

xe pbd-unplug uuid=<UUID of PBD>

Forget the disk:

sr-forget uuid=<UUID of SR>

Extract FND Log per user

1. Get the user ID

select USER_ID
from fnd_user
where user_name= <'USERNAME'>

2. Get the starting Max log sequence for user

select max(LOG_SEQUENCE)
FROM FND_LOG_MESSAGES
where user_id =<USER_ID>

3. Reproduce the issue
N/A

4. Get the ending Max log sequence for user

SELECT module, message_text
FROM FND_LOG_MESSAGES LOG
WHERE user_id = 1360
AND LOG_SEQUENCE between <LOG_SEQUENCE> and <LOG_SEQUENCE>
ORDER BY LOG.LOG_SEQUENCE;

R12 Tablespace maintanance

How to maintain tablespaces in Oracle Apps R12

--check the segment size in specific tablespace
-----------------------------------------------------------------

col username format a8 justify c heading 'Username'
col extents format 999,999,990 justify c heading '# of Extents'
col segment_type format a15 justify c heading 'Segment Type'
col segment_name format a28 justify c heading 'Segment Name'
col kbytes format 999,999,990 justify c heading 'KB Used'
select
owner username,
segment_name segment_name,
segment_type segment_type,
bytes/1024 kbytes,
extents extents
from
dba_segments
where
tablespace_name = 'APPS_TS_TX_DATA'
order by
kbytes asc
/

select extent_id, file_id, block_id, blocks from dba_extents
where owner=upper('APPLSYS') and segment_name = upper('WF_ITEM_ATTRIBUTE_VALUES');

--check if segments are QUEUE type or if they have type LONG columns. If this queries return results then you should not touch this segments.
-----------------------------------------------------------------

select queue_table from dba_queue_tables
where owner=upper('APPLSYS') and queue_table = upper('WF_ITEM_ATTRIBUTE_VALUES');

select table_name, column_name,data_type from dba_tab_columns
where owner=upper('APPLSYS') and table_name = upper('WF_ITEM_ATTRIBUTE_VALUES')
and data_type in ('LONG','LONG RAW');

--Check the segment type
-----------------------------------------------------------------

select segment_name, segment_type from dba_segments
where owner=upper('APPLSYS') and segment_name = upper('WF_ITEM_ATTRIBUTE_VALUES');

--If segment type is table them move it and rebuild indexes
-----------------------------------------------------------------

alter table APPLSYS.WF_ITEM_ATTRIBUTE_VALUES move;

select owner, index_name, status from dba_indexes
where table_owner = upper('APPLSYS') and
table_name = upper('WF_ITEM_ATTRIBUTE_VALUES');

alter index APPLSYS.WF_ITEM_ATTRIBUTE_VALUES_PK rebuild;

Script to find database locks

Here is one script for finding database locks:

set linesize 150;
set head on;
col sid_serial form a13
col ora_user for a15;
col object_name for a35;
col object_type for a10;
col lock_mode for a15;
col last_ddl for a8;
col status for a10;
break on sid_serial;
SELECT l.session_id||','||v.serial# sid_serial,
       l.ORACLE_USERNAME ora_user,
       o.object_name,
       o.object_type,
       DECODE(l.locked_mode,
          0, 'None',
          1, 'Null',
          2, 'Row-S (SS)',
          3, 'Row-X (SX)',
          4, 'Share',
          5, 'S/Row-X (SSX)',
          6, 'Exclusive',
          TO_CHAR(l.locked_mode)
       ) lock_mode,
       o.status,
       to_char(o.last_ddl_time,'dd.mm.yy') last_ddl
FROM dba_objects o, gv$locked_object l, v$session v
WHERE o.object_id = l.object_id
      and l.SESSION_ID=v.sid
order by 2,3;

And here is the example on how to kill a session

alter system kill session '60,13';

or

alter system disconnect session '60,13' 

You can add POST_TRANSACTION or IMMEDIATE options after SID with DISCONNECT. With KILL session you can add only IMMEDIATE; 

Incomplete recoveries

3 types of recoveries: timed based recovery, log-sequence recovery,

Recover database until time ‘2008-10-23:13:00:00’
Recover database until sequence 34;
Recover database until change 226250;

Opening database after incomplete recovery:

alter database open resetlogs;

Recovering from the Loss of a Tempfile:

ALTER TABLESPACE TEMP ADD TEMPFILE ‘<FILE_LOCATION>/temp01.dbf’SIZE 200m  REUSE AUTOEXTEND ON;

Recovering from the Loss of an Online Redo Log Group

1. Dealing with the Loss of an Inactive Online Redo Log Group Member
Just recreate redo log group member:

alter database add logfile ‘D:\ORACLE\ORADATA\ORCL\REDO02.LOG’ reuse to
group 2;

2. Dealing with the Loss of an Inactive Online Redo Log Group

• During database startup - Drop the log file group then recreate online redo log group 

alter database drop logfile group 2;

alter database add logfile group 2 ‘c:\oracle\oradata\orcl\redo02.log’ size 50m;
alter database add logfile group 2 ‘c:\oracle\oradata\orcl\redo02.log’ size 50m;

• When database is running - force the checkpoint and then clear the log group

alter system checkpoint;
alter database clear logfile group 1;

          If you receive the the following error:

          ERROR at line 1:

          ORA-00350: log 1 of instance orcl (thread 1) needs to be archived

          ORA-00312: online log 1 thread 1: ‘/oracle01/oradata/orcl/redo01.log’ 

          Then you need to clear unarchived log file:

alter database clear unarchived logfile ‘/oracle01/oradata/orcl/redo01.log’;

          then again you issue:

alter database clear logfile group 1; 

3. Dealing with the Loss of an Active but Not Current Online Redo Log Group

alter database clear unarchived logfile ‘/oracle01/oradata/orcl/redo01.log’;

 4. Dealing with the Loss of the Current Online Redo Log Group

alter system checkpoint;
shutdown;
startup mount;
alter database clear unarchived logfile ‘/oracle01/oradata/orcl/redo01.log’;
alter database open

Recovering Lost Control Files with a Backup Control File
recovery from the loss of all control files using backup control file.

1. Restore backup control file to the location where control files should reside(check CONTROL_FILES parameter).
2. mount the database

startup mount;

3. Recover the database with:

recover database using backup controlfile 

4. At the prompt type AUTO to apply all archived logs.
5. If you get an error please run again:

recover database using backup controlfile 

6. At the prompt choose one of the online redo logs .
7. Issue:

alter database open resetlogs;

Recovering Lost Control Files Using the create control file Command 

1. Assuming that you created control file creation script:

alter database backup controlfile to trace;

2.  Manually issue the create control file command but first modify the RESETLOGS or NORESETLOGS parameter.

Recovering from the Loss of the Password File

If you have backup, please restore it to $ORACLE_HOME/dbs

If no backup then:

cd $ORACLE_HOME/dbs
orapwd file=orapw<SID> entries=20 password=acid

where password is the password for SYS user     

Restore views, parameter, commands

Put the data file offline

alter database datafile 4 offline; - data file ID used to identify datafile.

alter database datafile ’<DATA_FILE_LOCATION>\DATAF01.DBF’ offline;

From this view  you can get data file id`s

select file_id, file_name from dba_data_files;

Renaming data file name is used when changing data file location:

alter database rename file ’<DATA_FILE_OLD_LOCATION>/system01.dbf’ to ’<DATA_FILE_NEW_LOCATION>/system01.dbf’;

Recovering database:

recover database
recover tablespace
recover datafile

Figuring Out Which archived Redo logs You need from views:
V$RECOVER_FILE
V$LOG_HISTORY

Select a.file#, a.change#, b.first_change#, b.next_change#, b.sequence# from v$recover_file a, v$log_history b

To find the a name of the archived log use the V$ARCHIVED_LOG view:

Select a.file#, a.change#, b.first_change#, b.next_change#,
b.sequence#, b.name
from v$recover_file a, v$archived_log b
where a.change#<=b.next_change#;

Backup views,statements, parameters...

V$DATABASE - Provides basic database-related information, including the
logging mode

SQL> Select log_mode from v$database;
LOG_MODE

V$INSTANCE - Provides basic instance information
V$DATAFILE - Provides database datafile information stored in the control file
V$LOGFILE - Provides information on the individual redo log file members from
the control file
V$LOG - Provides information on the redo log groups from the control file
V$ARCHIVED_LOG - Provides archive log information from the control file
V$LOG_HISTORY - Provides information on redo log switches in the database
DBA_DATA_FILES - Provides datafile information from the data dictionary
DBA_TABLESPACES-  Provides information on tablespaces in the database
---------------------------------------------------------------------------------------------------------

LOG_ARCHIVE_DEST Indicates the destination to copy archived redo logs to.
Typically this parameter is not set and the LOG_ARCHIVE_DEST_N parameter is set instead.
LOG_ARCHIVE_DEST_n Indicates one of up to 10 destinations to copy archived
redo logs to. The first destination starts with 1 (LOG_ARCHIVE_DEST_01).
LOG_ARCHIVE_DEST_STATE_n Indicates the state of LOG_ARCHIVE_DEST_N (ENABLED,
DEFERRED, or ALTERNATE).]
LOG_ARCHIVE_FORMAT Indicates the format of the archived redo log filenames.

Alter system set log_archive_format=’<SID>_%s_%t_%r_%d.arc’;

%s represents the sequence number

%t is the thread number that represents an individual node on a cluster when your database is running on Oracle’s RAC.

%r represents the resetlogs number
%d represents the DBID that should be unique for each database

---------------------------------------------------------------------------------------------------------
To force an archive-log switch :

alter system switch logfile; 

ARCHIVE LOG MODE DATA DICTIONARY VIEWS

V$ARCHIVE - provides information on redo logs that are
in need of being archived.
V$ARCHIVE_DEST - provides information on each individual
archive-log destination. Used for Data Guard.
V$ARCHIVE_DEST_STATE - provides status information  on each of the individual archive-log destination directories.
V$ARCHIVE_PROCESSES - provides information on the different ARCH processes running on your system.
V$ARCHIVED_LOG - view provides information on individual
archived redo logs.
V$LOG - provides information on the online redo log groups
V$LOGFILE - provides information on specific online redo logs.
V$LOG_HISTORY - provides historical information on all online/archived redo logs.
------------------------------------------------------------------------------------------------------------
Recreate temp files

Alter tablespace test_temp
Add tempfile ’/u01/u01/testtempfile01.dbf’ size 100m;

Begin/End online backup:

alter database begin backup
alter database end backup 

Backup control file:

alter database backup controlfile to ‘<BACKUP+LOCATION>\backup_ctrl.ctl’;

or

alter database backup controlfile to trace

Trace file will be generated in DIAGNOSTIC_DEST folder.