Soumya's Database Blog : 2014

Resizing standby datafile if disk runs out of space on standby server

Resizing standby datafile if disk runs out of space on standby server:-

Primary db: Prim
Standby db: Stand

On primary database server:-

[root@server1 ~]# df -h
Filesystem Size Used Avail Use% Mounted on
/dev/sda2 8.7G 6.4G 1.9G 78% /

Here on primary server i have 1.9G as freespace.

On standby database server:-

[oracle@server2 ~]$ df -h
Filesystem Size Used Avail Use% Mounted on
/dev/sda2 8.7G 7.8G 428M 95% /

On standby server we have 428 mb left in /

Now we will create a tablespace on primary server.

[oracle@server1 ~]$ sqlplus / as sysdba
SQL> create tablespace somtbs datafile '/home/oracle/dropme.dbf' size 200M;

Tablespace created.

SQL> alter system switch logfile;

System altered.

At primary :-
[root@server1 ~]# df -h
Filesystem Size Used Avail Use% Mounted on
/dev/sda2 8.7G 7.0G 1.3G 85% /

At standby:-
Filesystem Size Used Avail Use% Mounted on
/dev/sda2 8.7G 8.0G 228M 98% /

Now i'm gonna resize the datafile on primary database to 1024mb which is not actually available in standby database and check the result.
At primary server:-
QL> alter database datafile '/home/oracle/dropme.dbf' resize 1024M;

Database altered.

SQL> alter system switch logfile;

System altered.

After resizing the the datafile
At primary :-
[root@server1 ~]# df -h
Filesystem Size Used Avail Use% Mounted on
/dev/sda2 8.7G 8.0G 700M 92% /

At standby server:-
Filesystem Size Used Avail Use% Mounted on
/dev/sda2 8.7G 8.0G 228M 98% /

[oracle@server2 ~]$ ls -ltrh
total 301M
-rw-r----- 1 oracle oinstall 201M Dec 30 15:26 dropme.dbf

We see the datafile has not been resized at standby database due to no required space available.

Let's check the alert log for reference.
On standby database:-

in alertlog on standby server:-
Tue Dec 30 15:26:38 2014
MRP0: Background Media Recovery terminated with error 1237
Errors in file /u01/app/oracle/diag/rdbms/stand/stand/trace/stand_mrp0_2720.trc:
ORA-01237: cannot extend datafile 6
ORA-01110: data file 6: '/home/oracle/dropme.dbf'
ORA-19502: write error on file "/home/oracle/dropme.dbf", block number 54656 (block size=8192)
ORA-27072: File I/O error
Linux-x86_64 Error: 25: Inappropriate ioctl for device
Additional information: 4
Additional information: 54656
Additional information: 155648
Managed Standby Recovery not using Real Time Apply
Recovery interrupted!
Recovered data files to a consistent state at change 1043748
Errors in file /u01/app/oracle/diag/rdbms/stand/stand/trace/stand_mrp0_2720.trc:
ORA-01237: cannot extend datafile 6
ORA-01110: data file 6: '/home/oracle/dropme.dbf'
ORA-19502: write error on file "/home/oracle/dropme.dbf", block number 54656 (block size=8192)
ORA-27072: File I/O error
Linux-x86_64 Error: 25: Inappropriate ioctl for device

The alert log shows media recovery failed as the datafile could not be resized and media recovery stops
The standby database cannot be opened now as the files need recovery.

Solution of this problem as follows:-

Step 1. Shutdown both databases.
SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

Step 2. Add a new db_file_name_convert path in pfile where we have enough space to relocate the datafile.
[oracle@server1 ~]$ cd $ORACLE_HOME/dbs
[oracle@server1 dbs]$ ll
total 32
-rw-r--r--. 1 oracle oinstall 1423 Dec 27 23:44 initprim.ora
-rw-r--r--. 1 oracle oinstall 1427 Dec 27 23:59 initstand.ora
-rw-r-----. 1 oracle oinstall 3584 Dec 28 00:49 spfileprim.ora

[oracle@server1 dbs]$ vi initprim.ora
locate the following line in pfile and make the following changes
*.log_file_name_convert='/u01/app/oracle/oradata/stand/','/u01/app/oracle/oradata/prim/','/home/oracle/','/u01/'

:wq (save & exit)

Do the same changes in initstand.ora pfile which is in standby database server.

Step 3. Copy dropme.dbf to /u01/ on both server from original area.As this will be the location where i will keep the the new resized datafile .

[oracle@server1 ~]$ cp dropme.dbf /u01/

Now transfer the datafile into standby database
[oracle@server1 u01]$ scp dropme.dbf oracle@server2:/u01/
oracle@server2's password:
dropme.dbf 100% 1024MB 20.5MB/s 00:50

Step 4. Mount the primary database from newly created pfile
[oracle@server1 u01]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Sun Dec 28 01:28:51 2014

Copyright (c) 1982, 2009, Oracle. All rights reserved.

Connected to an idle instance.

SQL> startup mount pfile='/u01/app/oracle/product/11.2.0/db_1/dbs/initprim.ora';
ORACLE instance started.

Total System Global Area 413372416 bytes
Fixed Size 2213896 bytes
Variable Size 310380536 bytes
Database Buffers 96468992 bytes
Redo Buffers 4308992 bytes
Database mounted.

Step 5. Move the datafile to new location
SQL> alter database rename file '/home/oracle/dropme.dbf' to '/u01/dropme.dbf';

