Recovering from Non-critical files

An Overview of Non-critical files
The recovery of non-critical files is an important matter that you should be familiar with so you can resolve them in an efficient manner. Non-critical files are essentially database files that do not have a critical impact on the operations of the database when they have been compromised.

• Temporary Tablespaces: Can be recovered without impacting database operations. All database users need a temporary tablespace of some kind to perform database operations. They essentially provide sorting operations.

• Redo log files: Non-current redo log files are also considered non-critical database files. A lost redo group can be much more severe and does not come into the category of a non-critical recovery

• Index tablespaces: Index tablespaces contain only indexes and can be re-created or rebuilt.

• Read-only tablespaces: These tablespaces are static. This allows recovery to be fairly straightforward process under most circumstances.

• Password files: Password files contain the passwords for privileged administrative users such as SYSDBA and SYSOPER. This allows you to connect remotely to a database instance and perform administrative functions. The password file can be deleted and re-created if necessary.

Creating a New Temporary Tablespace
A temporary tablespace is responsible for various database sorting operations. A temporary tablespace is part of the physical database files, which the Oracle control file will expect to exist under normal operations. Because temporary tablespace does not have any permanent objects stored within it, there is no change in the SCN from the checkpoint process in the control file or file header.

SQL> create temporary tablespace temp2 tempfile
‘C:\oracle\ora101t\temp2_01.dbf’ size 100M
Extent management local uniform size 1M;

Starting Database with a Missing Tempfile
startup mount

drop tablespace temp including contents;

create temporary tablespace temp tempfile
‘C:\oracle\ora101t\temp01.dbf’ size 100M
extent management local uniform size 1M;

Altering Default Temporary Tablespace
alter database default temporary tablespace temp2;

Re-creating Redo Log Files
Redo logs contain all the transactions committed or uncommitted. An important standard for creating Oracle database is to have mirrored redo logs, also called multiplexed redo logs. If a redo log member is lost or deleted and the mirrored log member still exists, then redo log member can be easily rebuilt. The command ALTER DATABASE ADD LOGFILE MEMBER will create a log member if one has been lost or deleted.

SQL> alter database drop logfile member ‘C:\oracle\redo01.log’;
SQL> alter database add logfile member ‘C:\oracle\redo01.log’ to group 1;

NOTE: Make sure that the database is in restrict mode if you do not have many redo logs.

Recovering an Index Tablespace
Recovering the database with a missing index tablespace is another non-critical recovery. An index tablespace should contain only indexes. Indexes are objects that can be created from the underlying database tables. Rebuild index scripts can be rerun to build the indexes in the index tablespace.

startup mount

drop tablespace indexes including contents;

create tablespace indexes datafile ‘C:\oracle\ora101t\index01.dbf’ size 20M;

Re-creating Indexes:
Re-creating indexes is required after rebuilding the index tablespace. As long as you have the create index scripts, this is a non-critical recovery process.

Recovering Read-Only Tablespaces
A read-only tablespace is a tablespace that contains static information. This means that in most cases, no media recovery is needed.

Re-creating the Password File
There are multiple methods for a DBA to authenticate to an Oracle database. The standard method is to log in directly to the operating system of the server, connect directly to the database with Inter-Process Control (IPC), and establish local connection on the database, which does not need to use SQL*Net. This method requires the operating system’s account to require the password for validation. Once in the secure OS account, you can connect as SYSDBA or SYSOPER. SYSOPER has partial database administration privilege, which is good for operational support.
A second primary method is to connect remotely using SQL*Net and authenticate with a password file. The password file is required for all remote database administrative connections to an Oracle database using SYSDBA or SYSOPER. ORAPWD is an Oracle utility that generates a password file for remote connections. ORAPWD should be run when the database is shutdown. When using ORAPWD, one should use appropriate naming convention, which includes orapw$ORACLE_SID. The password file must be located in $ORACLE_HOME/dbs in UNIX and in $ORACLE_HOME\database in Windows. The init.ora file must also contain REMOTE_LOGIN_PASSWORDFILE parameter, set to SHARED or EXCLUSIVE.

orapwd file=orapwdORA101T password=syspass entries=20

The entries option determines how many users can be stored in password file. To see what users are utilizing the password file,
SQL> select * from v$pwfile_users;


NOTE:
Redo log member cannot be added to current or active online redo log group, because the log group is actively recording transactions