文章目录

1. Oracle数据库安装配置1.1. 安装准备1.2. 修改内核参数1.3. 创建安装用户和目录1.4. 修改配置文件1.5. 开始安装1.5.1. 上传安装包1.5.2. 静默安装(命令行安装)1.5.3. 日志查询1.5.4. 防火墙 1.6. 设置监听1.7. 新建数据库1.8. 数据库的启动和关闭1.9. sqlplus操作1.10. Linux操作 2. 创建表空间2.1. 创建表空间2.2. 查询验证2.3. 数据库导入导出 3. 错误3.1. ORA-280403.2 密码错误

1. Oracle数据库安装配置

1.1. 安装准备

# root用户su - root# 检查rpm -q binutils compat-libcap1 compat-libstdc++-33 compat-libstdc++-33*.i686 glibc glibc*.i686 glibc-devel glibc-devel*.i686 ksh libaio libaio*.i686 libaio-devel libaio-devel*.i686 libX11 libX11*.i686 libXau libXau*.i686 libXi libXi*.i686 libXtst libXtst*.i686 libgcc libgcc*.i686 libstdc++ libstdc++*.i686 libstdc++-devel libstdc++-devel*.i686 libxcb libxcb*.i686 make nfs-utils net-tools smartmontools sysstat unixODBC unixODBC-devel gcc gcc-c++ libXext libXext*.i686 zlib-devel zlib-devel*.i686 unzip# 安装必要包yum install -y compat-libcap1 compat-libstdc++-33 compat-libstdc++-33*.i686yum install -y glibc*.i686 glibc-devel glibc-devel*.i686yum install -y ksh libaio*.i686 libaio-devel libaio-devel*.i686yum install -y libX11 libX11*.i686 libXau libXau*.i686 libXi libXi*.i686yum install -y libXtst libXtst*.i686 libgcc*.i686 libstdc++*.i686 libstdc++-devel libstdc++-devel*.i686yum install -y libxcb libxcb*.i686 make nfs-utils net-tools smartmontools sysstat unixODBC unixODBC-develyum install -y gcc gcc-c++ libXext libXext*.i686 zlib-devel zlib-devel*.i686 unzip

1.2. 修改内核参数

# 将以下内容添加到/etc/sysctl.conf中。vim /etc/sysctl.conf============================================================# Oracle Installfs.aio-max-nr = 1048576fs.file-max = 6815744kernel.shmall = 2097152kernel.shmmax = 8329226240kernel.shmmni = 4096kernel.sem = 250 32000 100 128net.ipv4.ip_local_port_range = 9000 65500net.core.rmem_default = 262144net.core.rmem_max = 4194304net.core.wmem_default = 262144net.core.wmem_max = 1048586============================================================# 配置生效sysctl -psysctl -a

1.3. 创建安装用户和目录

# 为Oracle创建一个用户组，名字是Oracle，并对该用户设置登录密码。groupadd oinstallgroupadd dbagroupadd operuseradd -g oinstall -G dba,oper oracle# 修改用户密码echo "123" | passwd --stdin oracle# 建立安装目录mkdir /u01# oracle数据库安装目录mkdir -p /u01/app/oracle# oracle数据库配置文件目录mkdir -p /u01/app/oraInventory# 存放数据库的数据目录mkdir -p /u01/app/oracle/oradata# 存放数据库备份文件mkdir -p /u01/app/oracle/oradata_back# 修改权限chmod -R 775 /u01/appchown -R oracle:oinstall /u01

1.4. 修改配置文件

# 配置 /etc/profilevim /etc/profile============================================================# Oracle Installif [ $USER = "oracle" ]; thenif [ $SHELL = "/bin/ksh" ]; thenulimit -p 16384ulimit -n 65536aelseulimit -u 16384 -n 65536fifi============================================================

# 配置 /home/oracle/.bash_profilevim /home/oracle/.bash_profile============================================================# Oracle InstallORACLE_BASE=/u01/app/oracleORACLE_HOME=$ORACLE_BASE/product/12.2.0/db_1ORACLE_SID=orclPATH=$PATH:$HOME/bin:$ORACLE_HOME/binLD_LIBRARY_PATH=$ORACLE_HOME/lib:/usr/libexport ORACLE_BASE ORACLE_HOME ORACLE_SID PATH LD_LIBRARY_PATH============================================================# 使配置生效source /home/oracle/.bash_profile

