Chinaunix首页 | 论坛 | 博客
  • 博客访问: 105352
  • 博文数量: 41
  • 博客积分: 0
  • 博客等级: 民兵
  • 技术积分: 0
  • 用 户 组: 普通用户
  • 注册时间: 2018-05-23 12:43
文章分类

全部博文(41)

文章存档

2016年(2)

2015年(3)

2014年(16)

2013年(20)

分类: Oracle

2013-11-23 20:52:03

第0章 虚拟环境搭建
alter user identified by tiger account unlock
connect sys/pasword as sysdba


col name for a20
select * from v$controlfile;


shutdown immediate


host copy D:\app\Administrator\oradata\orcl\*.* E:\Backup


host copy D:\app\Administrator\product\11.2.0\dbhome_1\database\*.*  D:\Backup\dabse;


第1章 oracle体系结构
alter system set share_pool_size=32M;


select id ,name,block_size,advice_status from v$db_cache_advice;
--动态修改db_buffers_cache(数据库高速缓冲区=db_block_size * db_buffers_size)大小
alter system set db_cache_size = 48M;
--监控db_buffers_cache(数据库高速缓冲区)
select id,name,block_size,advice_status from v$db_cache_advice;
alter system set db_cache_advice = off;


show parameter
show sga


col value for a25


select name,type,value from v$parameter where name='sga_max_size';
select name,type,value from v$parameter where name='db_block_size';   
select name,type,value from v$parameter where name='db_buffers_size';
select name,type,value from v$parameter where name='db_cache_size';


第二章 数据库管理工具
sqlplus/nolog


第三章 Oracle实例的管理
 create spfile from pfile;
 create pfile='mypfile' from spfile;
 
 startup [pfile=''] open | mount | nomount;
 alter database open |read only|read write;
 startup restrict;
 
 alter system enable restricted session ;
 select user#,username, sid,serial# from v$session;
 select user#,sid,serial#,username from v$session where username='SCOTT';
 alter system kill session '73.922';
 
 /***诊断文件**/
 col name for a23
 col value for a33
 set line 100
 select name , value from v$parameter where name like '%_dest';
 --1.报警文件(alter语句,表空间创建等):background__dump_dest
 alter tablespace users read only;
 alter tablespace users read write;
 --2.后台进程追踪文件记录(进程错误信息)background__dump_dest
 --3.用户进程追踪文件(用户进程遇到用户会话错误时创建):user_dump_dest
   --会话级开启用户(进程)追踪
   alter session set sql_trace = true;
   alter session set sql_trace = false;
   --实例级别开启(尽量避免)
    初始化参数文件中sql_trace = trace
   
第四章 数据字典和控制文件
/**数据字典***/
------     ------   -------
user_*      all_*   dba_*


*_tables  *_index   *_objects
------    -------   ------
col owner for a8
col object_name for a12
col object_type for a10
select owner,object_name,object_id,created,status,object_type from all_objects where owner='SCOTT';
--查询可使用的表
select table_name,owner from all_tables where owner not like '%sys';
select * from user_catalog(cat);
select * from dictionary where table_name like '%table%';
select * from v$fixed_table;
select table_name,column_name,comments from dict_columns where table_name='DBA_TABLES' AND COLUMN_NAME='INITIAL_EXTENT'; 


=====宝儿第一天上班============================
select name,created,log_mode,open_mode from v$database;


col host_name for a10;
select host_name,instance_name,version from v$instance;


select * from v$version;


col name for a25
select * from v$controlfile;


select group#,members,bytes,status,archived from v$log;
col  member for a15
select * from v$logfile;


col tablespace_name for a12
select tablespace_name,block_size,status,contents,logging from dba_tablespaces;


col file_name for a40
select file_id,file_name,tablespace_name,status,bytes from dba_data_files;


select username,created from dba_users;
=================================


/**控制文件**/
set pagesize 30
select type,record_size,records_total,records_used from v$controlfile_record_section;


