RESTORE RMAN BACKUP FROM ONE SERVER TO ANOTHER SERVER IN 10G:

[oracle@localhost dbs]$ export ORACLE_SID=prodb
[oracle@localhost dbs]$
[oracle@localhost dbs]$
[oracle@localhost dbs]$ rman target /

Recovery Manager: Release 10.2.0.1.0 - Production on Wed Sep 21 11:13:06 2016

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

connected to target database (not started)

RMAN> startup nomount

Oracle instance started

Total System Global Area    1073741824 bytes

Fixed Size                     2026296 bytes
Variable Size                754975944 bytes
Database Buffers             314572800 bytes
Redo Buffers                   2166784 bytes


RMAN> restore controlfile from '/u02/hotbackup/54rgbtcg_1_1';

Starting restore at 21-SEP-16
using channel ORA_DISK_1

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:16
output filename=/u01/oradata/proddb/control01.ctl
output filename=/u02/oradata/proddb/control02.ctl
output filename=/u03/oradata/proddb/control03.ctl
Finished restore at 21-SEP-16

RMAN> catalog backuppiece '/u02/hotbackup/hotbackup_4irg9sfe_1_1';

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of catalog command on default channel at 09/21/2016 11:21:42
ORA-01507: database not mounted


RMAN> alter database mount;

database mounted
released channel: ORA_DISK_1

RMAN> catalog backuppiece '/u02/hotbackup/hotbackup_4irg9sfe_1_1';

cataloged backuppiece
backup piece handle=/u02/hotbackup/hotbackup_4irg9sfe_1_1 recid=88 stamp=923138535

RMAN> catalog backuppiece '/u02/hotbackup/hotbackup_4drg9s5j_1_1';

cataloged backuppiece
backup piece handle=/u02/hotbackup/hotbackup_4drg9s5j_1_1 recid=89 stamp=923138545

RMAN> catalog backuppiece '/u02/hotbackup/hotbackup_48rg9rjj_1_1';

cataloged backuppiece
backup piece handle=/u02/hotbackup/hotbackup_48rg9rjj_1_1 recid=90 stamp=923138558

RMAN>  catalog backuppiece '/u02/hotbackup/hotbackup_4frg9s8d_1_1';

cataloged backuppiece
backup piece handle=/u02/hotbackup/hotbackup_4frg9s8d_1_1 recid=91 stamp=923138626

RMAN> catalog backuppiece '/u02/hotbackup/hotbackup_4brg9s3d_1_1';

cataloged backuppiece
backup piece handle=/u02/hotbackup/hotbackup_4brg9s3d_1_1 recid=92 stamp=923138631

RMAN> catalog backuppiece '/u02/hotbackup/hotbackup_4grg9sae_1_1';

cataloged backuppiece
backup piece handle=/u02/hotbackup/hotbackup_4grg9sae_1_1 recid=93 stamp=923138639

RMAN> catalog backuppiece '/u02/hotbackup/hotbackup_4hrg9sdd_1_1';

cataloged backuppiece
backup piece handle=/u02/hotbackup/hotbackup_4hrg9sdd_1_1 recid=94 stamp=923138645

RMAN> catalog backuppiece '/u02/hotbackup/hotbackup_47rg9rfc_1_1';

cataloged backuppiece
backup piece handle=/u02/hotbackup/hotbackup_47rg9rfc_1_1 recid=95 stamp=923138651

RMAN> catalog backuppiece '/u02/hotbackup/hotbackup_49rg9roe_1_1';

cataloged backuppiece
backup piece handle=/u02/hotbackup/hotbackup_49rg9roe_1_1 recid=96 stamp=923138656

RMAN> catalog backuppiece '/u02/hotbackup/hotbackup_44rg9quu_1_1';

cataloged backuppiece
backup piece handle=/u02/hotbackup/hotbackup_44rg9quu_1_1 recid=97 stamp=923138663

RMAN> catalog backuppiece '/u02/hotbackup/hotbackup_4crg9s4g_1_1';

cataloged backuppiece
backup piece handle=/u02/hotbackup/hotbackup_4crg9s4g_1_1 recid=98 stamp=923138667

RMAN> catalog backuppiece '/u02/hotbackup/hotbackup_4arg9rtu_1_1';

cataloged backuppiece
backup piece handle=/u02/hotbackup/hotbackup_4arg9rtu_1_1 recid=99 stamp=923138672

RMAN> catalog backuppiece '/u02/hotbackup/hotbackup_4erg9s6m_1_1';

cataloged backuppiece
backup piece handle=/u02/hotbackup/hotbackup_4erg9s6m_1_1 recid=100 stamp=923138679

