Посмотреть базу данных oracle
This chapter describes how to administer Oracle Database for Windows.
This chapter contains these topics:
43.1 About CDB and PDB Information in Views
In a multitenant container database (CDB), the metadata for data dictionary tables and view definitions is stored only in the root. However, each pluggable database (PDB) has its own set of data dictionary tables and views for the database objects contained in the PDB.
Because each PDB can contain different data and schema objects, PDBs can display different information in data dictionary views, even when querying the same data dictionary view in each PDB. For example, the information about tables displayed in the DBA_TABLES view can be different in two different PDBs, because the PDBs can contain different tables. An internal mechanism called a metadata link enables a PDB to access the metadata for these views in the root.
If a dictionary table stores information that pertains to the CDB as a whole, instead of for each PDB, then both the metadata and the data displayed in a data dictionary view are stored in the root. For example, Automatic Workload Repository (AWR) data is stored in the root and displayed in some data dictionary views, such as the DBA_HIST_ACTIVE_SESS_HISTORY view. An internal mechanism called an object link enables a PDB to access both the metadata and the data for these types of views in the root.
Oracle Database Concepts for more information about dictionary access in containers, metadata links, and object links
43.5 Determining Whether a Database Is a CDB
You can query the CDB column in the V$DATABASE view to determine whether a database is a CDB or a non-CDB. The CDB column returns YES if the current database is a CDB or NO if the current database is a non-CDB.
To determine whether a database is a CDB:
In SQL*Plus, connect to the database as an administrative user.
Query the V$DATABASE view.
Example 43-1 Determining Whether a Database is a CDB
Starting and Shutting Down a Database with SQL*Plus
These instructions assume that a database instance has been created.
Directory path examples in this chapter follow Optimal Flexible Architecture (OFA) guidelines. If you specified non-OFA compliant directories during installation, then your directory paths will differ. See Appendix B, "Optimal Flexible Architecture", in Oracle Database Installation Guide for Microsoft Windows for more information.
To start or shut down Oracle Database:
Go to your Oracle Database server.
Start SQL*Plus at the command prompt:
Connect to Oracle Database with username SYSDBA :
To start a database, enter:
This command uses the initialization parameter file specified in path \ filename . To start a database using a file named init2.ora located in
you would enter:
If no PFILE is specified, then the command looks for an SPFILE in ORACLE_HOME \database . If the command finds one, then the command uses it to start the database. If it does not find an SPFILE , then it uses the default initialization parameter file located in ORACLE_BASE \ADMIN\db_name\pfile .
To stop a database, enter:
where mode is normal , immediate , or abort .
In a normal shutdown, Oracle Database waits for all currently-connected users to disconnect and disallows any new connections before shutting down. This is the default mode.
In an immediate shutdown, Oracle Database terminates and rolls back active transactions, disconnects clients, and shuts down.
In an abort shutdown, Oracle Database terminates active transactions and disconnects users; it does not roll back transactions. The database performs automatic recovery and rollback the next time it is started. Use this mode only in emergencies.
Chapter 2, "Database Tools on Windows" for a list of other tools that can start Oracle Database and this guide for information on options you can specify when starting your database.
43.3 About Viewing Information When the Current Container Is the Root
When the current container is the root, a common user can view data dictionary information for the root and for PDBs by querying container data objects.
A container data object is a table or view that can contain data pertaining to the following:
One or more containers
The CDB as a whole
One or more containers and the CDB as a whole
Container data objects include V$ , GV$ , CDB_ , and some Automatic Workload Repository DBA_HIST* views. A common user's CONTAINER_DATA attribute determines which PDBs are visible in container data objects.
In a CDB, for every DBA_ view, there is a corresponding CDB_ view. All CDB_ views are container data objects, but most DBA_ views are not.
Each container data object contains a CON_ID column that identifies the container for each row returned. Table 43-1 describes the meanings of the values in the CON_ID column.
Table 43-1 CON_ID Column in Container Data Objects
The data pertains to the entire CDB
The data pertains to the root
The data pertains to the seed
The data pertains to a PDB
Each PDB has its own container ID.
The following views behave differently from other [G]V$ views:
When queried from the root, these views return instance-wide data, with 0 in the CON_ID column for each row returned. However, you can query equivalent views that behave the same as other container data objects. The following views can return specific data for each container in a CDB: [G]V$CON_SYSSTAT , [G]V$CON_SYS_TIME_MODEL , [G]V$CON_SYSTEM_EVENT , and [G]V$CON_SYSTEM_WAIT_CLASS .
When querying a container data object, the data returned depends on whether PDBs are open and on the privileges granted to the user running the query.
In an Oracle Real Application Clusters (Oracle RAC) environment, the data returned by container data objects might vary based on the instance to which a session is connected.
In a non-CDB, all CON_ID columns in container data objects are 0 (zero).
Oracle Database Concepts for a conceptual overview of container data objects
Oracle Database Security Guide for detailed information about container data objects
Oracle Database Service Naming Conventions for Multiple Oracle Homes
Oracle Database for Windows lets you have multiple Oracle homes on a single computer. This feature, described in Appendix B, "Optimal Flexible Architecture", in Oracle Database Installation Guide for Microsoft Windows , affects Oracle services naming conventions. As you perform installations into Oracle home directories:
You must accept default Oracle home name provided or specify a different name for each Oracle home directory.
You are prompted to give a system identifier and global database name for each database installation.
43.4 Views for a CDB
You can query a set of views for information about a CDB and its PDBs.
Table 43-2 describes data dictionary views that are useful for monitoring a CDB and its PDBs.
Table 43-2 Views for a CDB
Container data objects, including:
Container data objects can display information about multiple PDBs. Each container data object includes a CON_ID column to identify containers.
There is a CDB_ view for each corresponding DBA_ view.
Displays information about the PDBs associated with the CDB, including the status of each PDB.
Displays the permanent properties of each container in a CDB.
Displays the history of each PDB.
Displays information about the user-level and object-level CONTAINER_DATA attributes specified in the CDB.
Displays the PDBs and instances in the Workload Repository.
Displays information about the current saved PDB states in the CDB.
Displays information about all the CDB resource plans.
Displays information about all the CDB resource plan directives.
Contains descriptions of reasons for PDB alerts.
Displays information about database objects, and the SHARING column shows whether a database object is a metadata-linked object, an object-linked object, or a standalone object that is not linked to another object.
Displays information about database services, and the PDB column shows the name of the PDB associated with each service.
The CONTAINER_DATA column shows whether the view or table is a container data object.
The COMMON column shows whether a user is a common user or a local user.
The COMMON column shows whether a role or privilege is commonly granted or locally granted.
The ORIGIN_CON_ID column shows the ID of the container from which the row originates.
Displays information about the database from the control file. If the database is a CDB, then CDB-related information is included.
Displays information about the containers associated with the current CDB, including the root and all PDBs.
Displays information about the PDBs associated with the current CDB, including the open mode of each PDB.
Displays displays information about all PDB incarnations. Oracle creates a new PDB incarnation whenever a PDB is opened with the RESETLOGS option.
Displays information about initialization parameters, and the ISPDB_MODIFIABLE column shows whether a parameter can be modified for a PDB.
Viewing and Hiding the Password File
The password file is not automatically hidden. It can be made invisible and visible again from two different locations:
The password file must be visible before it can be moved, copied, or deleted.
To see the password file, enter:
The password file is displayed as PWD sid .ora :
To make the password file invisible, enter:
To see the effect of the change, enter:
The password file is now hidden:
To make the password file visible again, enter:
To make the password file invisible or visible again:
Navigate to directory ORACLE_HOME \database .
Right-click PWD sid .ora .
The PWD sid .ora Properties dialog opens.
In Attributes , check or clear the checkbox next to Hidden .
To view or hide an invisible password file:
Navigate to directory ORACLE_BASE \ ORACLE_HOME \database .
Choose Folder Options from the View main menu.
Choose the View tab.
To view an invisible password file, choose Show hidden files and folders .
To hide a visible password file, choose Do not show hidden files and folders .
Auto-starting Oracle Database Services
Oracle Database services can be set to start automatically whenever the Windows computer is restarted. You can turn auto-start on or off from two different locations:
To use the Control Panel to configure when and how Oracle Database is started:
Access your Windows Services dialog.
Your operating system documentation for instructions
Select service OracleServiceSID and click Startup .
Choose Automatic from the Startup Type field.
Click Close to exit the Services dialog.
Oracle Administration Assistant for Windows
To automatically start Oracle Database services from Oracle Administration Assistant for Windows:
From the Start menu, select Programs , then select Oracle - HOME_NAME , then select Configuration and Migration Tools and then select Administration Assistant for Windows .
Right-click the SID .
where SID is a specific instance name, such as orcl .
Choose Startup/Shutdown Options .
Choose the Oracle Service tab.
Choose Automatic in Oracle Service Startup Type .
Managing Oracle Database Services
This section tells you how to manage the services that Oracle Database installs on your computer.
This section provides information on the following:
Starting Multiple Instances
Start the service for each instance using ORADIM or the Services dialog of the Control Panel.
At the command prompt set the ORACLE_SID configuration parameter to the SID for the first instance to run:
where SID is the name of the Oracle Database instance.
Connect AS SYSDBA :
Start up the first instance:
where ORACLE_BASE is c:\app\oracle\product\11.2.0 (unless you changed it during installation) and db_name is the name of the instance.
Repeat Steps 2-5 for the other instances to run.
Stopping Oracle Database Services
On occasion (for example, when re-installing Oracle Database), you must stop Oracle Database services. You can stop Oracle Database services from three different locations:
You can stop Oracle Database in normal, immediate, or abort mode when you stop OracleService SID . See "Starting and Shutting Down a Database Using Services" for information on registry parameters that enable you to do this.
To stop Oracle Database services from the Control Panel:
Access your Windows Services dialog.
Your operating system documentation for instructions
Select Oracle HOME_NAME TNSListener and click Stop .
Oracle HOME_NAME TNSListener is stopped.
Select OracleService SID and click Stop .
OracleService SID is stopped.
To stop Oracle Database services from the command prompt, enter:
where service is a specific service name, such as OracleServiceORCL .
Oracle Administration Assistant for Windows
To stop Oracle Database services from Oracle Administration Assistant for Windows:
From the Start menu, select Programs , then select Oracle - HOME_NAME , then select Configuration and Migration Tools and then select Administration Assistant for Windows .
Right-click the SID .
where SID is a specific instance name, such as orcl .
Click Stop Service .
This stops service OracleServiceORCL.
Starting Oracle Database Services
Oracle Database services must be started for you to use Oracle Database and its products. You can start Oracle Database services from three different locations:
You can start Oracle Database when you start OracleService SID . See "Starting and Shutting Down a Database Using Services" for information on registry parameters that enable you to do this.
To start Oracle Database services from the Control Panel:
Access your Windows Services dialog.
Your operating system documentation for instructions
Find the service to start in the list, select it, and click Start .
If you cannot find OracleService SID in the list, then use ORADIM to create it.
Click Close to exit the Services dialog.
To start Oracle Database services from the command prompt, enter:
where service is a specific service name, such as OracleServiceORCL.
Oracle Administration Assistant for Windows
To start Oracle Database services from Oracle Administration Assistant for Windows:
From the Start menu, select Programs , then select Oracle - HOME_NAME , then select Configuration and Migration Tools and then select Administration Assistant for Windows .
Right-click the SID .
where SID is a specific instance name, such as orcl .
Click Start Service .
This starts service OracleServiceORCL .
43.6 Viewing Information About the Containers in a CDB
The V$CONTAINERS view provides information about all of the containers in a CDB, including the root and all PDBs.
To view this information, the query must be run by a common user whose current container is the root. When the current container is a PDB, this view only shows information about the current PDB.
To view information about the containers in a CDB:
In SQL*Plus, ensure that the current container is the root.
Query the V$CONTAINERS view.
Example 43-2 Viewing Identifying Information About Each Container in a CDB
Connecting Remotely to the Database as SYS
When connecting to the starter database from a remote computer as SYS , you must use a different password from the one described in Oracle Database Installation Guide for Microsoft Windows when logging on with SYSDBA privileges. This is because the password file enables database access in this situation and it requires the password oracle for this purpose.
Automatically Encrypted Database Passwords
With Oracle Database, the password used to verify a remote database connection is automatically encrypted. Whenever a user attempts a remote login, Oracle Database encrypts the password before sending it to the remote database. If the connection fails, then the failure is noted in the operating system audit log.
Configuration parameter ORA_ENCRYPT_LOGIN is retained for backward compatibility and is set to true by default. See Chapter 15, "Configuration Parameters and the Registry" for instructions on adding and setting configuration parameters in the registry.
Creating and Populating Password Files
Use Password Utility to create password files. Password Utility is automatically installed with Oracle Database utilities. Password files are located in directory ORACLE_HOME \database and are named PWD sid .ora , where SID identifies the Oracle Database instance. Password files can be used for local or remote connections to Oracle Database.
To create and populate a password file:
Create a password file with the Password Utility:
FILE specifies the password file name.
SID identifies the database instance.
ENTRIES sets maximum number of entries in password file. This corresponds to maximum number of distinct users allowed to connect to the database simultaneously with either the SYSDBA or the SYSOPER DBA privilege .
Set initialization parameter file parameter REMOTE_LOGIN_PASSWORDFILE to exclusive , shared , or none .
The value exclusive specifies that only one instance can use the password file and that the password file contains names other than SYS . In search of the password file, Oracle Database looks in the registry for the value of parameter ORA_SID_PWFILE . If no value is specified, then it looks in the registry for the value of parameter ORA_PWFILE , which points to a file containing usernames, passwords, and privileges. If that is not set, then it uses the default:
The default value is shared . It specifies that multiple instances (for example, an Oracle RAC environment) can use the password file. However, the only user recognized by the password file is SYS . Other users cannot log in with SYSOPER or SYSDBA privileges even if those privileges are granted in the password file. The shared value of this parameter affords backward compatibility with earlier Oracle releases. Oracle Database looks for the same files as it does when the value is exclusive .
The value none specifies that Oracle Database ignores the password file and that authentication of privileged users is handled by the Windows operating system.
Connect AS SYSDBA :
For an ASM instance, connect AS SYSASM :
Start Oracle Database:
Grant appropriate privileges to each user. Users who must perform database administration, for example, would be granted privilege SYSDBA :
For an ASM instance:
If the grant is successful, then the following message displays:
This adds scott to the password file and enables scott to connect to the database with SYSDBA privileges. Use SQL*Plus to add or delete usernames, user passwords, and user privileges in password files.
Copying or manually moving password files may result in ORADIM being unable to find a password to start an instance.
Archiving Redo Log Files
If you installed Oracle Database through the Typical installation, then it is created in NOARCHIVELOG mode. If you created your database through the Custom option of Database Configuration Assistant, then you had the choice of either ARCHIVELOG or NOARCHIVELOG .
In NOARCHIVELOG mode, redo logs are not archived. Setting your archive mode to ARCHIVELOG and enabling automatic archiving causes redo log files to be archived. This protects Oracle Database from both instance and disk failure.
"Managing Archived Redo Logs" in Oracle Database Administrator's Guide for more information about the archiving modes and the archiving process.
Как запросить базу данных Oracle, чтобы отобразить имена всех таблиц в ней?
Это предполагает, что у вас есть доступ к представлению DBA_TABLES словаря данных. Если у вас нет этих привилегий, но они нужны, вы можете запросить, чтобы администратор БД явно предоставил вам привилегии для этой таблицы, или чтобы администратор БД предоставил вам SELECT ANY DICTIONARY привилегию или SELECT_CATALOG_ROLE роль (любая из которых позволит вам запросить любую таблицу словаря данных). ). Конечно, вы можете захотеть исключить определенные схемы, такие как SYS и SYSTEM имеющие большое количество таблиц Oracle, которые вам, вероятно, не нужны .
Кроме того, если у вас нет доступа DBA_TABLES , вы можете просмотреть все таблицы, к которым у вашей учетной записи есть доступ, через ALL_TABLES представление:
Хотя это может быть подмножеством таблиц, доступных в базе данных ( ALL_TABLES показывает информацию обо всех таблицах, к которым у вашего пользователя есть доступ).
Если вас интересуют только те таблицы, которыми вы владеете, а не те, к которым у вас есть доступ, вы можете использовать USER_TABLES :
Поскольку он USER_TABLES содержит информацию только о тех таблицах, которыми вы владеете, у него нет OWNER столбца - владельцем по определению является вы.
Oracle также имеет ряд устаревших данных словаря views-- TAB , DICT , TABS и CAT для example-- , которые можно было бы использовать. В целом, я бы не советовал использовать эти устаревшие представления, если вам абсолютно не нужно перенести свои сценарии в Oracle 6. Oracle не изменяла эти представления в течение длительного времени, поэтому у них часто возникают проблемы с объектами более новых типов. Например, TAB и CAT представления, и представления показывают информацию о таблицах, которые находятся в корзине пользователя, в то время как [DBA|ALL|USER]_TABLES все представления отфильтровывают их. CAT также показывает информацию о материализованных журналах представлений с TABLE_TYPE «TABLE», что вряд ли будет тем, что вы действительно хотите. DICT объединяет таблицы и синонимы и не говорит вам, кто владеет объектом.
Для нашего приложения у нас есть файл конфигурации для подключения к базе данных Oracle (URL, имя пользователя, пароль).
Запуск приложения занимает 2-5 минут. Если с базой данных что-то не так, я вижу это в логах, но мне нужно подождать 2-5 минут. Это много времени.
Поэтому я хочу быстро и просто найти способ определить, что с базой данных все в порядке и он-лайн.
Ты хоть представляешь, как я могу это сделать?
Если вы выполните следующий запрос:
ps -ef | grep pmon
Процесс PMON (монитор процесса) проверяет все остальные фоновые процессы. Затем вы должны проверить журнал предупреждений для дальнейшего расследования.
Первое и первое: вам нужно знать имя пользователя и пароль для подключения к базе данных на шаге 2
Проверьте процесс оракула:
если команда в любом случае возвращает выходные данные, т.е. если в вашей среде запущен процесс pmon / oracle, то база данных работает.
Перейти ORACLE_HOME/bin и запустить:
Если после входа в систему вы получаете ошибки, то база данных не запускается:
Вы должны использовать / написать скрипт, который будет подключаться к серверу и посмотреть, подключена ли ваша база данных. Здесь ребята из Oracle помогут. Просто запустите его из сценария пакетной обработки / оболочки (см. Запуск сценариев из командной строки SQL ) и посмотрите его вывод. Если все в порядке, тогда у вас есть ответ.
Или просто попробуйте с клиентом SQL * Plus.
Возможно, было бы целесообразно выполнить фактический запрос к базе данных. Это просто гарантирует, что вы можете подключиться и запросить. Я бы сделал это очень простым и быстрым, например:
PMON проверит все процессы bg
Кроме того, мы можем проверить, работает база данных или нет.
Чтобы проверить общий статус базы данных, я рекомендую:
Проверьте, запущены ли процессы базы данных. Например, из оболочки Unix, работающей:
покажет запущенные базы данных, время запуска и то, какие пользователи Oracle запустили каждую из них.
Проверьте, работают ли слушатели, используя $ ps -ef | grep tns и $ lsnrctl status LISTENER
Выбор gv$resource_limit покажет, достигла ли база данных какого-либо настроенного предела.
Чтобы проверить, были ли какие-либо ошибки базы данных, вы должны проверить alert.log .
С помощью этой команды show databases; Я вижу базы данных в в MySQL.
Как показать доступные базы данных в Oracle?
вы можете думать о "базе данных" MySQL как о схеме / пользователе в Oracle. Если у вас есть привилегии, вы можете запросить DBA_USERS просмотр для просмотра списка схем.
SELECT NAME FROM v$database; показывает имя базы данных в Oracle
Oracle не имеет простой модели базы данных, такой как MySQL или MS SQL Server. Я нахожу, что самое близкое-запросить табличные пространства и соответствующих пользователей в них.
например, у меня есть табличное пространство DEV_DB со всеми моими фактическими "базами данных"внутри них:
также можно запросить пользователей во всех табличных пространствах:
или в определенном табличном пространстве (используя my Dev_db табличное пространство в качестве примера):
может быть, вы могли бы использовать этот вид, но я не уверен.
но я думаю, что он покажет вам только информацию о текущей БД.
другой вариант, если БД работает в Linux. что бы это могло быть:
Я не ясно об этом, но обычно один сервер имеет одну базу данных (со многими пользователями), если вы создаете много баз данных, это означает, что вы создаете много экземпляров, слушателей. также. Таким образом, вы можете проверить свой LISTENER для установления его личности.
в моем тестировании я создал 2 базы данных ( dbtest и dbtest_1 ) поэтому, когда я проверяю свой статус слушателя, он выглядит так:
.
состояние слушателя
.
(описание=(адрес=(протокол = tcp) (хост=10.10.20.20) (порт=1521)))
Резюме Услуги.
сервис "dbtest" имеет 1 экземпляр(ы).
экземпляр "dbtest", состояние готово, имеет 1 обработчик(ы) для этой службы.
сервис "dbtest1XDB" имеет 1 экземпляр(ы).
экземпляр "dbtest1", состояние готово, имеет 1 обработчик(ы) для этой службы.
сервис "dbtest_1" имеет 1 экземпляр(ы).
экземпляр "dbtest1", состояние готово, имеет 1 обработчик(ы) для этой службы. Команда выполнена успешно
You can view information about CDBs and PDBs with SQL*Plus.
Starting and Shutting Down a Database Using Services
You can start or shut down Oracle Database by starting or stopping service OracleService SID in the Control Panel. Starting OracleService SID is equivalent to using the STARTUP command or manually entering:
Stopping OracleService SID is equivalent to using the SHUTDOWN command or manually entering:
You can enable starting and stopping Oracle Database through OracleService SID two different ways:
Oracle Administration Assistant for Windows
To start or stop a database using Oracle Database services from Oracle Administration Assistant for Windows:
From the Start menu, select Programs , then select Oracle - HOME_NAME , then select Configuration and Migration Tools and then select Administration Assistant for Windows .
Right-click the SID .
where SID is a specific instance name, such as ORCL .
Choose Startup/Shutdown Options .
Choose the Oracle Instance tab.
Select Start up instance when service is started , Shut down instance when service is stopped , or both.
Setting Registry Parameters
To start or stop Oracle Database through Oracle Database Services, set the following registry parameters to the indicated values:
ORA_ SID _AUTOSTART
When set to true , the default value, this parameter causes Oracle Database to start when OracleService SID is started.
ORA_ SID _PFILE
This parameter sets the full path to the initialization parameter file. If this entry is not present, then oradim will try to start the database with an SPFILE or PFILE from ORACLE_HOME \database .
When set to true , this parameter enables the selected instance of Oracle Database to be shut down when OracleService SID is stopped. This includes any database in the current Oracle home. Default value is false .
ORA_ SID _SHUTDOWN
When set to true , the default value, this parameter causes the instance of Oracle Database identified by the SID value to shut down when OracleService SID is stopped manually‘Äîusing either the Control Panel or Net stop command.
If ORA_SHUTDOWN or ORA_SID_SHUTDOWN is set to false , then manually shutting down OracleService SID will still shut down Oracle Database. But it will be an abnormal shutdown, and Oracle does not recommend it.
The following two registry parameters are optional:
ORA_ SID _SHUTDOWNTYPE
This parameter controls database shutdown mode. Set it to a ( abort ), i ( immediate ), or n ( normal ). Default mode is i ( immediate ) if you do not set this parameter.
ORA_ SID _SHUTDOWN_TIMEOUT
This parameter sets maximum time to wait before the service for a particular SID stops.
The registry location of these required and optional parameters is determined by the number of Oracle home directories on your computer. If you have only one Oracle home directory, then these parameters belong in:
If you have multiple Oracle home directories, then these parameters belong in:
where ID is incremented for each additional Oracle home directory on your computer.
If you use ORADIM to create or edit instances, then it automatically sets the relevant registry parameters to their appropriate values.
Chapter 15, "Configuration Parameters and the Registry" for instructions on adding and editing registry parameters
Starting or Stopping OracleService SID from the Control Panel
To start the database, start OracleService SID .
This automatically starts ORADIM and issues the - STARTUP command using the initialization parameter file identified by ORA_ SID _PFILE .
To stop the database, stop OracleService SID .
This automatically starts ORADIM, which issues the -SHUTDOWN command in the mode indicated by ORA_ SID _SHUTDOWNTYPE , and shuts down Oracle Database.
Your operating system documentation for instructions on starting and stopping services.
43.2 About Viewing Information When the Current Container Is a PDB
When the current container is a PDB, a user can view data dictionary information for the current PDB only.
To an application connected to a particular PDB, the data dictionary appears as it would for a non-CDB. The data dictionary only shows information related to the PDB. Also, in a PDB, CDB_ views only show information about database objects visible through the corresponding DBA_ view.
Читайте также: