ALTER DATABASE REGISTER LOGFILE 手工注册日志测试

最新推荐文章于 2023-10-17 10:37:31 发布

转载最新推荐文章于 2023-10-17 10:37:31 发布 · 1.3k 阅读

文章标签：

#数据库

本文介绍在Oracle数据库中手工注册归档日志的方法，并通过示例展示如何创建用户、授予权限、创建表及进行日志切换等操作。此外，还演示了如何通过FTP传输归档文件以及在备库中进行恢复的过程。

ALTER DATABASE REGISTER LOGFILE 手工注册日志测试[@more@]

不设置 log2

手工同步arc

ALTER DATABASE REGISTER LOGFILE '/trunkbow/oracle/oradata/mmcdb/archive/1_6.dbf';

SQL> create user wrj identified by wrj;

User created.

SQL>
SQL>
SQL> grant dba,connect,resource to wrj;

Grant succeeded.

SQL> grant unlimited tablespace to wrj;

Grant succeeded.

SQL> connect wrj/wrj;
Connected.
SQL> create table test1 as select * from dba_objects;

Table created.

SQL>
SQL>
SQL>
SQL> alter system switch logfile;

System altered.

SQL> connect / as sysdba
Connected.
SQL>
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /trunkbow/oracle/oradata/mmcdb/archive
Oldest online log sequence 5
Next log sequence to archive 7
Current log sequence 7

ftp 172.150.34.17
Connected to 172.150.34.17.
220 MMC-APP-1 FTP server ready.
Name (172.150.34.17:oracle): oracle
331 Password required for oracle.
Password:
230 User oracle logged in.
Remote system type is UNIX.
Using binary mode to transfer files.
ftp> bin
200 Type set to I.
ftp> cd /trunkbow/oracle/oradata/mmcdb/archive
250 CWD command successful.
ftp> mput 1*
mput 1_6.dbf? y
200 PORT command successful.
150 Opening BINARY mode data connection for 1_6.dbf.
226 Transfer complete.
local: 1_6.dbf remote: 1_6.dbf
3804672 bytes sent in 0.32 seconds

日志过来，如果没有手工注册，备库不自动恢复：

观察备库alert

alter database recover managed standby database disconnect from session
Attempt to start background Managed Standby Recovery process
MRP0 started with pid=11, OS id=11137
MRP0: Background Managed Standby Recovery process started
Media Recovery Waiting for thread 1 seq# 6
Thu Jan 10 12:53:49 2008
Completed: alter database recover managed standby database di

手工注册

SQL> show parameter standby

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
standby_archive_dest string /trunkbow/oracle/oradata/mmcdb
/archive
standby_file_management string AUTO
SQL> show parameter fal

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
fal_client string MMCDBBAK
fal_server string MMCDB
SQL> ALTER DATABASE REGISTER LOGFILE '/trunkbow/oracle/oradata/mmcdb/archive/1_6.dbf';

Database altered.

观测备用库 alert 日志

Media Recovery Waiting for thread 1 seq# 6
Thu Jan 10 12:53:49 2008
Completed: alter database recover managed standby database di
Thu Jan 10 13:04:57 2008
ALTER DATABASE REGISTER LOGFILE '/trunkbow/oracle/oradata/mmcdb/archive/1_6.dbf'
Thu Jan 10 13:04:57 2008
There are 1 logfiles specified.
ALTER DATABASE REGISTER [PHYSICAL] LOGFILE
Completed: ALTER DATABASE REGISTER LOGFILE '/trunkbow/oracle/
Thu Jan 10 13:05:04 2008
Media Recovery Log /trunkbow/oracle/oradata/mmcdb/archive/1_6.dbf
Media Recovery Waiting for thread 1 seq# 7

QL> select ARCHIVED_THREAD#, ARCHIVED_SEQ# , APPLIED_THREAD# , APPLIED_SEQ# ,status from v$archive_dest_status;

ARCHIVED_THREAD# ARCHIVED_SEQ# APPLIED_THREAD# APPLIED_SEQ# STATUS
---------------- ------------- --------------- ------------ ---------
0 0 0 0 VALID
0 0 0 0 INACTIVE
0 0 0 0 INACTIVE
0 0 0 0 INACTIVE
0 0 0 0 INACTIVE
0 0 0 0 INACTIVE
0 0 0 0 INACTIVE
0 0 0 0 INACTIVE
0 0 0 0 INACTIVE
0 0 0 0 INACTIVE
0 0 0 0 VALID

11 rows selected.

SQL> select process,client_process,status,sequence#,block# from v$managed_standby;

PROCESS CLIENT_P STATUS SEQUENCE# BLOCK#
--------- -------- ------------ ---------- ----------
ARCH ARCH CONNECTED 0 0
ARCH ARCH CONNECTED 0 0
MRP0 N/A WAIT_FOR_LOG 7 0

SQL> select OPEN_MODE,PROTECTION_MODE,PROTECTION_LEVEL,SWITCHOVER_STATUS from v$database;

OPEN_MODE PROTECTION_MODE PROTECTION_LEVEL SWITCHOVER_STATUS
---------- -------------------- -------------------- ------------------
MOUNTED MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE NOT ALLOWED

SQL> select recovery_mode from v$archive_dest_status;

RECOVER
-------
MANAGED

=================================================================================

alter system set log_archive_dest_2='SERVICE=mmcdbbak REOPEN=100' scope=both;

SQL> alter system set log_archive_dest_2='SERVICE=mmcdbbak REOPEN=100' scope=both;

System altered.

SQL> alter system set log_archive_dest_state_2=enable scope=both;

System altered.

SQL> connect wrj/wrj
Connected.
SQL> create table test2 as select * from dba_objects;

Table created.

SQL> alter system archive log current;

System altered.

SQL> select OPEN_MODE,PROTECTION_MODE,PROTECTION_LEVEL,SWITCHOVER_STATUS from v$database;

OPEN_MODE PROTECTION_MODE PROTECTION_LEVEL SWITCHOVER_STATUS
---------- -------------------- -------------------- ------------------
MOUNTED MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE NOT ALLOWED

SQL> select ARCHIVED_THREAD#, ARCHIVED_SEQ# , APPLIED_THREAD# , APPLIED_SEQ# ,status from v$archive_dest_status;

Completed: ALTER DATABASE REGISTER LOGFILE '/trunkbow/oracle/
Thu Jan 10 13:05:04 2008
Media Recovery Log /trunkbow/oracle/oradata/mmcdb/archive/1_6.dbf
Media Recovery Waiting for thread 1 seq# 7
Media Recovery Log /trunkbow/oracle/oradata/mmcdb/archive/1_7.dbf
Media Recovery Waiting for thread 1 seq# 8

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/66233/viewspace-997030/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/66233/viewspace-997030/