Chequeo del parametro... db_recovery_file_dest_size

 How do I increase the limit for db_recovery_file_dest_size?

Answer:  Even though db_recovery_file_dest_size is 100%, deleting files from the directory was not the ultimate solution because I also had to update the RMAN catalog to remove the old redo log entries.

Also note errors/bugs in v$_flash_recovery_area.

The trick to increasing the limit for db_recovery_file_dest_size was to:

1 - Start the database, then mount the database (Do not open the database)

2 - Use RMAN to run a crosscheck on the archive logs and then update the RMAN catalog to delete the expired redo logs that I had manually deleted via the OS to free-up space in the disk filesystem directory.  See these notes on using the RMAN crosscheck command.

The following scripts will check space utilization for db_recovery_file_dest_size:

col name     format a32
col size_mb  format 999,999,999
col used_mb  format 999,999,999
col pct_used format 999

select
   name,
   ceil( space_limit / 1024 / 1024) size_mb,
   ceil( space_used / 1024 / 1024) used_mb,
   decode( nvl( space_used, 0),0, 0,
   ceil ( ( space_used / space_limit) * 100) ) pct_used
from
    v$recovery_file_dest
order by
   name desc;

*********************************

set lines 100
col name format a60

select
   name,
   floor(space_limit / 1024 / 1024) "Size MB",
   ceil(space_used / 1024 / 1024)   "Used MB"
from
   v$recovery_file_dest
order by
   name;

Restrict MODE

 When a database is started in restricted mode only users with restricted access can login to the database. The reason to start database in restrict is to restrict normal users to connect to the database. To switch the database back to normal mode, you would need to restart the database.

One can start the database in restricted mode by using the following option when starting up.

SQL> STARTUP RESTRICT;

shows database is in restricted mode

sojha@testdb> select logins from v$instance;

LOGINS
RESTRICTED

If you need to grant a user access to database when it’s in restricted mode, you can run the following SQL.

SQL> GRANT RESTRICTED SESSION TO scott;

Grant succeeded.

To revoke access restricted mode access
SQL> REVOKE RESTRICTED SESSION FROM scott;

How to find the users that have restricted session access:
find users who have been granted through the role (displays 1 level)
SELECT b.grantee, a.grantee || ‘ (Role)’ AS granted
FROM dba_sys_privs a, dba_role_privs b
WHERE a.privilege = ‘RESTRICTED SESSION’
AND a.grantee = b.granted_role
UNION
SELECT b.username, ‘User (Direct)’ -- find users who have given access not through role
FROM dba_sys_privs a, dba_users b
WHERE a.privilege = ‘RESTRICTED SESSION’
AND a.grantee = b.username;

GRANTEE GRANTED
SCOTT User (Direct)
SYS DBA (Role)
SYSTEM DBA (Role)

To change from restricted mode to unrestricted/active mode, which can switch without restarting the database.

SQL> alter system disable restricted session;

System altered.

SQL> select logins from v$instance;

LOGINS
ALLOWED

Also database can be placed in restricted mode by running the following DDL without restarting the database.

SQL> alter system enable restricted session;

System altered.

SQL> select logins from v$instance;

LOGINS
RESTRICTED

Note: There seems to be an issue when disabling restricted mode in 10.2.0.1, you can try to shutdown from the same session where restricted session was disable was executed from.

SQL> shutdown immediate;
ORA-01097: cannot shutdown while in a transaction commit or rollback first

You can work around this by executing shutdown from a new session or executing commit/rollback from the session where restricted session was disabled.

Generando reportes AWR, ASH y ADDM

 En algún momento durante el monitoreo de nuestra base de datos Oracle será necesario obtener información detallada en reportes, estos podrían ser los de AWR, AHR y/o ADDM. Este post se enfocará en realizar las mismas utilizando scripts, una manera alternativa a lo que nos ofrece con la interfaz gráfica de Enterprise Manager. A continuación se detalla los pasos para obtener los reportes.

A) Reporte AWR (awrrpt.sql)

