ORACLE数据库恢复和格式转换方法
浏览：
审计数据处理主要使用AO和联网审计系统，均使用SQL SERVER数据库，因此审计人员采集到ORACLE数据库以后应转换为SQL SERVER数据库格式，以方便与其他数据一起做综合分析。大型数据库的格式转换费时费力，如果操作不当，会浪费大量时间，严重影响数据分析工作进度，应由计算机专业人员完成。现总结数据采集、恢复和几种格式转换方法，供同行参考。
一、数据采集 采集数据应同时填写数据信息表，至少应包含下列信息：信息系统名称和版本、数据库系统名称和版本、数据库系统字符集、数据库用户名、表空间名、表空间大小、表空间已使用大小、数据库涉及的角色列表、数据采集脚本等。
其中数据库系统字符集信息特别重要但往往被忽略，如果字符集设置不正确，恢复的数据库中汉字很可能显示为乱码，修复非常困难，可能只有重新安装系统再次恢复。以ORACLE10g为例，安装过程中可以选择字符集，但安装以后注册表中仍显示使用字符集ZHS16GBK，应在注册表中修改字符集。如要在ORACLE10g中使用字符集US7ASCII,应修改注册表项HKEY_Local_Machine/Software/Oracle/KEY_ORADB10g_home1/NLS_LANG=AMERICAN_AMERICA.US7ASCII，HKEY_Local_Machine/Software/Oracle/KEY_ORAClient10g_home1/NLS_LANG=AMERICAN_ AMERICA.US7ASCII。
二、数据库恢复 1.创建表空间
应按照数据信息表中填写的表空间信息，逐个创建表空间。建议表空间的大小设置为已使用大小的110％以上，保存INDEX的表空间可略小，恢复数据库可不恢复INDEX。表空间的最后一个文件应设置为自动增长，防止表空间用完导致数据恢复失败。创建表空间和设置表空间为自增长的语句如下：
Create tablespace 表空间名 datefile 'path/文件名.ora' size 1000M autoextend on；
Alter database datafile 'path/文件名.ora' autoextend on;
2.创建用户
应按照数据信息表中填写的用户信息，逐个创建用户。我们可以不理会这些用户在被审计单位数据库中的角色，一律设为DBA角色，默认表空间为最大的一个表空间。
3.创建角色
被审计单位往往不能提供角色表，或提供的角色表不全。缺少角色会导致导入时系统提示错误，但一般不影响数据导入，但会使导入日志文件变大而不便阅读。
我们可以在数据导入的过程中创建角色。例如系统看到系统提示由于缺少用户或角色JSXX而导致错误时，我们在ORACLE Enterprise Manager Console中创建角色JSXX，并设置为数据库管理员（DBA）。
4.恢复数据库
ORACLE恢复数据库的命令为imp，关于imp的用法，可在网络上查询。一般建议使用下列命令：
Imp system/orcl fromuser=(用户名1,用户名2....) touser=用户名1 indexes=n ignore=n file='path/filename.imp' log='path/导入日志文件1.txt'
该命令不导入index（索引)，导入日志记录到“导入日志文件1.TXT”中。
导入过程可能花费很长时间（台式机每天可按40G测算），大型数据库导入应密切关注进展情况，发现重要错误应立即分析原因，必要时应中断导入，避免导入的数据不全。如果已经花费了很长时间，则可以不中断（除非系统自己中断导入），等导入过程结束后分析日志文件，找出导入失败的表，先删除这些表（空表），再重新导入。导入命令为：
Imp system/orcl fromuser=(用户名1,用户名2....) touser=用户名1 indexes=n ignore=n file='path/filename.imp' log='path/导入日志文件2.txt' tables=(表名1,表名2...)
导入数据以后应立即根据导入日志文件生成表目录，记录表名称和导入行数，以便在后面的转换过程中核对，确保转换后的数据完整准确。
三、数据导入到SQL SERVER数据库中 ORACLE数据导入SQL SERVER可以使用以下4种方式，各有利弊，可配合使用。
1.直接导入
使用SQL SERVER的导入导出工具，选择数据源为ORACLE Provider for OLE DB，选择要导入的表，可导入数据。这个方法的优点是使用方便，不需其他的工具。缺点是不能正确识别ORACLE的部分数据类型（如VARCHAR2)，需手工设置，费时费力。
2.使用SSMA(Microsoft SQL Server Migration Assistant for Oracle)
这是微软公司提供的一个免费的数据迁移工具，可直接把ORACLE数据库迁移到SQL SERVER中。这个工具的缺点有三个，一是选择全部表，系统会自动选择每个表的索引（INDEX）和触发器（TRIGGER），其中TRIGGER可能因导入表的顺序问题而导致错误，仅导出空表。若逐个选择表，花费时间较长。二是无法设置ORACLE字符集，如果ORACLE未使用ZHS16GBK字符集，可能导致汉字乱码。三是部分表虽无特殊数据类型，但导出行数不正确，原因不详。
3.使用LINK SERVER
关于LINK SERVER的使用，可参阅署计算机中心下发的《LINK SERVER及其在AO中的应用》一书。在SQL SERVER中设置好链接服务器后，编写脚本，导入数据。
例如在ORACLE中已导入USER1的1张表T1，欲导入已建好的SQL SERVER数据库“地税”，可用用户USER1创建链接服务器ORCL，在SQL SERVER中使用以下脚本：
Select * into 地税.dbo.T1 from openquery(orcl,'select * from T1')
Go
由于表比较多，脚本很长，逐个录入很费时间，容易出错，可以利用前面使用导入日志生成的表目录，在WPS表格中生成脚本。如下图所示，将C列全选，粘贴到SQL SERVER中即可。
使用LINKSERVER的缺点有两个，一是极少数表导入出错，提示接口返回错误的信息，原因可能是使用了用户自定义数据类型。二是部分表中因使用了datetime2类型而导致不能正确导入。
4.使用文本文件过渡
极少数表使用前3种方法均不能正确转换，但在SQL SERVER中建立了空表。可以先把ORACLE表导出为文本文件，再使用SQL SERVER的导入导出工具导入数据库。导出文本文件应应选择恰当的字符串分隔字段，保证不与数据库中的串重复，例如“|@@|”。
该方法的缺点有两个，一是需要导出文本文件，费时间又费空间，二是部分备注字段中有换行符，导致表中的一行数据在文本文件中显示为多行，影响导入SQL SERVER的过程，需编写程序进行处理后才可正确导入。（成唯一 韩航飞）
一、数据采集 采集数据应同时填写数据信息表，至少应包含下列信息：信息系统名称和版本、数据库系统名称和版本、数据库系统字符集、数据库用户名、表空间名、表空间大小、表空间已使用大小、数据库涉及的角色列表、数据采集脚本等。
其中数据库系统字符集信息特别重要但往往被忽略，如果字符集设置不正确，恢复的数据库中汉字很可能显示为乱码，修复非常困难，可能只有重新安装系统再次恢复。以ORACLE10g为例，安装过程中可以选择字符集，但安装以后注册表中仍显示使用字符集ZHS16GBK，应在注册表中修改字符集。如要在ORACLE10g中使用字符集US7ASCII,应修改注册表项HKEY_Local_Machine/Software/Oracle/KEY_ORADB10g_home1/NLS_LANG=AMERICAN_AMERICA.US7ASCII，HKEY_Local_Machine/Software/Oracle/KEY_ORAClient10g_home1/NLS_LANG=AMERICAN_ AMERICA.US7ASCII。
二、数据库恢复 1.创建表空间
应按照数据信息表中填写的表空间信息，逐个创建表空间。建议表空间的大小设置为已使用大小的110％以上，保存INDEX的表空间可略小，恢复数据库可不恢复INDEX。表空间的最后一个文件应设置为自动增长，防止表空间用完导致数据恢复失败。创建表空间和设置表空间为自增长的语句如下：
Create tablespace 表空间名 datefile 'path/文件名.ora' size 1000M autoextend on；
Alter database datafile 'path/文件名.ora' autoextend on;
2.创建用户
应按照数据信息表中填写的用户信息，逐个创建用户。我们可以不理会这些用户在被审计单位数据库中的角色，一律设为DBA角色，默认表空间为最大的一个表空间。
3.创建角色
被审计单位往往不能提供角色表，或提供的角色表不全。缺少角色会导致导入时系统提示错误，但一般不影响数据导入，但会使导入日志文件变大而不便阅读。
我们可以在数据导入的过程中创建角色。例如系统看到系统提示由于缺少用户或角色JSXX而导致错误时，我们在ORACLE Enterprise Manager Console中创建角色JSXX，并设置为数据库管理员（DBA）。
4.恢复数据库
ORACLE恢复数据库的命令为imp，关于imp的用法，可在网络上查询。一般建议使用下列命令：
Imp system/orcl fromuser=(用户名1,用户名2....) touser=用户名1 indexes=n ignore=n file='path/filename.imp' log='path/导入日志文件1.txt'
该命令不导入index（索引)，导入日志记录到“导入日志文件1.TXT”中。
导入过程可能花费很长时间（台式机每天可按40G测算），大型数据库导入应密切关注进展情况，发现重要错误应立即分析原因，必要时应中断导入，避免导入的数据不全。如果已经花费了很长时间，则可以不中断（除非系统自己中断导入），等导入过程结束后分析日志文件，找出导入失败的表，先删除这些表（空表），再重新导入。导入命令为：
Imp system/orcl fromuser=(用户名1,用户名2....) touser=用户名1 indexes=n ignore=n file='path/filename.imp' log='path/导入日志文件2.txt' tables=(表名1,表名2...)
导入数据以后应立即根据导入日志文件生成表目录，记录表名称和导入行数，以便在后面的转换过程中核对，确保转换后的数据完整准确。
三、数据导入到SQL SERVER数据库中 ORACLE数据导入SQL SERVER可以使用以下4种方式，各有利弊，可配合使用。
1.直接导入
使用SQL SERVER的导入导出工具，选择数据源为ORACLE Provider for OLE DB，选择要导入的表，可导入数据。这个方法的优点是使用方便，不需其他的工具。缺点是不能正确识别ORACLE的部分数据类型（如VARCHAR2)，需手工设置，费时费力。
2.使用SSMA(Microsoft SQL Server Migration Assistant for Oracle)
这是微软公司提供的一个免费的数据迁移工具，可直接把ORACLE数据库迁移到SQL SERVER中。这个工具的缺点有三个，一是选择全部表，系统会自动选择每个表的索引（INDEX）和触发器（TRIGGER），其中TRIGGER可能因导入表的顺序问题而导致错误，仅导出空表。若逐个选择表，花费时间较长。二是无法设置ORACLE字符集，如果ORACLE未使用ZHS16GBK字符集，可能导致汉字乱码。三是部分表虽无特殊数据类型，但导出行数不正确，原因不详。
3.使用LINK SERVER
关于LINK SERVER的使用，可参阅署计算机中心下发的《LINK SERVER及其在AO中的应用》一书。在SQL SERVER中设置好链接服务器后，编写脚本，导入数据。
例如在ORACLE中已导入USER1的1张表T1，欲导入已建好的SQL SERVER数据库“地税”，可用用户USER1创建链接服务器ORCL，在SQL SERVER中使用以下脚本：
Select * into 地税.dbo.T1 from openquery(orcl,'select * from T1')
Go
由于表比较多，脚本很长，逐个录入很费时间，容易出错，可以利用前面使用导入日志生成的表目录，在WPS表格中生成脚本。如下图所示，将C列全选，粘贴到SQL SERVER中即可。
使用LINKSERVER的缺点有两个，一是极少数表导入出错，提示接口返回错误的信息，原因可能是使用了用户自定义数据类型。二是部分表中因使用了datetime2类型而导致不能正确导入。
4.使用文本文件过渡
极少数表使用前3种方法均不能正确转换，但在SQL SERVER中建立了空表。可以先把ORACLE表导出为文本文件，再使用SQL SERVER的导入导出工具导入数据库。导出文本文件应应选择恰当的字符串分隔字段，保证不与数据库中的串重复，例如“|@@|”。
该方法的缺点有两个，一是需要导出文本文件，费时间又费空间，二是部分备注字段中有换行符，导致表中的一行数据在文本文件中显示为多行，影响导入SQL SERVER的过程，需编写程序进行处理后才可正确导入。（成唯一 韩航飞）
-
2024-06-12
oracle批量删除指定前缀数据表
BEGIN FOR t IN (SELECT table_n…
-
2024-06-12
oracle查询当前用户所有表的数据行数
SELECT table_name, to_number(e…
-
2024-03-26
审计采集Oracle数据最简单之六步走流
第一步.安装Oracle可以前往本站数据审计工具板块下载。第
-
2024-03-15
Oracle 数据库常用操作语句大全
1. 创建数据库在Oracle中，通常不直接创建整个数据库，
-
2024-03-15
MySQL常用语法和语句大全
B/S开发框架 中MySQL和SQL Server从语法和语…
中文