Convert Primary Database To Standby Database

PRIMARY DATABASE TO STANDBY DATABASE

database version: 11.2.0.1

First of all, you should remember that a standby database cannot be activated and then returned to standby recovery mode.

*Always the first step is to make a backup of database.

1) verify database role:

2) Make a standby controlfile backup:

3) Open database in "NOMOUNT" state:

4) restore standby controlfile

5) mount database in standby ("alter database mount standby database;") and verify that we have a physical standby database.

DONE!! Good Job :)

ORA-06512: at "SYS.UTL_FILE"

oracle version : RAC 11.2.0.3 (2 nodes called fran1 and fran2)

OS: RHEL 6

I was trying to do a export of my schema and import into another database when sudenly:

[oracle@fran ~]expdp system/fran@fran1 directory=DUMP_DIR dumpfile=full_01112013.dmp logfile=full_01112013.log schemas=fran;

Export: Release 11.2.0.3.0 - 64bit Production on Wensday, 11 December, 2013 12:05:2 0

Copyright (c) 2003, 2007, Oracle. All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0  - 64bit Production

With the Partitioning, Real Application Clusters, OLAP, Data Mining

and Real Application Testing options

ORA-39002: invalid operation

ORA-39070: Unable to open the log file.

ORA-29283: invalid file operation

ORA-06512: at "SYS.UTL_FILE", line 536

ORA-29283: invalid file operation

hmm? why?

I checked the directory exists and It is writable:

SQL>  select directory_name, directory_path from dba_directories where directory_name='DUMP_DIR';

DIRECTORY_NAME                      DIRECTORY_PATH

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

DUMP_DIR                                   /backup/fran/

SQL> exit

Disconnected from Oracle Database 11g Release 11.1.0.6.0 - Production

[oracle@fran~]$ cd /backup/

[oracle@fran backup]$ ls -l

total 40

drwx------ 2 root   root     16384 Mar  9  2009 lost+found

drwxr-xr-x 2 oracle oinstall  4096 Mar 21 11:15 fran

Directory exists and are writable. User is "system" so it has the neccesary privileges to do the export (if you are trying with another user, check the privileges it has and be sure that it has "read" and "write" on that "directory")

So I tried again but this time without connect string:

[oracle@fran ~]expdp system/fran directory=DUMP_DIR dumpfile=full_01112013.dmp logfile=full_01112013.log schemas=fran;

Export: Release 11.2.0.3.0 - 64bit Production on Wensday, 11 December, 2013 12:05:2 0
Copyright (c) 2003, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01": system/******** directory=DUMP_DIR dumpfile=full_01112013.dmp logfile= full_01112013.log schemas=fran
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 3.25 MB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/FUNCTION/FUNCTION
Processing object type SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTION
Processing object type SCHEMA_EXPORT/POST_SCHEMA/PROCACT_SCHEMA
. . exported "fran"."test1" 1.122 MB 1156 rows
. . exported "fran"."test2" 131.1 KB 3980 rows
. . exported "fran"."prueba" 7.710 KB 1 rows
. . exported "fran"."contacts" 6.054 KB 5 rows
. . exported "fran"."upgrading" 5.5 KB 2 rows
. . exported "fran"."partitioned" 5.492 KB 1 rows
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
/opt/xxxxxxxxxx/3.0/admin/dbbackup/dump/xxxxxxxxxxx.dmp
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at 11:15:41

successfully completed without errors.What was the reason then?

The reason can be because the connect string (TNS Name) is a load balancing connect string (remember I am in RAC)and whenever you try to use the connect string with expdp/impdp, it goes to the other node where the directory information is not available, or the directory might be a local folder which is not a shared one.

Hope it helps!!

How to enable Oracle Label Security

This example is for Oracle Database 12c already installed, but you can execute on previous database version (from 9i and above)

Open a DBCA console and select on Configure Database Options:

Select your database and click on next