El utilitario de informes AWR proporciona una visión general del rendimiento de bases de datos en un determinado tiempo. En esencia, calcula la variación de la actividad de la base de datos en el intervalo de tiempo elegido. el script "awrrpt.sql" se encuentra en el directorio $ORACLE_HOME/RDBMS/admin.

La salida del archivo es ubicada en el directorio actual. Para una mejor comprensión de dicho informe seleccionar el formato HTML.

1. Ingresamos al directorio donde se generará el Informe AWR.

bash-3.2$ cd /u01/informes/
bash-3.2$ pwd
/u01/informes
bash-3.2$ ls
bash-3.2$

2 Nos conectamos vía sqlplus con usuario de provilegios de DBA.

bash-3.2$ sqlplus pticona

SQL*Plus: Release 11.2.0.3.0 Production on Sun Jun 22 23:58:15 2014

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

Enter password:

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

SQL>

3. Ejecutamos awrrpt.sql y nos aparecerá los datos actuales de la instancia.

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

Current Instance
~~~~~~~~~~~~~~~~

   DB Id    DB Name      Inst Num Instance
----------- ------------ -------- ------------
  798387748 PRMY                1 prmy

3.1. Elegimos la opción para que el informe se genere en formato "html".

Specify the Report Type
~~~~~~~~~~~~~~~~~~~~~~~
Would you like an HTML report, or a plain text report?
Enter 'html' for an HTML report, or 'text' for plain text
Defaults to 'html'
Enter value for report_type:html

Type Specified:  html


Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

   DB Id     Inst Num DB Name      Instance     Host
------------ -------- ------------ ------------ ------------
* 798387748         1 PRMY         prmy         primario

Using  798387748 for database Id
Using          1 for instance number

3.2. Para encontrar todos los AWR de la base de datos solo damos enter sin escribir nada.

Specify the number of days of snapshots to choose from
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Entering the number of days (n) will result in the most recent
(n) days of snapshots being listed.  Pressing  without
specifying a number lists all completed snapshots.

Enter value for num_days:

Listing all Completed Snapshots

                                                        Snap
Instance     DB Name        Snap Id    Snap Started    Level
------------ ------------ --------- ------------------ -----
prmy         PRMY                 1 18 Jun 2014 01:30      1
                                  2 18 Jun 2014 11:13      1
                                  3 18 Jun 2014 12:00      1
                                  4 22 Jun 2014 15:04      1
                                  5 22 Jun 2014 16:00      1
                                  6 22 Jun 2014 17:00      1
                                  7 22 Jun 2014 18:00      1
                                  8 22 Jun 2014 19:00      1
                                  9 22 Jun 2014 20:00      1
                                 10 22 Jun 2014 21:00      1
                                 11 22 Jun 2014 22:00      1
                                 12 22 Jun 2014 23:00      1
                                 13 23 Jun 2014 00:01      1

3.3. Con el listado desplegado, elegimos el intervalo de tiempo.

Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 10
Begin Snapshot Id specified: 10

Enter value for end_snap: 11
End   Snapshot Id specified: 11

3.4. Asignamos el nombre para el reporte.

Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is awrrpt_1_10_11.html.  To use this name,
press  to continue, otherwise enter an alternative.

Enter value for report_name: reporte_awr.html
.
.
Report written to reporte_awr.html

3.5. Salimos de sqlplus.

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
bash-3.2$

4 Finalmente verificamos el archivo creado.

bash-3.2$ pwd
/u01/informes
bash-3.2$ ls
reporte_awr.html
bash-3.2$

5 Para realizar una comparativa de reportes AWR, utilizar el siguiente script.

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

B) Reporte ASH (ashrpt.sql)

El reporte ASH es útil para determinar la cantidad de sesiones activas, lo que estaban haciendo, y que sentencias SQL son las más activas durante el período de tiempo seleccionado. Especialmente es muy útil para el análisis de problemas de rendimiento transitoria. El script "ashrpt.sql" se encuentra en el directorio $ORACLE_HOME/RDBMS/admin.

La salida del archivo es ubicada en el directorio actual. Para una mejor comprension de dicho informe seleccionar el formato HTML. 

1. Ingresamos al directorio donde se generará el Informe ASH.

