Oracle Non-critical file recovery, redo, temp files, index files, password files

Application of incremental backup in recovery phase, no longer restore phase

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

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 text

, you can still use a copy of other redo log files 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

The program cannot be used.
The loss of the online Redo log group (as long as it is not the current online log group) causes the database operation to be suspended until the next attempt is made to write to the group LGWR, until a new log text is generated

Pieces.

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, redo log groups follow

The ring undergoes 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 protect the

This state until you switch to a different 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 the Redo log group appears in the

Any data changes 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:
Sql>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 a log file:
Sql>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

--Delete the original log group; (only log groups with inactive status)
--Log switchover is required to operate the log group as current: alter system switch logfile;
--if active, the checkpoint can be enforced: alter system checkpoint;

1. Operation log file;

1). When a data file in a log group is lost, the online operation is added first, 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 and add a member;

Sql>alter Database Add logfile Group 4 '/u01/app/oracle/oradata/orcl/onlinelog/redo04.log ' size 100m;

Sql>alter database Add logfile member '/u01/app/oracle/oradata/orcl/onlinelog/redo04_b.log ' to group 4;

3). View log groups and members;
Sql>select group#,members,bytes/1024/1024 size_m,status,archived from V$log;

2. log file is missing, reboot or alter system switch logfile triggers an error; the production library is usually a log switching trigger;

1) Loss of non-current redo log Group

Select a.group#, A.status, a.archived,b.member from V$log A,v$logfile b where a.group#=b.group#;
--View Log group status and files in location

$RM/u01/app/oracle/oradata/enmoedu/redo09*
--Delete all files from group Nineth
$ls/u01/app/oracle/oradata/enmoedu/redo09*
--See if Delete succeeded

sql> shutdown Immediate
Sql> Startup
--Restart Database triggering error
Sql> Startup Mount
Sql> ALTER DATABASE clear logfile Group 9;
--Reconstruction of the Nineth group of two documents, two files are empty;
sql> ALTER DATABASE open;

cat/u01/app/oracle/oradata/enmoedu/redo03*;
--View physical addresses to see if files are generated

Sql>select group#,members,bytes/1024/1024 size_m,status,archived from V$log;
--View log groups and members

Error in Alert log:
Errors in File

/U01/APP/ORACLE/DIAG/RDBMS/ENMOEDU/ENMOEDU/TRACE/ENMOEDU_LGWR_35031.TRC:
Ora-00313:open failed for members of log Group 1 of thread 1
Ora-00312:online Log 1 Thread 1: '/u01/app/oracle/oradata/enmoedu/redo01.log '
Ora-27037:unable to obtain file status
linux-x86_64 error:2: No such file or directory
Additional Information:3
Errors in FILE/U01/APP/ORACLE/DIAG/RDBMS/ENMOEDU/ENMOEDU/TRACE/ENMOEDU_LGWR_35031.TRC:
Ora-00313:open failed for members of log Group 1 of thread 1
Ora-00312:online Log 1 Thread 1: '/u01/app/oracle/oradata/enmoedu/redo01.log '
Ora-27037:unable to obtain file status
linux-x86_64 error:2: No such file or directory
Additional Information:3

2). Missing the current redo log Group

Select a.group#, A.status, A.archived,b.member
From V$log A,v$logfile b
where a.group#=b.group#;

$RM/u01/app/oracle/oradata/enmoedu/redo09*;

alter system switch logfile;
/
/
The database appears hanging phenomenon, error in alert.log;

or restart the database trigger error:
sql> shutdown Immediate
Sql> Startup
--Trigger Error
Sql> Startup Mount
ORACLE instance started.

Total System Global area 237998080 bytes
Fixed Size 2227216 bytes
Variable Size 192938992 bytes
Database buffers 37748736 bytes
Redo buffers 5083136 bytes
Database mounted.

Sql> ALTER DATABASE clear logfile Group 9;
--Lost Current status log group, cannot be restored like this

ALTER DATABASE clear logfile Group 9
*
ERROR at line 1:
Ora-00350:log 9 of Instance enmoedu (thread 1) needs to be archived
Ora-00312:online Log 9 Thread 1: '/u01/app/oracle/oradata/enmoedu/redo03.log '

Sql> ALTER DATABASE Clear unarchived logfile Group 9;
--No archive recovery
Database altered.

Verify:

!ls/u01/app/oracle/oradata/enmoedu/

Sql> 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. Compared to a single server process to create an index sequentially, the

Assigning the work required to create an index between multiple server processes allows the Oracle server to create indexes more quickly. The table is randomly sampled and a set of index keys is found

Words, these index keywords divide the index evenly into the same number of fragments according to the specified degree of parallelism. The first set of query processes will scan the table, extract the keywords, row ID pairs and base

The keyword sends each pair 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. The

When an indexed fragment is 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 heavy workload has drastically reduced

and direct Loader (Sql*loader) inserts are also available. This is a persistent property and is therefore displayed in the data dictionary. Can be used at any time

ALTER INDEX nologging/logging command to be updated.
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 can be very

Easy to re-create 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 for SYS would is prompted if not specified at 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 of the SYSDBA logon (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 more

Big values are more 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

Verify the database administrator by using operating system authentication or password file validation:

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

Number of operating systems, the database administrator's authentication requires the database administrator's operating system user name to be placed in a special group, commonly referred to as OSDBA. Used in this group

Users 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 OSDBA or osoper group of the operating system, and you are SYSDBA or Sysoper

Connection, you will have administrative privileges associated with it, regardless of the username/password you specify.

Oracle DB provides a password utility, orapwd, to create a password file. When connecting using SYSDBA permissions, it is connected in the SYS scheme, not with the

The schema connection associated with the user name. For Sysoper, you will be connected to the public scheme. Permission to access a database using a password file is granted by an authorized user

The order is provided.
Typically, the password file is not included in the backup because it is easy to recreate the password file in almost all cases.
Protect password files and identify password files

The location of the environment variable is 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, there will be no

Use the password file to reconnect from the remote.
Note: Passwords are case-sensitive and must be taken into account when recreating the password file.

Oracle Non-critical file recovery, redo, temp files, index files, password files