Oracle non-critical file recovery, redo, temporary files, index files, password files, and oracleredo

Oracle non-critical file recovery, redo, temporary files, index files, password files, and oracleredo

The Application of Incremental backup is in the rediscovery stage and is no longer in the restore stage.

Learn about 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 can continue to run without the database and most applications. For example, if the database loses a multiplexing redo log file

To keep the database running.
Although the loss of non-critical files will not cause database crashes, it will weaken the function of the database. For example:

The loss of index tablespaces can significantly slow down the running of applications and queries, or if these indexes are used to enforce constraints, the loss may even lead to Application

The program cannot be used.
Lost online redo log group (as long as it is not the current online log group) will cause the database operation to be suspended when LGWR attempts to write data to the group next time until a new log file is generated

.

The loss of temporary tablespace will make the user unable to run queries or create indexes until these users are allocated to the new temporary tablespace.
-- A temporary tablespace is lost at 10 Gb, and the database cannot be started. When a temporary file is lost at 11 GB, the database automatically creates a temporary file with the same name and bit;
-- When this happens in 11g, the following message is displayed in the warning log during startup:
--
Re-creating tempfile/u01/app/oracle/oradata/orcl/temp01.dbf
Bytes ----------------------------------------------------------------------------------------------------------------

-

I. Log member loss:
To deal with the loss of redo log files, it is very important to understand the possible status of the redo log group. When Oracle DB runs normally, redo log group will follow

The Environment goes through three different states. In the order of loops, the statuses are:

CURRENT: this status indicates that LGWR is writing data to the redo log group to record the redo data of all ongoing transactions in the database. This log group will be

Hold this status until you switch to another log group.

ACTIVE: The redo log group still contains the redo data required to restore the instance. This is the status of the redo log group that appears in the redo log group before the checkpoint is executed.

All data changes are written into the data file.

INACTIVE: the checkpoint has been executed. This indicates that the redo log group is no longer required to restore the instance. It can be changed to the next CURRENT log group.

 

Operations on online redo logs:

View the status of the 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
View the log file status:
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/ow.mf_1_b2255nxl _. log

 

 

-- Delete the original log group. (only the inactive log group is operated)
-- If the operation log group is current, you must switch the Log First: alter system switch logfile;
-- If it is active, you can force the checkpoint: alter system checkpoint;

1. operation log file;

1) when a data file in a log group is lost, add it before deleting it online;

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 another member;

SQL> alter database add logfile group 4'/u01/app/oracle/oradata/ORCL/onlinelog/redo04.log 'size 100 m;

SQL> alter database add logfile member '/u01/app/oracle/oradata/ORCL/onlinelog/redo04_ B .log' to group 4;

3) view the log group and its members;
SQL> select GROUP #, members, BYTES/1024/1024 size_M, STATUS, ARCHIVED from v $ log;

2. Log File loss, restart, or alter system switch logfile trigger error. In the production database, it is generally a log switch trigger error;

1) Loss of Non-current redo log groups

Select a. group #, a. status, a. archived, B. member from v $ log a, v $ logfile B where a. group # = B. group #;
-- Check the status of the log group and the position of the file

$ Rm/u01/app/oracle/oradata/ENMOEDU/redo09 *
-- Delete all files in group 9
$ Ls/u01/app/oracle/oradata/ENMOEDU/redo09 *
-- Check whether the deletion is successful

SQL> shutdown immediate
SQL> startup
-- Database restart trigger Error
SQL> startup mount
SQL> alter database clear logfile group 9;
-- Rebuilt two files in the ninth group. Both files are empty;
SQL> alter database open;

Cat/u01/app/oracle/oradata/ENMOEDU/redo03 *;
-- Check the physical address and whether the file is 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). The current redo log group is lost.

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;
/
/
An error is reported in alert. log when hanging occurs in the database;

Or a database restart trigger error occurs:
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;
-- The current Status log group is lost and cannot be recovered in this way

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;
-- Restore without Archive
Database altered.

Verification:

! Ls/u01/app/oracle/oradata/ENMOEDU/
 
SQL> select GROUP #, members, BYTES/1024/1024 size_M, STATUS, ARCHIVED from v $ log;

Bytes ----------------------------------------------------------------------------------------------------------------

Ii. Loss of temporary files:

1. -- view the location and name of the temporary file
SYS> select name from v $ tempfile;
/U01/app/oracle/oradata/PROD/datafile/o1_mf_temp_b22570tq _. tmp

2. -- delete temporary files
SYS>! Rm/u01/app/oracle/oradata/PROD/datafile/o1_mf_temp_b22570tq _. tmp

3. -- trigger error. Temporary 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 the temporary data file is lost, you can recover it without restarting the database;

SQL> ALTER TABLESPACE temp ADD TEMPFILE '/u01/app/oracle/oradata/orcl/temp02.dbf' SIZE 20 M;
Tablespace altered.

 

SQL> ALTER TABLESPACE temp DROP TEMPFILE '/u01/app/oracle/oradata/orcl/temp01.dbf ';
 

Tablespace altered.

5. 1) You can restart the database within 11 GB. The database will automatically recreate the temporary data file. You can view the alert Log to view the relevant information (generally, you can simply go to step 1)

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 Reconstruction

2) If the database cannot be restarted before 11 GB, you can create a new temporary tablespace:

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

SQL> alter database default temporary tablespace temp2;
-- Set the new tablespace to the default temporary tablespace.

SQL> drop tablespace temp including contents and datafiles;
-- Delete the old tablespace and its data

SQL> ALTER TABLESPACE temp ADD TEMPFILE '/u01/app/oracle/oradata/orcl/temp2.dbf' SIZE 20 M;
-- Add files to temporary tablespace