In that page you can install OLS selecting it and clicking on next and later on finish (if you can't click on it is  like in the photo is because you have installed it when you installed database)

Let finish the installation and open again DBCA and this time click on "Manage connection database":

Configure Connection Database:

Select your database:

Select your Conecction Database:

Enable "Configure Label Security"

Next and Finish and you got it installed.


Now, you must unlock LBACSYS user and grant "select any dictionary" on It, so connect to sqlplus as SYS user and execute (connect to your database, NOT to connection database):

SQL> ALTER USER LBACSYS identified by LBCASYS;
SQL> ALTER USER LBACSYS account UNLOCK;
SQL> GRANT SELECT ANY DICTIONARY to LBACSYS CONTAINER=all;

and run catols.sql:
SQL>@$ORACLE_HOME/rdbms/admin/catols.sql


Verify OLS installation (LBACSYS must have objects and all must be valid):

Now you have OLS installed and enabled. Now you can create OLS policy.

ASM block corruption with ORA-600

The last week i got some problems with the database because the server was shutdown by an outage.

I tried to startup manually when server was operating again.

SQL> startup
ORACLE instance started.

Total System Global Area  849530880 bytes
Fixed Size                  1339824 bytes
Variable Size             528485968 bytes
Database Buffers          314572800 bytes
Redo Buffers                5132288 bytes
ORA-00205: error in identifying control file, check alert log for more info

so, let's check alertFran.log:

ALTER DATABASE   MOUNT
ORA-00210: cannot open the specified control file
ORA-00202: control file: '+FRA/fran/controlfile/control01.ctl'
ORA-27048: skgfifi: file header information is invalid
ORA-205 signalled during: ALTER DATABASE   MOUNT...

Checker run found 1 new persistent data failures
ORA-205 signalled during: ALTER DATABASE   MOUNT...

controlfile01.ctl is invalid so let's repair them. Two ways:

1) you have multiplixed controlfile:

$export ORACLE_SID=+ASM
$export ORACLE_HOME=/u01/app/grid
$sqlplus / as sysasm
SQL> alter database nomount;
ORACLE instance started.

Total System Global Area  849530880 bytes
Fixed Size                  1339824 bytes
Variable Size             528485968 bytes
Database Buffers          314572800 bytes
Redo Buffers                5132288 bytes
SQL> exit
$asmcmd
ASMCMD> rm -rf '<path_invalid_controlfile>;
ASMCMD> cp '<path_valid_controlfile>' '<path_invalid_controlfile>;

2) You haven't multiplexed controlfile, create manually one:
SQL> CREATE CONTROLFILE
     SET DATABASE Fran
     LOGFILE GROUP 1 ('+FRA/fran/onlinelog/group_1.A',
                      '+FRAN_DATA/fran/onlinelog/group_1.B'),
             GROUP 2 ('+FRA/fran/onlinelog/group_2.A',
                      '+FRAN_DATA/fran/onlinelog/group_2.B'),
             GROUP 3 ('+FRA/fran/onlinelog/group_3.A',
                      '+FRAN_DATA/fran/onlinelog/group_3.B'),
             GROUP 4 ('+FRA/fran/onlinelog/group_4.A'),
                      '+FRAN_DATA/fran/onlinelog/group_4.B')
     RESETLOGS                
     DATAFILE '+FRAN_DATA/fran/datafiles/system01.dbf' SIZE 150M,
              '+FRAN_DATA/fran/datafiles/rbs01.dbs' SIZE 512M,
              '+FRAN_DATA/fran/datafiles/temp01.dbs' SIZE 2G,
              '+FRAN_DATA/fran/datafiles/sysaux01.dbf' SIZE 30M,
              '+FRAN_DATA/fran/datafiles/user01.dbf' SIZE 100M,
              '+FRAN_DATA/fran/datafiles/test01.dbf' SIZE 512,
              '+FRAN_DATA/fran/datafiles/fran01.dbf' SIZE 1G;

**if you modified or create controlfile remember that you must modify the new path at pfile. if you create pfile from spfile, open pfile and you see something like this:
**control_files = ('+FRAN_DATA/fran/controlfile/control01.ctl','+FRA/fran/controlfile/control02.ctl');
**modify manually with vi or textpad (depends OS) changing its paths
**control_files = ('new_path/control01.ctl','new_path/control02.ctl');
**SQL> STARTUP PFILE='path_pfile';
**SQL> create spfile from pfile;

we can now try to open database.

$sqlplus / as sysdba
SQL> ALTER DATABASE OPEN;


**It should open without problem, but like my issue is different i got a new error

ORA-00338: log 3 of thread 2 is more recent than control file
ORA-00312: online log 3 thread 2: '+FRAN_DATA/Fran/onlinelog/group_3.A'
ORA-00338: log 3 of thread 2 is more recent than control file
ORA-00312: online log 3 thread 2: '+FRA/Fran/onlinelog/group_3.B'

the solution was restore/recover database from last backup until 10 minutes before the outage:
RMAN> SHUTDOWN IMMEDIATE;
RMAN> STARTUP MOUNT;
RMAN> RUN{
  ALLOCATE CHANNEL c1 FORMAT '<path_of_backup>';
  set until time "to_date('2013-11-01:11:27:00', 'yyyy-mm-dd:hh24:mi:ss')";
  restore database;
  recover database;
    release channel c1;
 }

