跨平台迁移数据库

botang · 发表于 2020-12-3 20:50:56

windb:1976086287

SQL> exit
PS C:\windows\system32> lsnrctl status
LSNRCTL for 64-bit Windows: Version 12.1.0.2.0 - Production on 03-12月-2020 20:09:34
Copyright (c) 1991, 2014, Oracle. All rights reserved.
正在连接到 (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
LISTENER 的 STATUS
------------------------
别名 LISTENER
版本 TNSLSNR for 64-bit Windows: Version 12.1.0.2.0 - Production
启动日期 03-12月-2020 20:05:33
正常运行时间 0 天 0 小时 4 分 1 秒
跟踪级别 off
安全性 ON: Local OS Authentication
SNMP OFF
监听程序日志文件 C:\app\Apple\diag\tnslsnr\DESKTOP-69JBF6I\listener\alert\log.xml
监听端点概要...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=DESKTOP-69JBF6I)(PORT=1521)))
监听程序不支持服务
命令执行成功
PS C:\windows\system32> lsnrctl start
LSNRCTL for 64-bit Windows: Version 12.1.0.2.0 - Production on 03-12月-2020 20:16:08
Copyright (c) 1991, 2014, Oracle. All rights reserved.
启动tnslsnr: 请稍候...
TNSLSNR for 64-bit Windows: Version 12.1.0.2.0 - Production
系统参数文件为C:\app\Apple\product\12.1.0\dbhome_1\network\admin\listener.ora
写入C:\app\Apple\product\12.1.0\dbhome_1\log\diag\tnslsnr\DESKTOP-69JBF6I\listener\alert\log.xml的日志信息
监听: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=DESKTOP-69JBF6I)(PORT=1521)))
正在连接到 (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=DESKTOP-69JBF6I)(PORT=1521)))
LISTENER 的 STATUS
------------------------
别名 LISTENER
版本 TNSLSNR for 64-bit Windows: Version 12.1.0.2.0 - Production
启动日期 03-12月-2020 20:16:13
正常运行时间 0 天 0 小时 0 分 8 秒
跟踪级别 off
安全性 ON: Local OS Authentication
SNMP OFF
监听程序参数文件 C:\app\Apple\product\12.1.0\dbhome_1\network\admin\listener.ora
监听程序日志文件 C:\app\Apple\product\12.1.0\dbhome_1\log\diag\tnslsnr\DESKTOP-69JBF6I\listener\alert\log.xml
监听端点概要...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=DESKTOP-69JBF6I)(PORT=1521)))
服务摘要..
服务 "windb" 包含 1 个实例。
实例 "windb", 状态 UNKNOWN, 包含此服务的 1 个处理程序...
命令执行成功
PS C:\windows\system32> lsnrctl services
LSNRCTL for 64-bit Windows: Version 12.1.0.2.0 - Production on 03-12月-2020 20:16:36
Copyright (c) 1991, 2014, Oracle. All rights reserved.
正在连接到 (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=DESKTOP-69JBF6I)(PORT=1521)))
服务摘要..
服务 "windb" 包含 1 个实例。
实例 "windb", 状态 UNKNOWN, 包含此服务的 1 个处理程序...
处理程序:
"DEDICATED" 已建立:0 已被拒绝:0
LOCAL SERVER
命令执行成功
PS C:\windows\system32> sqlplus /nolog
SQL*Plus: Release 12.1.0.2.0 Production on 星期四 12月 3 20:16:52 2020
Copyright (c) 1982, 2014, Oracle. All rights reserved.
SQL> conn sys/oracle_4U@windb as sysdba
已连接到空闲例程。
SQL> startup mount
ORACLE 例程已经启动。
Total System Global Area 1728053248 bytes
Fixed Size 3046512 bytes
Variable Size 1023411088 bytes
Database Buffers 687865856 bytes
Redo Buffers 13729792 bytes
数据库装载完毕。
SQL> alter database open read only;
数据库已更改。
SQL>
SQL>
SQL> set linesize 1000
SQL> select * from v$transportable_platform ;
PLATFORM_ID PLATFORM_NAME ENDIAN_FORMAT CON_ID
----------- ----------------------------------------------------------------------------------------------------- -------------- ----------
1 Solaris[tm] OE (32-bit) Big
0
2 Solaris[tm] OE (64-bit) Big
0
7 Microsoft Windows IA (32-bit) Little
0
10 Linux IA (32-bit) Little
0
6 AIX-Based Systems (64-bit) Big
0
3 HP-UX (64-bit) Big
0
5 HP Tru64 UNIX Little
0
4 HP-UX IA (64-bit) Big
0
11 Linux IA (64-bit) Little
0
15 HP Open VMS Little
0
8 Microsoft Windows IA (64-bit) Little
0
PLATFORM_ID PLATFORM_NAME ENDIAN_FORMAT CON_ID
----------- ----------------------------------------------------------------------------------------------------- -------------- ----------
9 IBM zSeries Based Linux Big
0
13 Linux x86 64-bit Little
0
16 Apple Mac OS Big
0
12 Microsoft Windows x86 64-bit Little
0
17 Solaris Operating System (x86) Little
0
18 IBM Power Based Linux Big
0
19 HP IA Open VMS Little
0
20 Solaris Operating System (x86-64) Little
0
21 Apple Mac OS (x86-64) Little
0
已选择 20 行。
SQL> exit
从 Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options 断开
PS C:\windows\system32> rman target sys/oracle_4U@windb
恢复管理器: Release 12.1.0.2.0 - Production on 星期四 12月 3 20:29:01 2020
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
已连接到目标数据库: WINDB (DBID=1976086287)
RMAN> convert database to platform 'Linux IA (64-bit)' format 'c:\data\%U';
启动 conversion at source 于 03-12月-20
使用目标数据库控制文件替代恢复目录
分配的通道: ORA_DISK_1
通道 ORA_DISK_1: SID=242 设备类型=DISK
在数据库中找到外部表 SYS.OPATCH_XML_INV
在数据库中找到目录 SYS.ORACLE_HOME
在数据库中找到目录 SYS.ORACLE_BASE
在数据库中找到目录 SYS.OPATCH_LOG_DIR
在数据库中找到目录 SYS.OPATCH_SCRIPT_DIR
在数据库中找到目录 SYS.OPATCH_INST_DIR
在数据库中找到目录 SYS.DATA_PUMP_DIR
在数据库中找到目录 SYS.XSDDIR
在数据库中找到目录 SYS.XMLDIR
在数据库中找到目录 SYS.ORACLECLRDIR
在数据库中找到目录 SYS.ORACLE_OCM_CONFIG_DIR
在数据库中找到目录 SYS.ORACLE_OCM_CONFIG_DIR2
在口令文件中找到用户 SYS (具有 SYSDBA and SYSOPER 权限)
在口令文件中找到用户 SYSDG (具有 SYSDG 权限)
在口令文件中找到用户 SYSBACKUP (具有 SYSBACKUP 权限)
在口令文件中找到用户 SYSKM (具有 SYSKM 权限)
通道 ORA_DISK_1: 启动数据文件转换
输入数据文件: 文件号=00001 名称=C:\APP\APPLE\ORADATA\WINDB\DATAFILE\O1_MF_SYSTEM_HVNQY99M_.DBF
已转换的数据文件 = C:\DATA\DATA_D-WINDB_I-1976086287_TS-SYSTEM_FNO-1_01VH655M
通道 ORA_DISK_1: 数据文件转换完毕, 经过时间: 00:00:16
通道 ORA_DISK_1: 启动数据文件转换
输入数据文件: 文件号=00003 名称=C:\APP\APPLE\ORADATA\WINDB\DATAFILE\O1_MF_SYSAUX_HVNQWVR9_.DBF
已转换的数据文件 = C:\DATA\DATA_D-WINDB_I-1976086287_TS-SYSAUX_FNO-3_02VH6566
通道 ORA_DISK_1: 数据文件转换完毕, 经过时间: 00:08:33
通道 ORA_DISK_1: 启动数据文件转换
输入数据文件: 文件号=00005 名称=C:\APP\APPLE\ORADATA\WINDB\DATAFILE\O1_MF_UNDOTBS1_HVNR01X2_.DBF
已转换的数据文件 = C:\DATA\DATA_D-WINDB_I-1976086287_TS-UNDOTBS1_FNO-5_03VH65M7
通道 ORA_DISK_1: 数据文件转换完毕, 经过时间: 00:00:22
通道 ORA_DISK_1: 启动数据文件转换
输入数据文件: 文件号=00006 名称=C:\APP\APPLE\ORADATA\WINDB\DATAFILE\O1_MF_USERS_HVNR00T4_.DBF
已转换的数据文件 = C:\DATA\DATA_D-WINDB_I-1976086287_TS-USERS_FNO-6_04VH65MT
通道 ORA_DISK_1: 数据文件转换完毕, 经过时间: 00:00:01
编辑 init.ora 文件 C:\DATA\INIT_00VH655L_1_0.ORA。此 PFILE 将用于在目标平台上创建数据库据
要重新编译所有 PL/SQL 模块, 请在目标平台上运行 utlirp.sql 和 utlrp.sql
要更改内部数据库标识符, 请使用 DBNEWID 实用程序
完成 conversion at source 于 03-12月-20
RMAN>

