Oracle Non-critical file recovery, log member, temp file, index tablespace, password file (password file)

Critical and non-critical
Non-critical files are files that the database and most applications can continue to run without it. For example, if a database is missing a multiplexed redo log file, you can still use a different copy of the redo log file to keep the database running continuously.
Although losing a non-critical file does not cause the database to crash, it weakens the functionality of the database. For example:

Missing index Table spaces can cause applications and queries to run significantly slower, or, if they are used to enforce constraints, they can even cause the application to become unusable.
The loss of an online redo log group (as long as it is not the current online log group) causes the database operation to be suspended until a new log file is generated when LGWR next attempts to write to the group.

Losing a temporary tablespace can make it impossible for users to run queries or create indexes until they are assigned to a new temporary table space.
--10g loss of temporary tablespace, the database can not start, when the 11g, the loss of temporary files, the database will automatically create a same name as a temporary file;
When this happens in--11g, a message similar to the following is displayed in the alert log during startup:
--
Re-creating TEMPFILE/U01/APP/ORACLE/ORADATA/ORCL/TEMP01.DBF
--------------------------------------------------------------------------------------------------------------- --

One, the log members are missing:
To handle the loss of redo log files, it is important to understand the possible state of the Redo log group. During the normal operation of Oracle DB, the Redo log group loops through three different states. In the order of the loops, the states are:

Current: This state indicates that LGWR is writing data to the Redo log group to record the redo data for all transactions that are in progress in the database. The log group will remain in this state until you switch to another log group.

ACTIVE: The Redo Log group still contains the redo data required to recover the instance. This is the state of the Redo log group that has not been performed during the checkpoint, and any data changes that occur in the Redo log group will be written out to the data file.

INACTIVE: The above checkpoint has been executed, which means that the Redo log group is no longer needed to recover the instance and it can become the next current log group.

Operation on the online redo log:

To view the status of a log group:
[email protected]>select group#,sequence#,members,archived,status from V$log;

    group#  sequence#    members ARC STATUS
------------------------------ -------------------
         1           7          2 YES INACTIVE
          2          8           2 no  Current
         3           6          2 YES INACTIVE
To view the status of the log file:
[email protected] >select group#, type,member from V$logfile;

group# TYPE MEMBER
---------- -----------------------------------------------------------------
3 ONLINE
/u01/app/oracle/oradata/prod/onlinelog/o1_mf_3_b22567o2_.log

3 ONLINE
/u01/app/oracle/fast_recovery_area/prod/onlinelog/o1_mf_3_b2256827_.log

2 ONLINE
/u01/app/oracle/oradata/prod/onlinelog/o1_mf_2_b2255zsg_.log

2 ONLINE
/u01/app/oracle/fast_recovery_area/prod/onlinelog/o1_mf_2_b22560gb_.log

1 ONLINE
/u01/app/oracle/oradata/prod/onlinelog/o1_mf_1_b2255npg_.log

1 ONLINE
/u01/app/oracle/fast_recovery_area/prod/onlinelog/o1_mf_1_b2255nxl_.log

6 rows selected.

1. When a data file in a log group is lost, the online operation, first added, then deleted;

ALTER DATABASE add logfile member '/u01/app/oracle/oradata/prod/onlinelog/o1_mf_2_b_.log ' to group 2;

ALTER DATABASE drop logfile member '/u01/app/oracle/oradata/prod/onlinelog/o1_mf_1_b2255npg_.log ' to group 2;

2. Add a group online, add a member;

[email protected]>alter database Add logfile Group 4 '/u01/app/oracle/oradata/orcl/onlinelog/redo04.log ' Size 100m;

[email protected]>alter database Add logfile member '/u01/app/oracle/oradata/orcl/onlinelog/redo04_b.log ' To group 4;

3. View log groups and members;
[email protected]>select group#,members,bytes/1024/1024 size_m,status,archived from V$log;

--------------------------------------------------------------------------------------------------------------- --
Second, the loss of temporary files:

1.--viewing the location and name of temporary files
Sys>select name from V$tempfile;
/u01/app/oracle/oradata/prod/datafile/o1_mf_temp_b22570tq_.tmp