Database altered.

Step 6.Create spfile from the pfile.
SQL> create spfile from pfile='/u01/app/oracle/product/11.2.0/db_1/dbs/initprim.ora';

SQL> shut immediate;
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area 413372416 bytes
Fixed Size 2213896 bytes
Variable Size 310380536 bytes
Database Buffers 96468992 bytes
Redo Buffers 4308992 bytes
Database mounted.

Step 7.Create standby controlfile
SQL> alter database create standby controlfile as '/u01/app/oracle/oradata/prim/stand.ctl';
SQL> alter database open;

Step 8.Now copy the standby controlfile into standby server
[oracle@server1 u01]$ cd /u01/app/oracle/oradata/prim/

[oracle@server1 prim]$ scp stand.ctl oracle@server2:/u01/app/oracle/oradata/stand/
oracle@server2's password:
stand.ctl 100% 9808KB 9.6MB/s 00:00

Step 9. Startup standby database into mount stage using the new pfile.
SQL> startup mount pfile ='/u01/app/oracle/product/11.2.0/db_1/dbs/initstand.ora';
SQL> create spfile from pfile='/u01/app/oracle/product/11.2.0/db_1/dbs/initstand.ora';
SQL> shut immediate;
SQL> startup nomount;
SQL> alter database mount standby database;

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/stand/system01.dbf
/u01/app/oracle/oradata/stand/sysaux01.dbf
/u01/app/oracle/oradata/stand/undotbs01.dbf
/u01/app/oracle/oradata/stand/users01.dbf
/u01/app/oracle/oradata/stand/example01.dbf
/u01/dropme.dbf

Primary server : Switch a few logfiles….

Now start managed recovery on standby.

SQL>recover managed standby database disconnect;

Alert log below…
Media Recovery Waiting for thread 1 sequence 32
Fetching gap sequence in thread 1, gap sequence 32-34
Tue Dec 30 19:59:44 2014
RFS[4]: Assigned to RFS process 4848
RFS[5]: Assigned to RFS process 4850
RFS[5]: Identified database type as 'physical standby': Client is ARCH pid 3781
RFS[5]: Opened log for thread 1 sequence 32 dbid -164957407 branch 837708259
Archived Log entry 23 added for thread 1 sequence 33 rlc 837708259 ID 0xf62acd21 dest 2:
Archived Log entry 24 added for thread 1 sequence 32 rlc 837708259 ID 0xf62acd21 dest 2:
Completed: ALTER DATABASE RECOVER managed standby database disconnect
Tue Dec 30 19:59:54 2014
Media Recovery Log /u01/app/oracle/flash_recovery_area/1_32_837708259.dbf
Media Recovery Log /u01/app/oracle/flash_recovery_area/1_33_837708259.dbf
Media Recovery Log /u01/app/oracle/flash_recovery_area/1_34_837708259.dbf
Media Recovery Log /u01/app/oracle/flash_recovery_area/1_35_837708259.dbf
Media Recovery Log /u01/app/oracle/flash_recovery_area/1_36_837708259.dbf
Media Recovery Log /u01/app/oracle/flash_recovery_area/1_37_837708259.dbf
Media Recovery Log /u01/app/oracle/flash_recovery_area/1_38_837708259.dbf
Media Recovery Log /u01/app/oracle/flash_recovery_area/1_39_837708259.dbf
Media Recovery Log /u01/app/oracle/flash_recovery_area/1_40_837708259.dbf

Done....

How to Recover database without control files and redolog files.

Scenario:- We are deleting controlfiles from database and would recover database after dropping them.

Database version :- Oracle 11g R2
OS: Rhel 6
Database sid: orcl
Archivelog mode: enabled

Step1:-
$cd /u01/app/oracle/oradata/orcl
$rm -rf *.ctl

$sqlplus / as sysdba
SQL>startup
ORACLE instance started.

Total System Global Area 413372416 bytes
Fixed Size 2213896 bytes
Variable Size 402655224 bytes
Database Buffers 4194304 bytes
Redo Buffers 4308992 bytes
ORA-00205: error in identifying control file, check alert log for more info

Step2:-Start the database in nomount stage
startup nomount
ORACLE instance started.

Total System Global Area 209715200 bytes
Fixed Size 1248140 bytes
Variable Size 75498612 bytes
Database Buffers 130023424 bytes
Redo Buffers 2945024 bytes

Step3:-Recreate the control file:-

CREATE CONTROLFILE REUSE DATABASE "ORCL" RESETLOGS NOARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/u01/app/oracle/oradata/orcl/REDO01.log' SIZE 50M,
GROUP 2 '/u01/app/oracle/oradata/orcl/REDO02.log' SIZE 50M,
GROUP 3 '/u01/app/oracle/oradata/orcl/REDO03.log' SIZE 50M
DATAFILE
'/u01/app/oracle/oradata/orcl/system01.dbf',
'/u01/app/oracle/oradata/orcl/sysaux01.dbf',
'/u01/app/oracle/oradata/orcl/users01.dbf',
'/u01/app/oracle/oradata/orcl/example01.dbf',
'/u01/app/oracle/oradata/orcl/undotbs01.dbf'
CHARACTER SET WE8MSWIN1252;

Now after creating the control file, the database has been mounted.

Step 4.
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/u01/app/oracle/oradata/prim/system01.dbf'

