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;*/