实验室数据库相关的问题总结

实验室服务器数据库相关的问题总结

一．数据库（sys，system之类）忘记密码(windows)：

1.通过cmd打开命令提示符, sqlplus /nolog
2.输入conn /as sysdba
3. 输入alter user 用户名 identified by 新密码;
例如：alter user sys identified by 123456;

二．连接数据库

注意：负责服务端与客户端之间的通信的重要文件-监听（实例名字与IP更改为相同）：
服务端设置：listener.ora
客户端设置：tnsnames.ora
文件所在位置：
Windows（本主机）：D:\Oracle\product\11.2.0\dbhome_1\admin
Linux（实验室服务器）：/data/oracle/product/11.2.0/db_1/network/admin/

1.利用ipconfig或者ifconfig查询服务端IP
利用Oracle自带的net configuration工具或者手动进入目录更改listener.ora与tnsnames.ora的IP（默认localhost）与Port（默认1521）
本实验室服务器内网IP：
192.168.1.230/192.168.1.231
SERVICE_NAME:ais_data/orcl

2.服务端下开启oracle服务和开启监听
（1）.Windows端
主要检查服务是否开启,若未开启，则cmd执行

net start OracleServiceORCL
net start  OracleOraDb10g_home2TNSListener
net start OracleOraDb10g_home2iSQL*Plus

以上方式是在windows服务中启动服务,当windows服务不能启动数据库实例的时候,应用以下的语句:
set oracle_sid=orcl
oradim -startup -sid orcl
sqlplus internal/oracle
startup

（2）Linux端（每次需要手动开启）
su - oracle // 切换到oracle用户模式下，“-” 不能省略否则报错：-bash:lsnrctl:command not found错误并且所有oracle指令无效

sqlplus /nolog  //登录sqlplus
SQL> connect /as sysdba  //连接oracle
SQL> startup //起动数据库
SQL> exit  //退出sqlplus ，起动监听
cd $ORACLE_HOME/bin //进入oracle安装目录
lsnrctl start  //起动监听

（3）下面是导入数据时Linux服务端未开启（或者异常关闭与重启是发生）
问题:

 ORA-12514: TNS: 监听程序当前无法识别连接描述符中请求的服务
the listener support no service
IMP-00058: 遇到 ORACLE 错误 1034
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
Linux-x86_64 Error: 2: No such file or directory
IMP-00005: 所有允许的登录尝试均失败
IMP-00000: 未成功终止导入

方案:
先判断完监听中listener.ora,tnsnames.ora,$ORACLE_HOME环境,还有init.ora文件等都正常时:
以下解决方案:
服务器宿主(我的是Linux)启动oracle:
命令的模式下输入下面三条命令

sqlplus / as sysdba
startup
exit

3.客户端判断是否连接上
tnsping orcl(全局数据库名称) //我这客户端是Windows 所以cmd 执行

问题1:

TNS-12541: TNS: 无监听

思路1：
判断监听中listener.ora,tnsnames.ora 监听IP与port，设置正常后，随机故障现象解除。

问题2:

TNS-12535: TNS: 操作超时

思路2：
先判断完监听中listener.ora,tnsnames.ora,$ORACLE_HOME环境（Linux）,还有init.ora文件等都正常时:
关闭Oracle数据库服务器上的iptables防火墙或开放1521端口(linux)。随机故障现象解除。
防火墙操作的常见命令：

systemctl stop firewalld
systemctl status firewalld
systemctl disable firewalld
systemctl enable firewalld

三．建立新的数据库，并导入数据问题集锦

1.创建表空间

第一,启动服务(如果数据库处于启动状态,那么略过这一步)
参考上文来连接数据库

第二,如果我们在数据库曾经使用过,我们先来清理一下痕迹(注意:xxxx是自定义,复制语句时,注意全部替换为你使用的名字)
(如果数据库是第一次建立,或者需要新建用户来导入数据,请选择性阅读以下内容)
drop user xxxx cascade; //删除用户
drop tablespace xxxx;//删除表空间
e:/xxxx.dbf //删除数据库文件

第三,接下来,准备工作做好后,我们就可以开始了