RMAN> catalog backuppiece '/u02/hotbackup/hotbackup_45rg9r4e_1_1';

cataloged backuppiece
backup piece handle=/u02/hotbackup/hotbackup_45rg9r4e_1_1 recid=101 stamp=923138685

RMAN> catalog backuppiece '/u02/hotbackup/hotbackup_46rg9rb5_1_1';

cataloged backuppiece
backup piece handle=/u02/hotbackup/hotbackup_46rg9rb5_1_1 recid=102 stamp=923138691


RMAN> restore database;

Starting restore at 21-SEP-16
using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /u01/oradata/proddb/system01.dbf
restoring datafile 00003 to /u01/oradata/proddb/sysaux.dbf
restoring datafile 00018 to /u02/oradata/proddb/undotbs14.dbf
restoring datafile 00019 to /u03/oradata/proddb/undotbs15.dbf
channel ORA_DISK_1: reading from backup piece /u03/hotbackup_44rg9quu_1_1

channel ORA_DISK_1: restored backup piece 1
failover to piece handle=/u02/hotbackup/hotbackup_44rg9quu_1_1 tag=TAG20160920T161006
channel ORA_DISK_1: restore complete, elapsed time: 01:17:09
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00004 to /u02/oradata/proddb/proddb_data_ts_01.dbf
restoring datafile 00017 to /u03/oradata/proddb/undotbs13.dbf
channel ORA_DISK_1: reading from backup piece /u03/hotbackup_45rg9r4e_1_1
channel ORA_DISK_1: restore complete, elapsed time: 00:42:49
Finished restore at 30-SEP-16

database opened

RMAN> quit


Recovery Manager complete.
[oracle@localhost ~]$ export ORACLE_SID=primdb
[oracle@localhost ~]$ sqlplus

SQL*Plus: Release 10.2.0.1.0 - Production on Wed Sep 21 13:48:58 2016

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Enter user-name: /as sysdba

Connected to:
Oracle Database 10g Release 10.2.0.1.0 - 64bit Production

SQL>

Request you all to put your valuable comments on  posts of  this blog, will motivate me to write more good post.

Enabling Automated SQL Tuning in Oracle 11g

To identify if Automatic SQL Tuning job is enabled and regularly running. Use the following query to determine if any Automatic SQL Tuning jobs are enabled:

SELECT client_name, status, consumer_group, window_group FROM dba_autotask_client ORDER BY client_name;

 If the Automatic SQL Tuning job is enabled, you will see something like this:

 SELECT client_name, status FROM dba_autotask_client;

CLIENT_NAME                                                      STATUS
---------------------------------------------------------------- --------
auto optimizer stats collection                                  ENABLED
auto space advisor                                                  ENABLED
sql tuning advisor                                                    ENABLED

Run the following query to view the last several times the Automatic SQL Tuning Advisor job has run:

SELECT task_name, status, TO_CHAR(execution_end,'DD-MON-YY HH24:MI')

FROM dba_advisor_executions

WHERE task_name='SYS_AUTO_SQL_TUNING_TASK'

ORDER BY execution_end;

TASK_NAME                      STATUS      TO_CHAR(EXECUTION_END,'DD-MON-YYHH24:MI')

------------------------------ ----------- ---------------------------------------------------------------------------

SYS_AUTO_SQL_TUNING_TASK       COMPLETED   05-JUN-16 06:01

SYS_AUTO_SQL_TUNING_TASK       COMPLETED   06-JUN-16 22:01

SYS_AUTO_SQL_TUNING_TASK       COMPLETED   07-JUN-16 22:00

SYS_AUTO_SQL_TUNING_TASK       COMPLETED   08-JUN-16 22:00

SYS_AUTO_SQL_TUNING_TASK       COMPLETED   09-JUN-16 22:02

SYS_AUTO_SQL_TUNING_TASK       COMPLETED   10-JUN-16 22:01

SYS_AUTO_SQL_TUNING_TASK       COMPLETED   11-JUN-16 06:00

SYS_AUTO_SQL_TUNING_TASK       COMPLETED   12-JUN-16 06:03

SYS_AUTO_SQL_TUNING_TASK       COMPLETED   13-JUN-16 22:02

SYS_AUTO_SQL_TUNING_TASK       COMPLETED   14-JUN-16 22:00

SYS_AUTO_SQL_TUNING_TASK       COMPLETED   15-JUN-16 22:01

SYS_AUTO_SQL_TUNING_TASK       COMPLETED   16-JUN-16 22:00

SYS_AUTO_SQL_TUNING_TASK       COMPLETED   17-JUN-16 22:02

SYS_AUTO_SQL_TUNING_TASK       COMPLETED   18-JUN-16 06:00

