create manual database

最新推荐文章于 2022-07-13 21:34:13 发布

huchenghua987

最新推荐文章于 2022-07-13 21:34:13 发布

阅读量266

点赞数

分类专栏： oracle之常做工作文章标签： database oracle dictionary insert diagnostics table

本文链接：https://blog.csdn.net/huchenghua987/article/details/5927276

版权

oracle之常做工作专栏收录该内容

13 篇文章 0 订阅

订阅专栏

现场支持细节

52上：

Tar /oracle oracle.tar

80上

ftp 10.15.6.52

user: root

pwd:

get oracle.tar

tar xvf oracle.tar

修改环境变量

$ cd /oracle

$ vi .profile

PATH=/usr/bin:/etc:/usr/sbin:/usr/ucb:$HOME/bin:/usr/bin/X11:/sbin:.

export PATH

if [ -s "$MAIL" ] # This is at Shell startup. In normal

then echo "$MAILMSG" # operation, the Shell checks

fi # periodically.

export ORACLE_SID=oratd

export ORACLE_BASE=/oracle

export ORACLE_HOME=/oracle/product/10.2

export ORA_NLS33=$ORACLE_HOME/ocommon/nls/admin/data

export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK

export PATH=$ORACLE_HOME/bin:$PATH

创建admin下的目录和一些相关目录

#!/bin/sh

mkdir -p /oracle/admin/oratd/adump

mkdir -p /oracle/admin/oratd/bdump

mkdir -p /oracle/admin/oratd/cdump

mkdir -p /oracle/admin/oratd/dpdump

mkdir -p /oracle/admin/oratd/pfile

mkdir -p /oracle/admin/oratd/udump

mkdir -p /oracle/admin/oratd/flash_recovery_area

mkdir -p /oracle/oradata/oratd

mkdir -p /oracle/product/10.2/cfgtoollogs/dbca/oratd

mkdir -p /oracle/product/10.2/dbs

ORACLE_SID=oratd; export ORACLE_SID

echo You should Add this entry in the /etc/oratab: oratd:/u01/app/oracle/product/10.2:Y

/oracle/product/10.2/bin/sqlplus /nolog @/oracle/admin/oratd/scripts/oratd.sql

修改初始化参数文件 ：

在/oracle/admin/oratd/pfile下Vi一个初始化参数文件init.ora.86200815437

##############################################################################

###########################################

# Cache and I/O

###########################################

db_block_size=8192

db_cache_size=303316480

db_file_multiblock_read_count=16

###########################################

# Cursors and Library Cache

###########################################

open_cursors=300

###########################################

# Database Identification

###########################################

db_domain=""

db_name=oratd[h1]

###########################################

# Diagnostics and Statistics

###########################################

background_dump_dest=/oracle/admin/oratd/bdump[h2]

core_dump_dest=/oracle/admin/oratd/cdump

user_dump_dest=/oracle/admin/oratd/udump

###########################################

# File Configuration

###########################################

control_files=("/oracle/oradata/oratd[h3] /control01.ctl", "/oracle/oradata/oratd/control02.ctl", "/oracle/oradata/oratd/control03.ctl")

db_recovery_file_dest=/oracle/admin/oratd/flash_recovery_area[h4]

db_recovery_file_dest_size=214748364

###########################################

# Job Queues

###########################################

job_queue_processes=10

###########################################

# Miscellaneous

###########################################

compatible=10.2.0.1.0

###########################################

# Pools

###########################################

java_pool_size=0

large_pool_size=0

shared_pool_size=203886080

###########################################

# Processes and Sessions

###########################################

processes=150

###########################################

# Security and Auditing

###########################################

audit_file_dest=/oracle/admin/oratd/adump[h5]

remote_login_passwordfile=EXCLUSIVE

###########################################

# Sort, Hash Joins, Bitmap Indexes

###########################################

pga_aggregate_target=167772160

###########################################

# System Managed Undo and Rollback Segments

###########################################

undo_management=AUTO

undo_tablespace=UNDOTBS1[h6]

手动建立密码文件(注意这里的路径)

/oracle/product/10.2/bin/orapwd file=/oracle/product/10.2/dbs/orapworatd password=oracle force=y

运行建库脚本:

spool /oracle/admin/oratd/scripts/CreateDB.log

SQL> startup nomount pfile="/oracle/admin/oratd/pfile/init.ora.86200815437";

CREATE DATABASE "oratd"

MAXINSTANCES 8

MAXLOGHISTORY 1

MAXLOGFILES 16

MAXLOGMEMBERS 3

MAXDATAFILES 200

DATAFILE '/oracle/oradata/oratd/system01.dbf' SIZE 500M REUSE

EXTENT MANAGEMENT LOCAL

SYSAUX DATAFILE '/oracle/oradata/oratd/sysaux01.dbf' SIZE 300M REUSE

SMALLFILE DEFAULT TEMPORARY TABLESPACE TEMP TEMPFILE '/oracle/oradata/oratd/temp01.dbf' SIZE 200M REUSE

SMALLFILE UNDO TABLESPACE "UNDOTBS1" DATAFILE '/oracle/oradata/oratd/undotbs01.dbf' SIZE 500M REUSE

CHARACTER SET ZHS16GBK

NATIONAL CHARACTER SET AL16UTF16

LOGFILE GROUP 1 ('/oracle/oradata/oratd/redo01.log') SIZE 100M,

GROUP 2 ('/oracle/oradata/oratd/redo02.log') SIZE 100M,

GROUP 3 ('/oracle/oradata/oratd/redo03.log') SIZE 100M

