Okay!! after successful installation of oracle database express edition, the next thing is to view the data from famous database tools although oracle sql developer was very easy to connect but like most of developer me too prefer to stay with TOAD with oracle database. I dont know why may be I am using it since long time.
Let's do the step by step to connect the toad with oracle autonomous database on local network or even on oracle cloud.
What is Oracle Autonomous Database
It is a self-driving database which you can work without almost no DBA-initiated management. The Auto patching, auto upgrades , auto backups, auto scaling, and may other database management related activates or tasks
There are two type of version available with oracle autonomous database
- Autonomous Transaction Processing (ATP)
- Autonomous Data Warehouse (ADW)
All the features mentioned above is just for database maintenance related still someone needs to access the database and do the operational things such as adding data, creating database objects and other tasks like that. It can not be done without a client tool although the browser based tools could be use.
Setting up TOAD with Oracle Database
In below steps we will show the steps to configure QUEST TOAD for Oracle Autonomous Database over cloud or on you local machine or server.
Do the below task to connect toad with oracle autonomous database
- environment setup , its may includes creating an Oracle Cloud Platform account(optional) , and installing Toad for Oracle
- creation of Oracle Autonomous Database Instance
- Download then install Connection Credentials
- Connection in Toad for Oracle
- check the Oracle Autonomous Database with Toad
Step 1: Setting the environment
An Oracle Cloud account might be required and an account may be created at Oracle Cloud Free Tier. Get the Always Free option for ATP and ADW from there.
Step 2: Creation of an autonomous database instance
To create an Oracle Autonomous Database Instance, log in to the Oracle Cloud Services console with the link sent when an Oracle Cloud Services account is created. Click on the button/link Create Instance. In the Create Instance window click on Create button for Autonomous Transaction Processing
Launch the Create Autonomous Database wizard from the Autonomous Databases table with the Create Autonomous Database button
Input basic information for creating a new instance, which are a Compartment, Title/Display Name and Name of Database . Just note that , Oracle Cloud Infrastructure compartment is a container for grouping and organizing related resources together.
Choose workload type as Transaction Processing. Choose deployment type as Shared Infrastructure
Specify administrator credentials . The Username is ADMIN by default and cannot be changed. Specify a password in the Password field, and confirm password in the Confirm Password field.
Select network access type as Allow secure access from everywhere.
Tags may be set with Show Advanced Options link. Choose License type as License Included. Click on Create Autonomous Database
A new ATP instance gets created as indicated by the AVAILABLE state
A new database instance gets listed in the Autonomous Databases table
Step 3: Download and install Connect Credentials
Downloading and configuring credentials
For connecting to Oracle Autonomous Database from a local machine, Oracle Instant Client needs to be installed locally. Download Instant Client version 18.5 (not the latest 19.8). Instant Client for Microsoft Windows (x64) is used in the article. Click on the link for version 18.5 and download and extract the instantclient-basic-windows.x64-18.5.0.0.0dbru.zip file. Create a new directory path C:\instantclient_18_5, and copy the contents of the extracted zip file to the new C:\instantclient_18_5 directory. Sub-directories within the extracted directory path are not to be copied, and only the files and directories from the last folder (C:\instantclient_18_5) in the extracted directory path are to be copied to the new C:\instantclient_18_5 directory. Create sub-directory path C:\instantclient_18_5\network\admin. Set two environment variables on the local Windows machine as discussed in Table 1.
Table 1. Environment Variables
Download and install/configure ATP database credentials. Click on DB Connection button in the Oracle Autonomous Database Detailspage.
In the Database Connection window click on Download Wallet in the Download Client Credentials (Wallet) section.
In the Download Wallet window specify a password that is needed by some clients to connect to the database. Click on Download. Even though Toad for Oracle doesn’t require a password and auto-login is used just with the wallet, a new password needs to be specified to be able to download the wallet zip file.
The client credentials zip file or wallet gets downloaded. Click on the Close button.
Extract the files in the wallet zip file such that the tnsnames.ora, sqlnet.ora and other files are in the C:\instantclient_18_5\network\admin directory.
Step 4: Creating a Connection in Toad for Oracle
Before connecting verify the following:
- Toad for Oracle is installed
- Oracle Autonomous Database instance is created
- The Oracle Instant Client 18.5 is installed
- The environment variables ORACLE_HOME and TNS_ADMIN are set
- The Oracle Autonomous Database instance credentials zip file is downloaded and extracted
Launch Toad for Oracle. The Toad for Oracle Database Login window gets displayed (Figure 16).
With the TNS tab selected specify User/Schema as ADMIN (Figure 17). Specify password for ADMIN. From the Database drop-down select one of the database services; these database services are pre-configured in the tnsnames.ora file that is downloaded in the wallet zip file.
Select Connect as: as Normal, and select Connect Using as Instant Client – 18.5.0.0, and click on Connect
A new connection gets created and a SQL worksheet or Editorassociated with the connection gets opened.
Multiple connections may be created simultaneously. To create another connection click on New Connection, which launches a new Login window.
A second connection and associated SQL worksheet, or Editor,
Step 5: Explore the Oracle Autonomous Database with Toad for Oracle Database Browser
Using the Database Browser
Use the Database Browser to browse the Oracle Autonomous Database instance. Click on Database Browser in the toolbar
The Database Browser gets launched
Even though one database instance is created multiple database services get provisioned for different types of use as discussed at Predefined Database Service Names for Autonomous Transaction Processing. All the Database instance service names are listed in the Database Browser.
Double-click on a database instance service (orcldb_high) icon to browse the database. In the LOGIN dialog, specify Username, Password, and click on OK. The Overview tab (Figure 24) displays Graphs sub-tab for the System Global Area (SGA), Shared Pool, Hit rates, and Event waits graphs. Select the Grid sub-tab to display the same information in a grid.
The Instance tab displays information such as SID, Version, Startup time, Status, Parallel status, number of threads, Archiver status, and instance role.
The Database tab displays information about the database such as SID, DBID, Log mode, Controlfile type, and Open reset logs status.
The Options tab displays information about Parameters such as ASM Proxy Instance, Advanced Analytics, Advanced Compression, Advanced replication, Automatic Storage Management, Backup Encryption, Basic Compression, Block Change Tracking, Change Data Capture, and Connection pooling, among others. The Parameters tab displays the settings for the instance parameters such as noncedb_compatible, target_pdbs, and cdb_cluster among others. The Sessions tab lists all the sessions, and the Top Sessions tab lists the top sessions. The RBS Activity tab lists information about the rollback segments. The Space Usage tab displays the space usage for each tablespace. The Datafile IO tab displays information about the Datafiles IO for each tablespace including Datafile name, total IO, and total block IO. The Redo Logs tab displays information about the redo logs by date. The Directories lists all the database directories.
Tablespaces node lists all the tablespaces including their status and size. Other instance nodes for Database Links, Editions, Flashback Archives, Profiles, Resource Groups, Resource Plans, Roles, Rollback Segments, Scheduler Job Classes, Scheduler Windows, Scheduler Window Groups, Schema Objects, System Privileges, Unified Audit Policies, and Users are also provided.
In a continuation article we shall discuss using Toad for Oracle to create a table in the Oracle Autonomous Database, add data, run an SQL query, export dataset to an Excel file, and create a report among other
0 Comments