RMAN 异机迁移-mysql教程-PHP中文網

首頁

資料庫

mysql教程

RMAN 异机迁移

WBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWB

Jun 07, 2016 pm 05:20 PM

oracle資料庫

目标库准备实例名准备：此实例名为原库的实例名。不要随意设，因控制文件里是有记录DB_NAME的，如果你随便设置实例名，是没办

1. 环境说明：
原机：192.168.102.156     Oracle 10.2.0.1
目标：192.168.102.157     ORACLE 10.2.0.1
2.对数据库做创建一张表。
SQL> create table rmantest(id number(9));
Table created.
SQL> insert into rmantest(id) values(88);
1 row created.
SQL> commit;
Commit complete.
SQL> alter system switch logfile;
System altered.
3. 目标库准备实例名准备：此实例名为原库的实例名。不要随意设，因控制文件里是有记录DB_NAME的，如果你随便设置实例名，是没办法完成迁移的。
[oracle@GOLDENGATE2 ~]$ export ORACLE_SID=gg1
[oracle@GOLDENGATE2 ~]$ echo $ORACLE_SID
gg1
4. 目标库准备参数文件。
（1）在原库创建一个PFILE文件。
SQL> create pfile='/home/oracle/initgg1.ora' from spfile;
File created.
（2）奖参数文件移至目标库。
[oracle@GOLDENGATE1 ~]$ scp initgg1.oraoracle@192.168.102.157:/home/oracle
oracle@192.168.102.157'spassword:
initgg1.ora                                                                                                100% 1051     1.0KB/s   00:00
5. 在目标库创建参数文件中所需要的目录。
[oracle@GOLDENGATE2 ~]$ mkdir -p admin/gg1/adump
[oracle@GOLDENGATE2 ~]$ mkdir -p admin/gg1/bdump
[oracle@GOLDENGATE2 ~]$ mkdir -p admin/gg1/cdump
[oracle@GOLDENGATE2 ~]$ mkdir -p admin/gg1/dpdump
[oracle@GOLDENGATE2 ~]$ mkdir -p admin/gg1/udump
[oracle@GOLDENGATE2 ~]$ mkdir flash_recovery_area
[oracle@GOLDENGATE2 ~]$ mkdir -p oradata/gg1
[oracle@GOLDENGATE2 ~]$ mkdir archivelog
6. 在目标库创建SPFILE文件，并将数据库启动到NOMOUNT状态。
[oracle@GOLDENGATE2 ~]$ export ORACLE_SID=gg1
[oracle@GOLDENGATE2 ~]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Fri Sep 21 01:21:51 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to an idle instance.
SQL> create spfile from pfile='/home/oracle/initgg1.ora';
File created.
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 167772160 bytes
Fixed Size                  1218292 bytes
Variable Size              83888396 bytes
Database Buffers           75497472 bytes
Redo Buffers                7168000 bytes