SYS_AUTO_SQL_TUNING_TASK       COMPLETED   19-JUN-16 06:00

SYS_AUTO_SQL_TUNING_TASK       COMPLETED   20-JUN-16 22:01

SYS_AUTO_SQL_TUNING_TASK       COMPLETED   21-JUN-16 22:00

SYS_AUTO_SQL_TUNING_TASK       COMPLETED   22-JUN-16 22:01

SYS_AUTO_SQL_TUNING_TASK       COMPLETED   23-JUN-16 22:05

SYS_AUTO_SQL_TUNING_TASK       COMPLETED   24-JUN-16 22:01

SYS_AUTO_SQL_TUNING_TASK       COMPLETED   25-JUN-16 06:00

SYS_AUTO_SQL_TUNING_TASK       COMPLETED   26-JUN-16 06:00

SYS_AUTO_SQL_TUNING_TASK       COMPLETED   27-JUN-16 22:02

SYS_AUTO_SQL_TUNING_TASK       COMPLETED   28-JUN-16 22:01

SYS_AUTO_SQL_TUNING_TASK       COMPLETED   29-JUN-16 22:00

SYS_AUTO_SQL_TUNING_TASK       COMPLETED   30-JUN-16 22:00

SYS_AUTO_SQL_TUNING_TASK       COMPLETED   01-JUL-16 22:00

SYS_AUTO_SQL_TUNING_TASK       COMPLETED   02-JUL-16 06:00

SYS_AUTO_SQL_TUNING_TASK       COMPLETED   03-JUL-16 06:00

SYS_AUTO_SQL_TUNING_TASK       COMPLETED   04-JUL-16 22:01

You will get the last execution date-time and status of Automatic SQL Tuning Task (SYS_AUTO_TUNING_TASK) or you will get the message No rows selected,

which obviously means that the Automatic SQL Tuning is enabled but the job has not been running.

How Does Automatic SQL Tuning Work?

When you create a database in Oracle Database 11g or higher, Oracle automatically implements three automatic maintenance jobs:

1 Automatic SQL Tuning Advisor

2 Automatic Segment Advisor

3 Automatic Optimizer Statistics Collection

How to View the Results of SQL Tuning Advisor:

SQL> SELECT DBMS_AUTO_SQLTUNE.REPORT_AUTO_TUNING_TASK FROM DUAL;

SQL> SELECT DBMS_SQLTUNE.REPORT_AUTO_TUNING_TASK FROM DUAL;

How to Enable or DISABLE Automatic SQL Tuning

BEGIN

DBMS_AUTO_TASK_ADMIN.ENABLE(

client_name => 'sql tuning advisor',

operation => NULL,

window_name => NULL);

END;

/

To DISABLE Automatic SQL Tuning, use the DISABLE procedure as shown below

BEGIN

DBMS_AUTO_TASK_ADMIN.DISABLE(

client_name => 'sql tuning advisor',

operation => NULL,

window_name => NULL);

END;

Daily Activity--Oracle dba

Shift handover report, it contains pending request.


    1)    Oracle Database instance is running or not
    2)    Database Listener is running or not.
    3)    Check any session blocking the other session
    4)    Check the alert log for an error
    5)    Check is there any dbms jobs running & check the status of the same
    6)    Check the Top session using more Physical I/O
    7)    Check the number of log switch per hour
    8)    How_much_redo_generated_per_hour
    9)    Run the statpack report
   10)    Detect lock objects
   11)  Check the SQL query consuming lot of resources.
   12)  Check the usage of SGA
   13)  Display database sessions using rollback segments
   14)  State of all the DB Block Buffer
   15)  Check for the invalid objects and recompile.
   16)  Verify the success of archive log backups, based on the backup interval.
   17)  Monitoring space Availability
   18)  Check tablespaces should not be used more that 75%.
   19)  Viewing the Alert log file to analyze the ORA errors if any.
   20)  Check all last night backups were successful.
   21)  Most Important - Check your oracle license and do not run/execute/create     anything beyond the oracle license policy.

To Find out the total size of the DB and Database growth weekly/monthly for future
necessary space arrangements

select( select sum(bytes)/1024/1024/1024 data_size from dba_data_files ) +( select nvl(sum(bytes),0)/1024/1024/1024 temp_size from dba_temp_files ) +( select sum(bytes)/1024/1024/1024 redo_size from sys.v_$log ) +( select sum(BLOCK_SIZE*FILE_SIZE_BLKS)/1024/1024/1024 controlfile_size from v$controlfile) "Size in GB" from dual;

