I have an ASP .NET application that connects to an Oracle or a SQL Server database. An installer has been developed to install a fresh database to an existing SQL Server using sql commands such as "restore database..." which simply restores a ".bak" file which we keep under source control.
我有一个连接到Oracle或SQL Server数据库的ASP .NET应用程序。已经开发了一个安装程序,用于使用诸如“restore database ...”之类的sql命令将新数据库安装到现有SQL Server,该命令只是恢复我们保持在源代码管理下的“.bak”文件。
I'm very new to Oracle and our application has only recently been ported to be compatible with 10g.
我是Oracle的新手,我们的应用程序最近才移植到与10g兼容。
We are currently using the "exp.exe" tool to generate a ".dmp" file and then using the "imp.exe" to import it into a developers box.
我们目前正在使用“exp.exe”工具生成“.dmp”文件,然后使用“imp.exe”将其导入开发人员框。
How would you go about creating an "Oracle Database Installer"?
您将如何创建“Oracle数据库安装程序”?
Would you create the database using script files and then populate the database with required default data?
您是否会使用脚本文件创建数据库,然后使用所需的默认数据填充数据库?
Would you run the "imp.exe" tool behind the scenes?
你会在幕后运行“imp.exe”工具吗?
Do we need to provide a clean interface for system administrators so that they can just select the destination server and have done, or should we just provide them with the ".dmp" file? What are the best practices?
我们是否需要为系统管理员提供一个干净的界面,以便他们可以选择目标服务器并完成,或者我们应该只提供“.dmp”文件?什么是最佳做法?
Thanks.
4 个解决方案
#1
The question is -- what do your customers know about Oracle?
问题是 - 您的客户对Oracle有何了解?
-
Nothing? You should probably rethink this position. Oracle is very large and complex. If you assume your customers know nothing, you'll then start providing tutorials and help that's inappropriate.
没有?你应该重新考虑这个立场。 Oracle非常庞大而且复杂。如果您认为您的客户一无所知,那么您将开始提供教程并提供不合适的帮助。
-
Minimally Competent? If they're competent, they know enough to run imp by themselves. Also, they know enough to run a script that executes SQL.
最低能力?如果他们有能力,他们就足够了解自己。此外,他们知道足以运行执行SQL的脚本。
-
Actual DBA's? Most organizations that can afford Oracle can afford real DBA's. Real DBA's can cope with a lot of things -- they do not need much hand-holding. Some of them like to assign storage parameters according to their shop standards.
实际的DBA?大多数能够负担得起Oracle的组织都可以负担得起真正的DBA。真正的DBA可以应付很多事情 - 他们不需要太多的手持。他们中的一些人喜欢根据他们的商店标准分配存储参数。
You should provide a script with reasonable defaults. You should define your script in a way that someone can easily find all of your storage parameters and tweak them if necessary.
您应该提供具有合理默认值的脚本。您应该以某人可以轻松找到所有存储参数并在必要时进行调整的方式定义脚本。
Your initial data can be via export/import or via a script. I prefer a script.
您的初始数据可以通过导出/导入或通过脚本。我更喜欢一个剧本。
#2
I have done this repeatedly from both sides (consumer and provider) as a DBA, developer, and architect.
作为DBA,开发人员和架构师,我从双方(消费者和提供商)反复做过这件事。
As a provider, one of my grand accomplishments (in 1996) was the creation of an installation CD for a commercial insurance claims management software product targeted to the largest insurance carriers (a multi-million dollar item). That installation CD installed the Oracle 7.2 RDBMS engine, the FileNet optical storage system (scans paper documents and creates cataloged binary versions), and our custom claim-processing application (built in VB 4.0), all integrated and ready to run. As part of the installation process, the user could skip the Oracle software installation or customize it, and the user could customize/override the database configuration in all of its major details (database, schemas, tablespaces, sizes, disks, etc.).
作为提供商,我的一项重大成就(1996年)是为最大的保险公司(数百万美元的项目)创建商业保险理赔管理软件产品的安装CD。该安装CD安装了Oracle 7.2 RDBMS引擎,FileNet光存储系统(扫描纸质文档并创建编目的二进制版本),以及我们的自定义索赔处理应用程序(内置于VB 4.0),所有这些都集成并准备运行。作为安装过程的一部分,用户可以跳过Oracle软件安装或对其进行自定义,用户可以在其所有主要细节(数据库,模式,表空间,大小,磁盘等)中自定义/覆盖数据库配置。
I also provided the field service for this product, which included traveling to the client site as necessary. I tested the installation CD literally hundreds of times under every imaginable scenario that I could replicate, and we NEVER had a field failure that required even a phone call, let alone a trip (I did travel on four occasions, but for pre-sales stuff instead).
我还为此产品提供了现场服务,其中包括必要时前往客户站点。在我可以复制的每个可以想象的情况下,我测试了安装CD几百次,我们从来没有一个现场故障,甚至需要打电话,更不用说旅行了(我曾经四次旅行,但对于售前的东西代替)。
More recently (2007), I scripted the creation of an Oracle 10g database for an internal system at a megacorp. In production, the database was sized at 8 TB, mostly for a single transaction table with high data volume. In test, the database was sized around 1 TB for a modest server. In development, the database was sized around 100 MB to run on my laptop. The EXACT SAME SCRIPTS created all three environments, and I could extend them to handle a new environment/machine in about five minutes. This database involved extreme performance tuning, so customization of all pertinent characteristics was absolutely crucial.
最近(2007年),我编写了为megacorp内部系统创建Oracle 10g数据库的脚本。在生产中,数据库的大小为8 TB,主要用于具有高数据量的单个事务表。在测试中,对于适度的服务器,数据库的大小约为1 TB。在开发中,数据库的大小约为100 MB,可以在我的笔记本电脑上运行。完全相同的脚本创建了所有三种环境,我可以在大约五分钟内扩展它们以处理新的环境/机器。该数据库涉及极端性能调整,因此定制所有相关特性绝对至关重要。
Back to the insurance claims processing product--let me please add that I was originally hired to lead its conversion from a SQL Server database to an Oracle database. That conversion was identified as a business necessity because most potential clients did not view a SQL-Server-based product as a professional, serious solution. That is not quite as common today, but it still applies in general: a software product has a better chance of market penetration if it can accommodate multiple database options as preferred by the target customers (especially enterprise-class customers).
回到保险索赔处理产品 - 请允许我补充说,我最初被雇用来领导它从SQL Server数据库转换到Oracle数据库。该转换被确定为业务必需品,因为大多数潜在客户并未将基于SQL Server的产品视为专业,严肃的解决方案。这在今天并不常见,但它仍然适用于一般情况:如果软件产品可以容纳目标客户(特别是企业级客户)首选的多个数据库选项,则它具有更好的市场渗透机会。
Likewise, the installation CD was also viewed as an essential element. However, that situation and many more have revealed to me that most "real" DBAs will not accept an import-based database installation. As a DBA and architect, I know that I definitely will not for the same reasons.
同样,安装CD也被视为一个基本要素。但是,这种情况以及更多情况向我透露,大多数“真正的”DBA都不接受基于导入的数据库安装。作为一名DBA和架构师,我知道我绝对不会出于同样的原因。
Simply put, an import-based database installation gives the customer almost no control over the resulting database. It is opaque to the customer, leaving them questioning what it did. It forces the customer to expend massive efforts to attempt to exercise what little control they can. It is notoriously fragile and error-prone (Oracle imports are well known for ownership and permission problems, constraint problems, etc.). Weighing all those impacts, an import-based database installation is unprofessional--it does not put the customers' needs first.
简而言之,基于导入的数据库安装使客户几乎无法控制生成的数据库。它对客户来说是不透明的,让他们质疑它的作用。它迫使客户花费大量的努力来尝试尽可能少地控制它们。众所周知,它很脆弱且容易出错(Oracle的进口因所有权和权限问题,约束问题等而众所周知)。权衡所有这些影响,基于导入的数据库安装是不专业的 - 它不会首先满足客户的需求。
Scripting the database installation provides the right kind of transparency, configurability, selective repeatability, and overall customer control that professionalism demands. It also encourages you to properly understand the impacts of your database design decisions in a way that an import does not.
脚本化数据库安装提供了正确的透明度,可配置性,选择性可重复性以及专业性所需的整体客户控制。它还鼓励您以导入的方式正确理解数据库设计决策的影响。
Best wishes.
#3
Personally I favour SQL scripts to database creation and data loads where possible. I tend to use PL/SQL Developer. It has some good options to generate scripts from an existing database. Once you have these you can run the scripts using sqlplus or any application code that can execute arbitrary SQL (eg JDBC with Java). Toad is the more common (and more expensive) tool for Oracle development.
我个人赞成SQL脚本尽可能地创建数据库和数据。我倾向于使用PL / SQL Developer。它有一些从现有数据库生成脚本的好选项。获得这些后,您可以使用sqlplus或任何可以执行任意SQL的应用程序代码(例如使用Java的JDBC)运行脚本。 Toad是Oracle开发中更常见(也更昂贵)的工具。
The only limitation of a SQL export is it can't export CLOB/BLOB fields. If you have those, you either need to do them separately (as a PL/SQL export) or do the whole thing as a PL/SQL export. Theres no dramas with this except the file is effectively a binary export (extension .pde) and is more limited in how you can execute it.
SQL导出的唯一限制是它无法导出CLOB / BLOB字段。如果你有这些,你需要单独执行它们(作为PL / SQL导出)或者将整个事情作为PL / SQL导出。除了文件实际上是二进制导出(扩展名.pde)之外,没有这方面的戏剧,并且在执行它的方式上更受限制。
The other big advantage of SQL source files is they can be version controlled easily. It's really handy to be able to create a database environment by running one or two scripts.
SQL源文件的另一大优势是它们可以轻松地进行版本控制。通过运行一个或两个脚本来创建数据库环境非常方便。
The import and export tools for Oracle I think are more applicable for backup and restore operations.
我认为Oracle的导入和导出工具更适用于备份和还原操作。
Now, as for delivering that to a customer, from your comments it seems that you'll be giving this to DBAs. Pretty much any Oracle installation will have DBAs involved. They will be fine with SQL scripts to create the schema and do the data load. They will be doing a lot of site-specific configuration (eg tuning the SGA, temp tablespaces, # of concurrent connections, etc based on expected load).
现在,至于将这个提供给客户,从您的评论中看来,您似乎将这个提供给DBA。几乎任何Oracle安装都会涉及DBA。使用SQL脚本可以很好地创建模式并进行数据加载。他们将进行大量特定于站点的配置(例如,根据预期负载调整SGA,临时表空间,并发连接数等)。
You, as the vendor, can give guidance on any relevant configuration and you may get involved in support and possibly installation but ultimately it's up to them to figure out what works for them. Oracle runs on a large number of operating systems and hardware variants with infinite variations in network topology and firewall configuraiton. You can't factor in all of these to an installer or even a set of instructions (other than the guidelines mentioned previously).
作为供应商,您可以为任何相关配置提供指导,您可能会参与支持和可能的安装,但最终由他们决定哪些适用于他们。 Oracle运行在大量操作系统和硬件变体上,在网络拓扑和防火墙配置方面存在无限变化。您不能将所有这些因素都考虑在安装程序或甚至一组指令中(除了前面提到的指南之外)。
#4
The last time I was involved in the creation of a (oracle) db (for a reasonably large company with in-house DBAs) the DBAs wanted to know things like:
我最后一次参与创建(oracle)数据库(对于拥有内部DBA的合理大公司),DBA想知道如下事情:
- what we wanted to call the db,
- what tablespaces we would need, and an estimate of how much data would be in each one
- how many users would be connecting.
我们想要称之为db,
我们需要什么样的表空间,以及估计每个表空间的数量
有多少用户会连接。
(From memory) they set up the db and tablespaces, then we provided a combination of simple scripts that they could run (or clear instructions if a task wasn't easy to automate)
As I say this was for an in-house app, so your mileage may vary, but in my case they wanted all instructions clearly spelt out so that (a) there was no possibily of a misunderstanding leading to the wrong thing being done, and (b) no culpability on their part if something didn't work ("we were just following the instructions")
(从内存中)他们设置了db和tablespaces,然后我们提供了他们可以运行的简单脚本的组合(或者如果任务不容易自动化则清除指令)正如我所说这是针对内部应用程序,因此,你的里程可能会有所不同,但在我的情况下,他们希望明确说明所有指示,以便(a)不可能产生误解导致错误的事情发生,并且(b)如果有什么事情没有他们的责任,那么他们就没有责任。工作(“我们只是按照说明”)
#1
The question is -- what do your customers know about Oracle?
问题是 - 您的客户对Oracle有何了解?
-
Nothing? You should probably rethink this position. Oracle is very large and complex. If you assume your customers know nothing, you'll then start providing tutorials and help that's inappropriate.
没有?你应该重新考虑这个立场。 Oracle非常庞大而且复杂。如果您认为您的客户一无所知,那么您将开始提供教程并提供不合适的帮助。
-
Minimally Competent? If they're competent, they know enough to run imp by themselves. Also, they know enough to run a script that executes SQL.
最低能力?如果他们有能力,他们就足够了解自己。此外,他们知道足以运行执行SQL的脚本。
-
Actual DBA's? Most organizations that can afford Oracle can afford real DBA's. Real DBA's can cope with a lot of things -- they do not need much hand-holding. Some of them like to assign storage parameters according to their shop standards.
实际的DBA?大多数能够负担得起Oracle的组织都可以负担得起真正的DBA。真正的DBA可以应付很多事情 - 他们不需要太多的手持。他们中的一些人喜欢根据他们的商店标准分配存储参数。
You should provide a script with reasonable defaults. You should define your script in a way that someone can easily find all of your storage parameters and tweak them if necessary.
您应该提供具有合理默认值的脚本。您应该以某人可以轻松找到所有存储参数并在必要时进行调整的方式定义脚本。
Your initial data can be via export/import or via a script. I prefer a script.
您的初始数据可以通过导出/导入或通过脚本。我更喜欢一个剧本。
#2
I have done this repeatedly from both sides (consumer and provider) as a DBA, developer, and architect.
作为DBA,开发人员和架构师,我从双方(消费者和提供商)反复做过这件事。
As a provider, one of my grand accomplishments (in 1996) was the creation of an installation CD for a commercial insurance claims management software product targeted to the largest insurance carriers (a multi-million dollar item). That installation CD installed the Oracle 7.2 RDBMS engine, the FileNet optical storage system (scans paper documents and creates cataloged binary versions), and our custom claim-processing application (built in VB 4.0), all integrated and ready to run. As part of the installation process, the user could skip the Oracle software installation or customize it, and the user could customize/override the database configuration in all of its major details (database, schemas, tablespaces, sizes, disks, etc.).
作为提供商,我的一项重大成就(1996年)是为最大的保险公司(数百万美元的项目)创建商业保险理赔管理软件产品的安装CD。该安装CD安装了Oracle 7.2 RDBMS引擎,FileNet光存储系统(扫描纸质文档并创建编目的二进制版本),以及我们的自定义索赔处理应用程序(内置于VB 4.0),所有这些都集成并准备运行。作为安装过程的一部分,用户可以跳过Oracle软件安装或对其进行自定义,用户可以在其所有主要细节(数据库,模式,表空间,大小,磁盘等)中自定义/覆盖数据库配置。
I also provided the field service for this product, which included traveling to the client site as necessary. I tested the installation CD literally hundreds of times under every imaginable scenario that I could replicate, and we NEVER had a field failure that required even a phone call, let alone a trip (I did travel on four occasions, but for pre-sales stuff instead).
我还为此产品提供了现场服务,其中包括必要时前往客户站点。在我可以复制的每个可以想象的情况下,我测试了安装CD几百次,我们从来没有一个现场故障,甚至需要打电话,更不用说旅行了(我曾经四次旅行,但对于售前的东西代替)。
More recently (2007), I scripted the creation of an Oracle 10g database for an internal system at a megacorp. In production, the database was sized at 8 TB, mostly for a single transaction table with high data volume. In test, the database was sized around 1 TB for a modest server. In development, the database was sized around 100 MB to run on my laptop. The EXACT SAME SCRIPTS created all three environments, and I could extend them to handle a new environment/machine in about five minutes. This database involved extreme performance tuning, so customization of all pertinent characteristics was absolutely crucial.
最近(2007年),我编写了为megacorp内部系统创建Oracle 10g数据库的脚本。在生产中,数据库的大小为8 TB,主要用于具有高数据量的单个事务表。在测试中,对于适度的服务器,数据库的大小约为1 TB。在开发中,数据库的大小约为100 MB,可以在我的笔记本电脑上运行。完全相同的脚本创建了所有三种环境,我可以在大约五分钟内扩展它们以处理新的环境/机器。该数据库涉及极端性能调整,因此定制所有相关特性绝对至关重要。
Back to the insurance claims processing product--let me please add that I was originally hired to lead its conversion from a SQL Server database to an Oracle database. That conversion was identified as a business necessity because most potential clients did not view a SQL-Server-based product as a professional, serious solution. That is not quite as common today, but it still applies in general: a software product has a better chance of market penetration if it can accommodate multiple database options as preferred by the target customers (especially enterprise-class customers).
回到保险索赔处理产品 - 请允许我补充说,我最初被雇用来领导它从SQL Server数据库转换到Oracle数据库。该转换被确定为业务必需品,因为大多数潜在客户并未将基于SQL Server的产品视为专业,严肃的解决方案。这在今天并不常见,但它仍然适用于一般情况:如果软件产品可以容纳目标客户(特别是企业级客户)首选的多个数据库选项,则它具有更好的市场渗透机会。
Likewise, the installation CD was also viewed as an essential element. However, that situation and many more have revealed to me that most "real" DBAs will not accept an import-based database installation. As a DBA and architect, I know that I definitely will not for the same reasons.
同样,安装CD也被视为一个基本要素。但是,这种情况以及更多情况向我透露,大多数“真正的”DBA都不接受基于导入的数据库安装。作为一名DBA和架构师,我知道我绝对不会出于同样的原因。
Simply put, an import-based database installation gives the customer almost no control over the resulting database. It is opaque to the customer, leaving them questioning what it did. It forces the customer to expend massive efforts to attempt to exercise what little control they can. It is notoriously fragile and error-prone (Oracle imports are well known for ownership and permission problems, constraint problems, etc.). Weighing all those impacts, an import-based database installation is unprofessional--it does not put the customers' needs first.
简而言之,基于导入的数据库安装使客户几乎无法控制生成的数据库。它对客户来说是不透明的,让他们质疑它的作用。它迫使客户花费大量的努力来尝试尽可能少地控制它们。众所周知,它很脆弱且容易出错(Oracle的进口因所有权和权限问题,约束问题等而众所周知)。权衡所有这些影响,基于导入的数据库安装是不专业的 - 它不会首先满足客户的需求。
Scripting the database installation provides the right kind of transparency, configurability, selective repeatability, and overall customer control that professionalism demands. It also encourages you to properly understand the impacts of your database design decisions in a way that an import does not.
脚本化数据库安装提供了正确的透明度,可配置性,选择性可重复性以及专业性所需的整体客户控制。它还鼓励您以导入的方式正确理解数据库设计决策的影响。
Best wishes.
#3
Personally I favour SQL scripts to database creation and data loads where possible. I tend to use PL/SQL Developer. It has some good options to generate scripts from an existing database. Once you have these you can run the scripts using sqlplus or any application code that can execute arbitrary SQL (eg JDBC with Java). Toad is the more common (and more expensive) tool for Oracle development.
我个人赞成SQL脚本尽可能地创建数据库和数据。我倾向于使用PL / SQL Developer。它有一些从现有数据库生成脚本的好选项。获得这些后,您可以使用sqlplus或任何可以执行任意SQL的应用程序代码(例如使用Java的JDBC)运行脚本。 Toad是Oracle开发中更常见(也更昂贵)的工具。
The only limitation of a SQL export is it can't export CLOB/BLOB fields. If you have those, you either need to do them separately (as a PL/SQL export) or do the whole thing as a PL/SQL export. Theres no dramas with this except the file is effectively a binary export (extension .pde) and is more limited in how you can execute it.
SQL导出的唯一限制是它无法导出CLOB / BLOB字段。如果你有这些,你需要单独执行它们(作为PL / SQL导出)或者将整个事情作为PL / SQL导出。除了文件实际上是二进制导出(扩展名.pde)之外,没有这方面的戏剧,并且在执行它的方式上更受限制。
The other big advantage of SQL source files is they can be version controlled easily. It's really handy to be able to create a database environment by running one or two scripts.
SQL源文件的另一大优势是它们可以轻松地进行版本控制。通过运行一个或两个脚本来创建数据库环境非常方便。
The import and export tools for Oracle I think are more applicable for backup and restore operations.
我认为Oracle的导入和导出工具更适用于备份和还原操作。
Now, as for delivering that to a customer, from your comments it seems that you'll be giving this to DBAs. Pretty much any Oracle installation will have DBAs involved. They will be fine with SQL scripts to create the schema and do the data load. They will be doing a lot of site-specific configuration (eg tuning the SGA, temp tablespaces, # of concurrent connections, etc based on expected load).
现在,至于将这个提供给客户,从您的评论中看来,您似乎将这个提供给DBA。几乎任何Oracle安装都会涉及DBA。使用SQL脚本可以很好地创建模式并进行数据加载。他们将进行大量特定于站点的配置(例如,根据预期负载调整SGA,临时表空间,并发连接数等)。
You, as the vendor, can give guidance on any relevant configuration and you may get involved in support and possibly installation but ultimately it's up to them to figure out what works for them. Oracle runs on a large number of operating systems and hardware variants with infinite variations in network topology and firewall configuraiton. You can't factor in all of these to an installer or even a set of instructions (other than the guidelines mentioned previously).
作为供应商,您可以为任何相关配置提供指导,您可能会参与支持和可能的安装,但最终由他们决定哪些适用于他们。 Oracle运行在大量操作系统和硬件变体上,在网络拓扑和防火墙配置方面存在无限变化。您不能将所有这些因素都考虑在安装程序或甚至一组指令中(除了前面提到的指南之外)。
#4
The last time I was involved in the creation of a (oracle) db (for a reasonably large company with in-house DBAs) the DBAs wanted to know things like:
我最后一次参与创建(oracle)数据库(对于拥有内部DBA的合理大公司),DBA想知道如下事情:
- what we wanted to call the db,
- what tablespaces we would need, and an estimate of how much data would be in each one
- how many users would be connecting.
我们想要称之为db,
我们需要什么样的表空间,以及估计每个表空间的数量
有多少用户会连接。
(From memory) they set up the db and tablespaces, then we provided a combination of simple scripts that they could run (or clear instructions if a task wasn't easy to automate)
As I say this was for an in-house app, so your mileage may vary, but in my case they wanted all instructions clearly spelt out so that (a) there was no possibily of a misunderstanding leading to the wrong thing being done, and (b) no culpability on their part if something didn't work ("we were just following the instructions")
(从内存中)他们设置了db和tablespaces,然后我们提供了他们可以运行的简单脚本的组合(或者如果任务不容易自动化则清除指令)正如我所说这是针对内部应用程序,因此,你的里程可能会有所不同,但在我的情况下,他们希望明确说明所有指示,以便(a)不可能产生误解导致错误的事情发生,并且(b)如果有什么事情没有他们的责任,那么他们就没有责任。工作(“我们只是按照说明”)