7. 对原数据库进行热备份。必备条件：数据库属归档模式。
[oracle@GOLDENGATE1 ~]$ rman target /
Recovery Manager: Release 10.2.0.1.0 - Production on Fri Sep 21 01:21:49 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: GG1 (DBID=1620494887)
RMAN> run{
allocate channel a1 type disk;
allocate channel a2 type disk;
backup format='/home/oracle/rman/full_%d_%T_%s' database;
backup format='/home/oracle/rman/arch_%d_%T_%s' archivelog all;
backup format='/home/oracle/rman/ctl_%U' current controlfile;
release channel a2;
release channel a1;
}2> 3> 4> 5> 6> 7> 8> 9>
released channel: ORA_DISK_1
allocated channel: a1
channel a1: sid=158 devtype=DISK
allocated channel: a2
channel a2: sid=141 devtype=DISK
Starting backup at 21-SEP-12
channel a1: starting full datafile backupset
channel a1: specifying datafile(s) in backupset
input datafile fno=00001 name=/home/oracle/oradata/gg1/system01.dbf
input datafile fno=00005 name=/home/oracle/oradata/gg1/example01.dbf
input datafile fno=00003 name=/home/oracle/oradata/gg1/sysaux01.dbf
channel a1: starting piece 1 at 21-SEP-12
channel a2: starting full datafile backupset
channel a2: specifying datafile(s) in backupset
input datafile fno=00006 name=/home/oracle/oradata/gg1/gg01.dbf
input datafile fno=00002 name=/home/oracle/oradata/gg1/undotbs01.dbf
input datafile fno=00004 name=/home/oracle/oradata/gg1/users01.dbf
channel a2: starting piece 1 at 21-SEP-12
channel a2: finished piece 1 at 21-SEP-12
piece handle=/home/oracle/rman/full_GG1_20120921_10 tag=TAG20120921T013222 comment=NONE
channel a2: backup set complete, elapsed time: 00:00:25
channel a2: starting full datafile backupset
channel a2: specifying datafile(s) in backupset
including current control file in backupset
channel a2: starting piece 1 at 21-SEP-12
channel a2: finished piece 1 at 21-SEP-12
piece handle=/home/oracle/rman/full_GG1_20120921_11 tag=TAG20120921T013222 comment=NONE
channel a2: backup set complete, elapsed time: 00:00:03
channel a2: starting full datafile backupset
channel a2: specifying datafile(s) in backupset
including current SPFILE in backupset
channel a2: starting piece 1 at 21-SEP-12
channel a2: finished piece 1 at 21-SEP-12
piece handle=/home/oracle/rman/full_GG1_20120921_12 tag=TAG20120921T013222 comment=NONE
channel a2: backup set complete, elapsed time: 00:00:02
channel a1: finished piece 1 at 21-SEP-12
piece handle=/home/oracle/rman/full_GG1_20120921_9 tag=TAG20120921T013222 comment=NONE
channel a1: backup set complete, elapsed time: 00:00:48
Finished backup at 21-SEP-12
Starting backup at 21-SEP-12
current log archived
channel a1: starting archive log backupset
channel a1: specifying archive log(s) in backup set
input archive log thread=1 sequence=4 recid=1 stamp=794527523
channel a1: starting piece 1 at 21-SEP-12
channel a2: starting archive log backupset
channel a2: specifying archive log(s) in backup set
input archive log thread=1 sequence=5 recid=2 stamp=794538401
input archive log thread=1 sequence=6 recid=3 stamp=794539337
input archive log thread=1 sequence=7 recid=4 stamp=794539385
channel a2: starting piece 1 at 21-SEP-12
channel a2: finished piece 1 at 21-SEP-12
piece handle=/home/oracle/rman/arch_GG1_20120921_13 tag=TAG20120921T013312 comment=NONE
channel a2: backup set complete, elapsed time: 00:00:02
channel a1: finished piece 1 at 21-SEP-12
piece handle=/home/oracle/rman/arch_GG1_20120921_14 tag=TAG20120921T013312 comment=NONE
channel a1: backup set complete, elapsed time: 00:00:03
channel a2: starting archive log backupset
channel a2: specifying archive log(s) in backup set
input archive log thread=1 sequence=8 recid=5 stamp=794539992
channel a2: starting piece 1 at 21-SEP-12
channel a2: finished piece 1 at 21-SEP-12
piece handle=/home/oracle/rman/arch_GG1_20120921_15 tag=TAG20120921T013312 comment=NONE
channel a2: backup set complete, elapsed time: 00:00:02
Finished backup at 21-SEP-12
Starting backup at 21-SEP-12
channel a1: starting full datafile backupset
channel a1: specifying datafile(s) in backupset
including current control file in backupset
channel a1: starting piece 1 at 21-SEP-12
channel a1: finished piece 1 at 21-SEP-12
piece handle=/home/oracle/rman/ctl_0gnlndut_1_1 tag=TAG20120921T013317 comment=NONE
channel a1: backup set complete, elapsed time: 00:00:02
Finished backup at 21-SEP-12
released channel: a2
released channel: a1
8. 将备份移至目标机。
[oracle@GOLDENGATE1 ~]$ cd rman/
[oracle@GOLDENGATE1 rman]$ ls
arch_GG1_20120921_13 arch_GG1_20120921_15 full_GG1_20120921_10 full_GG1_20120921_12
arch_GG1_20120921_14 ctl_0gnlndut_1_1      full_GG1_20120921_11 full_GG1_20120921_9
[oracle@GOLDENGATE1 rman]$ scp *oracle@192.168.102.157:/home/oracle/rman
oracle@192.168.102.157'spassword:
arch_GG1_20120921_13                                                                                       100%   13MB 12.8MB/s   00:01
arch_GG1_20120921_14                                                                                       100%   25MB 12.5MB/s   00:02
arch_GG1_20120921_15                                                                                       100% 281KB 281.0KB/s   00:00
ctl_0gnlndut_1_1                                                                                           100% 6944KB   6.8MB/s   00:00
full_GG1_20120921_10                                                                                       100%   33MB   8.2MB/s   00:04
full_GG1_20120921_11                                                                                       100% 6944KB   6.8MB/s   00:00
full_GG1_20120921_12                                                                                       100%   96KB 96.0KB/s   00:01
full_GG1_20120921_9                                                                                        100% 561MB 11.9MB/s   00:47
9. 在目标机上恢复控制文件。
[oracle@GOLDENGATE2 rman]$ rman target /
Recovery Manager: Release 10.2.0.1.0 - Production on Fri Sep 21 01:36:10 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: gg1 (not mounted)
RMAN> restore controlfile from '/home/oracle/rman/ctl_0gnlndut_1_1';
Starting restore at 21-SEP-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
output filename=/home/oracle/oradata/gg1/control01.ctl
output filename=/home/oracle/oradata/gg1/control02.ctl
output filename=/home/oracle/oradata/gg1/control03.ctl
Finished restore at 21-SEP-12
10. 启动数据库到MOUNT状态。
RMAN> alter database mount;
database mounted
released channel: ORA_DISK_1
11.恢复数据库。
RMAN> restore database;
Starting restore at 21-SEP-12
Starting implicit crosscheck backup at 21-SEP-12
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK
Crosschecked 7 objects
Finished implicit crosscheck backup at 21-SEP-12
Starting implicit crosscheck copy at 21-SEP-12
using channel ORA_DISK_1
Finished implicit crosscheck copy at 21-SEP-12
searching for all files in the recovery area
cataloging files...
no files cataloged
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00002 to /home/oracle/oradata/gg1/undotbs01.dbf
restoring datafile 00004 to /home/oracle/oradata/gg1/users01.dbf
restoring datafile 00006 to /home/oracle/oradata/gg1/gg01.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/rman/full_GG1_20120921_10
channel ORA_DISK_1: restored backup piece 1
piece handle=/home/oracle/rman/full_GG1_20120921_10 tag=TAG20120921T013222
channel ORA_DISK_1: restore complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /home/oracle/oradata/gg1/system01.dbf
restoring datafile 00003 to /home/oracle/oradata/gg1/sysaux01.dbf
restoring datafile 00005 to /home/oracle/oradata/gg1/example01.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/rman/full_GG1_20120921_9
channel ORA_DISK_1: restored backup piece 1
piece handle=/home/oracle/rman/full_GG1_20120921_9 tag=TAG20120921T013222
channel ORA_DISK_1: restore complete, elapsed time: 00:00:35
Finished restore at 21-SEP-12
RMAN> recover database;
Starting recover at 21-SEP-12
using channel ORA_DISK_1
starting media recovery
channel ORA_DISK_1: starting archive log restore to default destination
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=8
channel ORA_DISK_1: reading from backup piece /home/oracle/rman/arch_GG1_20120921_15
channel ORA_DISK_1: restored backup piece 1
piece handle=/home/oracle/rman/arch_GG1_20120921_15 tag=TAG20120921T013312
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
archive log filename=/home/oracle/archivelog/1_8_794474474.dbf thread=1 sequence=8
unable to find archive log
archive log thread=1 sequence=9
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 09/21/2012 01:38:18
RMAN-06054: media recovery requesting unknown log: thread 1 seq 9 lowscn 529513
查看原库日志：
[oracle@GOLDENGATE1 archivelog]$ ls
1_4_794474474.dbf 1_5_794474474.dbf 1_6_794474474.dbf 1_7_794474474.dbf 1_8_794474474.dbf
[oracle@GOLDENGATE1 archivelog]$
干，这里就没有那日志。
因为原库并未生成SEQ为9的日志，这里根本就没有9的备份。
用SQLPLUS连进行执行恢复。
SQL> recover database using backup controlfile;
ORA-00279: change 529513 generated at 09/21/2012 01:33:11 needed for thread 1
ORA-00289: suggestion : /home/oracle/archivelog/1_9_794474474.dbf
ORA-00280: change 529513 for thread 1 is in sequence #9

