&nbsp;
Welcome to Oracle DBA Tutorials. The objective of these tutorials is to provide in depth understand of Oracle DBA.
In addition to free Oracle DBA Tutorials, we will cover common interview questions, issues and how to&rsquo;s of Oracle DBA.
<h2 style="text-align: justify;">Introduction:</h2>
An Oracle database is a collection of data treated as a unit. The purpose of a database is to store and retrieve related information. A database server is the key to solving the problems of information management. In general, a server reliably manages a large amount of data in a multi user environment so that many users can concurrently access the same data. All this is accomplished while delivering high performance. A database server also prevents unauthorized access and provides efficient solutions for failure recovery.
Oracle Database is the first database designed for enterprise grid computing, the most flexible and cost effective way to manage information and applications. Enterprise grid computing creates large pools of industry-standard, modular storage and servers. With this architecture, each new system can be rapidly provisioned from the pool of components. There is no need for peak workloads, because capacity can be easily added or reallocated from the resource pools as needed.
The database has logical structures and physical structures. Because the physical and logical structures are separate, the physical storage of data can be managed without affecting the access to logical storage structures.
<blockquote>
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Inclined to build a profession as Oracle DBA Developer? Then here is the blog post on, explore <a title="Oracle DBA Training" href="oracle-dba-training" target="_blank" rel="noopener">Oracle DBA Training</a>
</blockquote>
<h3 style="text-align: justify;">Operating Systems:</h3>
A 64-bit Linux version of Oracle will not install on a 32-bit Linux or Windows 64-<img class="alignnone size-full wp-image-21913" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2016/06/Capture.33.png" alt="Oracle DBA Tutorials" width="537" height="272" /><img class="alignnone size-full wp-image-21914" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2016/06/Capture.331.png" alt="Oracle DBA Tutorials" width="537" height="272" /><img class="alignnone size-full wp-image-21915" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2016/06/Capture.332.png" alt="Oracle DBA Tutorials" width="537" height="272" />bit system, for example. The Oracle release notes provide information about where to find the compatibility matrix and the system requirements for the server.
<img class=" wp-image-21912 aligncenter" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2016/06/Capture.32.png" alt="Oracle DBA Tutorials" width="593" height="249" />
The disk space requirements depend on the components installed, but the base product alone does require more disk space for 64-bit operating systems. You can use the following command-line option to run the installer with parameters to perform just the system prerequisites checks, without continuing to install Oracle:
E:Oracle11gR2database&gt; setup.exe &ndash;executeSysPrereqs
The results can be viewed in the prerequisite_results.xml file, which will be in the oraInventory/logs directory.
<h3 style="text-align: justify;">Oracle Homes:</h3>
&nbsp;SQL Server tends to have a default location for the binaries, and Oracle will set up a default as well: the Oracle home directory. This directory location can be set during the installation process, or it can be specified by setting the ORACLE_HOME environment variable before running the installer.
Although the environment variables are set by the Oracle installer for Windows, there might be more than one Oracle home on a server. If this is the case, when using a command line, you will need to set the variable ORACLE_HOME first, or fully qualify the path to the correct Oracle home.
C:&gt; set ORACLE_HOME=d:oracleproduct11.2.0db_1
C:&gt; set ORACLE_SID=orcl
C:&gt; sqlplus
<h3 style="text-align: justify;">User Account for Installation:</h3>
The installer creates the ORA_DBA group automatically on the Windows platform. A standard practice with SQL Server is to create another user that has administrator privileges to install the software and be the owner of the SQL Server services.
By default, the Oracle services will use the Local Service account. By having a separate domain account to manage these services and perform the installation.
<h3 style="text-align: justify;">File System:</h3>
The database software should be installed on the NTFS file system because of the security available for the Oracle home directory, which will contain trace files and database files.
<h3 style="text-align: justify;">Network Connectivity:</h3>
Database is not really meant to be a stand-alone machine, it needs network connectivity. Clustering will have different requirements, but the database server needs to have a primary IP address that is accessible (it doesn&rsquo;t need to be a static IP, unless your environment requires that
If you are using a dynamic configuration (DHCP), a test conducted during the Oracle installer&rsquo;s prerequisite check will fail if Microsoft Loopback Adapter is not the primary network adapter on the system. Here is a quick check for this adapter:
C:&gt; ipconfig /all Ethernet adapter Local Area Connection 2: Connection-specific DBS Suffix . . . . : Description . . . . . . . . . . . . . .: Microsoft Loopback Adapter Physical Address . . . . . . . . . . . : 7A-80-4C-9F-57-5D DHCP Enabled . . . . . . . . . . .. . .: Yes Autoconfiguration Enabled . . . . . . .: Yes
If Microsoft Loop back Adapter is not configured, you can set it up through Add/Remove Hardware in the Control Panel. Select Network Adapters, and add it as a new network adapter.
<h3 style="text-align: justify;">Windows Platform Checklist:</h3>
&nbsp;For Windows installations:
<ul>
<li style="text-align: justify;">Check that the operating system version and Oracle version and edition are correct.</li>
<li style="text-align: justify;">Verify that the hardware requirements are met.</li>
<li style="text-align: justify;">Create an Oracle account with administrator permissions to perform the installation.</li>
<li style="text-align: justify;">Run the prerequisite check and correct any issues found.</li>
</ul>
&nbsp;
<a style="background-color: #e94350; color: #ffffff; padding: 10px 20px;" href="oracle-dba-interview-questions-answers" target="_blank" rel="noopener">Oracle DBA Interview Questions</a>
&nbsp;
<h3 style="text-align: justify;">Linux/Unix Commands:</h3>
&nbsp;The following are some other useful Linux commands:
pwd Shows the current directory.
echo $ORACLE_HOME Shows the value of the variable.
whoami Shows the current user.
ps -ef Shows the list of current processes running on the server.
grep Searches for a name or value in a file or list or process.
chmod Changes permissions for a file or directory.
chgrp Changes the group for permissions of a file or directory.
chown Changes the owner of a file or directory.
The manual pages (man pages) provide parameter options and examples of how to use the commands. To access the man page for a command, just type man and the command at the prompt, as in this example:
<h3 style="text-align: justify;">man grep:</h3>
When you download files for a Linux system, such as patches or software, they might come in a couple of formats, with file names ending in .Z, .gz, .zip, .cpio, or .tar. These are compressed files. You&rsquo;ll need to uncompress these files so that they are usable. The following are sample commands to uncompress the various formats:
<pre>&gt; uncompress *.Z

&gt; unzip *.zip

&gt; gunzip *.gz

&gt; tar -xvf file.tar

&gt; cpio -idmv &lt; file_name</pre>
<h3 style="text-align: justify;">Linux Setup:</h3>
&nbsp;For a Linux system, you need to set up users, adjust permissions and kernel parameters, and make sure the required packages are installed.
<h3 class="sect2" style="text-align: justify;">Overview of Oracle Grid Architecture:</h3>
Grid computing is a new IT architecture that produces more resilient and lower cost enterprise information systems. With grid computing, groups of independent, modular hardware and software components can be connected and rejoined on demand to meet the changing needs of businesses.
The grid style of computing aims to solve some common problems with enterprise IT: the problem of application silos that lead to under utilized, dedicated hardware resources, the problem of monolithic, unwieldy systems that are expensive to maintain and difficult to change, and the problem of fragmented and disintegrated information that cannot be fully exploited by the enterprise as a whole.
<h3 class="sect2" style="text-align: justify;">Overview of Application Architecture:</h3>
There are two common ways to architect a database: client/server or multitier. As internet computing becomes more prevalent in computing environments, many database management systems are moving to a multitier environment.
<div class="sect3" style="text-align: justify;">Client/Server ArchitectureMultiprocessing uses mo<a id="sthref28"></a><a id="sthref29"></a>re than one processor for a set of related jobs. Distributed processing reduces the load on a single processor by allowing different processors to concentrate on a subset of related tasks, thus improving the performance and capabilities of the system as a whole. An Oracle database system can easily take advantage of distributed processing by using its client/server architecture. In this architecture, the database system is divided into two parts: a front-end or a client, and a back-end or a server.
<div class="sect4">
<h3 class="sect4">The Client:</h3>
The client is a database application that initiates a request for an operation to be performed on the database server. It requests, processes, and presents data managed by the server. The client workstation can be optimized for its job. For example, it might not need large disk capacity, or it might benefit from graphic capabilities. Often, the client runs on a different computer than the database server, generally on a PC. Many clients can simultaneously run against one server.</div>
<div class="sect4">The server runs Oracle software and handles the functions required for concurrent, shared data access. The server receives and processes the SQL and PL/SQL statements that originate from client applications. The computer that manages the server can be optimized for its duties. For example, it can have large disk capacity and fast processors.</div>
</div>
<div class="sect3">
<h3 class="sect3" style="text-align: justify;">Multitier Architecture: Application Servers:</h3>
A multitier architecture has the following components:
A client or initiator process that starts an operation
One or more application servers that perform parts of the operation. An application server provides access to the data for the client and performs some of the query processing, thus removing some of the load from the database server. It can serve as an interface between clients and multiple database servers, including providing an additional level of security.
An end or database server that stores most of the data used in the operation
This architecture enables use of an application server to do the following:
<ul>
<li style="text-align: justify;">Validate the credentials of a client, such as a Web browser</li>
<li style="text-align: justify;">Connect to an Oracle database server</li>
<li style="text-align: justify;">Perform the requested operation on behalf of the client</li>
</ul>
</div>
<h3>For Indepth knowledge on Oracle DBA click on:</h3>
<ul>
<li><a href="https://tekslate.com/oracle-service-bus/" target="_blank" rel="noopener">Oracle Service BUS</a></li>
<li><a href="https://tekslate.com/working-flashback-table-oracle-dba/" target="_blank" rel="noopener">Working with Flashback Table in Oracle DBA</a></li>
<li><a href="https://tekslate.com/types-privileges-sysoper-oracle-dba/" target="_blank" rel="noopener">Types of privileges of &lsquo;SYSOPER&rsquo; in Oracle DBA</a></li>
</ul>
&nbsp;

Free E-Learning Oracle DBA Tutorials by Experts

Oracle DBA Tutorials

tutorialsoracle-dba

TOP 50+ Oracle DBA Interview Questions (2023)

<ul>
<li><a style="color: #000000;" href="#interview-questions">Oracle DBA Interview Questions and Answers</a></li>
<li><a style="color: #000000;" href="#database">Oracle Database Interview Questions</a></li>
<li><a style="color: #000000;" href="#basic">Basic Oracle Apps DBA Interview Questions</a></li>
<li><a style="color: #000000;" href="#scenario-based">Oracle DBA Scenario Based Interview Questions</a></li>
<li><a style="color: #000000;" href="#experienced">Oracle DBA Interview Questions for 3 years Experienced</a></li>
</ul>

Oracle DBA Interview Questions

oracle-dba-interview-questions-answers

<h2>Introduction to Oracle Service Bus&nbsp;</h2>
<a href="https://tekslate.com/osb-oracle-service-bus/" target="_blank">Oracle Service Bus</a> (previously known as Weblogic ALSB) is a light weight messaging component of SOA Suite. In addition to providing basic routing and transformation services,&nbsp;OSB&nbsp;also provides advanced capabilities for service abstraction.
As of version 11.1.1.3, OSB can only run on Weblogic platform. Since it runs in a separate JVM, there is an overhead in communicating with rest of<a href="https://tekslate.com/tutorials/oracle-soa-11g/" target="_blank"> SOA</a> Suite components.
Though OSB is similar to Mediator component at the conceptual level, there are specific scenarios when OSB is very useful.
OSB has a built-in feature to modify service end point at the run time. This would be very useful when virtualizing external services, probably hosted by various partners.
OSB can also be used to throttle services, thus limiting the number of invocations. This can be valuable in meeting client service level agreements as well as preventing services from being swamped.
In other cases, Mediator will offer more benefits since it is available in the same JVM as rest of SCA components.
<h3>OSB service invocation overview</h3>
<img class="aligncenter wp-image-1431" title="oracle soa tutorials-OSB service invocation overview" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/11/Slide1161-460x345.png" alt="oracle soa tutorials" width="492" height="369" />
OSB service invocation happens through two different layers: proxy service and business service. Proxy service is an entry point for all service invocations. Ideally, proxy service should present canonical interfaces to clients.
Proxy service then routes an inbound message through a series of steps (aka pipes). Each step can perform distinct operations such as transformation, enrichment, and logging, etc. Eventually this message is routed to Business Service which is just a reference to target service. Similarly, output message from business services follows reverse flow through an outbound pipeline, proxy service and finally to the client.
Input and output pipe lines are also responsible for transforming data from and to a canonical format.
In subsequent lessons, we will explore various features of OSB.
<h3>OSB Sample Application</h3>
In this lesson, we are going to create our first Hello World&nbsp;OSB&nbsp;service. This requires us to create a Business Service, a Proxy Service and import an External Service we created earlier using BPEL.
Installed&nbsp;Hello World BPEL process&nbsp;is a pre-requisite for this lesson. Also, please see&nbsp;Introduction to OSB&nbsp;for an overview of the product.
Login to OSB console using the URL&nbsp;http://localhost:7001/sbconsole. This URL may change based on your server&rsquo;s hostname and port number.
<img class="aligncenter wp-image-1433" title="oracle soa tutorials" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/11/Slide1171-460x345.png" alt="oracle soa tutorials" width="491" height="368" />
Click on &ldquo;Create&rdquo; under Change Center to create an exclusive session for our Helloworld project. This ensures that resulting changes are not in conflict with other users&rsquo; work.
<img class="aligncenter wp-image-1437" title="oracle soa tutorials" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/11/Slide1211-460x345.png" alt="oracle soa tutorials" width="491" height="368" />
Create a project to hold XML artefacts and WSDLs. Click on Project Explorer at the bottom left side of the console. Enter &ldquo;helloworld&rdquo; as the project name.
<img class="aligncenter wp-image-1435" title="oracle soa tutorials" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/11/Slide1191-460x345.png" alt="oracle soa tutorials" width="495" height="371" />
Click on helloworld project to get to the project details page.
<img class="aligncenter wp-image-1436" title="oracle soa tutorials-project detail page" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/11/Slide1201-460x345.png" alt="oracle soa tutorials" width="507" height="380" />
<img class="aligncenter wp-image-1437" title="oracle soa tutorials-project detail page" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/11/Slide1211-460x345.png" alt="oracle soa tutorials" width="493" height="369" />
Create three folders to hold Business Service, Proxy Service, and External Service resources: BusinessService, ProxyService, and ExtService respectively.
<img class="aligncenter wp-image-1438" title="oracle soa tutorials-project detail page" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/11/Slide1221-460x345.png" alt="oracle soa tutorials" width="495" height="371" />
<img class="aligncenter wp-image-1439" title="oracle soa tutorials-project detail page" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/11/Slide1231-460x345.png" alt="oracle soa tutorials" width="493" height="370" />
Create External Service
Lets import WSDL required to invoke HelloWorld external service. Click on ExtService folder link. From Create Resource drop down, select &ldquo;Resource from URL&rdquo; option.
<img class="aligncenter wp-image-1440" title="oracle soa tutorials" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/11/Slide1241-460x345.png" alt="oracle soa tutorials " width="493" height="370" />
Get Hello World BPEL process&rsquo;s WSDL from EM Console.
<img class="aligncenter wp-image-1441" title="oracle soa tutorials" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/11/Slide1251-460x345.png" alt="oracle soa tutorials" width="485" height="364" />
Copy Hello World BPEL Process&rsquo;s WSDL URL for URL/Path field. Enter other field values as shown below. Click on Next.
<img class="aligncenter wp-image-1442" title="oracle soa tutorials" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/11/Slide1261-460x345.png" alt="oracle soa tutorials" width="502" height="377" />
From Load Resources page, we can see that OSB has imported WSDL as well as referenced XSD file. Click on Import button.
<img class="aligncenter wp-image-1443" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/11/Slide1271-460x345.png" alt="oracle soa tutorials" width="487" height="365" />
Import confirmation is shown in the next screen.
<img class="aligncenter wp-image-1444" title="oracle soa tutorials" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/11/Slide1281-460x345.png" alt="oracle soa tutorials" width="492" height="369" />
Create Business Service
Click on BusinessService folder link from Project Explorer on the left side to go back to project folder.
Let us create a Business Service using the imported resources. From Create Resource menu, select Business Service.
<img class="aligncenter wp-image-1445" title="oracle soa tutorials-BusinessService folder" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/11/Slide1291-460x345.png" alt="oracle soa tutorials" width="491" height="368" />
Enter HelloWorldBS for Service Name.
<img class="aligncenter wp-image-1446" title="oracle soa tutorials" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/11/Slide130-460x345.png" alt="oracle soa tutorials" width="492" height="369" />
Click on Browse next to WSDL Web Service to open up WSDL we added in previous steps. Click on HelloWorldBPEL WSDDL link.
<img class="aligncenter wp-image-1447" title="oracle soa tutorials" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/11/Slide1311-460x345.png" alt="oracle soa tutorials" width="489" height="367" />
Select HelloWorldProcess_pt port type and click on Submit.
<img class="aligncenter wp-image-1448" title="oracle soa tutorials" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/11/Slide1321-460x345.png" alt="oracle soa tutorials" width="493" height="370" />
<img class="aligncenter wp-image-1450" title="oracle soa tutorials" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/11/Slide1331-460x345.png" alt="oracle soa tutorials" width="495" height="371" />
Click on Next to go to Transport Configuration page. We can leave the default values. Click on Last button to go to summary page. Click on Save to complete creation of Business Service.
<img class="aligncenter wp-image-1451" title="oracle soa tutorials" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/11/Slide1341-460x345.png" alt="oracle soa tutorials" width="487" height="365" />
From Resource section, we can see that HelloWorldBS business service is successfully created.
<img class="aligncenter wp-image-1452" title="oracle soa tutorials" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/11/Slide1351-460x345.png" alt="oracle soa tutorials" width="497" height="373" />
Create Proxy Service Click on ProxyService folder link from Project Explorer. Under Resources section, select Proxy Service from Create Resource combo box.
<img class="aligncenter wp-image-1453" title="oracle soa tutorials" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/11/Slide1361-460x345.png" alt="oracle soa tutorials" width="489" height="367" />
Enter values as shown below.
<img class="aligncenter wp-image-1454" title="oracle soa tutorials" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/11/Slide137-460x345.png" alt="oracle soa tutorials" width="491" height="368" />
Select Business Service and click on Browse button to see the list of available business services.&nbsp;
<img class="aligncenter wp-image-1455" title="oracle soa tutorials" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/11/Slide138-460x345.png" alt="oracle soa tutorials" width="492" height="369" />
Select HelloWorldBS from the list and click on Submit.
<img class="aligncenter wp-image-1456" title="oracle soa tutorials" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/11/Slide139-460x345.png" alt="oracle soa tutorials" width="491" height="368" />
Click on Last and then Save from summary page to create Proxy Service. We can see HelloWorld proxy service successfully created from Project details page.
<img class="aligncenter wp-image-1457" title="oracle soa tutorials" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/11/Slide140-460x345.png" alt="oracle soa tutorials" width="499" height="374" />
Create Message Flow Since Proxy Service is created based on an existing Business Service, the request is already configured to be routed from proxy service to business service. Let us verify this. Click on Edit Message Flow icon from Actions section.
<img class="aligncenter wp-image-1458" title="oracle soa tutorials" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/11/Slide1411-460x345.png" alt="oracle soa tutorials" width="495" height="371" />
From Edit Message Flow window, we can see the message flow is already established. Click on Cancel to go back to project details page.
<img class="aligncenter wp-image-1459" title="oracle soa tutorials" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/11/Slide1421-460x345.png" alt="oracle soa tutorials" width="493" height="370" />
Let us save our work. Click on Activate button from Change Center. Click on Submit button.
<img class="aligncenter wp-image-1460" title="oracle soa tutorials" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/11/Slide1431-460x345.png" alt="oracle soa tutorials" width="497" height="373" />
Testing Proxy Service Click on ProxyService folder link from Project Explorer.
<img class="aligncenter wp-image-1461" title="oracle soa tutorials" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/11/Slide1441-460x345.png" alt="oracle soa tutorials" width="495" height="371" />
Click on Launch Test Console action as shown below.
<img class="aligncenter wp-image-1462" title="oracle soa tutorials" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/11/Slide1451-460x345.png" alt="oracle soa tutorials" width="487" height="365" />
Leave the default values for all fields except for Payload. Enter orafmwschool as input element value. Click on Execute.
<img class="aligncenter wp-image-1463" title="oracle soa tutorials" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/11/Slide146-460x345.png" alt="oracle soa tutorials" width="491" height="368" />
We can see the request and response&nbsp; XML documents. Observe response document contains &ldquo;Hello orafmwschool&rdquo; as the result element value.
<img class="aligncenter wp-image-1464" title="oracle soa tutorials" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/11/Slide147-460x345.png" alt="oracle soa tutorials" width="501" height="376" />
This completes the Helloworld OSB lesson.
For in-depth understanding of OSB click on
<a href="https://tekslate.com/osb-oracle-service-bus/" target="_blank">OSB [oracle service bus] in SOA</a>

Oracle Service Bus Tutorial by Oracle Experts

What is Oracle Service Bus

oracle-service-bus

Create or replace procedure pname (variable Direction data type, variable direction data type,&hellip;.)&nbsp; as number varchar2 Begin&agrave;keyword Statement;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; create procedure abc Statement; Statement; End;&agrave;key word /(execute) &nbsp;
<h3>Sp Ex:-1</h3>
A SP for adding two numbers &agrave;open oracle software &agrave;type as follows at SQL Prompt Create or replace procedure add 2(a in number, b in number, c out number)as Begin Bi direction. Operator C:=a+b; End; [:=B1-Directional operator] /
<h3>&nbsp;</h3>
<h3>SP EX:-2</h3>
A Procedure for turning the spelling of a given number Create or replace procedure Get spell(n in number, X out vachar2)as Begin To_ data(n, &rsquo;J&rsquo;)X Select &nbsp;to &ndash; char(to-date(n,&rsquo;J&rsquo;),&rsquo;JSP&rsquo;) in to x Upper case From dual; End; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;logical&nbsp; table which is created and destroyed at runtime
<h3>&nbsp;</h3>
<h3>A Query to display all procedure names</h3>
Select* from user-object where Object-type=&rsquo;PROCEDURE&rsquo; &nbsp;
<h3>Steps to execute a procedure at SQL prompt</h3>
Variable a varchar2(100) Exec Get spell(98765,:a) Print a; Copy it to the sql prompt &nbsp;
<h3>Calling the stored procedure from c#.NET</h3>
&nbsp; <img class="alignnone wp-image-8355" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/Screenshot_616-460x206.png" alt="Screenshot_6" width="549" height="245" /> &nbsp; &nbsp; Open WFAP Place two textboxes and a button &nbsp; <img class="alignnone size-full wp-image-8356" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/Screenshot_719.png" alt="Screenshot_7" width="256" height="131" /> &nbsp; Using system. data.ole Ob; Code for button1_click { OleDb connection cn=new oleDb connection (&ldquo;Prorider = msdaora.1;user id = scott; password = tiger&rdquo;); Cn. Open(); oleDb command cmd= new oleDb command(); cmd. connection=(); cmd. command text=&rdquo;Get spell&rdquo;; Procedure name cmd. command type= command type stored procedure; oleDb parameter P1,P2; P1= cmd. Parameter Add(&ldquo;a&rdquo;, oleDb type. Integer); P1.Direction = Parameter Direction. Input; P1. Value = Int. parle(text box1. text); P2= cmd. Parameter Add(&ldquo;b&rdquo;, oleDb type. Varchar,100); P2.Direction = Parameter Direction. out put; //for out put Parameter value is not allowed. cmd. Execute non Query(); text box2.text = p2. Value. To string(); } F5 &nbsp; &nbsp; &nbsp;
<h3>Working with data relations</h3>
<img class="alignnone wp-image-8357" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/Screenshot_137-460x387.png" alt="Screenshot_1" width="596" height="501" /> &nbsp; &nbsp;
<ul>
<li>Data relation is a class, which is the part of dataset</li>
<li>Data relation class, is used to create the relation in between two tables with the belD 001 primary and foreign keys</li>
<li>Logical Link .between tow tables is called as relation</li>
<li>To create a Relation, fire things are required:-</li>
</ul>
(a) Parent table (b) primary key (c)Child table (d)Foreign key (e)Relation name
<ul>
<li>Data grid view control of .NET 3.5 is capable to display only one table at a time</li>
</ul>
Data grid control of&nbsp; .NET 1.1 is capable to display more than one table also at a time
<ul>
<li>Data grid control is not available in the tool box by default, hence it need to be added</li>
</ul>
<img class="alignnone size-full wp-image-7854" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/11/Screenshot_242.png" alt="Screenshot_24" width="34" height="14" />&nbsp;Open WFAP <img class="alignnone size-full wp-image-7854" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/11/Screenshot_242.png" alt="Screenshot_24" width="34" height="14" />Open Tool box<img class="alignnone size-full wp-image-7854" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/11/Screenshot_242.png" alt="Screenshot_24" width="34" height="14" /> right click inside of general tab<img class="alignnone size-full wp-image-7854" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/11/Screenshot_242.png" alt="Screenshot_24" width="34" height="14" /> choose Items<img class="alignnone size-full wp-image-7854" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/11/Screenshot_242.png" alt="Screenshot_24" width="34" height="14" />Select data grid&agrave; ok <img class="alignnone size-full wp-image-7854" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/11/Screenshot_242.png" alt="Screenshot_24" width="34" height="14" />Place a button and data grid control on the form using system. Data. oleDb; <img class="alignnone size-full wp-image-7854" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/11/Screenshot_242.png" alt="Screenshot_24" width="34" height="14" />Code for button 1 _click { oleDb connection cn = new oleDb connection (&ldquo;user id =scott; pass word = tiger; provider = msdaora.1&rdquo;); oleDb Data adapter dl=new oleDb Data Adapter (&ldquo;Select*from dept&rdquo;,cn); oleDb Data Adapter d2= new oleDb Data Adapter (&ldquo;Select*from emp&rdquo;,cn); Data set ds=new dataset(); dl.fill(ds,&rdquo;d&rdquo;); d2.fill(ds,&rdquo;e&rdquo;)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Allas names Data Column prk = ds. Tables[&ldquo;d&rdquo;].Columns[&ldquo;deptno&rdquo;]; Data Column fk = ds. Tables[&ldquo;e&rdquo;].Columns[&ldquo;dept no&rdquo;]; Data relation dr=new data relation(&ldquo;rel&rdquo;, prk, fk); Alias name for the relation &nbsp; &nbsp; Ds. Relation. Add(dr); &nbsp; <img class="alignnone size-full wp-image-8358" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/Screenshot_815.png" alt="Screenshot_8" width="319" height="187" /> &nbsp; Data gridl. data source=ds; (or) Data Grid view1.Data source= ds. Tables[&ldquo;d&rdquo;]; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;

Syntax to write a stored procedure in oracle

syntax-write-stored-procedure-oracle

TOPIC oracle 10 s architecture &ndash; 1 &nbsp; Resource distribution is depends neither on the no. of end users now on the size of the database. But it depends only on the processes <img class="alignnone wp-image-10067" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2015/01/Screenshot_1-460x248.png" alt="Screenshot_1" width="679" height="364" /> &nbsp; &nbsp; Resource distribution is depends neither on the no. of end users now on the size of the database. But it depends only on the processes <img class="alignnone wp-image-10068" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2015/01/Screenshot_2-460x153.png" alt="Screenshot_2" width="485" height="160" /> &nbsp; <img class="alignnone wp-image-10069" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2015/01/Screenshot_3-460x196.png" alt="Screenshot_3" width="529" height="224" /> &nbsp; &nbsp; database couldn&rsquo;t enteract with hardware directly, can be interact with the help of operating system (unix) only ஃ unix admin. Requirements are needed for DBA&rsquo;s. &nbsp; <img class="alignnone wp-image-10070" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2015/01/Screenshot_4-460x114.png" alt="Screenshot_4" width="491" height="121" /> &nbsp; <img class="alignnone wp-image-10071" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2015/01/Screenshot_5-460x419.png" alt="Screenshot_5" width="605" height="551" /> <img class="alignnone wp-image-10072" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2015/01/Screenshot_6-460x134.png" alt="Screenshot_6" width="569" height="165" /> <img class="alignnone wp-image-10073" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2015/01/Screenshot_7-460x286.png" alt="Screenshot_7" width="490" height="304" /> &nbsp; Httpd (Hyper text transfer protocol demoin), works&nbsp; as a broker between server (html) browser (uses) <img class="alignnone wp-image-10074" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2015/01/Screenshot_9-460x375.png" alt="Screenshot_9" width="578" height="470" /> &nbsp; &nbsp; <img class="alignnone wp-image-10075" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2015/01/Screenshot_10-460x354.png" alt="Screenshot_10" width="421" height="323" /> &nbsp;
<ul>
<li>Data base should be up gradual along with version of s/w</li>
<li>Base tables increase in the new versions</li>
</ul>
&nbsp; eg &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; version&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; base tables &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; oracle 6 --------249 oracle 7 --------400 oracle 9i-------900 oracle 10s------1200 &nbsp; Base tables are increasing in new versions
<ol>
<li>new features ore coming up</li>
<li>more options in existing features</li>
</ol>
&nbsp; <img class="alignnone wp-image-10076" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2015/01/Screenshot_11-460x283.png" alt="Screenshot_11" width="534" height="328" /> &nbsp; <img class="alignnone wp-image-10077" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2015/01/Screenshot_12-460x279.png" alt="Screenshot_12" width="600" height="362" /> &nbsp; &nbsp; &nbsp;

Oracle 10'g Architecture

oracle-10s-architecture

&nbsp;
<h2 style="text-align: center;">3 tier Architecture</h2>
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;<img class="alignnone wp-image-10086 aligncenter" style="display: block; margin-left: auto; margin-right: auto;" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2015/01/Screenshot_171-460x148.png" alt="3-tier architecture" width="654" height="209" /> &nbsp;
<blockquote>
Inclined to build a profession as Oracle DBA Training? Then here is the blog post on, explore <a title="Oracle DBA Training" href="oracle-dba-training" target="_blank" rel="noopener">Oracle DBA Training</a>
</blockquote>
<h2 style="text-align: left;">4 tier Architecture</h2>
We can add &lsquo;n&rsquo; no.of architectures &nbsp;
<h3>Library cache performs </h3>
<ul>
<li>Syntactical checking &amp; whether SQL statement is valid or not like expressions messing from ok</li>
<li>Semantically checking whether the user is valid or not checks the privileges.</li>
<li>Data dictionary cache checks in base tables whether the table is available or not if available then the optimizer the oracle brain] checks whether the table has an index or not then the DDC makes an execution plan.&nbsp;</li>
</ul>
&nbsp;
<img class="alignnone wp-image-10087" style="display: block; margin-left: auto; margin-right: auto;" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2015/01/Screenshot_181-460x325.png" alt="4-tier Architecture" width="634" height="446" />
&nbsp; &nbsp; <img class="alignnone wp-image-10089" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2015/01/Screenshot_221-460x201.png" alt="updating db file" width="589" height="257" /> &nbsp; &nbsp; &nbsp; <img class="alignnone wp-image-10088" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2015/01/Screenshot_211-460x555.png" alt="Redo log files" width="527" height="638" />
IF there is any update Then DBWR does 3 things i) updating db files ii) updating redo log files iii) updating index files <img class="alignnone size-full wp-image-10090" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2015/01/Screenshot_231.png" alt="Architecture files" width="257" height="320" /> &nbsp; &nbsp;
Inclined to build a profession as Client-server computing? Then here is the blog post on, explore <a title="Client server computing" href="client-server-computing-oracle-dba" target="_blank" rel="noopener">Client-server computing</a>

