Shulou(Shulou.com)05/31 Report--

This article mainly shows you "oracle 11g how to open the archive mode and modify the archive directory", the content is easy to understand, clear, hope to help you solve your doubts, the following let the editor lead you to study and learn "oracle 11g how to open the archive mode and modify the archive directory" this article.

Oracle 11g turn on the archive mode and modify the archive directory

In Oracle 11g, when archive log mode is enabled, the default archive directory is specified as db_recovery_file_dest. This parameter can be specified in pfile/spfile:

Db_recovery_file_dest='/u01/app/oracle/flash_recovery_area'

Change the archive mode

The archiving mode needs to be changed in the mount state.

SQL > shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL > startup mount

ORACLE instance started.

If more than one library is installed, an error will be reported and the handle cannot be found

Exit re-use the administrator to enter

Total System Global Area 1258291200 bytes

Fixed Size 1219160 bytes

Variable Size 318768552 bytes

Database Buffers 922746880 bytes

Redo Buffers 15556608 bytes

Database mounted.

SQL > alter database archivelog

Database altered.

SQL > alter database open

Database altered.

SQL > archive log list

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

Change the log_archive_dest_1 parameter to change the archive log directory (the directory specified by parameter db_recovery_file_dest in pfile/spfile will be invalid)

The last directory name of SQL > alter system set log_archive_dest_1='location=/data/oracle/log1/archive_log'; needs to be archive_log!

Linux:alter system set log_archive_dest_1='location=/u01/oracle/log/archive_log'

System altered.

SQL > archive log list

Database log mode Archive Mode

Automatic archival Enabled

Archive destination / data/oracle/log1/archive_log

Oldest online log sequence 26

Next log sequence to archive 28

Current log sequence 28

In fact, starting from Oracle 10g, you can generate multiple identical logs and save multiple locations in case of accidents. The methods are as follows:

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

SQL > archive log list

Database log mode Archive Mode

Automatic archival Enabled

Archive destination / data/oracle/log2/archive_log can only see the most recently set archive directory.

Oldest online log sequence 30

Next log sequence to archive 32

Current log sequence 32

SQL > alter system archive log current

Update it.

System altered.

SQL > select name from v$archived_log

NAME

/ data/oracle/log1/archive_log1_6637_737857592.dbf

/ data/oracle/log2/archive_log1_6637_737857592.dbf

2rows selected.

Not tried to set as the default

When log_archive_log is set to the default value, the archive directory becomes? / dbs/arch.

SQL > alter system set log_archive_dest_1=''

System altered.

SQL > alter system set log_archive_dest_2=''

Press enter at the end of the 2;;

System altered.

SQL > archive log list

Database log mode Archive Mode

Automatic archival Enabled

Archive destination? / dbs/arch

Oldest online log sequence 33

Next log sequence to archive 35

Current log sequence 35

SQL > shutdown immediate

After restarting the database, the archive directory changes back to the directory specified by db_recovery_file_dest.

Startup mount

SQL > archive log list

Database log mode Archive Mode

Automatic archival Enabled

Archive destination USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence 33

Next log sequence to archive 35

Current log sequence 35

Modify the log file naming format:

SQL > alter system set log_archive_max_processes = 5

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

*

SQL > archive log list

If it is displayed as ENABLE, it means that the archive was successful.

Or SQL > select log_mode from v$database

If ARCHIVELOG, the archive has been successful.

*

SQL > show parameter db_recovery

NAME TYPE VALUE

-

Db_recovery_file_dest string / data/oracle/flash_recovery_area

Db_recovery_file_dest_size big integer 4231m

You can modify the size of the db_recovery_file_dest_size parameter

SQL > alter system set db_recovery_file_dest_size=21474836480

Shutdown immediate

Startup

Complete

The following are not tested:

Check to see if the oracle database is archived and modify the archive mode

Http://wenku.baidu.com/view/862b79d1b14e852458fb57fb.html

Operation mode of ORACLE 11G archive cleaning

Http://wenku.baidu.com/view/9b65e47402768e9951e7386a.html Database Archive Mode Management

Http://wenku.baidu.com/view/d46089a1b0717fd5360cdc9a.html**

Http://baike.baidu.com/view/3431413.htm

There are two ways:

1. Configure RMAN to automatically manage ARCHIVELOG. You can also back up the data to tape in RMAN and then delete the expired ARCHIVELOG

2. You can handle it manually, and the steps are as follows