Specify log: {=suggested | filename | AUTO | CANCEL}
cancel
Media recovery cancelled.
12。打开数据库。
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: '/home/oracle/oradata/gg1/system01.dbf'
因为控制文件和数据文件不一致。
解决办法：
修改参数文件：增加 _allow_resetlogs_corruption='TURE'
关闭数据库，重建SPFILE文件，，并启动到NOMOUNT状态。
打开数据库：
SQL> alter database open resetlogs;
Database altered.
13。检查
REDO日志和临时表空间文件会在打开数据库时根据控制文件中的记录去创建。
查看原库所建的表：
SQL> select * from rmantest;
        ID
----------
        88
迁移完成。

linux

陳述

本文內容由網友自願投稿，版權歸原作者所有。本站不承擔相應的法律責任。如發現涉嫌抄襲或侵權的內容，請聯絡admin@php.cn

MySQL：世界上最受歡迎的數據庫的簡介Apr 12, 2025 am 12:18 AM

MySQL是一種開源的關係型數據庫管理系統，主要用於快速、可靠地存儲和檢索數據。其工作原理包括客戶端請求、查詢解析、執行查詢和返回結果。使用示例包括創建表、插入和查詢數據，以及高級功能如JOIN操作。常見錯誤涉及SQL語法、數據類型和權限問題，優化建議包括使用索引、優化查詢和分錶分區。