--查看控制文件的位置
select value from v$parameter where name='control_files';
col name for a15
select * from v$controlfile;




alter system set control_files = 
'E:\Disk1\CONTROL01.CTL',
'E:\Disk2\CONTROL02.CTL' SCOPE=SPFILE;


--备份
alter database backup controlfile to 'E:\Backup\control.bak';
--将创建控制文件的命令备份到一个追踪文件中
alter database backup controlfile to trace;


=======================================
/*移动控制文件*/
0.show parameter pfile
1.
select value from v$parameter where name='control_files';
col name for a15
select * from v$controlfile;


NAME
----------------------------------------
C:\ORACLE\PRODUCT\10.2.0\ORADATA\MOON\CO
NTROL01.CTL


C:\ORACLE\PRODUCT\10.2.0\ORADATA\MOON\CO
NTROL02.CTL


C:\ORACLE\PRODUCT\10.2.0\ORADATA\MOON\CO
NTROL03.CTL
2.
alter system set control_files = 
'C:\Disk3\CONTROL01.CTL',
'C:\Disk6\CONTROL02.CTL',
'C:\Disk9\CONTROL03.CTL' SCOPE=SPFILE;
3.
shutdown immediate;
4.
host copy  C:\ORACLE\PRODUCT\10.2.0\ORADATA\MOON\CONTROL01.CTL  C:\Disk3\CONTROL01.CTL;
host copy  C:\ORACLE\PRODUCT\10.2.0\ORADATA\MOON\CONTROL02.CTL  C:\Disk6\CONTROL02.CTL;
host copy  C:\ORACLE\PRODUCT\10.2.0\ORADATA\MOON\CONTROL01.CTL  C:\Disk9\CONTROL03.CTL;
5.
startup 
6.检验是否正确
select name from v$controlfile;


/*备份控制文件*/
alter database backup controlfile to 'C:\backup\control.bak';


alter database backup controlfile to trace;
 col name for a23
 col value for a33
select name , value from v$parameter where name like '%_dest';


=======================================


第5章 重做日志文件
ALTER SYSTEM SWITCH LOGFILE;
ALTER SYSTEM CHECKOUT;


select group#,sequence#,members,bytes,status,archived from v$log;
col member for a30
select * from v$logfile;


alter database add logfile
('C:/Disk3/REDO04A','C:/Disk6/REDO04B')
size 15M;--组号自动递增


alter database drop logfile group 4;


--增加重做日志成员
alter database add logfile member
 'C:\Disk3\redo01b.log' to group 1,
 'C:\Disk3\redo02b.log' to group 2,
 'C:\Disk3\redo03b.log' to group 3;
 
--删除重做日记成员
alter database drop logfile member
 'C:\Disk3\redo01b.log' ,
 'C:\Disk3\REDO02B.LOG' ,
 'C:\Disk3\REDO03B.LOG' ;
 
--重新初始化联机重做日志文件
alter database clear logfile group 组号;
alter database clear unarchived logfile group 组号;


--以OMF(Oracle Managered Files)的方式创建一组重做日志
alter system set DB_CREATE_ONLINE_LOG_DEST_1 = 'C:\Disk3';
alter system set DB_CREATE_ONLINE_LOG_DEST_2 = 'C:\Disk6';
alter system set DB_CREATE_ONLINE_LOG_DEST_3 = 'C:\Disk9';
alter database add logfile;
SELECT group#,sequence#,members,bytes,status,archived from V$log;
col member for a50;
set line 120
select * from v$logfile;


--删除以OMF(Oracle Managered Files)的方式创建一组重做日志
alter database drop logfile group 4;


===========================
select group#,sequence#,members,bytes,status,archived from v$log;
col member for a30
select * from v$logfile;


===========================


阅读(1854) | 评论(1) | 转发(1) |
0

上一篇:Oracle日常性能查看

下一篇:ODS浅析

给主人留下些什么吧!~~