The datatype to hold XML in PL/SQL or in the database is XMLTYPE. So you can use the generated XML in PL/SQL, store it in table (XMLTYPE column), transform it to a CLOB (using the XMLTYPE getClobVal member function which I use in the examples) and write it to a file.
XMLTYPE
The easiest way to create an XML document, is using the constructor of XMLTYPE. This constructor can have several datatypes as input, like a CLOB and VARCHAR2, but as we’re going to base our XML on table data, we’re using a REF CURSOR.
You can create a ref cursor and pass on this ref cursor to the XMLTYPE constructor like this:
DECLARE
l_refcursor SYS_REFCURSOR;
l_xmltype XMLTYPE;
BEGIN
OPEN l_refcursor FOR SELECT department_id
, department_name
FROM departments
WHERE department_id IN (10,20);
l_xmltype := XMLTYPE(l_refcursor);
dbms_output.put_line(l_xmltype.getClobVal);
END;
/
<?xml version="1.0"?> <ROWSET> <ROW> <DEPARTMENT_ID>10</DEPARTMENT_ID> <DEPARTMENT_NAME>Administration</DEPARTMENT_NAME> </ROW> <ROW> <DEPARTMENT_ID>20</DEPARTMENT_ID> <DEPARTMENT_NAME>Marketing</DEPARTMENT_NAME> </ROW> </ROWSET>
As you can see it creates a simple XML document where each row is seperated with a ROW tag.
DBMS_XMLGEN
The DBMS_XMLGEN built-in is similar to the XMLTYPE constructor, but accepts a query directly:
DECLARE
l_xmltype XMLTYPE;
BEGIN
l_xmltype := dbms_xmlgen.getxmltype('SELECT department_id
, department_name
FROM departments
WHERE department_id IN (10,20)'
);
dbms_output.put_line(l_xmltype.getClobVal);
END;
/
<ROWSET> <ROW> <DEPARTMENT_ID>10</DEPARTMENT_ID> <DEPARTMENT_NAME>Administration</DEPARTMENT_NAME> </ROW> <ROW> <DEPARTMENT_ID>20</DEPARTMENT_ID> <DEPARTMENT_NAME>Marketing</DEPARTMENT_NAME> </ROW> </ROWSET>
But it also provides procedures to change the ROWSET and ROW tags.
DECLARE
l_xmltype XMLTYPE;
l_ctx dbms_xmlgen.ctxhandle;
BEGIN
l_ctx := dbms_xmlgen.newcontext('SELECT department_id
, department_name
FROM departments
WHERE department_id in (10,20)'
);
dbms_xmlgen.setrowsettag(l_ctx, 'Departments');
dbms_xmlgen.setrowtag(l_ctx, 'Dept');
l_xmltype := dbms_xmlgen.getXmlType(l_ctx) ;
dbms_xmlgen.closeContext(l_ctx);
dbms_output.put_line(l_xmltype.getClobVal);
End;
/
<Departments> <Dept> <DEPARTMENT_ID>10</DEPARTMENT_ID> <DEPARTMENT_NAME>Administration</DEPARTMENT_NAME> </Dept> <Dept> <DEPARTMENT_ID>20</DEPARTMENT_ID> <DEPARTMENT_NAME>Marketing</DEPARTMENT_NAME> </Dept> </Departments>
dbms_xmldom
With the XMLTYPE constructor and DBMS_XMLGEN package, you can create simple XML documents, fast and easy. When you need to create more advanced XML documents or want to have more control on how your XML document looks like, DBMS_XMLDOM can be used. The DBMS_XMLDOM package is a bit more complicated as you’ll have to create the entire document by calling functions and procedures of the package.
The following example creates an XML document with the department information retrieved from the query. In short, this is how it works: create new elements and add them as a (child) node.
DECLARE
l_xmltype XMLTYPE;
l_domdoc dbms_xmldom.DOMDocument;
l_root_node dbms_xmldom.DOMNode;
l_department_element dbms_xmldom.DOMElement;
l_departments_node dbms_xmldom.DOMNode;
l_dept_element dbms_xmldom.DOMElement;
l_dept_node dbms_xmldom.DOMNode;
l_name_element dbms_xmldom.DOMElement;
l_name_node dbms_xmldom.DOMNode;
l_name_text dbms_xmldom.DOMText;
l_name_textnode dbms_xmldom.DOMNode;
l_location_element dbms_xmldom.DOMElement;
l_location_node dbms_xmldom.DOMNode;
l_location_text dbms_xmldom.DOMText;
l_location_textnode dbms_xmldom.DOMNode;
BEGIN
-- Create an empty XML document
l_domdoc := dbms_xmldom.newDomDocument;
-- Create a root node
l_root_node := dbms_xmldom.makeNode(l_domdoc);
-- Create a new node Departments and add it to the root node
l_department_element := dbms_xmldom.createElement(l_domdoc, 'Deptartments' );
l_departments_node := dbms_xmldom.appendChild(l_root_node,dbms_xmldom.makeNode(l_department_element));
FOR r_dept IN (SELECT dept.department_id
, dept.department_name
, loc.city
FROM departments dept
JOIN locations loc
ON loc.location_id = dept.location_id
WHERE dept.department_id IN (10,20)
)
LOOP
-- For each record, create a new Dept element with the Department ID as attribute.
-- and add this new Dept element to the Departments node
l_dept_element := dbms_xmldom.createElement(l_domdoc, 'Dept' );
dbms_xmldom.setAttribute(l_dept_element, 'DeptID', r_dept.department_id);
l_dept_node := dbms_xmldom.appendChild(l_departments_node,dbms_xmldom.makeNode(l_dept_element));
-- Each Dept node will get a Name node which contains the department name as text
l_name_element := dbms_xmldom.createElement(l_domdoc, 'Name' );
l_name_node := dbms_xmldom.appendChild(l_dept_node,dbms_xmldom.makeNode(l_name_element));
l_name_text := dbms_xmldom.createTextNode(l_domdoc, r_dept.department_name );
l_name_textnode := dbms_xmldom.appendChild(l_name_node,dbms_xmldom.makeNode(l_name_text));
-- Each Dept node will aslo get a Location node which contains the location(city) as text
l_location_element := dbms_xmldom.createElement(l_domdoc, 'Location' );
l_location_node := dbms_xmldom.appendChild(l_dept_node,dbms_xmldom.makeNode(l_location_element));
l_location_text := dbms_xmldom.createTextNode(l_domdoc, r_dept.city );
l_location_textnode := dbms_xmldom.appendChild(l_location_node,dbms_xmldom.makeNode(l_location_text));
END LOOP;
l_xmltype := dbms_xmldom.getXmlType(l_domdoc);
dbms_xmldom.freeDocument(l_domdoc);
dbms_output.put_line(l_xmltype.getClobVal);
END;
/
<Deptartments> <Dept DeptID="10"> <Name>Administration</Name> <Location>Seattle</Location> </Dept> <Dept DeptID="20"> <Name>Marketing</Name> <Location>Toronto</Location> </Dept> </Deptartments>
In this second example I added the employees to each department. I created a new cursor loop on employees inside the department cursor loop, so that the employees of that department are added in a child node of the department. I also changed some code: I cast(dbms_xmldom.makeNode) the elements directly to a node, this way I need less variables:
DECLARE
l_domdoc dbms_xmldom.DOMDocument;
l_xmltype XMLTYPE;
l_root_node dbms_xmldom.DOMNode;
l_departments_node dbms_xmldom.DOMNode;
l_dept_element dbms_xmldom.DOMElement;
l_dept_node dbms_xmldom.DOMNode;
l_name_node dbms_xmldom.DOMNode;
l_name_textnode dbms_xmldom.DOMNode;
l_location_node dbms_xmldom.DOMNode;
l_location_textnode dbms_xmldom.DOMNode;
l_employees_node dbms_xmldom.DOMNode;
l_emp_element dbms_xmldom.DOMElement;
l_emp_node dbms_xmldom.DOMNode;
l_emp_first_name_node dbms_xmldom.DOMNode;
l_emp_first_name_textnode dbms_xmldom.DOMNode;
l_emp_last_name_node dbms_xmldom.DOMNode;
l_emp_last_name_textnode dbms_xmldom.DOMNode;
BEGIN
-- Create an empty XML document
l_domdoc := dbms_xmldom.newDomDocument;
-- Create a root node
l_root_node := dbms_xmldom.makeNode(l_domdoc);
-- Create a new node Departments and add it to the root node
l_departments_node := dbms_xmldom.appendChild( l_root_node
, dbms_xmldom.makeNode(dbms_xmldom.createElement(l_domdoc, 'Deptartments' ))
);
FOR r_dept IN (SELECT dept.department_id
, dept.department_name
, loc.city
FROM departments dept
JOIN locations loc
ON loc.location_id = dept.location_id
WHERE dept.department_id IN (10,20)
)
LOOP
-- For each record, create a new Dept element with the Department ID as attribute.
-- and add this new Dept element to the Departments node
l_dept_element := dbms_xmldom.createElement(l_domdoc, 'Dept' );
dbms_xmldom.setAttribute(l_dept_element, 'Deptno', r_dept.Department_Id );
l_dept_node := dbms_xmldom.appendChild( l_departments_node
, dbms_xmldom.makeNode(l_dept_element)
);
-- Each Dept node will get a Name node which contains the department name as text
l_name_node := dbms_xmldom.appendChild( l_dept_node
, dbms_xmldom.makeNode(dbms_xmldom.createElement(l_domdoc, 'Name' ))
);
l_name_textnode := dbms_xmldom.appendChild( l_name_node
, dbms_xmldom.makeNode(dbms_xmldom.createTextNode(l_domdoc, r_dept.department_name ))
);
-- Each Dept node will aslo get a Location node which contains the location(city) as text
l_location_node := dbms_xmldom.appendChild( l_dept_node
, dbms_xmldom.makeNode(dbms_xmldom.createElement(l_domdoc, 'Location' ))
);
l_location_textnode := dbms_xmldom.appendChild( l_location_node
, dbms_xmldom.makeNode(dbms_xmldom.createTextNode(l_domdoc, r_dept.city ))
);
-- For each department, add an Employees node
l_employees_node := dbms_xmldom.appendChild( l_dept_node
, dbms_xmldom.makeNode(dbms_xmldom.createElement(l_domdoc, 'Employees' ))
);
FOR r_emp IN (SELECT employee_id
, first_name
, last_name
FROM employees
WHERE department_id = r_dept.department_id
)
LOOP
-- For each record, create a new Emp element with the Employee ID as attribute.
-- and add this new Emp element to the Employees node
l_emp_element := dbms_xmldom.createElement(l_domdoc, 'Emp' );
dbms_xmldom.setAttribute(l_emp_element, 'empid', r_emp.employee_id );
l_emp_node := dbms_xmldom.appendChild( l_employees_node
, dbms_xmldom.makeNode(l_emp_element)
);
-- Each emp node will get a First name and Last name node which contains the first name and last name as text
l_emp_first_name_node := dbms_xmldom.appendChild( l_emp_node
, dbms_xmldom.makeNode(dbms_xmldom.createElement(l_domdoc, 'FirstName' ))
);
l_emp_first_name_textnode := dbms_xmldom.appendChild( l_emp_first_name_node
, dbms_xmldom.makeNode(dbms_xmldom.createTextNode(l_domdoc, r_emp.first_name ))
);
l_emp_last_name_node := dbms_xmldom.appendChild( l_emp_node
, dbms_xmldom.makeNode(dbms_xmldom.createElement(l_domdoc, 'LastName' ))
);
l_emp_last_name_textnode := dbms_xmldom.appendChild( l_emp_last_name_node
, dbms_xmldom.makeNode(dbms_xmldom.createTextNode(l_domdoc, r_emp.last_name ))
);
END LOOP;
END LOOP;
l_xmltype := dbms_xmldom.getXmlType(l_domdoc);
dbms_xmldom.freeDocument(l_domdoc);
dbms_output.put_line(l_xmltype.getClobVal);
END;
/
This is how the final example looks, with the employees working for the department added in the XML document:
<Deptartments>
<Dept Deptno="10">
<Name>Administration</Name>
<Location>Seattle</Location>
<Employees>
<Emp empid="200">
<FirstName>Jennifer</FirstName>
<LastName>Whalen</LastName>
</Emp>
</Employees>
</Dept>
<Dept Deptno="20">
<Name>Marketing</Name>
<Location>Toronto</Location>
<Employees>
<Emp empid="201">
<FirstName>Michael</FirstName>
<LastName>Hartstein</LastName>
</Emp>
<Emp empid="202">
<FirstName>Pat</FirstName>
<LastName>Fay</LastName>
</Emp>
</Employees>
</Dept>
</Deptartments>
As you can see DBMS_XMLDOM is the most advanced way to create XML documents from table data, but it can also be used to create a document from different sources (query, CLOB, …), to search in XML documents, and to change documents (e.g. add nodes to an existing document).
Prerequisites
- A local database, for development
- SQL Developer version 3.2.20.09.87 or higher (Download)
- Wallet Manager
Getting started
To get started, we first need an account on the Oracle cloud. So go to Oracle Cloud, login with your OTN account and request a trial period. You will need to enter a credit card for verification, but it will not get charged with anything.
Once you have an account, we first need to setup and configure some of the basics, when dealing with the Oracle cloud. At first you should login to your cloud account at cloud.oracle.com. Here it will list the different services that you subscribe to. Note down the Identity Domain name, since we will use this throughout the entire article. This is your unique identifier at oracle.
Click the services link as show here:
Service Details
and note down the following from the details section, of the overview tab:
Service Details
Host – This is the host URL assigned to your account, and we need this for our SQL Developer connection and later on, as we develop a REST service, for our demo application.
Service SFTP Details
We need host, port and username for the Service SFTP account. This account is used in the SQL Developer connection, when we upload tables/data/objects to the cloud database. The other SFTP account we do not need for this demo. It is for downloading datapump exports, if you choose to close your account.
Changing SFTP account password
On the services page click on the Identity Console button:
When the identity console is loaded, click manage users link, enter a percent sign into the search box and press search. From the result list choose the username, that is equal to our SFTP details from the last paragraph. Click the change password button, and change the password to something known.
Creating Cloud SQL Developer user and connection
Once we changed the password we are ready to login to the database service for first time, and create the user we need for the connection from SQL Developer. Go back to the Service page, and click the “Launch Service” button:
Launch service button
Log in to APEX using your Identity Domain name and your cloud account.
Once we are logged into APEX we need to create a user for the SQL Developer connection. Click on the “Administration” tab, and choose “Manage Users and Groups”. Click on the “Create User” button, and create a user.
Fill in username, email address, a password (change requirement to change password at login, to no) and choose “SQL Developer” under user groups:
Once we are logged into APEX we need to create a user for the SQL Developer connection. Click on the “Administration” tab, and choose “Manage Users and Groups”. Click on the “Create User” button, and create a user.
Fill in username, email address, a password (change requirement to change password at login, to no) and choose “SQL Developer” under user groups:
Now the user is created, we need to start up SQL Developer. Once started right click “Cloud Connections” and choose “New Cloud Connection”. Choose a name for your connection (I tend to use my identity domain name). The username for the Database, is the user we have just created in APEX, and the host, is the string that we noted down, from earlier, on the service details screen. It should be something like https://database-trial(identity-domain).db.(datacenter location).oraclecloudapps.com/apex/. The SFTP connection we also collected from the service details screen earlier:
SQL Developer connection screenshot
Once all that is filled in, we should be able to expand that connection, type in the password, and see some of the demo tables, that are automatically deployed to a trial account.
Creating development schema in our local database
Create a sys connection to a local database, that we are going to use to develop our demo application. In that database create a user defined as follows:
create user localcloud identified by localcloud default tablespace users temporary tablespace temp; grant create session, resource to localcloud;
When the user is created, create a connection within SQL Developer to that user.
Let us move on to the actual demo application.
Our demo application
Our small demo application, is going to be a central location, to register errors from all our local databases, so we can see which type of user errors are occuring the most, and we can spot trends in application usage. For this we will develop a REST webservice that our local databases will call whenever an error occurs, and we will create a small APEX application to report on the errors and occurrences.
Creating the database objects and deploying them to the cloud
For this small application we will create 2 tables. One to hold information about the databases that calls our webservice, and one to contain a row for each error that occurs in our databases. Create these tables inside the localcloud schema, that was created earlier.
DATABASES table:
create table databases ( db_id number, db_name varchar2(4000), db_reg date ); alter table databases add constraint db_pk primary key(db_id);
DATABASE_ERRORS table:
create table database_errors ( db_id number constraint db_err_id_ref references databases(db_id), error_num number , error_reg date );
Once they are are created, I’ll insert a row for my local database, so I have a test database available.
To get the information to insert, perform the following select as sys against your local database:
SQL> select dbid, name from v$database;
DBID NAME
---------- ---------
1404381942 ME11
with that I will insert a row into the databases table, as the localcloud user:
insert into databases values (1404381942, 'ME11', sysdate); commit;
Transferring tables and data to your cloud database
Inside SQL Developer, navigate to View->Cart, to open the “cloud shopping cart”:
This is tool we use to transfer database objects from our local database to the cloud database. We simply drag and drop the objects from our local connection tree, into the cart. When in the cart, mark the option to transfer data for the databases table and click the “Deploy Cloud” button and SQL Developer will package it into a zip file, sftp it to your services account, and deploy it to the database:
SQL Developer – Deploy from cart to cloud
To check the status of your deployment, navigate to the “Deployments” option the cloud connection tree, and look at the status field. When it says “Processed” the deployment is finished and we should be able to see the tables we created in our local database, inside the cloud database as well.
Creating a REST webservice
Oracle Cloud, does not accept sqlnet connections, and you cannot deploy software to the service. So the only way to communicate with the cloud from the outside world, is using http protocol. To allow our local databases to send data to our cloud application we will create a REST webservice inside APEX, and call that to register errors happening on the local database. To create a REST webservice log into your cloud account, and launch the APEX service. Inside APEX navigate to “SQL Workshop”->”RESTful Services”.
Click the create button, and fill out the first form page as shown here:
Create REST Module and Template
What we have created here is the basic definition of a REST service. Now we need to create a handler to do something when our REST url is called. Click the “Create Handler” link below the “demo_errors/{dbid}/{errornum}” template, and fill out the form as follows:
Create REST Handler
Now we can test the service. Click the “Set Bind Variables” button, and set the value of DBID to what you insert earlier into the databases table (in my case it was 1404381942), and set errornum to any number, and click the “Test” button. Depending on whether or not Oracle has fixed the bug, the test window might fail with the following:
As you can see, the second parameter {errornum} is not set to the value, that was typed in the set bind variables screen. To fix this copy and paste the url to a new browser window, change %7Berrornum%7D with a random number, and you should now see:
Successful REST call
Creating a couple of APEX screens
To see the data that our webservice inserts, I will just create one simple screen. I am not an APEX expert, so it will be default all the way. So click the Application Builder option, and choose “Create” to make a new application. Choose a database application. On the next screen give it a name (I chose “ATO Demo App”), and click the “Create Application” button, defaulting all other steps. When done, you should see a list of pages like the following:
Apex page list
Click on the home link, and when you get to the page definition, right click on “Position 01″ and choose “Create”. Choose “Chart” as region type and click next. Choose a column chart and click next. Choose a simple 2D Column type, and continue. Give it a Title: “Daily Error Count” and click next. Give the same title, and just accept rest as default and click next. As the source we wil use the following SQL:
select null link, to_char(trunc(error_reg, 'DD'),'DD-MM') value, count(error_num) from database_errors group by to_char(trunc(error_reg, 'DD'),'DD-MM') order by 2 asc;
ignore the rest, and click next. Accept defaults, and click “Create Region”. Try and run the application, and you should see something like this:
Apex Application
So now we have a webservice that, when called will insert data into our database_errors table. We have a simple screen to display that data as a graph. So how do we call the webservice from outside the cloud.
Calling Oracle Cloud REST Services from a local database
The first thing we need to do is download the certificates from the Oracle Cloud. I used Firefox, so click on the litte lock to left of the url address bar, when you are logged into your service, and press more information. Go to the security tab and press show certificates:
Show Certificates
Now export both the certificate for the oraclecloudapps.com domain, and the Verisign certificate above that, each to a .crt file:
Certificate Export
Now we start up the Wallet Manager from our local database installation. Create a new wallet, and note down the password to the wallet, and select no to create a certificate request:
Wallet Manager – create wallet
Go to the Operations menu, and select “Import trusted certificate”, and import the certificates, that we just exported. Save the wallet, and note down the folder you saved it to.
Next we log into the local database as the sys user. If the database version is 11g (like mine) we first need to create an ACL for the localcloud user. Run the following logged in as sys:
begin
dbms_network_acl_admin.create_acl (
acl => 'cloud_http.xml',
description => 'HTTP Access',
principal => 'LOCALCLOUD',
is_grant => TRUE,
privilege => 'connect',
start_date => null,
end_date => null
);
dbms_network_acl_admin.add_privilege (
acl => 'cloud_http.xml',
principal => 'LOCALCLOUD',
is_grant => TRUE,
privilege => 'resolve',
start_date => null,
end_date => null
);
dbms_network_acl_admin.assign_acl (
acl => 'cloud_http.xml',
host => '*.oraclecloudapps.com',
lower_port => 443,
upper_port => 443
);
commit;
end;
/
Now log into the local database as localcloud. Lets say that I saved my wallet to c:\temp and I chose the password Manager123, and I use the URL from the REST service test, to execute the following:
exec utl_http.set_wallet('file:c:\temp', 'Manager123');
select utl_http.request('https://database-trialxxxxx.db.xxx.oraclecloudapps.com/apex/ato/demo_errors/1404381942/1555') from dual;
You should see a result like the following:
SQL> select utl_http.request('https://database-trialxxxx.db.xxx.oraclecloudapps.com/apex/ato/demo_errors/1404381942/1555') from dual;
UTL_HTTP.REQUEST('HTTPS://DATABASE-TRIALXXX.DB.XXX.ORACLECLOUDAPPS.COM/APEX/ATO
--------------------------------------------------------------------------------
Error registered
Now to start registering all errors automatically, we can create a system event trigger as sys, but owned by localcloud, that will call the webservice every time an error happens:
create or replace trigger localcloud.demo_errors
after servererror
on database
declare
captured_sql_id varchar2(1000);
error_stack_count number;
sql_reg varchar2(32000) := 'select utl_http.request(''https://database-trialxxxx.db.xxx.oraclecloudapps.com/apex/ato/demo_errors/1404381942/#ERR#'') from dual';
begin
error_stack_count := ora_server_error_depth;
exec utl_http.set_wallet('file:c:\temp', 'Manager123');
for x in 1..error_stack_count loop
execute immediate replace(sql_reg,'#ERR#', ora_server_error(x));
end loop;
end db_ping_errors;
/
Note: Please remember to replace the “xxxx” with the correct identity domain and serverlocation.
Now log back into your cloud service, and run the ATO Demo App, and the bar chart should show at least one more error for today.
So there you have it, your first litte demo application running in the Oracle cloud. In the next article, I will talk about how you manage the different services in the cloud, compared to managing a normal oracle database.
Oracle Database 12c New Features – Part 1
During this Oracle Database 12c new features article series, I shall be extensively exploring some of the very important new additions and enhancements introduced in the area of Database Administration, RMAN, High Availability and Performance Tuning.
Part I covers:
- Online migration of an active data file
- Online table partition or sub-partition migration
- Invisible column
- Multiple indexes on the same column
- DDL logging
- Temporary undo in- and- outs
- New backup user privilege
- How to execute SQL statement in RMAN
- Table level recovery in RMAN
- Restricting PGA size
1. Online rename and relocation of an active data file
Unlike in the previous releases, a data file migration or renaming in Oracle database 12c R1 no longer requires a number of steps i.e. putting the tablespace in READ ONLY mode, followed by data file offline action. In 12c R1, a data file can be renamed or moved online simply using the ALTER DATABASE MOVE DATAFILE SQL statement. While the data file is being transferred, the end user can perform queries, DML and DDL tasks. Additionally, data files can be migrated between storages e.g. from non-ASM to ASM and vice versa.
Rename a data file:
SQL> ALTER DATABASE MOVE DATAFILE '/u00/data/users01.dbf' TO '/u00/data/users_01.dbf';
Migrate a data file from non-ASM to ASM:
SQL> ALTER DATABASE MOVE DATAFILE '/u00/data/users_01.dbf' TO '+DG_DATA';
Migrate a data file from one ASM disk group to another:
SQL> ALTER DATABASE MOVE DATAFILE '+DG_DATA/DBNAME/DATAFILE/users_01.dbf ' TO '+DG_DATA_02';
Overwrite the data file with the same name, if it exists at the new location:
SQL> ALTER DATABASE MOVE DATAFILE '/u00/data/users_01.dbf' TO '/u00/data_new/users_01.dbf' REUSE;
Copy the file to a new location whilst retaining the old copy in the old location:
SQL> ALTER DATABASE MOVE DATAFILE '/u00/data/users_01.dbf' TO '/u00/data_new/users_01.dbf' KEEP;
You can monitor the progress while a data file being moved by querying the v$session_longops dynamic view. Additionally, you can also refer the alert.log of the database where Oracle writes the details about action being taken place.
2. Online migration of table partition or sub-partition
Migration of a table partition or sub-partition to a different tablespace no longer requires a complex procedure in Oracle 12c R1. In a similar way to how a heap (non-partition) table online migration was achieved in the previous releases, a table partition or sub-partition can be moved to a different tablespace online or offline. When an ONLINE clause is specified, all DML operations can be performed without any interruption on the partition|sub-partition which is involved in the procedure. In contrast, no DML operations are allowed if the partition|sub-partition is moved offline.
Here are some working examples:
SQL> ALTER TABLE table_name MOVE PARTITION|SUBPARTITION partition_name TO tablespace tablespace_name;
SQL> ALTER TABLE table_name MOVE PARTITION|SUBPARTITION partition_name TO tablespace tablespace_name UPDATE INDEXES ONLINE;
The first example is used to move a table partition|sub-partition to a new tablespace offline. The second example moves a table partition/sub-partitioning online maintaining any local/global indexes on the table. Additionally, no DML operation will get interrupted when ONLINE clause is mentioned.
Important notes:
- The UPDATE INDEXES clause will avoid any local/global indexes going unusable on the table.
- Table online migration restriction applies here too.
- There will be locking mechanism involved to complete the procedure, also it might leads to performance degradation and can generate huge redo, depending upon the size of the partition, sub-partition.
3. Invisible columns
In Oracle 11g R1, Oracle introduced a couple of good enhancements in the form of invisible indexes and virtual columns. Taking the legacy forward, invisible column concepts has been introduced in Oracle 12c R1. I still remember, in the previous releases, to hide important data –columns from being displayed in the generic queries– we used to create a view hiding the required information or apply some sort of security conditions.
In 12c R1, you can now have an invisible column in a table. When a column is defined as invisible, the column won’t appear in generic queries, unless the column is explicitly referred to in the SQL statement or condition, or DESCRIBED in the table definition. It is pretty easy to add or modify a column to be invisible and vice versa:
SQL> CREATE TABLE emp (eno number(6), ename name varchar2(40), sal number(9) INVISIBLE);
SQL> ALTER TABLE emp MODIFY (sal visible);
You must explicitly refer to the invisible column name with the INSERT statement to insert the database into invisible columns. A virtual column or partition column can be defined as invisible too. However, temporary tables, external tables and cluster tables won’t support invisible columns.
4. Multiple indexes on the same column
Pre Oracle 12c, you can’t create multiple indexes either on the same column or set of columns in any form. For example, if you have an index on column {a} or columns {a,b}, you can’t create another index on the same column or set of columns in the same order. In 12c, you can have multiple indexes on the same column or set of columns as long as the index type is different. However, only one type of index is usable/visible at a given time. In order to test the invisible indexes, you need to set the optimizer_use_use_invisible_indexes=true.
Here’s an the example:
SQL> CREATE INDEX emp_ind1 ON EMP(ENO,ENAME);
SQL> CREATE BITMAP INDEX emp_ind2 ON EMP(ENO,ENAME) INVISIBLE;
5. DDL logging
There was no direction option available to log the DDL action in the previous releases. In 12cR1, you can now log the DDL action into xml and log files. This will be very useful to know when the drop or create command was executed and by who. The ENABLE_DDL_LOGGING initiation parameter must be configured in order to turn on this feature. The parameter can be set at the database or session levels. When this parameter is enabled, all DDL commands are logged in an xml and a log file under the $ORACLE_BASE/diag/rdbms/DBNAME/log|ddl location. An xml file contains information, such as DDL command, IP address, timestamp etc. This helps to identify when a user or table dropped or when a DDL statement is triggered.
To enable DDL logging
SQL> ALTER SYSTEM|SESSION SET ENABLE_DDL_LOGGING=TRUE;
The following DDL statements are likely to be recorded in the xml/log file:
- CREATE|ALTER|DROP|TRUNCATE TABLE
- DROP USER
- CREATE|ALTER|DROP PACKAGE|FUNCTION|VIEW|SYNONYM|SEQUENCE
6. Temporary Undo
Each Oracle database contains a set of system related tablespaces, such as, SYSTEM, SYSAUX, UNDO & TEMP, and each are used for different purposes within the Oracle database. Pre Oracle 12c R1, undo records generated by the temporary tables used to be stored in undo tablespace, much similar to a general/persistent table undo records. However, with the temporary undo feature in 12c R1, the temporary undo records can now be stored in a temporary table instead of stored in undo tablespace. The prime benefits of temporary undo includes: reduction in undo tablespace and less redo data generation as the information won’t be logged in redo logs. You have the flexibility to enable the temporary undo option either at session level or database level.
Enabling temporary undo
To be able to use the new feature, the following needs to be set:
- Compatibility parameter must be set to 12.0.0 or higher
- Enable TEMP_UNDO_ENABLED initialization parameter
- Since the temporary undo records now stored in a temp tablespace, you need to create the temporary tablespace with sufficient space
- For session level, you can use: ALTER SESSION SET TEMP_UNDO_ENABLE=TRUE;
Query temporary undo information
The dictionary views listed below are used to view/query the information/statistics about the temporary undo data:
- V$TEMPUNDOSTAT
- DBA_HIST_UNDOSTAT
- V$UNDOSTAT
To disable the feature, you simply need to set the following:
SQL> ALTER SYSTEM|SESSION SET TEMP_UNDO_ENABLED=FALSE;
7. Backup specific user privilege
In 11g R2, SYSASM privilege was introduced to perform ASM specific operations. Similarly, backup and recovery tasks specific privilege SYSBACKUP has been introduced in 12c to execute backup and recovery commands in Recovery Manager (RMAN). Therefore, you can create a local user in the database and grant the SYSBACKUP privilege to perform any backup and recovery related tasks in RMAN without being granting the SYSDBA privilege.
$ ./rman target "username/password as SYSBACKUP"
8. How to execute SQL statement in RMAN
In 12c, you can now execute any SQL and PL/SQL commands in RMAN without the need of a SQL prefix: you can execute any SQL and PLS/SQL commands directly from RMAN. How you can execute SQL statements in RMAN:
RMAN> SELECT username,machine FROM v$session;
RMAN> ALTER TABLESPACE users ADD DATAFILE SIZE 121m;
9. Table or partition recovery in RMAN
Oracle database backups are mainly categorized into two types: logical and physical. Each backup type has its own pros and cons. In previous editions, it was not feasible to restore a table or partition using existing physical backups. In order to restore a particular object, you must have logical backup. With 12c R1, you can recover a particular table or partition to a point-in-time or SCN from RMAN backups in the event of a table drop or truncate.
When a table or partition recovery is initiated via RMAN, the following action is performed:
- Required backup sets are identified to recover the table/partition
- An auxiliary database will be configured to a point-in-time temporarily in the process of recovering the table/partition
- Required table/partitions will be then exported to a dumpfile using the data pumps
- Optionally, you can import the table/partitions in the source database
- Rename option while recovery
An example of a table point-in-time recovery via RMAN (ensure you already have a full database backup from earlier):
RMAN> connect target "username/password as SYSBACKUP";
RMAN> RECOVER TABLE username.tablename UNTIL TIME 'TIMESTAMP…'
AUXILIARY DESTINATION '/u01/tablerecovery'
DATAPUMP DESTINATION '/u01/dpump'
DUMP FILE 'tablename.dmp'
NOTABLEIMPORT -- this option avoids importing the table automatically.
REMAP TABLE 'username.tablename': 'username.new_table_name'; -- can rename table with this option.
Important notes:
- Ensure sufficient free space available under /u01 filesystem for auxiliary database and also to keep the data pump file
- A full database backup must be exists, or at least the SYSTEM related tablespaces
The following limitations/restrictions are applied on table/partition recovery in RMAN:
- SYS user table/partition can’t be recovered
- Tables/partitions stored under SYSAUX and SYSTEM tablespaces can’t be recovered
- Recovery of a table is not possible when REMAP option used to recovery a table that contains NOT NULL constraints
10. Restricting PGA size
Pre Oracle 12c R1, there was no option to limit and control the PGA size. Although, you set a certain size toPGA_AGGREGATE_TARGET initialization parameter, Oracle could increase/reduce the size of the PGA dynamically based on the workload and requirements. In 12c, you can set a hard limit on PGA by enabling the automatic PGA management, which requires PGA_AGGREGATE_LIMIT parameter settings. Therefore, you can now set the hard limit on PGA by setting the new parameter to avoid excessive PGA usage.
SQL> ALTER SYSTEM SET PGA_AGGREGATE_LIMIT=2G;
SQL> ALTER SYSTEM SET PGA_AGGREGATE_LIMIT=0; --disables the hard limit
Important notes:
When the current PGA limits exceeds, Oracle will automatically terminates/abort the session/process that holds the most untenable PGA memory.
Oracle Database 12c New Features – Part 2
During this Oracle Database 12c new features series, I shall be extensively exploring some of the miscellaneous, yet very useful, new additions and enhancements introduced in the areas of Database Administration, RMAN, Data Guard and Performance Tuning.
Part 2 covers:
- Table partition maintenance enhancements
- Database upgrade improvements
- Restore/Recover data file over the network
- Data Pump enhancements
- Real-time ADDM
- Concurrent statistics gathering
1. Table partition maintenance enhancements
In Part I, I explained how to move a table partition or sub-partition to a different tablespace either offline or online. In this section, you will learn other enhancements relating to table partitioning.
Adding multiple new partitions
Before Oracle 12c R1, it was only possible to add one new partition at a time to an existing partitioned table. To add more than one new partition, you had to execute an individual ALTER TABLE ADD PARTITION statement to every new partition. Oracle 12c provides the flexibility to add multiple new partitions using a single ALTER TABLE ADD PARTITION command. The following example explains how to add multiple new partitions to an existing partitioned table:
SQL> CREATE TABLE emp_part
(eno number(8), ename varchar2(40), sal number (6))
PARTITION BY RANGE (sal)
(PARTITION p1 VALUES LESS THAN (10000),
PARTITION p2 VALUES LESS THAN (20000),
PARTITION p3 VALUES LESS THAN (30000)
);
Now lets add a couple of new partitions:
SQL> ALTER TABLE emp_part ADD PARTITION
PARTITION p4 VALUES LESS THAN (35000),
PARTITION p5 VALUES LESS THAN (40000);
In the same way, you can add multiple new partitions to a list and system partitioned table, provided that theMAXVALUE partition doesn’t exist.
How to drop and truncate multiple partitions/sub-partitions
As part of data maintenance, you typically either use drop or truncate partition maintenance task on a partitioned table. Pre 12c R1, it was only possible to drop or truncate one partition at a time on an existing partitioned table. With Oracle 12c, multiple partitions or sub-partitions can be dropped or merged using a single ALTER TABLE table_name {DROP|TRUNCATE} PARTITIONS command.
The following example explains how to drop or truncate multiple partitions on an existing partitioned table:
SQL> ALTER TABLE emp_part DROP PARTITIONS p4,p5;
SQL> ALTER TABLE emp_part TRUNCATE PARTITONS p4,p5;
To keep indexes up-to-date, use the UPDATE INDEXES or UPDATE GLOBAL INDEXES clause, shown below:
SQL> ALTER TABLE emp_part DROP PARTITIONS p4,p5 UPDATE GLOBAL INDEXES;
SQL> ALTER TABLE emp_part TRUNCATE PARTITIONS p4,p5 UPDATE GLOBAL INDEXES;
If you truncate or drop a partition without the UPDATE GLOBAL INDEXES clause, you can query the columnORPHANED_ENTRIES in the USER_INDEXES or USER_IND_PARTITIONS dictionary views to find out whether the index contains any stale entries.
Splitting a single partition into multiple new partitions
The new enhanced SPLIT PARTITION clause in 12c will let you split a particular partition or sub-partition into multiple new partitions using a single command. The following example explains how to split a partition into multiple new partitions:
SQL> CREATE TABLE emp_part
(eno number(8), ename varchar2(40), sal number (6))
PARTITION BY RANGE (sal)
(PARTITION p1 VALUES LESS THAN (10000),
PARTITION p2 VALUES LESS THAN (20000),
PARTITION p_max (MAXVALUE)
);
SQL> ALTER TABLE emp_part SPLIT PARTITION p_max INTO
(PARTITION p3 VALUES LESS THAN (25000),
PARTITION p4 VALUES LESS THAN (30000), PARTITION p_max);
Merge multiple partitions into one partition
You can merge multiple partitions to a single partition using a single ALTER TBALE MERGE PARTITIONS statement:
SQL> CREATE TABLE emp_part
(eno number(8), ename varchar2(40), sal number (6))
PARTITION BY RANGE (sal)
(PARTITION p1 VALUES LESS THAN (10000),
PARTITION p2 VALUES LESS THAN (20000),
PARTITION p3 VALUES LESS THAN (30000),
PARTITION p4 VALUES LESS THAN (40000),
PARTITION p5 VALUES LESS THAN (50000),
PARTITION p_max (MAXVALUE)
);
SQL> ALTER TABLE emp_part MERGE PARTITIONS p3,p4,p5 INTO PARTITION p_merge;
If the range falls in the sequence, you can use the following example:
SQL> ALTER TABLE emp_part MERGE PARTITIONS p3 TO p5 INTO PARTITION p_merge;
2. Database upgrade improvements
Whenever a new Oracle version is announced, the immediate challenge that every DBA confronts is the upgrade process. In this section, I will explain the two new improvements introduced for upgrading to 12c.
Pre-upgrade script
A new and much improved pre-upgrade information script, preupgrd.sql, replaces the legacy utlu[121]s.sql script in 12c R1. Apart from the preupgrade checks verification, the script is capable of addressing the various issues – in the form of fixup scripts – that are raised during the pre-post upgrade process.
The fixup scripts that are generated can be executed to resolve the problems at different levels, for example, pre-upgrade and post upgrade. When upgrading the database manually, the script must be executed manually before initiating the actual upgrade procedure. However, when the Database Upgrade Assistant (DBUA) tool is used to perform a database upgrade, it automatically executes the pre-upgrade scripts as part of the upgrade procedure and will prompt you to execute the fixup scripts in case of any errors that are reported.
The following example demonstrates how to execute the scripts:
SQL> @$ORACLE_12GHOME/rdbms/admin/preupgrd.sql
The above script generates a log file and a [pre/post]upgrade_fixup.sql script. All these files are located under the $ORACLE_BASE/cfgtoollogs directory. Before you continue with the real upgrade procedure, you should run through the recommendations mentioned in the log file and execute the scripts to fix any issues.
Note: Ensure you copy the preupgrd.sql and utluppkg.sql scripts from the 12c Oracle home/rdbms/admin directory to the current Oracle database/rdbms/admin location.
Parallel-upgrade utility
The database upgrade duration is directly proportional to the number of components that are configured on the database, rather than the database size. In previous releases, there was no direct option or workaround available to run the upgrade process in parallel to quickly complete the overall upgrade procedure.
The catctl.pl (parallel-upgrade utility) that replaces the legacy catupgrd.sql script in 12c R1 comes with an option to run the upgrade procedure in parallel mode to improve the overall duration required to complete the procedure.
The following procedure explains how to initiate the parallel (with 3 processes) upgrade utility; you need to run this after you STARTUP the database in UPGRADE mode:
cd $ORACLE_12_HOME/perl/bin
$ ./perl catctl.pl –n 3 -catupgrd.sql
The above two steps need to be run explicitly when a database is upgraded manually. However, the DBUA inherits the both new changes.
3. Restore/Recover data files over the network
Yet another great enhancement in 12c R1. You can now restore or recover a data file, control file, spfile, tablespace or entire database between primary and standby databases using a SERVICE name. This is particularly useful to synchronize the primary and standby databases.
When there is a pretty long gap found between the primary and standby database, you no longer require the complex roll-forward procedure to fill the gap between the primary and standby. RMAN is able to perform standby recovery getting the incremental backups through the network and applying them to the physical standby database. Having said that, you can directly copy the required data files from the standby location to the primary site using the SERVICE name e.g. in the case of a data file, tablespace lost on the primary database, or without actually restoring the data files from a backup set.
The following procedure demonstrates how to perform a roll forward using the new features to synchronize the standby database with its primary database:
On the physical standby database:
./rman target "username/password@standby_db_tns as SYSBACKUP"
RMAN> RECOVER DATABASE FROM SERVICE primary_db_tns USING COMPRESSED BACKUPSET;
The above example uses the primary_db_tns connect string defined on the standby database, connects to the primary database, performs an incremental backup, transfers these incremental backups over standby destination, and then applies these files to the standby database to synchronize the standby. However, you need to ensure you have configured primary_db_tns to point to the primary database on the standby database side.
In the following example, I will demonstrate a scenario to restore a lost data file on the primary database by fetching the data file from the standby database:
On the primary database:
./rman target "username/password@primary_db_tns as SYSBACKUP"
RMAN> RESTORE DATAFILE ‘+DG_DISKGROUP/DBANME/DATAFILE/filename’ FROM SERVICE standby_db_tns;
4. Data Pump enhancements
This part of the section will focus on the important enhancements introduced in data pumps. There are quite a few useful additions, such as converting view into a table while exporting and turning off logging while import.
Turn off redo log generation
The new TRANSFORM option introduced in data pumps import provides the flexibility to turn off the redo generation for the objects during the course of import. When DISABLE_ARCHIVE_LOGGING values is specified with theTRANSFORM option, redo generation for the objects in the context will be turned off during the entire import duration. This feature provides a great relief when importing large tables, and reduces the excessive redo generation, which results in quicker imports. This attribute applies to tables and indexes.
This example demonstrates this feature:
$ ./impdp directory=dpump dumpfile=abcd.dmp logfile=abcd.log TRANSFORM=DISABLE_ARCHIVE_LOGGING:Y
Transport view as table
This is another improvement in the data pumps. With the new VIEWS_AS_TABLES option, you can unload the view data into a table. The following example describes how to unload views data into a table during export:
$ ./expdp directory=dpump dumpfile=abcd.dmp logfile=abcd.log views_as_tables=my_view:my_table
5. Real-time ADDM analysis
Analyzing past and current database health statuses through a set of automatic diagnostic tools such as AWR, ASH and ADDM is part of every DBAs life. Though each individual tool can be used at various levels to measure the database’s overall heath and performance, no tool can be used when the database is unresponsive or totally hung.
When you encounter an unresponsive database or hung state, and if you have configured Oracle Enterprise Manager 12c Cloud Control, you can diagnose serious performance issues. This would give you a good picture about what’s currently going on in the database, and might also provide a remedy to resolve the issue.
The following step-by-step procedure demonstrates how to analyze the situation on the Oracle EM 12c Cloud Control :
- Select the Emergency Monitoring option from the Performance menu on the Access the Database Home page.This will show the top blocking sessions in the Hang Analysis table.
- Select the Real-Time ADDM option from the Performance to perform Real-time ADDM analysis.
- After collecting the performance data, click on the Findings tab to get the interactive summary of all the findings.
6. Gathering statistics concurrently on multiple tables
In previous Oracle database editions, whenever you execute a DBMS_STATS procedure to gather table, index, schema or database level statistics, Oracle used to collect stats one table at a time. If the table is big enough, then increasing the parallelism was recommended. With 12c R1, you can now collect stats on multiple tables, partitions and sub partitions concurrently. Before you start using it, you must set the following at the database level to enable the feature
SQL> ALTER SYSTEM SET RESOURCE_MANAGER_PLAN='DEFAULT_MAIN';
SQL> ALTER SYSTEM SET JOB_QUEUE_PROCESSES=4;
SQL> EXEC DBMS_STATS.SET_GLOBAL_PREFS('CONCURRENT', 'ALL');
SQL> EXEC DBMS_STATS.GATHER_SCHEMA_STATS('SCOTT');
Oracle Database 12c New Features – Part 3
During this Oracle Database 12c new features article series; I shall be extensively exploring some of the miscellaneous, yet very useful, new additions and enhancements introduced in the areas of Clusterware, ASM and RAC database.
Part 3 covers:
- Additions/Enhancements in ASM
- Additions/Enhancements in Grid Infrastructure
- Additions/Enhancements in Real Application Cluster (database)
1. Additions/Enhancements in Automatic Storage Management (ASM)
Flex ASM
In a typical Grid Infrastructure installation, each node will have its own ASM instance running and act the as the storage container for the databases running on the node. There is a single point-of-failure threat with this setup. For instance, if the ASM instance on the node suffers or fails all the databases and instances running on the node will be impacted. To avoid ASM instance single-point-failure, Oracle 12c provides a Flex ASM feature. The Flex ASM is a different concept and architecture all together. Only a fewer number of ASM Instances need to run on a group of servers in the cluster. When an ASM instance fails on a node, Oracle Clusterware automatically starts surviving (replacement) ASM instance on a different node to maintain availability. In addition, this setup also provides ASM instance load balancing capabilities for the instances running on the node. Another advantage of Flex ASM is that it can be configured on a separate node.
When you choose Flex Cluster option as part of the cluster installation, Flex ASM configuration will be automatically selected as it is required by the Flex Cluster. You can also have traditional cluster over Flex ASM. When you decide to use Flex ASM, you must ensure the required networks are available. You can choose the Flex ASM storage option as part of Cluster installation, or use ASMCA to enable Flex ASM in a standard cluster environment.
The following command shows the current ASM mode:
$ ./asmcmd showclustermode
$ ./srvctl config asm
Or connect to the ASM instances and query the INSTANCE_TYPE parameter. If the output value is ASMPROX, then, the Flex ASM is configured.
Increased ASM storage limits
The ASM storage hard limits on maximum ASM disk groups and disk size has been drastically increased. In 12cR1, ASM support 511 ASM disk groups against 63 ASM disk groups in 11gR2. Also, an ASM disk can be now 32PB size against 20PB in 11gR2.
Tuning ASM rebalance operations
The new EXPLAIN WORK FOR statement in 12c measures the amount of work required for a given ASM rebalance operation and inputs the result in V$ASM_ESTIMATE dynamic view. Using the dynamic view, you can adjust thePOWER LIMIT clause to improve the rebalancing operation work. For example, if you want to measure the amount of work required for adding a new ASM disk, before actually running the manual rebalance operation, you can use the following:
SQL> EXPLAIN WORK FOR ALTER DISKGROUP DG_DATA ADD DISK data_005;
SQL> SELECT est_work FROM V$ASM_ESTIMATE;
SQL> EXPLAIN WORK SET STATEMENT_ID='ADD_DISK' FOR ALTER DISKGROUP DG_DATA AD DISK data_005;
SQL> SELECT est_work FROM V$ASM_ESTIMATE WHERE STATEMENT_ID = 'ADD_DISK’;
You can adjust the POWER limit based on the output you get from the dynamic view to improve the rebalancing operations.
ASM Disk Scrubbing
The new ASM Disk Scrubbing operation on a ASM diskgroup with normal or high redundancy level, verifies the logical data corruption on all ASM disks of that ASM diskgroup, and repairs the logical corruption automatically, if detected, using the ASM mirror disks. The disk scrubbing can be performed at disk group, specified disk or on a file and the impact is very minimal. The following examples demonstrate the disk scrubbing scenario:
SQL> ALTER DISKGROUP dg_data SCRUB POWER LOW:HIGH:AUTO:MAX;
SQL> ALTER DISKGROUP dg_data SCRUB FILE '+DG_DATA/MYDB/DATAFILE/filename.xxxx.xxxx'
REPAIR POWER AUTO;
Active Session History (ASH) for ASM
The V$ACTIVE_SESSION_HISOTRY dynamic view now provides the active session sampling on ASM instance too. However, the use of diagnostic pack is subject to the license.
2. Additions/Enhancements in Grid Infrastructure
Flex Clusters
Oracle 12c support two types of cluster configuration at the time of Clusterware installation: Traditional Standard Cluster and Flex cluster. In a traditional standard cluster, all nodes in a cluster are tightly integrated to each other and interact through a private network and can access the storage directly. On the other hand, the Flex Cluster introduced two types of nodes arranged in Hub and Leaf nodes architecture. The nodes arranged in Hub nodes category are similar to the traditional standard cluster, i.e. they are interconnected to each other through a private network and have the directly storage read/write access. The Leaf nodes are different from the Hub nodes. They don’t need to have direct access to the underlying storage; rather they access the storage/data through Hub nodes.
You can configure Hub nodes up to 64, and Leaf nodes can be many. In an Oracle Flex Cluster, you can have Hub nodes without having Leaf nodes configured, but no Leaf nodes exist without Hub nodes. You can configure multiple Leaf nodes to a single Hub node. In Oracle Flex Cluster, only Hub nodes will have direct access to the OCR/Voting disks. When you plan large scale Cluster environments, this would be a great feature to use. This sort of setup greatly reduces interconnect traffic, provides room to scale up the cluster to the traditional standard cluster.
There are two ways to deploy the Flex Cluster:
- While configuring a brand new cluster
- Upgrade a standard cluster mode to Flex Cluster
If you are configuring a brand new cluster, you need to choose the type of cluster configuration during step 3, select Configure a Flex Cluster option and you will have to categorize the Hub and Leaf nodes on Step 6. Against each node, select the Role, Hub or Leaf, and optionally Virtual Hostname too.
The following steps are required to convert a standard cluster mode to Flex Cluster mode:
1. Get the current status of the cluster using the following command:
$ ./crsctl get cluster mode status
2. Run the following command as the root user:
$ ./crsctl set cluster mode flex
$ ./crsctl stop crs
$ ./crsctl start crs –wait
3. Change the node role as per your design
$ ./crsctl get node role config
$ ./crsctl set node role hub|leaf
$ ./crsctl stop crs
$ ./crsctl start crs -wait
Note the following:
- You can’t revert back from Flex to Standard cluster mode
- Cluster node mode change requires cluster stack stop/start
- Ensure GNS is configured with a fixed VIP
OCR backup in ASM disk group
With 12c, OCR can be now be backed-up in ASM disk group. This simplifies the access to the OCR backup files across all nodes. In case of OCR restore, you don’t need to worry about which node the OCR latest backup is on. One can simply identify the latest backup stored in the ASM from any node and can perform the restore easily.
The following example demonstrates how to set the ASM disk group as OCR backup location:
$ ./ocrconfig -backuploc +DG_OCR
IPv6 support
With Oracle 12c, Oracle now supports IPv4 and IPv6 network protocol configuration on the same network. You can now configure public network (Public/VIP) either on IPv4, IPv6 or combination protocol configuration. However, ensure you use the same set of IP protocol configuration across all nodes in a cluster.
3. Additions/Enhancements in RAC (database)
What-If command evaluation
Using the new What-if command evaluation (-eval) option with srvctl, one can now determine the impact of running the command. This new addition to the srvctl command, will let you simulate the command without it actually being executed or making any changes to the current system. This is particularly useful in a situation when you want to make a change to an existing system and you’re not sure of the outcome. Therefore, the command will provide the effect of making the change. The –eval option also can be used with crsctl command.
For example, if you want to know what will happen if you stop a particular database, you can use the following example:
$ ./srvctl stop database –d MYDB –eval
$ ./crsctl eval modify resource <resource_name> -attr “value”
Miscellaneous srvctl improvements
There are a few new additions to the srvctl command. The following demonstrates the new addition to stop/start database/instance resources on the cluster:
srvctl start database|instance –startoption NOMOUNT|MOUNT|OPEN
srvctl stop database|instance –stopoption NOMOUNT|MOUNT|OPEN
The next article will focus on top most developers’ features on 12c.
Oracle Database 12c New Features – Part 4
Part 4 covers:
- How to truncate a master table while child tables contain data
- Limiting ROWS for Top-N query results
- Miscellaneous SQL*Plus enhancements
- Session level sequences
- WITH clause improvements
- Extended data types
Truncate table CASCADE
In the previous releases, there wasn’t a direct option provided to truncate a master table while it is referred to by the child tables and child records exist. The TRUNCATE TABLE with CASCADE option in 12c truncates the records in the master table and automatically initiates recursive truncate on child tables too, subject to foreign key reference as DELETE ON CASCADE. There is no CAP on the number of recursive levels as it will apply on all child, grand child and great grandchild etc.
This enhancement gets rid of the prerequisite to truncate all child records before truncating a master table. The newCASCADE clause can also be applied on table partitions and sub-partitions etc.
SQL> TRUNCATE TABLE <table_name> CASCADE;
SQL> TRUNCATE TABLE <table_name> PARTITION <partition_name> CASCADE;
An ORA-14705 error will be thrown if no ON DELETE CASCADE option is defined with the foreign keys of the child tables.
ROW limiting for Top-N result queries
There are various indirect approaches/methods exist to fetch Top-N query results for top/bottom rows in the previous releases. In 12c, retrieving Top-N query results for top/bottom rows simplified and become straight forward with the new FETCH FIRST|NEXT|PERCENT clauses.
In order to retrieve top 10 salaries from EMP table, use the following new SQL statement:
SQL> SELECT eno,ename,sal FROM emp ORDER BY SAL DESC
FETCH FIRST 10 ROWS ONLY;
The following example fetches all similar records of Nth row. For example, if the 10th row has salary of 5000 value, and there are other employees whose salary matches with the Nth value, the will also be fetched upon mentioningWITH TIES clause.
SQL> SELECT eno,ename,sal FROM emp ORDER BY SAL DESC
FETCH FIRST 10 ROWS ONLY WITH TIES;
The following example limits the fetch to 10 per cent from the top salaries in the EMP table:
SQL> SELECT eno,ename,sal FROM emp ORDER BY SAL DESC
FETCH FIRST 10 PERCENT ROWS ONLY;
The following example offsets the first 5 rows and will display the next 5 rows from the table:
SQL> SELECT eno,ename,sal FROM emp ORDER BY SAL DESC
OFFSET 5 ROWS FETCH NEXT 5 ROWS ONLY;
All these limits can be very well used within the PL/SQL block too.
BEGIN
SELECT sal BULK COLLECT INTO sal_v FROM EMP
FETCH FIRST 100 ROWS ONLY;
END;
Miscellaneous SQL*Plus enhancements
Implicit Results on SQL*Plus: SQL*Plus in 12c returns results from an implicit cursor of a PL/SQL block without actually binding it to a RefCursor. The new dbms_sql.return_result procedure will return and formats the results ofSELECT statement query specified within PL/SQL block. The following code descries the usage:
SQL> CREATE PROCEDURE mp1
res1 sys_refcursor;
BEGIN
open res1 for SELECT eno,ename,sal FROM emp;
dbms_sql.return_result(res1);
END;
SQL> execute mp1;
When the procedure is executed, it return the formatted rows on the SQL*Plus.
Display invisible columns: In Part 1 of this series, I have explained and demonstrated about invisible columns new feature. When the columns are defined as invisible, they won’t be displayed when you describe the table structure. However, you can display the information about the invisible columns by setting the following on the SQL*Plus prompt:
SQL> SET COLINVISIBLE ON|OFF
The above setting is only valid for DESCRIBE command. It has not effect on the SELECT statement results on the invisible columns.
Session level sequences
A new SESSION level database sequence can be created now in 12c to support the session level sequence values. These types of sequences are most useful and suitable on global temporary tables that have session level existence.
Session level sequences produce a unique range of values that are limited within the session, not across the sessions. Once the session ends, the state of the session sequences also goes away. The following example explains creating a session level sequence:
SQL> CREATE SEQUENCE my_seq START WITH 1 INCREMENT BY 1 SESSION;
SQL> ALTER SEQUENCE my_seq GLOBAL|SESSION;
The CACHE, NOCACHE, ORDER or NOORDER clauses are ignored for SESSION level sequences.
WITH clause improvements
In 12c, you can have faster running PL/SQL function/procedure in SQL, that are defined and declared within theWITH clause of SQL statements. The following examples demonstrate how to define and declare a procedure or function within the WITH clause:
WITH
PROCEDURE|FUNCTION test1 (…)
BEGIN
<logic>
END;
SELECT <referece_your_function|procedure_here> FROM table_name;
/
Although you can’t use the WITH clause directly in the PL/SQL unit, it can be referred through a dynamic SQL within that PL/SQL unit.
Extended data types
In 12c, the data type VARCHAR2, NAVARCHAR2, and RAW size will support up to 32,767 bytes in contrast to 4,000 and 2,000 in the earlier releases. The extended character size will reduce the use of going for LOB data types, whenever possible. In order to enable the extended character size, you will have to set theMAX_STRING_SIZE initialization database parameter to EXTENDED.
The following procedure need to run to use the extended data types:
- Shutdown the database
- Restart the database in UPGRADE mode
- Modify the parameter: ALTER SYSTEM SET MAX_STRING_SIZE=EXTENDED;
- Execute utl32k.sql as sysdba : SQL> @?/rdbms/admin/utl32k.sql
- Shutdown the database
- Restart the database in READ WRITE mode
In contrast to LOB data types, the extended data types columns in ASSM tablespace management are stored as SecureFiles LOBs, and in non-ASSM tablespace management they stored as BasciFiles LOBs.
Note: Once modified, you can’t change the settings back to STANDARD.