MySQL的重要性：數據存儲和管理Apr 12, 2025 am 12:18 AM

MySQL是一個開源的關係型數據庫管理系統，適用於數據存儲、管理、查詢和安全。 1.它支持多種操作系統，廣泛應用於Web應用等領域。 2.通過客戶端-服務器架構和不同存儲引擎，MySQL高效處理數據。 3.基本用法包括創建數據庫和表，插入、查詢和更新數據。 4.高級用法涉及復雜查詢和存儲過程。 5.常見錯誤可通過EXPLAIN語句調試。 6.性能優化包括合理使用索引和優化查詢語句。

為什麼要使用mysql？利益和優勢Apr 12, 2025 am 12:17 AM

選擇MySQL的原因是其性能、可靠性、易用性和社區支持。 1.MySQL提供高效的數據存儲和檢索功能，支持多種數據類型和高級查詢操作。 2.採用客戶端-服務器架構和多種存儲引擎，支持事務和查詢優化。 3.易於使用，支持多種操作系統和編程語言。 4.擁有強大的社區支持，提供豐富的資源和解決方案。

描述InnoDB鎖定機制（共享鎖，獨家鎖，意向鎖，記錄鎖，間隙鎖，下一鍵鎖）。Apr 12, 2025 am 12:16 AM

InnoDB的鎖機制包括共享鎖、排他鎖、意向鎖、記錄鎖、間隙鎖和下一個鍵鎖。 1.共享鎖允許事務讀取數據而不阻止其他事務讀取。 2.排他鎖阻止其他事務讀取和修改數據。 3.意向鎖優化鎖效率。 4.記錄鎖鎖定索引記錄。 5.間隙鎖鎖定索引記錄間隙。 6.下一個鍵鎖是記錄鎖和間隙鎖的組合，確保數據一致性。

MySQL查詢性能差的常見原因是什麼？Apr 12, 2025 am 12:11 AM

MySQL查询性能不佳的原因主要包括没有使用索引、查询优化器选择错误的执行计划、表设计不合理、数据量过大和锁竞争。1.没有索引导致查询缓慢，添加索引后可显著提升性能。2.使用EXPLAIN命令可以分析查询计划，找出优化器错误。3.重构表结构和优化JOIN条件可改善表设计问题。4.数据量大时，采用分区和分表策略。5.高并发环境下，优化事务和锁策略可减少锁竞争。

您什麼時候應該使用複合索引與多個單列索引？Apr 11, 2025 am 12:06 AM

在數據庫優化中，應根據查詢需求選擇索引策略：1.當查詢涉及多個列且條件順序固定時，使用複合索引；2.當查詢涉及多個列但條件順序不固定時，使用多個單列索引。複合索引適用於優化多列查詢，單列索引則適合單列查詢。

如何識別和優化MySQL中的慢速查詢？（慢查詢日誌，performance_schema）Apr 10, 2025 am 09:36 AM

要優化MySQL慢查詢，需使用slowquerylog和performance_schema：1.啟用slowquerylog並設置閾值，記錄慢查詢；2.利用performance_schema分析查詢執行細節，找出性能瓶頸並優化。

MySQL和SQL：開發人員的基本技能Apr 10, 2025 am 09:30 AM

MySQL和SQL是開發者必備技能。 1.MySQL是開源的關係型數據庫管理系統，SQL是用於管理和操作數據庫的標準語言。 2.MySQL通過高效的數據存儲和檢索功能支持多種存儲引擎，SQL通過簡單語句完成複雜數據操作。 3.使用示例包括基本查詢和高級查詢，如按條件過濾和排序。 4.常見錯誤包括語法錯誤和性能問題，可通過檢查SQL語句和使用EXPLAIN命令優化。 5.性能優化技巧包括使用索引、避免全表掃描、優化JOIN操作和提升代碼可讀性。

See all articles