数据库结构整理图片

lsnr故障
inctype: complete,incremental,cumulative exp file=f:\ts\1.dmp userid='scott/tiger as sysdba' inctype=incremental
imp scott/tiger@ip:port/sid file='path' full=y exp scott/tiger@ip:port/sid file='path' full=y
5.drop tablespace 表空间名 including contents and datafiles;
F:\oracle\product\10.2.0\db_1\network\ADMIN sqlnet.ora
1.确定为SQLNET.AUTHENTICATION_SERVICES= (NTS),为os认证
select userenv('language') from dual;
select * from nls_instance_parameters; --源于v$parameter
select * from nls_session_parameters;
备份原来数据
导出原来的数据
修改字符集 重新导入
AND l.sid = s.sid AND l.type = 'TM';
找锁
--kill
SID, serial#
kill
ALTER SYSTEM KILL SESSION '19,169';
DML kill
select name from v$datafile;
查看
data file丢失
select name from v$controlfile;
expdp scott/tiger DUMPFILE=scott.dmp DIRECTORY=dmpdir SCHEMAS=scott
1. mkdir f:\expdir
2. sqlplus / as sysdba
3. create or replace directory expdir as 'f:\expdir';
TNS Transparent Network Substrate
F:\oracle\product\10.2.0\db_1\network\ADMIN
目录
tnsnames
listener.ora tnsnames.ora
sqlnet.ora
%oracle_home%\RDBMS\ADMIN\scott.sql
关闭归档
archive log list
查看归档
Oracle (2)[
]
SQL> drop table emp;
drop table emp
* 第 1 行出现错误: ORA-00942: 表或视图不存在
解决方法
sql
DECLARE
vDropSql CHAR(10) := 'DROP TABLE';
cnt
expdp
impdp/expdp
非归档备份 冷备份
alter system switch logfile;
日志切换
归档备份
startup mount
加载不打开
alter database archivelog;
打开归档
alter database open;
打开数据库
操作
热备份
alter database noarchivelog;
alter database character set [ internal_use] zhs16gbk; 9i INTERNAL_CONVERT
alter database character set [internal_convert] ZHS16GBK;
环境变量
NLS_LANG=SIMPLIFIED CHINESE_CHINA.ZHS16GBK
figure
查看字符集
select * from nls_database_parameters; --源于props$ select name, value$ from props$ where name like '%NLS%'; select name, value$ from props$ where name='NLS_CHARACTERSET';
小常识
oracle SQLNET.AUTHENTICATION_SERVICES=（NONE）
方法一.去除密码法
? 1.sqlplus / as sysdba
一定要有OS的管理权限,只能本机
方法二.偷梁换柱法
2.select password from dba_users where username='SYS';(用户名一定要用大写) 3.ALTER USER username IDENTIFIED BY "passwd"; 4.alter user sys identified by values '1F8E5A929B6861AC';(改回原有密码) 1.把F:\oracle\product\10.2.0\db_1\database下PWDsid.ora文件,复制备份一下,删除PWDsid.ora文件 2. orapwd file=F:\oracle\product\10.2.0\db_1\database\pwdorcl.ora password="test"
查看
alter databa源自文库e backup controlfile to trace;
备份
show parameter user_dump
备份目录
control file丢失
select member from v$logfile; select * from v$log;
查看
log file丢失
1.sqlplus / as sysdba 2.alter database open; 3.alter database datafile 'F:\TS\1.DBF' offline drop; 4.alter database open;
alter database open
* 第 1 行出现错误: ORA-01157: 无法标识/锁定数据文件 6 - 请参阅 DBWR 跟踪文件 ORA-01110: 数据文件 6: 'F:\TS\1.DBF'
server 端 client端
shutdown immediate
startup mount exclusive
alter system enable restricted session;
alter system set job_queue_processes=0;
alter database open; 10g internal_use
UNDO_SQL OPERATION is set to ‘UNKNOWN’
,
11g
alter database add supplemental log data;
-SELECT s.serial#, l.*, o.object_name
FROM sys.all_objects o, v$lock l, v$session s WHERE o.object_id = l.id1
scott
SELECT * FROM user_recyclebin;
purge recyclebin;
SELECT a.object_name FROM sys.user_recyclebin a WHERE a.original_name = 'T_EXAM'; flashback table "BIN$xzctmpFfSmq66qjxUCQa0w==$0" to before drop rename to 原对象名;
SELECT b.undo_sql FROM flashback_transaction_query b
WHERE b.logon_user = 'SCOTT' AND b.table_name = 'T_TEST' AND b.start_timestamp BETWEEN to_date('2009-3-23 10:13:45', 'yyyy-mm-dd hh24:mi:ss') AND to_date('2009-3-23 10:17:45', 'yyyy-mm-dd hh24:mi:ss');
ORA-39087: 目录名 EXPDIR 无效
4.grant dba to scott
grant create any directory to scott;
一般步骤
version
5. expdp scott/tiger@localhost:1521/orcl directory=expdir version=10.2.0.1.0 dumpfile='emp.dmp' logfile=20110205.dmp.log
imp/exp
oracle_backup.bat定时备份
使用脚本做备份工具
DIRECTORY,就是 F:\ORACLE\PRODUCT\10.2.0\ADMIN\ORCL\DPDUMP impdp scott/tiger DIRECTORY=dmpdir DUMPFILE=scott.dmp
impdp
INT := 0;
BEGIN
SELECT COUNT(*) INTO cnt FROM tab a WHERE a.tname = 'EMP';
IF cnt > 0 THEN
EXECUTE IMMEDIATE vDropSql || ' ' || 'EMP';
END IF;
END;
ERROR: ORA-01033: ORACLE initialization or shutdown in progress
net start oracleservice< sid> win
net stop oracleservice< sid>
dbstart dbshut
linux
service
lsnrctl start/stop
lsnr
net manager
win
netmgr
linux
dbca netca
netmgr