Basic Operation: Starting Startup Force ignores the existing instance directly restarts Startup Mount to enable ControlFile, used to change the log mode, Rename file, Database Recovery, etc., complete DBASE is not opened, and user information cannot be read STARTUP. Nomount launches Instance, which can be used to create Database and Control File, but you cannot read the database's Structure Information Startup Open Startup Restrics Only users with RESTRICED privileges can be logged in.
Close Shutdown Immediate Shutdown Abort Create Table Create Table Table Name (available from Self-Query as Select * from;) Table Content Operation Update Table Name SET Operation Content Performs COMMIT
File Status: Files starting with V $ are dynamic view dynamic views with single numbers, static views in the plural inactive mode indicating that no user is accessing it Current mode indicates that the current is being used to use the transition that has not been used. State, there is no impact on the system itself invalid error
Triggers
Control File: Control file must be backed up and save it in different partitions. The file can only be copied when the CLOSE is status.
Redo Log File:
Log file: Select * from V $ logfile See all redo log files Select * from V $ log View all Groupselect * from V $ TABLESPACE View table space Select * from v $ datafile View Data file ALTER SYSTEM SWITCH logfile switching system Current log file Alert log file switch log file switch currently using ALTER DATABASE DROP lOGFILE MEMBER 'file path'; delete logfileALTER DATABASE ADD lOGFILE MEMBER 'file path' TO GROUP #; add logfileALTER DATABASE CLEAR lOGFILE 'file path'; Clear logfile (clear the contents of the file )
Checkpoint The most important function is to synchronize the Control Files and Data FileSckpt, which will modify the Control Files and Data FileSckpt: After the Log Switch has reached a block, set the CKPT to define a time with log_checkpoint_interval, if This time has reached mandatory execution. Set with log_checkpoint_timeout
Basic Principle of Database Recovery: Archiving Mode: Archived Redo Log Records the user's operation When the data is corrupted, first, first copy the latest backup file, then perform an archive log file one by one, you can restore all data. In the non-archive mode, only the data of the last backup can only be restored.
Redo Log Configuration Each MEMBER should have a backup of each group of Member should be in size. Part of each Disk should cross backup
How to relocate or rename the REDO file: 1. Copy the redo log file to the new location 2. Execute the ALTER DATABASE RENAME File 'Order Full Path' to 'Target Full Path' (generally in the Monk state)
Redo log may appear: 1. Some Member invalidated in a group 2. All Member in the next group is invalid 3. All of the Member of the current group is invalid.
View the contents of the Redo Log file: Using the Logminer tool (need to install SP4) 1 Define the initialization parameter file UTL_FILE_FILE_FILE_DIR = File path 2 restart database Make parameters to take effect 3 Use scripts to create dictionary files EXECUTE DBMS_LOGMNR_D.BUILD ('file name ",' file path '); 4 added into the archive log files dictionary file EXECUTE DBMS_LOGMNR.ADD_LOGFILE (' file path ', DBMS_LOGMNR.NEW); EXECUTE DBMS_LOGMNR.ADD_LOGFILE (' file path ', DBMS_LOGMNR.ADDFILE); 5 starts analysis EXECUTE DBMS_LOGMNR. START_LOGMNR (DictFileName => 'file path "); 6 View result select timestamp, username, sql_redo from v $ logmnr_contents; SQL_REDO file saves the user's operation SQL_UNDO to undo the user's operation (such as multiple changes, its UNDO effect Some results are stored in the following views after successful analysis. V $ logmnr_dictionaryv $ logmnr_parametersv $ logmnr_contents
MANAGING TABLESPACES AND DATA FILES:
Database | / | / TABLESPACE ------
TABLESPACE: A logical object separates Data file and segment, consists of one or more objects, which can break the limit of the size of the operating system block. Segment: Some growing objects such as Table INDEX EXTENT: Each space assignment is called an extent creation to perform in table space in accordance with a certain allocation policy. Initialization is initialized when the table is built, if the space is not enough, continue to expand every time it is expanded in size. Extent cannot be across space and can only belong to a data file. Oracle Block is referred to as DB Block, consists of OS Block, must be an integer multiple. Its size is determined by the initialization parameter dbplockset, and once this parameter is set to change in the process created in the database, it cannot be changed. System and non-system table space system table space mainly store system data non-system table space main storage user data Create TableSpaces Create TableSpace Table Space Name DataFile 'File Name.dbf' Size Space Size Minimum Extent Space Size Default Storage (Initial Space NEXT Space Size MaxExtents Number PctinceRease 0); TableSpaces Space Management Data Dictionary Management Table Space Local Management Table Space Create TableSpace Table Space Name DataFile 'File Path.dbf' Size File Size Extent Management Local Uniform Size Space Size Advantages: Space Management Does Not Generate (Slowly Request Slow) Reduce Contacts with Data Dictionary Table Generating a rollback segment (if the rollback executes the DML statement) does not need healing (coalescing) Temporary table space (Temporary TableSpace is not recommended, a large amount of fragmentation) CREATE TEMPORARY TABLESPACE Table Space Name Tempfile 'File Path.dbf' Size File Size EXTENT Management Local Uniform Size Space Size Used to sort the uniform size = sort_area_size * n recommended to use the local management table space, which can reduce debris can dynamically increase if the disk space can be used to use temporary table space, which is now rarely used. Modify the storage area Set the ALTER TABLESPACE table space name.. Set the table space to OFFLINE (at this time, the table space is not available, you should set it to this state when doing backup) ALTER TABLESPACE table space name OFFLINE; ALTER TABLESPACE Table Space Name Online; system table space and active returns Table space cannot be affected by OFFLINE Make the table space read-only ALTER TABLESPACE table space name read only; table spaces can still be dropped by DROP (DROP system modified data dictionary without modifying tablespace) Requirements: This table space must be an online status cannot be included There is an active returning segment table space cannot be involved in the current backup Delete Table Space Drop TableSpace Table Space Name Including Contents; Table Space Delete from the Data Dictionary, the object in the table space is also deleted but the file file still exists Change the size of the table space automatic and manual operation Adding a data file to the table space can add a file size and add file number to increase the size of the table space ALTER TABLESPACE Table Space Name Add DataFile 'file name' size file size Autoextend on Next Next Space size MaxSize maximum scalable space size ALTER DATABASE DATAFILE 'file name' resize changes the size of the file Table Space Management Principle: Use multiple tablespaces as much as possible, turn the data distinguished by the table space to the default parameter to create a table space, be sure to use the path to limit the user's occupation of space, using the quotas command to use Minimum Extens, can Possible reduction debris uses local management table spaces, not recommended to use the temporary management table space table space cannot be greater than 1023 Chapter 9 Database Logical Structure Segments: collectively growable object Segments such as assigning Table, cluster, Table partion, Index, index-organized table, index partion, Rollback segment, Temporary segment, LOB segment, LOB index, Nested table, Bootstrap segmentExtent space and deallocate : When segment is assigned by CreateDextendedaltered When Segment is released by DroppedalteredTruncated (delete all data in a table) AutomaticLBack seized (ROLLBACK Segment Only) DB Block: The minimum I / O unit is set by the OS Blocks to set the DB_BLOCK_SIZE set in the database. The use of the block space records the schedule in its head opening space, and each space has become a sketch. Everything needs to be applied for a sketch. Set by initrans and maxTrans PCTFree = 20 means that there is a 20% space in DB Block, which uses PCTUSED = 40 that means that if the space used in DB Block is less than 40%, the system will link this DB Block to the exactist in the Segment header. Go, it means that this block can be inserted into data (random selection) MIN_EXTENTS = 20 indicates that the system is initialized when it is initialized, and 20 extens is generated, and 20 extens is continuously expanded to avoid dynamically expand during use. DBA_EXTENTSFILE_ID absolute file number. The system has previously numbered block_id relative file numbers in the database. Number based on each table space Chapter 10 Rollover Management Once the commit command is executed, the space of the rollback segment is released. The role of the rollback segment: 1. Guarantee to read the one-time read consistency2. Rollback Transaction Rollback3. Recovery of things Transaction Recovery The system returns: Created when creating a database, is used by the system. Non-system returns: System administrators are created for all users. It is also divided into private and public Chapter 12 Index Management Logical index and physical index Anti-key index (reverse of its ID) can expand its index range bitmap index: establish a bitmap for each candidate word, and only use the corresponding bitmap as or calculated when the index is used. Comparison of Bitmap and B-Tree index: It is very expensive when using a small type of candidate value or less UPDATE when using it. Chapter 13 Constraint Constraint status in the database: Check if the new data is valid, whether the DISABLED Enabled inteacteats the previous data to check Novalidate Validata. There are four status portfolios: Disabled NoValidatedisabled ValidataNabled NoValidataNabled Validate Constraint delay: Insert all statements first, then check all delay constraints when performing CommIT, if there is a statement that does not meet the requirements, Commit invalid, all statements will be rolled back. After issuing a DML statement, the system first checks the non-delay constraint and checks the delay constraint when performing commits. Delay of the definition statement: This statement is set to be delayed when defining constraints and in the current session. This statement can be delayed. Alter session set constraint [s] = {immediate | deferred | default}; must establish an index when establishing a Primary Key, and there is a system automatically established, but it can specify its storage space. You can only create a unique index when constraints are delayed. Subsheets and parent tables can be in different tablespaces, but cannot be in different databases. The parent table must be guaranteed to be on the ONLINE state when performing DML operations on the child table. Defining a constrained CREATE TABLE table name {ID Number (#) constraint constraint name primary key deflable // is delayed Using Index // Points the index to another table space Storage (Initial size Next size) TableSpace Indx, Last_name VARCHAR2 (#) constraint constraint NOT NULL, DEPT_ID NUMBER (#)} TABLESPACE DATA; Principles of Index Loading: Primary and Unique Constraints: Index and Data should be placed in different tablespaces if a large amount of data is loaded, and it is best to use an index of uniqueness. Self-referenced foreign key constraints: constraint checks in the delay in the extension of foreign key after initialization loading Chapter 14 Loading Data Use SQL * Loader Tools Direct path loading INSERT / * APPEND * / INTO file name .Empnologging // Do not write redo logfile, unable to restore SELECT * from the original file name .emp If the data will be added to a high water point, the data will be accelerated due to the continuous supply of the space. Parallel insertion (depending on the number of CPUs): ALTER Session Enable Paallel DML; // Modify the current environment to allow parallel operations, and specific to whether the hardware is supported. INSERT / * Parallel (file name, #) * / // Open # 个 并 度 file name .Empnologging select * from the original file name .OLD_EMP The load of the ordinary path puts the data in the data file directly into the table in memory. Re-group data, generate an Insert statement, you must use COMMIT to take effect. Always generate REDO involves all constraint INSERT trigger TRIGGER Support CLUSTER other users can modify Direct path loads using Extent management, recombine the data in the data file directly in memory, and then write the high-water point of the table, the speed is very fast, but if the error is difficult to repair. With Data Save technology, only REDO is generated when EXTENT expansion only involves primare key, unique, not null constraint INSERT does not trigger TRIGGER does not support Cluster other users cannot modify, and cannot be DML operations SQL * Loader Precautions: Use the parameter file to record the operation If the amount of data is small and only loads the data directly in the control file to allocate enough space, avoid dynamic expansion loading large amounts of data into the index, you can order Loading to parallel loading can define different Temporary Segments Common error: When the space assignment is not loaded, INSRANCE failed (need to view the log file) After using the sorted indexes parameter, there are some data without row, and the unique index is defined in direct loading, unique or primary keybindsize definitions, general You can do not define an error that exceeds a certain limit Chart 15 Recombinant Data Export and Import's use execution logic backup Procedure: ImpuserNameImport File Note: Use its parameter file as much as possible PFiles Only use consistent = y in the output, otherwise it will occupy a lot of returned segments If there are many deleted rows, do not use compress = y (will Adding multiple extents of the original table) can allocate greater buffer to use direct path export Transfer tablespace (you can pass a table space from one database to another): can only be exported by the system administrator Specific steps: Make this table space read only the original data copy data file to the specified system Transfer EXPORT FILE (definition file for tablespace) Write the file to the specified system, if necessary, turn the table space to a read-write state Role and restriction: Data that can move the entire table space must be the restoration of the media must be the same operating system must be the same BLOCK size to use the same character set (Character Set). Objects in the table space must be self-contained Chapter 16 Password Safety Management uses the configuration file forced execution, which is always valid.
