Oracle works properly in archive Mode

To ensure that the Oracle database works normally in archive mode, perform the following settings::

1. Set the database to archive

If the database archive mode is not enabled, the query displays the following information (non-archive mode ):

1. query the current database mode

SQL> archive log list;

Database Log mode non-archive Mode
Disable automatic archiving
Archive endpoint D: oracleora81rdbms
The earliest log sequence of summary information 64
Current Log sequence 66

2. Shutdown immediate

3,
A.After the database is closed, the existing data is backed up. Changing the running mode of the database is an important change to the database. Therefore, you must back up the database to protect possible problems.
B.
Modify the initialization parameter file init. ora and add

# Log_archive_start = true

# Log_archive_dest = D: oracleoradatapeiyzarchive
# Log_archive_duplex_dest = D: oracleoradatapeiyzarchiveachive

Log_archive_dest_1 = 'location = D: oracleoradatapeiyzarchive1'
Log_archive_dest_3 = 'location = D: oracleoradatapeiyzarchive3'

# Log_archive_dest_2 = "service = standby_db1" // Save the archive file to the remote standby host. log_archive_format = arc % T % S. arc // here, the variable % s or % s is the log serial number, and % T or % T is the thread number, all of which are automatically generated by the operating system (part of the file name ), in addition, % s is a fixed length. If not, add 0 to the left.

Note:
Here, log_archive_dest and log_archive_duplex_dest are a group; log_archive_dest_1 to log_archive_dest_5 are a group (up to five image directories in 8i and up to 10 image directories in 9i ); the two groups can only be set to one group in the initialization parameter file, and they cannot exist at the same time;

4. Start the database to the Mount status, and perform the following operations to set the database to the archive mode.

SQL> alter database archivelog;

The database has been changed.

A. Check whether the archive mode of the current database is enabled. If the archive path is not defined in the initialization parameter file, the following information is displayed:

SQL> archive log list

Database Log mode archiving Mode
Disable automatic archiving
Archive end point D: oracleora81rdbms // note: this is the default archive if the archive path is not specified in the initialization parameter file.PathThe earliest log sequence of summary information 64
Next archive log sequence 66
Current Log sequence 66

B. My current display is:

SQL> archive log list;

Database Log mode archiving Mode
Disable automatic archiving
Archive end point D: oracleoradatapeiyzarchive3 // the log file is archived to this directory when the current log is switched
The earliest log sequence of summary information 66
Next archive log sequence 68
Current Log sequence 68

5. Manually change the database to the automatic archiving mode (ifLog_archive_start = true, this step can be omitted)

SQL> archive log start;

Processed statements

SQL> archive log list

Database Log mode archiving Mode
Automatic archiving is enabled // automatic archiving has been started. If the log_archive_start = true option is set in the initialization parameter file,The automatic archiving function is automatically enabled after the instance is started.
Archive endpoint D: oracleoradatapeiyzarchive3
The earliest log sequence of summary information 66
Next archive log sequence 68
Current Log sequence 68

6. Open the database

SQL> alter database open;

The database has been changed.

7. Perform some log switch checks to check whether the settings take effect.

SQL> alter system switch logfile;

The system has been changed.

SQL>/

The system has been changed.

Check the Operating System directories D: oracleoradatapeiyzarchive1 and D: oracleoradatapeiyzarchive3 for generating archive files. If yes, the setting is successful.

Ii. Two questions about testing in archive mode (RDBMS: 8.1.7.0/OS: Win2000)

A. Will the archive Path Modified using the alter system set... statement take effect after the next restart when the database is opened?

1. Modify the archive path

SQL> alter system set log_archive_dest_2 = 'location = D: oracleoradatapeiyzarchive2 ';

The system has been changed.

SQL> alter system set log_archive_dest_3 = 'location = D: oracleoradatapeiyzarchive4 ';

The system has been changed.

2. manually switch logs

SQL> alter system switch logfile;

The system has been changed.

SQL>/

The system has been changed.

3. Check D: oracleoradatapeiyzarchive2 and D: Check whether there are new archive log files under oracleoradatapeiyzarchive4. Check that only D: oracleoradatapeiyzarchive4 has new archive files generated; this indicates that the archive path can be dynamically modified only by defining parameters in the initialization parameter file. Although the log_archive_dest_3 parameter path can be modified and archive the archive file to the new path, after the system is restarted, can we save the dynamically modified archive path? We will know through the experiment below.

Note: If you use alter system set log_archive_dest_2 = 'location = D: oracleoradatapeiyzarchive2 ',In the system, the D: oracleoradatapeiyzarchive2 directory also exists, but the log is switched to logfile through alter system. If the log is switched, no archived log files are generated in the directory.Because the parameters are not defined in the initialization parameters.

4. Shutdown immediate

