创建的ORACLE的SID
#切换到oracle用户环境
su - oracle
#本次创建的ORACLE的SID为UMCISC
export ORACLE_SID=UMCISC
#打印当前ORACLE的SID
echo $ORACLE_SID
UMCISC
创建相应目录
#将UMCISC换成自己的SID
mkdir -p $ORACLE_BASE/admin/UMCISC/{a,b,c,u}dump
mkdir -p $ORACLE_BASE/admin/UMCISC/pfile
mkdir -p $ORACLE_BASE/oradata/UMCISC
创建初始化文件
#打开$ORACLE_HOME/dbs目录
[oracle@node1 ~]$ cd $ORACLE_HOME/dbs
[oracle@node1 dbs]$ ls
hc_orcl.dat init.ora lkORCL orapworcl spfileorcl.ora
#复制初始化文件,规则为init+实例名.ora
[oracle@node1 dbs]$ cp init.ora iniUMCISC.ora
#编辑,将<ORACLE_BASE>换成对应的绝对路径,不知道的可以通过echo $ORACLE_BASE命令查看
[oracle@node1 dbs]$ vi iniUMCISC.ora
db_name='UMCISC'#换成自己的SID
memory_target=1G
processes = 150
audit_file_dest='/u01/app/oracle/admin/UMCISC/adump'#替换成自己的
audit_trail ='db'
db_block_size=8192
db_domain=''
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=UMCISCXDB)'#UMCISC将替换成自己的SID
open_cursors=300
remote_login_passwordfile='EXCLUSIVE'
undo_tablespace='UNDOTBS1'
# You may want to ensure that control files are created on separate physical
# devices
control_files = (ora_control3, ora_control4)#原文件是1,2改成不一样的不然会报错
compatible ='11.2.0'
创建密码文件
#将orapwUMCISC的UMCISC改成自己的SID,admin是密码
orapwd file=$ORACLE_HOME/dbs/orapwUMCISC password=admin entries=5 force=y
创建oracle的建库脚本 createdb.sql
#打开路径,
cd $ORACLE_BASE/oradata/UMCISC
#创建文件
[oracle@node1 rmblc]$ vi createdb.sql
create database UMCISC #替换成自己的SID
MAXINSTANCES 1
MAXLOGHISTORY 1
MAXLOGFILES 5
MAXLOGMEMBERS 5
MAXDATAFILES 100
DATAFILE
'/u01/app/oracle/oradata/UMCISC/system01.dbf' size 100m reuse autoextend on next 1m maxsize unlimited extent management local #替换成自己的路径
sysaux datafile
'/u01/app/oracle/oradata/UMCISC/sysaux01.dbf' size 100m reuse autoextend on next 1m maxsize unlimited #替换成自己的路径
default temporary tablespace TEMP tempfile
'/u01/app/oracle/oradata/UMCISC/temp01.dbf' size 20m reuse autoextend on next 640k maxsize unlimited #替换成自己的路径
undo tablespace UNDOTBS1 datafile
'/u01/app/oracle/oradata/UMCISC/undo01.dbf' size 20m reuse autoextend on next 5M maxsize unlimited #替换成自己的路径
logfile
GROUP 1 ('/u01/app/oracle/oradata/UMCISC/redo1.dbf') size 10m, #替换成自己的路径
GROUP 2 ('/u01/app/oracle/oradata/UMCISC/redo2.dbf') size 10m, #替换成自己的路径
GROUP 3 ('/u01/app/oracle/oradata/UMCISC/redo3.dbf') size 10m #替换成自己的路径
CHARACTER SET ZHS16GBK
NATIONAL CHARACTER SET AL16UTF16
;
执行建库和数据字典脚本
#检查当前SID是否为自己创建的
echo $ORACLE_SID
UMCISC
#连接数据库
sqlplus / as sysdba
#执行这一句可能会提示错误,具体看问题列表
SQL> startup nomount
SQL> @$ORACLE_BASE/oradata/UMCISC/createdb.sql
SQL> @?/rdbms/admin/catalog.sql;
SQL> @?/rdbms/admin/catproc.sql;
SQL> @?/rdbms/admin/catexp.sql;
修改监听配置文件listener.ora
cd $ORACLE_HOME/network/admin
#增加以下内容
[oracle@node1 admin]$ vi listener.ora
UMCISC =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1522))
)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.58.200.35)(PORT = 1522))
)
)
)
SID_LIST_UMCISC =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = UMCISC)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
(SID_NAME = UMCISC)
)
)
LISTENER_FATPASE =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.58.200.35)(PORT = 1522))
启动监听
#这里要指明启动哪个数据库实例的监听
lsnrctl start UMCISC
数据库原始密码
用户名:sys 密码:change_on_install
用户名:system 密码:manager
用户名:scott 密码:tiger
修改密码,已经连接数据库
SQL> alter user sys identified by admin as sysdba;
创建表空间
CREATE TABLESPACE 用户名 DATAFILE '/data/DBAS/用户名_01.dbf' Size 256M autoextend on next 128M maxsize 10240M NOLOGGING ONLINE PERMANENT BLOCKSIZE 16384 EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT Auto;
创建用户及授权
CREATE USER 用户名 IDENTIFIED BY 密码 DEFAULT TABLESPACE 用户名 TEMPORARY TABLESPACE TEMP PROFILE DEFAULT;
BEGIN
EXECUTE IMMEDIATE 'GRANT SELECT ANY DICTIONARY TO 用户名';
EXECUTE IMMEDIATE 'GRANT ALTER ANY TABLE TO 用户名';
EXECUTE IMMEDIATE 'GRANT ALTER SYSTEM TO 用户名';
EXECUTE IMMEDIATE 'GRANT ALTER ANY PROCEDURE TO 用户名';
EXECUTE IMMEDIATE 'GRANT CONNECT TO 用户名';
EXECUTE IMMEDIATE 'GRANT CREATE ANY PROCEDURE TO 用户名';
EXECUTE IMMEDIATE 'GRANT CREATE ANY TABLE TO 用户名';
EXECUTE IMMEDIATE 'GRANT CREATE ANY INDEX TO 用户名';
EXECUTE IMMEDIATE 'GRANT CREATE DATABASE LINK TO 用户名';
EXECUTE IMMEDIATE 'GRANT CREATE PUBLIC DATABASE LINK TO 用户名';
EXECUTE IMMEDIATE 'GRANT CREATE ANY VIEW TO 用户名';
EXECUTE IMMEDIATE 'GRANT CREATE PUBLIC SYNONYM TO 用户名';
EXECUTE IMMEDIATE 'GRANT CREATE TABLE TO 用户名';
EXECUTE IMMEDIATE 'GRANT DELETE ANY TABLE TO 用户名';
EXECUTE IMMEDIATE 'GRANT DROP PUBLIC DATABASE LINK TO 用户名';
EXECUTE IMMEDIATE 'GRANT DROP ANY TRIGGER TO 用户名';
EXECUTE IMMEDIATE 'GRANT DROP ANY INDEX TO 用户名';
EXECUTE IMMEDIATE 'GRANT DROP ANY PROCEDURE TO 用户名';
EXECUTE IMMEDIATE 'GRANT DROP ANY TABLE TO 用户名';
EXECUTE IMMEDIATE 'GRANT DROP ANY VIEW TO 用户名';
EXECUTE IMMEDIATE 'GRANT CREATE PUBLIC SYNONYM TO 用户名';
EXECUTE IMMEDIATE 'GRANT DROP PUBLIC SYNONYM TO 用户名';
EXECUTE IMMEDIATE 'GRANT CREATE ANY SEQUENCE TO 用户名';
EXECUTE IMMEDIATE 'GRANT EXECUTE ANY PROCEDURE TO 用户名';
EXECUTE IMMEDIATE 'GRANT GRANT ANY PRIVILEGE TO 用户名';
EXECUTE IMMEDIATE 'GRANT GRANT ANY ROLE TO 用户名';
EXECUTE IMMEDIATE 'GRANT CREATE ANY TRIGGER TO 用户名';
EXECUTE IMMEDIATE 'GRANT INSERT ANY TABLE TO 用户名';
EXECUTE IMMEDIATE 'GRANT SELECT ANY DICTIONARY TO 用户名';
EXECUTE IMMEDIATE 'GRANT SELECT ANY SEQUENCE TO 用户名';
EXECUTE IMMEDIATE 'GRANT DROP ANY SEQUENCE TO 用户名';
EXECUTE IMMEDIATE 'GRANT SELECT ANY TABLE TO 用户名';
EXECUTE IMMEDIATE 'GRANT UNLIMITED TABLESPACE TO 用户名';
EXECUTE IMMEDIATE 'GRANT UPDATE ANY TABLE TO 用户名';
EXECUTE IMMEDIATE 'GRANT COMMENT ANY TABLE TO 用户名';
EXECUTE IMMEDIATE 'GRANT CREATE ANY TYPE TO 用户名';
EXECUTE IMMEDIATE 'GRANT CREATE TABLESPACE TO 用户名';
EXECUTE IMMEDIATE 'GRANT CREATE USER TO 用户名';
EXECUTE IMMEDIATE 'GRANT EXP_FULL_DATABASE TO 用户名';
EXECUTE IMMEDIATE 'GRANT IMP_FULL_DATABASE TO 用户名';
EXECUTE IMMEDIATE 'GRANT ALTER USER TO 用户名';
EXECUTE IMMEDIATE 'GRANT DEBUG ANY PROCEDURE TO 用户名';
EXECUTE IMMEDIATE 'GRANT DEBUG CONNECT SESSION TO 用户名';
END;
/
Oracle dmp导出、导入
su - oracle
--用sys登录
sqlplus sys/sys@10.51.101.138/NECPBZK as sysdba
--创建文件夹
create or replace directory impdp as '/data/impdp';
--赋权
grant read,write on directory impdp to system;
--退出
exit
--数据泵导入
impdp system/sys@10.xx.xx.xx/ECPTEST directory=impdp dumpfile=bzkumc680_20201211.dmp logfile=bzkumc680_20201211.log REMAP_SCHEMA=NECPUMC680:NECPUMCZC remap_tablespace=NECPUMC680:NECPUMCZC
NECPUMC680这个是源库的用户名
NECPUMCZC这个是目标库的用户名
--数据泵导出
expdp system/sys@10.xx.xx.xx/ECPTEST directory=expdir dumpfile=bzkumc680_20210202.dmp logfile=bzkumc680_20210202.log parallel=4 SCHEMAS=NECPUMC exclude=statistics job_name=yh