复制代码

Linux：

oracle@classroom admin]$ mkdir -p orcl2/adump
[oracle@classroom admin]$
[oracle@classroom admin]$ pwd
/u01/app/oracle/admin

复制代码

[root@classroom 下载]# ls
DATA_D-WINDB_I-1976086287_TS-SYSAUX_FNO-3_02VH6566
DATA_D-WINDB_I-1976086287_TS-SYSTEM_FNO-1_01VH655M
DATA_D-WINDB_I-1976086287_TS-UNDOTBS1_FNO-5_03VH65M7
DATA_D-WINDB_I-1976086287_TS-USERS_FNO-6_04VH65MT
INIT_00VH655L_1_0.ORA
windb_ora_7080.trc
[root@classroom 下载]# cp DATA_D-WINDB_I-1976086287_TS-* /u01/app/oracle/oradata/orcl2/
[root@classroom 下载]#

复制代码

[root@classroom orcl2]# ls
DATA_D-WINDB_I-1976086287_TS-SYSAUX_FNO-3_02VH6566
DATA_D-WINDB_I-1976086287_TS-SYSTEM_FNO-1_01VH655M
DATA_D-WINDB_I-1976086287_TS-UNDOTBS1_FNO-5_03VH65M7
DATA_D-WINDB_I-1976086287_TS-USERS_FNO-6_04VH65MT
[root@classroom orcl2]# chown oracle:oinstall *
[root@classroom orcl2]#