3-tier and 4-tier architecture | Tekslate

3 tier and 4 tire Architecture in Oracle DBA

3-tier-4-tire-architecture-oracle-dba

&nbsp;
<h2>&nbsp;Client-server computing</h2>
<blockquote>
Inclined to build a profession as Oracle DBA Training? Then here is the blog post on, explore <a title="Oracle DBA Training" href="oracle-dba-training" target="_blank" rel="noopener">Oracle DBA Training</a>
</blockquote>
<img class="wp-image-10080 aligncenter" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2015/01/Screenshot_13-460x236.png" alt="Client server computing" width="600" height="306" /> &nbsp; <img class="wp-image-10081 aligncenter" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2015/01/Screenshot_14-460x152.png" alt="SGA Types" width="597" height="197" /> &nbsp; <img class="wp-image-10082 aligncenter" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2015/01/Screenshot_15-460x474.png" alt="Client server database" width="561" height="578" /> To decrease the weight /pressure on server splitting into 2 or more servers. &nbsp; <img class="wp-image-10083 aligncenter" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2015/01/Screenshot_16-460x535.png" alt="Background processor" width="507" height="592" /> &nbsp; &nbsp; &nbsp;

Client server computing in Oracle

Client server computing in Oracle DBA

client-server-computing-oracle-dba