//创建用户
CREATE USER AIS_DATA IDENTIFIED BY 123456 DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP
//给予用户权限
grant connect,resource,dba to xxxx
//创建表空间,并指定文件名,和大小
//执行给予权限的脚本,将权限给予刚才创建的用户
//给予权限
GRANT CREATE USER,DROP USER,ALTER USER ,CREATE ANY VIEW ,
DROP ANY VIEW,EXP_FULL_DATABASE,IMP_FULL_DATABASE,
DBA,CONNECT,RESOURCE,CREATE SESSION TO xxxx
//开始导入(完全导入),file:dmp文件所在的位置, ignore:因为有的表已经存在，对该表就不进行导入。在后面加上 ignore=y 。buffer是指数据行的缓冲区大小，默认值根据系统而定，通常应设置为高值,exp的buffer最好>64000，imp的buffer最好>100000,,指定log文件位置自己定义。Index为索引,此处设置no。log=D:/log.txt
imp user/pass@orcl full=y file=e:/xxx.dmp buffer=9999999 ignore=y full=y indexes=n log=D:/log.txt
//当我们不需要完整的还原数据库的时候,我们可以单独地还原某个特定的表
imp user/pass@datbase file=e:/xxx.dmp ignore=y log=e:/log.txt tables=(xxxx)
imp user/pass@database file=e:/xxx.dmp ignore=y log=e:/log2.txt tables=(xxxx)
//---------------------------------------------------------------------
//做到这里我们就已经完成了,数据库的还原工作,下面我们就可以打开sqlplus查看表中的数据了
select * from **

第四,我们来看一下,对oracle常用的操作命令
1)查看表空间的属性
select tablespace_name,extent_management,allocation_type from dba_tablespaces
2)查找一个表的列,及这一列的列名,数据类型
select TABLE_NAME,COLUMN_NAME,DATA_TYPE from user_tab_columns where TABLE_NAME='xxxx'
3)查找表空间中的用户表
select * from all_tables where owner='xxx' order by table_name desc
4)在指定用户下,的表的数量
select count(*) from user_tab_columns
5)查看数据库中的表名,表列,所有列
select TABLE_NAME,COLUMN_NAME,DATA_TYPE from user_tab_columns order by table_name desc
6)查看用户ZBFC的所有的表名及表存放的表空间
select table_name,tablespace_name from all_tables where owner='xxxx' order by table_name desc
7)生成删除表的文本
select 'Drop table '||table_name||';' from all_tables where owner="ZBFC";
8)删除表级联删除
drop table table_name [cascade constraints];
9)查找表中的列
select TABLE_NAME,COLUMN_NAME,DATA_TYPE from user_tab_columns where column_name like '%'||'地'||'%' order by table_name
desc
10)查看数据库的临时空间
select tablespace_name,EXTENT_SIZE,current_users,total_extents,used_extents,MAX_SIZE,free_extents from v$sort_segment;

2.导入方式

（1）cmd导入
imp 用户名/密码@监听器路径/数据库实例名称 file='d:\数据库文件.dmp' full=y ignore=y
例如：imp system/123456@ORCL file=E:\2015\201501\aisdynamiclog20150113.dmp buffer=9999999 ignore=y full=y indexes=n

（2）使用Oracle的bin目录imp.exe导入
打开Oracle主目录
D:\Oracle\product\11.2.0\dbhome_1\bin
找到impdb.exe 进行导入
使用管理员身份运行。输入密码，输入密码再输入dmp 的路径，后边会出现什么 yes 什么 no的看情况输入回车就可以了。

(3)使用pl/sql软件中的导入表功能导入，原则上是与2相同，都是调用imp.exe文件实现文件的导入
注：在导入过程如果出现：
IMP-00032: SQL 语句超过缓冲区长度
IMP-00008: 导出文件中出现无法识别的语句
环境为9.2.0.8，导出过程正常没有报错，解决办法：
imp 命令行参数加入 buffer=819200 （缺省貌似4k）
问题即得到解决。
顺手把其他参数也记录一下：
buffer 仅仅对常规路径导出有效，对直接路径导出没有效。
INDEXES=N 不创建索引，以加快速度（对于主键需要先手工禁用）
INDEXFILE可生成创建索引的DLL脚本，可用于导入后的手工创建
也可以执行两次imp实现数据的导入和索引的创建
rows=y indexes=n
rows=n indexes=y

3.字符集不匹配问题

