一些SQL語句

[size=medium]
　　1.forcing log switches

　　sql> alter system switch logfile;

　　2.forcing checkpoints

　　sql> alter system checkpoint;

　　3.adding online redo log groups

　　sql> alter database add logfile [group 4]

　　sql> ( '/disk3/log4a.rdo ', '/disk4/log4b.rdo ') size 1m;

　　4.adding online redo log members

　　sql> alter database add logfile member

　　sql> '/disk3/log1b.rdo ' to group 1,

　　sql> '/disk4/log2b.rdo ' to group 2;

　　5.changes the name of the online redo logfile

　　sql> alter database rename file 'c:/oracle/oradata/oradb/redo01.log '

　　sql> to 'c:/oracle/oradata/redo01.log ';

　　6.drop online redo log groups

　　sql> alter database drop logfile group 3;

　　7.drop online redo log members

　　sql> alter database drop logfile member 'c:/oracle/oradata/redo01.log ';

　　8.clearing online redo log files

　　sql> alter database clear [unarchived] logfile 'c:/oracle/log2a.rdo ';

　　9.using logminer analyzing redo logfiles

　　a. in the init.ora specify utl_file_dir = ' '

　　b. sql> execute dbms_logmnr_d.build( 'oradb.ora ', 'c:\oracle\oradb\log ');

　　c. sql> execute dbms_logmnr_add_logfile( 'c:\oracle\oradata\oradb\redo01.log ',

　　sql> dbms_logmnr.new);

　　d. sql> execute dbms_logmnr.add_logfile( 'c:\oracle\oradata\oradb\redo02.log ',

　　sql> dbms_logmnr.addfile);

　　e. sql> execute dbms_logmnr.start_logmnr(dictfilename=> 'c:\oracle\oradb\log\oradb.ora ');

　　f. sql> select * from v$logmnr_contents(v$logmnr_dictionary,v$logmnr_parameters

　　sql> v$logmnr_logs);

　　g. sql> execute dbms_logmnr.end_logmnr;

表空間管理

　　1.create tablespaces

　　sql> create tablespace tablespace_name datafile 'c:\oracle\oradata\file1.dbf ' size 100m,

　　sql> 'c:\oracle\oradata\file2.dbf ' size 100m minimum extent 550k [logging/nologging]

　　sql> default storage (initial 500k next 500k maxextents 500 pctinccease 0)

　　sql> [online/offline] [permanent/temporary] [extent_management_clause]

　　2.locally managed tablespace

　　sql> create tablespace user_data datafile 'c:\oracle\oradata\user_data01.dbf '

　　sql> size 500m extent management local uniform size 10m;

　　3.temporary tablespace

　　sql> create temporary tablespace temp tempfile 'c:\oracle\oradata\temp01.dbf '

　　sql> size 500m extent management local uniform size 10m;

　　4.change the storage setting

　　sql> alter tablespace app_data minimum extent 2m;

　　sql> alter tablespace app_data default storage(initial 2m next 2m maxextents 999);

　　5.taking tablespace offline or online

　　sql> alter tablespace app_data offline;

　　sql> alter tablespace app_data online;

　　6.read_only tablespace

　　sql> alter tablespace app_data read only|write;

　　7.droping tablespace

　　sql> drop tablespace app_data including contents;

　　8.enableing automatic extension of data files

　　sql> alter tablespace app_data add datafile 'c:\oracle\oradata\app_data01.dbf ' size 200m

　　sql> autoextend on next 10m maxsize 500m;

　　9.change the size fo data files manually

　　sql> alter database datafile 'c:\oracle\oradata\app_data.dbf ' resize 200m;

　　10.Moving data files: alter tablespace

　　sql> alter tablespace app_data rename datafile 'c:\oracle\oradata\app_data.dbf '

　　sql> to 'c:\oracle\app_data.dbf ';

　　11.moving data files:alter database

　　sql> alter database rename file 'c:\oracle\oradata\app_data.dbf '