bash-3.2$ cd /u01/informes/
bash-3.2$ pwd
/u01/informes
bash-3.2$ ls
bash-3.2$

2 Nos conectamos vía sqlplus con usuario de provilegios de DBA.

bash-3.2$ sqlplus pticona

SQL*Plus: Release 11.2.0.3.0 Production on Sun Jun 22 23:58:15 2014

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

Enter password:

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

SQL>

3. Ejecutamos ashrpt.sql y nos aparecerá los datos actuales de la instancia.

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

Current Instance
~~~~~~~~~~~~~~~~

   DB Id    DB Name      Inst Num Instance
----------- ------------ -------- ------------
  798387748 PRMY                1 prmy

3.1. Elegimos la opción para que el informe se genere en formato "html".

Specify the Report Type
~~~~~~~~~~~~~~~~~~~~~~~
Enter 'html' for an HTML report, or 'text' for plain text
Defaults to 'html'
Enter value for report_type: html

Type Specified:  html

Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

   DB Id     Inst Num DB Name      Instance     Host
------------ -------- ------------ ------------ ------------
* 798387748         1 PRMY         prmy         primario

Defaults to current database

Using database id: 798387748

Enter instance numbers. Enter 'ALL' for all instances in a
RAC cluster or explicitly specify list of instances (e.g., 1,2,3).
Defaults to current instance.

Using instance number(s): 1

3.2. Ingresamos la hora de inicio y el intervalo de tiempo de duracion para el reporte ASH.

ASH Samples in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Oldest ASH sample available:  18-Jun-14 01:19:24   [   7150 mins in the past]
Latest ASH sample available:  23-Jun-14 00:29:06   [      0 mins in the past]


Specify the timeframe to generate the ASH report
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter begin time for report:

--    Valid input formats:
--      To specify absolute begin time:
--        [MM/DD[/YY]] HH24:MI[:SS]
--        Examples: 02/23/03 14:30:15
--                  02/23 14:30:15
--                  14:30:15
--                  14:30
--      To specify relative begin time: (start with '-' sign)
--        -[HH24:]MI
--        Examples: -1:15  (SYSDATE - 1 Hr 15 Mins)
--                  -25    (SYSDATE - 25 Mins)

Defaults to -15 mins
Enter value for begin_time: 06/22/14 21:05
Report begin time specified: 06/22/14 21:05

Enter duration in minutes starting from begin time:
Defaults to SYSDATE - begin_time
Press Enter to analyze till current time
Enter value for duration: 60
Report duration specified:   60

Using 22-Jun-14 21:05:00 as report begin time
Using 22-Jun-14 22:05:00 as report end time

3.3. Asignamos el nombre para el reporte ASH.

Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is ashrpt_1_0622_2205.html.  To use this name,
press  to continue, otherwise enter an alternative.
Enter value for report_name: reporte_ash.html
.
.
Report written to reporte_ash.html

3.4. Salimos de sqlplus.

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
bash-3.2$

4 Finalmente verificamos el archivo creado.

bash-3.2$ pwd
/u01/informes
bash-3.2$ ls
reporte_ash.html
bash-3.2$

C) Reporte ADDM (addmrpt.sql)
El utilitario de ADDM crea un informe con las conclusiones de rendimiento de base de datos. El script "addmrpt.sql" se encuentra en el directorio $ORACLE_HOME/RDBMS/admin. La salida del archivo se ubica en el directorio actual y es enformato de archivo de texto.
1. Ingresamos al directorio donde se generará el reporte ADDM.

bash-3.2$ cd /u01/informes/
bash-3.2$ pwd
/u01/informes
bash-3.2$ ls
bash-3.2$

2 Nos conectamos vía sqlplus con usuario de provilegios de DBA.

bash-3.2$ sqlplus pticona

SQL*Plus: Release 11.2.0.3.0 Production on Sun Jun 22 23:58:15 2014

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

Enter password:

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

SQL>

3. Ejecutamos addmrpt.sql y nos aparecerá los datos actuales de la instancia.

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

Current Instance
~~~~~~~~~~~~~~~~

   DB Id    DB Name      Inst Num Instance