Mandatory process in Oracle Database

Mandatory processes in Oracle DBA

mandatory-processes-oracle-dba

&nbsp;
<h2>Create Database Rajpal</h2>
 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; datafile &lsquo;/disk3/oraclate/pal/system/dbf&rsquo; size 170 m autoextend on
sysaux datafile &lsquo;/disk3/oradatra/pal/sysaux/dbf&rsquo; size 100 m autoextend on
default temporary tablespace temp tempfile &lsquo;/disk3/oradata/pal/temp.dbf&rsquo;size 20m
default tablespace userdata datafile &lsquo;/disk3/oradata/pal/userdata.dbf&rsquo; size 20,
undo tablespace undotbs datafile &lsquo;/disk3/oradata/pal/undotbs.dbf&rsquo; size 20m
Logfile
group 1 (&lsquo;/disk3/oradata/pal/redologi1a.log&rsquo;) size 4m,
group 2 (&lsquo;/disk3/oradata/pal/redolog2a.log&rsquo;) size 4m
controlfile reuse;
~
~
~
~
~
~
:wq
SQL &gt;@cr8db
database created.
SQL&gt; select status from v$instance;
STATUS
-------------
OPne SQL &gt; Cl scr
SQL &gt; ed run
~
~
~
@$ORACLE_HOME/rdbms/admin/catalog.sql
@$ORACLE_HOME/rdbms/admin/catpmoc.sql
conn system/manger
@ $ ORACLE_HOME/sqlplus/admin/pupbld.sql
~
~
~
~
:wq
SQL &gt; @ run
Procedure is created.
SQL &gt; select count (*) from tab;
SQL &gt; select * from dba_users;
&nbsp; &nbsp;Dump destination is the latest advance to oracle. It contains all transactions related information whatever we perform against the database. [alert$ORACLE_SID.log] user-related error
files will be generated in user dump destination Core dump destination contains memory-related errors. <img class="alignnone size-full wp-image-10105" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2015/01/Screenshot_35.png" alt="startup" width="262" height="107" /> &nbsp; &nbsp; <img class="alignnone wp-image-10106" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2015/01/Screenshot_36-460x208.png" alt="creating the database" width="596" height="268" /> &nbsp;
sanity check includes, whether the control file values (&nbsp;&nbsp;&nbsp; SCN #]
available physically or not.&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; data
$ORACLE_HOME/rdbms/admin/catalog/sql&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; user
$ORACLE_HOME/rdbms/admin/catalog/sql&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; all
$ORACLE_HOME/rdbms/admin/catalog/sql &agrave; [system/manager]
Catalog.sql: contains all user-friendly data dictionary vies [base table are not user friendly]
catproc.sql: contains tables, procedures &amp; functions
pupbld.sql: contains default users [6],
default profiles.
&nbsp; <img class="alignnone wp-image-10107" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2015/01/Screenshot_37-460x157.png" alt="Types of shutdown" width="532" height="181" /> &nbsp;
<blockquote>
Inclined to build a profession as Oracle DBA Training? Then here is the blog post on, explore <a title="Oracle DBA Training" href="oracle-dba-training" target="_blank" rel="noopener">Oracle DBA Training</a>
</blockquote>
Shut about: &nbsp;Shut down immediately whether the Txs open or not, do not bother &amp; kills the Txs abruptly
Shutdown transactional: Shutdown the database after open Txs are committed. &nbsp;
Shut immediate: &nbsp;if any open Tx, then it rollbacks the Tx and shuts down.
Shutdown normal: shuts down the database, after all the users terminated all the sessions.
Catalog.sql
contains no tables, but contains, only views like user <img class="alignnone size-full wp-image-10108" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2015/01/Screenshot_38.png" alt="Screenshot_38" width="441" height="285" /> &nbsp;
catproc.sql
contains pl/sql procedures&nbsp;&nbsp; up to 8i
&nbsp; Up to ORACLE 8i one server may
contain multiple databases&nbsp; of different
block sizes
from 9i
&nbsp; From ORACLE 9i one database can maintain multiple&nbsp; &nbsp;block sizes
&nbsp; <img class="alignnone size-full wp-image-10109" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2015/01/Screenshot_39.png" alt="Database" width="309" height="280" /> &nbsp;
<h3>CPU Work</h3>
in a day not more than 60%
in night 100%
Effective CPU utilization: if 1 db going with backup than let the &nbsp;other databases &nbsp;do the reports, pl/sql procedures &amp;
&nbsp; &nbsp;<img class="alignnone wp-image-10111" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2015/01/Screenshot_41-460x397.png" alt="Effective CPU Utilization" width="613" height="527" /> &nbsp; <img class="alignnone wp-image-10112" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2015/01/Screenshot_42-460x255.png" alt="Table" width="699" height="387" /> &nbsp; &nbsp;
For an in-depth knowledge, click on below
<ul>
<li><a href="oracle-dba-interview-questions-answers" target="_blank" rel="noopener">Oracle DBA Interview Questions</a></li>
<li><a href="introduction-oracle-10g-dba" target="_blank" rel="noopener">Introduction to Oracle 10g DBA</a></li>
<li><a href="create-database-link-oracle-dba" target="_blank" rel="noopener">Create database link Oracle DBA</a></li>
<li><a href="client-server-computing-oracle-dba" target="_blank" rel="noopener">Client-server computing in Oracle DBA</a></li>
</ul>
&nbsp;

Create database rajpal in Oracle Daatabase

create database rajpal in Oracle DBA

create-database-rajpal-oracle-dba

&nbsp;
<img class="alignnone wp-image-10116" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2015/01/Screenshot_44-460x610.png" alt="User management role management" width="574" height="762" /> &nbsp; &nbsp;
Whenever we are going to create any user we should follow the checklist:
<ol>
<li>identify the tablespace in which users need to store the object.</li>
<li>Plan for quotas for each tablespace.</li>
<li>Assign a default tablespace and temporary tablespace to the user.</li>
<li>Assign proper me sources [profiles] to the user.</li>
</ol>
<blockquote>
Inclined to build a profession as Oracle DBA Training? Then here is the blog post on, explore <a title="Oracle DBA Training" href="oracle-dba-training" target="_blank" rel="noopener">Oracle DBA Training</a>
</blockquote>
After creating the user by following the above 4 steps, the grant required privileges (or) roles 9set of privileges to the user
<img class="alignnone size-full wp-image-10118" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2015/01/Screenshot_45.png" alt="Quotes Profiles" width="121" height="94" />
<ul>
<li>Table space quotas control the amount of physical storage assigned to the user.</li>
<li>A profile is a named set of passwords &amp; resource limits. Generally, this profile contains &nbsp;</li>
</ul>
<ol>
<li>Password again &amp; expiration</li>
<li>Account locking should set in the parameter file Idle time<img class="alignnone size-full wp-image-9386" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2014/12/220.png" alt="2" width="30" height="12" /> resource_limit=TRUE</li>
<li>Cpu time</li>
<li>Connect time</li>
<li>Concurrent session</li>
</ol>
&nbsp; <img class="alignnone wp-image-10119" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2015/01/Screenshot_46-460x281.png" alt="Profile Management" width="492" height="300" /> &nbsp;
&rdquo;default&rdquo; is the default profile from the pupbld. SQL script. &nbsp;
<h2>Advantages of profiles:</h2>
<ol>
<li>Ensures that the users logoff their sessions when they have left their sessions idle for some time.</li>
<li>Enables similar resource limits for a group of users</li>
<li>Easily assign resource limit to the users and control of passwords</li>
</ol>
&nbsp; <img class="alignnone size-full wp-image-10120" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2015/01/Screenshot_47.png" alt="privileges" width="167" height="123" />
Privilege is a permission to perform transactions against the database &nbsp; <img class="alignnone wp-image-10121" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2015/01/Screenshot_48-460x141.png" alt="Privileges Types" width="544" height="165" /> &nbsp; <img class="alignnone wp-image-10123" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2015/01/Screenshot_491-460x178.png" alt="Privileges" width="631" height="244" /> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;
<h2 style="text-align: justify;">For an in-depth knowledge, click on below</h2>
<ul>
<li><a href="introduction-oracle-10g-dba" target="_blank" rel="noopener">Introduction to Oracle 10g DBA</a></li>
</ul>

User Management Role in Oracle Database

User Management Roles in Oracle DBA

user-management-roles-oracle-dba

&nbsp;
<h2>10g features</h2>
<ol>
<li>Renaming the table space</li>
<li>Temporary table space group up to 9i only one temp. tables space</li>
<li>Big file table space up to 120 tb., at table space level</li>
</ol>
SYS&gt; desc dba _tabespaces
SYS&gt; select table space_name, block_size, status, extent_managment, contents from dbna_table spaces;
SYS&gt; sava dba_ts
SYS&gt; desc dba_data_files
SYS&gt; desc v $tempfile
SYS&gt; desc data base_properties to create table space ls1
SYS&gt; create table space ts1 datafile &lsquo;/disk4/oradata/pal/ts1.dbf&rsquo; size 10m;
[default locally managed [in 10g]
SYS&gt; @dba_ts
SYS&gt; create table space ts_dict data file &lsquo;disk4/oradata / pal/ts_dict.dbf&rsquo; size 10m extent management&nbsp; dictionary;
[to create table space is_dict]
SYS&gt; @dba_ts
to increasing the data file size of existing table space
SYS&gt; alter table space 1st add datafile &lsquo;/disk4/oradata/pal/ts11.dbf&rsquo;size 5m;
SYS&gt; select * from dba_data_files;
to resize exioting data file
SYS&gt; alter database datafile &lsquo;/disk4 / oradata/pal/ts11.dbf&rsquo; resize 10m;
To drop label space
SYS&gt;drop tablespace ts_dict;
only the logical path will be broken but @ os level, the data files which are under this tablespace will be existing physically
<h3>To establish the logical path between physical files and newly creating tablespace. </h3>
SYS&gt; create tablespace ts_dict data file &lsquo;/disk4/oradata/pa;/ts_dick.dbf&rsquo;reuse; &nbsp;
<h3>To delete tablespace along with data files which it contains </h3>
SYS&gt; drop tablespace ts_dict including contents and data files; &nbsp;
<blockquote>
Inclined to build a profession as Oracle DBA Training? Then here is the blog post on, explore <a title="Oracle DBA Training" href="oracle-dba-training" target="_blank" rel="noopener">Oracle DBA Training</a>
</blockquote>
<h3>To make the tablespace contents in read-only mode </h3>
SYS&gt; alter tablespace ts1 read-only;
At read-only mode we drop the tablespace but can&rsquo;t perform only DML operations can only select (DRL) &nbsp;
<h3>To make the tablespace contents in read &amp; write mode </h3>
SYS&gt; alter tablespace ts1 read write; &nbsp;
<h3>To make a tablespace offline </h3>
SYS&gt; alter tablespace ts1 offline: &nbsp;
<h3>To make tablespace&nbsp; online </h3>
SYS&gt; alter tablespace ts1 online;
can perform DML, DBL, Txs.
In 10s the default block size is 8k
to make the block size as 2k or 4k&hellip;&hellip;&hellip;
[dba12 dbs] $ vi initrajpar. Ora
Db_2K_cache_size = 16m
:wq
SYS&gt; start up force
SYS&gt; sho parameter db-2K
To create data file size as 2k
SYS&gt; create table space ts-2k data file &lsquo;/disk4/oradata/pal/ts_2k.dbf &lsquo;size 10m blocksize 2k;
SYS&gt; @ dba_ts &nbsp;
<h3>Renaming table space </h3>
SYS&gt; alter table space ts1 rename to ts2; &nbsp;
<h3>To create nologging table space </h3>
SYS&gt; create table space ts-log data file &lsquo;/disk4/oradata/pal/ts-log.dbf&rsquo; size 5m nologging;
SYS&gt; select* from dba_table spaces:
Observe the logging column. &nbsp;
<h3>To create a temporary tablespace group [ 10g feature]</h3>
SYS&gt; create temporary tablespace ttsg1 tempfile &lsquo;disk4/oradata/pa;/ttsg1/dbf&rsquo; size 10m tablespace group group1;
SYS&gt; select * from dba_table space_groups; &nbsp;
<h3>To add a tablespace togroup1 </h3>
SYS&gt; create temporary tablespace ttog2 tempfile &lsquo;/disk4/oradata/pal/ttsg2.dbf&rsquo; size 15m tablespace group group1; &nbsp;
<h3>To assign group1 to default temporary tablespace </h3>
[initially, it was &ldquo;temp&rdquo;]
SYS&gt; alter database default temporary tablespace group1;
SYS&gt; select * from dba_properties; &nbsp;
<h3>To create big file table space </h3>
SYS&gt; create big file tablespace ts_big data file &lsquo;/disk4/oradata/pal/ts_big.dbf&rsquo; size 20m;
SYS&gt; select * from dba_table spaces;
[observe the big file column]
Resizing label space
SYS&gt; alter tablespace ts_big resize 25m
Tp rename ][or] move a data file &nbsp;
<h2> Steps:</h2>
<ol>
<li>Make the tablespace offline</li>
<li>From the OS level physically copy the data file to the new destination.</li>
<li>at SQL level rename the data file with a new location</li>
<li>make the tablespace</li>
<li>online.</li>
</ol>
SYS&gt; create table space temp1 data file &lsquo;/disk4/oradata/pal/ temp1/ dbf&rsquo; size.5m;
SYS&gt; alter table space temp1 offline;
SYS&gt; !
[dba12 ~] $ cd /disk4/oradata/pa;
[dba12 pa;] $ cp temp1.dbf /disk 3/oradata/pal /temp1.dbf
[dba12 pal]$ exit
SYS&gt; alter table space temp1 rename data file &lsquo;/disk4 /oradata / pal/ temp1.dfb&rsquo;;
SYS&gt; qlter &nbsp;table space temp1 oline;
SYS&gt; select * from dba _ data_ files; &nbsp; &nbsp;
<a style="background-color: #e94350; color: #ffffff; padding: 10px 20px;" href="Oracle-DBA-interview-questions-answers" target="_blank" rel="noopener">Oracle DBA Interview Questions</a>

Introduction to Oracle 10g Database

Introduction to Oracle 10g DBA

introduction-oracle-10g-dba

<h2>Oracle Database Privileges</h2>
A user can connect with different levels of privileges:
<ul>
<li>namely for the <a href="https://tekslate.com/pegarules-database/" target="_blank" rel="noopener">database</a>:</li>
</ul>
SYSDBA&nbsp;(the root or administrator of the database) and
SYSOPER - SYSOPER allows a user to perform basic operational tasks but without the ability to look at user data.
<ul>
<li>for&nbsp;Automatic Storage Management:</li>
</ul>
SYSASM&nbsp;(the root or administrator of&nbsp;Automatic Storage Management)
<ul>
<li style="text-align: justify;">Operating system accounts (user) must be members of the OSDBA or OSOPER groups to connect to the database with administrator privileges.</li>
<li style="text-align: justify;">When you connect with a privilege issuing a 'CONNECT / AS SYSDBA' (OR SYSOPER), Oracle checks if your account is a member of the&nbsp;corresponding OS group&nbsp;(for Windows: 'ORA_sid_DBA' or 'ORA_DBA' group).</li>
</ul>
<h2>Administrative Privileges</h2>
Administrative privileges that are required for an administrator to perform basic database operations are granted through two special system <a href="https://tekslate.com/analytical-privileges-sap-hana/" target="_blank" rel="noopener">privileges</a>,&nbsp;SYSDBA&nbsp;and&nbsp;SYSOPER. &nbsp;You must have one of these privileges granted to you, depending upon the level of authorization you require.
<h2 style="text-align: justify;">SYSDBA and SYSOPER</h2>
The following operations are authorized by the&nbsp;SYSDBA and SYSOPER&nbsp;system privileges:
<h3 style="text-align: justify;">The privileges of SYSOPER are:</h3>
<ul style="text-align: justify;">
<li>Perform&nbsp;STARTUP&nbsp;and&nbsp;SHUTDOWN&nbsp;operations</li>
<li>CREATE SPFILE</li>
<li>ALTER DATABASE OPEN/MOUNT/BACKUP</li>
<li>ALTER DATABASE ARCHIVELOG</li>
<li>ALTER DATABASE RECOVER&nbsp;(Complete recovery only. Any form of incomplete recovery, such as&nbsp;UNTIL TIME|CHANGE|CANCEL|CONTROLFILE&nbsp;requires connecting as&nbsp;SYSDBA.)</li>
<li>Includes the&nbsp;RESTRICTED SESSION&nbsp;privilege</li>
</ul>
This privilege allows a user to perform basic operational tasks, but without the ability to look at user data.
<blockquote>
Inclined to build a profession as Online Oracle DBA Training? Then here is the blog post on, explore <a title="Online Oracle DBA Training" href="oracle-dba-training" target="_blank" rel="noopener">Online Oracle DBA Training</a>
</blockquote>
<h3 style="text-align: justify;">The privileges of SYSDBA are:</h3>
<ul style="text-align: justify;">
<li>Perform&nbsp;STARTUP&nbsp;and&nbsp;SHUTDOWN&nbsp;operations</li>
<li>ALTER DATABASE: open, mount, back up, or change character set</li>
<li>CREATE DATABASE</li>
<li>DROP DATABASE</li>
<li>CREATE SPFILE</li>
<li>ALTER DATABASE ARCHIVELOG</li>
<li>ALTER DATABASE RECOVER</li>
<li>Includes the&nbsp;RESTRICTED SESSION&nbsp;privilege</li>
</ul>
Effectively, this system privilege allows a user to connect as user&nbsp;SYS.
<h2>Nonsecure Remote Connections</h2>
To connect to Oracle Database as a privileged user over a non-secure connection, you must be authenticated by a password file. When using password file authentication, the database uses a password file to keep track of database usernames that have been granted the SYSDBA or SYSOPERsystem privilege. This form of authentication is discussed in "Using Password File Authentication".
<h2 style="text-align: justify;">Local Connections and Secure Remote Connections</h2>
You can connect to Oracle Database as a privileged user over a local connection or a secure remote connection in two ways:
<ul style="text-align: justify;">
<li>If the database has a password file and you have been granted the SYSDBA&nbsp;or&nbsp;SYSOPER&nbsp;system privilege, then you can connect and be authenticated by a password file.</li>
<li>If the server is not using a password file, or if you have not been granted SYSDBA&nbsp;or&nbsp;SYSOPER&nbsp;privileges and are therefore not in the password file, you can use operating system authentication. On most operating systems, authentication for database administrators involves placing the operating system username of the database administrator in a special group, generically referred to as OSDBA. Users in that group are granted&nbsp;SYSDBA&nbsp; A similar group, OSOPER, is used to grant&nbsp;SYSOPER&nbsp;privileges to users.</li>
</ul>
<h2>SYSDBA and SYSOPER System Privileges</h2>
SYSDBA and SYSOPER are administrative privileges required to perform high-level administrative operations such as creating, starting up, shutting down, backing up, or recovering the database. The SYSDBA system privilege is for fully empowered database administrators and the SYSOPER system privilege allows a user to perform basic operational tasks but without the ability to look at user data.
The&nbsp;SYSDBA&nbsp;and&nbsp;SYSOPER&nbsp;system privileges allow access to a database instance even when the database is not open. Control of these privileges is therefore completely outside of the database itself. This control enables an administrator who is granted one of these privileges to connect to the database instance to start the database.
You can also think of the&nbsp;SYSDBA&nbsp;and&nbsp;SYSOPER&nbsp;privileges as types of connections that enable you to perform certain database operations for which privileges cannot be granted in any other way. For example, if you have the&nbsp;SYSDBA&nbsp;privilege, then you can connect to the database using&nbsp;AS SYSDBA.
The SYS user is automatically granted the SYSDBA privilege upon installation. When you log in as user SYS, you must connect to the database as SYSDBA or SYSOPER. Connecting as an SYSDBA user invokes the SYSDBA privilege; connecting as SYSOPER invokes the SYSOPER privilege. EM Express allows you to log in as user SYS and connect as SYSDBA or SYSOPER.
When you connect with the&nbsp;SYSDBA&nbsp;or&nbsp;SYSOPER&nbsp;privilege, you connect with a default schema, not with the schema that is generally associated with your user name. For&nbsp;SYSDBA&nbsp;this schema is&nbsp;SYS; for&nbsp;SYSOPER&nbsp;the schema is&nbsp;PUBLIC.
<h3>For in-depth knowledge on Oracle DBA click on&nbsp;</h3>
<ul>
<li><a href="https://tekslate.com/oracle-service-bus/" target="_blank" rel="noopener">Oracle Service Bus</a></li>
<li><a href="https://tekslate.com/working-flashback-table-oracle-dba/" target="_blank" rel="noopener">Working with Flashback Table in Oracle DBA</a></li>
<li><a title="Oracle DBA Interview Questions and Answers" href="oracle-dba-interview-questions-answers" target="_blank" rel="noopener">Oracle DBA Interview Questions</a> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;</li>
</ul>
&nbsp;

Types of privileges of ‘SYSOPER’ in Oracle DBA

types-privileges-sysoper-oracle-dba

Working capacity plan for oracle database

Working with Capacity plan  in Oracle DBA

working-capacity-plan-oracle-dba

&nbsp;
<h2>O/S level authentication</h2>
SYS&gt; !
[dba12 dbs] $ vi initrajpar.ora
&nbsp; &nbsp;<img class="alignnone wp-image-10129" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2015/01/Screenshot_50-460x56.png" alt="Single quote " width="592" height="71" />
:wq
[dba12 dbs] $ exit
SYS&gt; startup force&nbsp; &agrave; includes shut abort &amp; startup
[just like restart]
{not suggestible command}
SYS&gt; sho parameter OS_authent_prefix
SYS&gt; create user raj6x71A identified externally;
The user should have os level authentication.
SYS&gt; grant connect, resource to raj6x 71A;
SYS&gt; !
[dba12 ~] $ pwd
Dba12/raj6x71A
[dba12 ~] $ sqlplus
SQL &gt; sho user raj6x 71A
SQL &gt;
<blockquote>
Inclined to build a profession as Oracle DBA Training? Then here is the blog post on, explore <a title="Oracle DBA Training" href="oracle-dba-training" target="_blank" rel="noopener">Oracle DBA Training</a>
</blockquote>
To create password file 
[db a 12 dbs] $ vi initrajpor.ora
Remote-login_password file= exclusive
:wq
[dba12 dbs] $ orapwd file = orapw $ ORACLE_SID password= diamond
[dba12 dbs] $ cd
[dba12 ~] $ exit
SYS&gt; startup force
SYS&gt; sho parameter remote_login
Value
Exclusive
SYS&gt; select * from v$ pwfile_users
Now SYS can grant SYSDBA privileges to any other user.
SYS&gt; grant sysdba to usr1;
SYS&gt; startup force
SYS&gt; conn user 1/user1 as sysdba
User1&gt; startup force
SQL&gt; sho user user1
SQL&gt; &nbsp;
ROL =s: 
SYS&gt; select * from session _ privs;
SYS&gt; create role pay-1;
SYS&gt; grant create tablespsce, create user to pay-1;
SYS&gt; grant pay-1 to suer &ndash; 1;
Dba_role_privs
Role_sys_privs
(views related to roles)
&nbsp; &nbsp; <img class="alignnone wp-image-10130" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2015/01/Screenshot_51-460x438.png" alt="Storage management" width="576" height="547" /> &nbsp; &nbsp; &nbsp;
<ol>
<li>One segment can only be created in one tablespace</li>
<li>One tablespace can have multiple segments.</li>
</ol>
<ul>
<li>One segment is made up of one or more extents.</li>
</ul>
<ol>
<li>One extent is made up of COB.[contiguous oracle blocks]</li>
</ol>
<ul>
<li>One segment can have its multiple extents in the same or different data files provided these data files belongs to the same tablespace.</li>
<li>When a segment&rsquo;s extents are completely filled up, ORACLE offers another extent to support the growth of the segments on its own. This is known as DSA [dynamic space allocation] which is limited the free space available in the tablespace.</li>
</ul>
&nbsp; <img class="alignnone wp-image-10131" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2015/01/Screenshot_52-460x159.png" alt="EMP Table Extents" width="585" height="201" /> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;
<h2>For an in-depth knowledge, click on below</h2>
<ul>
<li><a href="different-rollback-segments-oracle-dba" target="_blank" rel="noopener">Different Rollback segments in oracle dba</a></li>
<li><a href="omg-oracle-managed-files-oracle-dba" target="_blank" rel="noopener">OMG in Oracle dba</a></li>
</ul>

O/S Level Authentication for Oracle Database

O/S level authentication in Oracle DBA

os-level-authentication-oracle-dba

Differed&nbsp; rollback segment If any active transaction is there in a table space, if we make the particular table space Maintains the differed rollback segment for that particular tx. SYS&gt; select tablespace_name, segment_name, segment_type from dba_segments where Segment_type like &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &lsquo;%DEFF%&rsquo;; SYS&gt; save drs User 2 &gt; insert into dept select * from dept; SYS &gt; @ drs SYS &gt; alter tablespace ts2 offline; SYS &gt; @ drs Tablespace_name&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; segment_type Ts2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; differed rollback Sp file &amp; OMF &nbsp;[ORACLE managed files ] [server parameter file &nbsp; <img class="alignnone wp-image-10151" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2015/01/Screenshot_68-460x236.png" alt="Screenshot_68" width="616" height="314" /> &nbsp; SERVER PARAMETER FILE To create sp file SQL&gt; create spfile from p file; dynamically we can change parameters, if our database maintains spfile. The default location for sp file is same as pfile location. i.e. $ ORACLE _ HOME /dbs spfile is a semi binary file where as&nbsp; pfile is ASCII file SQL &gt; alter system set ramote_login_password file = exclusive SCOPE= MEMORY&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; SCOPE = memory;/orspfile;/or both; Changes the parameter&rsquo;s values in the current running instance only. Scope=spfile&nbsp; Changes the parameter&rsquo;s values in spfile only Stop=both Changes the parameter&rsquo;s values in the current running instance and also in &nbsp; &nbsp; Note1&nbsp;-&nbsp; if we have both spfile and pfile then the ORACLE gives preference to &lsquo;spfile&rsquo; Note2&nbsp;-&nbsp; &nbsp;the default location of the spifle is the same as the pfile i.e. $ORACLE_HOME /dbs Note3&nbsp;-&nbsp;&nbsp; &nbsp;in the presence of the spfile. If we want to start our database by using pfile. Then manually we have to write as SQL &gt; startup pfile=&rsquo;$ORACLE_HOME/dbs/ init$ORACLE_SID.ora&rsquo;; &nbsp; Demo on sp file SQL&gt; desc v $ parameter SQL&gt; select name, isses_modifiable, isinstance_modifiable from v$parameter where name like &lsquo;rmote_login%&rsquo;; SQL&gt; select name, [sees_modifiable, Isinstance_modifiable from v$parameter where Name like &lsquo;resource_limit%&rsquo;; SYS &gt; create spfile from pfile; SYS&gt; startup force SYS&gt; sho parameter spfile SYS&gt; sho parameter resource_limit&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; [TRUE] SYS&gt; alter system set resource_limit=false Scope = memory SYS&gt; sho parameter resource-Limit&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; [FALES] SYS&gt; start up force SYS&gt; sho parameter resource_limit&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; [TRUE] (scop=memory is session dependent] SYS&gt; alter system set Resource_limit=false scope=spfile; SYS&gt; sho parameter resource_limit &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; [TRUE] [scope=spfile is not for current session but for next session] SYS&gt; startup force SYS&gt; sho parameter resource_limit &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; [FALES] (NEXT SESSION) Sys &gt; alter system set resource _limit = true scope = both; SYS&gt; sho parameter resource_limit&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; [TRUE] SYS&gt; startup force SYS&gt; sho parameter resource _limit&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; [TRUE] [Scope=both] SYS &gt; create file from spfile; [to synchronies the values in pfile and spfile] &nbsp; To remove sp file [physically at OS level] Dba12~ cde $ORACLE_HOME/dbs Dba12 dbs] $ rm spfile$ORACLE_SID.ora &nbsp;

Different  RollBack Segments in Oracle DBA

different-rollback-segments-oracle-dba

&nbsp;
<h2>OMG (ORACLE managed files)</h2>
&hellip;&hellip;to create tablespaces without specifying data files location
&nbsp; &nbsp; <img class="alignnone wp-image-10152" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2015/01/Screenshot_69-460x247.png" alt="Table space" width="677" height="363" /> &nbsp; &nbsp;
<ol>
<li>By introducing OMF from ORACLE 9i third party software need not maintain data file location anymore while creating the tablespace [OMF works regardless of what operating system behind]</li>
<li>Simplifies the creation of database default values:</li>
</ol>
100 mb size
Auto-extend on
Locally managed TS
For data files&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 01_mf_%t_%u.dbf
for log files&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 01_mf_%g_%u.log
for control file&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 01_mf_%u.ctl
g group member
mf managed files
t tablespace name
u unique id [8 characters]
<ol>
<li>OMF reduces the corruption caused by administrators specifying the wrong file.</li>
</ol>
<blockquote>
Inclined to build a profession as Oracle DBA Training? Then here is the blog post on, explore <a title="Oracle DBA Training" href="oracle-dba-training" target="_blank" rel="noopener">Oracle DBA Training</a>
</blockquote>
<h2>Problems with OMF </h2>
We can not have multiple destinations for the datafiles, wherein in the case of log files, we can have multiple destinations.
[more I/Os]
We need not to maintain including contents and data files when dropping tablespace. &nbsp;
<h2> DEMO ON OMF</h2>
Dba12 dbs]$ vi initrajpar.ora
&nbsp; <img class="alignnone wp-image-10156" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2015/01/Screenshot_70-460x270.png" alt="Optional" width="522" height="306" /> &nbsp;
SYS &gt; sho parameter db_create_
SYS &gt; create tablespace omf_1;
SYS&gt; select file_name, tablespace_name from dba_data_files;
SYS&gt; select file_name, bytes, tablespace_name from dba_data_files;
SYS&gt; create tablespace omf_2 datafile size 20m;
SYS &gt; create tablespace omf_3 extent management dictionary; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; [default local]
SYS &gt; alter database add logfile group 3 size 4m;
[size 4m compulsory, else default is 100m] &nbsp;
<h2> Database creation</h2>
Dba12 ~] $ export ORACLE _ SID = rajpar2
Dba12~] $ cd $ ORACLE_HOME/dbs
Dba12 dbs]$ cp init. Ora initrajpar2.ora
Dba12 dbs]$ vi intrajpar2. Ora
Db_name=rajpal2
Instance_name=rajpar2
Db_create_file_dest=&rsquo;/disk4/oradata/pal2&rsquo;
User_dump_dest=&rsquo;disk4/oradata/pal2/udump&rsquo;
Background_dump_dest=&rsquo;/disk4/oradata/pal2/bdump&rsquo;
Core_dump_dest= &lsquo;/disk4/oradata/pal2/cdump&rsquo;
# undo_tablespace=undotbs
:wq &nbsp;
Dba12 dbs] $cd
Dba12 ~[$ sqlplus &lsquo;/as sysdba&rsquo;
SQL &gt; startup nomount
SQL&gt; create database rajpal2; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;
<h2 style="text-align: justify;">For an in-depth knowledge, click on below</h2>
<ul>
<li><a title="Oracle DBA Interview Question and Answers" href="oracle-dba-interview-questions-answers" target="_blank" rel="noopener">Oracle DBA Interview Questions</a></li>
<li><a href="os-level-authentication-oracle-dba" target="_blank" rel="noopener">O/S level authentication in Oracle DBA</a></li>
<li><a href="different-rollback-segments-oracle-dba" target="_blank" rel="noopener">Different RollBack Segments in Oracle DBA</a></li>
</ul>
&nbsp;

Oracle managed files in Oracle DBA

OMG (ORACLE managed files) in Oracle DBA

omg-oracle-managed-files-oracle-dba

<h2>Flashback Table in Oracle DBA</h2>
The <code>FLASHBACK TABLE</code> the command allows point in time recovery of individual tables subject to the following requirements.
<ul style="text-align: justify;">
<li>You must have either the <code>FLASHBACK ANY TABLE</code> system privilege or have <code>FLASHBACK</code> object privilege on the table.</li>
<li>You must have SELECT, INSERT, DELETE, and ALTER privileges on the table.</li>
<li>There must be enough information in the undo tablespace to complete the operation.</li>
<li>Row movement must be enabled on the table (<code>ALTER TABLE tablename ENABLE ROW MOVEMENT;</code>).</li>
</ul>
<h2 style="text-align: justify;">Syntax Of Flashback Table</h2>
<img class="size-full wp-image-28002 aligncenter" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2015/01/flashback-syntax.png" alt="flashback syntax" width="630" height="315" />
The following example creates a table, inserts some data, and flashbacks to a point prior to the data insertion. Finally it flashbacks to the time after the data insertion.
<pre><code>CREATE TABLE flashback_table_test (
 id NUMBER(10)
);

ALTER TABLE flashback_table_test ENABLE ROW MOVEMENT;

SELECT current_scn FROM v$database;

CURRENT_SCN
-----------
 715315

INSERT INTO flashback_table_test (id) VALUES (1);
COMMIT;

SELECT current_scn FROM v$database;

CURRENT_SCN
-----------
 715340

FLASHBACK TABLE flashback_table_test TO SCN 715315;

SELECT COUNT(*) FROM flashback_table_test;

 COUNT(*)
----------
 0

FLASHBACK TABLE flashback_table_test TO SCN 715340;

SELECT COUNT(*) FROM flashback_table_test;

 COUNT(*)
----------
 1

</code> <code>Flashback of tables can also be performed using timestamps.</code> <code>FLASHBACK TABLE flashback_table_test TO TIMESTAMP TO_TIMESTAMP('2004-03-03 10:00:00', 'YYYY-MM-DD HH:MI:SS');</code></pre>
<div id="nmtbi6" class="_qhf _grf nmiw nmtbi nmt nmtbia " style="text-align: justify;" data-hveid="59" data-ved="0ahUKEwjS2Y2LmYfRAhVKsI8KHe6QBegQuk4IOygA">
&nbsp;
<blockquote>
Inclined to build a profession as Oracle DBA Training? Then here is the blog post on, explore <a title="Oracle DBA Training" href="oracle-dba-training" target="_blank" rel="noopener">Oracle DBA Training</a>
</blockquote>
<h2 class="_rhf">What is the Oracle Flashback Database?</h2>
Flashback Database is a part of the backup &amp; recovery enhancements in Oracle 10g Database that are called Flashback Features. The main purpose of Flashback Technology is to let you quickly recover from logical corruptions or user errors. Oracle Flashback Features include the following:
<ul>
<li>Flashback DATABASE</li>
<li>Flashback DROP</li>
<li>Flashback TABLE</li>
<li>Flashback QUERY</li>
<li>Flashback VERSIONS QUERY</li>
<li>Flashback TRANSACTION QUERY</li>
</ul>
What is a flashback in Oracle?
&nbsp;
</div>
<div id="nmtbi6cntw" class="nmcw nmbl nmca" style="text-align: justify;" data-hveid="60" data-ved="0ahUKEwjS2Y2LmYfRAhVKsI8KHe6QBegQu04IPCgB">
<div id="nmtbi6cnt">
<div class="_Zjj">
<div class="mod" data-md="61">
<div class="_oDd" data-hveid="61">Oracle Flashback Technology is a group of Oracle Database features that let you view past states of database objects or to return database objects to a previous state without using point-in-time media recovery. With flashback features, you can do the following: Perform queries that return past data.</div>
</div>
<div class="_oDd" data-hveid="61">
<h2 id="insertedID0" class="sect1">Recovering Tables Using Oracle Flashback Table</h2>
Oracle Flashback Table enables you to restore a table to its state as of a previous point in time. It provides a fast, online solution for recovering a table that has been accidentally modified or deleted by a user or application. In many cases, Oracle Flashback Table eliminates the need for you to perform more complicated point-in-time recovery operations.
<a style="background-color: #e94350; color: #ffffff; padding: 10px 20px;" href="oracle-dba-interview-questions-answers" target="_blank" rel="noopener">Oracle DBA Interview Questions</a>
Oracle Flashback Table:
<ul>
<li>Restores all data in a specified table to a previous point in the time described by a timestamp or SCN.</li>
<li>Performs the restore operation online.</li>
<li>Automatically maintains all of the table attributes, such as indexes, triggers, and constraints that are necessary for an application to function with the flashed-back table.</li>
<li>Maintains any remote state in a distributed environment. For example, all of the table modifications required by replication if a replicated table is flashed back.</li>
<li>Maintains data integrity as specified by constraints. Tables are flashed back provided none of the table constraints are violated. This includes any referential integrity constraints specified between a table included in the <code>FLASHBACK TABLE</code> statement and another table that is not included in the <code>FLASHBACK TABLE</code> statement.</li>
<li>Even after a flashback operation, the data in the original table is not lost. You can later revert to the original state.</li>
</ul>
</div>
</div>
</div>
</div>
<h2>Benefits Of Flashback</h2>
Flashback Database provides:
<ul style="text-align: justify;">
<li>A very effective way to recover from complex human errors</li>
<li>Faster database point-in-time recovery</li>
<li>Simplified management and administration</li>
<li>Little performance overhead</li>
</ul>
<h2>For in-depth knowledge on Oracle DBA click on:</h2>
<ul>
<li><a href="https://tekslate.com/oracle-service-bus/" target="_blank" rel="noopener">Oracle Service BUS</a></li>
<li><a href="https://tekslate.com/working-flashback-table-oracle-dba/" target="_blank" rel="noopener">Working with Flashback Table in Oracle DBA</a></li>
<li><a href="https://tekslate.com/types-privileges-sysoper-oracle-dba/" target="_blank" rel="noopener">Types of privileges of &lsquo;SYSOPER&rsquo; in Oracle DBA</a></li>
</ul>
&nbsp;

Working with Flashback Table in Oracle DBA

Working with Flashback Table  in Oracle DBA

working-flashback-table-oracle-dba

&nbsp;
&nbsp;
<h2>Preparing the server</h2>
<ol>
<li>we need to install ORACLE network protocol adopter for TCP/IP</li>
<li>we need to configure a special background process called &lsquo;listener&rsquo;.</li>
</ol>
<ul>
<li>In one server we can have one or more listeners depending on the load 9the no.of clients&rsquo; communications to the server)</li>
</ul>
<ol>
<li>While defining the &lsquo;listener&rsquo; we provide</li>
<li>Listener name</li>
<li>List of SID&rsquo;s [databases]</li>
<li>Protocol [TCP/IP]</li>
<li>Port number [default 1521]</li>
<li>One &lsquo;listener&rsquo; can have service handlers for one or more instances.</li>
<li>In case of having multiple listeners, they must go with different port numbers.</li>
</ol>
<ul>
<li>One instance can be listed in one or more instances. Listener: instance ~ m:m [many to many]</li>
<li>A listener can be started regardless of the status of the instance.</li>
</ul>
<ol>
<li>$lsnrctl start [starts the all the listeners]</li>
<li>$ lsnrctl starl ls1 [for 1 listener]</li>
<li>All the listeners are mentioned in</li>
</ol>
&nbsp; &nbsp; &nbsp; $ORACLE_HOME/network/admin/listener.ora[an ASCLL file]
<ol>
<li>Using lsnrctl command, we can</li>
</ol>
<ul>
<li>Start</li>
<li>Stop</li>
<li>Services</li>
<li>Debug</li>
<li>Status</li>
<li>$ lsnrctl</li>
<li>LSNRCTL &gt; help&nbsp; [for more information] &nbsp;</li>
</ul>
<blockquote>
Inclined to build a profession as Oracle DBA Training? Then here is the blog post on, explore <a title="Oracle DBA Training" href="oracle-dba-training" target="_blank" rel="noopener">Oracle DBA Training</a>
</blockquote>
<h2>Preparing the client</h2>
<ol>
<li>Install ORACLE network protocol adaptor for TCP/IP [client side components]</li>
<li>We need to define an ALIAS in c: ORACLE_HOMEnetworkadminTNSNAMES.ora</li>
<li>While defining an ALIAS, we provide</li>
</ol>
<ol>
<li style="list-style-type: none;">
<ul>
<li>Alias name</li>
<li>Target server name [or IP address]</li>
<li>Target SID [case sensitive]</li>
<li>Protocol</li>
<li>Port number [usually 1521]</li>
</ul>
</li>
</ol>
<ol>
<li>After defining the ALIAS, we check using c:tnsping &lt;ALIANS_NAME&gt;</li>
</ol>
[&lsquo;tnsping&rsquo; is the utility for ORACLE, whereas &lsquo;ping&rsquo; is the utility of TCP/IP]
<ol>
<li>The client application can be of 2 types</li>
</ol>
<ol>
<li>ORACLE client tools [farms/reports]</li>
</ol>
[in this case, we can use ORACLE&lsquo;s native drivens]
<ol>
<li>Non-ORACLE&rsquo;s applications like v8 or cristal reports</li>
</ol>
[in this case, we need to use odbc drivers in conjunction with ORACLE&rsquo;s ALIAS] Applications ratio 30:70 ORACLE products: non ORACLE products Configuring ODBC [open database connectivity drivers] Start Control panel Odbc 32 bit administration Create new DSN [data source name] Provide DSN name Select ORACLE&rsquo;s odbc drivers Choose the ALIAS name what we defined earlier test the new DSN got created. &nbsp; Creating a DNS &nbsp;domain name server instead of creating a server at each client. &nbsp; <img class="alignnone wp-image-10161" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2015/01/Screenshot_73-460x282.png" alt="SQL Network Errors" width="539" height="329" /> <img class="alignnone wp-image-10162" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2015/01/Screenshot_74-460x356.png" alt="Service Handler" width="466" height="360" /> &nbsp;
<table>
<tbody>
<tr>
<td width="319">Ring</td>
<td width="319">Ping</td>
</tr>
<tr>
<td width="319">Operator</td>
<td width="319">Listener</td>
</tr>
<tr>
<td width="319">Clerk</td>
<td width="319">Db down</td>
</tr>
<tr>
<td width="319">Clerk reject</td>
<td width="319">Db_auth_failed</td>
</tr>
<tr>
<td width="319">Mgr is not available</td>
<td width="319">No service handler for junk.</td>
</tr>
</tbody>
</table>
&nbsp; &nbsp; <img class="alignnone wp-image-10163" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2015/01/Screenshot_75-460x350.png" alt="Neptune and Pluto image" width="553" height="420" /> &nbsp;
Here Neptune works as client &amp; Pluto acts as server to the Neptune &nbsp;
DEMO&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Server SIDE
Dba15 ~] cd $ ORACLE_ HOME/network/admin
Dba15 odmin]$ cp listener.ora $ HOME
Dba15 ~ ] $ vi .bash_profile
Export TNS_ADMIN = $ HOME
:wq
Dba15&nbsp;&nbsp; ~]$&nbsp; . . bash_profile
Dba15 &nbsp; ~]$&nbsp;&nbsp; vi listener.ora &nbsp;
&nbsp; <img class="alignnone wp-image-10164" style="display: block; margin-left: auto; margin-right: auto;" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2015/01/Screenshot_76-460x274.png" alt="Protocol" width="549" height="326" /> &nbsp;
:wq
Dba15 ~]$&nbsp; lsnrctl&nbsp;&nbsp; start listernr_dba15
Dba15 ~]$ p5 &ndash;x
~]$lsnrctl
lsnrctl&gt; help
[for commands] &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;
<h2 style="text-align: justify;">For an in-depth knowledge, click on below</h2>
<ul>
<li><a href="client-side-settings-oracle-dba" target="_blank" rel="noopener">Client-side settings oracle dba</a></li>
</ul>

