oracle dba 学习日志1(controlfile,tablespace)

--controlfile
/*examplae
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "X201006" NORESETLOGS  NOARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\X201006\REDO01.LOG'  SIZE 50M,
  GROUP 2 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\X201006\REDO02.LOG'  SIZE 50M,
  GROUP 3 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\X201006\REDO03.LOG'  SIZE 50M,
  group 4 'e:\redo1.log' size 10m
-- STANDBY LOGFILE
DATAFILE
  'D:\ORACLE\PRODUCT\10.2.0\ORADATA\X201006\SYSTEM01.DBF',
  'D:\ORACLE\PRODUCT\10.2.0\ORADATA\X201006\UNDOTBS01.DBF',
  'D:\ORACLE\PRODUCT\10.2.0\ORADATA\X201006\SYSAUX01.DBF',
  'D:\ORACLE\PRODUCT\10.2.0\ORADATA\X201006\USERS01.DBF',
  'D:\ORACLE\PRODUCT\10.2.0\ORADATA\X201006\EXAMPLE01.DBF',
  'E:\TESTDATA01.DBF'
CHARACTER SET ZHS16GBK;

ALTER TABLESPACE TEMP ADD TEMPFILE 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\X201006\TEMP01.DBF'
     SIZE 20971520  REUSE AUTOEXTEND ON NEXT 655360  MAXSIZE 32767M;
ALTER TABLESPACE TMPTESTSPACE ADD TEMPFILE 'E:\TMPTESTSPACE.DBF'
     SIZE 33554432  REUSE AUTOEXTEND ON NEXT 33554432  MAXSIZE 1024M;
;
*/
--multiplexing control files
/*shut down database,
copy control file to more locations,
change initialization parameters(control_files),
startup*/
select * from v$controlfile;
select * from v$controlfile_record_section;
select * from v$datafile;
select * from v$parameter where name like '%control%';
select * from v$archived_log;
select * from v$backup_redolog;
select * from v$log;
select * from v$database;
select * from v$logfile;--stale:file's contents are incomplete
select * from v$thread;
select * from v$archived_log;
select * from v$archive_processes;

--generate control files to user_dump_desc;
alter database backup controlfile to trace;
/*数据库mount的时候要读取controlfile,before opening the database,
some dynamic performance views can be accessed.*/


/*redo log files=online redo log files(日志文件可以在操作数据库的时候打开和访问),
database 至少有两个日志文件，LGWR循环访问日志文件，一个周期后开始覆盖LOG FILE.
日志文件由日志记录构成（redo record also called redo entry),包括rollback segment.*/
alter system switch logfile;手工日志切换，一般都是自动切换，当上一个日志满了，就自动切换到下一个文件。
--CHECKPOINT
checkpoint 是一个操作：就是将BUFFER CACHE中修改的数据写到硬盘同时更新control file and datafile.
触发条件：redolog file 达到90%（开始初始化);一些参数改变的时候；自己强制修改
alter system checkpoint;log_checkpoint_interval;log_checkpoint_timeout;fast_start_to_target;
alter database add logfile 'e:\log01.log' size 10m;

archive log files
在线日志文件记录了数据库的记录变化。可以将这些数据复制到不同的位置或者离线存储介质中。这个操作过程就是archiving.
ARCn.
通过这个可以恢复数据库。
alter database archivelog/noarchivelog(mount);



--tablespaces and data files
database=total tablespaces=total datafiles;
smalles logical unit of storage is database block which can't be changed .创建数据库时定义的大小
system 表空间是在数据库创建时创建的。存放数据字典的对象。（函数，存储过程，触发器等也存储在这里）。


database block<extents(contiguous blocks)<segment(serval extents)

//创建临时表空间

create temporary tablespace test_temp 
tempfile 'E:\oracle\product\10.2.0\oradata\testserver\test_temp01.dbf' 
size 32m 
autoextend on 
next 32m maxsize 2048m
extent management local;

//创建数据表空间
create tablespace test_data
logging
datafile 'E:\oracle\product\10.2.0\oradata\testserver\test_data01.dbf' 
size 32m 
autoextend on 
next 32m maxsize 2048m
extent management local;

//创建用户并指定表空间
create user testserver_user identified by testserver_user
default tablespace test_data
temporary tablespace test_temp;

//给用户授予权限

grant connect,resource to testserver_user;

//以后以该用户登录，创建的任何数据库对象都属于test_temp 和test_data表空间，这就不用在每创建一个对象给其指定表空间了。

ALTER TABLESPACE TESTDATA ADD dataFILE 'E:\TMPTESTSPACE11.DBF'
     SIZE 33554432  REUSE AUTOEXTEND ON NEXT 33554432  MAXSIZE 1024M;

'--segments
select * from dba_rollback_segs where tablespace_name='SYSTEM';


select owner,segment_type,tablespace_name,count(*) from dba_segments
group by owner,segment_type,tablespace_name order by segment_type;
select owner,tablespace_name,count(*) from dba_rollback_segs
group by owner,tablespace_name order by owner;
select distinct segment_type from dba_segments;*/