So in this case we have to do the recovery using the online redolog files.
SQL> select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ ---------
1 1 0 52428800 512 1 YES UNUSED 0 0
3 1 0 52428800 512 1 YES CURRENT 0 0
2 1 0 52428800 512 1 YES UNUSED 0 0

SQL> recover database until cancel using backup controlfile;
ORA-00279: change 1030644 generated at 01/08/2015 22:25:57 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/flash_recovery_area/PRIM/archivelog/2015_01_08/o1_mf_1_1_%u_.arc
ORA-00280: change 1030644 for thread 1 is in sequence #1

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/u01/app/oracle/flash_recovery_area/PRIM/archivelog/2015_01_08/o1_mf_1_1_%u_.arc
ORA-00308: cannot open archived log
'/u01/app/oracle/flash_recovery_area/PRIM/archivelog/2015_01_08/o1_mf_1_1_%u_.ar
c'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

SQL> recover database until cancel using backup controlfile;

ORA-00279: change 1030644 generated at 01/08/2015 22:25:57 needed for thread 1

ORA-00289: suggestion :

/u01/app/oracle/flash_recovery_area/PRIM/archivelog/2015_01_08/o1_mf_1_1_%u_.arc

ORA-00280: change 1030644 for thread 1 is in sequence #1

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

/u01/app/oracle/oradata/prim/REDO03.log

Log applied.

Media recovery complete.

SQL> alter database open resetlogs;

Database altered.

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
READ WRITE

How to Rename A Datafile in A Physical Standby Environment

How to Rename A Datafile in A Physical Standby Environment:-

Primary db_unique_name:- prim
Standby db_unique_name:- stand

1.Verify primary and standby databases and also change the initialization parameter STANDBY_FILE_MANAGEMENT from AUTO to MANUAL.
At Primary Database :-
SQL> select name,db_unique_name,database_role,switchover_status from v$database;

NAME DB_UNIQUE_NAME DATABASE_ROLE SWITCHOVER_STATUS
--------- ------------------------------ ---------------- --------------------
PRIM prim PRIMARY TO STANDBY

SQL> SHOW PARAMETER STANDBY_FILE_MANAGEMENT;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
standby_file_management string auto

SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=MANUAL;

System altered.

SQL> show parameter STANDBY_FILE_MANAGEMENT

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
standby_file_management string MANUAL

SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/app/oracle/flash_recovery_area
Oldest online log sequence 27
Next log sequence to archive 29
Current log sequence 29

On Standby database :
SQL> select name,db_unique_name,database_role,switchover_status from v$database;

NAME DB_UNIQUE_NAME DATABASE_ROLE SWITCHOVER_STATUS
--------- ------------------------------ ---------------- --------------------
PRIM stand PHYSICAL STANDBY NOT ALLOWED

SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/app/oracle/flash_recovery_area
Oldest online log sequence 21
Next log sequence to archive 0
Current log sequence 29

SQL> SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

SEQUENCE# APPLIED
---------- ---------
7 YES
8 YES
9 YES
10 YES
11 YES
12 YES
13 YES
14 YES
15 YES
16 YES
17 YES

SEQUENCE# APPLIED
---------- ---------
18 YES
19 YES
20 YES
21 YES
22 YES
23 YES
24 YES
25 YES
26 YES
27 YES
28 YES

22 rows selected.

SQL> sho parameter STANDBY_FILE_MANAGEMENT

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
standby_file_management string auto

SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=MANUAL;

2.Now we will verify the tablespace to which the datafile belongs and make it offline. This step is performed on primary database.

SQL> select file_name , tablespace_name from dba_data_files;
/u01/app/oracle/oradata/prim/users01.dbf USERS

/u01/app/oracle/oradata/prim/undotbs01.dbf UNDOTBS1

/u01/app/oracle/oradata/prim/sysaux01.dbf SYSAUX

/u01/app/oracle/oradata/prim/system01.dbf SYSTEM

/u01/app/oracle/oradata/prim/example01.dbf EXAMPLE

3. Rename the datafile to new location and bring tablespace back online. This step is performed on primary database.
[oracle@server1 prim]$ cd /u01/app/oracle/oradata/prim
[oracle@server1 prim]$ ls -ltrh
-rw-r-----. 1 oracle oinstall 671M Dec 28 01:14 system01.dbf
-rw-r-----. 1 oracle oinstall 101M Dec 28 01:16 example01.dbf
......
[oracle@server1 prim]$ mv example01.dbf /home/oracle/example_01.dbf
[oracle@server1 prim]$ sqlplus / as sysdba
SQL> alter tablespace example rename datafile '/u01/app/oracle/oradata/prim/example01.dbf' to '/home/oracle/example_01.dbf';

Tablespace altered.

SQL> alter tablespace example online;

Tablespace altered.

SQL> select file_name from dba_data_files where tablespace_name= 'EXAMPLE';

FILE_NAME
--------------------------------------------------------------------------------
/home/oracle/example_01.dbf

4. Verify the same tablespace on standby database.
SQL> select ts# , name from v$tablespace;

TS# NAME
---------- ------------------------------
0 SYSTEM
1 SYSAUX
2 UNDOTBS1
4 USERS
3 TEMP
6 EXAMPLE

5. Stop recovery on standby database and shut it down.
QL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Database altered.

SQL> shut immediate;

6. Rename the datafile on standby database.
[oracle@server2 ~]$ cd /u01/app/oracle/oradata/stand/
[oracle@server2 stand]$ mv example01.dbf /home/oracle/example_01.dbf
[oracle@server2 stand]$ sqlplus / as sysdba
SQL> startup mount
SQL> alter database rename file '/u01/app/oracle/oradata/stand/example01.dbf' to '/home/oracle/example_01.dbf';