# 配置 /etc/security/limits.confvim /etc/security/limits.conf============================================================# Oracle Installoracle soft nproc 2047oracle hard nproc 16384oracle soft nofile 1024oracle hard nofile 65536oracle soft stack 10240============================================================

1.5. 开始安装

1.5.1. 上传安装包

# 下载cd /u01# 把下载好的安装包放到/u01目录下，并解压。unzip linuxx64_12201_database.zip

1.5.2. 静默安装(命令行安装)

# 修改应答文件vim /u01/database/response/db_install.rsp============================================================# 21行oracle.install.responseFileVersion=/oracle/install/rspfmt_dbinstall_response_schema_v12.2.0# 30行 安装类型,只装数据库软件oracle.install.option=INSTALL_DB_SWONLY# 35行 用户组UNIX_GROUP_NAME=oinstall# 42行 INVENTORY目录（不填就是默认值）INVENTORY_LOCATION=/u01/app/oraInventory# 46行 oracle目录ORACLE_HOME=/u01/app/oracle/product/12.2.0/db_1# 51行 oracle基本目录ORACLE_BASE=/u01/app/oracle# 63行 oracle版本oracle.install.db.InstallEdition=EE# 80行oracle.install.db.OSDBA_GROUP=dba# 86行oracle.install.db.OSOPER_GROUP=oper# 91行 oracle.install.db.OSBACKUPDBA_GROUP=dba# 96行oracle.install.db.OSDGDBA_GROUP=dba# 101行oracle.install.db.OSKMDBA_GROUP=dba# 106行oracle.install.db.OSRACDBA_GROUP=dba# 180行 数据库类型oracle.install.db.config.starterdb.type=GENERAL_PURPOSE# 185行oracle.install.db.config.starterdb.globalDBName=orcl# 190行oracle.install.db.config.starterdb.SID=orcl# 216行oracle.install.db.config.starterdb.characterSet=AL32UTF8# 386行SECURITY_UPDATES_VIA_MYORACLESUPPORT=false# 398行 设置安全更新（貌似是有bug，这个一定要选true，否则会无限提醒邮件地址有问题，终止安装。PS：不管地址对不对）DECLINE_SECURITY_UPDATES=true============================================================

# 安装Oracle数据库软件su - oraclecd /u01/database./runInstaller -force -silent -noconfig -ignorePrereq -ignoreSysPreReqs -responseFile /u01/database/response/db_install.rsp

1.5.3. 日志查询

============================================================[oracle@oracle database]$ ./runInstaller -force -silent -noconfig -ignorePrereq -ignoreSysPreReqs -responseFile /u01/database/response/db_install.rspStarting Oracle Universal Installer...Checking Temp space: must be greater than 500 MB. Actual 23862 MB PassedChecking swap space: must be greater than 150 MB. Actual 4095 MB PassedPreparing to launch Oracle Universal Installer from /tmp/OraInstall-11-12_10-55-47AM. Please wait ...[oracle@oracle database]$ You can find the log of this install session at:/u01/app/oraInventory/logs/installActions-11-12_10-55-47AM.log============================================================# 出现上面提示以后，可查询日志：tail –300f /u01/app/oraInventory/logs/installActions-11-12_10-55-47AM.log

============================================================As a root user, execute the following script(s):1. /u01/app/oraInventory/orainstRoot.sh2. /u01/app/oracle/product/12.2.0/db_1/root.shINFO: Cleaning up, please wait...Successfully Setup Software.============================================================# 出现上述提示，表示数据库安装成功，按照提示完成安装。# 切换rootsu - root/u01/app/oraInventory/orainstRoot.sh============================================================Changing permissions of /u01/app/oraInventory.Adding read,write permissions for group.Removing read,write,execute permissions for world.Changing groupname of /u01/app/oraInventory to oinstall.The execution of the script is complete.============================================================/u01/app/oracle/product/12.2.0/db_1/root.sh============================================================Check /u01/app/oracle/product/12.2.0/db_1/install/root_oracle_-11-12_11-07-22-742906487.log for the output of root script============================================================

1.5.4. 防火墙

su - rootfirewall-cmd --zone=public --add-port=1521/tcp --permanentfirewall-cmd --reload

1.6. 设置监听

