Oracle learning notes
Install and manage Oracle
1, Oracle General Installer (OUI)
A tool based on Java and applies to all platforms to install Oracle9i.
2, Database Configuration Assistant (Database Configuration Assistant)
To create a new database
3, Oracle Network Configuration Assistant (Oracle Net Configuration Assistant)
Can be set for database libraries
4, Oracle Enterprice Manager (OEM) Oracle Enterprise Manager
Is a graphical system management tool for managing work Oracle components, and manages databases in a session
5, optimize flexible architecture (OFA)
It is a set of guides defined by Oracle to better manage Oracle software and databases.
6, SELECT * FROM V $ PWFILE_USERS
Isolated all information that is empowered as SYSDBA or SYSOPER users.
7, start instance
Startup Nomount -à instance can be started, but do not install the database. This statement is usually used when you create a new database or create a new control file.
Startup mount -à can start and assemble the database, but does not open it. Usually used to perform specific maintenance operations. For example, rename data files, enable or deactivate archive logs.
Startup [Open] -à launched instance, assemble the database, and open the database for normal operations.
ALTER DATABASE MOUNT-● When the database is not installed, the command will be issued to assemble the database with a previously started instance.
ALTER DATABASE OPEN-à This command will open a closed database.
Alter Database Open Read Only -à opens a database with read-only mode and cannot modify any data.
Startup Force-ia sometimes there may be problems when the instance is started, and this command can be used to force the launch of a database that is not possible or has not started. You should only use this option when you cannot close the database, Startup Force will turn off the running instance and restart it.
Startup Restrict à launches the database in a restriction mode to limit access to the database. Only users with RESTRICTED SESSION system privileges can be connected to the database. After opening the database, you can also use the ALTER System [Enable / Disable] RESSIICTED SESSION statement to start or close access restrictions.
8, get the parameter value
Show parameters [parameter name] -à When this command does not specify a parameter name, Oracle displays all parameters and its value in alphabetical order. SHOW parameters db_block_size
Or Show Parameters OS Displays all commands with the parameters of the OS letter.
9, Select * from V $ Parameter à Displays the parameter value of the current session.
Select * from V $ SYSTEM_PARAMETER-P display system parameters
Select Name, Value from V $ Parameter
10, set the parameter value
ALTER system - à changes the value of the system parameters
SLTER SESTEM SET LOG_ARCHIVE_DEST = '/ ORACLE / Archive / DB01' IMMEDIATE 'IMMEDIATE' The value of this instance is immediately applied to all sessions of the instance as long as the value of the parameter is changed.
Alter system set timed_statistics = true deferred 'DeferRed' The changed change will not take effect immediately before the new session begins, and the current session still uses the current value.
Alter session -à changes the value of the parameters
Use this command to change a parameter, the value of the parameter is only changed in that session period. When the next session is started, the parameters will be given the original value.
Alter session set nls_date_formate = 'mm-dd-yyyy';
11, management session
When it is a database connection, Oracle launches a session, as long as the user is connected to the database, the session is available.
For a user session connected to the database, you can query the view: V $ session
The SID and serial numbers (Serial #) in V $ Session uniquely identifies each session.
Such as: SELECT Username, SID, Serial #, Status from V $ SESSION
12, suspended session
Alter System Kill Session 'SID, Serial #';
Such as: Alter System Kill Session '9, 3'; where SID, Serial # is known from the V $ SESSION view.
If you want the user to complete the current transaction and then end the session, you can use the alter system disconnect session command.
Such as: ALTER SYSTEM Disconnect Session '9, 3' Post_Transaction;
If you want the user to fall back to the ongoing transaction, release all session locks, restore the entire session status, remember to return control. You can use: Alter System Disconnect Session '9, 3' Immediate; or Alter System Kill Session '9, 3' Immediate;
13. Turn off the Oracle instance
Shutdown Normal When does not take the first shutdown command, the default prior is Normal
This command does the following:
A does not allow new users to connect
B. Waiting for all users to disconnect with the database, all connected users can continue working.
C. Turn off the database, uninstall instance, and turn off the instance once all user data is connected to the database.
Shutdown Immediate makes the database as quickly as possible.
This command does the following:
A. New user connection is not allowed
B. Terminate all users and database connections
C. Roll back all submitted transactions
D. Turn off the database, uninstall instance, close instance
Shutdown Transactional turns off the database when users complete their current transactions.
A. New users are not allowed
B. Not allowing new transactions of the database, if the user is trying to start a new transaction, the session is disconnected.
C. Waiting for the user to fall back or submit uncommitted transactions.
D. Turn off the database, uninstall the instance, once all transactions are done. Close instance
Shutdown Abort If the other three closed options do not work, this command suddenly shuts down the database. When you start the database next time, you need to recover an instance.
A. Terminate all current SQL statements that are being executed
B. Disconnect all connections
C. Terminate the instance
D. Do not return the uncommitted transaction. If the database is started after the database, Oracle must use the online redo log file to roll back uncommitted transactions.
14, instance message and instance warning
Oracle writes information about messages and warnings to different files. The location of these different files is specified in the initialization parameters:
Background_dump_dest-à Write the location of the debug tracking file
User_dump_dest -à writes the location of the tracking file generated by the user session.
Core_dump_dest -à Write the location of the main memory dump file.
15, OMF (Oracle Management File)
Oracle Management File Features: You can use two primating parameter definition files in the operating system: db_create_file_dest-à Specifies the default location of the new data file.
DB_CREATE_ONLINE_LOG_DEST_N-à Specifies the most five locations for online redo log files and control files.
Create a database and data dictionary
1. Create a prerequisite for the database:
A. Assign the necessary hardware resources such as memory and hard disk space
B. Set the operating system and sysdba permissions
C. Set parameter file
D. Set environment variable
E. Install Oracle Software
F. Backup existing database
2. Create a database command
Before creating a database, you must start an instance (with startup nomount)
example:
CREATE DATABASE "Database Name"
ControlFile Reuse -à overrides existing control files, usually only used when rebuilding the database.
Logfile Group 1 àLogfile clause Specify the location of the online redo log file, if the Group clause is omitted, Oracle creates a group for the first file. A database requires at least two heavy execution groups.
('/Oradata02/prod01/redo0101.log',
'/Oradata03/prod01/redo0102.log )Size 5m Reuse,
GROUP 2
('/Oradata02/prod01/redo0201.log',
'/Oradata03/prod01/redo0202.log) Size 5m Reuse
MaxLogfiles 4 à Defines the number of maximum redo log files that can be created by the database.
MaxLogMembers 2 à Defines the maximum number of redo log files that you can have.
MaxLoghistory 0 à is only used to set up a real-use cluster. Define the size of the redo log file that is automatically recovered by the media.
MaxDataFiles 254 à Defines the number of data files that can be created (when creating tablespace)
MaxInstances 1 à defines the maximum number of instances that can be assembled and open.
NOARCHIVELOG à Set the storage mode of the redo log file (this is the default) Another value is: ArchiveLog
Character set "we8mswin1252" à Specifies the character set used by the stored data (this is the default value of the Window platform)
National Character Set "AL16UTF16" à Specifies the national character set, used to store data in nchar, nclog, or nvarchar2 columns..
DataFile '/oradata01/prod01/system01.dbf' size 90m AutoExtend ON next 5m maxsize unlimited à Defines one or more files created for system tablespace.
Undo tablespace undotbs datafile '/oradata04/prod01/undo01.dbf' size 35mà Specifies an undo table space with one or more related data files.
Default Temporary TableSpace Temp Tempfile '/oradata05/Prod01/Temp01.dbf' size 20m à Defines the location of all temporary tablespaces
3. Creating a summary of the work involved in the database:
A. Make sure you have enough resources and necessary permissions.
B. Determine the name of the database, the location of the control file, the block size of the database, and prepare a parameter file including other necessary parameters.
C. Determine the location of the control file, data file, and redo log files. Where possible, data files that may compete resources should be dispersed into different physical storage components.
D. Determine the version and instance name of the database. Set the environment variable Oracle_home to install the directory of the Oracle software, set Oracle_sid as an instance name. Normally, the instance name and database name are the same. If you do not use the default character set, set the ORA_nls33 environment variable.. Start instance. Use SQL * Plus, link the Sysdba account, run the startup nomount command
F. Create a database using the Creatate Database Command
4. Each database should have at least one instance associated with it, and multiple instances can access a database. Can each instance have multiple databases? If there are multiple databases, is it opened when there is a startup command? Can you specify one of the databases? How to specify which database is specified when creating a user or creation table space?
5, after the database is installed, Oracle will automatically call the following two SQL scripts to create data dictionary
Catalog.sql à is used to create a data dictionary view
Catproc.sql à Create the necessary dictionary items for implementing the PL / SQL function
6. Manage store programs and packages
Creating a process, software or function code can be accessed by DBA_Source, All_Source, User_Source Dictionary
DBA_OBJECTS, ALL_OBJECTS, and USER_OBJECTS view provide status information about stored procedures.
7, reconfile command:
ALTER Procedure
Alter Procedure <包 名> Compile;
Alter Procedure <包 名> Compile Body;
8, data dictionary classification
DBA_ à contains information about all structures in the database
All_ à provides information about users accessible objects
User_ à Displays information about the structure owned by the user (by user program)
V $ -à dynamic running view, (true dynamic running view is prefixed by v_ $ is prefix, V $ is just its synonym)
9, you can use the data dictionary information to generate the code created in the object in the database.
The table information can be obtained by DBA_TABLES, DBA_TAB_COLUMNS, AL1_TABLES, ALL_TAB_COLUMNS, USER_TABLES, USER_TAB_COLUMNS dictionary view.
10. Dictionary View Dictionary (dict) contains the name and description information of all data dictionaries in the database.
Control files and re-execute log files
Control file
1. Control file à Maintain information about the physical structure of the database, it can be seen as a metadata warehouse for the actual database, which contains the structure of the database à data files and re-execute log files.
Control files are continuous updates,
The size of the control file is determined by the MAX clause of the creation database.
When adding a file to the database or changing the file, the Oracle server mileage will immediately update the corresponding information in the control file.
Control files contain two records: reusable and non-reused.
2, the control file should contain the following:
A. Control file name belongs, one control file can only belong to a database.
B. Creating a database timestamp
C. Data file, time, location, and online offline status information.
D. Relogue the name and location of the log file, and archive information
E. Table space name
F. Current log serial number
G. Recent checkpoint information
H. The beginning and end of the withdrawal section
I.Rman's backup information
3. Reemption Control File àoracle suggests that there are at least two control files
There are two ways to multiplex:
A> Using the init.ora multiplex control file
To define multiplexed control files, copy control files to different locations and change the Control_Files parameters in the initialization file init.ra.
You should usually store the control file in multiple disks
If a control file is lost, you can restart the database if you copy additional control files or change the control_files parameters in your initial file.
Steps to increase the copy of the control file:
A. Close the database
B. Copy the control file to multiple locations using the operating system command
C. Update the control_files parameter of the initial changing parameter file to enable it to contain all the names of the new control file.
D. Start database
B> Use the SPFILE multiplex control file steps:
A. Change SPFILE in the database.
SQL> ALTER System SET Control_Files =
'/Ora01/oradata/mydb/ctrlmydb01.ctl',
'/Ora02/oradata/mydb/ctrlmydb02.ctl',
'/Ora03/oradata/mydb/ctrlmydb03.ctl',
'/Ora04/oradata/mydb/ctrlmydb04.ctl' scope = spfile;
B. Close the database
SQL> Shutdown Normal;
C. Copy existing control files to new locations (copy, paste)
D. Start instance.
SQL> Startup;
4. Create a new control file
In the following cases, you need to create a new control file.
A. If you lose all control files
B. If you want to change any MAX clause in the CREATE DATABASE command.
C. If you want to change the name of the database
Prerequisite the creation of a new control file:
Need to know the name of the data file and the name of the redo log file
5. Steps to create a control file
A.
Create ControlFile Set Database "Oracle"
NoSetlogs noarchiveLog
MaxLogfiles 32
MaxLogmembers 2
MaxDataFiles 32
MaxInstances 1
MaxLoghistory 1630
Logfile
Group 1 'c: /oracle/database/log2orcl.ora' size 500k,
Group 2 'c: /oracle/database/log1orcl.ora' size 500k
DataFile
'C: /oracle/database/sys1orcl.ora',
'C: /oracle/database/usr1orcl.ora',
'C: /oracle/database/RBS1ORCL.ORA',
'C: /oracle/database/tmp1orcl.ora',
'C: /oracle/database/appdata1.ora',
'C: /oracle/database/appinidx1.ora';
B. Close the database
C.Startup Nomount (Oracle needs to open the control file to load the database)
D. The name and location of the control file follow the definition of the initialization parameter control_files
E. Open the database with the Alter Database Open command.
F. Turn off the database. And back up the database
6. Query the V $ DataFile view, you can find if the control files listed in the control file are missing. If there is an missing file, it is displayed in the MissingNNN name.
If you create a control file in a resetLogs option, the missing data file cannot be added back to the database.
If you create a control file with a NoresetLogs option, the omissionable data file can be added to the database through the media.
7. Backup control file
Back up the control file with the following command in the database on state
Alter Database Backup ControlFile To '
You can also back up the control file:
Alter Database Backup ControlFile to TRACE;
This command puts the control file into a trace file located in the text format of user_dump_dest, deletes some unrelated information to recreate the control file.
Oracle recommends that once changing the structure of the database: If you add a data file, a rename of a data file, or delete a log file, you should back up the control file.
8. Query Control File Information V $ ControlFile
This view lists the names of all database control files
You can also get the name of the control file through the show parameter command:
SQL> Show Parameter Control_Files;
9. Display the record of the control file.-Àv $ controlfile_record_section
Archive Log Information àv $ Archived_log Composition of a single data file of a database - àv $ backup_datafile
Information about backup blocks, update àv $ backup_piece when using RMAN
Information on archive log files with RMAN backup àv $ backup_redolog
Successfully use RMAN backup information àv $ backup_set
Database information àv $ dataBase
Information about data files related to database àv $ datafile
Information on data files with hot backup or using RMAN copies àv $ datafile_copy
Online redo log file group information àv $ log
Online redo log file group file or member àv $ logfile
Information on log files assigned to each instance àv $ thread
Re-execute log file -à record all changes in the database