Query to find the 10 Largest Objects in DB:
============================================


 select * from (select    SEGMENT_NAME,SEGMENT_TYPE,BYTES/1024/1024/1024 GB,TABLESPACE_NAME from  dba_segments order by 3 desc ) where rownum<= 10;

DBMS_COMPARISON: Oracle 11g new features

It can use this package for several reason: if  checking or replicatiion  between production and test databases data etc.

For all scan modes to be supported by the DBMS_COMPARISON package, the database objects must have one of the following types of indexes:

    *  single-column index on a number, timestamp, interval, or DATE datatype column
    *  A composite index that only includes number, timestamp, interval, or DATE datatype columns. Each column in the composite index            must either have a NOT NULL constraint or be part of the primary key.

 The DBMS_COMPARISON package supports all Oracle Data Types (VARCHAR2, NVARCHAR2, NUMBER, FLOAT, DATE, TIMESTAMP etc) except the following:

LONG
LONG RAW
ROWID
UROWID
CLOB
NCLOB
BLOB
BFILE

There are some useful data dictionary views

    *    DBA/USER_COMPARISON_SCAN
    *    DBA/USER_COMPARISON_SCAN_SUMMARY
    *    DBA/USER_COMPARISON_SCAN_VALUES
    *    DBA/USER_COMPARISON_ROW_DIF

Now,Let us proceed

DECLARE
        scan_info  dbms_comparison.comparison_type;
        scan_id    INTEGER;
        consistent BOOLEAN;
BEGIN
--  dbms_comparison.drop_comparison(comparison_name => 'empcompare');
dbms_comparison.create_comparison(comparison_name    => 'empcompare',
schema_name        => 'TEST',
object_name        => 'compareclone',
dblink_name        => null,
remote_schema_name => 'TEST_REMOTE',
remote_object_name => 'EMP'
);

consistent := dbms_comparison.compare(comparison_name => 'empcompare', scan_info       => scan_info);

IF consistent = TRUE THEN
        dbms_output.put_line('No differences were found.');
ELSE
        dbms_output.put_line('Differences were found.');
END IF;

dbms_comparison.converge(comparison_name  => 'empcompare',
scan_id          => scan_info.scan_id,
scan_info        => scan_info,
converge_options => dbms_comparison.CMP_CONVERGE_REMOTE_WINS);

dbms_output.put_line('Scan ID: ' || scan_info.scan_id);
dbms_output.put_line('Local Rows Merged:' || scan_info.loc_rows_merged);
dbms_output.put_line('Remote Rows Merged:' || scan_info.rmt_rows_merged);
dbms_output.put_line('Local Rows Deleted:' || scan_info.loc_rows_deleted);
dbms_output.put_line('Remote Rows Deleted:' ||scan_info.rmt_rows_deleted);
END;

Now ,create a new table  with structure only

17:00:47 TEST_REMOTE> create table emp1 as select  *  from TEST.emp where 1=2;

after delete run the below procedure
====================================


DECLARE
        scan_info  dbms_comparison.comparison_type;
        scan_id    INTEGER;
        consistent BOOLEAN;
BEGIN
--  dbms_comparison.drop_comparison(comparison_name => 'empcompare_new');
dbms_comparison.create_comparison(comparison_name    => 'empcompare_new',
schema_name        => 'TEST',
object_name        => 'compareclone',
dblink_name        => null,
remote_schema_name => 'TEST_REMOTE',
remote_object_name => 'EMP'
);

consistent := dbms_comparison.compare(comparison_name => 'empcompare_new', scan_info       => scan_info);

IF consistent = TRUE THEN
        dbms_output.put_line('No differences were found.');
ELSE
        dbms_output.put_line('Differences were found.');
END IF;

dbms_comparison.converge(comparison_name  => 'empcompare_new',
scan_id          => scan_info.scan_id,
scan_info        => scan_info,
converge_options => dbms_comparison.CMP_CONVERGE_REMOTE_WINS);

dbms_output.put_line('Scan ID: ' || scan_info.scan_id);
dbms_output.put_line('Local Rows Merged:' || scan_info.loc_rows_merged);
dbms_output.put_line('Remote Rows Merged:' || scan_info.rmt_rows_merged);
dbms_output.put_line('Local Rows Deleted:' || scan_info.loc_rows_deleted);
dbms_output.put_line('Remote Rows Deleted:' ||scan_info.rmt_rows_deleted);
END;
/

DECLARE
*
ERROR at line 1:
ORA-23626: No eligible index on table TEST.compareclone
ORA-06512: at "SYS.DBMS_COMPARISON", line 4793
ORA-06512: at "SYS.DBMS_COMPARISON", line 448
ORA-06512: at line 7

17:04:47 TEST_REMOTE>alter table emp1 add CONSTRAINT "emp_ID_PK" PRIMARY KEY (EMP_CODE);

