使用 sqlplus 的 Oracle 备份数据库可能吗？

Oracle Backup Database with sqlplus it's possible?

提问by sakana

I need to do some structural changes to a database (alter tables, add new columns, change some rows etc) but I need to make sure that if something goes wrong i can rollback to initial state:

我需要对数据库进行一些结构性更改（更改表、添加新列、更改某些行等）但我需要确保如果出现问题我可以回滚到初始状态：

• All needed changes are inside a SQL script file.
• I don't have administrative access to database.
• I really need to ensure the backup is done on server side since the BD has more than 30 GB of data.
• I need to use sqlplus (under a ssh dedicated session over a vpn)
• Its not possible to use "flashback database"! It's off and i can't stop the database.

• 所有需要的更改都在一个 SQL 脚本文件中。
• 我没有对数据库的管理访问权限。
• 我真的需要确保备份是在服务器端完成的，因为 BD 有超过 30 GB 的数据。
• 我需要使用 sqlplus（在通过 vpn 的 ssh 专用会话下）
• 不可能使用“闪回数据库”！它关闭了，我无法停止数据库。

Am i in really deep $#$%?

我真的很深 $#$% 吗？

Any ideas how to backup the database using sqlplus and leaving the backup on db server?

任何想法如何使用 sqlplus 备份数据库并将备份保留在 db 服务器上？

回答by KevinDTimm

better than exp/imp, you should use rman. it's built specifically for this purpose, it can do hot backup/restore and if you completely screw up, you're still OK.

比 exp/imp 好，你应该使用 rman。它是专门为此目的而构建的，它可以进行热备份/恢复，如果你完全搞砸了，你仍然可以。

One 'gotcha' is that you have to backup the $ORACLE_HOME directory too (in my experience) because you need that locally stored information to recover the control files.

一个“问题”是您也必须备份 $ORACLE_HOME 目录（以我的经验），因为您需要本地存储的信息来恢复控制文件。

a search of rman on google gives some VERY good information on the first page.

在 google 上搜索 rman 会在第一页上提供一些非常好的信息。

回答by DCookie

An alternate approach might be to create a new schema that contains your modified structures and data and actually test with that. That presumes you have enough space on your DB server to hold all the test data. You really should have a pretty good idea your changes are going to work before dumping them on a production environment.

另一种方法可能是创建一个包含修改后的结构和数据的新模式，并实际使用它进行测试。这假设您的数据库服务器上有足够的空间来保存所有测试数据。在将更改转储到生产环境之前，您真的应该有一个很好的想法。

I wouldn't use sqlplus to do this. Take a look at export/import. The export utility will grab the definitions and data for your database (can be done in read consistent mode). The import utility will read this file and create the database structures from it. However, access to these utilities does require permissions to be granted, particularly if you need to backup the whole database, not just a schema.

我不会使用 sqlplus 来做到这一点。看看导出/导入。导出实用程序将获取数据库的定义和数据（可以在读取一致模式下完成）。导入实用程序将读取此文件并从中创建数据库结构。但是，访问这些实用程序确实需要授予权限，尤其是当您需要备份整个数据库而不仅仅是模式时。

That said, it's somewhat troubling that you're expected to perform the tasks of a DBA (alter tables, backup database, etc) without administrative rights. I think I would be at least asking for the help of a DBA to oversee your approach before you start, if not insisting that the DBA (or someone with appropriate privileges and knowledge) actually perform the modifications to the database and help recover if necessary.

也就是说，您需要在没有管理权限的情况下执行 DBA 的任务（更改表、备份数据库等），这有点令人不安。我想我至少会在开始之前寻求 DBA 的帮助来监督您的方法，如果不是坚持让 DBA（或具有适当权限和知识的人）实际执行对数据库的修改并在必要时帮助恢复。

回答by Thomas Jones-Low

Trying to back up 30GB of data through sqlplus is insane, It will take several hours to do and require 3x to 5x as much disk space, and may not be possible to restore without more testing.

尝试通过 sqlplus 备份 30GB 的数据是疯狂的，这将需要几个小时才能完成，并且需要 3 到 5 倍的磁盘空间，而且如果没有更多测试，可能无法恢复。

You need to use exp and imp. These are command line tools designed to backup and restore the database. They are command line tools, which if you have access to sqlplus via your ssh, you have access to imp/exp. You don't need administrator access to use them. They will dump the database (with al tables, triggers, views, procedures) for the user(s) you have access to.

您需要使用 exp 和 imp。这些是用于备份和恢复数据库的命令行工具。它们是命令行工具，如果您可以通过 ssh 访问 sqlplus，则可以访问 imp/exp。您不需要管理员访问权限即可使用它们。他们将为您有权访问的用户转储数据库（包括所有表、触发器、视图、过程）。