after restore recover finished succesfully i got a new error when we opened database (ALTER DATABASE OPEN RESETLOGS)

ORA-600 : internal error code, arguments: [krsi_al_hdr_update.15], [4294967295], [], [], [], [], [], [], [], [], [], []

this error are serious, we searched by this arguments at ORA-600 aplication of MOS (My Oracle Support) but no luck....

searching at alert.log from asm instance we found the error:

ORA-600 : internal error code, arguments: [krsi_al_hdr_update.15], [4294967295], [], [], [], [], [], [], [], [], [], []
ORA-15032: not all alterations performed
ORA-15130: diskgroup "FRA" is being dismounted
ORA-15066: offlining disk "FRA_0004" in group
"FRA" may result in a data loss
ORA-15196: invalid ASM block header [kfc.c:26076] [hard_kfbh] [11223]
[2147483648] [34 != 130]
ORA-15196: invalid ASM block header [kfc.c:26076] [hard_kfbh] [11223]
[2147483648] [34 != 130]

It is an ASM corruption block!! Well, breathe, everything has a solution in that life...


SQL> SELECT count(*) from v$database_block_corruption;

 COUNT(*)
---------
     217    <-- confirmed, 217 blocks corrupted.

solution--> move everything of this asm diskgroup to another asm diskgroup or copy them at OS level. If you have enterprise edition licence you can easily repair with "RMAN> blockrecover corruption list;" (you must need a valid backup, of course), like we have standard edition we haven't any other option....

At this ASM diskgroup we have logfiles, controlfile, backups and archivelogs. We aren't worry about backups and archivelogs because they are copying in other server every 1 hour. So we just moved logfiles and controlfile:

$sqlplus / as sysasm
*REMEMBER: I had database on mount state. So we can do it without any problem.

SQL> ALTER SYSTEM SET control_files = ('+FRAN_DATA/fran/controlfile/control01.ctl','/backup/controlfile/control02.ctl') scope=spfile;

SQL> select member from v$logfile;

MEMBER
--------------------------------------------------------------------------------
+FRA/fran/onlinelog/group_1.A
+FRA/fran/onlinelog/group_2.A
+FRAN_DATA/fran/onlinelog/group_2.B
+FRA/fran/onlinelog/group_3.A
+FRAN_DATA/fran/onlinelog/group_3.B
+FRA/fran/onlinelog/group_4.A
+FRAN_DATA/fran/onlinelog/group_4.B
+FRAN_DATA/fran/onlinelog/group_1.B

SQL> ALTER DATABASE RENAME FILE '+FRA/fran/onlinelog/group_1.A', '+FRAN_DATA/fran/onlinelog/group_1.B' TO '+FRAN_DATA/ASM/ONLINELOG/group_1NEW_A', '+FRAN_DATA/ASM/ONLINELOG/group_1NEW_B';

Database altered.

SQL> ALTER DATABASE RENAME FILE '+FRA/fran/onlinelog/group_2.A', '+FRAN_DATA/fran/onlinelog/group_2.B' TO '+FRAN_DATA/ASM/ONLINELOG/group_2NEW_A', '+FRAN_DATA/ASM/ONLINELOG/group_2NEW_B';

Database altered.

SQL> ALTER DATABASE RENAME FILE '+FRA/fran/onlinelog/group_3.A', '+FRAN_DATA/fran/onlinelog/group_3.B' TO '+FRAN_DATA/ASM/ONLINELOG/group_3NEW_A', '+FRAN_DATA/ASM/ONLINELOG/group_3NEW_B';

Database altered.

SQL> ALTER DATABASE RENAME FILE '+FRA/fran/onlinelog/group_4.A', '+FRAN_DATA/fran/onlinelog/group_4.B' TO '+FRAN_DATA/ASM/ONLINELOG/group_4NEW_A', '+FRAN_DATA/ASM/ONLINELOG/group_4NEW_B';

Database altered.


SQL> select member from v$logfile;

MEMBER
--------------------------------------------------------------------------------
+FRAN_DATA/asm/onlinelog/group_1new_A
+FRAN_DATA/asm/onlinelog/group_2new_A
+FRAN_DATA/asm/onlinelog/group_2new_B
+FRAN_DATA/asm/onlinelog/group_3new_A
+FRAN_DATA/asm/onlinelog/group_3new_B
+FRAN_DATA/asm/onlinelog/group_4new_A
+FRAN_DATA/asm/onlinelog/group_4new_B
+FRAN_DATA/asm/onlinelog/group_1new_B