The database has been closed.
The database has been detached.
The Oracle routine has been disabled.

5. startup

The Oracle routine has been started.

Total system global area 51648540 bytes
Fixed size 75804 bytes
Variable Size 42651648 bytes
Database buffers 8388608 bytes
Redo buffers 532480 bytes
The database has been loaded.
The database has been opened.

6. Change the database to the automatic archiving mode.

SQL> archive log start;

Processed statements

7. Check archiving information

SQL> archive log list

Database Log mode archiving Mode
Enable automatic archiving
Archive endpoint D: oracleoradatapeiyzarchive3
Earliest summary information log sequence 76
Next archive log sequence 78
Current Log sequence 78

8. manually switch logs

SQL> alter system switch logfile;

The system has been changed.

SQL>/

The system has been changed.

9. Check the archive directory in the operating system

Check the Operating System D: Release and D: oracleoradatapeiyzarchive2 and D: oracleoradatapeiyzarchive3 and D: oracleoradatapeiyzarchive4. Only archive files in 1 and 3 are generated, it indicates that the dynamically modified parameters cannot take effect after the database is restarted (8i, if you want to continue to take effect after the restart 9i, some parameters can be specified by adding scope = both ), keep the settings defined in the initialization parameters. If you want to continue to take effect after the next restart, you must modify the content of the initialization parameter file.

B. What will happen if the database is in archive mode but the automatic archiving function is not enabled? How can this problem be solved? I will discuss the problem below

Test

1. Start the database

SQL> startup

The Oracle routine has been started.

Total system global area 51648540 bytes
Fixed size 75804 bytes
Variable Size 42651648 bytes
Database buffers 8388608 bytes
Redo buffers 532480 bytes
The database has been loaded.
The database has been opened.

2. Check database Archiving

SQL> archive log list

Database Log mode archiving Mode
Disable automatic archiving
Archive endpoint D: oracleoradatapeiyzarchive3
Earliest summary information log sequence 76
Next archive log sequence 78
Current Log sequence 78

Note:Although the database is in archive mode, the automatic archiving function is not enabled.

3. manually switch logs to see what will happen

SQL> alter system switch logfile;

The system has been changed.

SQL> alter system switch logfile;

The system has been changed.

SQL> /......//Here the log cannot be switched, Hang is here, and there is no "system changed ." Prompt, but the cursor is flashing

At this time, I checked the archive directory of the operating system and found no new archive log generation. This indicates that the initial log has not been archived after one week of log switching, at this time, the lgwr process must wait for the log to be archived before it can be used, so the database hang is here;
How can this problem be solved? Are you always waiting here? The answer is no..

You can solve this problem by using the following methods:

4. Open another sqlplus window to log on to the database as sysdba

SQL> Conn/As sysdba
Connected.

5. manually enable the automatic archiving function of the database

SQL> archive log start;

Processed statements

6. Check the Hang window and find that the log switch is complete.

SQL>/

The system has been changed.

7. Check the related archive directories in the operating system to find new archive logs generated.

The database works normally.

Another methodTo enable another sqlplus window when the database hangs, log on to the database as sysdba and perform the following operations:

1. First query the archive information

SQL> archive log list

Database Log mode archiving Mode
Disable automatic archiving
Archive endpoint D: oracleoradatapeiyzarchive3
Earliest summary information log sequence 81
Next archive log sequence 81
Current Log sequence 83

2. Manually archive. Because the earliest online log after the database is started is 81, archive the online Log File No. 81 first. Archive files 82 and 83:

SQL> alter system archive log sequence 81;

The system has been changed.

Note:

At this time, you can view the log operation in the Hang window and find that the log operation has been switched, as shown below:

SQL>/

The system has been changed.

Note the following:

A,If online log no. 83 is archived first, the following error is reported (because it is an online log currently in Hang and cannot be archived ):

SQL> alter system archive log sequence 83;

Alter system archive log sequence 83
*
Error is located in row 1st:
ORA-00259: log 2 (Open thread 1) is the current log and cannot be archived
ORA-00312: Online log 2 thread 1: 'd: oracleoradatapeiyzredo02.log'

B,If you archive the online Log File No. 82, the system automatically archives the online Log File No. 81. You do not need to archive the online Log File No. 81.

SQL> alter system archive log sequence 82;

The system has been changed.

SQL> alter system archive log sequence 81;

Alter system archive log sequence 81
*
Error is located in row 1st:
ORA-00260: Online log sequence 81 not found (thread 1)

3. Check the related directories in the operating system and find that all online logs are archived;

4. Of course, this is only a temporary solution. To stop the database from being hang and do not archive it manually, you must enable the automatic archiving function, that is:

SQL> archive log start;

Processed statements

Or

Add in the initialization parameter fileLog_archive_start = trueParameter options.