首先先将旧数据库启动起来,sartup force;
再导出用户数据:exp userid=USERNMAE/PSW@SID FILE=/***.DMP LOG=***.LOG OWNER=USERNAME FEEDBACK=10000.
导完或再建库。
建库步骤:
1、手工创建相关目录
/oraclepro/admin/dxora
/oraclepro/admin/dxora/adump
/oraclepro/admin/dxora/bdump
/oraclepro/admin/dxora/cdump
/oraclepro/admin/dxora/dpump
/oraclepro/admin/dxora/pfile
/oraclepro/admin/dxora/udump
2、手工创建初始化启动参数文件:/oraclepro/admin/dxora/pfile/initdSID.ora,内容:我是从旧实例的PFILE里拷贝出来的的,再修改一下SID。
内容如下:
#########################################################
# SGA Memory
###########################################
sga_target=2146435072
###########################################
# Job Queues
###########################################
job_queue_processes=10
###########################################
# Shared Server
###########################################
dispatchers="(PROTOCOL=TCP) (SERVICE=dxptoraXDB)"
###########################################
# Miscellaneous
###########################################
compatible=10.2.0.1.0
###########################################
# Security and Auditing
###########################################
audit_file_dest=/oraclepro//admin/dxora/adump
remote_login_passwordfile=EXCLUSIVE
###########################################
# Sort, Hash Joins, Bitmap Indexes
###########################################
pga_aggregate_target=839909376
###########################################
# Database Identification
###########################################
db_domain=""
db_name=dxora
###########################################
# File Configuration
###########################################
control_files=("/oracleapp/oradata/dxora/control01.ctl", "/oracleapp/oradata/dxora/control02.ctl", "/oracleapp/oradata/dxora/control03.ctl")
db_recovery_file_dest=/oracleapp/flash_recovery_area
db_recovery_file_dest_size=2147483648
###########################################
# Cursors and Library Cache
###########################################
open_cursors=300
###########################################
# System Managed Undo and Rollback Segments
###########################################
undo_management=AUTO
undo_tablespace=UNDOTBS1
###########################################
# Diagnostics and Statistics
###########################################
background_dump_dest=/oraclepro//admin/dxora/bdump
core_dump_dest=/oraclepro//admin/dxora/cdump
user_dump_dest=/oraclepro//admin/dxora/udump
###########################################
# Processes and Sessions
###########################################
processes=150
###########################################
# Cache and I/O
###########################################
db_block_size=8192
db_file_multiblock_read_count=16
############################################################
3、手工创建/oraclepro/product/10.2.0/db_1/dbs/initdxora.ora文件,
内容为:IFILE='/oraclepro/admin/dxora/pfile/initdxora.ora'
4、使用orapwd.exe命令,创建/oraclepro/product/10.2.0/db_1/dbs/PWDdxora.ora
命令:orapwd file=/oraclepro/product/10.2.0/db_1/dbs/PWDdxora.ora password=dxpt123 entries=5
5、通过oradism.exe命令(在10G以下版本的是oradim.exe),在服务里生成一个新的实例管理服务,启动方式为手工
set ORACLE_SID=dxora
oradism -new -sid dxora -startmode manual -pfile "/oraclepro/admin/dxora/pfile/initdxora.ora"
6.修改ORACLE环境变量 vi /home/oracle/.bash_profile
将SID=旧实例名更改为SID=新实例名
7.退出远程,用ORACLE重新登陆,可使用env命令查看环境变量是否已经更改。更改后
8.sqlplus /nolog
conn / as sysdba (会提示已连接到一个空的实例)
9.startup nomount pfile="/oraclepro/admin/dxora/pfile/initdxora.ora"
10,创建数据库
一、
create database dxora
MAXINSTANCES 2
MAXLOGHISTORY 32
MAXLOGFILES 32
MAXLOGMEMBERS 3
MAXDATAFILES 2048
character set ZHS16GBK
NATIONAL CHARACTER SET AL16UTF16
DATAFILE '/oracleapp/oradata/dxora/system01.dbf' size 500M autoextend on next 64K maxsize unlimited
sysaux DATAFILE '/oracleapp/oradata/dxora/sysaux01.dbf' size 500M autoextend on next 64K maxsize unlimited
DEFAULT TEMPORARY TABLESPACE TEMP TEMPFILE '/oracleapp/oradata/dxora/temp01.dbf' size 50M autoextend on MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1024K
UNDO TABLESPACE UNDOTBS1 datafile '/oracleapp/oradata/dxora/undotbs01.dbf' SIZE 1024M autoextend on next 64K maxsize unlimited
LOGFILE
GROUP 1 ('/oracleapp/oradata/dxora/redo01.log') SIZE 50M REUSE,
GROUP 2 ('/oracleapp/oradata/dxora/redo02.log') SIZE 50M REUSE,
GROUP 3 ('/oracleapp/oradata/dxora/redo03.log') SIZE 50M REUSE;
二、创建用户表空间
CREATE TABLESPACE USERS DATAFILE '/oracleapp/oradata/dxora/users01.dbf' SIZE 2048M REUSE
AUTOEXTEND ON NEXT 1280K
MINIMUM EXTENT 128K
DEFAULT STORAGE ( INITIAL 128K NEXT 128K MINEXTENTS 1 MAXEXTENTS 4096 PCTINCREASE 0);
三、创建数据字典
@/oraclepro/product/10.2.0/db_1/rdbms/admin/catalog.sql;
@/oraclepro/product/10.2.0/db_1/rdbms/admin/catproc.sql;
@/oraclepro/product/10.2.0/db_1/rdbms/admin/catblock.sql;
@/oraclepro/product/10.2.0/db_1/rdbms/admin/catoctk.sql;
conn system/manager
@/oraclepro/product/10.2.0/db_1/sqlplus/admin/pupbld.sql;
11、创建spfile并重启db
create spfile from pfile='/oraclepro/admin/dxora/pfile/initdxora.ora'
startup force
12.删除旧的实例
SQL> select instance_name from v$instance;
INSTANCE_NAME
----------------
wmsdev
SQL> shutdown immediate;
Database closed.
Database dismounted.
orACLE instance shut down.
SQL> startup mount exclusive
orACLE instance started.
Total System Global Area 939524096 bytes
Fixed Size 781984 bytes
Variable Size 245633376 bytes
Database Buffers 692060160 bytes
Redo Buffers 1048576 bytes
Database mounted.
SQL> alter system enable restricted session;
System altered.
SQL> drop database;
Database dropped.
创建用户
create user USERNAME identified by PSW;
权限:
Grant DBA,CONNECT ,RESOURCE TO USERNAME;
创建后有可能需要解锁才能进行远程连接。下面是解锁命令,如果不需解锁则可跳过
#####################################################################
alter profile default limit FAILED_LOGIN_ATTEMPTS unlimited;
改完后再
alter user USERNAME account unlock;
#####################################################################
以上是因为远程登陆报ora-2800用户被锁,须用以上方法解开。
下面就可以导进用户数据。
imp userid=USERNAME/PSW@SID FILE=/***.DMP LOG=/imp***.LOG FROMUSER=USERNAME,TOUSER=USERNAME.FEEDBACK=10000
由于我们那台服务器做了群集,因此在备机上也需要创建实例服务与参数。
须先shutdown normal 数据库,再切换到备机上运行。
1、手工创建相关目录
/oraclepro/admin/dxora
/oraclepro/admin/dxora/adump
/oraclepro/admin/dxora/bdump
/oraclepro/admin/dxora/cdump
/oraclepro/admin/dxora/dpump
/oraclepro/admin/dxora/pfile
/oraclepro/admin/dxora/udump
2.手工创建初始化启动参数文件:/oraclepro/admin/dxora/pfile/initdxora.ora
scp initdxora.ora node2:/oraclepro/admin/dxora/pfile/initdxora.ora
3、手工创建/oraclepro/product/10.2.0/db_1/dbs/initdxora.ora文件,
内容为:IFILE='/oraclepro/admin/dxora/pfile/initdxora.ora'
4、使用orapwd.exe命令,创建/oraclepro/product/10.2.0/db_1/dbs/PWDdxora.ora
命令:orapwd file=/oraclepro/product/10.2.0/db_1/dbs/PWDdxora.ora password=dxpt123 entries=5
5、通过oradism.exe命令,在服务里生成一个新的实例管理服务,启动方式为手工
set ORACLE_SID=dxora
oradism -new -sid dxora -startmode manual -pfile "/oraclepro/admin/dxora/pfile/initdxora.ora"
6.修改ORACLE环境变量 vi .bash_profile
7.修改TNSNAME.ORA
8.startup nomount pfile="/oraclepro/admin/dxora/pfile/initdxora.ora"
9.conn / as sysdba;
创建spfile并重启db
create spfile from pfile='/oraclepro/admin/dxora/pfile/initdxora.ora';
alter database mount;
alter database open;
(完)
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/21100044/viewspace-1018627/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/21100044/viewspace-1018627/