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

--controlfile/*examplaeSTARTUP NOMOUNTCREATE CONTROLFILE REUSE DATABASE "X201006" NORESETLOGS  NOARCHIVELOG    MAXLOGFILES 16    MAXLOGMEMBERS 3    MAXDATAFILES 100    MAXINSTANCES 8    MAXLOGHISTORY 292LOGFILE  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 LOGFILEDATAFILE  '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 incompleteselect * 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;手工日志切换，一般都是自动切换，当上一个日志满了，就自动切换到下一个文件。--CHECKPOINTcheckpoint 是一个操作：就是将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 filesdatabase=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 2048mextent management local;//创建数据表空间create tablespace test_dataloggingdatafile 'E:\oracle\product\10.2.0\oradata\testserver\test_data01.dbf' size 32m autoextend on next 32m maxsize 2048mextent management local;//创建用户并指定表空间create user testserver_user identified by testserver_userdefault tablespace test_datatemporary 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;'--segmentsselect * from dba_rollback_segs where tablespace_name='SYSTEM';select owner,segment_type,tablespace_name,count(*) from dba_segmentsgroup by owner,segment_type,tablespace_name order by segment_type;select owner,tablespace_name,count(*) from dba_rollback_segsgroup by owner,tablespace_name order by owner;select distinct segment_type from dba_segments;*/