Instances, databases, and tablespaces

The first architectural difference between SQL Server and Oracle lies in the definition of the concepts of instances and databases. In SQL Server, the term "instance" is used to represent an Independent Application Service that contains operating system files, memory structures, background processes, and registry information. In Windows, a stopped or running service is used to represent an instance. When the instance is in the running state, it must occupy a certain amount of server memory and generate a certain number of background processes. The center of an SQL server instance is a database. An SQL Server database refers to the program code required for a database and data operations. When the instance is not running, the database in the instance cannot be accessed. SQL Server has two types of databases: System database and user database ). After an SQL server instance is installed, five system databases are automatically created: Master, model, MSDB, tempdb, and resource. If multiple SQL Server instances are installed on a machine, no instance will have its own system database. In addition to the MSDB database, instances cannot be started if other databases are inaccessible or damaged. In contrast, a user database is created after the database instance is installed and the system database is started by a DBA or developer. These databases store company business information. In short, an SQL server instance always includes some databases (although sometimes only those System databases), and a database always has one (and only one) associated instance. Physically, an SQL Server database is a collection of operating system files stored on disks. There are two types of database files:Data File)AndTransaction Log File). A database must contain at least one data file and one transaction log file. The data of the SQL Server database is mainly stored in the data file, the transaction log file is used to record the changes that occur on the Data. SQL server uses it when executing system recovery. A data file or transaction log file can only belong to a specific database. There is no situation where two databases share a data file or log file. A database with a large amount of data can use multiple data files, which can be combined by logic intoFile Group). In Oracle, all of this seems a bit reversed. When oracle is started, like SQL Server, it must first occupy some server memory for operations. This memory zone is the famousSGA (system global area)-- It is divided into several different structures. When SGA is created, a series of background processes are started to interact with SGA, here, the allocated memory space is combined with the background process to form an oracle instance. Please note that we have not seen the shadow of the database yet. In fact, Oracle instances run well when there is no database or the database is inaccessible. When installing Oracle, we can choose to install only the software, after that, install the database. Oracle contains a group of operating system files. Unlike SQL Server databases, Oracle databases do not represent logical groups of database objects. They are more like a single set of nouns that contain multiple files stored on disks for data storage. The files that make up the Oracle database can be divided into three types:Data File),Redo log file)AndControl File). Data Files store data. Oracle can store any number of data files. redo log files are the same as SQL Server transaction log files to save records of data changes, it must be used in the system recovery phase. The control file is a small file used to store important information about the database. Without this file, the instance cannot open the database. In addition to data files, redo log files, and control files, the database also containsParameter file),Password File)And optionalArchive log files). These file types will be discussed soon. When the Oracle system starts, first create a database instance in the memory, then find the database saved in the disk by the instance, and finally open the database for user operations. When the system is disabled, the instance will be removed from the memory: The entire memory structure and background process will disappear, but the database still exists on the disk and is only in the closed state. As mentioned before, Oracle instances can run without opening the database-this is the biggest difference with SQL Server databases, and SQL Server instances cannot run without leaving the system database. However, like SQL Server, Oracle databases cannot be accessed when the instance is not started. Generally, there is a one-to-one relationship between Oracle instances and databases. An instance corresponds to a database, but a database can be accessed by multiple instances at the same time. An independent Oracle Installation contains an instance and a database for instance operation, and is configured as RAC (real application cluster) you can allow multiple instances on different machines to access databases on a shared disk. Where is the logical grouping of database objects in Oracle? In SQL Server, logical grouping is done by the database itself, and in Oracle, this work is doneTable space)Complete. The Oracle tablespace is the logical structure used to group tables, views, indexes, and other database objects. For example, your Oracle product database can apply a separate tablespace to the HR application, and the payment application uses another one. A database can be logically divided into several tablespaces, which are physically composed of one or more data files. Therefore, tablespace is equivalent to the SQL Server database in Oracle. Because these two structures have similar functions, the process of creating a database in SQL Server is very similar to that of creating a tablespace in Oracle. Whether you are creating a database or a tablespace, the DBA must first specify a name and then allocate one or more data files to the newly created database or tablespace, specify the initial size and data growth for each data file. In SQL Server, a user database can be offline or read-only, and the user tablespace of Oracle can be the same. In SQL Server, one or more data files in a user database can be read-only, while one or more data files in the Oracle user tablespace can also be marked offline. However, there are differences between databases and tablespaces in some aspects: In SQL Server, data files can be grouped by file group logic, while Oracle tablespace does not have a similar concept. Each database of SQL Server has its own transaction log files, and the attributes of these log files must be specified when the database is created. In Oracle, transaction logs of the entire database (meaning all tablespaces) are recorded in the same redo log. Therefore, there is no statement to create a separate log file for each tablespace. In SQL Server, the database can be setSimple recovery mode)In simple recovery mode, active database logs are truncated after the checkpoint operation is completed. Oracle also has a similar concept, which will be mentioned later, but it cannot be set at the tablespace level. Top Instance name and Sid Both SQL Server and Oracle allow multiple instances to run on the same machine at the same time. The execution environment of multiple instances is completely independent: for a single database engine, it does not know or care whether there are other instances running on this machine. In SQL Server, this mechanism is implemented through the concept of instances. SQL Server can be named as a (named) Or Default) The default Instance name is the same as the name of the Windows server that runs it. Obviously, only one default instance in a system may exist, however, a single machine can have multiple named instances. The command Instance name format is Hostname \ instance_name , For each running instance on the same host Instance_name It must be unique. Each instance has its own set of program files and some common components shared with other instances. Oracle is similar. When installing Oracle, DBA needs to specify Global Database Name)AndSystem identifier (SID, system identifier). The instance and database in Oracle are completely different. A global database name is used to uniquely identify the storage location of a database on the network. A complete name is usually in the following format: database_name.network_domain_name. Sid is used to identify an instance associated with the database. In most cases, an instance is associated with a single database. The database name and Sid name are the same. The RAC environment is different. RAC allows multiple instances to access the same database in the shared storage. The instance name and database name are different at this time. Of course, like SQL Server, two instances are not allowed to use the same Sid on an Oracle database server. Another thing that is similar is that once specified during installation, neither the SQL server instance name nor the Oracle SID can be modified ). SQL Server DBA can query the Instance name of the currently logged on system using the following statement: View Source Code Print help 1 SELECT @@SERVERNAME The statement used by Oracle DBA to query instance names and data names is as follows: 1 SELECT INSTANCE_NAME, HOST_NAME, VERSION, DATABASE_STATUS FROM V$INSTANCE; 2 SELECT NAME, DATABASE_ROLE, CREATED FROM V$DATABASE; Top System database and system tablespace An SQL server instance requires five system databases (four databases before 2005): Master, model, MSDB, tempdb, and resource, an Oracle database requires at least three system tablespaces for normal operation. These are system, sysaux, and temp. The master and resource databases centrally store all the information required by the SQL server itself, including system configurations, database lists and file paths, endpoints, connection servers, and user accounts (or "Logon" Information). System-level objects are stored in the read-only database resource. In Oracle, the system tablespace is equivalent to the master database, and the system tablespace containsData Dictionary)That is, the metadata of Oracle itself (metadata). The data dictionary here can be compared with the resource database in SQL Server. Here you may have guessed: if the system does not exist or is damaged, the Oracle database cannot be opened. For an SQL server instance, the model database is used as a "template" for all newly created databases in this instance. any modifications to the model database will be reflected in other databases created later. There is no such template in Oracle, but when you create a tablespace, you can specify it as a permanent tablespace or other tablespaces similar to temp and undo, permanent tablespace is used to save user data. SQL Server's tempdb is used as the "test site" for the entire instance. Every time the instance is restarted, tempdb will be re-created. Oracle's temp tablespace functions similarly: it is used to include intermediate results of large sorting operations. Of course, SQL Server's tempdb can also be used to saveRow Versioning)The required information. When the row version is enabled, the row version feature ensures that the database engine can retain each modification record of the Data row, the modified version will be stored in the version library in tempdb. Generally, the query will return the last submitted version on a data row, when a read operation that uses a specific isolation level dependent row version no longer blocks other transactions that modify the same data, this is because the read operation does not use a shared lock on the Data row. However, this feature must be enabled independently on a single database. Oracle uses a separate tablespace-the famous undo tablespace-to achieve the same purpose. The undo tablespace stores a copy of the read consistency of the data block modified by the DML statement. When the user starts to modify the data, the data block before the modification will be saved to the Undo tablespace. When another user needs to query the data, the read consistency version is actually found in the Undo tablespace. Unlike SQL Server's row version, Oracle's undo does not need to be enabled-because it is part of Oracle's parallel access mechanism. The last SQL Server MSDB database to be introduced, which must be operated by the SQL Server proxy service. The SQL Server Agent schedules tasks, warnings, copies, logs, and many other things. the normal operation of the agent service is inseparable from the MSDB database. Oracle does not specify what corresponds to mdsb data. The sysaux tablespace is also a system tablespace, which is created during the installation process (for example, the database creation process) and stores information such as Oracle AWR (automatic workload repository) information, multi-dimensional data, multimedia data, XML database, and so on.