1.生成pfile并修改
[oracle@odbt1 dbs]$ cat init.ora |grep -v ^#|grep -v ^$ > inittandb.ora
修改里面的db_name和controlfile
删除其他内存参数:db_block_buffers、shared_pool_size
增加
sga_max_size=300m
sga_target=300M
undo_management=auto
undo_tablespace=UNDOTBS
2.生成密码文件
SQL> startup nomount
SQL> ho
[oracle@odbt1 dbs]$ orapwd file=orapwtandb password=system entries=10
[oracle@odbt1 dbs]$ exit
3.生成spfile,并重启库
create spfile from pfile;
4.修改部分参数
如有需要,可以修改db_create_file_dest。
5.创建库
刚拷贝过来的脚本创建表空间是缺少目录的,得加上才行,或者修改spfile时加上db_create_file_dest做成OMF管理数据文件。
CREATE DATABASE tandb
USER SYS IDENTIFIED BY system
USER SYSTEM IDENTIFIED BY system
LOGFILE GROUP 1 ('/oracle/oradata/tandb/logfiles/redo01.log') SIZE 100M,
GROUP 2 ('/oracle/oradata/tandb/logfiles/redo02.log') SIZE 100M,
GROUP 3 ('/oracle/oradata/tandb/logfiles/redo03.log') SIZE 100M
MAXLOGFILES 5
MAXLOGMEMBERS 5
MAXLOGHISTORY 1
MAXDATAFILES 100
MAXINSTANCES 1
CHARACTER SET US7ASCII
NATIONAL CHARACTER SET AL16UTF16
DATAFILE '/oracle/oradata/tandb/datafiles/system01.dbf' SIZE 325M REUSE
EXTENT MANAGEMENT LOCAL
SYSAUX DATAFILE '/oracle/oradata/tandb/datafiles/sysaux01.dbf' SIZE 325M REUSE
DEFAULT TABLESPACE tbs_1
DATAFILE '/oracle/oradata/tandb/datafiles/tbs_1.dbf' SIZE 325M REUSE
DEFAULT TEMPORARY TABLESPACE tempts1
TEMPFILE '/oracle/oradata/tandb/datafiles/temp01.dbf'
SIZE 20M REUSE
UNDO TABLESPACE undotbs
DATAFILE '/oracle/oradata/tandb/datafiles/undotbs01.dbf'
SIZE 200M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;
6.创建数据字典catalog,catproc
@?/rdbms/admin/catalog.sql;
@?/rdbms/admin/catproc.sql;
7.用netca配置监听,然后修改为静态监听
在动态监听的基础上加以下内容:
(SID_DESC=
(GLOBAL_DBNAME=tandb)
(SID_NAME=tandb)
(ORACLE_HOME=/apps/product/10.2.0/db_1)
vi复制:6yy p
8.设置归档:
SQL> alter system set log_archive_dest_1='location=/oracle/arch1/tandb' scope=both;
SQL> shutdown immediate
SQL> startup mount;
SQL> alter database archivelog;
SQL> alter database open;
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /oracle/arch1/tandb
Oldest online log sequence 3
Next log sequence to archive 5
Current log sequence 5
9.创建几个dump目录
-bash-3.00$ mkdir -p /edm01/ocmdb/admin/bdump
-bash-3.00$ mkdir -p /edm01/ocmdb/admin/cdump
-bash-3.00$ mkdir -p /edm01/ocmdb/admin/udump
-bash-3.00$ mkdir -p /edm01/ocmdb/admin/adump
alter system set audit_file_dest='/edm01/ocmdb/admin/adump' scope=spfile;
alter system set background_dump_dest='/edm01/ocmdb/admin/bdump' scope=spfile;
alter system set core_dump_dest='/edm01/ocmdb/admin/cdump' scope=spfile;
alter system set user_dump_dest='/edm01/ocmdb/admin/udump' scope=spfile;
10.创建一个bigfile tablespace
create bigfile tablespace tbsbig1
datafile '/edm01/ocmdb/tbsbig1.dbf' size 10m
autoextend on maxsize unlimited
extent management local
segment space management auto uniform size 128k;
11.将所有数据文件和临时文件更改为可扩展。
alter database datafile 1 autoextend on;
alter database datafile 2 autoextend on;
alter database datafile 3 autoextend on;
alter database datafile 4 autoextend on;
alter database datafile 5 autoextend on;
12.备份
删除不存在的备份集
CROSSCHECK BACKUP;
DELETE EXPIRED BACKUP;
delete noprompt expired backup;
删除不存在的归档信息
crosscheck archivelog all;
delete expired archivelog all;
delete noprompt expired archivelog all;
备份:
run
{
CONFIGURE CONTROLFILE AUTOBACKUP on;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/edm01/arch/%F_%d';
backup database include current controlfile
format '/edm01/arch/db_%d_%T_%U';
}
*****.遇到的错误及解决办法:
错误1:
SQL> alter system set log_archive_dest_1='/location=/edm01/arch' scope=both;
alter system set log_archive_dest_1='/location=/edm01/arch' scope=both
*
ERROR at line 1:
ORA-32017: failure in updating SPFILE
ORA-16179: incremental changes to "log_archive_dest_1" not allowed with SPFILE
原因是这写错了:'/location= 不写这个也报错
错误2:
SQL> alter system set background_core_dump='/edm01/tandb/admin/bdump' scope=spfile;
alter system set background_core_dump='/edm01/tandb/admin/bdump' scope=spfile
*
ERROR at line 1:
ORA-00096: invalid value /edm01/tandb/admin/bdump for parameter
background_core_dump, must be from among partial, full
应该是:background_dump_dest
错误3:
SQL> alter database archivelog;
alter database archivelog
*
ERROR at line 1:
ORA-00265: instance recovery required, cannot set ARCHIVELOG mode
SQL> shutdown immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 314572800 bytes
Fixed Size 1279988 bytes
Variable Size 96471052 bytes
Database Buffers 213909504 bytes
Redo Buffers 2912256 bytes
Database mounted.
Database opened.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 314572800 bytes
Fixed Size 1279988 bytes
Variable Size 96471052 bytes
Database Buffers 213909504 bytes
Redo Buffers 2912256 bytes
Database mounted.
SQL> alter database archivelog;
Database altered.
SQL> alter database open;
Database altered.
SQL>