复制代码

[oracle@classroom orcl2]$ mv DATA_D-WINDB_I-1976086287_TS-SYSAUX_FNO-3_02VH6566 sysaux01.dbf
[oracle@classroom orcl2]$ ls
DATA_D-WINDB_I-1976086287_TS-SYSTEM_FNO-1_01VH655M
DATA_D-WINDB_I-1976086287_TS-UNDOTBS1_FNO-5_03VH65M7
DATA_D-WINDB_I-1976086287_TS-USERS_FNO-6_04VH65MT
sysaux01.dbf
[oracle@classroom orcl2]$ mv DATA_D-WINDB_I-1976086287_TS-SYSTEM_FNO-1_01VH655M system01.dbf
[oracle@classroom orcl2]$ ls
DATA_D-WINDB_I-1976086287_TS-UNDOTBS1_FNO-5_03VH65M7 sysaux01.dbf
DATA_D-WINDB_I-1976086287_TS-USERS_FNO-6_04VH65MT system01.dbf
[oracle@classroom orcl2]$ mv DATA_D-WINDB_I-1976086287_TS-UNDOTBS1_FNO-5_03VH65M7 undotbs1.dbf
[oracle@classroom orcl2]$ ls
DATA_D-WINDB_I-1976086287_TS-USERS_FNO-6_04VH65MT system01.dbf
sysaux01.dbf undotbs1.dbf
[oracle@classroom orcl2]$ mv DATA_D-WINDB_I-1976086287_TS-USERS_FNO-6_04VH65MT users01.dbf
[oracle@classroom orcl2]$ ls
sysaux01.dbf system01.dbf undotbs1.dbf users01.dbf
[oracle@classroom orcl2]$

复制代码

root@classroom orcl2]# cp /root/下载/INIT_00VH655L_1_0.ORA /u01/app/oracle/product/12.1.0/dbhome_1/dbs/initorcl2.ora

复制代码

---------------------------------

db_recovery_file_dest = '/u01/app/oracle/fast_recovery_area'
db_recovery_file_dest_size= 6731857920
audit_file_dest = '/u01/app/oracle/admin/orcl2/adump'
db_name = 'windb'
memory_target = 2G
undo_tablespace = 'UNDOTBS1'
control_files ='/u01/app/oracle/oradata/orcl2/control01.ctl','/u01/app/oracle/oradata/orcl2/control02.ctl','/u01/app/oracle/oradata/orcl2/control03.ctl'
processes = 300
db_block_size = 8192
compatible = '12.1.0.2.0'
open_cursors = 300