Table altered.

Elapsed: 00:00:00.03

17:05:46 TEST_REMOTE>DECLARE
        scan_info  dbms_comparison.comparison_type;
        scan_id    INTEGER;
        consistent BOOLEAN;
BEGIN
--  dbms_comparison.drop_comparison(comparison_name => 'empcompare');
dbms_comparison.create_comparison(comparison_name    => 'empcompare',
schema_name        => 'TEST',
object_name        => 'compareclone',
dblink_name        => null,
remote_schema_name => 'TEST_REMOTE',
remote_object_name => 'EMP'
);

consistent := dbms_comparison.compare(comparison_name => 'empcompare', scan_info       => scan_info);

IF consistent = TRUE THEN
        dbms_output.put_line('No differences were found.');
ELSE
        dbms_output.put_line('Differences were found.');
END IF;

dbms_comparison.converge(comparison_name  => 'empcompare',
scan_id          => scan_info.scan_id,
scan_info        => scan_info,
converge_options => dbms_comparison.CMP_CONVERGE_REMOTE_WINS);

dbms_output.put_line('Scan ID: ' || scan_info.scan_id);
dbms_output.put_line('Local Rows Merged:' || scan_info.loc_rows_merged);
dbms_output.put_line('Remote Rows Merged:' || scan_info.rmt_rows_merged);
dbms_output.put_line('Local Rows Deleted:' || scan_info.loc_rows_deleted);
dbms_output.put_line('Remote Rows Deleted:' ||scan_info.rmt_rows_deleted);
END;17:05:47   2  17:05:47   3  17:05:47   4  17:05:47   5  17:05:47   6  17:05:47   7  17:05:47   8  17:05:47   9  17:05:47  10  17:05:47  11  17:05:47  12  17:05:47  13  17:05:47  14  17:05:47  15  17:05:47  16  17:05:47  17  17:05:47  18  17:05:47  19  17:05:47  20  17:05:47  21  17:05:47  22  17:05:47  23  17:05:47  24  17:05:47  25  17:05:47  26  17:05:47  27  17:05:47  28  17:05:47  29  17:05:47  30  17:05:47  31  17:05:47  32  17:05:47  33 
17:05:48  34  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.99
17:05:50 TEST_REMOTE>select count(1) from emp1;

  COUNT(1)
----------
      2339

Elapsed: 00:00:00.00
17:06:09 TEST_REMOTE>delete from emp1 where rownum <13;

12 rows deleted.

Elapsed: 00:00:00.00
17:06:59 TEST_REMOTE>commit;

Commit complete.

Elapsed: 00:00:00.00
17:07:02 TEST_REMOTE>select count(1) from emp1;

  COUNT(1)
----------
      2327


17:08:13 TEST_REMOTE>DECLARE
        scan_info  dbms_comparison.comparison_type;
        scan_id    INTEGER;
        consistent BOOLEAN;
BEGIN
--  dbms_comparison.drop_comparison(comparison_name => 'empcompare_new');
dbms_comparison.create_comparison(comparison_name    => 'empcompare_new',
schema_name        => 'TEST',
object_name        => 'compareclone',
dblink_name        => null,
remote_schema_name => 'TEST_REMOTE',
remote_object_name => 'EMP'
);

consistent := dbms_comparison.compare(comparison_name => 'empcompare_new', scan_info       => scan_info);

IF consistent = TRUE THEN
        dbms_output.put_line('No differences were found.');
ELSE
        dbms_output.put_line('Differences were found.');
END IF;

dbms_comparison.converge(comparison_name  => 'empcompare_new',
scan_id          => scan_info.scan_id,
scan_info        => scan_info,
converge_options => dbms_comparison.CMP_CONVERGE_REMOTE_WINS);

dbms_output.put_line('Scan ID: ' || scan_info.scan_id);
dbms_output.put_line('Local Rows Merged:' || scan_info.loc_rows_merged);
dbms_output.put_line('Remote Rows Merged:' || scan_info.rmt_rows_merged);
dbms_output.put_line('Local Rows Deleted:' || scan_info.loc_rows_deleted);
dbms_output.put_line('Remote Rows Deleted:' ||scan_info.rmt_rows_deleted);
END;17:11:54   2  17:11:54   3  17:11:54   4  17:11:54   5  17:11:54   6  17:11:54   7  17:11:54   8  17:11:54   9  17:11:54  10  17:11:54  11  17:11:54  12  17:11:54  13  17:11:54  14  17:11:54  15  17:11:54  16  17:11:54  17  17:11:54  18  17:11:54  19  17:11:54  20  17:11:54  21  17:11:54  22  17:11:54  23  17:11:54  24  17:11:54  25  17:11:54  26  17:11:54  27  17:11:54  28  17:11:54  29  17:11:54  30  17:11:54  31  17:11:54  32  17:11:54  33 
17:11:55  34  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.48
17:11:57 TEST_REMOTE>select count(*) from emp1;

  COUNT(*)
