Tag Archives: Oracle

Find LOB object parent table

Some times you find that there are some BIG lob segments and you don’t know which table they belong to…

Here’s the solution:

SELECT TABLE_NAME, column_name
FROM   user_lobs
WHERE  segment_name = (SELECT object_name
                       FROM   user_objects
                       WHERE  object_name = UPPER('&object_name')
                       AND    object_type = 'LOB');

in MacLochlainns Weblog

I hope it was useful!!!
Cheers

Recover GAP on Logical Standby with errors on apply

  • Verify logstandby background processes
SELECT PID, TYPE, STATUS, HIGH_SCN FROM V$LOGSTDBY;
  • Start logstandby apply processes
ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;
  • If the processes stop, verify the logstandby events and generate the script to skip the transaction if necessary

NOTE: If it’s your production environment, please check with application development team the transactions with error.

set lines 200 pages 200
col status for a30
col script for a50
SELECT to_char(EVENT_TIME,'dd-mm-yyyy hh24:mi:ss'), XIDUSN, XIDSLT, XIDSQN, STATUS, STATUS_CODE,
'EXEC DBMS_LOGSTDBY.SKIP_TRANSACTION ('||XIDUSN||','||XIDSLT||','||XIDSQN||');' "SCRIPT"
FROM DBA_LOGSTDBY_EVENTS WHERE EVENT_TIME = (SELECT MAX(EVENT_TIME) FROM DBA_LOGSTDBY_EVENTS);
  • Skip Transaction
EXEC DBMS_LOGSTDBY.SKIP_TRANSACTION (XIDUSN=>10,XIDSLT=>40,XIDSQN=>36784);

The script above is generated on logstandby events query.

I hope it was useful!!!
Cheers

Recriar a FRA (Fast Recovery Area)

Recriar a FRA (Fast Recovery Area)

 Links de apoio:

 

Criação nova FRA

Basta criarmos a nova FRA e já podemos passar a lá por os redo log files e archive log files. Entretanto os controlfiles requerem restart da base de dados (toda).

alter system set db_recovery_file_dest_size = '50G' scope=both sid='*';

System altered.

SQL> alter system set db_recovery_file_dest = '+FRADG' scope=both sid='*';

System altered.

 

Passagem ControlFiles para FRA

Normalmente existe mais de um controlfile por motivos de segurança a serem escritos ao mesmo tempo pela BD, por isso os passos para mover o controlfile para a nova FRA são:

 

1) Passar a ter um controlfile somente fora da FRA antiga (em um datagroup de dados, por exemplo).

 

alter system set control_files = +FCHFRADG/SIBSP13/CONTROLFILE/control02.ctl' scope=spfile sid='*';

Neste passo temos de parar a base de dados toda e trabalharmos somente em uma instância:

 

srvctl stop database -d <service name>

Arrancamos numa instância somente e passamos a adicionar agora o novo FRA como uma copia do controlfile, mas não especificamos o path completo, só o nome do FRA (usado no db_recovery_file_dest). Outro restart da instância e deixamos a mesma em modo mount.

startup

alter system set control_files = '+FCHFRADG/SIBSP13/CONTROLFILE/control02.ctl', '+FCHFRADG' scope=spfile sid='*';

shutdown immediate

startup nomount

Passamos para o Rman para realizarmos uma cópia fidedigna do novo controlfile para a nova FRA e não especificamos mais uma nova o path.

 

rman nocatalog
connect target /

restore controlfile from '+FCHFRADG/SIBSP13/CONTROLFILE/control02.ctl';

Podemos voltar ao SQLPLUS e montar e abrir a instância:

 

alter database mount;

alter database open;

Vefica-se os novos controlfiles criados e se eles estão mesmo na FRA (IS_RECOVERY_DEST_FILE):

select name, is_recovery_dest_file as is_recovery from gv$controlfile;

NAME                                                                                                 IS_RECOVERY       
---------------------------------------------------------------------------------------------------- ----------------- 
+FDATDG/sibsp11/controlfile/current.280.784135039                                                    NO                
+RECODG/sibsp11/controlfile/current.272.784135039                                                    YES               
+FDATDG/sibsp11/controlfile/current.280.784135039                                                    NO                
+RECODG/sibsp11/controlfile/current.272.784135039                                                    YES               

4 row(s) fetched

Agora aproveitamos para deixar o Rman a executar automaticamente o backup do controlfile a medida que ele é alterado:

rman nocatalog
rman> connect target /
CONFIGURE CONTROLFILE AUTOBACKUP ON;

Pode ser que seja preciso executar o seguinte statement para direccionar o controle file auto backup para a FRA.

CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK CLEAR;

Neste ponto, estamos prontos para passarmos os archive logs e os redo logs.

Passagem Redo e Arhive Log Files

Os archivelogs basta que alteramos o seguinte parâmetro:

alter system set LOG_ARCHIVE_DEST_1 = 'LOCATION = USE_DB_RECOVERY_FILE_DEST' scope=both;

Os redo log files devem ser passados segundo o procedimento já descrito nesta pagina: http://blogdaprima.com/2012/redimensionar-red-logs-11gr2/

 