复制代码

有“set”：

CREATE CONTROLFILE REUSE SET DATABASE "windb" RESETLOGS NOARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/u01/app/oracle/oradata/orcl2/redo01.log' SIZE 500M BLOCKSIZE 512,
GROUP 2 '/u01/app/oracle/oradata/orcl2/redo02.log' SIZE 500M BLOCKSIZE 512,
GROUP 3 '/u01/app/oracle/oradata/orcl2/redo03.log' SIZE 500M BLOCKSIZE 512
DATAFILE
'/u01/app/oracle/oradata/orcl2/system01.dbf',
'/u01/app/oracle/oradata/orcl2/sysaux01.dbf',
'/u01/app/oracle/oradata/orcl2/undotbs1.dbf',
'/u01/app/oracle/oradata/orcl2/users01.dbf'
CHARACTER SET ZHS16GBK
;

复制代码

------------------------------------------------------------------------
扩展1：改名字（也可以用nid命令）
有“set”：
改参数文件里的db_name

CREATE CONTROLFILE REUSE SET DATABASE "mydb" RESETLOGS NOARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/u01/app/oracle/oradata/orcl2/redo01.log' SIZE 500M BLOCKSIZE 512,
GROUP 2 '/u01/app/oracle/oradata/orcl2/redo02.log' SIZE 500M BLOCKSIZE 512,
GROUP 3 '/u01/app/oracle/oradata/orcl2/redo03.log' SIZE 500M BLOCKSIZE 512
DATAFILE
'/u01/app/oracle/oradata/orcl2/system01.dbf',
'/u01/app/oracle/oradata/orcl2/sysaux01.dbf',
'/u01/app/oracle/oradata/orcl2/undotbs1.dbf',
'/u01/app/oracle/oradata/orcl2/users01.dbf'
CHARACTER SET ZHS16GBK
;

复制代码

扩展2：模板建库：
有“set”：

Create controlfile reuse set database "em12rep"
MAXINSTANCES 8
MAXLOGHISTORY 1
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
Datafile
'&&file0',
'&&file1',
'&&file2',
'&&file3'
LOGFILE GROUP 1 ('/u01/app/oracle/oradata/em12rep/redo01.log') SIZE 50M,
GROUP 2 ('/u01/app/oracle/oradata/em12rep/redo02.log') SIZE 50M,
GROUP 3 ('/u01/app/oracle/oradata/em12rep/redo03.log') SIZE 50M RESETLOGS;

复制代码

扩展3：控制文件的trace脚本（有日志坏掉和日志没有坏两个部分）：
没有“set”：

CREATE CONTROLFILE REUSE DATABASE "ORCL" NORESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 (
'+DATA/ORCL/ONLINELOG/group_1.295.1060029747',
'+FRA/ORCL/ONLINELOG/group_1.408.1060029749'
) SIZE 200M BLOCKSIZE 512,
GROUP 2 (
'+DATA/ORCL/ONLINELOG/group_2.257.1060030163',
'+FRA/ORCL/ONLINELOG/group_2.427.1060030163'
) SIZE 200M BLOCKSIZE 512,
GROUP 3 (
'+DATA/ORCL/ONLINELOG/group_3.261.1060030381',
'+FRA/ORCL/ONLINELOG/group_3.424.1060030381'
) SIZE 200M BLOCKSIZE 512
DATAFILE
'+DATA/ORCL/DATAFILE/system.291.1060031335',
'+DATA/ORCL/DATAFILE/sysaux.275.1060031335',
'+DATA/ORCL/DATAFILE/undotbs1.292.1060031335',
'+DATA/ORCL/DATAFILE/users.259.1060031357'
CHARACTER SET AL32UTF8
;
-- Configure RMAN configuration record 1
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('DEFAULT DEVICE TYPE TO','''SBT_TAPE''');
-- Configure RMAN configuration record 2
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('DEVICE TYPE','''SBT_TAPE'' PARALLELISM 2 BACKUP TYPE TO BACKUPSET');
-- Configure RMAN configuration record 3
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('CHANNEL','1 DEVICE TYPE ''SBT_TAPE'' PARMS ''ENV=(OB_MEDIA_FAMILY=classroom)''');
-- Configure RMAN configuration record 4
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('CHANNEL','2 DEVICE TYPE ''SBT_TAPE'' PARMS ''ENV=(OB_MEDIA_FAMILY=classroom)''');
--

复制代码