----------
      2339

ORA-24248 XMLDB extensible security not installed

A few days ago I ran into this problem. We upgraded one of our development database from oracle 10gR2 to 11gR1 recently.
 After the upgrade the application was broken very badly. Any user trying to login to the application was getting the error below:


ORA-24248: XML DB extensible security not installed
ORA-06512: at "SYS.UTL_INADDR", line 19
ORA-06512: at "SYS.UTL_INADDR", line 40
ORA-06512: at line 1


 I found that that in one of Java classes in the application, where a connection was being established to the databases, a query was being executed like this.


SELECT global_name,utl_inaddr.get_host_address FROM global_name;

The error above is quite self explanatory. I went ahead and verified the existence of Oracle XML Database and found the following:


11:49:41 sql>select comp_name from dba_registry;

COMP_NAME
----------------------------------------------------------------

Oracle Database Catalog Views
Oracle Database Packages and Types
Oracle Enterprise Manager

As can be seen from the output of the above query, we were missing Oracle XML Database. I was able to reproduce the problem in sqlplus.


11:52:23 sql>select global_name,utl_inaddr.get_host_address from global_name;
select global_name,utl_inaddr.get_host_address from global_name
                   *
ERROR at line 1:
ORA-24248: XML DB extensible security not installed
ORA-06512: at "SYS.UTL_INADDR", line 19
ORA-06512: at "SYS.UTL_INADDR", line 40
ORA-06512: at line 1


Install XML Database: Oracle 11g Enterprise Edition


$ cd $ORACLE_HOME/rdbms/admin

$ sqlplus / as sysdba

