Oracle 12c is around the corner and due that I have received many questions from fellow DBAs about what was new about Backup and Recovery at 11g I have decided to write a small white paper about it.
Hope you will enjoy reading it as much I enjoyed writing it.
Cheers,
Francisco Munoz Alvarez
Oracle introduced hot backups early on version 6, allowing us to start to have the ability to make hot backups of our database tablespaces without the need to shutdown the database as earlier versions forced us to do, also on version 10g Oracle gave us the possibility to start doing hot backups of the entire database making our life a little more easy. When executing a user managed backup online, Oracle stops recording checkpoints to all associated datafiles.
Let’s take a deep look on what’s happening internally in the database when we use the BEGIN BACKUP option:
Hot backups (inconsistent backups due to fractured data blocks) can be made to the whole database or at tablespace level and it will require a recovery process after restore the backup. Due to this, is very important to ensure that your archivelog files are being backed up all the time.
Note: When doing a hot backup of your database, do not include the temporary tablespace. This tablespace is not recorded in the control files, and should be recreated when restoring and recovering a database.
You can make a full backup of your database easily placing first your database to be in backup mode using the “ALTER DATABASE BEGIN BACKUP;” statement at SQL*PLUS. This feature was introduced on version 10g of the Oracle database. This is the most common type of user managed backup that is used for many DBAs around the world.
Now let’s go through all steps required to perform this backup with the database open:
1. Place your entire database in backup mode;
SQL> ALTER DATABASE BEGIN BACKUP;
2. Backup all datafiles and the PFILE or SPFILE. This backup is a simple physical copy of files at OS level.
$ cp $ORACLE_BASE/oradata/cdb1/*.dbf /stage/backup
$ cp $ORACLE_HOME/dbs/spfilecdb1.ora /stage/backup
3. Take the database out of the backup mode;
SQL> ALTER DATABASE END BACKUP;
4. Archive all unarchived redo logs so any redo required to recover the database are archived;
SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;
5. Make a backup of all archived redolog files (OS level).
$ cp $ORACLE_BASE/fast_recovery_area/*.arc /stage/backup
6. Create a copy of your control file using the statement “ALTER DATABASE”.
SQL> ALTER DATABASE BACKUP CONTROLFILE TO’/stage/backup/control.ctf’;
Note: The steps 4 and 5 above are required due that all redo generated during the backup must be available to be applied in case of a recovery, also in this example I’m suggesting the backup of the controlfile using the statement “ALTER DATABASE BACKUP CONTROLFILE TO” to avoid any risk of overwrite the original controlfile during the recovery process.
Now it’s time to learn how to do a hot backup of one or more tablespaces, in other worlds, do a backup of your tablespaces with your database running. The beauty about this is that you can make an inconsistent backup of some pieces of your database at different times without affect the availability of your database. On this option you will need to switch each tablespace you want to make an online backup into backup mode before manually copying all datafiles involved to a secondary storage location on your machine, network or tape. Everytime a tablespace is placed in backup mode, the database copies the whole changed data blocks into the redo stream to make all data consistent until that point.
Tip: Avoid parallel online tablespace backups (placing more than one tablespace in backup mode at the same time) due that it will generate performance problems to your database; also always make the backup of a tablespace on low load periords, never when it is being heavly used by the users.
To do an online backup of one or more tablespace you will just need to follow these simple steps:
1. Identify the tablespace’s datafiles by quering the DBA_DATA_FILES view;
SQL> SELECT tablespace_name, file_name
2 FROM sys.dba_data_files
3 WHERE tablespace_name = ‘EXAMPLE’;
2. Place the tablespace in backup mode;
SQL> ALTER TABLESPACE example BEGIN BACKUP;
3. Backup all datafiles associated with the tablespace that you placed in backup mode. This backup is a physical copy of the datafiles at OS level;
$ cp $ORACLE_BASE/oradata/cdb1/pdb1/example_01.dbf /stage/backup
4. Place the tablespace back in normal mode;
SQL> ALTER TABLESPACE example END BACKUP;
5. If you need to backup another tablespace, go back to the step 1, if not proceed to step 5;
6. Archive all unarchived redo logs so any redo required to recover the database are archived;
SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;
7. Make a backup of all archived redolog files (OS level).
$ cp $ORACLE_BASE/fast_recovery_area/*.arc /stage/backup
Note: If you forget to take your tablespace(s) off backup mode, it will generate performance problems and also will raise an error (ORA-01149) if trying to SHUTDOWN the database.
It’s always useful to know how to check whether a datafile is part of a current online tablespace backup or even to determine whether you forgot any tablespaces in backup mode, this can be very easily accomplished by querying the V$BACKUP view, where the STATUS column value determine if a datafile is currently in backup mode or not (ACTIVE means in backup mode and NOT ACTIVE means not in backup mode).
Note: The V$BACKUP view is not useful if the control file currently in use by the database is a restored or newly created one, because a restored or re-created control file does not contain the information necessary to populate the V$BACKUP view accurately.
Here is a useful query that can be used over the V$BACKUP view to check the status of all datafiles at root or PDB level:
SQL> SET PAGESIZE 300
SQL> SET LINESIZE 300
SQL> COLUMN tb_name FORMAT A10
SQL> COLUMN df_name FORMAT A50
SQL> COLUMN status FORMAT A10
SQL> SELECT b.name AS “TB_NAME”, a.file# as “DF#”, a.name AS “DF_NAME”, c.status
2 FROM V$DATAFILE a, V$TABLESPACE b, V$BACKUP c
3 WHERE a.TS#=b.TS#
4 AND c.FILE#=a.FILE#
5 AND c.STATUS=’ACTIVE’;
TB_NAME DF# DF_NAME STATUS
————– ———- ———————————————– ——-
EXAMPLE 11 /u01/app/oracle/oradata/cdb1/pdb1/example01.dbf ACTIVE
SQL>
Have a valid backup of the control file of a database is crucial for a successful recovery of a database, due to this, we will take a closer look on the available methods to make a user managed backup of a control file.
Tip: Always do a backup of the control file after making a structural change to a database operating in ARCHIVELOG mode, such as creating, deleting or modifying a tablespace.
You can generate a binary copy of the control file via a simple SQL statement. A binary copy of the control file contains additional information such as the archived redo log history, offline range for read-only and offline tablespaces, temp files entries (since 10.2) and RMAN backup sets and copies data.
Here is one example of the SQL statement used to create a binary copy of the control file:
SQL> ALTER DATABASE BACKUP CONTROLFILE TO ‘[filename_including_location] ‘;
You can also specify REUSE at the end of the above statement to allow the new control file to overwrite one that currently exists with the same name.
You can generate a text file that contains a CREATE CONTROLFILE statement based on the current control file in use by the database via a simple SQL statement that can be executed on a database in MOUNT or OPEN state. This file is generated as a trace file and can be easily edited to create a proper script to be used to create a new control file.
Some example of the SQL statement used to create a trace file based on the current control file in use are:
SQL> ALTER DATABASE BACKUP CONTROLFILE TO TRACE;
SQL> ALTER DATABASE BACKUP CONTROLFILE TO TRACE RESETLOGS;
SQL> ALTER DATABASE BACKUP CONTROLFILE TO TRACE NORESETLOGS;
SQL> ALTER DATABASE BACKUP CONTROLFILE TO TRACE AS ‘/stage/backup/ctlf.sql’;
SQL> ALTER DATABASE BACKUP CONTROLFILE TO TRACE AS ‘/stage/backup/ctlf.sql’ REUSE;
SQL> ALTER DATABASE BACKUP CONTROLFILE TO TRACE TRACE AS ‘/stage/backup/ctlf.sql’ RESETLOGS;
SQL> ALTER DATABASE BACKUP CONTROLFILE TO TRACE TRACE AS ‘/stage/backup/ctlf.sql’ NORESETLOGS;
In the next post you will find some more interesting information regarding this topic 
Cheers,
Francisco
In the past few weeks I have received many questions regarding user managed backups, and due to this, I decided to write a little regarding this topic.
User managed backups are basically all backups you can make without the use of RMAN and no automatic metadata record of the backup is generated anywhere in the database, therefore you must keep records of what you backed up and where all the time, also this method allows a DBA to make consistent backups of a whole database (cold backup), partial backups of a database making one or more tablespaces offline (inconsistent backup) or even online backups (inconsistent backup) can be performed as well if your database is running on ARCHIVELOG mode. The last option, allows you to perform user managed backups without affecting the availability of your database to the business, very useful when your database needs to be available 24x7x365. RMAN was introduced in version 8.0 and it’s not compatible with prior releases of the database, and it will make most of your work when executing backups or any recovery process, consequently making your life a lot more easy and your database safer.
Note: A database is only in a consistent state after being SHUTDOWN in a consistent way (using SHUTDOWN [NORMAL/IMMEDIATE/TRANSACTIONAL).
Understanding the basics involving a manual backup and recovery will help you to easily understand what is going on in the background of your database when using RMAN and it will also help you to compare and easily understand all benefits of using RMAN against any other backup method when working with Oracle.
Before you start a user managed backup, you will need to know first:
Tip: When doing user managed backups, never backup online redo log files, because these files contain the end of backup marker and would cause corruption if used in a recovery process.
You can easily collect all information necessary with the following commands:
· To select the datafiles information you can use the following SQL*Plus command:
SQL> SELECT name FROM v$datafile;
Or if you prefer to see the tablespaces and all associated datafiles, you can use the following SQL*Plus command:
SQL> SELECT a.name tablespace, b.name datafile
2 FROM v$tablespace a, v$datafile b
3 WHERE a.ts# = b.ts#
4 ORDER BY a.name;
· To check where your archive logs are being generated, you should use the following command:
SQL> SELECT destination
2 FROM v$archive_dest;
· Use the following command to see the name and location of your current control files:
SQL> SELECT name
2 FROM v$controlfile;
Tip: Always remember that the control files plays a crucial role in the database restore and recovery process, and you only need to make a backup of only one copy of a multiplexed control file.
Also, do not forget to include a copy of your PFILE or SPFILE (these files are generally found in $ORACLE_HOME/dbs) when making a user managed backup.
Now that you have all the information you need to perform a user managed backup, let’s take a close look in some of the most common user managed backup and recovery options available.
As I mentioned before, cold backups are the only way possible to a DBA to perform a consistent backup of a database independent of the mode your database is running (ARCHIVELOG or NOARCHIVELOG).
Note: If your database is running on NOARCHIVELOG mode, all our backlups should be made using this method to ensure that your database is always in a consistent mode. If your database is on ARCHIVELOG mode, than you will be able to perform an additional recovery process to a more current point in time applying all ARCHIVELOG files generated after your consistent backup was made.
You can easily perform a manual backup of your database with the database down just following these simple steps:
$ cp $ORACLE_BASE/oradata/cdb1/*.dbf /stage/backup
$ cp $ORACLE_BASE/oradata/cdb1/control01.ctl /stage/backup
$ cp $ORACLE_HOME/dbs/spfilecdb1.ora /stage/backup
3. Restart the database.
4. Archive all unarchived redo logs so any redo required to recover the tablespace is archived and executes a backup of all archivelog files.
SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;
$ cp $ORACLE_BASE/fast_recovery_area/*.arc /stage/backup
Tip: Always backup your archive log files generated between cold backups, this will ensure that you will not lose data when a recovery of your database is required.
If what you need to do is to perform an offline backup of one or more tablespace, you should first notice that you cannot take offline the SYSTEM tablespace or a tablespace with any active UNDO segments, also when using this method always take in consideration first if the tablespace is completely self contained before perform this type of backup, in other words, you should first check if any logical or physical dependencies between objects exists, as per example, if any index related to any table in the tablespace that will become offline is stored in a different tablespace, in such case both tablespaces (DATA and INDEX) should be taken offline and backed up together.
Tip: Never perform Offline Backups of your database if it’s running on NOARCHIVELOG mode.
You can easily check if a tablespace is self contained using the very useful DBMS_TTS.TRANSPORT_SET_CHECK procedure (that is part of the DBMS_TTS package), and the view TRANSPORT_SET_VIOLATIONS as per the example bellow:
SQL> EXECUTE DBMS_TTS.TRANSPORT_SET_CHECK(‘example’, TRUE);
PL/SQL procedure successfully completed.
SQL> SELECT * FROM transport_set_violations;
no rows selected
SQL>
The example above checked if the tablespace EXAMPLE is self contained, you can also use the same statement to check more than one tablespace at the same time just adding all tablesapces separated by “comma”.
SQL> EXECUTE DBMS_TTS.TRANSPORT_SET_CHECK(‘example,example2′, TRUE);
If the query to the view TRANSPORT_SET_VIOLATIONS returns no rows, than your tablespace is self contained.
Note: If not connected as SYS to the database, you must have been granted the EXECUTE_CATALOG_ROLE role to be able to execute this procedure.
Now that you know all guidelines when backing up offline tablespaces, let’s see how it can be done:
SQL> SELECT tablespace_name, file_name
2 FROM sys.dba_data_files
3 WHERE tablespace_name = ‘EXAMPLE’;
2. Take the tablespace offline using the NORMAL priority if possible (This guarantees that no recovery will be necessary when bringing the tablespace online later);
SQL> ALTER TABLESPACE example OFFLINE NORMAL;
3. Backup all datafiles related to the now offline tablespace via OS;
$ cp $ORACLE_BASE/oradata/cdb1/pdb1/example_01.dbf /stage/backup
4. Bring the tableaspace online;
SQL> ALTER TABLESPACE example ONLINE;
5. Archive all unarchived redo logs so any redo required to recover the tablespace is archived and executes a backup of all archivelog files.
SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;
$ cp $ORACLE_BASE/fast_recovery_area/*.arc /stage/backup
Note: Taking a tablespace offline using the TEMPORARY or IMMEDIATE priority, will always require a tablespace recovery at the moment to bring the tablespace online.
In the next part, I will talk about Hot Backups (inconsistent) and much more.
Cheers,
Francisco Munoz Alvarez
Did you know that Oracle has determine that some software bugs (affecting databases between version 10.1.0.5 and 11.2.0.3) could cause the database to attempt to exceed the current maximum SCN value (The limit is currently 281 trillion) causing a transaction to be cancelled by the database, and the application would see an error message. But in the next attempt the limit is increased and typically allows the application to continue with a slight hiccough when processing, however in some very rare cases, the database does need to be shutdown to preserve its integrity.
The difference between the current SCN the database is using and the “not to exceed” upper limit for the number of SCN’s a database can use is known as the SCN headroom.
All bugs associated with SCN headroom have been fixed in the January, 2012 CPU (and associated PSU), and you also are able to download a specific patch (for your database version) to solve this issue from My Oracle Support (patch 13498243) and this patch contains a script (scnhealthcheck.sql) that allows you to check the available SCN headroom of your database. For more information please refer to My Oracle Support the documents 1376995.1 and 1393363.1 .
This is an example of the output when executing the scnhealthcheck.sql in a database:
SQL> @csnhealthcheck
————————————————————–
ScnHealthCheck
————————————————————–
Current Date: 2012/10/29 11:36:39
Current SCN: 10267706
Version: 11.2.0.3.0
————————————————————–
Result: A – SCN Headroom is good
Apply the latest recommended patches
based on your maintenance schedule
For further information review MOS document id 1393363.1
————————————————————–
SQL>
Cheers,
Francisco Munoz Alvarez
It is not possible to place an IOT (Index Organized Table) table in NOLOGGING mode, but we can place the index associated with this table in NOLOGGING mode when doing a CTAS (CREATE TABLE AS SELECT) operation. This will help us to reduce redo generation in the creation process of the IOT; Any intent to use INSERT /*+APPEND*/ latter will not give us any redo reduction advantage.
Now, let’s see if what I’m saying is true. We will start testing the amount of redo generated when inserting bulk data in a normal table in NOLOGGING mode and then compare the same operation against an IOT table in NOLOGGING. But before we start, we will first create our tables to this exercise.
SQL> CREATE TABLE iot_test
(object_name,object_type,owner,
CONSTRAINT iot_test_pk PRIMARY KEY(object_name,object_type,owner))
ORGANIZATION INDEX
NOLOGGING
AS
SELECT DISTINCT object_name, object_type,owner
FROM dba_objects
WHERE rownum = 0
/
Table created.
SQL> CREATE TABLE test5 NOLOGGING
AS
SELECT object_name, object_type,owner
FROM dba_objects
WHERE rownum = 0
/
Table created.
SQL>
Now that we have both tables created, let generate a bulk insert of 87,887 records on each table and compare the amount of redo each transaction generate.
SQL> set autotrace on statistics
SQL> INSERT /*+ APPEND */ INTO test5
SELECT DISTINCT object_name, object_type, owner
FROM dba_objects;
87887 rows created.
Statistics
———————————————————-
51460 redo size
87887 rows processed
SQL> INSERT /*+ APPEND */ INTO iot_test
SELECT DISTINCT object_name, object_type, owner
FROM dba_objects;
87887 rows created.
Statistics
———————————————————-
16391704 redo size
87887 rows processed
SQL>
The amount of redo generated by the INSERT /*+APPEND*/ in the normal table with NOLOGGING was 51,460 bytes and the amount generated when doing the same INSERT /*+APPEND*/ in the IOT table with NOLOGGING was 16,391,704 bytes. Clearly the first statement used the NOLOGGING option, but the second one not.
Now let’s take a deeper look and see why this happened. We will take a look at USER_TABLES and USER_INDEXES to see if both tables and the IOT index are in NOLOGGING mode:
SQL> SELECT table_name, logging FROM user_tables;
TABLE_NAME LOGGING
———————————– ——-
TEST5 NO
IOT_TEST
2 rows selected.
SQL>
SQL> SELECT index_name, logging FROM user_indexes;
INDEX_NAME LOGGING
———————————– ——-
IOT_TEST_PK NO
SQL>
As a result of the queries above, we can easily see that the normal table (TEST5) is set to NO (No LOGGING generation) but the IOT table (IOT_TEST) value for LOGGING is null, and that the index associated with the IOT table is in NOLOGGING mode. Now we will compare the amount of redo that will be generate when using the bulk insert in the CTAS (CREATE TABLE AS SELECT) statement.
To know how much redo each statement is generating, we will need to check first the total amount of redo generated in the database until now.
SQL> SELECT name,value FROM v$sysstat
WHERE name LIKE ‘%redo size%’;
NAME VALUE
————————————————- ———-
redo size 153316932
SQL>
Now, we will use the CTAS statement to create the table TEST5 in NOLOGGING mode and load all data at the same time, and after this, check the DB amount of redo again after the table was created.
SQL> CREATE TABLE test5 NOLOGGING
AS
SELECT DISTINCT object_name, object_type,owner
FROM dba_objects
/
Table created.
SQL>
SQL> SELECT name,value FROM v$sysstat
WHERE name LIKE ‘%redo size%’;
NAME VALUE
————————————————- ———-
redo size 153436632
SQL>
We can see that the amount of redo generated by the CTAS was (153,436,632 – 153,316,932) 119,700 bytes. Now we will do the same but using the IOT.
SQL> SELECT name,value FROM v$sysstat
WHERE name LIKE ‘%redo size%’;
NAME VALUE
————————————————- ———-
redo size 153436632
SQL> CREATE TABLE iot_test
(object_name,object_type,owner,
CONSTRAINT iot_test_pk PRIMARY KEY(object_name,object_type,owner))
ORGANIZATION INDEX
NOLOGGING
AS
SELECT DISTINCT object_name, object_type,owner
FROM dba_objects
/
Table created.
SQL> SELECT name,value FROM v$sysstat
WHERE name LIKE ‘%redo size%’;
NAME VALUE
————————————————- ———-
redo size 153573460
SQL>
For this second example, the amount of redo generated was (153,573,460-153,436,632) 136,828 bytes. This show us that when using CTAS and NOLOGGING, it will be more efficient and generate less redo if using NOLOGGING than a normal table, but the down side is that we will not be able to make use of NOLOGGING operation after the CTAS is done.
OOW has announced the new Oracle Database 12c, and this new version of the database product as expected has introduced many new features and enhancements for Backup and Recovery, on this post I will introduce you to some of them.
Of course, I cannot start talking about 12c without talk first about a revolutionary whole new concept that was introduced with this new version, it is called, “Pluggable Database”.
Pluggable Database
We are now able to have multiple databases sharing a single instance and Oracle binaries, and each of the databases will have their own configuration and parameters and what is better, each database will be completely isolated of each other without either know that each other exists.
A Container Database (CDB) is a single physical database that contains a root container with the main Oracle data dictionary, and at least one Pluggable Database (PDB) with specific application data, a PDB is a portable container with its own data dictionary including metadata and internal links to the system supplied objects in the root container, and this PDB will appear to an Oracle Net client as a traditional Oracle database. The CDB also contain a PDB called SEED, that is used as a template when a empty PDB needs to be created.
When creating a database on 12c, you can now create a CDB with one or more PDBs, and what is even better; you can easily clone a PDB, or unplug it and plug it on a different server with a preinstalled CBD if your target server is running out of resources such as CPU or memory. Many years ago, the introduction of external storages gave us the possibility to store data on external devices and the flexibility to plug and unplug them to any system independent of their OS (Operating System). As per example, you can connect an external device to a system using Windows XP and read your data without any problems, later you can unplug it and connect it to a laptop running Windows 7 and you will still be able to read your data. Now with the introduction of Oracle Pluggable Databases, we will be able to something similar with Oracle when upgrading a PDB , making this process simple and easy. All you will need to do to upgrade a PDB as per example (see figure bellow) is to unplug your PDB (step 1) that is using a CDB running 12.1.0.1, copy the PDB to the destination location with a CDB that is using a later version like 12.2.0.1 (step 2), and plug the PDB to the CDB (step 3), and your PDB is now upgraded to 12.2.0.1.
This new concept is a great solution for database consolidation and is very useful for multi-tenant SaaS (Software as a Service) providers, improving resource utilization, manageability, integration and service management, and You can have many PDBs you want inside a single container (A CDB can contain a maximum of 250 PDBs), also a PDB is fully backward compatible to pre-12.1 database releases.
RMAN New Features and Enhancements
Now we can continue and take a fast and closer look on some of the new features and enhancements introduced in this database version for RMAN.
As we saw before, the introduction of 12c and the new Pluggable Database concept made possible to easily centralize multiple databases maintaining the individuality of each one when using a single instance. The introduction of this new concept also forced Oracle to introduce some new enhancements to the already existent BACKUP, RESTORE and RECOVERY commands, to enable us to be able to make an efficient backup or restore of the complete CDB including all PDBs, or just one of more PDBs or if you want to be more specific, you can also just backup or restore one or more tablespace from a PDB.
The Oracle Enterprise Manager Database Console or Database Control that many of us used to manage an entire database its now deprecated and replaced by the new Oracle Enterprise Manager Database Express. This new tool uses flash technology and allows the DBA to easily manage configurations, storage, security and performance of a database. Note that RMAN, Data Pump and the Oracle Enterprise Manager Cloud Control are now the only tools able to perform backup and recovery operations in a Pluggable Database environment. In other words, you cannot manage Oracle Backups on Enterprise Manager Database Express.
In the past when using a third-party snapshot technology to make a backup or clone of a database you was forced to change the database to backup mode (BEGIN BACKUP) before execute the storage snapshot. This requirement is now not necessary if the following conditions are met:
On previous versions of Oracle database, the process to recover a table to an specific point-in-time was never easy, but now Oracle solved this major issue introducing the possibility to do a point-in-time recovery of a table, group of tables or even table partitions without affect the remaining database objects using RMAN, making this process easy and faster than ever before. Remember that previously Oracle have introduced features like database point-in-time recovery (DBPITR), tablespace point-in-time recovery (TSPITR) and Flashback database, this is an evolution of the same technology and principles
The recovery of tables and table partitions is useful when in the following situations:
Now is the time to take a closer look on some of the new features and enhancements introduced in this database version on Data Pump.
A new feature on Data Pump introduced with 12c is the possibility to disable logging generation during an import operation, allowing us to have faster imports due that the redo log information is not written to disk or even archived. This is particularly useful for large data loads like database migrations.
Another new feature introduced to Data Pump, is the option to export a view as a table. In this case, Data Pump will include in the dump file the corresponding table definition and all data that was visible in the view, instead to only write the view definition. This allows the Data Pump import to create it as a table with the same columns and data as the original view during the import process. All objects depending on the view will be also exported as if they were defined on the table, grants and constraints that are associated with the view will be now recorded as grants and constraints on the corresponding table in the dump file.
This new version of the Oracle database extends the maximum size of the VARCHAR2, NVARCHAR2 from 4000 bytes to 32767 bytes and the RAW data type from 2000 bytes to 32767 bytes. Columns with a declarated length of 4000 bytes or less will be stored inline and for columns with a length greater than 4000 bytes are called extended character data type columns and will be stored out-of-line, leveraging the Oracle Large Object (LOB) technology. Data Pump utilities and the related packages DBMS_DATAPUMP and DBMS_METADATA (PL/SQL) are now modified to support the extended data types.
I know that many more new features will come with Oracle 12c, but we will need to wait until the product is released to see what else was included!
The above document is intended to outline Oracle’s general product direction based on the information compiled from the references listed. It is intended for information purposes only for learning. The development and release of these functions including the release dates remain at the sole discretion of Oracle and no documentation is available at the time of this writing. The command shown may or may not be accurate when the final database release goes GA. Please refer Oracle documentation when it becomes available.
I just got a client that changed his server hostname and discovered that after this change he was unable to use the DB Control. To solve this problem you will just need to follow these simple steps:
1) Set $ORACLE_HOSTNAME to the old hostname:
[oracle]$ export ORACLE_HOSTNAME=oc-oracle-prod.com
2) De-configure the old DB Console by running the following command:
[oracle]$ $ORACLE_HOME/bin/emca-deconfig dbcontrol db STARTED EMCA at Jul 20, 2012 10:27:25 AM EM Configuration Assistant, Version 11.2.0.3.0 Production Copyright (c) 2003, 2011, Oracle. All rights reserved. Enter the following information: Database SID: COLD Do you wish to continue? [yes(Y)/no(N)]: Y log4j:WARN No appenders could be found for logger (emSDK.config). log4j:WARN Please initialize the log4j system properly. Jul 20, 2012 10:27:38 AM oracle.sysman.emcp.EMConfig perform INFO: This operation is being logged at /u01/app/oracle/cfgtoollogs/emca/COLD/emca_2012_07_20_10_27_24.log. Jul 20, 2012 10:27:38 AM oracle.sysman.emcp.util.DBControlUtil stopOMS INFO: Stopping Database Control (this may take a while) ... Enterprise Manager configuration completed successfully FINISHED EMCA at Jul 20, 2012 10:27:53 AM [oracle]$
3) Un-set $ORACLE_HOSTNAME in the environment, in other words, set $ORACLE_HOSTNAME to the new hostname:
[oracle]$ export ORACLE_HOSTNAME=oracle-prod.com
4) Configure the new DB Console:
[oracle]$ $ORACLE_HOME/bin/emca -config dbcontrol db -repos recreate STARTED EMCA at Jul 20, 2012 10:28:49 AM EM Configuration Assistant, Version 11.2.0.3.0 Production Copyright (c) 2003, 2011, Oracle. All rights reserved. Enter the following information: Database SID: COLD Listener port number: 1521 Listener ORACLE_HOME [ /u01/app/oracle/product/11.2.0.3/db_1 ]: Password for SYS user: Password for DBSNMP user: Password for SYSMAN user: Email address for notifications (optional): Outgoing Mail (SMTP) server for notifications (optional): ----------------------------------------------------------------- You have specified the following settings Database ORACLE_HOME ................ /u01/app/oracle/product/11.2.0.3/db_1 Local hostname ................ col-oracle-prod.lakros.com Listener ORACLE_HOME ................ /u01/app/oracle/product/11.2.0.3/db_1 Listener port number ................ 1521 Database SID ................ COLD Email address for notifications ............... Outgoing Mail (SMTP) server for notifications ............... ----------------------------------------------------------------- ---------------------------------------------------------------------- WARNING : While repository is dropped the database will be put in quiesce mode. ---------------------------------------------------------------------- Do you wish to continue? [yes(Y)/no(N)]: Y Jul 20, 2012 10:29:34 AM oracle.sysman.emcp.EMConfig perform INFO: This operation is being logged at /u01/app/oracle/cfgtoollogs/emca/COLDFX/emca_2012_07_20_10_28_49.log. Jul 20, 2012 10:29:35 AM oracle.sysman.emcp.EMReposConfig invoke INFO: Dropping the EM repository (this may take a while) ... Jul 20, 2012 10:31:10 AM oracle.sysman.emcp.EMReposConfig invoke INFO: Repository successfully dropped Jul 20, 2012 10:31:11 AM oracle.sysman.emcp.EMReposConfig createRepository INFO: Creating the EM repository (this may take a while) ... Jul 20, 2012 10:37:48 AM oracle.sysman.emcp.EMReposConfig invoke INFO: Repository successfully created Jul 20, 2012 10:37:52 AM oracle.sysman.emcp.EMReposConfig uploadConfigDataToRepository INFO: Uploading configuration data to EM repository (this may take a while) ... Jul 20, 2012 10:39:32 AM oracle.sysman.emcp.EMReposConfig invoke INFO: Uploaded configuration data successfully Jul 20, 2012 10:39:37 AM oracle.sysman.emcp.util.DBControlUtil secureDBConsole INFO: Securing Database Control (this may take a while) ... Jul 20, 2012 10:40:03 AM oracle.sysman.emcp.util.DBControlUtil secureDBConsole INFO: Database Control secured successfully. Jul 20, 2012 10:40:03 AM oracle.sysman.emcp.util.DBControlUtil startOMS INFO: Starting Database Control (this may take a while) ... Jul 20, 2012 10:40:32 AM oracle.sysman.emcp.EMDBPostConfig performConfiguration INFO: Database Control started successfully Jul 20, 2012 10:40:32 AM oracle.sysman.emcp.EMDBPostConfig performConfiguration INFO: >>>>>>>>>>> The Database Control URL is https://col-oracle-prod.lakros.com:5500/em <<<<<<<<<<< Jul 20, 2012 10:40:36 AM oracle.sysman.emcp.EMDBPostConfig invoke WARNING: ************************ WARNING ************************ Management Repository has been placed in secure mode wherein Enterprise Manager data will be encrypted. The encryption key has been placed in the file: /u01/app/oracle/product/11.2.0.3/db_1/oracle-prod.com_COLD/sysman/config/emkey.ora. Ensure this file is backed up as the encrypted data will become unusable if this file is lost. *********************************************************** Enterprise Manager configuration completed successfully FINISHED EMCA at Jul 20, 2012 10:40:36 AM [oracle@col-oracle-prod bin]$
Now after this last step is completed, your DB Console is ready to be used again 
If using Oracle RAC you need to replace the step s 2 and 4 for:
[oracle]$ $ORACLE_HOME/bin/emca -deconfig dbcontrol db -cluster [oracle]$ $ORACLE_HOME/bin/emca -config dbcontrol db -repos recreate -cluster
Cheers,
Francisco Munoz Alvarez
In this small Tutorial we will learn how to add a new disk to our Virtual Machine created in our previous tutorial. Add a new disk to the virtual machine is very easy, just follow the tasks bellow:
First, with your virtual machine off click on Settings, than go to Storage , than you will see two main options: IDE Controller and SATA Controller. Choose the second icon with the green “+” for SATA Controller and the screen bellow will show up. Please click on [Create new disk].
Now click [Next].
Select [Dynamically expanding storage] and click [Next].
Enter the file name for your new disk, for this example I choose NewDisk.vdi and enter the size of this new disk. For this tutorial I choose 4.0 GB, than click [Next].
Now to finish with this creation part, all you need to do is to click on [Finish] as bellow.
You can easily see the new disk in the Storage Settings.
Now Turn on your virtual machine and open a terminal screen. To prepare this new disk to be used, we will use the fdisk command as show bellow.
The next step will be to use the “Logical Volume Management” tool in the Oracle Enterprise Linux menu [“System/Administration” ] as you can see in the image bellow.
When the first screen is open, please go to [Uninitialized Entities], select the new disk partition (In this case /dev/sdd) and click on [Initialize Entity].
You will see the warning screen asking to confirm the initialization. Please click [Yes].
Now that the new disk was initialize, go to Unallocated Volumes and select the partition of disk /dev/sdd and click on [Create new Volume Group].
A screen asking to enter the new volume group name will appears, enter “Data” and click [Ok].
Now go to “Volume Groups”, select the Logical View of the Volume Group you just created “Data” and click on [Create New Logical Volume].
Now you will need to enter the LV Name, in this case Data. Select the size of the VL (4GB for this example), select the Filesystem to be mount (Ext3) and select the “Mount” and “Mount when rebooted” options. And click [Ok].
Please click [Yes] in the warning screen.
And That’s it, you can go to your terminal screen and run the command “df-k” to see all mount points including the new disk
Kind Regards,
Francisco Munoz Alvarez
The idea of this tutorial is to show you how you can create an Oracle Database 11gR2 using Oracle VirtualBox and OEL.
This installation should never be used for Production or Development purposes. This installation was created for educational purpose only, and is extremely helpful to learn and understand how Oracle works if you do not have access to a traditional hardware resource.
Now let’s start this fouth part of the tutorial (if do you want to see the first part, please click here: http://bit.ly/hmEVv5 , to the second part here: http://bit.ly/gkpHn6 , third part here: http://bit.ly/e0PSux or the fourth part http://bit.ly/e7UU6a).
*** Remember to always read the official Oracle Documentation before do a new installation of this product, you can find the documentation here: http://bit.ly/h7UHdV .
Now in the last part of this tutorial we will go thru the final and easy part, the installation and creation of the Oracle Database.
First we need to run the RunInstaller (from the directory where you unziped the Oracle binaries for the installation), than you will need to fill your information (as per your My Oracle Support/Metalink) if you want to be informed regarding any Oracle Security issues (if you do not want to enter your information, just click [Next] and click [OK] in the warning screen that will show up.
Next, Select Create and Configure a Database and click [Next].
Now select Server Class installation and click [Next].
Select Single Instance database installation and click [Next].
For this lab, we will select Typical Installation and click [Next].
Now, confirm all default information, enter your ASM password, Database name (in this example we will use “orcl”) and your administrative password and click [Next].
Oracle will now verify that all is ok.
The easy part, just click on [Finish] and the installation will begin.
Now is time to run the root.sh script as root and only after this click [Ok].
And that it! you have the Oracle binaries now installed and the new database “orcl” runing. This last screen show you your Enterprise Manager Database control URL and after click [Done] you are ready to play in your new environment.
Thank you
for follow this tutorial, and hope to see you around for the next one.
Kind Regards,
Francisco Munoz Alvarez
| Project Name | 11gR2/2011 |
| Author | Francisco Munoz Alvarez |
| Software Used | Oracle Enterprise Linux 5.6 Oracle VirtualBox 4.0.2 Oracle 11.2.0.2 Database and Infrastructure Software |
| Date | 02/02/2011 |
The idea of this tutorial is to show you how you can create an Oracle Database 11gR2 using Oracle VirtualBox and OEL.
This installation should never be used for Production or Development purposes. This installation was created for educational purpose only, and is extremely helpful to learn and understand how Oracle works if you do not have access to a traditional hardware resource.
Now let’s start this fouth part of the tutorial (if do you want to see the first part, please click here: http://bit.ly/hmEVv5 , to the second part here: http://bit.ly/gkpHn6 , or third part here: http://bit.ly/e0PSux).
*** Remember to always read the official Oracle Documentation before do a new installation of this product, you can find the documentation here: http://bit.ly/h7UHdV .
For the installation of the Oracle Grid Infrastructure installation you have the following requirements:
Before we start we need to copy the following files to our virtual machine (in this case we will copy them to a directory located at: /home/oracle/software):
Next you need to unzip the files and after this step you will have 2 directories, one called grid and another called database. After this step, please add a new disk to your virtual machine with the size of 4GB to allow you to install the Grid Infrastructure.
Now we will need to identify our partitions available (using the user root), for this we will need to use the following command:
# cat /proc/partitions
In our case, we will need to use fdisk to prepare the disk /dev/sdb as show bellow:
Than reboot your virtual machine to allow the new partition to be used.
Now let’s configure the disk(s ) to be used by ASM, for this we will use the command:
# oracleasm configure –i
Initialize the asmlib with the oracleasm init command. This command will load the oracleasm module and also mounts the oracleasm filesystem.
Now is time to use the oracleasm createdisk <diskname> <device_name> command to create the ASM disk label for each disk as follow:
To check if the disk is visible to the ASM and to see if it is mounted in the oracleasm filesystem use the following commands as bellow:
Now we are ready to install the grid infrastructure software, let’s go to the directory where your grid software is and run the command (as oracle, not root) ./runInstaller
Please, select your language and click [Next].
Select the disks
to be used by the ASM and click [Next]
Now enter the new passwords as requested bellow and click [Next]
For this example, we will select DBA to all operation system groups.
You are going to receive a warning message because using the same OS group, in this case, please click [Yes]
Please check if the path requested bellow are ok and click [Next]
In this case, this is the first Oracle installation in this server and Oracle will request you to enter the new location for the inventory, please check and click [Next]
Now to finish the Grid Infrastructure installation just click [Finish]
The next step will be to run as root the scripts as show bellow and click [OK]
Now your final screen will show you that all the software was install successful and you just need to click on [Close] to finish this process.
Congratulations, you have your Grid Infrastructure successfully installed in your virtual machine.
In the next part, we will install the Database software to conclude this tutorial.
Kind Regards,
Francisco Munoz Alvarez
Theme: WordPress Classic. Blog at WordPress.com.
