oracle 创建测试数据库,[20151124]快速建立测试数据库.txt

[20151124]快速建立测试数据库.txt

-- 以建立11.2.0.4的数据库为例子说明，以前写过使用内存来运行测试数据库，以这个为基础并且做一个记录。

-- 重新删除在建立数据库。

startup nomount;

alter system enable restricted session;

RMAN> drop database including backups;

1.建立内存盘:

# mkdir -p /mnt/ramdisk

# mount -t tmpfs -o size=8G tmpfs /mnt/ramdisk

# su - oracle

2.检查环境变量是否设置正确

$ env | grep -i oracle

odbs=/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs

onetadmin=/u01/app/oracle/product/11.2.0.4/dbhome_1/network/admin

onet=/u01/app/oracle/product/11.2.0.4/dbhome_1/network

USER=oracle

LD_LIBRARY_PATH=/u01/app/oracle/product/11.2.0.4/dbhome_1/lib:/u01/app/oracle/product/11.2.0.4/dbhome_1/oracm/lib:/lib:/usr/lib:/usr/local/lib:/lib64:/usr/lib64:/usr/local/lib64

ORACLE_SID=book

oh=/u01/app/oracle/product/11.2.0.4/dbhome_1

ORACLE_BASE=/u01/app/oracle

MAIL=/var/spool/mail/oracle

PATH=.:/usr/kerberos/bin:/usr/local/bin:/bin:/usr/bin:/usr/NX/bin:/home/oracle/bin:/home/oracle/bin:/u01/app/oracle/product/11.2.0.4/dbhome_1/bin

ob=/u01/app/oracle

PWD=/home/oracle

obdump=/u01/app/oracle/admin/dbcn1/bdump

oudump=/u01/app/oracle/admin/dbcn1/udump

HOME=/home/oracle

LOGNAME=oracle

ORACLE_HOME=/u01/app/oracle/product/11.2.0.4/dbhome_1

--设置如下环境变量

export NLS_LANG=AMERICAN_AMERICA.zhs16gbk

export NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS'

export NLS_TIMESTAMP_TZ_FORMAT='YYYY-MM-DD HH24:MI:SS.FF TZH:TZM'

3.执行dbca:

--注意在执行前检查/etc/oratab文件，删除以前的遗留信息。

$ export ORACLE_SID=

$ORACLE_HOME/bin/dbca -createDatabase -templateName General_Purpose.dbc -gdbName book -sid book -sysPassword oracle \

-systemPassword oracle -storageType FS -characterSet ZHS16GBK -nationalCharacterSet AL16UTF16 -listeners LISTENER -sampleSchema  true -totalMemory 800 \

-databaseType MULTIPURPOSE -silent -datafileDestination /mnt/ramdisk -redoLogFileSize 50

-- 最好加入-sampleSchema  true 这样安装测试带的测试例子。在我的测试环境大约5分钟安装完成。

4.检查并设置：

$ export ORACLE_SID=book

$ rlsql

SQL*Plus: Release 11.2.0.4.0 Production on Tue Nov 24 09:14:42 2015

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

SYS@book> show sga

Total System Global Area  626327552 bytes

Fixed Size                  2255832 bytes

Variable Size             180356136 bytes

Database Buffers          436207616 bytes

Redo Buffers                7507968 bytes

--有3个细节要注意，建立的数据库没有打开archivelog：

SYS@book> archive log list;

Database log mode              No Archive Mode

Automatic archival             Disabled

Archive destination            USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence     3

Current log sequence           5

RMAN> list incarnation;

using target database control file instead of recovery catalog

List of Database Incarnations

DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time

------- ------- -------- ---------------- --- ---------- ----------

1       1       BOOK     1337401710       PARENT  1          2013-08-24 11:37:30

2       2       BOOK     1337401710       CURRENT 925702     2015-11-24 09:11:12

--实际上安装是做了1次open resetlogs打开的。检查/u01/app/oracle/cfgtoollogs/dbca/book目录：

$ grep resetlogs *

trace.log:[Thread-14] [ 2015-11-24 09:11:12.764 CST ] [CloneDBCreationStep.executeImpl:517]  alter database "book" open resetlogs;

--另外就是example表空间属性NOLOGGING：

CREATE TABLESPACE EXAMPLE DATAFILE

'/mnt/ramdisk/book/example01.dbf' SIZE 320640K AUTOEXTEND ON NEXT 640K MAXSIZE UNLIMITED

NOLOGGING

ONLINE

EXTENT MANAGEMENT LOCAL AUTOALLOCATE

BLOCKSIZE 8K

SEGMENT SPACE MANAGEMENT AUTO

FLASHBACK ON;

5.修改相关设置：

$ mv /u01/app/oracle/fast_recovery_area/book/control02.ctl /mnt/ramdisk/book/

$ tar czvf book.tgz /mnt/ramdisk/book/

--做1个冷备份，坏了直接覆盖就ok了。

SYS@book> startup nomount

SYS@book> alter system set control_files='/mnt/ramdisk/book/control01.ctl','/mnt/ramdisk/book/control02.ctl' scope=spfile;

System altered.

SYS@book> alter system set log_archive_dest_1="location=/u01/app/oracle/archivelog/book" ;

System altered.

SYS@book> alter database archivelog ;

Database altered.

SYS@book> archive log list;

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            /u01/app/oracle/archivelog/book

Oldest online log sequence     3

Next log sequence to archive   5

Current log sequence           5

SYS@book> alter user scott account unlock ;

User altered.

SYS@book> password scott

Changing password for scott

New password:

Retype new password:

Password changed