SYS> select * from database_properties
Where property_name = 'default _ TEMP_TABLESPACE '; -- view the default database of the current database

Bytes ----------------------------------------------------------------------------------------------------------------

-
3. Index reconstruction and restoration after index tablespace is lost

Re-create Index
:
Use the following options to shorten the time required to create an index:

PARALLEL NOLOGGING

PARALLEL (NOPARALLEL is the default value): multiple processes can work together to create indexes. Compared with the index creation by a single server process

Allocate the necessary work to create indexes among multiple server processes. The Oracle server can create indexes more quickly. Sample random tables and find a set of key indexes

These index keywords divide the index into the same number of segments according to the specified degree of parallelism. The first Query Process scans the table, extracts the key words, row ID pairs, and

To send each pair to a process in the second group of query processes. In the second group, each process sorts the keywords and builds indexes in the conventional way. Institute

After index fragments are built, the parallel Coordination Program cascade these fragments (sorted) to form the final index.

SQL> CREATE INDEX rname_idx
ON hr. regions (region_name)
PARALLEL 4;

NOLOGGING: using this keyword will speed up index creation, because there are very few redo log entries created by the creation process. This type of workload is greatly reduced.

It is also applicable to Direct path insertion and Direct Loader (SQL * Loader) insertion. This is a permanent attribute and will be displayed in the data dictionary. Available at any time

Alter index nologging/LOGGING command to update.
Note: If the database is in the nologging status, the operated data should be backed up and then changed back to nologging to prevent major errors from being recovered.

Restore the table space after the index is lost:
When an index is lost, you can create a new index instead of trying to restore the index.

Indexes are calculated objects, because they do not provide any raw data, but are another representation of existing data. Therefore, in most cases

It is easy to recreate the index.
If your tablespace only contains an index, You can simplify the recovery after data files belonging to the tablespace are lost.
If this type of data file is lost, you can

Perform the following steps:

1. Delete the data file.

2. Delete the tablespace. 3. Recreate the index tablespace.

4. Recreate the index contained in the tablespace.
1. You can restore tablespaces that only contain indexes without executing the RECOVER task.

2. If data files in tablespaces that only contain indexes are lost, it is easier to recreate the tablespace and re-create the index.

Bytes ----------------------------------------------------------------------------------------------------------------

-
4. Recreate the password verification file

1. Password File loss
Cd $ ORACLE_HOME/dbs -- location of the password file
Ls-lrt | grep orapw
Rm orapw... -- delete the password file

2. Log in
SQL> CONNECT sys/admin AS SYSDBA
-- Login Failed. No Password File Found

3. Another logon method:
Sqlplus/as sysdba
-- OS-level Login
Grant sysdba to hr;
-- If you authorize sysdba to send an error to hr;
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
[Oracle @ ocum ~] $ 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 will be prompted if not specified at command line,
Entries-maximum number of distinct DBA (optional ),
Force-whether to overwrite existing file (optional ),
Ignorecase-passwords are case-insensitive (optional ),
Nosysdba-whether to shut out the SYSDBA logon (optional Database Vault only ).

There must be no spaces around the equal-to (=) character.

-- Use the password utility orapwd to create a password file.
Orapwd file = filename password = password entries = max_users
Where:
--
Filename is the name (required) of the password file ).
--
Password is the SYS password (optional ). If the password parameter is not included, you are prompted to enter the password.
--
Entries is the maximum number of users that can be connected as SYSDBA or SYSOPER. If this value is exceeded, a new password file must be created. Relatively

A large value is relatively safe.
-- There is no space on either side of the equal sign (=.
 
-- 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;

Verification Method for Database Administrators

Choose

Choose operating system verification or password file verification to verify the database administrator:

If the database has a password file and you already have SYSDBA or SYSOPER system permissions, you can use the password file for verification.

If the server does not use the password file, or you do not have the SYSDBA or SYSOPER permission and therefore are not in the password file, you can use the operating system for verification. In most

In a data operating system, the user name of the database administrator must be placed in a special group for database administrator authentication, which is generally called OSDBA. Used in this group

The user will be granted the SYSDBA permission. A similar group of OSOPER is used to grant SYSOPER permissions to users.

Operating system verification takes precedence over Password File verification. In particular, if you are a member of the OSDBA or OSOPER Group of the operating system and are using SYSDBA or SYSOPER

Connection, you will have the relevant management permissions, regardless of the user name/password you specified.

Oracle DB provides a password utility orapwd to create a password file. The SYSDBA permission is used to connect to the system instead

The solution connection associated with the account name. For SYSOPER, connect to the PUBLIC solution. The permission to access the database using the password file is granted by the Authorization User

.
Generally, the password file is not included in the backup, because the password file can be easily re-created in almost all cases.
Protect password files and identify password files

Location environment variables are crucial to system security. Any user who has access to these files and environment variables may potentially affect connection security.

.
If the database or instance is mounted using REMOTE_LOGIN_PASSWORDFILE = EXCLUSIVE or SHARED, the password file should not be deleted or modified. Otherwise, no

Use this password file to remotely reconnect.
Note: passwords are case sensitive and must be taken into account when re-creating the password file.

 

 

 

In oracle, if the redo log file is damaged, how should we restore it?

It's easy to go to rman
Redolog file loss:
Shutdown immediate;
Starup mount;
Alter database resetlogs; (this step may cause problems, add a sentence before this
Recover database until cancel;
And all the steps are repeat in sqlplus)

However, this method uses resetlogs, which may cause data loss.

How Does ORACLE restore data through the DBF file?

Oralce cold Backup recovery method:
Install databases of the same version as before, including parameter configuration, global. dbname, and sid.
Close the database and delete controlfile, logfile, and datafile.
Copy the previously crashed controlfile, logfile, and datafile to the directory you deleted.
Open the database.