8 rows selected.

SQL> alter database open resetlogs
2 ;

Database altered.

WOW, opened!! good work!!

Now, i recommend drop and recreate diskgroup +FRA, move again logfiles and controlfile to +FRA and forgot all about corruption blocks.... for the moment....

Complete Deinstall/Remove Oracle 11gR2

Imagine that you have an Oracle database 11gR2 installed. That database is working but you don't need it anymore and need that space.

That manual is made on windows 7 64 bits but the procedure is simililar on Unix so you can use this guide.

Oracle provides an useful tool called "deinstall tool" for standalone, RAC, ASM Oracle installations. You can find Deinstall Tool under ORACLE_HOME\deinstall\. If you don't have it you can go to this link and download it:
ORACLE DOWNLOADS --> Under Oracle Database 11gR2, click "See all" and look for "Oracle De-Install Utility".

1) Deinstall tool will stop Oracle software, and removes Oracle software and configuration files on the operationg system for a specifil Oracle Home. If you are running an Oracle grid infraestructure for a standalon server, then the deinstaller prompts you to run roothas.pl script to deconfigure Oracle Restart.

2)Go to your Oracle_home --> deinstall
or Oracle_home --> grid --> deinstall if you are under grid infraestructure and execute deinstall.bat.

deinstall tool use the following sintax (if you want execute manually):

deinstall.bat -home complete path of Oracle home [-silent] [-checkonly] [-local]
[-cleanupOBase] [-paramfile complete path of input parameter property file] [-params name1=value name2=value . . .]
[-o complete path of directory for saving files] [-help]

Just answer the questions and wait until finish.

Estimate Time To Complete RMAN Execution (backup, recover, restore)

To estimate how many time any rman operation will take, you can check it with this script:

SELECT SID, SERIAL#, CONTEXT, SOFAR, TOTALWORK,
       ROUND(SOFAR/TOTALWORK*100,2) "%_COMPLETE"
FROM V$SESSION_LONGOPS
WHERE OPNAME LIKE 'RMAN%'
  AND OPNAME NOT LIKE '%aggregate%'
  AND TOTALWORK != 0
  AND SOFAR <> TOTALWORK;

For example if we are doing a full backup of database we can predict how many time we will take (approximately) :

Same will happen with restore/recover.

EMKEY problems in Oracle RAC 11gR2

Oracle database 11gR2 (RAC 2 nodes)
O.S.: Redhat 5.3


1) After do a duplicate database in another host  i was unable to start Enterprise Manager Console. I got an error about EMKEY file corruption.

[oracle@fran ~]$ emctl status emkey
Oracle Enterprise Manager 11g Database Control Release 11.2.0.3.0
Copyright (c) 1996, 2011 Oracle Corporation.  All rights reserved.
Please enter repository password:

The Em Key is not configured properly or is corrupted in the file system and does not exist in the Management Repository. To correct the problem:
1) Copy the emkey.ora file from another OMS or backup machine to the OH/sysman/config directory.
2) Configure the emkey.ora file by running "emctl config emkey -emkeyfile <emkey.ora file location>".

2) I went to ORACLE_HOME/<host>_<sid>/sysman/config  directory and look for emkey.ora, It exists but when i tried to run "emkey config emkey -emkeyfile <emkey.ora file location>" it shows:

The Em Key could not be configured. /u01/app/oracle/product/11.2.0.3/dbhome_1/fran_orcl/sysman/config/emkey.ora is corrupted or contains an invalid Em Key.

So i replaced this emkey.ora from an older emkey.ora but it shows again the same error:

 The Em Key could not be configured. /u01/app/oracle/product/11.2.0.3/dbhome_1/fran_orcl/sysman/config/emkey.ora is corrupted or contains an invalid Em Key.

3) My next try was configure manually but again it shows an error:

[oracle@fran config]$ emctl config emkey -repos -sysman_pwd oracle -emkeyfile /u01/app/oracle/product/11.2.0.3/dbhome_1/fran_orcl/sysman/config/emkey.ora
Oracle Enterprise Manager 11g Database Control Release 11.2.0.3.0
Copyright (c) 1996, 2011 Oracle Corporation.  All rights reserved.

The Em Key could not be configured. The Em Key was not found in Management Repository.

4) If Em Key wasn't found in Management Repository you only have one way to resolve, recreate Repository...

You can check how to recreate repository in this blog: How To Recreate Oracle Repository