　　sql> to 'c:\oracle\app_data.dbf ';
使用者管理

　　1.create a user: database authentication

　　sql> create user juncky identified by oracle default tablespace users

　　sql> temporary tablespace temp quota 10m on data password expire

　　sql> [account lock|unlock] [profile profilename|default];

　　2.change user quota on tablespace

　　sql> alter user juncky quota 0 on users;

　　3.drop a user

　　sql> drop user juncky [cascade];

　　4. monitor user

　　view: dba_users , dba_ts_quotas

備份與恢復

　　1. v$sga,v$instance,v$process,v$bgprocess,v$database,v$datafile,v$sgastat

　　2. Rman need set dbwr_IO_slaves or backup_tape_IO_slaves and large_pool_size

　　3. Monitoring Parallel Rollback

　　> v$fast_start_servers , v$fast_start_transactions

　　4.perform a closed database backup (noarchivelog)

　　> shutdown immediate

　　> cp files /backup/

　　> startup

　　5.restore to a different location

　　> connect system/manager as sysdba

　　> startup mount

　　> alter database rename file '/disk1/../user.dbf ' to '/disk2/../user.dbf ';

　　> alter database open;

　　6.recover syntax

　　--recover a mounted database

　　> recover database;

　　> recover datafile '/disk1/data/df2.dbf ';

　　> alter database recover database;

　　--recover an opened database

　　> recover tablespace user_data;

　　> recover datafile 2;

　　> alter database recover datafile 2;

　　7.how to apply redo log files automatically

　　> set autorecovery on

　　> recover automatic datafile 4;

　　8.complete recovery:

　　--method 1(mounted databae)

　　> copy c:\backup\user.dbf c:\oradata\user.dbf

　　> startup mount

　　> recover datafile 'c:\oradata\user.dbf;

　　> alter database open;

　　--method 2(opened database,initially opened,not system or rollback datafile)

　　> copy c:\backup\user.dbf c:\oradata\user.dbf (alter tablespace offline)

　　> recover datafile 'c:\oradata\user.dbf ' or

　　> recover tablespace user_data;

　　> alter database datafile 'c:\oradata\user.dbf ' online or

　　> alter tablespace user_data online;

　　--method 3(opened database,initially closed not system or rollback datafile)

　　> startup mount

　　> alter database datafile 'c:\oradata\user.dbf ' offline;

　　> alter database open

　　> copy c:\backup\user.dbf d:\oradata\user.dbf

　　> alter database rename file 'c:\oradata\user.dbf ' to 'd:\oradata\user.dbf '

　　> recover datafile 'e:\oradata\user.dbf ' or recover tablespace user_data;

　　> alter tablespace user_data online;

　　--method 4(loss of data file with no backup and have all archive log)

　　> alter tablespace user_data offline immediate;

　　> alter database create datafile 'd:\oradata\user.dbf ' as 'c:\oradata\user.dbf ' '

　　> recover tablespace user_data;

　　> alter tablespace user_data online

　　5.perform an open database backup

　　> alter tablespace user_data begin backup;

　　> copy files /backup/

　　> alter database datafile '/c:/../data.dbf ' end backup;

　　> alter system switch logfile;

　　6.backup a control file

　　> alter database backup controlfile to 'control1.bkp ';

　　> alter database backup controlfile to trace;

　　7.recovery (noarchivelog mode)

　　> shutdown abort

　　> cp files

　　> startup

　　8.recovery of file in backup mode

　　> alter database datafile 2 end backup;

　　9.clearing redo log file

　　> alter database clear unarchived logfile group 1;

　　> alter database clear unarchived logfile group 1 unrecoverable datafile;

　　10.redo log recovery

　　> alter database add logfile group 3 'c:\oradata\redo03.log ' size 1000k;

　　> alter database drop logfile group 1;

　　> alter database open;

　　or > cp c:\oradata\redo02.log ' c:\oradata\redo01.log

　　> alter database clear logfile 'c:\oradata\log01.log ';
[/size]