Search <book_title>...

Veritas InfoScale™ 8.0.2 Storage and Availability Management for Oracle Databases - AIX, Linux, Solaris

Last Published: 2023-06-12

Product(s): InfoScale & Storage Foundation (8.0.2)

Platform: AIX,Linux,Solaris

Section I. Storage Foundation High Availability (SFHA) management solutions for Oracle databases
1. Overview of Storage Foundation for Databases
Section II. Deploying Oracle with Veritas InfoScale products
Section III. Configuring Storage Foundation for Database (SFDB) tools
1. Configuring and managing the Storage Foundation for Databases repository database
2. Configuring authentication for Storage Foundation for Databases (SFDB) tools
Section IV. Improving Oracle database performance
Section V. Using point-in-time copies
Section VI. Optimizing storage costs for Oracle
Section VII. Managing Oracle disaster recovery
1. Using Storage Foundation for Databases (SFDB) tools in a replicated environment
2. Using volume snapshots in a replicated environment
Section VIII. Storage Foundation for Databases administrative reference
Section IX. Reference

Handling Oracle temporary tablespaces and Quick I/O

You can create a new temporary tablespace using Quick I/O files. However, you cannot convert existing temporary tablespaces which use regular files to Quick I/O with the qio_getdbfiles command on Oracle9.

By default, qio_getdbfiles skips any tablespaces marked TEMPORARY because they can be sparse, which means that not all blocks in the file are allocated. Quick I/O files cannot be sparse, as Quick I/O provides a raw-type interface to storage. If a sparse file is converted to a Quick I/O file, the Oracle instance can fail if Oracle attempts to write into one of these unallocated blocks. When you initially create a temporary tablespace on Quick I/O files, however, Oracle sees them as raw devices and does not create sparse files.

To convert a temporary tablespace using regular files to Quick I/O files, you can drop your existing temporary tablespaces which use regular files and recreate them using Quick I/O files. You can also leave the temporary tablespaces as regular files.

To obtain a list of file names that are not temporary

Use the following SQL statements:

	$ sqlplus /nolog
		SQL> connect / as sysdba;
		SQL> select file_name from dba_data_files a, 
		dba_tablespaces b where a.tablespace_name = 
		b.tablespace_name and b.contents <> 'TEMPORARY';

To drop an existing temporary tablespace and recreate using Quick I/O files

Drop the temporary tablespace, including its contents:

$ sqlplus /nolog
SQL> connect / as sysdba;
SQL> drop tablespace tablespace_name including contents;

Create a Quick I/O file on a VxFS file system:

# /opt/VRTS/bin/qiomkfile -h header_size -s size \
/mount_point/filename.dbf

Change the owner and group permissions on the file
```
# chown oracle:dba .filename
```
```
# chmod 660 .filename
```

Create a new temporary tablespace using the create temporary tablespace command.

To use the create temporary tablespace command:

$ sqlplus /nolog
SQL> connect / as sysdba;
SQL> create temporary tablespace tablespace_name \
tempfile '/mount_point/new_filename.dbf' size size reuse;

This example shows how to drop tablespace tempts, create a Quick I/O file temp01.dbf, change permissions and then create a new temporary tablespace tempts using the create temporary tablespace command:

$ sqlplus /nolog
SQL> connect / as sysdba;
SQL> drop tablespace tempts including contents;
Tablespace dropped.
# /opt/VRTS/bin/qiomkfile -h 32k -s 100M /db01/temp01.dbf

# chown oracle:dba .temp01
# chmod 660 .temp01

$ sqlplus /nolog
SQL> connect / as dba;
SQL> create temporary tablespace tempts \
tempfile '/db01/temp01.dbf' \
size 100M reuse;
Tablespace created.