Shulou(Shulou.com)06/01 Report--

First, check whether the archiving mode is enabled

[oracle@db ~] $sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Wed Oct 17 07:24:38 2018

Copyright (c) 1982, 2011, Oracle. All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0-64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL > archive log list # to check whether it is enabled. The following shows that it is not enabled.

Database log mode No Archive Mode

Automatic archival Disabled

Archive destination USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence 15

Current log sequence 17

SQL >

2. Open the filing mode

SQL > shutdown immediate # shut down the instance

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL > startup mount # Boot to mount

ORACLE instance started.

Total System Global Area 1286066176 bytes

Fixed Size 2228024 bytes

Variable Size 352321736 bytes

Database Buffers 922746880 bytes

Redo Buffers 8769536 bytes

Database mounted.

SQL >

SQL > alter database archivelog; # enable archiving mode

Database altered.

SQL > archive log list; # View Archive Mode

Database log mode Archive Mode

Automatic archival Enabled

Archive destination USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence 15

Next log sequence to archive 17

Current log sequence 17

SQL >

SQL > alter database open; # Open the database

Database altered.

SQL > show parameter db_recovery

# View parameters db_recovery_file_dest archive log directory (default flashback recovery area) and db_recovery_file_dest_size size

NAME TYPE VALUE

-

Db_recovery_file_dest string / u01/app/oracle/fast_recovery_area

Db_recovery_file_dest_size big integer 2G

By default, the archive log is stored in USE_DB_RECOVERY_FILE_DEST (flashback recovery area flash_recovery_area). If the flashback recovery area is full, the archive log may not be able to continue archiving. The common solution is to increase the flashback recovery area, which can be implemented with the following SQL:

SQL > alter system set db_recovery_file_dest_size=3G

#

There is something called spfile in Oracle, which is a dynamic parameter file, which sets various parameters of Oracle. The so-called dynamic means that you can change the database parameters and record them in spfile without shutting down the database.

Syntax: alter system set parameter = value scope=spfile

Note: the difference between SCOPE=SPFILE/MEMORY/BOTH in ALTER SYSTEM:

SCOPE = SPFILE: this change is written to the initialization parameter file and will take effect the next time it starts.

SCOPE = MEMORY: it is only modified in memory and takes effect immediately, but it will no longer take effect after restart, because it is not written to the initialization parameter file. Only applicable to dynamic parameters, static parameters are not allowed.

SCOPE = BOTH: the default option, both written to the initialization parameter file and modified in memory, takes effect immediately. Similarly, it only applies to dynamic parameters, while static parameters are not allowed.

#

The flashback recovery area ORACLE will be managed automatically, and useless data will be cleaned up if there is not enough space. In addition, you can modify the path of the archived log and put the archived log under other unrestricted paths to solve this problem, that is, modify the storage path of the archived log through the following SQL:

SQL > alter system set log_archive_dest_1='location=/u01/app/oracle/archivelog'

SQL > archive log list

Database log mode Archive Mode

Automatic archival Enabled

Archive destination / u01/app/oracle/archivelog

Oldest online log sequence 15

Next log sequence to archive 17

Current log sequence 17

Starting with 10g, you can set up multiple archive paths to generate multiple identical logs:

SQL > alter system set log_archive_dest_2 = 'location=/home/oracle/archive_log'

SQL > archive log list

Database log mode Archive Mode

Automatic archival Enabled

Archive destination / home/oracle/archive_log

Oldest online log sequence 15

Next log sequence to archive 17

Current log sequence 17

# View the location of the archive log

SQL > show parameter log_archive_dest

NAME TYPE VALUE

-

Log_archive_dest string

Log_archive_dest_1 string location=/u01/app/oracle/archivelog

Log_archive_dest_10 string

Log_archive_dest_11 string

Log_archive_dest_12 string

Log_archive_dest_13 string

Log_archive_dest_14 string

Log_archive_dest_15 string

Log_archive_dest_16 string

