使用rman的duplicate来创建备库,过程简洁了不少,无需在手动创建备库控制文件。
主库SPFILE
*.log_archive_format='%T%S%r.ARC'*.DB_UNIQUE_NAME='primary'
*.log_archive_config='DG_CONFIG=(primary,standby)'
*.log_archive_dest_1='location=C:/oracle/product/10.2.0/oradata/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=primary'
*.log_archive_dest_2='SERVICE=standby arch ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=standby'
*.STANDBY_FILE_MANAGEMENT=AUTO
*.LOG_ARCHIVE_DEST_STATE_1=ENABLE
*.LOG_ARCHIVE_DEST_STATE_2=ENABLE
*.FAL_SERVER='standby'
*.FAL_CLIENT='primary'
*.log_archive_format='%T%S%r.ARC'
*.DB_UNIQUE_NAME='standby'
*.log_archive_config='DG_CONFIG=(primary,standby)'
*.log_archive_dest_1='location=C:/oracle/product/10.2.0/oradata/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=standby'
*.log_archive_dest_2='SERVICE=primary arch ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=primary'
*.STANDBY_FILE_MANAGEMENT=AUTO
*.LOG_ARCHIVE_DEST_STATE_1=ENABLE
*.LOG_ARCHIVE_DEST_STATE_2=ENABLE
*.FAL_SERVER='primary'
*.FAL_CLIENT='standby'
配置好TNS 监听能互相TNSPING通
主库操作
修改LOGFILE大小为100M,添加4组备用联机日志
SQL> alter database add logfile group 4 'c:\oracle\product\10.2.0\oradata\orcl\redolog04.log' size 50M;
SQL> alter database add logfile group 5 'c:\oracle\product\10.2.0\oradata\orcl\r
edolog05.log' size 50M;
SQL> alter system switch logfile;
SQL> alter database drop logfile group 1;
SQL> alter database drop logfile group 2;
SQL> alter database drop logfile group 3;
SQL> alter database add logfile group 2 'c:\oracle\product\10.2.0\oradata\orcl\r
edolog02.log' size 100M;
SQL> alter database add logfile group 3 'c:\oracle\product\10.2.0\oradata\orcl\r
edolog03.log' size 100M;
SQL> alter database drop logfile group 4;
SQL> select group#,sequence#,status from v$log;
GROUP# SEQUENCE# STATUS
---------- ---------- --------------------------------
1 15 CURRENT
2 0 UNUSED
3 0 UNUSED
5 14 ACTIVE
SQL> alter system switch logfile;
SQL> select group#,sequence#,status from v$log;
GROUP# SEQUENCE# STATUS
---------- ---------- --------------------------------
1 19 INACTIVE
2 20 ACTIVE
3 21 CURRENT
5 18 INACTIVE
SQL> alter database drop logfile group 5;
数据库已更改。
SQL> select group#,sequence#,status from v$log;
GROUP# SEQUENCE# STATUS
---------- ---------- --------------------------------
1 19 INACTIVE
2 20 ACTIVE
3 21 CURRENT
SQL> alter database add standby logfile group 4 'c:\oracle\product\10.2.0\oradat
a\orcl\standbylog04.log' size 50M;
数据库已更改。
SQL> alter database add standby logfile group 5 'c:\oracle\product\10.2.0\oradat
a\orcl\standbylog05.log' size 50M;
数据库已更改。
SQL> alter database add standby logfile group 6 'c:\oracle\product\10.2.0\oradat
a\orcl\standbylog06.log' size 50M;
数据库已更改。
SQL> alter database add standby logfile group 7 'c:\oracle\product\10.2.0\oradat
a\orcl\standbylog07.log' size 50M;
数据库已更改。
-- 添加日志组成员方法
SQL> alter database add logfile member 'c:\oracle\product\10.2.0\oradata\orcl\re
dolog012.log' to group 1;
数据库已更改。
--删除日志组成员方法
SQL> alter database drop logfile member 'c:\oracle\product\10.2.0\oradata\orcl\
redolog012.log';
数据库已更改。
RMAN全备数据库 控制文件 归档日志
RMAN> backup full format 'c:\backup\fullbackup_%d_%T_%s.bak' database include cu
rrent controlfile for standby;
sql 语句: alter system archive log current
RMAN> backup archivelog all format 'c:\backup\archive_%d_%T_%s';
拷贝备份到备库相同目录c:\backup下
备库操作C:\Documents and Settings\Administrator>rman target sys/oracle@primary auxiliary
/
恢复管理器: Release 10.2.0.1.0 - Production on 星期三 11月 6 16:04:22 2013
Copyright (c) 1982, 2005, Oracle. All rights reserved.
连接到目标数据库: ORCL (DBID=1351749857)
已连接到辅助数据库: ORCL (未装载)
RMAN> duplicate target database for standby dorecover nofilenamecheck;
介质恢复完成, 用时: 00:00:03
完成 recover 于 06-11月-13
完成 Duplicate Db 于 06-11月-13
SQL> select status from v$instance;
STATUS
------------
MOUNTED
SQL> alter database recover managed standby database disconnect from session;
数据库已更改。
主库操作
SQL> archive log list
数据库日志模式 存档模式
自动存档 启用
存档终点 C:/oracle/product/10.2.0/oradata/arch
最早的联机日志序列 22
下一个存档日志序列 24
当前日志序列 24
插入一条数据测试
SQL> insert into wang2 values(1);
已创建 1 行。
SQL> commit;
提交完成。
SQL> alter system switch logfile;
系统已更改。
SQL> archive log list;
数据库日志模式 存档模式
自动存档 启用
存档终点 C:/oracle/product/10.2.0/oradata/arch
最早的联机日志序列 23
下一个存档日志序列 25
当前日志序列 25
备库操作
SQL> archive log list;
数据库日志模式 存档模式
自动存档 启用
存档终点 C:/oracle/product/10.2.0/oradata/arch
最早的联机日志序列 24
下一个存档日志序列 0
当前日志序列 25
SQL> shutdown immediate;
ORA-01109: 数据库未打开
已经卸载数据库。
ORACLE 例程已经关闭。
SQL> startup
ORACLE 例程已经启动。
Total System Global Area 167772160 bytes
Fixed Size 1247900 bytes
Variable Size 88081764 bytes
Database Buffers 75497472 bytes
Redo Buffers 2945024 bytes
数据库装载完毕。
数据库已经打开。
验证 数据已插入过来
SQL> select * from wang2;
ID
----------
1
主备切换
主库操作
SQL> select switchover_status from v$database;
SWITCHOVER_STATUS
----------------------------------------
SESSIONS ACTIVE
SQL> alter database commit to switchover to physical standby with session shutdo
wn;
数据库已更改。
SQL> select status from v$instance;
STATUS
------------------------
STARTED
SQL> shutdown immediate;
ORA-01507: ??????
ORACLE 例程已经关闭。
SQL> startup mount;
ORACLE 例程已经启动。
Total System Global Area 167772160 bytes
Fixed Size 1247900 bytes
Variable Size 83887460 bytes
Database Buffers 79691776 bytes
Redo Buffers 2945024 bytes
数据库装载完毕。
SQL> alter database recover managed standby database disconnect from session;
数据库已更改。
SQL> select database_role from v$database;
DATABASE_ROLE
----------------
PHYSICAL STANDBY
SQL> select switchover_status from v$database;
SWITCHOVER_STATUS
-------------------
TO PRIMARY
SQL>alter database commit to switchover to primary;
数据库已更改。
主库切换备库操作
SQL> select database_role from v$database;DATABASE_ROLE
----------------
PHYSICAL STANDBY
SQL> select switchover_status from v$database;
SWITCHOVER_STATUS
--------------------
TO PRIMARY
SQL> alter database commit to switchover to primary;
数据库已更改。
SQL> shutdown immediate;
ORA-01109: 数据库未打开
已经卸载数据库。
ORACLE 例程已经关闭。
SQL> startup
ORACLE 例程已经启动。
Total System Global Area 167772160 bytes
Fixed Size 1247900 bytes
Variable Size 88081764 bytes
Database Buffers 75497472 bytes
Redo Buffers 2945024 bytes
数据库装载完毕。
ORA-01092: ORACLE 实例终止。强制断开连接
SQL>
SQL> exit
从 Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options 断开
C:\Documents and Settings\Administrator>sqlplus "/as sysdba"
SQL*Plus: Release 10.2.0.1.0 - Production on 星期三 11月 6 16:42:21 2013
Copyright (c) 1982, 2005, Oracle. All rights reserved.
已连接到空闲例程。
SQL> startup
ORACLE 例程已经启动。
Total System Global Area 167772160 bytes
Fixed Size 1247900 bytes
Variable Size 88081764 bytes
Database Buffers 75497472 bytes
Redo Buffers 2945024 bytes
数据库装载完毕。
ORA-01092: ORACLE 实例终止。强制断开连接
SQL> exit
从 Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options 断开
--通过查看ALER错误 是SPFILE中UNDO表空间设置错误导致的
Wed Nov 06 16:42:32 2013
Errors in file c:\oracle\product\10.2.0\admin\orcl\bdump\orcl_mman_476.trc:
ORA-30012: undo tablespace '' does not exist or of wrong type
Wed Nov 06 16:42:32 2013
Errors in file c:\oracle\product\10.2.0\admin\orcl\bdump\orcl_psp0_1632.trc:
ORA-30012: undo tablespace '' does not exist or of wrong type
Wed Nov 06 16:42:32 2013
Instance terminated by USER, pid = 3408
ORA-1092 signalled during: ALTER DATABASE OPEN...
C:\Documents and Settings\Administrator>sqlplus "/as sysdba"
SQL*Plus: Release 10.2.0.1.0 - Production on 星期三 11月 6 16:44:06 2013
Copyright (c) 1982, 2005, Oracle. All rights reserved.
已连接到空闲例程。
SQL> startup mount;
ORACLE 例程已经启动。
Total System Global Area 167772160 bytes
Fixed Size 1247900 bytes
Variable Size 88081764 bytes
Database Buffers 75497472 bytes
Redo Buffers 2945024 bytes
数据库装载完毕。
SQL> show parameter undo;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS01
SQL> exit
从 Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options 断开
C:\Documents and Settings\Administrator>sqlplus "/as sysdba"
SQL*Plus: Release 10.2.0.1.0 - Production on 星期三 11月 6 16:44:52 2013
Copyright (c) 1982, 2005, Oracle. All rights reserved.
连接到:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> create pfile='c:\pfile.ora' from spfile;
文件已创建。
SQL> show parameter spfile;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string C:\ORACLE\PRODUCT\10.2.0\DB_1\
DATABASE\SPFILEORCL.ORA
SQL> alter system set undo_tablespace='undotbs1' scope=both;
alter system set undo_tablespace='undotbs1' scope=both
*
第 1 行出现错误:
ORA-02097: 无法修改参数, 因为指定的值无效
ORA-30012: 还原表空间 'undotbs1' 不存在或类型不正确
SQL> alter system set undo_tablespace='undotbs1' scope=spfile;
系统已更改。
SQL> shutdown immediate;
ORA-01109: 数据库未打开
已经卸载数据库。
ORACLE 例程已经关闭。
SQL> startup
ORACLE 例程已经启动。
Total System Global Area 167772160 bytes
Fixed Size 1247900 bytes
Variable Size 88081764 bytes
Database Buffers 75497472 bytes
Redo Buffers 2945024 bytes
数据库装载完毕。
数据库已经打开。
SQL> show parameter undo;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string undotbs1
完毕
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/28894640/viewspace-776010/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/28894640/viewspace-776010/