Oracle归档
Oracle归档
1.开启归档
1.1 查看归档状态
使用以下命令查看归档状态
SQL> ARCHIVE LOG LIST;
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination /u01/app/oracle/product/12.2.0/db_1/dbs/arch
Oldest online log sequence 13
Current log sequence 16
SQL>
可以看到没有开启归档
1.2 创建归档目录
以root用户创建归档文件存放的目录，并修改所属用户和所属组。
[root@oracle ~]# mkdir /backup
[root@oracle ~]# chown -R oracle:oinstall backup/
chown: cannot access ‘backup/’: No such file or directory
[root@oracle ~]# chown -R oracle:oinstall /backup
[root@oracle ~]# ll /
total 20
drwxr-xr-x. 2 oracle oinstall 6 Nov 29 21:13 backup
lrwxrwxrwx. 1 root root 7 Nov 12 17:02 bin -> usr/bin
dr-xr-xr-x. 5 root root 4096 Nov 26 22:55 boot
drwxr-xr-x. 20 root root 3340 Nov 29 21:05 dev
drwxr-xr-x. 85 root root 8192 Nov 29 21:05 etc
drwxr-xr-x. 3 root root 20 Nov 12 20:54 home
lrwxrwxrwx. 1 root root 7 Nov 12 17:02 lib -> usr/lib
lrwxrwxrwx. 1 root root 9 Nov 12 17:02 lib64 -> usr/lib64
drwxr-xr-x. 2 root root 6 Apr 11 2018 media
drwxr-xr-x. 2 root root 6 Apr 11 2018 mnt
drwxr-xr-x. 4 root root 32 Nov 13 19:27 opt
dr-xr-xr-x. 190 root root 0 Nov 29 21:04 proc
dr-xr-x---. 4 root root 180 Nov 13 19:07 root
drwxr-xr-x. 27 root root 800 Nov 29 21:05 run
lrwxrwxrwx. 1 root root 8 Nov 12 17:02 sbin -> usr/sbin
drwxr-xr-x. 2 root root 6 Apr 11 2018 srv
dr-xr-xr-x. 13 root root 0 Nov 29 21:05 sys
drwxrwxrwt. 13 root root 4096 Nov 29 21:13 tmp
drwxr-xr-x. 5 oracle oinstall 124 Nov 12 21:13 u01
drwxr-xr-x. 13 root root 155 Nov 12 17:02 usr
drwxr-xr-x. 20 root root 282 Nov 12 17:27 var
drwxr-xr-x. 4 root root 38 Nov 12 20:08 yums
[root@oracle ~]# su - oracle
Last login: Tue Nov 29 21:11:26 CST 2022 on pts/2
[oracle@oracle ~]$ cd /backup/
[oracle@oracle backup]$ mkdir arch
[oracle@oracle backup]$ ls -l
total 0
drwxr-xr-x. 2 oracle oinstall 6 Nov 29 21:18 arch
1.3 设置归档目录
SQL> show parameter log_archive_dest
NAME TYPE
------------------------------------ ----------------------
VALUE
------------------------------
log_archive_dest string
log_archive_dest_1 string
log_archive_dest_10 string
log_archive_dest_11 string
log_archive_dest_12 string
NAME TYPE
------------------------------------ ----------------------
VALUE
------------------------------
log_archive_dest_13 string
log_archive_dest_14 string
log_archive_dest_15 string
log_archive_dest_16 string
NAME TYPE
------------------------------------ ----------------------
VALUE
------------------------------
log_archive_dest_17 string
log_archive_dest_18 string
log_archive_dest_19 string
log_archive_dest_2 string
log_archive_dest_20 string
NAME TYPE
------------------------------------ ----------------------
VALUE
------------------------------
log_archive_dest_21 string
log_archive_dest_22 string
log_archive_dest_23 string
log_archive_dest_24 string
NAME TYPE
------------------------------------ ----------------------
VALUE
------------------------------
log_archive_dest_25 string
log_archive_dest_26 string
log_archive_dest_27 string
log_archive_dest_28 string
log_archive_dest_29 string
NAME TYPE
------------------------------------ ----------------------
VALUE
------------------------------
log_archive_dest_3 string
log_archive_dest_30 string
log_archive_dest_31 string
log_archive_dest_4 string
NAME TYPE
------------------------------------ ----------------------
VALUE
------------------------------
log_archive_dest_5 string
log_archive_dest_6 string
log_archive_dest_7 string
log_archive_dest_8 string
log_archive_dest_9 string
1.4 设置归档目录
- 设置归档目录
SQL> alter system set log_archive_dest_1 = 'location=/backup/arch';
System altered.
SQL>
-
再次查看归档目录
SQL> show parameter log_archive_dest NAME TYPE VALUE ------------------------------------ ---------------------- ------------------------------ log_archive_dest string log_archive_dest_1 string location=/backup/arch 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 log_archive_dest_18 string NAME TYPE VALUE ------------------------------------ ---------------------- ------------------------------ log_archive_dest_19 string log_archive_dest_2 string log_archive_dest_20 string log_archive_dest_21 string log_archive_dest_22 string log_archive_dest_23 string log_archive_dest_24 string log_archive_dest_25 string log_archive_dest_26 string log_archive_dest_27 string log_archive_dest_28 string NAME TYPE VALUE ------------------------------------ ---------------------- ------------------------------ log_archive_dest_29 string log_archive_dest_3 string log_archive_dest_30 string log_archive_dest_31 string log_archive_dest_4 string log_archive_dest_5 string log_archive_dest_6 string log_archive_dest_7 string log_archive_dest_8 string log_archive_dest_9 string
1.5 设置归档文件名称格式
-
查看归档文件格式
SQL> show parameter log_archive_format NAME TYPE VALUE ------------------------------------ ---------------------- ------------------------------ log_archive_format string %t_%s_%r.dbf SQL> -
设置归档文件格式
设置log_archive_format参数，该参数是静态参数，所以scope必须设置成spfile。
SQL> alter system set log_archive_format='orcl_arch_%t_%s_%r.arc' scope=spfile; System altered. SQL> -
查看归档文件格式
此时未生效，重启数据库后生效
SQL> show parameter log_archive_format NAME TYPE VALUE ------------------------------------ ---------------------- ------------------------------ log_archive_format string %t_%s_%r.dbf SQL>
1.6 一致性关闭数据库
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
1.7 将数据库启动到mount模式
SQL> startup mount
ORACLE instance started.
Total System Global Area 838860800 bytes
Fixed Size 8626240 bytes
Variable Size 322965440 bytes
Database Buffers 499122176 bytes
Redo Buffers 8146944 bytes
Database mounted.
SQL>
1.8 开启归档
SQL> alter database archivelog;
Database altered.
SQL>
1.9 数据库打开open状态
SQL> alter database open;
Database altered.
SQL>
1.10 检查数据库是否为归档模式
SQL> ARCHIVE LOG LIST;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /backup/arch
Oldest online log sequence 13
Next log sequence to archive 16
Current log sequence 16
SQL>
1.11 切换日志组
-
手动切换归档
联机归档日志会flush到归档目录
SQL> alter system archive log current; System altered. SQL> -
查看归档
[oracle@oracle arch]$ ls orcl_arch_1_16_1120685375.arc [oracle@oracle arch]$
本文来自博客园，作者：何以卿卿，转载请注明原文链接：https://www.cnblogs.com/shiqiang-lee/p/16936958.html