Database altered.

SQL> select name from v$datafile where ts#=6;

NAME
--------------------------------------------------------------------------------
/home/oracle/example_01.dbf

7. Keep standby database in recovery mode.

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;

Database altered.

8. Set initialization parameter STANDBY_FILE_MANAGEMENT value back to AUTO.

On Primary :

SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;

System altered.

On Standby :

SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;

System altered.

on Standby:-
SQL> alter database recover managed standby database cancel;

Database altered.

SQL> alter database recover managed standby database using current logfile disconnect from session;

Database altered.

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
READ ONLY WITH APPLY

Health checkups of the Database Instance

Health check of the Database Instance :-

Explanation:-
An ideal health check should provide an overview of a database’s stability across three major areas for consideration:

Availability
Performance
Scalability

Category: Availability
1. Database space (Should consider both database space and OS space; would be ideal to also consider growth trends.)
2. Archive log space and listener status.
3. Dump area space (bdump, cdump, adump, udump, etc.)
4. Verifying success of database archive logs to disk/tape.
5. Verify success of database backup
6. Snapshot/Materialized view status
7. Status of DBMS Jobs
8. Replication collisions
9. Monitoring Backups.
10. Online redo logs multiplexed (On different mount-points.)
11. Control file multiplexed (On different mount-points.)
12. Misc errors (potential bugs) in alert.log
13. Daily Tablespace Utilization.
14.Checking the temporary tablespace/files.
15.Checking the UNDO tablespace and retention.
16.Monitoring the Unix /tmp and /var Location.
17.Check Invalid objects and recompile.

