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 ~]$

How To Restore 10g RMAN Backup to 11g

The steps for 10g database:


1- Run the utlu112i.sql Pre-upgrade script. You can find this script under @$ORACLE_HOME/rdbms/admin/.
It must be copied from the 11g database software.



SQL> @$ORACLE_HOME/rdbms/admin/utlu112i.sql

SQL> select version from v$timezone_file;

and do the following steps


SQL> ALTER TABLE registry$database ADD (tz_version NUMBER);

SQL> UPDATE registry$database set tz_version =4;

SQL> ALTER  PACKAGE "SYS"."DBMS_REGISTRY"  COMPILE BODY;

SQL> ALTER VIEW "SYS"."DBA_REGISTRY_DATABASE"  COMPILE;


Step :2

Now connect to 10g and take RMAN full backup


[oracle@Tetsdby ~]$ rman  target /

RMAN> backup as backupset database;


The below steps for 11g database:



Step :3

Now copy backupfiles 10g to 11g database server


Create temporary pfile in $ORACLE_HOME/dbs


[oracle@testdb dbs]$ vi  inittestdb.ora
testdb.__db_cache_size=360710144
testdb.__java_pool_size=33554432
testdb.__large_pool_size=33554432
testdb.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
testdb.__pga_aggregate_target=1073741824
testdb.__sga_target=1073741824
testdb.__shared_io_pool_size=0
testdb.__shared_pool_size=415236096
testdb.__streams_pool_size=8388608
*._system_trig_enabled=FALSE
*.aq_tm_processes=2
*.audit_file_dest='/u01/testdb/adump'
*.audit_trail='db'
*.compatible='11.2.0'
*.control_files='/u01/testdb/ora_control1.ctl','/u01/testdb/ora_control2.ctl'
*.cursor_sharing='SIMILAR'
*.db_block_size=8192
*.db_domain=''
*.db_name='testdb'
*.db_recovery_file_dest='/u01/testdb/flash_recovery_area'
*.db_recovery_file_dest_size=2G
*.db_recycle_cache_size=209715200
*.diagnostic_dest='/u01/testdb/diag'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=testdb)'
*.fast_start_mttr_target=2000
*.java_pool_size=33554432
*.job_queue_processes=10
*.large_pool_size=33554432
*.log_archive_dest_1='LOCATION=/testdb/archv'
*.log_checkpoint_interval=10000
*.memory_target=2G
*.open_cursors=1000
*.optimizer_index_caching=90
*.optimizer_mode='FIRST_ROWS'
*.pga_aggregate_target=838860800
*.processes=500
*.remote_login_passwordfile='EXCLUSIVE'
*.resource_limit=TRUE
*.session_cached_cursors=1000
*.sessions=555
*.sga_max_size=1073741824
*.transactions=600
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS2'

and database startup nomount stage


[oracle@Tetsdby ~]$ rman  target /

RMAN> startup nomount;


Restore control file backup

RMAN> restore controlfile from ‘/u01/backup/rman/testdb/c-3532288055-20160227-00’;


Now Open the database

RMAN> alter database mount;



RMAN> catalog start with ‘/u01/backup/rman/testdb/backup’;

RMAN> catalog start with ‘/u01/backup/rman/testdb/archive’;


RMAN> run
{
allocate channel c1 device type disk;
SET NEWNAME FOR DATAFILE 1 TO ‘/u01’;
SET NEWNAME FOR DATAFILE 2 TO ‘/u02’;
SET NEWNAME FOR DATAFILE 3 TO ‘/u03’;
SET NEWNAME FOR DATAFILE 4 TO ‘/u04’;
restore database until sequence 6;
switch datafile all;
recover database until sequence 6;
}



Open the database with RESETLOGS UPGRADE.


[oracle@Tetsdby ~]$  sqlplus / as sysdba


SQL> alter database open resetlogs upgrade;


Step :4


Run the upgrade script.

SQL> SPOOL upgrade.log

SQL> @$ORACLE_HOME/rdbms/admin/catupgrd.sql

SQL> SPOOL off

Step :5

If 10g and 11g same platform no problem otherwise need to run the below script


SQL> SHUTDOWN IMMEDIATE

SQL> STARTUP UPGRADE

SQL> SPOOL migrate.log

SQL> @$ORACLE_HOME/rdbms/admin/utlmmig.sql

        SQL> SPOOL off
     
     
       SQL> SHUTDOWN IMMEDIATE
     
       SQL> STARTUP
     
     
Step :6

       SQL> @$ORACLE_HOME/rdbms/admin/utlu112s.sql
     
       The above scriop for pos-upgrade script
     
Step :7

SQL> @$ORACLE_HOME/rdbms/admin/utlrp.sql


The above script for  compile invalid objects