2.--Deleting temporary files
sys>! Rm/u01/app/oracle/oradata/prod/datafile/o1_mf_temp_b22570tq_.tmp

3.--trigger Error, temp file not found
Sys>create Global temporary table tab_temp as SELECT * from Dba_objects;
Create global temporary table Tab_temp as SELECT * from Dba_objects
*
ERROR at line 1:
Ora-01116:error in opening database file 201
Ora-01110:data File 201:
'/u01/app/oracle/oradata/prod/datafile/o1_mf_temp_b22570tq_.tmp '
Ora-27041:unable to open File
linux-x86_64 error:2: No such file or directory
Additional Information:3

4. When you know that temporary data files are missing, you can recover from missing temporary files without restarting the database;

sql> ALTER tablespace temp ADD tempfile '/u01/app/oracle/oradata/orcl/temp02.dbf ' SIZE 20M;
Tablespace altered.

sql> ALTER tablespace temp DROP tempfile '/u01/app/oracle/oradata/orcl/temp01.dbf ';

Tablespace altered.

5.1) in 11g can also restart the database, the database will automatically rebuild the temporary data files, you can view the alert log to see the relevant information (generally directly 4th step)

Shutdown immediate;

Sys>startup
ORACLE instance started.
Total System Global area 839282688 bytes
Fixed Size 2257880 bytes
Variable Size 545262632 bytes
Database buffers 289406976 bytes
Redo buffers 2355200 bytes
Database mounted.
Database opened.

Sys>select name from V$tempfile;
/u01/app/oracle/oradata/prod/datafile/o1_mf_temp_b29o0g6r_.tmp

1 row selected.
--Database rebuilding

2) before 11g, if the database cannot be re-started, you can create a new temporary tablespace:

Create temporary tablespace temp2 tempfile
'/u01/app/oracle/oradata/orcl/temp2.dbf ' size 10m reuse autoextend on maxsize 200m;

Sql>alter database default temporary tablespace temp2;
--Set the new tablespace as the default temporary table space

Sql>drop tablespace temp including contents and datafiles;
--delete old table space and its data

Sql>alter tablespace temp ADD tempfile '/u01/app/oracle/oradata/orcl/temp2.dbf ' SIZE 20M;
--Add files to the temp table space

Sys>select * from Database_properties
where property_name= ' defailt_temp_tablespace '; --View the current database default database

--------------------------------------------------------------------------------------------------------------- --
Iii. rebuilding the index and losing the index table space for recovery

Re-create INDEX
：
Use the following options to shorten the time it takes to create an index:

PARALLEL nologging

PARALLEL (Noparallel is the default value): Multiple processes can work together to create an index. By allocating the work required to create indexes between multiple server processes, the Oracle server can create indexes more quickly than a single server process creates indexes sequentially. Randomly samples the table and finds a set of index keywords that divide the index evenly into the same number of fragments according to the specified degree of parallelism. The first set of query processes scans the table, extracts the keywords, row ID pairs, and sends each pair based on the keyword to a process in the second set of query processes. Each process in the second group sorts the keywords and builds the indexes as usual. When all index fragments are built, the parallel coordinator cascade the Fragments (sorted) to form the final index.

sql> CREATE INDEX Rname_idx
On Hr.regions (Region_name)
PARALLEL 4;

Nologging: Using this keyword speeds up the creation of indexes because there are very few redo log entries created by the creation process. This drastically reduced rework build also applies to direct path insertions and directly Loader (Sql*loader) insertions. This is a persistent property and is therefore displayed in the data dictionary. You can use the ALTER INDEX nologging/logging command to update it at any time.
Note: If the database in the Nologging state, the operation of the data should be backed up, and then change the nologging back, so as to avoid major errors difficult to recover

Recovery after missing the index table space:
When indexes are lost, a faster and simpler approach is to recreate the index instead of trying to recover it.

Indexes are computed objects because they do not provide any raw data, just another representation of the data already in existence. Therefore, in most cases, it is easy to recreate the index.
If your tablespace contains only indexes, you can simplify recovery after you lose a data file that belongs to that tablespace.
If you lose such a data file, you can perform the following steps:

1. Delete the data file.

2. Delete the table space. 3. Re-create the index table space.

