This article describes how to switch archivelog/noarchivelog mode in Oracle,Also
it give a short introduction on how to maintain archivelog files using RMAN.
Note:
In Oracle Enterprise Manager , we can switch to archivelog
mode easily.But, It's still useful to know how to implement
it using SQLPLUS.
Details:
1. Invoke SQLPLUS, issue the following command:
SQL> archive log list
From the output, we can get these information:
. Whether current database is running under archivelog
mode ;
. The store location of archivelog files ;
. The current and next SCN
2. If current database isn't running under archivelog mode,
please finish the following operations to switch to
archivelog mode:
For 9i:
SQL> show parameter spfile;
If the result shows the directory of spfile's location
is not null,which represents current initialization
parameter file is spfile, otherwise is pfile.
SQL> create pfile from spfile;
SQL> shutdown immediate;
SQL> exit
Open the init<sid></sid>.ora from this location:
UNIX: $ORACLE_HOME/dbs
Windows: $ORACLE_HOMEdatabase
Add or edit the following rows, for example:
log_archive_dest_n(n=1,2,3,.....) ='Location=...'
log_archive_start=true
Note:
1. Change the directory to actual direcotry of
your environment;
2. Create the directory first if the directory
doesn't exist.
Invoke sqlplus(use SYS with sysdba privilege or
use OS authentication)
SQL> startup mount pfile='/u01/oracle/10gdb/
orcl/dbs/init<sid></sid>.ora'
SQL> alter database archivelog;
SQL> alter database open;
SQL> archive log list (Check the output)
SQL> alter system switch logfile;
(check whether a new archivelog file has been
generated under your appointed dir)
SQL> create spfile from pfile='/u01/oracle/10gdb
/orcl/dbs/init<sid></sid>.ora'
SQL> shutdown immediate;
SQL> exit
Invoke sqlplus again:
SQL> startup (spfile will be used this time)
For 10G:
By default, 10G use spfile. spfile is a binary file so
that we can't modify it. we must use a pfile based
on this spfile and modify this pfile.
SQL> create pfile from spfile;
Open the init<sid></sid>.ora from this location:
UNIX: $ORACLE_HOME/dbs
Windows: $ORACLE_HOMEdatabase
Because the parameter 'log_archive_start' is obsolete,
if we find this parameter from init<sid></sid>.ora, we should delete
it or remark it.
In 10G, You can have two available solutions about how
to store archivelog files:
. Keeping the old store mode (log_archive_dest_n);
. Using flash recovery area instead of defining old
parameters (log_archive_dest_n);
In order to use flash recovery area, You need to
define:
db_recovery_file_dest= ...
db_recovery_file_dest_size=...
Steps:
SQL> alter system set db_recovery_file_dest=
'...' scope=spfile;
SQL> alter system set db_recovery_file_dest_size=2G
scope=spfile;
SQL> create pfile from spfile;
SQL> shutdown immediate;
SQL> exit
Invoke sqlplus again:
SQL> startup mount pfile='xxx'
SQL> alter database archivelog;
the following sql statement is optional:
SQL> alter database flashback on;
Note:
If you also want to enable flashback,pleas add the
following parameter from init<sid></sid>.ora:
db_flashback_retention_target=1440
(1440 means database will save last 24 hours'
operation in order to implement flashback)
SQL> archive log list
SQL> alter system switch logfile;
(check whether a new archive log has been
generated under your appointed dir)
SQL> alter database open;
SQL> create spfile from pfile='xxx'
SQL> shutdown immediate;
SQL> exit
Invoke sqlplus again:
SQL> startup (spfile will be used this time)
3. If current database is running under archivelog mode,
please finish the following operations to switch to
noarchivelog mode (10G)
Steps:
1. Backup all archivelog files first ;
2. Invoke sqlplus:
SQL> create pfile from spfile;
SQL> shutdown immediate;
SQL> exit
Open the init<sid></sid>.ora from this location:
UNIX: $ORACLE_HOME/dbs
Windows: $ORACLE_HOMEdatabase
delete or remark these lines:
log_archive_dest_n=...
db_recovery_file_dest=...
db_recovery_file_dest_size=...
db_flashback_retention_target=...
Invoke sqlplus:
SQL> startup mount pfile='xxx'
# this operation will delete all flashback files
SQL> alter database flashback off
SQL> alter database noarchivelog;
SQL> alter database open;
SQL> create spfile from pfile='xxx'
SQL> shutdown immediate;
SQL> exit
Now, you can delete all archive log files safely.
sometimes, there will be large log or trace files
under $ORACLE_BASE/admin/sid/bdump(udump),
delete them also.
Invoke sqlplus again:
SQL> startup (spfile will be used this time)
4. Using RMAN to manage archivelog files
Usually, the value for parameter 'db_recovery_file_dest_size'
is too small to hold so much archive log files. we must
define a reasonable policy to delete unuseful archive log files.
Please read RMAN related document first.
Refer:
http://download-west.oracle.com/docs/cd/B14117_01/server.101/b10734/toc.htm
Short Introduction:
. Finish at least one full database backup first;
for example: (10G)
run{
allocate channel c1 type disk format 'xxx/full%u_%s_%p.bak'
maxpiecesize 10 M;
allocate channel c2 type disk format 'xxx/full%u_%s_%p.bak'
maxpiecesize 10 M;
allocate channel c3 type disk format 'xxx/full%u_%s_%p.bak'
maxpiecesize 10 M;
delete noprompt obsolete;
# Backup full database
backup filesperset = 20 keep until time 'SYSDATE+30' logs as
BACKUPSET tag 'fullbak' database include current controlfile;
sql'alter system archive log current';
release channel c1;
release channel c2;
release channel c3;
}
allocate channel for maintenance type disk;
delete noprompt obsolete device type disk;
release channel;
# List backup
list backup;
. Write an archivelog rman backup script and run it termly;
for example: (10G)
run{
allocate channel c1 type disk ;
allocate channel c2 type disk ;
allocate channel c3 type disk ;
sql'alter system archive log current';
backup filesperset = 20 as BACKUPSET tag 'archivebak'
archivelog alldelete all input format
'/xxxarch%u_%s_%p.bak';
release channel c1;
release channel c2;
release channel c3;
}
# List backup
list backup of archivelog all;
. Common useful RMAN commands:
Crosscheck ARCHIVELOG xxx
(xxx means rman log sequence)
# Crosscheck backupsets
CROSSCHECK BACKUPSET xxx
(xxx means backupset number)
# crosschecks backup sets and image copies
CROSSCHECK BACKUP;
# Crosscheck all(backupsets and archive log files)
CROSSCHECK BACKUPSET;
CROSSCHECK COPY;
DELETE NOPROMPT ARCHIVELOG xxx
(xxx means rman log number)
Delete noprompt backupset xxx
(xxx means backupset number)
# Batch remove archivelog files
Delete noprompt archivelog until sequence=xxx;
# Job: Delete obsolete
DELETE NOPROMPT OBSOLETE;
# Job: Delete all unavailable backupsets and archivelogs:
DELETE NOPROMPT EXPIRED BACKUP;
DELETE NOPROMPT EXPIRED COPY;
分享到:
相关推荐
oracle归档日志步骤+RMAN步骤 . set ORACLE_SID=EKP sqlplus /nolog conn / as sysdba SQL> archive log list; show parameter db_reco show parameter log_archive alter system set db_recovery_file_...
采用Oracle Archive Log模式和非Archive Log模式对备份恢复的影响。努力学习,天天向前。
NULL 博文链接:https://laozy.iteye.com/blog/600061
You’ll learn how to understand Oracle database architecture, set up and manage an Oracle database, and keep it running in tiptop form. Oracle 11g For Dummies covers: The building blocks behind the ...
Oracle9RMAN设置和配置 RMAN设置和配置 1.9i下将数据库配置为ARCHIVELOG模式 虽说RMAN也可以在非归档模式下使用,但是我们不推荐。因为非归档模式下很多RMAN的功能都用不上。在9i中必须配置如下参数: 􀂾 ...
ORACLE啟用歸檔模式及建立ARCHIVE LOG副本
RMAN学习测试整理1 20121219 by Apollo 一、 Oracle数据库的备份分为物理备份和逻辑备份。今天测试整理下物理备份Rman,也就是oracle的恢复管理器(Recovery Manager)。 毕竟逻辑备份是不能实现时间点恢复的,所以...
CDC异步autolog archivelog模式配置
rman的备份与恢复 , 常见操作 1、切换服务器归档模式,如果已经是归档模式可跳过此步: %sqlplus /nolog (启动sqlplus) ...SQL> alter system archive log start; (启用自动归档) SQL> exit (退出) 2、连接:
Select Created, Log_Mode, Log_Mode From V$Database; 四、ORACLE用户连接的管理 用系统管理员,查看当前数据库有几个用户连接: SQL> select username,sid,serial# from v$session; 如果要停某个连接用 SQL> ...
一、rman相关配置1、进入rman:CMD下rman target/2、查看是否处于归档模式:SQL>archive log list;(rman需运行在归档模式下)3、开启归档模式: 代码如下:SQL>shutdown immediate;SQL>startup mount;SQL>alter ...
介绍了oracle自动清理archivelog文件的具体方法,有需要的朋友可以参考一下
Oracle 数据库物理体系结构 Oracle 操作内核 ARCHIVELOG模式操作与 NOARCHIVELOG 模式操作 Oracle 恢复模式 Oracle 中的手动备份操作 Oracle 中的手动恢复操作
SQL>archive log list; SQL>shutdowin immediate;--关闭数据库 SQL>startup mount;--装载数据库 SQL>alter database archivelog --设置归档状态 SQL>startup;--启动 1.进入rman a)连接到本地数据库: ...
ORACLE 数据库备份分为物理备份和逻辑备份。...热备份是在数据库运行的情况下,采用archivelog mode方式备份数据的方法。这涉及到将每个表空间设置为备份状态,然后备份其数据文件,最后将表空间恢复成正常的状态。
我的oracle rman应用实践,nocatalog,也就是仅使用control file Full Database backup 目标database是Archivelog模式 备份完成后删除所有的数据文件,包括control file, data file, redo logs, rollback files, etc...
Oracle在开启了归档模式后,会在指定的archive目录下产生很多的archivelog文件,而且默认是不会定期清除的,时间长久了,该文件夹会占用很大的空间。 问题:如何定期正确删除archivelog文件呢? 很多人直接在archive...
查询日志模式: archive log list; shutdown immediate; startup mount; 把日志改为归档模式: alter database archivelog; alter database open; alter system archive log current; select name from v$archived_...
1.rman完全恢复的前提条件:历史的datafile,controlfile和spfile备份,加上完整的archivelog和完好的redolog。 2.rman备份脚本: a.RMAN0级备份命令: run{ allocatechannelc1typedisk; ...