[oracle@Localhost ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Fri Apr 8 11:55:27 2016

Copyright (c) 1982, 2009, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options


SQL> spool install_xml_db.log
SQL> @catqm xdb sysaux temp YES
-- xdb is the password for XML Database repository schema
-- sysaux is the default tablespace for XML Database repository schema.
-- temp is the default temporary tablespace for XML Database repository schema.
-- YES is for using secure files for xdb$resource
.
.
.

SQL> declare
  2    suf  varchar2(26);
  3    stmt varchar2(2000);
  4  begin
  5    select toksuf into suf from xdb.xdb$ttset where flags = 0;
  6    stmt := 'grant all on XDB.X$PT' || suf || ' to DBA';
  7    execute immediate stmt;
  8    stmt := 'grant all on XDB.X$PT' || suf || ' to SYSTEM WITH GRANT OPTION';
  9    execute immediate stmt;
 10  end;
 11  /

PL/SQL procedure successfully completed.

SQL>spool off
11:58:14 SQL> select comp_name , status from dba_registry;

 COMP_NAME                                STATUS
---------------------------------------- --------------------------------------------
Oracle XML Database                      VALID
Oracle Database Catalog Views          VALID
Oracle Database Packages and Types     VALID
Oracle Enterprise Manager         VALID

The XML Database installation is complete. I opened the install_xml_db.log and didn't find any significant errors in it. Then I verified from dba_registry again to see the status of XML Database installed.


12:04:33 SQL> select global_name,utl_inaddr.get_host_address from global_name;
select global_name,utl_inaddr.get_host_address from global_name
                   *
 ERROR at line 1:
ORA-24247: network access denied by access control list (ACL)
ORA-06512: at "SYS.UTL_INADDR", line 19
ORA-06512: at "SYS.UTL_INADDR", line 40
ORA-06512: at line 1

Now the ORA-24248 is gone because we have XML Database installed. Now we are seeing ORA-24247, which means we can't access any network host using UTL_INADDR unless we allow access to this host via ACL.


SQL> conn / as sysdba
Connected.

BEGIN
   DBMS_NETWORK_ACL_ADMIN.CREATE_ACL (
        acl          =>'my_application_acl.xml',
        description  => 'ACL for users of my application.',
        principal    => 'SCOTT',
        is_grant     => TRUE,
        privilege    => 'resolve',
        start_date   => null,
        end_date     => null
    );
END;
/

PL/SQL procedure successfully completed.

/*
 All that UTL_INADDR.get_host_address does is resolve the host and return the IP Address.
 DBMS_NETWORK_ACL_ADMIN.CREATE_ACL creates an ACL and grant privilege to a user or role
 to that ACL.
 For the sake of this article I am using SCOTT as the grantee of the privilege resolve but
 in my application I granted this privilege to a role which was assigned to all the
 application users. So through that role all the users were granted resole privilege to
 this ACL.
*/


BEGIN
   DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL (
     acl         =>'my_application_acl.xml',
     host        => 'localhost',
     lower_port  => null,
     upper_port  => null
     );
END;
/

PL/SQL procedure successfully completed.

/*
 When UTL_INADDR.get_host_address is invoked without a host name specified with it,
 it sends the request to localhost. So we need to assign our ACL to the host "localhost".
*/

commit;


SQL> conn scott/tiger
Connected.
SQL> set lines 10000
SQL> column global_name format a20
SQL> column get_host_address format a15
SQL> select global_name,utl_inaddr.get_host_address from global_name;


TESTDB  10.XXX.XXX.XXX



As you can see above, SCOTT is now able to get server's IP Adress using utl_inaddr.get_host_address. And this solves our problem with the application.

Uninstall XML Database: Oracle 11g Enterprise Edition


$ cd $ORACLE_HOME/rdbms/admin
$ sqlplus / as sysdba

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> @catnoqm.sql
-- This will uninstall the Oracle XML Database.

Where are my DBA_BLOCKERS and DBA_WAITERS ?

While doing performance checks and tuning for one of the database found that two of the very well known dynamic views are not working and throwing “Does Not Exist”. We are using – Oracle Database 11g Release 11.2.0.1.0 – 64bit Production.
I was trying to call the views using SYS user.

The two dynamic views are:

DBA_BLOCKERS: It displays a session if it is not waiting for a locked object but is holding a lock on an object for which another session is waiting.
DBA_WAITERS: It shows all the sessions that are waiting for a lock, but do not hold locks for which another session is waiting.

SQL> conn / as sysdba
Connected.

SQL> desc dba_blockers;
ERROR:
ORA-04043: object dba_blockers does not exist

SQL> desc dba_waiters
ERROR:
ORA-04043: object dba_waiters does not exist

[oracle@testdb ~]$ sqlplus '/as sysdba'

SQL*Plus: Release 11.2.0.1.0 Production on Mon Mar 28 17:25:18 2016

Copyright (c) 1982, 2009, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> @?/rdbms/admin/catblock.sql

View created.


Synonym created.


Grant succeeded.

drop synonym DBA_LOCKS
             *
ERROR at line 1:
ORA-01434: private synonym to be dropped does not exist


drop view DBA_LOCKS
*
ERROR at line 1:
ORA-00942: table or view does not exist



View created.


Synonym created.


Grant succeeded.


Synonym created.


View created.


Synonym created.


Grant succeeded.


View created.


Synonym created.


Grant succeeded.


View created.


Synonym created.


Grant succeeded.


View created.


Synonym created.


Grant succeeded.


View created.


Synonym created.


Grant succeeded.


This will create following views.

dba_locks
dba_blockers
dba_kgllock
dba_lock
dba_lock_internal
dba_dml_locks
dba_ddl_locks
dba_waiters












SQL> conn test/test
Connected.
SQL> select sid, username, program, machine from v$session where sid in (select holding_session from dba_blockers);

no rows selected

Starting the Oracle Enterprise Manager Console --oracle 11g

Step 1:

[oracle@localdomain ~]$ emca -deconfig dbcontrol db -repos drop

STARTED EMCA at Mar 21, 2016 2:12:28 PM
EM Configuration Assistant, Version 11.2.0.0.2 Production
Copyright (c) 2003, 2005, Oracle.  All rights reserved.

Enter the following information:
Database SID: testdb
Listener port number: 1521
Password for SYS user: 
Password for SYSMAN user: 

Do you wish to continue? [yes(Y)/no(N)]: y
Mar 21, 2016 2:12:41 PM oracle.sysman.emcp.EMConfig perform
INFO: This operation is being logged at /u01/app/oracle/cfgtoollogs/emca/testdb/emca_2016_03_21_14_12_28.log.
Mar 21, 2016 2:12:41 PM oracle.sysman.emcp.EMDBPreConfig performDeconfiguration
WARNING: EM is not configured for this database. No EM-specific actions can be performed.
Mar 21, 2016 2:12:41 PM oracle.sysman.emcp.EMReposConfig invoke
INFO: Dropping the EM repository (this may take a while) ...
Mar 21, 2016 2:13:47 PM oracle.sysman.emcp.EMReposConfig invoke
INFO: Repository successfully dropped
Enterprise Manager configuration completed successfully
FINISHED EMCA at Mar 21, 2016 2:13:47 PM

Step 2:

[oracle@localdomain ~]$ emca -repos create

STARTED EMCA at Mar 21, 2016 2:14:07 PM
EM Configuration Assistant, Version 11.2.0.0.2 Production
Copyright (c) 2003, 2005, Oracle.  All rights reserved.

Enter the following information:
Database SID: testdb
Listener port number: 1521
Password for SYS user: 
Password for SYSMAN user: 
Password for SYSMAN user: 
Do you wish to continue? [yes(Y)/no(N)]: y
Mar 21, 2016 2:14:21 PM oracle.sysman.emcp.EMConfig perform
INFO: This operation is being logged at /u01/app/oracle/cfgtoollogs/emca/testdb/emca_2016_03_21_14_14_07.log.
Mar 21, 2016 2:14:21 PM oracle.sysman.emcp.EMReposConfig createRepository
INFO: Creating the EM repository (this may take a while) ...
Mar 21, 2016 2:18:03 PM oracle.sysman.emcp.EMReposConfig invoke
INFO: Repository successfully created
Enterprise Manager configuration completed successfully
FINISHED EMCA at Mar 21, 2016 2:18:03 PM
[oracle@localdomain ~]$

Step 3:

[oracle@localdomain ~]$ emca -config dbcontrol db

STARTED EMCA at Mar 21, 2016 2:18:18 PM
EM Configuration Assistant, Version 11.2.0.0.2 Production
Copyright (c) 2003, 2005, Oracle.  All rights reserved.

Enter the following information:
Database SID: testdb
Listener port number: 1521
Listener ORACLE_HOME [ /u01/app/oracle/product/11.2.0/db_1 ]:
Password for SYS user: 
Password for DBSNMP user:  s
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/db_1

Local hostname ................ localdomain.local.com
Listener ORACLE_HOME ................ /u01/app/oracle/product/11.2.0/db_1
Listener port number ................ 1521
Database SID ................ testdb
Email address for notifications ...............
Outgoing Mail (SMTP) server for notifications ...............

-----------------------------------------------------------------
Do you wish to continue? [yes(Y)/no(N)]: y
Mar 21, 2016 2:18:40 PM oracle.sysman.emcp.EMConfig perform
INFO: This operation is being logged at /u01/app/oracle/cfgtoollogs/emca/testdb/emca_2016_03_21_14_18_18.log.
Mar 21, 2016 2:18:42 PM oracle.sysman.emcp.EMReposConfig uploadConfigDataToRepository
INFO: Uploading configuration data to EM repository (this may take a while) ...
Mar 21, 2016 2:19:26 PM oracle.sysman.emcp.EMReposConfig invoke
INFO: Uploaded configuration data successfully
Mar 21, 2016 2:19:28 PM oracle.sysman.emcp.util.DBControlUtil configureSoftwareLib
INFO: Software library configured successfully.
Mar 21, 2016 2:19:28 PM oracle.sysman.emcp.EMDBPostConfig configureSoftwareLibrary
INFO: Deploying Provisioning archives ...
Mar 21, 2016 2:19:45 PM oracle.sysman.emcp.EMDBPostConfig configureSoftwareLibrary
INFO: Provisioning archives deployed successfully.
Mar 21, 2016 2:19:45 PM oracle.sysman.emcp.util.DBControlUtil secureDBConsole
INFO: Securing Database Control (this may take a while) ...
Mar 21, 2016 2:19:51 PM oracle.sysman.emcp.util.DBControlUtil secureDBConsole
INFO: Database Control secured successfully.
Mar 21, 2016 2:19:51 PM oracle.sysman.emcp.util.DBControlUtil startOMS
INFO: Starting Database Control (this may take a while) ...
Mar 21, 2016 2:20:08 PM oracle.sysman.emcp.EMDBPostConfig performConfiguration
INFO: Database Control started successfully
Mar 21, 2016 2:20:08 PM oracle.sysman.emcp.EMDBPostConfig performConfiguration
INFO: >>>>>>>>>>> The Database Control URL is https://localdomain.local.com:1158/em <<<<<<<<<<<
Mar 21, 2016 2:20:10 PM 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/db_1/localdomain.local.com_testdb/sysman/config/emkey.ora.   Please 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 Mar 21, 2016 2:20:10 PM

Step 4:

[oracle@localdomain ~]$ emctl status dbconsole
Oracle Enterprise Manager 11g Database Control Release 11.2.0.1.0
Copyright (c) 1996, 2009 Oracle Corporation.  All rights reserved.
https://localdomain.local.com:1158/em/console/aboutApplication
Oracle Enterprise Manager 11g is running.
------------------------------------------------------------------
Logs are generated in directory /u01/app/oracle/product/11.2.0/db_1/localdomain.local.com_testdb/sysman/log
[oracle@localdomain ~]$