USER SYS IDENTIFIED BY "oracle" USER SYSTEM IDENTIFIED BY "oracle";

spool off

运行相关脚本：

connect SYS/oracle as SYSDBA

set echo on

spool /oracle/admin/oratd/scripts/CreateDBCatalog.log

@/oracle/product/10.2/rdbms/admin/catalog.sql; ----〉建立数据字典视图

@/oracle/product/10.2/rdbms/admin/catblock.sql; ----〉Creates views of oracle locks

@/oracle/product/10.2/rdbms/admin/catproc.sql; ----〉用于创建使用 PL/SQL 所需的程序包和过程

@/oracle/product/10.2/rdbms/admin/catoctk.sql;

@/oracle/product/10.2/rdbms/admin/owminst.plb;

@/oracle/product/10.2/rdbms/admin/catexp.sql；EXP/IMP

@/oracle/product/10.2/rdbms/admin/catexp7.sql; 解决不同版本EXP问题（可选）

conect SYSTEM/oracle

@/oracle/product/10.2/sqlplus/admin/pupbld.sql[k7] ;

connect SYSTEM/oracle

set echo on

spool /oracle/admin/oratd/scripts/sqlPlusHelp.log

@/oracle/product/10.2/sqlplus/admin/help/hlpbld.sql helpus.sql; ---生成SQL*Plus帮助系统

spool off

connect SYS/oracle as SYSDBA

set echo on

spool /oracle/admin/oratd/scripts/postDBCreation.log

connect "SYS"/oracle as SYSDBA

set echo on

create spfile='/oracle/product/10.2/dbs/spfileoratd.ora' FROM pfile='/oracle/admin/oratd/scripts/init.ora';

shutdown immediate;

connect SYS/oracle as SYSDBA

startup ;

select 'utl_recomp_begin: ' || to_char(sysdate, 'HH:MI:SS') from dual;

execute utl_recomp.recomp_serial();

select 'utl_recomp_end: ' || to_char(sysdate, 'HH:MI:SS') from dual;

spool /oracle/admin/oratd/scripts/postDBCreation.log

exit;

建库后测试:

$ sqlplus "/ as sysdba"

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Oct 20 16:21:23 2008

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production

With the Partitioning, OLAP and Data Mining options

SQL> show sga

Total System Global Area 1191182336 bytes

Fixed Size 2020424 bytes

Variable Size 318770104 bytes

Database Buffers 855638016 bytes

Redo Buffers 14753792 bytes

SQL> select open_mode from v$database;

OPEN_MODE

----------

READ WRITE

建立用户并授予相应权限 ( 记录一部分)

create user apm_itsm

identified by apm_itsm

default tablespace USERS

temporary tablespace temp1

profile soxprofile;

grant connect to apm_itsm

grant SELECT ANY DICTIONARY to apm_itsm

moka/moka

create user moka

identified by moka

default tablespace USERS

temporary tablespace temp1

profile soxprofile;

grant connect,resource to moka

grant select any table to moka

grant SELECT ANY DICTIONARY to moka

grant exp_full_database to moka;

grant IMP_FULL_DATABASE to moka

模拟故障

故障1:

删除logfile文件其中一个组成员

故障2：

并发用户，模拟死锁实验

1.1 主表

create table WDZ1

(WDZ1ID NUMBER not null,

MEMO VARCHAR2(20));

alter table WDZ1

add constraint solo1 primary key (WDZ1ID);

1.2 从表(没有外健的索引)

create table WDZ2

(WDZ2ID NUMBER not null,

WDZ1ID NUMBER,

MEMO VARCHAR2(20)

);

alter table WDZ2

add constraint solo2 primary key (WDZ2ID);

alter table WDZ2

add constraint solo3 foreign key (WDZ1ID)

references WDZ1 (WDZ1ID);

1.3 插入数据表到住表

begin

insert into wdz1 values (1,'aa');

insert into wdz1 values(2,'aa2');

insert into wdz1 values (3,'aa3');

insert into wdz2 values(10,3,'wdz3--1');

commit;

end;

1.4 在一个数据库seeesion里面插入数到从表，但是不提交事务

begin

update wdz2 set memo='update wdz3 momo'

where wdz2id=10;

insert into wdz2 values(20,2,'wdz2--1');

end;

对从表进行插入/修改记录，施加的锁也就是行级锁

1.5 在另外一个数据库seeesion里面删除主表数据

delete from wdz1 where wdz1id=1

这时候，程序会死锁，报错ORA-00060，除非上面的对从表的数据操作提交事务或者回滚事务。

session1:

SQL> update wdz2 set memo='update wdz3 momo'

2 where wdz2id=10;

session2:

delete from wdz1 where wdz1id=1

产生死锁

当session1 Rollback complete后，释放资源，锁消失

故障3：文件系统/usr 使用率达到93%

故障4：文件系统/test没有mount上

故障5：拔网线(ent1) ,出现错误报警

[h1]

数据库名

[h2]注意这些路径bdump [h2]，cdump….

必须已经存在了

[h3]/oracle/oradata/oratd [h3]/路径必须存在，control01.ctl这些会在启动实例时产生

[h4]这个 flash_recovery_area [h4]

必须已经存在

[h5]adump [h5]

必须已经存在

[h6]注意和建库脚本里的名字一致

[k7]用于创建 “产品用户配置文件” (Product User Profile) 表以及相关的过程，该脚本位于 $ORACLE_HOME/sqlplus/admin 目录中。运行此脚本还可在每次用户连接到 SQL*Plus 时防止生成警告消息。必须以用户 SYSTEM 的身份运行此脚本