How to prepare Server and Client side in Oracle DBA

Preparing the Server and Client side in Oracle DBA

preparing-server-client-side-oracle-dba

&nbsp;
<h2>CLIENT SIDE</h2>
Dba12~]$ cd $ ORACLE_HOME / network /admin
Dba12 admin}$ cp tnsnames.ora $ HOME
Dba12 admin ] $ cd
Dba12 ~] $ vi .bash-profile
Export tTNS_ADMIN = $ HOME
:wq
Dba12 &nbsp; ~]$&nbsp; ..bash_profile
Dba12 &nbsp; ~]$&nbsp;&nbsp;&nbsp; vi tnsnames.ora
Tnsnames_dba12=
(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; [Alias]
(
(protocol=tcp) (HOST=192.168.0.158)
(PORT=1111)
[same of dba15] (
SERVICE_NAME=bunny)
[parameter name of dba15}
:wq
<blockquote>
Inclined to build a profession as Oracle DBA Training? Then here is the blog post on, explore <a title="Oracle DBA Training" href="oracle-dba-training" target="_blank" rel="noopener">Oracle DBA Training</a>
</blockquote>
Dba12~] $ tnsping tnsnames_dba12
Dba12 ~] $ sqlplus&nbsp; &lsquo;/as sysbda&rsquo;
Dba12 sys&gt; startup
Dba12 SYS&gt; !
Dba12 &nbsp; ~]$ sqlplus <a href="mailto:Wilshire/Wilshire@tnsnames.dba12">Wilshire/Wilshire@tnsnames.dba12</a>
(dba15&rsquo;s user)
Dba12 SQL &gt; sho user
Dba12 wilshire &gt; select * fro tab;
[his own schema from server dba 15]
Dba15 SYS&gt; select username, machine,
Terminal from V$ version
Where username = &lsquo;WILSHIRE&rsquo;;
Username&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; machine&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; terminal
Wilshire&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; dba12&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; PTS/1
Dba15 SYS&gt; !PS&nbsp; -ef
(for all users remotely)
Dba15 SYS&gt; !PS&nbsp; -x
(for all users locally)
For connecting from dba12 (server) [now acting as client] as sysdba of dba15&rsquo;s SYS
Dba15 ~]$ cd $ ORACLE_HOME/dbs
Dba 15 dbj]$&nbsp; vi initbunny.ora
Remote_login_password file = exclusive
:wq
Dba12 dba]$ orapwd file=orapw$ ORACLE_SID
Password=bunny
Dba15 dbs]$ exit
Dba15 SYS &gt; startup force
Dba15 SYS&gt; select * from v$pwfile_users &nbsp;
Username&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; SYSDBA&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; SYSOPER
SYS&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; TRUE&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; TRUE
Dba12&nbsp; ~]$ sqlplus &lsquo;SYS/SYS @tnsnames_dba12 as SYS dba&rsquo;
Dba12 Sys&gt; sho user
&ldquo;SYS&rdquo; [for the database of bunny of server dba15]
To connect remotely for more than one servers
Dba12 ~]$ vi tnsnames.ora
ALIAS 1 = (
(
(
(protocol=tcp) (Host= 192.168.0.158)
(port = 1111)
(SERVICE _ NAME= bunny) &nbsp;
ALIAS 2 = (
(
(
(protocol=tcp) (Host= 192.168.0.158)
(port =2222)
(SERVICE _ NAME= sunny)
ALIAS 3 = (
(
(
(protocol=tcp) (Host= 192.168.0.158)
(port = 3333)
(SERVICE _ NAME= funny) &nbsp;
<h2 style="text-align: justify;">For an Indepth knowledge on,click on below</h2>
<ul>
<li><a href="preparing-server-client-side-oracle-dba" target="_blank" rel="noopener">Preparing the server and client side in oracle DBA</a></li>
</ul>

How to do client side settings in Oracle DBA

CLIENT SIDE settings in Oracle DBA

client-side-settings-oracle-dba

&nbsp;
<h2>LMTS notes (additional)</h2>
In the case of LMTS either auto or uniform, ORACLE takes care of extent sizes, but ORACLE still supports already defined storage parameters just because of backward compatibilities.
ORACLE calculates the initial demand which may or may not be equal to the initial extent depending on min extents.
But, the post-growth be controlled by ORACLE after meeting &ldquo;initial demand&rdquo;.
User1&gt;&nbsp; create bable test 2 (two number, tname varchar 2 (10))
storage e(initial 100k
next 100k
minextents 5
maxextents 6
pot increase 50);
<img class="alignnone wp-image-10172" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2015/01/Screenshot_77-460x243.png" alt="DMTS and LMTS" width="572" height="301" />&nbsp; &nbsp;&nbsp;
<h3> Initial demands</h3>
100
100
150
225
350
950&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; [opprox] &nbsp;
<blockquote>
Inclined to build a profession as Oracle DBA Training? Then here is the blog post on, explore <a title="Oracle DBA Training" href="oracle-dba-training" target="_blank" rel="noopener">Oracle DBA Training</a>
</blockquote>
<h2> LMTS auto</h2>
&nbsp; <img class="alignnone wp-image-10173" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2015/01/Screenshot_78-460x85.png" alt="LMTS Auto" width="638" height="117" /> <img class="alignnone wp-image-10174" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2015/01/Screenshot_79-460x259.png" alt="TCP/IP LAN" width="580" height="325" /> &nbsp;
&nbsp;
Dblink within one server and only one database but different users &nbsp; <img class="alignnone wp-image-10175" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2015/01/Screenshot_80-460x365.png" alt="Scott Smith" width="511" height="405" /> &nbsp;
<h3>In july </h3>
SQL&gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; create database link pro_dev [100+50-20 = 130]
SQL&gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; select table_name from user_tables; [production&rsquo;s]
100 table
SQL&gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; select table_name from user_tables@ pro_dev;
130 table
<h3>For dropped tables </h3>
SQL&gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; select tabale_name from user_tables minus select table_name from
<a href="mailto:user_tables@pro_dev;">user_tables@pro_dev; </a>
100-130 = 20 tables
<h3>For added tables </h3>
SQL&gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; select table_name from user_table@pro_dev minus select table_name from
user_tables;
130-100 = 50 tables
For altered tables&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;user_mod_columns &nbsp; <img class="alignnone wp-image-10176" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2015/01/Screenshot_81-460x324.png" alt="altered and user mod columns" width="466" height="328" /> &nbsp; &nbsp; <img class="alignnone wp-image-10177" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2015/01/Screenshot_82-460x386.png" alt="Power building user" width="601" height="503" /> &nbsp;
Wilshire: dba15&rsquo;s user&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; dba15 :- server
Rajpal: dba12&rsquo;s user&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; dba12:- client &nbsp;
&nbsp;
Dba15 ~ ] $ cd $ ORACLE_HOME/notwork admin
Dba15 admin]$ cp listener.ora $ HOME
Dba15 admin]$ cd
Dba15&nbsp; ~] $ vi .bash_profile
Export TNS_ADMIN = $ HOME
:wq
Dba15 ~ ] $ .. bash_profile
Dba15&nbsp; ~]$ vi listener.ora
Listener_dba15&nbsp; [Alias]
:wq
Dba15&nbsp; ~] $ lsnrctl start listener_dba15
Dba15&nbsp; ~ ]$ sqlplus&nbsp;&nbsp; &lsquo;/as sysdba&rsquo;
Dba 15&nbsp; SYS &gt; startup
Dba12&nbsp; ~] $ vi .bash_profile
Export TNS_ADMIN = $ HOME
:wq
Dba12 ~ ] $ . .bash_profile
Dba12 ~ ] $ vi tnsnames.ora
Tnsnames_dba12&nbsp; [Alias]
:wq
Dba12 ~ ] $ tnsping tnsnames_dba12
Dba12~]$ sqlplus &lsquo;/as SYSbad&rsquo;
Dba12 SYS &gt; startup
Dba12 &nbsp;SYS&gt; grant create database link to rajpal;
Dba12 SYS&gt;!
Dba12 ~]$ cd $ ORACLE_HOME/dbs
Dba12 dbs] $ vi initrajpar.ora
Global_names=false
{by default true}
:wq
Dba12 dbs]$ exit
Dba12 SYS &gt; startup force
Dba12 SYS&gt; conn rajpal/rajpal
Dba12 rajpal&gt; select * from session_privs; &nbsp; &nbsp; &nbsp; &nbsp;
<h2 style="text-align: justify;">For an in-depth knowledge, click on below</h2>
<ul>
<li><a href="create-database-link-oracle-dba" target="_blank" rel="noopener">Create database link Oracle DBA</a></li>
<li><a href="log-files-oracle-dba" target="_blank" rel="noopener">Log files in Oracle DBA</a></li>
</ul>