Block Tracking

Não devemos esquecer de ligar o block tracking (para backups mais rápidos e etc) e apontar a nova configuração para a nova FRA.

 

alter database enable block change tracking using file '+FCHFRADG';

e verificando o status na view:

 

select * from v$block_change_tracking;

STATUS           FILENAME                                                                                             BYTES                                        
---------------- ---------------------------------------------------------------------------------------------------- -------------------------------------------- 
ENABLED          +FCHFRADG/sibsp13/changetracking/ctf.292.792003881                                                                                       11599872

Confirmação FRA em uso

select file_type, percent_space_used, number_of_files from V$FLASH_RECOVERY_AREA_USAGE

select file_type, percent_space_used, number_of_files from V$FLASH_RECOVERY_AREA_USAGE

FILE_TYPE                  PERCENT_SPACE_USED                           NUMBER_OF_FILES                              
-------------------------- -------------------------------------------- -------------------------------------------- 
CONTROL FILE                                                       0,24                                            1 
REDO LOG                                                           3,56                                            4 
ARCHIVED LOG                                                       1,12                                            2 
BACKUP PIECE                                                       1,05                                            4 
IMAGE COPY                                                            0                                            0 
FLASHBACK LOG                                                         0                                            0 
FOREIGN ARCHIVED LOG                                                  0                                            0 

7 row(s) fetched

 

Oracle EM DBConsole don’t start due to Error starting ORMI server

If your Enterprise Manager Console won’t start due to “Error starting ORMI server … port 5520. The socket name is already in use”, here’s the solution:

  • Shutdown Enterprise Manager Console.
emctl stop dbconsole
  • Check if there are any processes with name like “oc4j”. If there are, kill them.
ps -ef | grep oc4j
kill -9 ....
  • Open Enterprise Manager Console.
emctl start dbconsole

in Bünyamin Balaban – Oracle Blog

I hope it was useful!!!
Cheers

Oracle RAC 11g R2 on AIX 7.1 installation issue on irman relink

If you are installing Oracle RAC 11g R2 on AIX 7.1, you may have the following error:

You press retry and the installation finish without any more errors. This error will occour on both CRS installation and Database installation.

After that I had a ORA-03113: end-of-file on communication channel on dbca database creation.

Checking installation log I found the following error:

0711-780 SEVERE ERROR: Symbol .ksmpfpva

It can happen during installation, re-linking or patching

If the relink error is ignored, the following error is reported when shutting down database:

ORA-03113: end-of-file on communication channel

In instance alert.log:

ORA-07445 [ksmpclrpga()+23248]

Call stack from trace file:

skdstdst ksedst1 ksedst dbkedDefDump ksedmp ssexhd 47dc opidcl opidrv sou2o opimai_real ssthrdmain main start

This problem is also known to generate large core files at the time of hitting ORA-7445 [ksmpclrpga]:
A core file is likely generated in $ORACLE_HOME/dbs

Cause

AIX OS issue.

Solution

The following AIX fix needs to be applied:

For AIX 7.1 apply IFIX IV09541: https://www-304.ibm.com/support/docview.wss?uid=isg1IV09541

At the time of this writing, IBM has pre-built the iFixes and uploaded to:

ftp://public.dhe.ibm.com/aix/efixes/

where the APAR numbers are:

5.3 TL11 – iv10538
5.3 TL12 – iv11158
6.1 TL4 – iv11167
6.1 TL5 – iv10576
6.1 TL6 – iv10539
6.1 TL7 – iv09580
7.1 TL0 – unaffected
7.1 TL1 – iv09541

Once the OS patch is applied, relink oracle binary so it will be effective.

in Oracle Support

This worked for me…

I hope it was useful!!!
Cheers

Configure NTP on AIX 7.1 for Oracle RAC

  1. Verify that you have a server suitable for synchronization. Enter:
  1. # ntpdate -d ip.address.of.server

    The offset must be less than 1000 seconds for xntpd to synch. If the offset is greater than 1000 seconds, change the time manually on the client and run the ntpdate -d again.

    If you get the message, "no server suitable for synchronization found", verify xntpd is running on the server (see above) and that no firewalls are blocking port 123.

  2. Specify your xntp server in /etc/ntp.conf, enter:
  1. # vi /etc/ntp.conf

    (Comment out the “broadcastclient” line and add server ip.address.of.server prefer.)Leave the driftfile and tracefile at their defaults.

  2. Start the xntpd daemon:
  1. # startsrc -s xntpd

    (Use the -x flag if it is appropriate for your environment.)

  1. Uncomment xntpd from /etc/rc.tcpip so it will start on a reboot.
  1. # vi /etc/rc.tcpip

    Uncomment the following line:

    start /usr/sbin/xntpd "$src_running" "-x"

    NOTE: Use the -x flag to prevent the clock from changing in a negative direction.

  2. Verify that the client is synched.
  1. # lssrc -ls xntpd

    NOTE: Sys peer should display the IP address or name of your xntp server. This process may take up to 6 minutes.

in IBM Support

ATENTION!!!