su - oraclenetca -silent -responsefile /u01/database/response/netca.rsp============================================================Parsing command line arguments:Parameter "silent" = trueParameter "responsefile" = /u01/database/response/netca.rspDone parsing command line arguments.Oracle Net Services Configuration:Profile configuration complete.Oracle Net Listener Startup:Running Listener Control: /u01/app/oracle/product/12.2.0/db_1/bin/lsnrctl start LISTENERListener Control complete.Listener started successfully.Listener configuration complete.Oracle Net Services configuration successful. The exit code is 0[oracle@oracle ~]$ netstat -tlnp(Not all processes could be identified, non-owned process infowill not be shown, you would have to be root to see it all.)Active Internet connections (only servers)============================================================# 可以通过 netstat -tlnp 命令查看监听地址netstat -tlnp============================================================Proto Recv-Q Send-Q Local Address Foreign Address State PID/Program name tcp 00 127.0.0.1:631 0.0.0.0:*LISTEN- tcp 00 0.0.0.0:111 0.0.0.0:*LISTEN- tcp 00 192.168.122.1:53 0.0.0.0:*LISTEN- tcp 00 0.0.0.0:22 0.0.0.0:*LISTEN- tcp6 00 ::1:631 :::*LISTEN- tcp6 00 :::111 :::*LISTEN- tcp6 00 :::1521 :::*LISTEN98652/tnslsnr tcp6 00 :::22 :::*LISTEN- ============================================================

1.7. 新建数据库

su - root# 修改dbca.rsp文件，静默安装配置文件路径：/u01/database/response/dbca.rspvim /u01/database/response/dbca.rsp============================================================# 21行 不可更改responseFileVersion=/oracle/assistants/rspfmt_dbca_response_schema_v12.2.0# 32行 全局数据库名gdbName=orcl# 42行 系统标识符sid=orcl# 52行databaseConfigType=SI# 74行policyManaged=false# 88行createServerPool=false# 127行force=false# 163行 createAsContainerDatabase设为true表示创建容器数据库；false为普通数据库createAsContainerDatabase=false# 172行numberOfPDBs=1# 182行pdbName=orclpdb# 192行useLocalUndoForPDBs=true# 203行 库密码pdbAdminPassword=123456# 223行templateName=/u01/app/oracle/product/12.2.0/db_1/assistants/dbca/templates/General_Purpose.dbc# 233行 超级管理员密码sysPassword=123456# 233行 管理员密码systemPassword=123456# 273行emExpressPort=5500# 284行runCVUChecks=false# 313行omsPort=0# 341行dvConfiguration=false# 391行olsConfiguration=false# 401行datafileJarLocation={ORACLE_HOME}/assistants/dbca/templates/# 411行datafileDestination={ORACLE_BASE}/oradata/{DB_UNIQUE_NAME}/# 421行recoveryAreaDestination={ORACLE_BASE}/fast_recovery_area/{DB_UNIQUE_NAME}# 431行storageType=FS# 468行 字符集创建库之后不可更改characterSet=AL32UTF8# 478行nationalCharacterSet=AL16UTF16# 488行registerWithDirService=false# 526行listeners=LISTENER# 546行variables=DB_UNIQUE_NAME=orcl,ORACLE_BASE=/u01/app/oracle,PDB_NAME=,DB_NAME=orcl,ORACLE_HOME=/u01/app/oracle/product/12.2.0.1/db_1,SID=orcl# 555行initParams=undo_tablespace=UNDOTBS1,memory_target=796MB,processes=300,db_recovery_file_dest_size=2780MB,nls_language=AMERICAN,dispatchers=(PROTOCOL=TCP) (SERVICE=orclXDB),db_recovery_file_dest={ORACLE_BASE}/fast_recovery_area/{DB_UNIQUE_NAME},db_block_size=8192BYTES,diagnostic_dest={ORACLE_BASE},audit_file_dest={ORACLE_BASE}/admin/{DB_UNIQUE_NAME}/adump,nls_territory=AMERICA,local_listener=LISTENER_orcl,compatible=12.2.0,control_files=("{ORACLE_BASE}/oradata/{DB_UNIQUE_NAME}/control01.ctl", "{ORACLE_BASE}/fast_recovery_area/{DB_UNIQUE_NAME}/control02.ctl"),db_name=cdb1,audit_trail=db,remote_login_passwordfile=EXCLUSIVE,open_cursors=300# 565行sampleSchema=false# 574行memoryPercentage=40# 584行databaseType=MULTIPURPOSE# 594行automaticMemoryManagement=false# 604行totalMemory=0============================================================