LMTS in Oracle DBA

LMTS Notes in Oracle DBA

lmts-notes-oracle-dba

&nbsp;
<h2>Create database link</h2>
Dba12 rajpal&gt; create database link link1 connect
To Wilshire identified by Wilshire
Using tnsnames_dba12&rsquo;;
Dba12 rajpal&gt; &nbsp;&nbsp; select * from <a href="mailto:tab@link1;">tab@link1;</a>
&hellip; the tables of Wilshire
Dba12 rajpal&gt; select * from user_db_links;
Like1
Dba12 rajpal&gt; select * from <a href="mailto:emp@link1;">emp@link1; </a>
Dba12 rajpal&gt; insert into emp@link1 select * from <a href="mailto:emp@link1;">emp@link1; </a>
Dba12 rajpal&gt; comit;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; {only DML Txs not DDL Txs
Dba12 SYS &gt; grant creat synonym to rajpal;
Dba12 rajpal&gt; create synonym el1 for <a href="mailto:emp@link1">emp@link1 </a>
Dba12 raj&gt; select * from el1;
Public database links:&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ram{dba12&rsquo;s another user
Dba12 SYS &gt; grant create public database link to rajpal;
Dba12 rajpal&gt; create public database link plink1 connect to Wilshire identified by Wilshire using tnsnames_dba12;
Dba12 rajpal&gt; select * from user_db_links;
Link1
Plink1
Dba12 ram&gt; select * from user_db_links;
No rows selected
Dba12 ram&gt; select*from all_db_links;
Plink1
Dba12 ram&gt; select * from <a href="mailto:emp@plink1;">emp@plink1;</a>
&hellip;.records of wilshire&rsquo;s emp table.
On public links [only dml.drl, tcl Txs. But not DDL txs]
Dba12 SYS &gt; select * from dba_db_links;
Dba12 SYS&gt; select * from all_db_linnks;
SYS/rajpal&gt; drop database link link1;
&gt;drop public database link plink1; {only SYS [or] rajpal- [creator of the link]
SNAP HOTS (materialized views)
{cjq} co &ndash; ordinate job queue, the background process is responsible to reflect the newly inserted records on the master table
Dba12 SYS&gt; grant create snapshot to rajpal;
Dba12 SYS&gt; !
Dba12 ~ ] $ cd $ ORACLE _ HOME /dbs
Dba12 dbs] $ vi initrajpar.ora
Job_queue_processes+3
[at a time 3 processes]
:wq
Dba12 dbs]$exit
Dba12 SYS &gt; startup force
Dba12 SYS&gt; conn rajpal/rajpal
Dba12 rajpal&gt; select * from session_prives;
Dba12 rajpal&gt; create materialized view mv1 refresh complete with rowed start with sysdate next
sypdate&nbsp;&nbsp; +1 /(24*60*60) as select * from <a href="mailto:emp@link1;">emp@link1; </a>
materialized view created
dba12 rajpal&gt;&nbsp;&nbsp;&nbsp;&nbsp; save mv1.sql
dba12 rajpal&gt; &nbsp;&nbsp;&nbsp; select * from tab;
mv1
dba12 rajpal&gt; select * from mv1;
materialized view with refresh fast option
it should contain or we should assign primory key in master table
log table should be created.
Dba15 wilshire&gt; alter table emp add primary key (empno);
Dba15 wilshire&gt; create materialized view log on emp:
Dba15 wilshire&gt; select * from tab;
MLOG$ _emp&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; These table contains only those records which are
UPA$ _ EMP&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Modified snapshots will receive data from these tables.
DBA12 rajpal&gt; &nbsp;&nbsp; create masterialized view mvf 1 refresh fast with row id start with SYSdate next sys date&nbsp; +1/24*60*60) as select * from <a href="mailto:emp@link1;">emp@link1; </a>
Dba15 wilshire&gt; insert into emp select * from emp;
Dba15 wilshire&gt; select * from molg$_emp;
Dba15 wilshire&gt; commit; &nbsp;
&nbsp;
Views regarding {SNAP SHOTS (MV)
------------------------------------------------
USER_MVIEWS DBA_MVIEWS
For manual refresh&nbsp; - package-
&nbsp; <img class="alignnone wp-image-10179" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2015/01/Screenshot_84-460x139.png" alt="physical structure of database" width="440" height="132" /> <img class="alignnone wp-image-10180" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2015/01/Screenshot_85-460x300.png" alt="Types" width="583" height="379" /> &nbsp; <img class="alignnone wp-image-10181" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2015/01/Screenshot_86-460x152.png" alt="Startup" width="527" height="174" /> &nbsp;
To know the SCN # for any moment
Dbms_flashback_get_system_change_number [ a package]
Controlfile related views
V $ controlfile
V$controlfile_record_section
SYS &gt; select type from v $ controlfile_record_section;
SYS &gt; select name from v$controlfile;
SYS&gt; desc v$ database_incarnation;
SYS &gt; select incarnation#, resetlogs_id from
V$database_incarnation; &nbsp;
<blockquote>
Inclined to build a profession as Oracle DBA Training? Then here is the blog post on, explore <a title="Oracle DBA Training" href="oracle-dba-training" target="_blank" rel="noopener">Oracle DBA Training</a>
</blockquote>
<h2>Multiplexing the control files</h2>
Make a copy of the control file in the new location physically.
SYS &gt; shut immediate
Dba12 ~] $ cd / disk 3 / oradata/pal
Dba12 pal] $ cp cont. ctl / disk4/oradata/pal/cont2.ctl
Dba 12 pal ] $ cd $ ORACLE _ HOME / dbs
Dba12 dbs] $ vi initrajpar.ora
Control _ files = (i/disk3/oradata/pal/cont.ct;, &lsquo;/disk4 / oradata/pal/cont2. Ctl&rsquo;)
:wq
Dba12 dbs ] $ exit
SYS &gt; startup
SYS&gt; select name from v$controlfile;
Name
/disk3 /oradata/pal/cont.ctl
/disk4/oradata/pal/cont2.ctl &nbsp;
<h2>Renaming the database</h2>
Dba12&nbsp; `]$ sqlplus&nbsp; &lsquo;/as stsdba&rsquo;
SYS &gt; startup
SYS&gt; alter database badkup controlfile to trace;
SYS&gt;!
Dba12&nbsp; ~] $ cd /disk4 /oradata/pal/udump
Dba12 udump ] $ ls &ndash; lrt&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; [longlisting reversely with time]
Dba12 udump] $ cp rajpar_ora_20457.trc&nbsp;&nbsp;&nbsp; ~/control.trc
Dba12 udump]$ cd
Dba12 ~] $ vi control.trc
[will be in ASCII format]
In esc mode
Dgg&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; removing upto beginning of the page &nbsp;
Startup nomount&nbsp;&nbsp;
Character setr US7ASCII ; &nbsp;
D G &nbsp;removing up to end of the page
:wq
Dba12 ~ ] $ exit
SYS &gt; shut immediate
SYS&gt;!
Dba12 ~]$ cd $ ORACLE_HOME/dbs
Dba12 dbs]$ vi initrajpar. Ora
Db_name= rajpal1
[max 8 chars}
:wq
Dba12 dbs]$ cd
Dba12 ~ ] $ vi control.trc
Startup nomount change to db-name&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; [No] should be removed
Create controlfile set database &ldquo;rajpal2&rdquo; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; RESETLOGS noarchivelogs
:wq
Dba12 ~ ] $ cd / disk3/oradata/pa;
Dba12 pal]$ cp cont.ctl &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ~
Dba12 pal ] $ rm cont. ctl
Dba 12 pal}$ exit
SYS &gt; @ control.trc
SYS&gt; alter database open resetlogs;
SYS&gt; select incarnation #, resetlogs-id from
V$database_incarnation;
SYS&gt; select name from v$database;
Name
Rajpal2 &nbsp;
<h2 style="text-align: justify;">For an in-depth knowledge, click on below</h2>
<ul>
<li><a title="LMTS Notes in oracle dba" href="lmts-notes-oracle-dba" target="_blank" rel="noopener">LMTS Notes in oracle dba</a></li>
<li><a title="Log files in Oracle DBA" href="log-files-oracle-dba" target="_blank" rel="noopener">Log files in Oracle DBA</a></li>
</ul>

How to create a database link in oracle dba

Create database link Oracle DBA

create-database-link-oracle-dba

&nbsp;
<h2>Redo log files (minimum 4m in ORACLE 10g)</h2>
Reasons for maintaining redo log files;
<ul>
<li>Fast commit mechanism</li>
<li>Roll forward mechanism &amp; roll backward mechanism [through smon]</li>
<li>Recovery operations [through archive files]&nbsp; &nbsp;</li>
</ul>
<h2>To enable or achieve a mod</h2>
In init. Ora file
Up to ORACLE 9i&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; log_archive_start = time
Log_archive_dest= /disk3/oradata/pol/arch
Log_archive_format= %s.arc
In ORACLE 10 g log-archive-dest= % t_%s_%r.arc
%t &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; thread no. [instance]
%s&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; sequence no.
%r &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; resetlog id
<blockquote>
Inclined to build a profession as Oracle DBA Training? Then here is the blog post on, explore <a title="Oracle DBA Training" href="oracle-dba-training" target="_blank" rel="noopener">Oracle DBA Training</a>
</blockquote>
<h3>Note:&nbsp; &nbsp;</h3>
The database should be in the mount stage to enable archive log files.
Demo: Redolog file management
SYS&gt;&nbsp;&nbsp;&nbsp;&nbsp; select member from vi$logfile;
SYS&gt;&nbsp;&nbsp;&nbsp;&nbsp; select member, group# from v$logfile;
SYS&gt;&nbsp;&nbsp;&nbsp;&nbsp; desc v$log
SYS&gt; &nbsp;&nbsp;&nbsp; select group#, members, status from v$log;
SYS&gt; &nbsp;&nbsp;&nbsp; save vdlog
SYS&gt; &nbsp;&nbsp;&nbsp; alter system switch logfile;
[for manual swithch]
SYS&gt;&nbsp;&nbsp;&nbsp;&nbsp; alter database add logfile group3;
&lsquo;disk3/oradata/pal/redo3.dbf&rsquo;size 4m;
SYS&gt;&nbsp;&nbsp;&nbsp;&nbsp; @vdlog
Group1
Group2
Group 3&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; unused
SYS&gt;&nbsp;&nbsp;&nbsp;&nbsp; alter system switch logfile;
SYS&gt; &nbsp;&nbsp;&nbsp; alter system switch logfile;
SYS&gt; &nbsp;&nbsp;&nbsp; @vdlog
To drop the redo log file, it&rsquo;s status should be &lsquo;INACTIVE&rsquo;
SYS&gt; &nbsp;&nbsp;&nbsp; alter database drop logfile group3; &nbsp;
<h2>Enabling the database into archive log mode</h2>
Dba 12 ~] $ sq!plus &lsquo;/as sysdba&rsquo;
SYS&gt; startup
SYS&gt; archive log list
Archive log&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; disable
SYS&gt;&nbsp;&nbsp;&nbsp;&nbsp; shut immediate
SYS&gt;!
Dba12 ~]$ cd $ORACLE_HOME/ dbs
Dba12 dbs] $ vi initrajpar.ora
Log_archive_dest=disk3/oradatga/pal/arch
:wq
Dba12 dbs ] $ cd /disk3/oradata/pal/
Dba12 pal]$ mkdir arch
Dba12 pal] $ ls
Dba12 pal]$ exit
SYS &gt; startup mount
SYS&gt; alter database archivelog;
SYS&gt; alter database open;
SYS&gt; archive log list
Archivelog&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; enable
SYS&gt; !ps&nbsp; - x
SYS&gt;&nbsp;&nbsp;&nbsp;&nbsp; grant connect, resource to user 2
Identified by user2;
SYS&gt; conn user 2 / user 2
User2&gt; ! demobld user 2 / user2
User 2&gt; select * from tab;
User 2&gt; insert inot emp select * from emp;
User 2 &gt; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; /
User 2 &gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; /&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; [up to some thousands of records]
User2&gt;!
Dba12 ~] $ cd / disk 3 /oradata /pal /arch
Dba12 arch ] $ ls
&nbsp; <img class="alignnone wp-image-10185" src="https://newtekslateassets.s3.us-west-2.amazonaws.com/wp-content/uploads/2015/01/Screenshot_87-460x227.png" alt="tsr" width="387" height="191" /> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;
<h2 style="text-align: justify;">For an in-depth knowledge, click on below</h2>
<ul>
<li><a href="create-database-link-oracle-dba" target="_blank" rel="noopener">Create database link oracle dba</a></li>
<li><a href="lmts-notes-oracle-dba" target="_blank" rel="noopener">LMTS Notes in Oracle DBA</a></li>
</ul>

Log Files in Oracle Database

Log Files in Oracle DBA

log-files-oracle-dba

&nbsp;
<h2>Oracle DBA Sample Resume</h2>
123 Main street, San Francisco, California.
(415)-546-789
jamesbond@yahoo.com
<h3 style="text-align: justify;">Professional summary :&nbsp;</h3>
<ul style="text-align: justify;">
<li>4.5 years experience in maintaining oracle database running LINUX and Windows.</li>
<li>Good experience in DataBase Configuration and managing oracle databases running on MS- Windows and Linux.</li>
<li>Good communication skills with the system administrator and development team.</li>
<li>Involved with installation and maintenance of Oracle 9i, 10g, and standby database for a better purpose.</li>
<li>Extensive experience in writing Linux scripts.</li>
<li>Good experience in Oracle forms and developing reports.</li>
<li>Expertise in technical skills (MS-SQL, MY SQL).</li>
</ul>
<h3>Technical Skills :</h3>
<ul style="text-align: justify;">
<li>Operating systems: Linux, Sun Solaris.</li>
<li>Databases: Oracle 12c, 11g, SQL server 2007.</li>
<li>Programming Skills: C, C++, SQL, PL SQL.</li>
<li>Tools: Oracle Management server, SQL loader.</li>
</ul>
<blockquote>
Inclined to build a profession as Oracle DBA Training? Then here is the blog post on, explore <a title="Oracle DBA Training" href="oracle-dba-training" target="_blank" rel="noopener">Oracle DBA Training</a>
</blockquote>
<h3>Academic Profile :</h3>
Bachelor of Engineering in Computer Science Engineering at Southern Methodist University, Dallas.
<h3>Professional Experience :</h3>
Oracle DBA Developer April-2010- At Present, Green Dot, Los-Angels.
<h3>Roles And Responsibilities :</h3>
<ul style="text-align: justify;">
<li>Involved in Installation and Configuration of OEM Grid Control.</li>
<li>Refreshing the database applications for developing and testing process.</li>
<li>Configured the oracle standby database to offload the running of huge projects.</li>
<li>Monitoring the performance and availability of applications.</li>
<li>Upgrade the production and development database from one to another like 8i to 9i, 11g to 12c.</li>
<li>Worked out on operating systems and command-line utilities.</li>
</ul>
<h3>Accomplishments :</h3>
<ul style="text-align: justify;">
<li>Implemented third-party solutions.</li>
<li>Established an IT team to support database operations.</li>
</ul>
<h3>Oracle DBA Sample Resume&nbsp; Secrets You Need to Know :</h3>
<ul>
<li style="text-align: justify;">Highlight your Roles and responsibilities.</li>
<li style="text-align: justify;">Bold the most recent jobs you have done.</li>
<li style="text-align: justify;">Invest time in highlighting the most relevant skills.</li>
<li style="text-align: justify;">Showcase transparency in Employment Gaps (if any).</li>
<li style="text-align: justify;">Make it clear in the &lsquo;Objectives&rsquo; that you are qualified for the types of DBA jobs you are applying.</li>
<li style="text-align: justify;">Demonstrate the extensive knowledge involved in attaining complex goals.</li>
</ul>
<h3>Download Oracle DBA Sample Resume (PDF) Here :</h3>
Oracle DBA Sample Resume
<a style="background-color: #e94350; color: #ffffff; padding: 10px 20px;" href="Oracle-DBA-interview-questions-answers" target="_blank" rel="noopener">Oracle DBA Interview Questions</a>