Category: Performance
18. Disparate segment types (tables, indexes, etc.) in same tablespace
19. SYSTEM tablespace being granted as default or temp tablespace
20. Temporary tablespace not being a true temp tablespace
21. Deadlock related errors in alert.log
22. Non-symmetric segments or non-equi sized extents in tablespace (For dictionary managed tablespaces.)
23. Invalid objects
24. Any event that incurred a wait over X seconds (“X” to be defined by user during healthcheck report execution. Default value could be 5 seconds. Obviously, for this value to be available, some kind of stats recording mechanism needs to be in place. In our case, Data Palette is used to collect these stats so the health check report can query the Data Palette repository for wait events and corresponding durations.)
25. Hit ratios: DB buffer cache, redo log, SQL area, dictionary/row cache, etc. (While there is a mixed opinion on whether these are useful or not, I like to include them for DBAs that do rely on them to identify whether any memory shortage exists in the database instance and adjust the related resource(s) accordingly. While I do have an opinion on this matter, my goal is not to argue whether this stat is useful or not, instead, it’s to provide them to people that need them (and there are quite a few folks that still value hit ratios.)
26. I/O / disk busy (I/O stats, at the OS and database levels.)
27. CPU load average or queue size
28. RAM usage
29. Swap space usage
30. Network bandwidth usage (Input errors, output errors, queue size, collisions, etc.)
31. Multi-threaded settings (Servers, dispatchers, circuits, etc.)
32. RAC related statistics (False pings, cache fusion and interconnect traffic, etc. – based on the Oracle version.)

Category: Scalability (Note: For ensuring there are no scalability related issues, the health check generating mechanism ideally should be able to relate to current resource consumption trends and apply predictive algorithms to discern whether there will be contention or shortfall. In the absence of such predictive capabilities, a basic health check routine can still use thresholds to determine whether a resource is close to being depleted.)
33. Sessions
34. Processes
35. Multi-threaded resources (dispatchers, servers, circuits, etc.)
36. Disk Space
37. Memory structures (locks, latches, semaphores, etc.)
38. I/O
39. CPU
40. RAM
41. Swap space
42. Network bandwidth
43. RAC related statistics (False pings, cache fusion and interconnect traffic, etc. – based on the Oracle version.)
44. Understanding system resources consumed by non-DB processes running on the same server/domain (3rd party applications such as ETL jobs, webservers, app servers, etc.)
45. Understanding system resources consumed by DB-related processes running outside their normal scheduled window (Applications such as backup processes, archive log propagation, monitoring (OEM) agents, etc. This requires the health check utility to know which processes are related to the database and their normal execution time/frequency.)

Scripts related to TEMP TABLESPACE

scripts related to temp tablespace

To check instance-wise total allocated, total used TEMP for both rac and non-rac:-

set lines 152
col FreeSpaceGB format 999.999
col UsedSpaceGB format 999.999
col TotalSpaceGB format 999.999
col host_name format a30
col tablespace_name format a30
select tablespace_name,
(free_blocks*8)/1024/1024 FreeSpaceGB,
(used_blocks*8)/1024/1024 UsedSpaceGB,
(total_blocks*8)/1024/1024 TotalSpaceGB,
i.instance_name,i.host_name
from gv$sort_segment ss,gv$instance i where ss.tablespace_name in (select tablespace_name from dba_tablespaces where contents='TEMPORARY') and
i.inst_id=ss.inst_id;

Total Used and Total Free Blocks:-

select inst_id, tablespace_name, total_blocks, used_blocks, free_blocks from gv$sort_segment;

Another 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';

Temporary Tablespace groups:-

SELECT * FROM DATABASE_PROPERTIES where PROPERTY_NAME='DEFAULT_TEMP_TABLESPACE';

select tablespace_name,contents from dba_tablespaces where tablespace_name like '%TEMP%';

select * from dba_tablespace_groups;

Block wise Check:-

select TABLESPACE_NAME, TOTAL_BLOCKS, USED_BLOCKS, MAX_USED_BLOCKS, MAX_SORT_BLOCKS, FREE_BLOCKS from V$SORT_SEGMENT;

select sum(free_blocks) from gv$sort_segment where tablespace_name = 'TEMP';

To Check 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 check Used Extents ,Free Extents available in Temp Tablespace:-

SELECT tablespace_name, extent_size, total_extents, used_extents,free_extents, max_used_size FROM v$sort_segment;

To list all tempfiles of Temp Tablespace:-

col file_name for a45
select tablespace_name,file_name,bytes/1024/1024,maxbytes/1024/1024,autoextensible from dba_temp_files order by file_name;

SELECT d.tablespace_name tablespace , d.file_name filename, d.file_id fl_id, d.bytes/1024/1024
size_m
, NVL(t.bytes_cached/1024/1024, 0) used_m, TRUNC((t.bytes_cached / d.bytes) * 100) pct_used
FROM
sys.dba_temp_files d, v$temp_extent_pool t, v$tempfile v
WHERE (t.file_id (+)= d.file_id)
AND (d.file_id = v.file#);

Additional checks:-

select distinct(temporary_tablespace) from dba_users;

select username,default_tablespace,temporary_tablespace from dba_users order by temporary_tablespace;

SELECT * FROM DATABASE_PROPERTIES where PROPERTY_NAME='DEFAULT_TEMP_TABLESPACE';

Changing the default temporary Tablespace:-

SQL> alter database default temporary tablespace TEMP;

Database altered.

To add tempfile to Temp Tablespace:-

alter tablespace temp add tempfile '/u01/app/oracle/oradata/prim/temp02.dbf' size 1000m;

alter tablespace TEMP add tempfile '/u01/app/oracle/oradata/prim/temp04.dbf' size 1800M autoextend on maxsize 1800M;

To resize the tempfile in Temp Tablespace:-

alter database tempfile '/u01/app/oracle/oradata/prim/temp01.dbf' resize 250M;

alter database tempfile '/u01/app/oracle/oradata/prim/temp01.dbf' autoextend on maxsize 1800M;

alter tablespace TEMP add tempfile '/u01/app/oracle/oradata/prim/temp05.dbf' size 1800m reuse;

To find Sort Segment Usage by Users

select username,sum(extents) "Extents",sum(blocks) "Block"
from v$sort_usage
group by username;

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 'FreeSpace ' || (free_blocks*8)/1024/1024 ||' GB' from v$sort_segment where tablespace_name='TEMP';

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

To find Total Space Allocated for Temp Tablespace:-

select 'TotalSpace ' || (sum(blocks)*8)/1024/1024 ||' GB' from dba_temp_files where tablespace_name='TEMP';

Get 10 sessions with 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;

Displays the amount of IO for each tempfile:-

SELECT SUBSTR(t.name,1,50) AS file_name,
f.phyblkrd AS blocks_read,
f.phyblkwrt AS blocks_written,
f.phyblkrd + f.phyblkwrt AS total_io
FROM v$tempstat f,v$tempfile t
WHERE t.file# = f.file#
ORDER BY f.phyblkrd + f.phyblkwrt DESC;

select * from (SELECT u.tablespace, s.username, s.sid, s.serial#, s.logon_time, program, u.extents, ((u.blocks*8)/1024) as MB,
i.inst_id,i.host_name
FROM gv$session s, gv$sort_usage u ,gv$instance i
WHERE s.saddr=u.session_addr and u.inst_id=i.inst_id order by MB DESC) a where rownum<10;

I used these queries to check some settings:

-- List all database files and their tablespaces:
select file_name, tablespace_name, status
,bytes /1000000 as MB
,maxbytes/1000000 as MB_max
from dba_data_files ;

-- List all schemas temp tablespaces and default tablespaces?:
select username, temporary_tablespace, default_tablespace from dba_users ;

List all tablespaces and some settings:
select tablespace_name, status, contents, extent_management
from dba_tablespaces ;

TABLESPACE_NAME STATUS CONTENTS EXTENT_MAN
------------------------------ --------- --------- ----------
SYSTEM ONLINE PERMANENT LOCAL
SYSAUX ONLINE PERMANENT LOCAL
UNDOTBS1 ONLINE UNDO LOCAL
TEMP ONLINE TEMPORARY LOCAL
USERS ONLINE PERMANENT LOCAL
EXAMPLE ONLINE PERMANENT LOCAL
SOUMYATBS ONLINE PERMANENT LOCAL

Show number of tables in the TEMP tablespace:-
select count(*) from dba_all_tables
where tablespace_name = 'TEMP' ;

Checking for any other objects (views, indexes, triggers, pl/sql, etc.) is trickier, but this query seems to work correctly - note that you'll probably need to connect internal in order to see the sys_objects view:

-- Shows all objects which exist in the TEMP tablespace - should get
-- NO rows for this:
column owner format a20
column object_type format a30
column object_name format a40
select
o.owner ,o.object_name
,o.object_type
from sys_objects s
,dba_objects o
,dba_data_files df
where df.file_id = s.header_file
and o.object_id = s.object_id
and df.tablespace_name = 'TEMP' ;

Identifying WHO is currently using TEMP Segments

10g onwards

SELECT sysdate,a.username, a.sid, a.serial#, a.osuser, (b.blocks*d.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.blocks*d.block_size)/1048576 > 1024
ORDER BY b.tablespace, 6 desc;

ORA-00845: MEMORY_TARGET not supported on this system

ORA-00845: MEMORY_TARGET not supported on this system
Problem Description:-
While creating a startup database using dbca the database creation GUI gives error message in a pop up window,
ORA-00845: MEMORY_TARGET not supported on this system
from where you can ignore the error message.
The similar scenario also occurs whenever you try to start your database using startup command, shows error message like below.

SQL> STARTUP
ORA-00845: MEMORY_TARGET not supported on this system

Cause of the Problem
•Starting from Oracle 11g the automatic memory management feature is now defined with parameter MEMORY_TARGET and MEMORY_MAX_TARGET.

•On linux file system the shared memory need to be mounted on /dev/shm directory on the operating system.

•And the size of /dev/shm needs to be greater than MEMORY_TARGET or MEMMORY_MAX_TARGET.

•The AMM (Automatic Memory Management) now in 11g manages both SGA and PGA together by MMAN process.

•The MEMORY_TARGET parameter in 11g comes for (SGA_TARGET+PGA_AGGREGATE_TARGET) which was in 10g.

•And MEMORY_MAX_TARGET parameter in 11g comes instead of SGA_MAX_TARGET parameter which was in 10g.

•The ORA-00845:can arises for the following two reasons on linux system.

1)If the shared memory which is mapped to /dev/shm directory is less than the size of MEMORY_TARGET or MEMORY_MAX_TARGET.
or,
2)If the shared memory is not mapped to /dev/shm directory.

