Oracle测试schema包含HR、SH等用户,在Oracle 11g R2下可以按照如下方式生成
1. 检查字符集,若不是US7ASCII,则要修改字符集,否则会在mkplug.sql restore的时候报错1:
ERROR at line 1:
ORA-19583: conversation terminated due to error
ORA-19870: error while restoring backup piece
/opt/app/oracle/product/11.2.0/db_1/assistants/dbca/templates/example01.dfb
ORA-19615: some files not found in backup set
ORA-19613: datafile 6 not found in backup set
ORA-06512: at “SYS.DBMS_BACKUP_RESTORE”,line 5824
ORA-06512: at line 33
--检查字符集
sql>@H_301_18@select userenv('language') @H_301_18@from dual;
若不是US7ASCII则修改字符集2
sql>SHUTDOWN IMMEDIATE
sql>STARTUP MOUNT;
sql>ALTER SYSTEM ENABLE RESTRICTED SESSION;
sql>ALTER SYSTEM @H_301_18@SET JOB_QUEUE_PROCESSES=0;
sql>ALTER SYSTEM @H_301_18@SET AQ_TM_PROCESSES=0;
sql>ALTER DATABASE OPEN;
sql>ALTER DATABASE CHARACTER @H_301_18@SET INTERNAL_USE US7ASCII;
sql>SHUTDOWN IMMEDIATE;
sql>STARTUP
2.运行mkplug.sql脚本,前7个参数是password,后4-5个参数(参数数量应该和具体版本有关),设置如下3
cd /u01/app/oracle/product/11.2.0/db_1/demo/schema
sql>@?/demo/schema/mkplug.sql
·····
specify INPUT Metadata import file @H_301_18@as parameter 8:
Enter value @H_301_18@for 8: /u01/app/oracle/product/11.2.0/db_1/assistants/dbca/templates/example.dmp
specify INPUT database backup file @H_301_18@for tablespace EXAMPLE @H_301_18@as parameter 9:
Enter value @H_301_18@for 9: /u01/app/oracle/product/11.2.0/db_1/assistants/dbca/templates/example01.dfb
specify OUTPUT database file @H_301_18@for tablespace EXAMPLE @H_301_18@as parameter 10:
Enter value @H_301_18@for 10: /u01/app/oracle/oradata/skyHost/example01.dbf
specify OUTPUT log directory @H_301_18@as parameter 11:
Enter value @H_301_18@for 11: /u01/app/oracle/product/11.2.0/db_1/demo/schema/log/
specify OUTPUT dump file directory @H_301_18@as parameter 12:
Enter value @H_301_18@for 12: /u01/app/oracle/product/11.2.0/db_1/assistants/dbca/templates/
······
mkplug.sql DONE
TO_CHAR(SYSTIMEST
-----------------
20171127 03:45:07
参考资料