Oracle 11g on ECS 测试实践--DB篇
我通过脚本和RMAN克隆两种方法创建数据库
一.通过SQL创建数据库
参考文档
1.创建pfile
[oracle@orcl1 dbs]$ cd /u01/app/oracle/product/11.2.0/db_1/dbs
[oracle@orcl1 dbs]$ cat bak.initORCL.ora
db_name='orcl'
db_unique_name='orcl1'
memory_target=400437056
processes = 150
audit_file_dest='/u01/app/oracle/admin/orcl/adump'
audit_trail ='db'
db_block_size=8192
db_create_file_dest='/u01/app/oracle/oradata'
db_recovery_file_dest='/u01/app/oracle/flash_recovery_area'
db_recovery_file_dest_size=2G
diagnostic_dest='/u01/app/oracle'
dispatchers='(PROTOCOL=TCP) (SERVICE=ORCLXDB)'
open_cursors=300
remote_login_passwordfile='EXCLUSIVE'
undo_tablespace='UNDOTBS1'
control_files = '/u01/app/oracle/oradata/orcl/control01.ctl','/u01/app/oracle/flash_recovery_area/orcl/control02.ctl'
compatible ='11.2.0'2.启动创建数据库
##创建数据库的脚本
[oracle@orcl1 dbs]$ cat /home/oracle/scripts/createdb.sql
CREATE DATABASE ORCL
USER SYS IDENTIFIED BY oracle
USER SYSTEM IDENTIFIED BY oracle
CONTROLFILE REUSE
CHARACTER SET UTF8
NATIONAL CHARACTER SET AL16UTF16
EXTENT MANAGEMENT LOCAL
ARCHIVELOG
MAXLOGFILES 192 MAXLOGMEMBERS 5 MAXLOGHISTORY 292 MAXDATAFILES 1024 MAXINSTANCES 32
LOGFILE GROUP 1('/oradata/ORCL/redo01a.log','/oradata/ORCL/redo01b.log') SIZE 50M BLOCKSIZE 512,
GROUP 2('/oradata/ORCL/redo02a.log','/oradata/ORCL/redo02b.log') SIZE 50M blocksize 512,
GROUP 3('/oradata/ORCL/redo03a.log','/oradata/ORCL/redo03b.log') SIZE 50M BLOCKSIZE 512
DATAFILE '/oradata/ORCL/system01.dbf' SIZE 300M REUSE AUTOEXTEND ON NEXT 10M MAXSIZE 10G
SYSAUX DATAFILE '/oradata/ORCL/sysaux01.dbf' SIZE 100M REUSE AUTOEXTEND ON NEXT 10M MAXSIZE 10G
DEFAULT TEMPORARY TABLESPACE TEMP TEMPFILE '/oradata/ORCL/temp01.dbf' SIZE 10M REUSE AUTOEXTEND ON NEXT 10M MAXSIZE 1G
UNDO TABLESPACE UNDOTBS1 DATAFILE '/oradata/ORCL/undotbs01.dbf'SIZE 10M REUSE AUTOEXTEND ON NEXT 10M MAXSIZE 1G
DEFAULT TABLESPACE USERS DATAFILE '/oradata/ORCL/users01.dbf' SIZE 10M REUSE AUTOEXTEND ON NEXT 10M MAXSIZE 10G
/
##基表、动态性能视图等
[oracle@orcl1 dbs]$ cat /home/oracle/scripts/env.sql
@?/rdbms/admin/catalog.sql;
@?/rdbms/admin/catproc.sql;
conn system/oracle;
@?/sqlplus/admin/pupbld.sql;
[oracle@orcl1 dbs]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Wed Jul 24 22:11:55 2019
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> @/home/oracle/scripts/createdb.sql;
SQL> @/home/oracle/scripts/env.sql;二、通过DBCA中的自带的备份片进行克隆
1.创建基于seed的pfile
DBCA使用的备份片，dbname是seeddata，如使用11.2.0.4版本，pfile应如下
[oracle@orcl1 dbs]$ cat initseed.ora
db_name=seeddata
sga_target=500M
control_files=/oradata/ORCL/control01.ctl
compatible ='11.2.0.4'2.复制控制文件
[oracle@orcl1 templates]$ cd /u01/app/oracle/product/11.2.0/db_1/assistants/dbca/templates
[oracle@orcl1 templates]$ cp Seed_Database.ctl /oradata/ORCL/control01.ctl3.启动seed实例
[oracle@orcl1 ~]$ export ORACLE_SID=seed
[oracle@orcl1 dbs]$ rman target /
Recovery Manager: Release 11.2.0.4.0 - Production on Wed Jul 24 22:29:21 2019
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database (not started)
RMAN> startup mount;
Oracle instance started
database mounted
Total System Global Area 521936896 bytes
Fixed Size 2254824 bytes
Variable Size 159385624 bytes
Database Buffers 356515840 bytes
Redo Buffers 3780608 bytes4.修改redo位置
SQL> select 'alter database rename file '||chr(39)||member||chr(39)||' to '||chr(39)||replace(member,'/ade/b/2232964209/oracle/oradata/seeddata','/oradata/ORCL') ||chr(39)||';' from v$logfile;
'ALTERDATABASERENAMEFILE'||CHR(39)||MEMBER||CHR(39)||'TO'||CHR(39)||REPLACE(MEMBER,'/ADE/B/2232964209/ORACLE/ORADATA/SEEDDATA','/ORADATA/ORCL')||CHR(39)||';'
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
alter database rename file '/ade/b/2232964209/oracle/oradata/seeddata/redo01.log' to '/oradata/ORCL/redo01.log';
alter database rename file '/ade/b/2232964209/oracle/oradata/seeddata/redo02.log' to '/oradata/ORCL/redo02.log';
alter database rename file '/ade/b/2232964209/oracle/oradata/seeddata/redo03.log' to '/oradata/ORCL/redo03.log';
SQL> alter database rename file '/ade/b/2232964209/oracle/oradata/seeddata/redo01.log' to '/oradata/ORCL/redo01.log';
Database altered.
SQL> alter database rename file '/ade/b/2232964209/oracle/oradata/seeddata/redo02.log' to '/oradata/ORCL/redo02.log';
Database altered.
SQL> alter database rename file '/ade/b/2232964209/oracle/oradata/seeddata/redo03.log' to '/oradata/ORCL/redo03.log';
Database altered.5.RMAN恢复数据库
RMAN> catalog start with '/u01/app/oracle/product/11.2.0/db_1/assistants/dbca/templates/Seed_Database.dfb';
using target database control file instead of recovery catalog
searching for all files that match the pattern /u01/app/oracle/product/11.2.0/db_1/assistants/dbca/templates/Seed_Database.dfb
List of Files Unknown to the Database
=====================================
File Name: /u01/app/oracle/product/11.2.0/db_1/assistants/dbca/templates/Seed_Database.dfb
Do you really want to catalog the above files (enter YES or NO)? y
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: /u01/app/oracle/product/11.2.0/db_1/assistants/dbca/templates/Seed_Database.dfb
RMAN> run
2> {set newname for datafile 1 to '/oradata/ORCL/system01.dbf';
3> set newname for datafile 2 to '/oradata/ORCL/sysaux01.dbf';
4> set newname for datafile 3 to '/oradata/ORCL/undotbs01.dbf';
5> set newname for datafile 4 to '/oradata/ORCL/user01.dbf';
6> restore database;
7> switch datafile all;
8> recover database;
9> }
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting restore at 24-JUL-19
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=18 device type=DISK
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /oradata/ORCL/system01.dbf
channel ORA_DISK_1: restoring datafile 00002 to /oradata/ORCL/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /oradata/ORCL/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /oradata/ORCL/user01.dbf
channel ORA_DISK_1: reading from backup piece /ade/b/2232964209/oracle/oradata/Seed_Database.dfb
channel ORA_DISK_1: errors found reading piece handle=/ade/b/2232964209/oracle/oradata/Seed_Database.dfb
channel ORA_DISK_1: failover to piece handle=/u01/app/oracle/product/11.2.0/db_1/assistants/dbca/templates/Seed_Database.dfb tag=NULL
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:45
Finished restore at 24-JUL-19
datafile 1 switched to datafile copy
input datafile copy RECID=5 STAMP=1014503999 file name=/oradata/ORCL/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=6 STAMP=1014503999 file name=/oradata/ORCL/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=7 STAMP=1014503999 file name=/oradata/ORCL/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=8 STAMP=1014503999 file name=/oradata/ORCL/user01.dbf
Starting recover at 24-JUL-19
using channel ORA_DISK_1
starting media recovery
RMAN-08187: WARNING: media recovery until SCN 925701 complete
Finished recover at 24-JUL-19
RMAN> alter database open resetlogs;
database opened6.修复临时文件
[oracle@orcl1 dbs]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Wed Jul 24 22:52:08 2019
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select file_name from dba_temp_files;
select file_name from dba_temp_files
*
ERROR at line 1:
ORA-01157: cannot identify/lock data file 201 - see DBWR trace file
ORA-01110: data file 201:
'/ade/b/2232964209/oracle/oradata/seeddata/temp01.dbf'
SQL> alter tablespace temp add tempfile '/oradata/ORCL/temp01.dbf' size 10m;
Tablespace altered.
SQL> alter tablespace temp drop tempfile '/ade/b/2232964209/oracle/oradata/seeddata/temp01.dbf';
Tablespace altered.7.修改dbname
SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 521936896 bytes
Fixed Size 2254824 bytes
Variable Size 159385624 bytes
Database Buffers 356515840 bytes
Redo Buffers 3780608 bytes
Database mounted.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@orcl1 dbs]$ nid target=sys/oracle dbname=ORCL
DBNEWID: Release 11.2.0.4.0 - Production on Wed Jul 24 23:17:23 2019
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to database SEEDDATA (DBID=4152976186)
Connected to server version 11.2.0
Control Files in database:
/oradata/ORCL/control01.ctl
Change database ID and database name SEEDDATA to ORCL? (Y/[N]) => y
Proceeding with operation
Changing database ID from 4152976186 to 1542337155
Changing database name from SEEDDATA to ORCL
Control File /oradata/ORCL/control01.ctl - modified
Datafile /oradata/ORCL/system01.db - dbid changed, wrote new name
Datafile /oradata/ORCL/sysaux01.db - dbid changed, wrote new name
Datafile /oradata/ORCL/undotbs01.db - dbid changed, wrote new name
Datafile /oradata/ORCL/user01.db - dbid changed, wrote new name
Datafile /oradata/ORCL/temp01.db - dbid changed, wrote new name
Control File /oradata/ORCL/control01.ctl - dbid changed, wrote new name
Instance shut down
Database name changed to ORCL.
Modify parameter file and generate a new password file before restarting.
Database ID for database ORCL changed to 1542337155.
All previous backups and archived redo logs for this database are unusable.
Database has been shutdown, open database with RESETLOGS option.
Succesfully changed database name and ID.
DBNEWID - Completed succesfully.8.使用ORCL的pfile启动实例
[oracle@orcl1 dbs]$ cat initORCL.ora
db_name='ORCL'
db_unique_name='ORCL1'
memory_target=512m
processes = 150
audit_file_dest='/u01/app/oracle/admin/orcl/adump'
audit_trail ='db'
db_block_size=8192
db_recovery_file_dest='/orabackup/flash_recovery_area'
db_recovery_file_dest_size=2G
diagnostic_dest='/u01/app/oracle'
dispatchers='(PROTOCOL=TCP) (SERVICE=ORCLXDB)'
open_cursors=300
remote_login_passwordfile='EXCLUSIVE'
undo_tablespace='UNDOTBS1'
control_files = '/oradata/ORCL/control01.ctl'
compatible ='11.2.0.4'
[oracle@orcl1 dbs]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Wed Jul 24 23:27:44 2019
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 534462464 bytes
Fixed Size 2254952 bytes
Variable Size 385877912 bytes
Database Buffers 142606336 bytes
Redo Buffers 3723264 bytes
Database mounted.
SQL> alter database open resetlogs;
Database altered.DB创建完成完成。