olution of the Problem
Make sure /dev/shm is properly mounted. You can see it by,
#df -h or df -Th
The output should be similar like
$ df -h
Filesystem Size Used Avail Use% Mounted on
/dev/sda2 8.7G 4.7G 3.6G 57% /
shmfs 1G 512M 512M 50% /dev/shm

We see here for /dev/shm we have assigned 1G memory. Now if you set MEMORY_TARGET more than 1G then above ORA-845 will arise. For example if you have MEMORY_TARGET or MEMORY_MAX_TARGET set to 12G then you can mount shared memory to 13g like below.
As a root user,
# mount -t tmpfs shmfs -o size=20g /dev/shm
In order to make the settings persistence so that it will affect after restarting machine add an entry in /etc/fstab similar to the following:
vi /etc/fstab
shmfs /dev/shm tmpfs size=20g 0 0

:wq

#alter system set memory_target=20G scope=spfile;
and then bounce the database to affect the changes.

Step by Step Oracle installation 11g R2 on RHEL 6

Before you Begin-

System Requirements

Architecture

These instructions are for installing Red Hat Enterprise Linux 6 (RHEL6) on a 32-bit and 64-bit systems (x86_64).

Minimum Memory

Red Hat recommends for 32 bit a minimum of 1GB memory/logical CPU, and for 64 bit a minimum of 1GB of memory, 1GB/logical CPU.

Minimum Disk Space

minimum disk size of above 12 GB, and a minimum root partition ("/") size of about 9 GB.

CDROM Drive

Red Hat does not include a floppy version of the boot images for RHEL6. Your system will need a boot-capable CDROM drive.

INSTALLATION STEPS OF LINUX/CENTOS 6 –

1.Boot from the RHEL6 DVD. At the boot screen, press the "Enter" key.

2. Press the "tab" key to move focus to the "Skip" key, then press the "Enter" key to continue.
3. On the "Welcome" screen, click the "Next" button.
4. Select the appropriate language, then click the "Next" button.
5. Select the relevant keyboard setting, then click the "Next" button.
6. Select the storage option necessary for the installation i.e. Basic storage device, then click the "Next" button.
7. Enter a fully qualified host name “dipak.example.com”, then click the "Configure Network" button.
Check the "Connect automatically" checkbox. If you are not using DHCP, click on the "IPv4 Settings" tab, set the method to "Manual", click the "Add" button and enter the appropriate network details.IP: 192.168.0.5 Netmask:255.255.255.0 Gateway:192.168.0.1 then click the Apply button.
8. Select the relevant time zone by clicking on your nearest city on the map. Click on the "Next" button to proceed.
9. Enter a root password for the server, then click the "Next" button to proceed.
10. Check the partitioning type you require. Click on create custom layout
11.Then created the following partitions
/root -12gb
/temp-4 Gb

/i01-15Gb
/swap-2gb (1.5 of RAM)
/undotbs-512mb
/temptbs-512mbs
/redoA-512mb
/redoB-512mb
/redoC-512mb
/index- 1024mb
*Please be advised i have used the above partition structure keeping in mind of production servers.

12. Accept the "Basic Server" installation and check the "Customize now" option, then click the "Next" button.
13.Then select the following packages.(Gcc,lib,compat,sys)

Base System > Base
Base System > Client management tools
Base System > Compatibility libraries
Base System > Hardware monitoring utilities
Base System > Large Systems Performance
Base System > Network file system client
Base System > Performance Tools
Base System > Perl Support
Servers > Server Platform
Servers > System administration tools
Desktops > Desktop
Desktops > Desktop Platform
Desktops > Fonts
Desktops > General Purpose Desktop
Desktops > Graphical Administration Tools
Desktops > Input Methods
Desktops > X Window System
Development > Additional Development
Development > Development Tools
Applications > Internet Browser