Log_archive_dest_17 string

NAME TYPE VALUE

-

Log_archive_dest_18 string

Log_archive_dest_19 string

Log_archive_dest_2 string location=/home/oracle/archive_log

# Archive log format

SQL > show parameter log_archive_format

NAME TYPE VALUE

-

Log_archive_format string t_%s_%r.dbf

SQL > alter system set log_archive_format = "archive_%t_%s_%r.log" scope=spfile

System altered.

# number of archive log processes

SQL > show parameter log_archive_max_process

NAME TYPE VALUE

-

Log_archive_max_processes integer 4

SQL > alter system set log_archive_max_processes = 5

System altered.

SQL > show parameter log_archive_max_process

NAME TYPE VALUE

-

Log_archive_max_processes integer 5

# Archive the current redo log

SQL > alter system archive log current

SQL > select name from v$archived_log

NAME

/ u01/app/oracle/archivelog1_17_989497127.dbf

/ home/oracle/archive_log1_17_989497127.dbf

#

Alter system switch logfile is a mandatory log switch, so it is not necessary to archive the current redo log files (if automatic archiving is enabled, redo logs before archiving are enabled; if automatic archiving is not enabled, current redo logs are not archived. )

Alter system archive log current is the archiving of the current redo log files, regardless of whether the automatic archive is typed or not.

The main differences are:

Alter system switch logfile performs log switching to the current instance in a single instance database or RAC

Alter system archive log current performs a log switch on all instances in the database.

#

# check the space usage of the flashback recovery area

SQL > set linesize 300

SQL > select * from v$flash_recovery_area_usage

FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES

--

CONTROL FILE 0

REDO LOG 0

ARCHIVED LOG 0

BACKUP PIECE 0

IMAGE COPY 0

FLASHBACK LOG 0

FOREIGN ARCHIVED LOG 0

SQL > col name for A50

SQL > select * from v$recovery_file_dest

NAME SPACE_LIMIT SPACE_USED SPACE_RECLAIMABLE NUMBER_OF_FILES

/ u01/app/oracle/fast_recovery_area 4322230272 0 0

Third, modify to non-archiving mode

SQL > shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL > startup mount

ORACLE instance started.

Total System Global Area 1286066176 bytes

Fixed Size 2228024 bytes

Variable Size 352321736 bytes

Database Buffers 922746880 bytes

Redo Buffers 8769536 bytes

Database mounted.

SQL > alter database noarchivelog

SQL > alter database open

SQL > archive log list

Database log mode No Archive Mode

Automatic archival Disabled

Archive destination / home/oracle/archive_log

Oldest online log sequence 16

Current log sequence 18

Fourth, the method of dealing with the full filing space.

Here are two methods for manually deleting archive logs:

4.1. Delete the physical archive log manually, and then execute the following command

RMAN > DELETE ARCHIVELOG ALL COMPLETED BEFORE 'SYSDATE-7'

Description: SYSDATA-7, indicating that the current system time is 7 days ago, and the before keyword indicates the archived logs 7 days ago. If the flashback feature is used, the flashback data will also be deleted. By the same token, you can delete all logs from 7 days ago to the present, but this command should be carefully considered. After this deletion, it is best to make a full backup of the database immediately. DELETE ARCHIVELOG from TIME 'SYSDATE-7'; deletes all logs from 7 days ago to the present. Use with caution.

4.2. Under UNIX/LINUX, you can also find the archived data from 7 days ago through FIND, and delete it using EXEC sub-operation.

Find / oracle/oraarchive-xdev-mtime + 7-name "* .dbf"-exec rm-f {}

Or

Find / mnt/oradb/archivelog-type f-mtime + 1-exec rm {}

Then leave unmanaged archives in RMAN, and execute the following two commands in RMAN:

Crosscheck archivelog all

Delete expired archivelog all

Just type crosscheck archivelog all; one last time.

This article is transferred from hand to hand: https://www.shoubashou.com/db/oracle/102.html

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.