1) move the relevant ARCHIVELOG log files under / oracle to another file system (keep the ARCHIVELOG log for a period of time, others can be removed, use the system command mv to remove). It is then packaged, compressed, and backed up to the media, where these removed files can be deleted. Note: don't pack in the original / oracle, otherwise there will be some trouble when the space is full.

2) Log in as oracle user and execute rman target /. If multiple instances execute rman target username / password @ instance name at this time, enter rman

3) execute in rman

RMAN > list archivelog all; / * list all archive log files

RMAN > crosscheck archivelog all; / * is synchronized with the physical archive log files, some of which have been removed before, so the archive logs that cannot be found in the / oracle directory will be marked as expired after executing this command

RMAN > list expired archivelog all; / * lists all expired (expired) archive log files, and you can see that the removed archive log files are marked as expired

RMAN > delete expired archivelog all; / * Delete all expired expired files in oracle

RMAN > list archivelog all; / * list all the archive log files, and you will find that the removed log files have been deleted

RMAN > exit / * exit

Http://hi.baidu.com/xyqq163/item/b16e73c32d705f25a0b50a97

Maintenance and Management of oracle Database Archive Mode

One is set to archiving mode.

-

01.1 sql > archive log list; # check whether it is archived

02.2 sql > alter system set log_archive_start=true scope=spfile; # enable active archiving

03. Sql > alter system set log_archive_dest='location=/oracle/ora9/oradata/arch' scope=spfile

04. # set the archive path

05. Sql > alter system set log_archive_dest_1='location=/oracle/ora9/oradata/arch2' scope=spfile

06. Sql > alter system set log_archive_dest_2='location=/oracle/ora9/oradata/arch3' scope=spfile

07. # if archived to two locations, it can be achieved through the above method

08. Sql > alter system set log_archive_format='arch_%d_%t_%r_%s.log' # set archive diary style

09.3 sql > shutdown immediate

10.4 sql > startup mount; # Open the control file, not the data file

11.5 sql > alter database archivelog; # switch the database to archive mode

12.6 sql > alter database open; # Open the data file

13.7 sql > archive log list; # to see if you are in archive mode at this time

14.8 query to determine that the database is in archivelog schema and that the archiving process is running

15.sql > select log_mode from v$database

16.sql > select archiver from v$instance

17.9 Log switching

18.sql > alter system switch logfile

19.10 this log switch writes the archive to two destinations

20. 1, that is, / oracle/ora9/oradata/arch2 and / oracle/ora9/oradata/arch2 in the second step, if you want to confirm the directory

21.

twenty-two。 Run the following query in the case of oracle:

23.sql > select name from v$archived_log

24. Then confirm the files listed in the query in the operating system

Second, set non-archiving mode

-

01.1 sql > archive log list; # check whether it is archived

02.2 sql > alter system set log_archive_start=false scope=spfile; # disable automatic archiving

03.3 sql > shutdown immediate

04.4 sql > startup mount; # Open the control file, not the data file

05.5 sql > alter database noarchivelog; # switch the database to non-archive mode

06.6 sql > alter database open; # Open the data file

07.7 sql > archive log list; # View is now in non-archive mode

Common sentences for daily maintenance:

1. Query the size of daily archive backups:

SELECT TRUNC (FIRST_TIME) "date", TRUNC (SUM (BLOCKS*BLOCK_SIZE) / 1024ax 1024Universe 2) "GB/DAY" FROM V$ARCHIVED_LOG GROUP BY TRUNC (FIRST_TIME) ORDER BY 1 DESC

two。 View archive space usage:

Select NAME,SPACE_LIMIT/1024/1024/1024,SPACE_USED/1024/1024/1024,NUMBER_OF_FILES from V$RECOVERY_FILE_DEST

# check the size unit is Gb

Http://zhidao.baidu.com/question/149648251.html

1. Query which set of log files the system uses: select * from vault log 2. Query the log file corresponding to the group in use: select * from vault log file 3. Forced log switching: alter system switch logfile;4. Query the history log: select * from vault log log. The archiving mode of query log: select dbid,name,created,log_mode from vault database share 6. Query the information of archived logs: select recid,stamp,thread#,sequence#,name from vault archived log 7. Add and delete log filegroup alter database add logfile group 1 ('/ home1/oracle/oradata/ora8i/log1a.log'),'/ home2/oracle/oradata/ora8i/log1b.log') size 100M alter database drop logfile group 1. Add and delete log member alter database add logfile member'/ home1/oracle/oradata/ora8i/log1a.log' to group 1 MagneTime alter database drop logfile member'/ home1/oracle/oradata/ora8i/log1a.log'; 9. Alter database rename file'/ home1/oracle/oradata/ora8i/log1a.log' to'/ home2/oracle/oradata/ora8i/log1a.log'; before executing this command, you must make sure that the log file has been physically moved to the new directory 10. Clear the log file alter database clear logfile'/ home1/oracle/oradata/ora8i/log1a.log'; this command cannot be used when deleting logs with the delete group and group members command

