How to convert RAC to Race One Node

srvctl convert database -d <db_name> -c RACONENODE

Now instance processes will change to ora_smon_inst_#

When you try to relocate the instance you may fail with the following error:

PRCD-1222 : Online relocation of database rontest failed but database was restored to its original state
PRCD-1129 : Failed to start instance rontest_2 for database rontest
PRCR-1064 : Failed to start resource ora.rontest.db on node node3
CRS-5017: The resource action "ora.rontest.db start" encountered the following error:

ORA-1576: The instance inst_2 is not enabled

To avoid/solve this error, run the following:

1. Add logfiles for tread 2.

Alter database db_name add logfile tread 2 group # '+DATA' size x M;

Alter database enable public tread 2;

Alter database enable public instance 2;

srvctl relocate database -d dbname -n nodename

How to create sql tune profile

Starting from version 10g

You may need to use Sql Profile to affect a query that uses the wrong index,

So you can adjust the optimizer use the correct index.

Find the <SQL_ID> from v$sql:

select sql_id,sql_text

from v$sql

where sql_text like 'select somthing ... %';

Decide which index you want the query use.

Change the parameters in the section with a title TO CHANGE,

And run the script in Sql Plus

DECLARE

/****************** TO CHANGE ********************************/

   l_profile_name   VARCHAR2 (30) := 'PROFILE_<SQL_ID>';

   l_index_name     VARCHAR2 (30) := '<INDEX_NAME>';

   l_table_name     VARCHAR2 (30) := '<TABLE_NAME>';

   l_category       VARCHAR2 (30) := 'DEFAULT';

   l_sql_id         VARCHAR2 (30) := '<SQL_ID>';

/************************************************************/

   cl_sql_text      CLOB;

BEGIN

   SELECT sql_fulltext

     INTO cl_sql_text

     FROM v$sqlarea

    WHERE sql_id = l_sql_id;

   DBMS_SQLTUNE.import_sql_profile (

      sql_text   => cl_sql_text,

      profile    => sqlprof_attr (

                      q'[index(' || l_index_name || '@SEL$1, ' || l_table_name || ')]'),

      category   => l_category,

      name       => l_profile_name);

   DBMS_OUTPUT.put_line ('Profile created!');

EXCEPTION

   WHEN NO_DATA_FOUND

   THEN

      DBMS_OUTPUT.put_line (

         'ERROR: SQL_ID: ' || l_sql_id || ' does not exist in v$sqlarea.');

END;

To check if the Sql Profile created:

select * 

from dba_sql_profiles

where name like 'PROFILE_<SQL_ID>';

Now the query should use a defined index.

If you want to drop Sql Profile:

begin

  dbms_sqltune.drop_sql_profile( 'PROFILE_<SQL_ID>' );

end;

How to know how many rows there are in the block

In order to plan and / or improve the approaches to the table
many developers and database administrators are wondering how many rows of a table has a particular block.

Recently I came across this question, and the solution I found is:

SELECT COUNT(*),
               DBMS_ROWID.ROWID_RELATIVE_FNO(rowid) "FILE#", 
               DBMS_ROWID.ROWID_BLOCK_NUMBER(rowid) "BLOCK#"
FROM HR.PERSON
GROUP BY DBMS_ROWID.ROWID_RELATIVE_FNO(rowid), 
                    DBMS_ROWID.ROWID_BLOCK_NUMBER(rowid);

You can see that in this case there is a file number of the block.


But what happens when we try this query on IOT?

SELECT COUNT(*),
               DBMS_ROWID.ROWID_RELATIVE_FNO(rowid) "FILE#",
               DBMS_ROWID.ROWID_BLOCK_NUMBER(rowid) "BLOCK#"
FROM SCHEMA.IOT_TABLE
GROUP BY DBMS_ROWID.ROWID_RELATIVE_FNO(rowid),
                    DBMS_ROWID.ROWID_BLOCK_NUMBER(rowid);

ORA-06553: PLS-306: wrong number or types of arguments in call to 'ROWID_BLOCK_NUMBER'

In the case of IOT We will use a function that is not documented: sys_op_lbid

To use it we need to find the out the PK's object number of the IOT:

SELECT O.OBJECT_ID

FROM DBA_CONSTRAINTS C, DBA_OBJECTS O 

WHERE C.TABLE_NAME = '<IOT_TABLE>'

AND C.OWNER = '<OWNER>' 

AND C.CONSTRAINT_TYPE = 'P'

AND C.CONSTRAINT_NAME = O.OBJECT_NAME

AND C.OWNER = O.OWNER;

Now we can use

SELECT COUNT(*),

               DBMS_ROWID.ROWID_RELATIVE_FNO(sys_op_lbid(<

OBJECT_ID>

,'L',t.rowid)) "FILE#",  

               DBMS_ROWID.ROWID_BLOCK_NUMBER(sys_op_lbid( <

OBJECT_ID>

 ,'L',t.rowid)) "BLOCK#"

FROM  SCHEMA.IOT_TABLE t

GROUP BY DBMS_ROWID.ROWID_RELATIVE_FNO(sys_op_lbid( <

OBJECT_ID>

 ,'L',t.rowid)),  

                    DBMS_ROWID.ROWID_BLOCK_NUMBER(sys_op_lbid( <

OBJECT_ID>

 ,'L',t.rowid));

                      

We now have the ability to perform the following query and get the number of rowsin the block on PK of the IOT.
In the same way we can check the amount of all lines in the block of the index,
And even to know what information is found in the block.

Impersonating other users

Sometimes we need to connect with a specific user database
But we have no password.
There are two ways to do this oracle:

1. Change password by using the - password hash of its Value

    How to make it work:

    select password

    from sys.user$

    where name like 'SOCTT';

    This query will return some value that you have to keep.

    Now you can change SCOTT's password to what you want:

    Alter user scott identified by alex;

    Now you know the password and you can safely connect,

    After you connected do not forget to return the original password:

    Alter user scott identified by values '<The String from the query befor>';

2. Proxy User

    How to make it work:

    Create user alex identified by password;

    grant create session to alex;

    Alter user scott grant connect through alex;

   

    connect alex[scott]@db