Wednesday, May 9, 2012

Create physical standby database with Oracle 11gr2 from active database

I've created a physical standby database from active database
Here are the steps to do that

primary database asm451p1 server ota5070
standby database asm452p1 server ota5071
listener run in our enviroment under the user grid on linux


on the primary database asm451p1 server ota5070
create standby redolog files with the same size as the redo log files
always create 1 extra standby redo logfile then the normal redo logfiles
In this example whe have 3 redo logfiles so we create 4 standby redolog files.

alter database add standby logfile group 4 size 100M;
alter database add standby logfile group 5 size 100M;
alter database add standby logfile group 6 size 100M;
alter database add standby logfile group 7 size 100M;

alter database add standby logfile member '+FRA' to group 4;
alter database add standby logfile member '+DATA' to group 5;
alter database add standby logfile member '+FRA' to group 6;
alter database add standby logfile member '+DATA' to group 7;

on the primary database asm451p1 set the archivelog destinations

ALTER SYSTEM SET log_archive_dest_1 = 'LOCATION=+FRA DB_UNIQUE_NAME=ASM451P VALID_FOR=(ALL_LOGFILES,ALL_ROLES)' scope=both;

ALTER SYSTEM SET log_archive_dest_2 = 'SERVICE=ASM452P1 ASYNC DB_UNIQUE_NAME=ASM452P VALID_FOR=(ONLINE_LOGFILE,PRIMARY_ROLE)' scope =both;

after that set the following parameters on the primary database asm451p1

ALTER SYSTEM SET log_archive_dest_state_1 = 'ENABLE';
ALTER SYSTEM SET log_archive_dest_state_2 = 'ENABLE';
ALTER SYSTEM SET standby_file_management = 'AUTO';
ALTER SYSTEM SET log_archive_config = 'DG_CONFIG=(ASM451P,ASM452P)';

Put in the tnsnames.ora the following databases. This must be done on the ota5070

ASM451P1 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (LOAD_BALANCE=ON)
      (ADDRESS = (Protocol = TCP)(Host = ota5070)(Port = 1521))
    )
    (CONNECT_DATA =
    (SERVICE_NAME =  ASM451P)
    )
  )
ASM452P1 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (LOAD_BALANCE=ON)
      (ADDRESS = (Protocol = TCP)(Host = ota5071)(Port = 1521))
    )
    (CONNECT_DATA =
    (SERVICE_NAME =  ASM451P)(UR=A)
    )
  )


 
On the primary database host ota5070 you must set the following lines in the listener.ora
The listener runs under the user grid so logon as grid
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = ota5070.business.finl.fortis)(PORT = 1521))
    )
  )

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = ASM451P)
      (ORACLE_HOME = /oracle/home/11.2.0.3.0)
      (SID_NAME = ASM451P1)
    )
  )

ADR_BASE_LISTENER = /grid/home
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON              # line added by Agent


On the standby host ota5071 put the following lines in the listener.ora. The listener runs under the user grid
so logon as grid

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = ota5071.business.finl.fortis)(PORT = 1521))
    )
  )

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = ASM451P)
      (ORACLE_HOME = /oracle/home/11.2.0.3.0)
      (SID_NAME = ASM452P1)
    )
  )


ADR_BASE_LISTENER = /grid/home

ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON              # line added by Agent


on the standby host ota5071 under user oracle set the following lines in the tnnames.ora
 ASM451P1 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (LOAD_BALANCE=ON)
      (ADDRESS = (Protocol = TCP)(Host = ota5070)(Port = 1521))
    )
    (CONNECT_DATA =
    (SERVICE_NAME =  ASM451P)
    )
  )

ASM452P1 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (LOAD_BALANCE=ON)
      (ADDRESS = (Protocol = TCP)(Host = ota5071)(Port = 1521))
    )
    (CONNECT_DATA =
    (SERVICE_NAME =  ASM451P)
    )
  )


 
copy password file from primary host to standby host
scp orapwASM451P1 ota5071:/tmp
cp /tmp/ orapwASM451P1 /oracle/home/11.2.0.3.0/dbs
cd /oracle/home/11.2.0.3.0/dbs
chmod 640 orapwASM451P1
mv orapwASM451P1 orapwASM452P1


because we use audting we nust create adump directory on standby host ota5071
/oracle/home/admin/ASM451P/adump
Set standby database name in oratab
ASM452P1:/oracle/home/11.2.0.3.0:N
 
Create pfile in /oracle/home/11.2.0.3.0/dbs
we need only 1 parameter in it
$ vi initASM452P1.ora
db_name=ASM451P

now start the standby database asm452p1
SQL> startup nomount;

Now go to the primary host ota5070 and start RMAN
RMAN>connect target sys@asm451p1
RMAN> connect auxiliary sys@asm452p1