Http://www.2cto.com/database/201109/104615.html

The archive log log is full

ORA-00257: archiver error. How to deal with Connect internal only and until freed errors

1. Log in with the sys user

Sqlplus sys/pass@tt as sysdba

two。 Look at the location of archiv log.

SQL > show parameter log_archive_dest

NAME TYPE VALUE

-

Log_archive_dest string

Log_archive_dest_1 string

Log_archive_dest_10 string

3. When VALUE is empty, you can use archive log list; to check the archive directory and log sequence

SQL > archive log list

Database log mode Archive Mode

Automatic archival Enabled

Archive destination USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence 360

Next log sequence to archive 360

Current log sequence 362

4. Check the use of flash recovery area, you can see that archivelog has been very large, reaching 96.62

SQL > select * from V$FLASH_RECOVERY_AREA_USAGE

FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES

--

CONTROLFILE. 13 0 1

ONLINELOG 2.93 0 3

ARCHIVELOG 96.62 0 141

BACKUPPIECE 0 0 0

IMAGECOPY 0 0 0

FLASHBACKLOG 0 0 0

5. Calculate the space already occupied by flash recovery area

SQL > select sum (percent_space_used) * 3Accord 100 from v$flash_recovery_area_usage

SUM (PERCENT_SPACE_USED) * 3Compact 100

-

2.9904

6. Find the recovery directory, show parameter recover

SQL > show parameter recover

NAME TYPE VALUE

-

Db_recovery_file_dest string / u01/app/oracle/flash_recovery_area

Db_recovery_file_dest_size big integer 5G

Recovery_parallelism integer 0

7 the above results tell us that the archive location is the default value, which is placed under flash_recovery_area (db_recovery_file_dest directory = / u01/app/oracle/flash_recovery_area)

[root@sha3 10.2.0] # echo $ORACLE_BASE

/ u01/app/oracle

[root@sha3 10.2.0] # cd $ORACLE_BASE/flash_recovery_area/tt/archivelog

Transfer or clear the corresponding archive log, delete some files in the unused date directory, and pay attention to keeping the last few files (for example, after 360)

-

Note:

After deleting the archive log, you must use RMAN to maintain the control file, otherwise the space display will not be released.

-

8. Rman target sys/pass

[root@sha3 oracle] # rman target sys/pass

Recovery Manager: Release 10.2.0.4.0-Production on Tue Jan 20 01:41:26 2009

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

Connected to target database: tt (DBID=4147983671)

9. Check some useless archivelog

RMAN > crosscheck archivelog all

10. Delete expired archives

RMAN > delete expired archivelog all

Delete archivelog until time 'sysdate-1'; delete all archivelog as of the previous day

11. Check again and find that the utilization rate is normal, which has dropped to 23.03.

SQL > select * from V$FLASH_RECOVERY_AREA_USAGE

FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES

--

CONTROLFILE. 13 0 1

ONLINELOG 2.93 0 3

ARCHIVELOG 23.03 0 36

BACKUPPIECE 0 0 0

IMAGECOPY 0 0 0

FLASHBACKLOG 0 0 0

Other useful Command:

-

If the startup cannot be normal in archive log mode, it will be restored to noarchive log,startup first and then shutdown.

Shutdown immediate

Startup mount

Alter database noarchivelog

Alter database open

Shutdown immediate

Startup again in archive log mode

Shutdown immediate

Startup mount

Show parameter log_archive_dest

Alter database archivelog

Archive log list

Alter database open

If not, delete some archlog log

SQL > select group#,sequence# from v$log

GROUP# SEQUENCE#

--

1 62

3 64

2 63

It turns out that a log of log group one cannot be archived.

SQL > alter database clear unarchived logfile group 1

Alter database open

Finally, you can also specify the location Arch Log, please configure as follows

Select name from v$datafile

Alter system set log_archive_dest='/opt/app/oracle/oradata/usagedb/arch' scope=spfile

Or change the size.

SQL > alter system set db_recovery_file_dest_size=3G scope=both

The above is all the contents of the article "how to turn on oracle 11g Archive Mode and modify Archive Catalog". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

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.