First, you can use the following statement to view the existing database mode:
Select name, log_mode from v $ database;
You can also use the following statement
Archive log list; (This method requires the as sysdba)
SQL> archive log list
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination/export/home/oracle/product/8.1.7/dbs/arch
Oldest online log sequence 28613
Current log sequence 28615
SQL> SELECT NAME, LOG_MODE FROM V $ DATABASE;
NAME LOG_MODE
--------------------
BIGSUN NOARCHIVELOG
To archive a non-archive database, follow these steps:
1. SQL> alter system set log_archive_dest_1 = 'location =/oracle/oracle10g/log/archive_log ';
This statement determines the path of the archived log. In fact, Oracle 10 Gb can generate multiple identical logs and store multiple locations to prevent unexpected logs.
For example, you can use the following statement to add another log location:
SQL> alter system set log_archive_dest_2 = 'location =/oracle/oracle10g/log2/archive_log ';
2. Shut down the database
SQL> shutdown immediate
3. Start the data mount status:
SQL> startup mount;
4. Change the database to archive mode:
SQL> alter database archivelog;
5. Open the database and query:
SQL> alter database open;
Modify the log file name format:
SQL> alter system set log_archive_max_processes = 5;
SQL> alter system set log_archive_format = "archive _ % t _ % s _ % r. log" scope = spfile;
After modification, you can check whether the log mode has been modified successfully!
In particular, you must modify the log_archive_start = true parameter in Oracle 9i to take effect. This parameter has been abolished in oracle 10g, so you do not need to set this parameter.
Oracle 9i starts Archiving
1. Run sqlplus "/as sysdba" on the doscommand line to go to the SQL Administrator console.
2. shutdown immediate; shut down the ORACLE database.
3. startup mount; start the database to the mount status.
4. alter database archivelog (noarchivelog); Enable (disable) archive mode.
5. alter database open; open the database.
6. archive log list; check whether the archive mode is enabled.
7. archive log start (stop); Enable (stop) Archiving mode.
8. alter system switch logfile; -- force the system to switch logs. You can immediately observe the generation of archived logs.
Archive mode to non-archive Mode
All operations here must be performed on the database's machine. Log On with the SYS user and set the archive process to close.
Alter system set log_archive_start = false scope = spfile;
Close the database.
Shutdown immediate
Then, start the database to the mount mode.
Startup mount
Close the flash back database mode, if not closed, there will be a nasty ORA-38774 error when you close archiving logs later.
Alter database flashback off
Change the database to non-archive mode.
Alter database noarchivelog;
After modification, open the database
Alter database open;
Check the space of archived logs.
Select * from v $ recovery_file_dest;
Next, let's take a look at the log status.
Select * from v $ log;
Check the usage of the flash back log.
Select * from v $ flash_recovery_area_usage;
If there are no problems, the database problems will be solved.
From: David Dai Oracle notes