4. Recreate the indexes that are contained in the table space.
1. You can recover a table space that contains only indexes without performing a RECOVER task.

2. If a data file that is part of a table space that contains only indexes is lost, it may be easier to recreate the tablespace and re-create the index.

--------------------------------------------------------------------------------------------------------------- --
Four, re-create password verification file

1. password file is missing
CD $ORACLE _home/dbs--Location of the password file
Ls-lrt|grep ORAPW
RM ORAPW ...--delete password file

2. Login
Sql> CONNECT Sys/admin as SYSDBA
--Login failed, no password file found

3. Another way to log in:
Sqlplus/as SYSDBA
--os Level Login
Grant SYSDBA to HR;
--Authorized SYSDBA to HR, will error;
Sql> Grant SYSDBA to admin2;

Grant SYSDBA to Admin2

*

ERROR at line 1:

Ora-01994:grant failed:password file missing or disabled

4. Create a password file
[Email protected] ~]$ orapwd
Usage:orapwd file=<fname> entries=<users> force=<y/n> ignorecase=<y/n> nosysdba=<y/n>

  WHERE
    file-name of password file (required),
    Password-password F Or SYS would be prompted if not specified on command line,
    entries-maximum number of distinct DBA ( Optional),
    force-whether to overwrite existing file (optional),
    Ignorecase-passwords is case-insensitive (optional),
    Nosysdba-whether to shut out the SYSDBA Lo Gon (optional Database Vault only).
   
  There must be no spaces around the equal-to (=) character.

--Use the password utility orapwd to create the password file.
Orapwd file=filename Password=password entries=max_users
which
--
FileName is the name of the password file (required).
--
Password is the password for SYS (optional). If the password parameter is not included, you will be prompted to enter the password.
--
Entries is the maximum number of different users that are allowed to connect as SYSDBA or Sysoper. If this value is exceeded, a new password file must be created. Use larger values to compare insurance.
--There are no spaces on either side of the equals sign (=) character.

--Example: $ orapwd file= $ORACLE _HOME/DBS/ORAPWORCL password=oracle entries=5

5. Verify that the password file is available

Sql> CONNECT Sys/admin as SYSDBA

Sql> Grant SYSDBA to admin2;

Validation Methods for database administrators

Depending on whether you want to manage the database locally on the same computer as the database, or if you want to manage many different database servers from a remote client, you can choose to use operating system authentication or password file validation to verify the database administrator:

If the database has a password file and you already have SYSDBA or sysoper system permissions, you can verify it through a password file.

If the server does not use a password file, or if you do not have SYSDBA or Sysoper permissions and therefore are not in the password file, you can use operating system authentication. In most operating systems, the database administrator's authentication requires the database administrator's operating system user name to be placed in a special group, commonly known as OSDBA. Users in this group will be granted SYSDBA permissions. A similar group osoper is used to grant Sysoper permissions to the user.

Operating system validation takes precedence over password file validation. In particular, if you are a member of the operating system's OSDBA or Osoper group, and you are connected as SYSDBA or Sysoper, you will have administrative rights associated with the connection, regardless of the username/password you specify.

Oracle DB provides a password utility, orapwd, to create a password file. When you connect using SYSDBA permissions, you are connecting in the SYS scheme, not with the schema associated with the user name. For Sysoper, you will be connected to the public scheme. The permission to access the database using the password file is provided by the grant command issued by the authorized user.
Typically, the password file is not included in the backup because it is easy to recreate the password file in almost all cases.
The environment variables that protect the password file and identify the location of the password file are critical for system security. Any user who has access to these files and environment variables can potentially affect the security of the connection.
If you mount a database or instance using Remote_login_passwordfile=exclusive or SHARED, you should not delete or modify the password file. Otherwise, you will not be able to reconnect from the remote using the password file.
Note: Passwords are case-sensitive and must be taken into account when recreating the password file.

Understanding the Database Settings table:
[email protected]>desc database_properties
Name Null? Type
----------------------------------------- -------- ----------------------------
Property_name not NULL VARCHAR2 (30)
Property_value VARCHAR2 (4000)
DESCRIPTION VARCHAR2 (4000)

Oracle Non-critical file recovery, log member, temp file, index tablespace, password file (password file)