----------- ------------ -------- ------------
  798387748 PRMY                1 prmy


Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

   DB Id     Inst Num DB Name      Instance     Host
------------ -------- ------------ ------------ ------------
* 798387748         1 PRMY         prmy         primario

Using  798387748 for database Id
Using          1 for instance number


Specify the number of days of snapshots to choose from
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Entering the number of days (n) will result in the most recent
(n) days of snapshots being listed.  Pressing  without
specifying a number lists all completed snapshots.



Listing the last 3 days of Completed Snapshots

                                                        Snap
Instance     DB Name        Snap Id    Snap Started    Level
------------ ------------ --------- ------------------ -----
prmy         PRMY                 4 22 Jun 2014 15:04      1
                                  5 22 Jun 2014 16:00      1
                                  6 22 Jun 2014 17:00      1
                                  7 22 Jun 2014 18:00      1
                                  8 22 Jun 2014 19:00      1
                                  9 22 Jun 2014 20:00      1
                                 10 22 Jun 2014 21:00      1
                                 11 22 Jun 2014 22:00      1
                                 12 22 Jun 2014 23:00      1
                                 13 23 Jun 2014 00:01      1

3.1. Elegimos el intervalo de snapshots para el reporte ADDM.

Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 10
Begin Snapshot Id specified: 10

Enter value for end_snap: 11
End   Snapshot Id specified: 11

3.2. Asignamos el nombre para el reporte.

Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is addmrpt_1_10_11.txt.  To use this name,
press  to continue, otherwise enter an alternative.

Enter value for report_name: reporte_addm.txt
.
.
.
The database's maintenance windows were active during 100% of the analysis
period.

End of Report
Report written to reporte_addm.txt
SQL>

3.3. Salimos de sqlplus.

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
bash-3.2$

4 Finalmente verificamos el archivo creado.

bash-3.2$ pwd
/u01/informes
bash-3.2$ ls
reporte_addm.txt
bash-3.2$

Upgrading the Recovery Catalog

 To upgrade the recovery catalog:

1. To install the new recovery catalog schema, the recovery catalog user must have TYPE privilege:

   sqlplus> connect sys/oracle@catdb as sysdba;
   sqlplus> grant TYPE to rman;
   
   

2. Use RMAN to connect to the target and recovery catalog databases. For example, enter:

   % rman TARGET / CATALOG rman/cat@catdb
   
   connected to recovery catalog database 
   PL/SQL package rcat.DBMS_RCVCAT version 08.00.04 in RCVCAT database 
   is too old
   
   

3. Issue the UPGRADE CATALOG command:

   UPGRADE CATALOG;
   
   recovery catalog owner is rman 
   enter UPGRADE CATALOG command again to confirm catalog upgrade 
   
   

4. Enter the UPDATE CATALOG command again to confirm:

   UPGRADE CATALOG;
   
   recovery catalog upgraded to version 09.02.00
   DBMS_RCVMAN package upgraded to version 09.02.00
   DBMS_RCVCAT package upgraded to version 09.02.00

Cambiar a modo restricted session en Oracle

 

1. Introducción

Que una base de datos oracle esté en restricted session significa que sólo los usuarios con CREATE SESSION y RESTRICTED SESSION system privileges ( privilegios de sistema ) pueden conectar con la base de datos.

Esto puede ser bueno, cuando queramos por ejemplo,que sólo un usuario concreto debido a una actualización de la base de datos, pueda conectar con esta.

2. Activar modo restricted session:

Para activar el modo restricted de una base de datos realizamos los siguiente pasos:

SQL> ALTER SYSTEM ENABLE RESTRICTED SESSION;

Cuando la base de datos está en este modo, si un usuario que no tenga el privilegio RESTRICTED SESSION no podrá conectar con la base de datos.

3. Desactivar el modo restricted session

SQL> ALTER SYSTEM DISABLE RESTRICTED SESSION;

4. Activar el modo restricted session al levantar la base de datos

En caso de que la base de datos no esté levantada y se quiera levantar en modo RESTRICTED SESSION habrá que hacerlo de la siguiente forma:

SQL> STARTUP RESTRICT;