解决办法：修改注册
打开注册表,‘开始’-‘运行’ 输入‘regedit’-确定。
找到提示中给出的路径，
找到 NLS_LANG 键，他的值原来是：
SIMPLIFIED CHINESE_CHINA.ZHS16GBK
修改为：
SIMPLIFIED CHINESE_CHINA.AL32UTF8
重新打开plsql ，登录，好了。

其他
为了防止临时表空间无限制的增加，我采用隔一段时间就重建临时表空间的方法，为了方便，我保留两组语句，轮流执行即可，假定现在临时表空间名称是temp，新建一个tempa表空间，删除temp表空间，方法如下：

Create temporary tablespace TEMP TEMPFILE '/opt/app/oracle/oradata/orcl/temp01.dbf ' SIZE 8192M REUSE AUTOEXTEND ON NEXT 1024K MAXSIZE UNLIMITED;
--创建中转临时表空间
alter database default temporary tablespace temp;
--改变缺省临时表空间
drop tablespace tempa including contents and datafiles;
--删除原来临时表空间
这样就可以保证临时表空间不至于过大，防止过多的占用有限的硬盘空间。

=====================================================
用下面语句可查看当前临时表空间使用空间大小与正在占用临时表空间的sql语句：
select sess.SID, segtype, blocks * 8 / 1000 "MB", sql_text
from v$sort_usage sort, v$session sess, v$sql sql
where sort.SESSION_ADDR = sess.SADDR
and sql.ADDRESS = sess.SQL_ADDRESS
order by blocks desc;

下面语句查询临时表空间的空闲程度：
select 'the ' || name || ' temp tablespaces ' || tablespace_name ||
' idle ' ||
round(100 - (s.tot_used_blocks / s.total_blocks) * 100, 3) ||
'% at ' || to_char(sysdate, 'yyyymmddhh24miss')
from (select d.tablespace_name tablespace_name,
nvl(sum(used_blocks), 0) tot_used_blocks,
sum(blocks) total_blocks
from v$sort_segment v, dba_temp_files d where d.tablespace_name = v.tablespace_name(+) group by d.tablespace_name) s, v$database;

合并分区
ALTER TABLE AISDYNAMICLOG
MERGE PARTITIONS AISDYNAMICLOG20120101,AISDYNAMICLOG20120102 INTO PARTITION AISDYNAMICLOG201201
UPDATE INDEXES;

--拆分分区(split partitions)
Range partition:
Alter table xxx split partition/subpartition p1 at (15) into (partition/SUBPARTITION p1_new1,partition/subpartition p1_new2);
List partition:
Alter table xxx split partition/subpartition p1 values(15,16) into (partition/subpartition p1_new1,partition/subpartition p1_new2);

--原分区中符合新值定义的记录会存入第一个分区,其他存入第二个分区,当然,在新分区后面可以指定属性,比如TABLESPACE。

--ALTER TABLE LC_CP.O_SO_T SPLIT PARTITION P_MAX AT (TO_DATE('2012-02-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')) INTO (PARTITION P1201,PARTITION P_MAX);
ALTER TABLE LC_CP.O_SO_T SPLIT PARTITION P_MAX AT (TO_DATE('2013-02-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')) INTO (PARTITION P1301,PARTITION P_MAX);

分区查询
select * from AISDYNAMICLOG partition(AISDYNAMICLOG20150902)

新建分区
ALTER TABLE AISDYNAMICLOG ADD PARTITION AISDYNAMICLOG201509 VALUES LESS THAN(1443628800) TABLESPACE vms;

plsql 登录后提示：
Database character set (AL32UTF8) and Client character set (ZHS16GBK) are different.
Character set conversion may cause unexpected results.
Note: you can set the client character set through the NLS_LANG environment variable or the NLS_LANG registry key in
HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\KEY_OraClient11g_home2.
解决办法：修改注册
打开注册表,‘开始’-‘运行’ 输入‘regedit’-确定。
找到提示中给出的路径，
找到 NLS_LANG 键，他的值原来是：
SIMPLIFIED CHINESE_CHINA.ZHS16GBK
修改为：
SIMPLIFIED CHINESE_CHINA.AL32UTF8
重新打开plsql ，登录，好了。

posted @ 2020-07-22 10:44 我是如子啊阅读(259) 评论(0) 收藏举报

刷新页面返回顶部

我是如子啊

混迹互联网的新晋菜鸟