su - oracle# 创建数据库实例dbca -silent -createDatabase -responseFile /u01/database/response/dbca.rsp============================================================Copying database files1% complete33% completeCreating and starting Oracle instance35% complete55% completeCompleting Database Creation56% complete66% completeExecuting Post Configuration Actions100% completeLook at the log file "/u01/app/oracle/cfgtoollogs/dbca/orcl/orcl.log" for further details.============================================================

1.8. 数据库的启动和关闭

# 启动监听器和实例。dbstart /u01/app/oracle/product/12.2.0/db_1/# 关闭此所有oracle的进程，监听器也停止。dbshut /u01/app/oracle/product/12.2.0/db_1/# 上面启动成功可以跳过下面# 启动su - oraclesqlplus / as sysdbastartup;exitlsnrctl startexit# 停止su - oraclesqlplus / as sysdbashutdown abort;exitlsnrctl stopexit

1.9. sqlplus操作

# 本地登录su - oraclesqlplus / as sysdba# 本地登录sqlplus /nologconn user/password

# 以 DBA 身份进入 sqlplus，查看数据库状态su - oraclesqlplus / as sysdba============================================================# 查看数据库select open_mode from v$database; # 查看数据库实例select status from v$instance;alter session set container=ORCLPDB;# 创建用户create user user01 identified by password default tablespace users temporary tablespace temp;grant create user,drop user,alter user,create any view,connect,resource,dba,create session,create any sequence to user01;# 删除用户drop user test;# 撤销授权revoke connect, resource from test;# 改sys超级管理员密码alter user sys identified by password; # 改system管理员密码alter user system identified by password;# 修改密码alter user 用户名 identified by 新密码;alter user sys identified by password;# 启动数据库startup# 关闭数据库shutdown abort# 运行时间select to_char(startup_time,'DD-MON-YYYY HH24:MI:SS') 启动时间,TRUNC(sysdate - (startup_time))||'天 '||TRUNC(24*((sysdate-startup_time) -TRUNC(sysdate-startup_time)))||'小时 '||MOD(TRUNC(1440*((SYSDATE-startup_time)-TRUNC(sysdate-startup_time))),60)||'分 '||MOD(TRUNC(86400*((SYSDATE-STARTUP_TIME)-TRUNC(SYSDATE-startup_time))),60)||'秒' 运行时间from v$instance;select startup_time from sys.v$instance;# 查看日志select * from v$logfile;# 查看最近的操作select * from v$sql;select * from v$sqlarea;# 查询和配置数据库连接数select count(*) from v$processselect value from v$parameter where name = 'processes'alter system set processes = 500 scope = spfile;# 查看密码（没什么卵用）select username,password from dba_users where username='SYS';# 检查用户状态以及锁定时间select username,account_status,lock_date from dba_users where username='user01';select username,lock_date from dba_users where username='user01';# 解锁alter user user01 account unlock;# 查询用户数据量大小select SUM(s.BYTES)/1024/1024 "sizes(MB)" from dba_segments s where s.owner= 'user01';# 查询用户表数量select count(*) from dba_tables t where t.owner='user01';============================================================

1.10. Linux操作

su - oracle# 关闭此所有oracle的进程，监听器也停止。dbshut /u01/app/oracle/product/12.2.0/db_1/# 启动监听器和实例。dbstart /u01/app/oracle/product/12.2.0/db_1/# 查看进程ps -ef | grep ora_ | grep -v grep# 查看监听状态及数据库状态lsnrctl statuslsnrctl startlsnrctl stop

2. 创建表空间

2.1. 创建表空间

注意实际数据的安装位置

CREATE TABLESPACE JTB_TXNLOG_TS DATAFILE '/u01/app/oracle/oradata/orcl/TABLE_SPACE_NAME_01.dbf' SIZE 256M AUTOEXTEND ON NEXT 128M MAXSIZE UNLIMITEDLOGGINGONLINEEXTENT MANAGEMENT LOCAL AUTOALLOCATEBLOCKSIZE 8KSEGMENT SPACE MANAGEMENT AUTOFLASHBACK ON;

2.2. 查询验证