Check your Time Zone settings. We had to change to our Time Zone (Lisbon).

The default value is CST6DST. Run the commad:

smit chtz_user

and change the values to GMT0WET.

Here’s a list of the values you can use: http://pic.dhe.ibm.com/infocenter/aix/v6r1/index.jsp?topic=%2Fcom.ibm.aix.files%2Fdoc%2Faixfiles%2Fenvironment.htm

I hope it was useful!!!
Cheers

Install / Configure utl_mail and utl_smtp on Oracle 11g R2

Install utl_mail/utl_smtp

[host@oracle]$ cd $ORACLE_HOME/rdbms/admin
[host@oracle]$sqlplus / as sysdba
SQL> @utlmail
SQL> @utlsmtp
SQL> @prvtmail.plb
SQL> GRANT EXECUTE ON utl_mail TO PUBLIC;
SQL> GRANT EXECUTE ON utl_smtp TO PUBLIC;
SQL> alter system set smtp_out_server='mail.server.com' scope=both;

ACL: Access Control List

in White Horses Blog

Create ACL and privileges

Now first create an ACL as SYS (or any other user with DBMS_NETWORK_ACL_ADMIN execute granted), this will hold the privileges. You can add as many privileges as you like to this file, but I would recommend to split privileges in the ACL to specific tasks or users. You must create an ACL with at least one privilege, so lets start with the ‘connect’ privilege for user SCOTT, (also a role can be added as principal):

begin
  dbms_network_acl_admin.create_acl (
    acl         => 'utl_mail.xml',
    description => 'Allow mail to be send',
    principal   => 'SCOTT',
    is_grant    => TRUE,
    privilege   => 'connect'
    );
    commit;
end;

Add Privilege

Great, now that the ACL is created, you can add more privileges like the ‘resolve’ privilege:

begin
  dbms_network_acl_admin.add_privilege (
  acl       => 'utl_mail.xml',
  principal => 'SCOTT',
  is_grant  => TRUE,
  privilege => 'resolve'
  );
  commit;
end;

Assign ACL

Cool, you granted SCOTT to connect and resolve, but you have not defined to which resources he is allowed to connect:

begin
  dbms_network_acl_admin.assign_acl(
  acl  => 'utl_mail.xml',
  host => 'smtp server host name or address'
  );
  commit;
end;

I hope it was useful!!!
Cheers

Oracle 11gR2 Enabling and Disabling Database Options After Installation

When you install Oracle Database, some options are enabled and others are disabled. To enable or disable a particular database feature for an Oracle home, shut down the database and use the chopt tool.

The chopt tool is a command-line utility that is located in the directory $ORACLE_HOME/bin. It uses the following syntax, where db_option is the option whose status you want to modify:

chopt [enable | disable] db_option

The possible values for db_option are:

Value Description
dm Oracle Data Mining Database Files
dv Oracle Database Vault
lbac Oracle Label Security
olap Oracle OLAP
partitioning Oracle Partitioning
rat Oracle Real Application Testing
ode_net Oracle Database Extensions for .NET 1.x
ode_net_2 Oracle Database Extensions for .NET 2.0

In this example, the chopt tool is used to enable Oracle Label Security in the database oradb1:

$ cd $ORACLE_HOME/bin
$ srvctl stop database -d oradb1
$ chopt enable lbac
$ srvctl start database -d oradb1

in Oracle 11g R2 Documentation

I hope it was useful!!!
Cheers

Disable Recycle Bin Oracle 11gR2

To disable Recycle Bin on Oracle 11gR2 database you must change the parameter and then restart the instance:

[oracle@host]> sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Fri Sep 16 15:52:56 2011

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, Automatic Storage Management, Oracle Label Security, OLAP,
Data Mining and Oracle Database Vault options

SQL> alter system set recyclebin=OFF scope=spfile;

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup
ORACLE instance started.

Total System Global Area 3206836224 bytes
Fixed Size                  2211064 bytes
Variable Size            1795162888 bytes
Database Buffers         1392508928 bytes
Redo Buffers               16953344 bytes
Database mounted.
Database opened.

SQL> show parameter recyclebin

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
recyclebin                           string      OFF

I hope it was useful!!!
Cheers

Error remote connection to SCAN after Oracle RAC 11gR2 installation

If after the installation of Oracle RAC 11gR2, when you try to connect remotely you have the following error:

ORA-12545: Connect failed because target host or object does not exist

connecting on both nodes of RAC you have the following error:

ORA-12504: TNS:listener was not given the SERVICE_NAME in CONNECT_DATA

and both LISTENER and LISTENER_SCAN1 are ok.

Then look at database parameter local_listener.

If it’s pointing to one of vip name’s (NODE1-vip or NODE2-vip), change it to RAC name (RAC01)

SQL> show parameter local_listener

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
local_listener                       string      (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=NODE1-vip)(PORT=1521))))

SQL> alter system set local_listener='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=RAC01)(PORT=1521))))';

System altered.

SQL> alter system register;

System altered.

I hope it was useful!!!
Cheers

Optimization WordPress Plugins & Solutions by W3 EDGE