Oracle simulates temporary files, log members, loss of password files, and recovery "Weber produced"

Temporary tablespace files, log files, and password files are non-critical files because they are missing and do not affect the integrity of the entire database.

However, when these files are lost we need to retrieve these files quickly. Next I will simulate the loss of temporary tablespace files, log files, and password files.

If temporary files that are part of the temp tablespace are missing or corrupted, the temp table space will be unavailable. For example, this issue is declared as an error during the execution of an SQL statement that requires a sort of TEMP space.

scenarios where temporary table spaces are typically used are:

index Create or rebuild

Order BY or group BYDISTINCT Operations union or intersect or Minussort-merge joinsanalyze now we simulate the loss of temporary files, but the scenario to use when sorting order by:

Select  from v$tempfile;name--------------------------------------------------------------------------------/u01/ oracle/oradata/orcl/temp02.dbfsql> show parameter pganame                                 TYPE        VALUE---------------- -------------------------------------------------------------pga_aggregate_target                 Big integer 192MSQL  set pga_aggregate_target=10m;

Make the pga_aggregate_target smaller so that the order by will have to use the temporary file.

In the operating system, delete this temporary file:

Rm-rf/u01/oracle/oradata/orcl/temp02.dbf

Create a simulation table

Sql> Conn scott/Tigersql> CREATE TABLE Temp_table as Select* fromall_objects; SQL> INSERT INTO Temp_tableSelect* fromall_objects; SQL>commit; SQL> sql>Select* fromTemp_table ORDER BY1,2,3,4,5,6,7,8,9,Ten, One, A, -;Select* fromTemp_table ORDER BY1,2,3,4,5,6,7,8,9,Ten, One, A, -*ERROR at line1: ORA-01116: ErrorinchOpening database file201ORA-01110: Data File201:'/u01/oracle/oradata/orcl/temp01.dbf'ORA-27041: Unable to open filelinux Error:2: No Such file or directoryadditional information:3

You do not need to restart the database to recover lost tempfile.

If you are recovering a database, you can first add a new data file to the database and then delete the data file that has been deleted at the OS level.

 sql> Conn/ Sysdbaconnected.sql  > ALTER tablespace temp ADD tempfile  " / U01/ORACLE/ORADATA/ORCL/TEMP02.DBF   "  > ALTER tablespace temp DROP tempfile   /u01/oracle/oradata/orcl/temp01.dbf     > select  name from   V$tempfile;name --------------------------------------------------------------------------------/u01/ ORACLE/ORADATA/ORCL/TEMP02.DBF 

Executing the ORDER BY statement again will succeed. Because the content of the temporary file is not very important, is the kind of dispensable, without rebuilding it.

Three, online redo log is always one of the following three states: Current, inactive, active.　　So redo log backup and recovery will be divided into three kinds. In fact, you just understand the current state of the redo log recovery will understand the remaining two kinds of situation is how to recover.if all members in the log group with a status of inactive are missing:

SelectA.group#,a.sequence#,a.archived,a.status,b.member fromV$log A,v$logfile bwherea.group#=B.group#order by1; group# sequence# ARC STATUS MEMBER------ --------- --- -------- ------------------------------------------1         theYES current/u01/oracle/oradata/orcl/Redo01.log1         theYES current/u01/oracle/oradata/orcl/Redo0101.log2         -NO inactive/u01/oracle/oradata/orcl/Redo0201.log2         -NO inactive/u01/oracle/oradata/orcl/Redo02.log3         -YES inactive/u01/oracle/oradata/orcl/Redo0301.log3         -YES inactive/u01/oracle/oradata/orcl/Redo03.logsql>!rm-rf/u01/oracle/oradata/orcl/Redo01.logsql>!rm-rf/u01/oracle/oradata/orcl/redo0101.log

Next, convert current to Active state:

Sql> alter systemSwitchlogfile;--first let it become Activesystem altered. SQL>SelectA.group#,a.sequence#,a.archived,a.status,b.member fromV$log A,v$logfile bwherea.group#=B.group#order by1;2    3group# sequence# ARC STATUS MEMBER------ --------- --- -------- ------------------------------------------1         ANO active/u01/oracle/oradata/orcl/Redo01.log1         ANO active/u01/oracle/oradata/orcl/Redo0101.log2         atNO current/u01/oracle/oradata/orcl/Redo0201.log2         atNO current/u01/oracle/oradata/orcl/Redo02.log3         +YES inactive/u01/oracle/oradata/orcl/Redo0301.log3         +YES inactive/u01/oracle/oradata/orcl/Redo03.log6rows selected. SQL> Alter system checkpoint;--and turn it into Inactivesystem altered. SQL>SelectA.group#,a.sequence#,a.archived,a.status,b.member fromV$log A,v$logfile bwherea.group#=B.group#order by1;2    3group# sequence# ARC STATUS MEMBER------ --------- --- -------- ------------------------------------------1         ANO inactive/u01/oracle/oradata/orcl/Redo01.log1         ANO inactive/u01/oracle/oradata/orcl/Redo0101.log2         atNO current/u01/oracle/oradata/orcl/Redo0201.log2         atNO current/u01/oracle/oradata/orcl/Redo02.log3         +YES inactive/u01/oracle/oradata/orcl/Redo0301.log3         +YES inactive/u01/oracle/oradata/orcl/Redo03.log6Rows selected.

Close the database and boot to the mounted State:

sql> shutdown Immediate

Sql>Startup Mount

sql>ALTER DATABASE clear unarchived logfile Group 1---Clear

sql> ALTER DATABASE open;

Database altered.

Sql> Select A.group#,a.sequence#,a.archived,a.status,b.member from V$log a,v$logfile b

where a.group#=b.group#

Order by 1; 2 3

group# sequence# ARC STATUS MEMBER

------ --------- --- -------- ------------------------------------------

1 NO Current/u01/oracle/oradata/orcl/redo01.log

1 NO Current/u01/oracle/oradata/orcl/redo0101.log

2 YES Inactive/u01/oracle/oradata/orcl/redo0201.log

2 YES Inactive/u01/oracle/oradata/orcl/redo02.log

3 YES Inactive/u01/oracle/oradata/orcl/redo0301.log

3 YES Inactive/u01/oracle/oradata/orcl/redo03.log

Okay, so redo log Group 1 is back. However, the members of the current log group if all two are broken, although the entire data is complete, but if you want to restore the current state before the business is not possible.

Four, the simulation password file corruption situation.

To delete a password file:

CD $ORACLE _home/dbs/-rf ORAPWORCL

Logging into the database using the operating system authentication:

 as Sysdbasql 10.2. 0.1. 0  - :£º19822005, Oracle.  All rights reserved. Error:ora-01031: Insufficient privilegesenter user

Password file is missing, can not log in with password verification, login only through operating system authentication

 [[email protected] dbs]$ sqlplus/as   Sysdbasql  *plus:release 10.2 . 0.1 . 0 -Production on Sat 18  08 : 40 : 55  2012  copyright (c)  1982 , 2005   , Oracle. All rights reserved. Connected to:oracle Database 10g Enterprise Edition Release  10.2 . 0.1 . 0 - Productionwith the partitioning, OLAP and Data Mining optionssql  > 

Set the Remote_login_passwordfile parameter to NONE and restart the database

The Remote_login_passwordfile parameter has a value of three:

NONE: Login with password is forbidden

EXCLUSIVE: Mainly for multi-instance, if the database is multi-instance, that is, RAC, only allow a single instance to use the password file

SHARE: Mainly for multi-instance, if the database is multi-instance, that is RAC, allow all instances to share the use of a password file

Set Remote_login_passwordfile=none scope=spfilestartup Force

To create a password file using the password utility orapwd

which

FileName is the name of the password file

Password is sysoper and SYSDBA's password.

Entries is the maximum number of different users allowed to connect as SYSDBA or Sysoper

If this value is exceeded, you must create a new password file and use a larger value to compare the insurance

There are no spaces on either side of the equals sign (=) character

Orapwd file= $ORACLE _home/dbs/orapworcl password=a entries=5set remote_login_ Passwordfile=exclusive scope=spfile;startup Force

Connect to the database using the password file that you created

CONNECT Sys/[email protected] as SYSDBA

Oracle simulates temporary files, log members, loss of password files, and recovery "Weber produced"