Don't worry too much about getting every package you might ever want -- you can always add additional packages later.

want to eject your boot CDROM prior to rebooting so you don't boot back to the boot iso image.

The final installation screen has a 'reboot' bottom on the right corner. Again, make sure to remove any CDROM left in the drive from the initial boot of the installation program.

Post Install

Red Hat Welcome

The first time you boot your newly-installed system in the graphical run level (run level 5) the Red Hat Welcome screens may automatically run.

If you are planning to taylor your system you can skip most or all of these steps -- they are either unneeded or will be handled by taylor.

The welcome screens are intended for stand-alone systems and guides you through a number of first time system administration tasks such as:

· License Information

You'll need to agree to the License agreement.

· Set Up Software Updates

If running Taylor, then choose 'no'. (You'll need to confirm 'No Thanks'.)

· Create User, (a non-admin local account)

For the Create User screen 'Forward' button with no additions or adjustments. You'll need to confirm you want to continue without a user account.

· Date and Time

Accept the defaults.

· Kdump

· Accept the defaults. Do not choose to reboot to enable kdumps.

On the next screen, you'll click on 'Other..' enter 'root' as the user and password you picked earlier. You'll see messages related to being logged in as root, and how it's not ideal, but this okay for this one time.

Once the installation of RHEL6 is done, We have to do following configuration of RHEL6 for oracle 11g installation –

Once the installtion of RHEL6 is done, We have to do following configuration of RHEL6 for oracle 11g installation-

A. We have to disable selinux from this file:

#vi /etc/selinux/config

Set selinux=disabled and save the file.

#service iptables stop

#service iptables save

B. Disable the firewall.

Then restart the service

#service network restart

C. Then we have to configure Yum

1.#mkdir /YUM
2.mount RHEL6 DVD

3.#mount /dev/cdrom/media

4.Go to the mounted diretory

#cd /cdrom/media

5.copy all contents of DVD to /YUM

#cp –av * /YUM

6. 5.Install createrepo package with dependancies

#rpm -ivh deltarpm-**.el6.x86_64.rpm

#rpm –ivh python-deltarpm-**.el6.x86_64.rpm

# rpm –ivh createrepo-**.rpm

7. 6.install FTP server

rpm -ivh vsftpd-2.2.2-6.el6.x86_64.rpm

8. 7.Create repository using

#createrepo -v /YUM

8.Create a repo file in /etc/yum.repos.d/

#cd /etc/yum.repos.d/

#cp rhel-source.repo rhel.repo

#vi rhel.repo

Edit the file

[rhel]

Name=Red Hat Enterprise Linux

Baseurl=file:///YUM

Enabled=1

Gpgcheck=0

:wq

#yum list

STEP 1: Download the software

http://www.oracle.com/technetwork/database/database-technologies/express-edition/downloads/index.html

STEP 2: Prerequisites

There are many prerequisites to installing Oracle 11gR2 and the following are the steps I took.

Update /etc/sysctl.conf

#vim /etc/sysctl.conf

Scroll to the bottom and add the following:

# ###########################

# ORACLE PARMS

# ###########################

# ###########################

# ORACLE PARMS

# ###########################

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

#P.S. There is another kernel.shmmax and kernel.shmall parameters.Make sure to put # before them or the new values wont work.

Run the following command to change the current kernel parameters.

/sbin/sysctl -p

Update /etc/security/limits.conf

#vim /etc/security/limits.conf
Scroll to the bottom and above the “# End of file” line, add:

oracle              soft    nproc   2047

oracle              hard    nproc   16384

oracle              soft    nofile  4096

oracle              hard    nofile  65536

oracle              soft    stack   10240

Add users and groups

groupadd -g 501 oinstall

groupadd -g 502 dba

useradd -u 502 -g oinstall -G dba oracle

passwd oracle

Amend the "/etc/security/limits.d/90-nproc.conf" file as described below.

# Change this

*          soft    nproc    1024

# To this

* - nproc 16384

Set secure Linux to permissive by editing the "/etc/selinux/config" file, making sure the SELINUX flag is set as follows.

SELINUX=permissive

Once the change is complete, restart the server.
If you have the Linux firewall enabled, you will need to disable it.

Edit host file & edit the following things at last line-

#Vim /etc/hosts

192.168.0.5 soumya.example.com soumya

Edit network file & edit the following things-

#Vim /etc/sysconfig/network

NETWORKING=yes

NETWORKING_IPV6=yes

HOSTNAME=soumya.example.com

:wq

#hostname soumya.example.com

Create the directories in which the Oracle software will be installed and setup the permissions there:

mkdir -p /i01/app/oracle/product/11.2.0/db_1

chown -R oracle:oinstall /i01

chmod -R 775 /i01

chown -Rf oracle:oinstall  /redoA

chown -Rf oracle:oinstall  /redoB

chown -Rf oracle:oinstall  /redoB

chown -Rf oracle:oinstall  /undotbs

chown -Rf oracle:oinstall  /temptbs

chown -Rf oracle:oinstall  /index

chmod -Rf 775 /redoA

chmod -Rf 775 /redoB

chmod -Rf 775 /redoC

chmod -Rf 775 /undotbs

chmod -Rf 775 /temptbs

chmod -Rf 775 /index

Switch to the Oracle software directory and setup the permissions there:
cd /root/Desktop/Oraclesoftware
chown -R oracle:oinstall database
chmod -R 775 database

Set up the oracle user environment

su oracle
vi /home/oracle/.bash_profile
***Add the following (use hostname from the command line to get your hostname and use the correct paths for your install):

export TMP=/tmp
export TMPDIR=$TMP
export ORACLE_HOSTNAME={Enter your hostname}
export ORACLE_UNQNAME={Enter your DB name}
export ORACLE_BASE=/i01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1
export ORACLE_SID={Enter your DB name}
export PATH=/usr/sbin:$ORACLE_HOME/bin:$PATH
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib:/usr/lib64
export CLASSPATH=$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib

Login as root and issue the following command.

xhost +soumya.example.com

Oracle has a list of dependencies which have to be verified. In the list below, those dependencies which had to be installed using yum are noted with (*):
binutils-2.17.50.0.6
compat-libstdc++-33-3.2.3 (*)
elfutils-libelf-0.125
elfutils-libelf-devel-0.125 (*)
gcc-4.1.2
gcc-c++-4.1.2 (*)
glibc-2.5-24
glibc-common-2.5
glibc-devel-2.5
glibc-headers-2.5
ksh-20060214 (*)
libaio-0.3.106
libaio-devel-0.3.106
libgcc-4.1.2
libgomp-4.1.2
libstdc++-4.1.2
libstdc++-devel-4.1.2
make-3.81
numactl-devel-0.9.8.i386 (*)
sysstat-7.0.2 (*)

After Successful installation of Linux/CentOS6. We will be install & used X-manager in window system to graphically export Linux screen in window to install Oracle 11g.

Xmanager is a powerful and easy-to-use PC X server that runs on Windows platforms. It allows you to bring remote Unix/Linux desktops to your Windows PC seamlessly. You can also run remote X applications securely through the SSH (Secure Shell) protocol even when your Windows PC is inside a private network and a firewall is between your PC and the remote server. so basically it will allow you to connect to any network/system having Unix/Linux OS installed from your windows machine and thus give you the ability to work at your own place using your own system.

Getting Started: Download, Install and Start with XDMCP

Download Xmanager Enterprise

Download Xmanager

Installation

1. When you execute the downloaded file, InstallShield Wizard will begin the installation process.

2. Follow the instruction of InstallShield Wizard. If you have purchased Xmanager license, please use the product key that you have received from us to install. Otherwise, use 'evaluation' for the product key.

3. When the install is completed, Xmanager 4 icon will be placed on the desktop.

Start with XDMCP(Xbrowser)

To access a full graphic desktop of remote Unix/Linux server, go through the following steps to start with the XDMCP connection method. You can also start with Xstart if you need to run only a single remote X application instead of a full desktop.

Connecting and executing remote X application with Xstart

With Xstart you can create a session that executes a remote X application. Once you have created an Xstartsession, you can bring a remote X application on your Windows with a single click.

1. From the Xmanager folder, run Xstart. RESULT: The Xstart window opens.

2. Click New. RESULT: A New Session dialog box appears.

3. Enter a new session name, and click OK.

4. In the Host box, enter the hostname or IP address of the remote Linux/Unix host.

5. In the Protocol box, select an appropriate protocol that is available on the host. The SSH protocol is appropriate for most hosts.

6. To set up protocol-specific options such as port and time-out, click Setup.

7. In the Username box, enter the user account on the host.

8. Select an Authentication type from the Authentication list.

9. To set up Authentication-specific options such as password and public key, click Setup.

10. In the Execution Command box, enter a command that will be executed on the host. For example, enter the following to run an xterm:

/xterm -ls -display $DISPLAY

11. Click Run. RESULT: An xterm window opens.

12. After login through x-manager, you can check it by running a command as root

user

# xclock

The output will show graphical clock. It means its working fine. To perform Oracle Installation process switch to ORACLE user.

# su - oracle

STEP 3: Install Oracle in RHEL6/centOS6

$cd /root/Desktop/Oracle software/database
./runInstaller

Installation steps of oracle:-

*1. Provide your email address and Oracle support password to get security updates from Oracle.

*2. . Installation options like creating a database, installing oracle software only or upgrading the database. Select "Create and configure a database".

*3. Choose the system class here. Select "Server Class" it provides more advanced options.

*4. Choose from creating a single node installation from node selections.

*5. Choose your installation type Typical .

*6. If you choose typical install in previous screen, then you will see this page for Install Configurations. Provide Oracle software installation location, database files location and administrator password etc.

*7. Specify the Oracle install inventory location and Operating system group "oinstall".

*8. Now all prerequisite checks will be performed here and if every thing is ok you will be moved to the install summary page. You can hit the "Back" button and come back to see the status of all the checks performed.

*9. Installation Summary page. Hit "Finish" to start the Installation.

*10. Installation Progress. This will take several minutes and it will automatically invoke Database COnfiguration Assistant to create a database.

*11. Database Configuration Assistant invoked by the installer.

*12. Once DBCA has finished creating the database, it will show a page like this. It is a summary of the database that has just been created.

*13. As a last step you will be asked to execute some configuration scripts as root.

Open another console and login as root. Execute following once logged in successfully.

# /i01/app/oraInventory/orainstRoot.sh

# /i01/app/oracle/product/11.2.0/db_1/root.sh

*14. When configuration scripts are executed successfully by root press OK in the installer and you will see this "Finish" page. Note down the management URL and press "Close".