now you can start the following script
RMAN>DUPLICATE TARGET DATABASE
FOR STANDBY
FROM ACTIVE DATABASE
DORECOVER
SPFILE
set control_files='+DATA','+FRA'
SET db_unique_name='ASM452P'
SET LOG_ARCHIVE_DEST_1='LOCATION=+FRA DB_UNIQUE_NAME=ASM452P VALID_FOR=
(ALL_LOGFILES,ALL_ROLES)'
SET LOG_ARCHIVE_DEST_2='SERVICE=ASM451P ASYNC VALID_FOR=
(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ASM451P'
SET FAL_SERVER='ASM452P'
SET db_recovery_file_dest='+FRA'
SET db_create_file_dest='+DATA'
SET standby_archive_dest='+FRA'
NOFILENAMECHECK;

Here is the the output of the logfile
Starting Duplicate Db at 04-MAY-12
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=111 device type=DISK
contents of Memory Script:
{
   backup as copy reuse
   targetfile  '/oracle/home/11.2.0.3.0/dbs/orapwASM451P1' auxiliary format 
 '/oracle/home/11.2.0.3.0/dbs/orapwASM452P1'   targetfile 
 '+DATA/spfileasm451p.ora' auxiliary format 
 '/oracle/home/11.2.0.3.0/dbs/spfileASM452P1.ora'   ;
   sql clone "alter system set spfile= ''/oracle/home/11.2.0.3.0/dbs/spfileASM452P1.ora''";
}
executing Memory Script
Starting backup at 04-MAY-12
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=27 device type=DISK
Finished backup at 04-MAY-12
sql statement: alter system set spfile= ''/oracle/home/11.2.0.3.0/dbs/spfileASM452P1.ora''
contents of Memory Script:
{
   sql clone "alter system set  control_files = 
 '' +DATA'', ''+FRA'' comment=
 '''' scope=spfile";
   sql clone "alter system set  db_unique_name = 
 ''ASM452P'' comment=
 '''' scope=spfile";
   sql clone "alter system set  LOG_ARCHIVE_DEST_2 = 
 ''SERVICE=ASM451P ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ASM451P'' comment=
 '''' scope=spfile";
   sql clone "alter system set  FAL_SERVER = 
 ''ASM452P'' comment=
 '''' scope=spfile";
   sql clone "alter system set  db_recovery_file_dest = 
 ''+FRA'' comment=
 '''' scope=spfile";
   sql clone "alter system set  db_create_file_dest = 
 ''+DATA'' comment=
 '''' scope=spfile";
   shutdown clone immediate;
   startup clone nomount;
}
executing Memory Script
sql statement: alter system set  control_files =  '' +DATA'', ''+FRA'' comment= '''' scope=spfile
sql statement: alter system set  db_unique_name =  ''ASM452P'' comment= '''' scope=spfile
sql statement: alter system set  LOG_ARCHIVE_DEST_2 =  ''SERVICE=ASM451P ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ASM451P'' comment= '''' scope=spfile
sql statement: alter system set  FAL_SERVER =  ''ASM452P'' comment= '''' scope=spfile
sql statement: alter system set  db_recovery_file_dest =  ''+FRA'' comment= '''' scope=spfile
sql statement: alter system set  db_create_file_dest =  ''+DATA'' comment= '''' scope=spfile
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area     680607744 bytes
Fixed Size                     2231472 bytes
Variable Size                352322384 bytes
Database Buffers             318767104 bytes
Redo Buffers                   7286784 bytes
contents of Memory Script:
{
   sql clone "alter system set  control_files = 
  ''/oracle/home/11.2.0.3.0/dbs/ +DATA'', ''+FRA/asm452p/controlfile/current.256.782404223'' comment=
 ''Set by RMAN'' scope=spfile";
   backup as copy current controlfile for standby auxiliary format  '/oracle/home/11.2.0.3.0/dbs/ +DATA';
   restore clone controlfile to  '+FRA/asm452p/controlfile/current.257.782404223' from 
 '/oracle/home/11.2.0.3.0/dbs/ +DATA';
   sql clone "alter system set  control_files = 
  ''/oracle/home/11.2.0.3.0/dbs/ +DATA'', ''+FRA/asm452p/controlfile/current.257.782404223'' comment=
 ''Set by RMAN'' scope=spfile";
   shutdown clone immediate;
   startup clone nomount;
}
executing Memory Script
sql statement: alter system set  control_files =   ''/oracle/home/11.2.0.3.0/dbs/ +DATA'', ''+FRA/asm452p/controlfile/current.256.782404223'' comment= ''Set by RMAN'' scope=spfile
Starting backup at 04-MAY-12
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
copying standby control file
output file name=/oracle/home/11.2.0.3.0/dbs/snapcf_ASM451P1.f tag=TAG20120504T143024 RECID=9 STAMP=782404224
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 04-MAY-12
Starting restore at 04-MAY-12
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=265 device type=DISK
channel ORA_AUX_DISK_1: copied control file copy
Finished restore at 04-MAY-12
sql statement: alter system set  control_files =   ''/oracle/home/11.2.0.3.0/dbs/ +DATA'', ''+FRA/asm452p/controlfile/current.257.782404223'' comment= ''Set by RMAN'' scope=spfile
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area     680607744 bytes
Fixed Size                     2231472 bytes
Variable Size                352322384 bytes
Database Buffers             318767104 bytes
Redo Buffers                   7286784 bytes
contents of Memory Script:
{
   sql clone 'alter database mount standby database';
}
executing Memory Script
sql statement: alter database mount standby database
contents of Memory Script:
{
   set newname for clone tempfile  1 to new;
   switch clone tempfile all;
   set newname for clone datafile  1 to new;
   set newname for clone datafile  2 to new;
   set newname for clone datafile  3 to new;
   set newname for clone datafile  4 to new;
   set newname for clone datafile  5 to new;
   backup as copy reuse
   datafile  1 auxiliary format new
   datafile  2 auxiliary format new
   datafile  3 auxiliary format new
   datafile  4 auxiliary format new
   datafile  5 auxiliary format new
   ;
   sql 'alter system archive log current';
}
executing Memory Script
executing command: SET NEWNAME
renamed tempfile 1 to +DATA in control file
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting backup at 04-MAY-12
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=+DATA/asm451p/datafile/system.260.782311383
output file name=+DATA/asm452p/datafile/system.256.782404245 tag=TAG20120504T143043
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=+DATA/asm451p/datafile/sysaux.261.782311387
output file name=+DATA/asm452p/datafile/sysaux.257.782404251 tag=TAG20120504T143043
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=+DATA/asm451p/datafile/undo.262.782311389
output file name=+DATA/asm452p/datafile/undo.258.782404259 tag=TAG20120504T143043
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=+DATA/asm451p/datafile/tools.264.782311397
output file name=+DATA/asm452p/datafile/tools.259.782404261 tag=TAG20120504T143043
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting datafile copy
input datafile file number=00005 name=+DATA/asm451p/datafile/users.265.782311399
output file name=+DATA/asm452p/datafile/users.260.782404265 tag=TAG20120504T143043
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 04-MAY-12
sql statement: alter system archive log current
contents of Memory Script:
{
   backup as copy reuse
   archivelog like  "+FRA/asm451p/archivelog/2012_05_04/thread_1_seq_96.346.782404265" auxiliary format 
 "/oracle/home/11.2.0.3.0/dbs/archarch_ASM451P_96_1_782311377.arc"   ;
   catalog clone archivelog  "/oracle/home/11.2.0.3.0/dbs/archarch_ASM451P_96_1_782311377.arc";
   switch clone datafile all;
}
executing Memory Script
Starting backup at 04-MAY-12
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log copy
input archived log thread=1 sequence=96 RECID=78 STAMP=782404265
output file name=/oracle/home/11.2.0.3.0/dbs/archarch_ASM451P_96_1_782311377.arc RECID=0 STAMP=0
channel ORA_DISK_1: archived log copy complete, elapsed time: 00:00:01
Finished backup at 04-MAY-12
cataloged archived log
archived log file name=/oracle/home/11.2.0.3.0/dbs/archarch_ASM451P_96_1_782311377.arc RECID=1 STAMP=782404267
datafile 1 switched to datafile copy
input datafile copy RECID=9 STAMP=782404267 file name=+DATA/asm452p/datafile/system.256.782404245
datafile 2 switched to datafile copy
input datafile copy RECID=10 STAMP=782404267 file name=+DATA/asm452p/datafile/sysaux.257.782404251
datafile 3 switched to datafile copy
input datafile copy RECID=11 STAMP=782404267 file name=+DATA/asm452p/datafile/undo.258.782404259
datafile 4 switched to datafile copy
input datafile copy RECID=12 STAMP=782404267 file name=+DATA/asm452p/datafile/tools.259.782404261
datafile 5 switched to datafile copy
input datafile copy RECID=13 STAMP=782404267 file name=+DATA/asm452p/datafile/users.260.782404265
contents of Memory Script:
{
   set until scn  780488;
   recover
   standby
   clone database
    delete archivelog
   ;
}
executing Memory Script
executing command: SET until clause
Starting recover at 04-MAY-12
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=263 device type=DISK
starting media recovery
archived log for thread 1 with sequence 96 is already on disk as file /oracle/home/11.2.0.3.0/dbs/archarch_ASM451P_96_1_782311377.arc
archived log file name=/oracle/home/11.2.0.3.0/dbs/archarch_ASM451P_96_1_782311377.arc thread=1 sequence=96
media recovery complete, elapsed time: 00:00:00
Finished recover at 04-MAY-12
Finished Duplicate Db at 04-MAY-12


Check archivelog number on primary database asm451p1
SQL> SELECT sequence#, first_time, next_time, applied
           FROM   v$archived_log
            ORDER BY sequence#;

Check if transport of archivelog works on the primary database asm451p1

SQL> select status, error from v$archive_dest where dest_id=2;


to stop media recovery on the standby database run the following command
SQL> alter database recover managed standby database cancel;


As we use standby redolog files it is better to start media recovery  this way

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;


As of Oracle 11gr2 we can use active dataguard. Which means that we can read only on the standby database while in the meantime media recovery is running keeping the standby up to date.




SQL> SHUTDOWN IMMEDIATE;

SQL> STARTUP MOUNT;
SQL> ALTER DATABASE OPEN READ ONLY;
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

Next blog a will setup dataguard broker and setup observer for fast fail over.

Thats it.

ORA-00937: not a single-group group function after upgrade to Oracle 11gr2

I've upgraded an Oracle database to 11.2.0.3
After the upgrade i got a message from the guys who run the batch jobs that one of those jobs
crashed with the error ORA-00937: not a single-group group function
The same job ran fine on the version 10.2.0.4
The error occured in this statement

Cursor crb_leidend_b is
        select
        group_nr,
        max(to_number(relnr)) relnr
        from    (
                select group_nr, relnr, score
                from crb_groepsscore_b a0
                where exists (
                               select  to_char(a1.group_nr)||to_char(max(a1.score))
                               from  crb_groepsscore_b a1
                               where  a0.group_nr = a1.group_nr
                               and a0.score = a1.score
                              )
                ) group by group_nr;

The select statement in red was the cause of the error witch makes sense because in that
statement the funcion max is used so then you have to use the goup by clause.
In Oracle 10gr2 or below Oracle Optimizer doesn't have a problem with that but as of Oracle 11gr2
the optimizer used stricter rules an gives the error  ORA-00937: not a single-group group function.
which is logical.
How to solve the problem.

Well of course you can add the group by clause in the sub query

Cursor crb_leidend_b is
        select
        group_nr,
        max(to_number(relnr)) relnr
        from    (
                select group_nr, relnr, score
                from crb_groepsscore_b a0
                where exists (
                               select  to_char(a1.group_nr)||to_char(max(a1.score))
                               from  crb_groepsscore_b a1
                               where  a0.group_nr = a1.group_nr
                               and a0.score = a1.score
                               group by group_nr,score
                              )
                ) group by group_nr;

But th subquery is used for the where exsist and this means it only want to know if the value exsist or not. So there is no need at all for the select  to_char(a1.group_nr)||to_char(max(a1.score)). It doesn't return the value only if it exsist.or not.

So the other solution would be

Cursor crb_leidend_b is
        select
        group_nr,
        max(to_number(relnr)) relnr
        from    (
                select group_nr, relnr, score
                from crb_groepsscore_b a0
                where exists (
                               select  1
                               from  crb_groepsscore_b a1
                               where  a0.group_nr = a1.group_nr
                               and a0.score = a1.score
                              )
                ) group by group_nr

Thats it
 

Thursday, May 3, 2012

Dml error logging

Dml error loggin is around since Oracle version 10.2.0.1
What is it.
One time datawarehouse was running a job that was inserting more then 10 milion rows.
Almost at the end the bacth job failed with ORA-00001: unique constraint violated
That let to some frustration because of the error message no rows where inserted.
As so many time Oracle has a soltion for this.

Here is a little example

CREATE TABLE t1 (col1 NUMBER PRIMARY KEY);
CREATE TABLE t2 (col1 NUMBER PRIMARY KEY);

INSERT INTO t1 VALUES(1);
INSERT INTO t1 VALUES(2);
INSERT INTO t1 VALUES(3);
INSERT INTO t1 VALUES(4);
INSERT INTO t2 VALUES(1);
INSERT INTO t2 VALUES(2);
INSERT INTO t2 VALUES(3);
INSERT INTO t2 VALUES(8);
INSERT INTO t2 VALUES(9);
COMMIT;

and here is the trick
SQL>EXECUTE DBMS_ERRLOG.CREATE_ERROR_LOG('t1',error_log_t1')

this means we put errorlogging on for table t1.and created the errorlog table error_log_t1
Now we going to insert rows into table t1 from table t2
SQL>insert into t1 select * from t2 log errors reject limit unlimited


2 rows created

That should have been 5 rows created.
Why where te rows rejected
select * from error_log_t1
ORA_ERR_NUMBER$ ORA_ERR_MESG$                                                COL
--------------- ------------------------------------------------------------ ---
              1 ORA-00001: unique constraint (GDA001.SYS_C0040470) violated  1
              1 ORA-00001: unique constraint (GDA001.SYS_C0040470) violated  2
              1 ORA-00001: unique constraint (GDA001.SYS_C0040470) violated  3

You see the error message and the value in the colomn under COL.
Which is correct because value 1,2,3 already exist in T1.
but al the other rows are inserted instead of when you not using dml error logging.

Thats it

Wednesday, May 2, 2012

User public in audit trail ???? what

After a call from a application manager that she had seen that a user public tried to login
into a database i of course check the audit trail and yes someone tried to login a user public.

OS_USERNAM USERNAME   USERHOST   TIMESTAMP     ACTION ACTION_NAME     COMMENT_TEXT                    SESSIONID RETURNCODE
---------- ---------- ---------- --------- ---------- --------------- ------------------------------ ---------- ----------
oracle     PUBLIC     spca5003   05-MAY-11        100 LOGON           Authenticated by: OS                   -1          0
srs401p    PUBLIC     spca5003   06-MAY-11        100 LOGON           Authenticated by: OS                   -1          0

So whats up.
Public is not a user nor a role.
SQL> select user#,name,type# from sys.user$ where name = 'PUBLIC';
           user#      name     type#
       --------------------------------
          1         public         0

type 0 means role
type 1 means user

SQL>select * from dba_roles
          where role = 'PUBLIC';

   no rows selected

so no role

SQL>select password from sys.user$
         where name = 'PUBLIC';

          PASSWORD
 -----------------------------------

 So can we login as user public

SQL> conn public
Enter password:
Error:
ORA-01005: null password given; login denied

No you can't login but what if you login like this
SQL> conn / as sysoper
Connected
SQL> show user
USER is "PUBLIC"

So now you login as public can you do something
SQL> create table aap(t1 number) tablespace users;
           ERROR at line 1:ORA-01918: user 'PUBLIC' does not exist

Well so your not user public ??? but if you look in the audit trail

OS_USERNAM USERNAME   USERHOST   TIMESTAMP     ACTION ACTION_NAME     COMMENT_TEXT                    SESSIONID RETURNCODE
---------- ---------- ---------- --------- ---------- --------------- ------------------------------ ---------- ----------
oracle     PUBLIC     spca5003   05-MAY-11        100 LOGON           Authenticated by: OS                   -1          0
oracle     PUBLIC     spca5003   05-MAY-11          1 CREATE TABLE                                           -1       1031
srs401p    PUBLIC     spca5003   06-MAY-11        100 LOGON           Authenticated by: OS                   -1          0

But why someone login as / as sysoper is still under investigation.






export public synoymns during schema export

During a schema export the public synoymns are not captured.
So is there a way to get them during a schema export
The answer is yes although it took some while.

In a parfile it will look like this

userid="/ as sysdba"
DIRECTORY=CONVERT_DIR
SCHEMAS=BAO4_OWN,BAO4_USR
FULL=yes
INCLUDE=SYNONYM:"IN(SELECT synonym_name FROM dba_synonyms WHERE owner = 'PUBLIC' and table_owner='BAO4_OWN'
dumpfile=exp.dmp
logfile=exp.log

That is the way to capture public synoyms during a schema export.

ORA-01180: can not create datafile 1

I was restoring a database when i got this error message
ORA-01180: can not create datafile 1

The restore did restore some file's but when it wanted to restore datafile 1
it gave the message. After some time i thought maby the file isn't on tape
anymore.

First i checked what the retention time of the backup was in rman for this database.
It was 21 days. The restore i wanted was from a backup 22 days ago.
Then in rman i ran the command : list backup of database;
It showed that the backup of 22 ago wasn't availble anymore.
It was just luck that some datafiles where still on tape but not datafile 1

The error message is at first sight a little strange.but if you know why it make's sense.
You can't create a datafile if it isn't on tape anymore.

Lesson learned: First make sure that the restore time is within your retention time.

Clone a database which doesn't exist anymore

A colleagua of mine ran into a problem when he wanted to clone a database
that didn't exist anymore.
Solution in Oracle 11G (Of course you must have a backup of that database)

In rman do not connect to the target database (it isn't there anymore)

connect catalog rmnt_own/@OCRM51P
connect auxiliary /;

set dbid=3870197482 # That is something you have to know because that is the id of the database
                                    you want to clone

run{
duplicate database AVC452T TO AVC452C
until time  "to_date('2012-04-02 00:30:00','YYYY-MM-DD HH24:MI:SS')"  ;
}

And that is it.

Restore read only tablespace with rman

A colleague of mine was restoring a database. After some time he shouted  i'm missing some
tablespace why won't rman restore them.
After a quick look from me and i saw what the poblem was. There where read only tablespaces in
that database. If you want rman to restore them you have to do the following

RESTORE DATABASE CHECK READONLY;
RECOVER DATABASE CHECK READONLY;

rman will check if the are of the right version and restore/recover them.

Offline patching Oracle Cloud Control 12c

As we can't use cloud control selfupdate due to security reasons how do you get the
patches inside cloud control.
Solution is offline patching. Here is how you do it

In cloud control go to
setup => provisiong and patching => offline patching
click on online to make the dot disappear
click on offline
click on APPLY

In cloud control go to
setup => extensibility and the connection mode is now offline
the check update is not gray anymore and you can click on it.
When you click on check update a box appears
read it. In our example whe did the following

https://updates.oracle.com/Orion/Download/download_patch/p9348486_112000_Generic.zip

Go to the above page login with your metalink account and dowload it and put the file on the server
where cloud control is installed in our case the srv5020

Logon to the server srv5020
go to the OMS_HOME in our case
$ cd /oracle/home/oms/oms
$ cd bin
and do the following
./emcli login -username=SYSMAN
./emcli import_update_catalog -file=/oracle/home/p9348486_112000_Generic.zip -omslocal

now you go back to cloud control

setup => selfupdate => agent software
You see all agent software also for aix bu it's not yet downloaded
Seelect aix agent and click on dowload  and you see this

https://updates.oracle.com/Orion/Services/download/p13528458_112000_Generic.zip?aru=14405528&patch_file=p13528458_112000_Generic.zip

Go to the above page login with your metalink account and dowload it and put the file on the server
where cloud control is installed in our case de srv5020

Logon to the server srv5020
go to the OMS_HOME in our case
$ cd /oracle/home/oms/oms
$ cd bin
and do the following
./emcli login -username=SYSMAN
./emcli import_update_catalog -file=/oracle/home/p13528458_112000_Generic.zip -omslocal

now you go back to cloud control

setup => selfupdate => agent software
You see that the aix agent software is downloaded
Now you can install the agent  but thats a whole other story
You'll see a post of that very soon

Transportable tablespace from rman backupset

In this blog i'm going to show how you can transport a set of tablespace to another database on
an other server.

Source database ORP310  Server ota5070
Targer database ORA310  Server ota5071

logon to the server ota5070

First create directory where the export file and the clone database are going to be.
in our case /oracle/home/exp
Now use the create directory command in sqlplus

SQL> create directory export_dir as '/oracle/home/exp';
SQL> grant read,write on directory export_dir to migusr;

Create the rman transportable tablespace script.

run{
transport tablespace
nja_idx_l,
nja_idx_m,
nja_idx_s,
nja_tbl_l,
nja_tbl_m,
nja_tbl_s,
nja_tbl_x
transport destination '/oracle/home/exp'
auxiliary destination '/oracle/home/exp'
datapump directory export_dir
dumpfile 'nja_test_dmp'
import script 'nja_test_imp'
export log  'exp_nja_test.log'
until time "to_date('10 MAY 2010 14:26:00','DD MON YYYY hh24:mi:ss')";
}

After sucessfully runing the script you see the following file in /oracle/home/exp

$ ls –altr
-rw-rw----   1 oracle   dba      7550803968 Dec 10 16:12 nja_idx_l_01.dbf
-rw-rw----   1 oracle   dba      3775930368 Dec 10 16:12 nja_idx_l_02.dbf
-rw-rw----   1 oracle   dba      3146784768 Dec 10 16:12 nja_idx_m_01.dbf
-rw-rw----   1 oracle   dba      1153441792 Dec 10 16:12 nja_idx_s_01.dbf
-rw-rw----   1 oracle   dba      10486816768 Dec 10 16:12 nja_tbl_l_01.dbf
-rw-rw----   1 oracle   dba      10486816768 Dec 10 16:12 nja_tbl_l_02.dbf
-rw-rw----   1 oracle   dba       944775168 Dec 10 16:12 nja_tbl_l_03.dbf
-rw-rw----   1 oracle   dba      6292512768 Dec 10 16:12 nja_tbl_m_01.dbf
-rw-rw----   1 oracle   dba       629153792 Dec 10 16:12 nja_tbl_s_01.dbf
-rw-rw----   1 oracle   dba      8127520768 Dec 10 16:12 nja_tbl_x_01.dbf
-rw-rw----   1 oracle   dba       787488768 Dec 10 16:10 nja_tbl_x_02.dbf
in the /oracle/home/exp you see also two other file

$ ls –altr
-rw-rw----   1 oracle   dba           77824 Dec 10 14:48 nja_test.dmp
-rw-rw-r--   1 oracle   dba            1947 Dec 10 14:48 nja_test.imp
copy the file to the server where the target database  ORA310 is running

scp nja*.dbf oracle@ota5071:/oracle/ORA310/data01

copy the dmp file and imp file to the ota5071

scp nja_test.* oracle@ota5071:/oracle/home/exp

logon to the ota5071 and in sqlplus logon to the ora310 and do the following

SQL>create directory export_dir as '/oracle/home/exp'
SQL>grant read,write on directory export_dir to migusr;

open the script nja_test_imp and remove everything execpt this

impdp  / directory=export_dir dumpfile= 'nja_test.dmp' transport_datafiles= (
'/oracle//ORA310/data01/nja_idx_l_01.dbf',
'/oracle/ORA310/data01/nja_idx_l_02.dbf',
'/oracle/ORA310/data01/nja_idx_m_01.dbf',
'/oracle/ORA310/data01/nja_idx_s_01.dbf',
'/oracle/ORA310/data01/nja_tbl_l_01.dbf',
'/oracle/ORA310/data01/nja_tbl_l_02.dbf',
'/oracle/ORA310/data01/nja_tbl_l_03.dbf',
'/oracle/ORA310/data01/nja_tbl_m_01.dbf',
'/oracle/ORA310/data/nja_tbl_s_01.dbf',
'/oracle/ORA310/data01/nja_tbl_x_01.dbf',
'/oracle/ORA310data01/nja_tbl_x_02.dbf')

Check fist if the datafiles don't already exsist in the ORA310 database on the ota5071 server
If the exsist you got to delete them first

SQL> drop tablespace nja_idx_l including contents and datafiles cascade constraints;
SQL> drop tablespace nja_idx_m including contents and datafiles cascade constraints;
SQL> drop tablespace nja_idx_s including contents and datafiles cascade constraints;
SQL> drop tablespace nja_tbl_l including contents and datafiles cascade constraints;
SQL> drop tablespace nja_tbl_mincluding contents and datafiles cascade constraints;
SQL> drop tablespace nja_tbl_s including contents and datafiles cascade constraints;
SQL> drop tablespace nja_tbl_x including contents and datafiles cascade constraints;

start script nja_test_imp

$ cd /oracle/home/exp
$ ./nja_test_imp

this start the import of the tablespaces.
When the script is done check if the tablespaces are pluged in the ORA310 database.

SQL> select tablespace_name from dba_tablespaces
          where tablespace_name like 'NJA%';
TABLESPACE_NAME
------------------------------
NJA_IDX_L
NJA_IDX_M
NJA_IDX_S
NJA_TBL_L
NJA_TBL_M
NJA_TBL_S
NJA_TBL_X

And thats it.

ORA-39071: Value for TABLES is badly formed

A colleague of mine was trying to export tables with expdp when he got the error
ORA-39071: Value for TABLES is badly formed

He( His name is Patrick Kamper) also foud the solution:
put the list of tables to export into a table

SQL> insert into pka003.export_tables values ('T_AGT_BUSI_PDT');
SQL>  insert into pka003.export_tables values ('T_AGT_PRDT_RATE');
SQL> commit;

expdp \'/ as sysdba\' dumpfile=BAO401T1.expdp logfile=BAO401T1 \
schemas=BAO4_OWN include=table:\"in \(select table_name from pka003.export_tables\)\"

Now a quote from patrick

Oracle Rules.

How to clone an Oracle Home 11gr2

How do you clone an Oracle home manually
Well here is an example

The source is running on server scca5003
The target server is th spca5003
Both the oracle home are named /oracle/home/11.2.0.1 and must by 15 gyg

On the source server scca5003:

$ cd /oracle/home
make a tar file of the 11.2.0.1 directory

$ tar -cvf /oracle/home/11gr2 11.2.0.1

then copy the tar file to the target server spca5003. I've used sftp
$ sftp spca5003

Connecting to spca5003...
oracle@spca5003's password:
sftp> pwd
Remote working directory: /oracle
sftp> cd home
sftp> pwd
Remote working directory: /oracle/home
sftp> put 11gr2.tar
Uploading 11gr2.tar to /oracle/home/exp/11gr2.tar
                  11gr2.tar                                                  100% 6428MB  23.7MB/s   04:31

Logon to the source server spca5003 and do the following:
$ cd /oracle/home
$ tar -xvf 11gr2.tar

After that start clone.pl
$ cd /oracle/home/11.2.0.1.0/clone/bin
$ perl clone.pl ORACLE_BASE=/oracle/home ORACLE_HOME=/oracle/home/11.2.0.1.0  ORACLE_HOME_NAME=oradb11g_home

read the logfile if everything went oke
                 ./runInstaller -clone -waitForCompletion  "ORACLE_BASE=/oracle/home" "ORACLE_HOME=/oracle/home/11.2.0.1.0" "ORACLE_HOME_NAME=OraDb11g_home1" -silent -noConfig -nowait
Starting Oracle Universal Installer...

Checking swap space: must be greater than 500 MB.   Actual 13044 MB    Passed
Preparing to launch Oracle Universal Installer from /tmp/OraInstall2010-06-28_12-19-47PM. Please wait ...Oracle Universal Installer, Version 11.2.0.1.0 Production
Copyright (C) 1999, 2009, Oracle. All rights reserved.

You can find the log of this install session at:
 /oracle/home/oraInventory/logs/cloneActions2011-03-03_12-19-47PM.log
.................................................................................................... 100% Done.



Installation in progress (Wednesday, March 03, 2011 12:20:29 PM WST)
..............................................................................                                                  78% Done.
Install successful

Linking in progress (Wednesday, March 03, 2011 12:21:06 PM WST)
Link successful

Setup in progress (Wednesday, March 03, 2011 12:23:34 PM WST)
Setup successful

End of install phases.(Wednesday, March 03, 2011 12:34:05 PM WST)
Starting to execute configuration assistants
The following configuration assistants have not been run. This can happen because Oracle Universal Installer was invoked with the -noConfig option.
--------------------------------------
The "/oracle/home/11.2.0.1.0/cfgtoollogs/configToolFailedCommands" script contains all commands that failed, were skipped or were cancelled. This file may be used to run these configuration assistants outside of OUI. Note that you may have to update this script with passwords (if any) before executing the same.
The "/oracle/home/11.2.0.1.0/cfgtoollogs/configToolAllCommands" script contains all commands to be executed by the configuration assistants. This file may be used to run the configuration assistants outside of OUI. Note that you may have to update this script with passwords (if any) before executing the same.

--------------------------------------
WARNING:
The following configuration scripts need to be executed as the "root" user.
/oracle/home/11.2.0.1.0/root.sh
To execute the configuration scripts:
    1. Open a terminal window
    2. Log in as "root"
    3. Run the scripts

The cloning of OraDb11g_home1 was successful.
Please check '/oracle/home/oraInventory/logs/cloneActions2010-06-28_12-19-47PM.log' for more details.

Run the root script
$ cd /oracle/home/11.2.0.1.0
$./root.sh

check the logfile in /oracle/home/11.2.0.1.0/install
$ cat /oracle/home/11.2.0.1.0/install/root_spca5003_2010-06-29_07-42-31.log

Running Oracle 11g root.sh script...

The following environment variables are set as:
    ORACLE_OWNER= oracle
    ORACLE_HOME=  /oracle/home/11.2.0.1.0
Entries will be added to the /etc/oracle/oratab file as needed by
Database Configuration Assistant when a database is created
Finished running generic part of root.sh script.
Now product-specific root actions will be performed.
     Finished product-specific root actions
On the target server spca5003 check the oraInventory
$ cat /etc/oraInst.loc
inventory_loc=/oracle/home/oraInventory
                        inst_group=dba

Check the contents of the inventory.xml. There must be an11gr2 entry
$ cd /oracle/home//oraInventory
$ cd /ContentsXML
$ vi inventory.xml
<?xml version="1.0" standalone="yes" ?>
<!-- Copyright (c) 1999, 2009, Oracle. All rights reserved. -->
<!-- Do not modify the contents of this file by hand. -->
<INVENTORY>
<VERSION_INFO>
   <SAVED_WITH>11.2.0.1.0</SAVED_WITH>
   <MINIMUM_VER>2.1.0.6.0</MINIMUM_VER>
</VERSION_INFO>
<HOME_LIST>
<HOME_LIST>
<HOME NAME="OH_10203" LOC="/oracle/home/10.2.0.3.0" TYPE="O" IDX="1"/>
<HOME NAME="agent10g" LOC="/oracle/home/agent10g" TYPE="O" IDX="2"/>
<HOME NAME="OH_10204" LOC="/oracle/home/10.2.0.4.0" TYPE="O" IDX="4"/>
<HOME NAME="OracleHome1" LOC="/oracle/app/oracle/product/10.2.0.2.0" TYPE="O" IDX="5"/>
<HOME NAME="OraDb11g_home1" LOC="/oracle/home/11.2.0.1.0" TYPE="O" IDX="6"/>
<HOME NAME="APPSDB_AMI401C" LOC="/oracle/home/apps11ic/db10g" TYPE="O" IDX="3" REMOVED="T"/>
</HOME_LIST>
          </INVENTORY


Now your ready to create Oracle 11gr2 database on the target server