Oracle DBA Learning Log 1 (controlfile, tablespace)

-- Controlfile/* examplaestartup nomountcreate 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 50 m, group 2 'd: \ oracle \ product \ 10.2.0 \ oradata \ x201006 \ redo02.log 'size 50 m, group 3 'd: \ oracle \ product \ 10.2.0 \ oradata \ x201006 \ redo03.log' Size 50 m, group 4 'e: \ redo1.log' size 10 m -- 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; alte R tablespace temp add tempfile 'd: \ oracle \ product \ 10.2.0 \ oradata \ x201006 \ temp01.dbf 'size 20971520 reuse autoextend on next 655360 maxsize 32767 m; Alter tablespace tmptestspace add tempfile 'e: \ tmptestspace. DBF 'size 33554432 reuse autoextend on next 33554432 maxsize 1024 m; */-- Multiplexing Control Files/* shut down database, copy control file to more locations, initichange alization parameters (contro L_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;/* read controlfile when the database is mounted, before opening the database, some Dynamic Performance views can be accessed. * // * redo log files = online redo log files (log files can be opened and accessed during database operations). The database has at least two log files, and the lgwr cyclically accesses log files, overwrite log file after one cycle. A log file consists of a redo record also called redo entry, including a rollback segment. */alter SY Stem switch logfile; manual log switch, usually automatic switch, when a log is full, it will automatically switch to the next file. -- Checkpointcheckpoint is an operation that writes the modified data in the buffer cache to the hard disk and updates the control file and datafile. trigger condition: redolog file reaches 90% (initialization starts). When some parameters are changed, force alter system checkpoint; log_checkpoint_interval; log_checkpoint_timeout; fast_start_to_target; Alter database add logfile 'e: \ log01.log 'size 10 m; the archive log files online log file records changes in the database. You can copy the data to different locations or offline storage media. This operation process is archiving. arcn. Through this, you can restore the database. 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. the size defined during database creation. The system tablespace is created when the database is created. The object that stores the data dictionary. (Functions, stored procedures, and triggers are also stored here ). 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 32 m autoextend on next 32 m maxsize 2048 mmttent management local; // create tablespace test_dataloggingdatafile E: \ oracle \ product \ 10.2.0 \ oradata \ testserver \ test_data01.dbf 'size 32 m autoextend on next 32 m Max Size 2048 mextent management local; // create a user and specify the tablespace create user testserver_user identified by testserver_userdefault tablespace test_datatemporary tablespace test_temp; // grant the user the permission to grant connect, resource to testserver_user; // log on to the user later. Any database objects created belong to the test_temp and test_data tablespaces. Therefore, you do not need to specify a tablespace for each object created. Alter tablespace testdata add datafile 'e: \ tmptestspace11.dbf' size 33554432 reuse autoextend on next 33554432 maxsize 1024 m; '-- segmentsselect * From region 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 distribute by owner, tablespace_name order by owner; select distinct segment_type from dba_segments ;*/