--1、查看表空间的名称及大小 SELECT t.tablespace_name, round(SUM(bytes / (1024 * 1024)), 0) ts_size FROM dba_tablespaces t, dba_data_files d WHERE t.tablespace_name = d.tablespace_name GROUP BY t.tablespace_name;--2、查看表空间物理文件的名称及大小 SELECT tablespace_name, file_id, file_name, round(bytes / (1024 * 1024), 0) total_space FROM dba_data_files ORDER BY tablespace_name;--3、查看回滚段名称及大小 SELECT segment_name, tablespace_name, r.status, (initial_extent / 1024) initialextent, (next_extent / 1024) nextextent, max_extents, v.curext curextent FROM dba_rollback_segs r, v$rollstat v WHERE r.segment_id = v.usn(+) ORDER BY segment_name;--4、查看控制文件 SELECT NAME FROM v$controlfile;--5、查看日志文件 SELECT MEMBER FROM v$logfile;--6、查看表空间的使用情况 SELECT SUM(bytes) / (1024 * 1024) AS free_space, tablespace_name FROM dba_free_space GROUP BY tablespace_name; SELECT a.tablespace_name, a.bytes total, b.bytes used, c.bytes free, (b.bytes * 100) / a.bytes "% USED ", (c.bytes * 100) / a.bytes "% FREE " FROM sys.sm$ts_avail a, sys.sm$ts_used b, sys.sm$ts_free c WHERE a.tablespace_name = b.tablespace_name AND a.tablespace_name = c.tablespace_name;--7、查看数据库库对象 SELECT owner, object_type, status, COUNT(*) count# FROM all_objects GROUP BY owner, object_type, status;--8、查看数据库的版本 SELECT version FROM product_component_version WHERE substr(product, 1, 6) = 'Oracle';--9、查看数据库的创建日期和归档方式 SELECT created, log_mode, log_mode FROM v$database; --1G=1024MB --1M=1024KB --1K=1024Bytes --1M=11048576Bytes --1G=1024*11048576Bytes=11313741824Bytes SELECT a.tablespace_name "表空间名", total "表空间大小", free "表空间剩余大小", (total - free) "表空间使用大小", total / (1024 * 1024 * 1024) "表空间大小(G)", free / (1024 * 1024 * 1024) "表空间剩余大小(G)", (total - free) / (1024 * 1024 * 1024) "表空间使用大小(G)", round((total - free) / total, 4) * 100 "使用率 %" FROM (SELECT tablespace_name, SUM(bytes) free FROM dba_free_space GROUP BY tablespace_name) a, (SELECT tablespace_name, SUM(bytes) total FROM dba_data_files GROUP BY tablespace_name) b WHERE a.tablespace_name = b.tablespace_name

2.3. 数据库导入导出

假设：

源数据库表所在用户/密码：user01/password;目标数据库表所在用户/密码：user02/password;

导出user01

su - oraclemkdir -p db_expdp# sqlplus本地登陆sqlplus / as sysdba# 创建目录(注意斜杠)create directory TMPDIR as '/home/oracle/db_expdp';# 查询目录select * from dba_directories where directory_name='TMPDIR';# 删除目录drop directory TMPDIR;# 赋权grant read ,write on directory TMPDIR to user01;# 退出sqlplusexit# 导出 expdp user01/password@orcl schemas=user01 dumpfile=db_expdp.dmp logfile=db_expdp.log directory=TMPDIR

导入到user02

将dmp文件拷贝到对应的TMPDIR目录下

# 导入(与导出类似，注意不同用户)...grant read ,write on directory TMPDIR to user02;exitimpdp user02/password directory=TMPDIR dumpfile=db_expdp.dmp remap_schema=user01:user02

如果远程连接速度太慢，或则数据量太大，可以考虑压缩数据，在通过其他方式将数据DMP文件拷贝到目标服务器上。

# 压缩，不同格式压缩比率不同tar cf db.tar db_expdptar zcf db.tar.gz db.tartar jcf db.tar.bz2 db.tartar Jcf db.tar.xz db.tar# 解压tar xvf db.tar.xz

3. 错误

3.1. ORA-28040

No matching authentication protocol

解决方法：

vim oracle/network/admin/sqlnet.ora============================================================# 添加SQLNET.ALLOWED_LOGON_VERSION_SERVER=8SQLNET.ALLOWED_LOGON_VERSION_CLIENT=8============================================================

3.2 密码错误

原因很复杂，很可能是版本不同造成的。

尝试重新修改密码