RMAN DUPLICATE DATABASE

Source Server - lnx133
Source database name: maze
Destination Server -lnx144

Goal: to clone database 'maze' on lnx144.

On source side:
lnx133

Step 1:
Source database 'maze' is in archivelog mode, ready to take hot backup.
Take a full backup of source database
RMAN> BACKUP DATABASE PLUS ARCHIVELOG;

SQL> Alter system switch logfile;



Step 2 : 
Ensure password file exists on the source database server (lnx133)

[oracle@lnx133 dbs]$ pwd
/u01/app/oracle/product/11g/11.2.0.2/dbhome_1/dbs
[oracle@lnx133 dbs]$ ls -ltr orapwmaze
-rw-r----- 1 oracle oinstall 1536 Apr 25 12:09 orapwmaze

Step3: 
Ensure service name is available in tnsnames.ora on target server (lnx144)

[oracle@lnx144 admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11g/11.2.0.2/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

sourcemaze  =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.133)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = maze)
    )
  )


[oracle@lnx144 admin]$ tnsping sourcemaze
TNS Ping Utility for Linux: Version 11.2.0.2.0 - Production on 25-APR-2013 16:01:58
Copyright (c) 1997, 2010, Oracle.  All rights reserved.

Used parameter files:
/u01/app/oracle/product/11g/11.2.0.2/dbhome_1/network/admin/sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.133)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = maze)))
OK (0 msec)

Step4:

Create pfile, this step matters if we are dealing with different directory structure. In this run directory structure is identical, so simply copying the pfile from source to targe t(lnx144), will serve the purpose.

Based on this you can start instance in nomount. (env variables are to be set).

Step 5:

SQL> startup nomount;
ORACLE instance started.
Total System Global Area  422670336 bytes
Fixed Size                  1344616 bytes
Variable Size             327158680 bytes
Database Buffers           88080384 bytes
Redo Buffers                6086656 bytes

From source location copy the backup files (along with archivelog) to target server (at the same backup location on disk)



[oracle@lnx133 rman]$ scp H* 192.168.1.144:/u01/app/oracle/backup/rman/.
oracle@192.168.1.144's password:
Hotbkp_MAZE_4so7v8j8_1_1.bak                                                                           100%  264MB   8.8MB/s   00:30
Hotbkp_MAZE_4to7v8j9_1_1.bak                                                                           100%  251MB   6.8MB/s   00:37
Hotbkp_MAZE_4uo7v8j9_1_1.bak                                                                           100%  245MB   7.2MB/s   00:34
Hotbkp_MAZE_4vo7v8j9_1_1.bak                                                                           100%  217MB   7.5MB/s   00:29
Hotbkp_MAZE_50o7v94i_1_1.bak                                                                           100%  146MB   6.4MB/s   00:23
Hotbkp_MAZE_51o7v94j_1_1.bak                                                                           100%  197MB   6.2MB/s   00:32
Hotbkp_MAZE_52o7v94k_1_1.bak                                                                           100% 6656KB   6.5MB/s   00:01
Hotbkp_MAZE_53o7v94k_1_1.bak                                                                           100%   21MB   3.0MB/s   00:07
Hotbkp_MAZE_54o7v9ac_1_1.bak                                                                           100% 1168KB   1.1MB/s   00:00
Hotbkp_MAZE_55o7v9af_1_1.bak                                                                           100%  624KB 624.0KB/s   00:00
Hotbkp_MAZE_56o7v9em_1_1.bak                                                                           100% 6601KB   6.5MB/s   00:01
[oracle@lnx133 rman]$  scp ctl_n_spfile_c-1370075061-20130425-00 192.168.1.144:/u01/app/oracle/backup/rman/.
oracle@192.168.1.144's password:
ctl_n_spfile_c-1370075061-20130425-00                                                                  100% 9696KB   9.5MB/s   00:01

Step 6:
Required if you have a different directory structure 
(create same directories to have a consistent directory structure.

Step 7:
Environment is ready to start the duplicate database step.
[oracle@lnx144 dbs]$ rman target system/abc123@sourcemaze auxiliary /

Recovery Manager: Release 11.2.0.2.0 - Production on Thu Apr 25 16:54:32 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: MAZE (DBID=1370075061)
connected to auxiliary database: MAZE (not mounted)

RMAN> DUPLICATE TARGET DATABASE TO maze
  SPFILE
  NOFILENAMECHECK;2> 3>

Starting Duplicate Db at 25-APR-13
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=134 device type=DISK
allocated channel: ORA_AUX_DISK_2
channel ORA_AUX_DISK_2: SID=10 device type=DISK
allocated channel: ORA_AUX_DISK_3
channel ORA_AUX_DISK_3: SID=135 device type=DISK
allocated channel: ORA_AUX_DISK_4
channel ORA_AUX_DISK_4: SID=11 device type=DISK

contents of Memory Script:
{
   set until scn  19416113;
   restore clone spfile to  '/u01/app/oracle/product/11g/11.2.0.2/dbhome_1/dbs/spfilemaze.ora';
   sql clone "alter system set spfile= ''/u01/app/oracle/product/11g/11.2.0.2/dbhome_1/dbs/spfilemaze.ora''";
}
executing Memory Script

executing command: SET until clause

Starting restore at 25-APR-13
using channel ORA_AUX_DISK_1
using channel ORA_AUX_DISK_2
using channel ORA_AUX_DISK_3
using channel ORA_AUX_DISK_4

WARNING: A restore time was estimated based on the supplied UNTIL SCN
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: restoring SPFILE
output file name=/u01/app/oracle/product/11g/11.2.0.2/dbhome_1/dbs/spfilemaze.ora
channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/backup/rman/ctl_n_spfile_c-1370075061-20130425-00
channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/backup/rman/ctl_n_spfile_c-1370075061-20130425-00 tag=TAG20130425T115818
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 25-APR-13

sql statement: alter system set spfile= ''/u01/app/oracle/product/11g/11.2.0.2/dbhome_1/dbs/spfilemaze.ora''

contents of Memory Script:
{
   sql clone "alter system set  db_name =
 ''MAZE'' comment=
 ''duplicate'' scope=spfile";
   shutdown clone immediate;
   startup clone nomount;
}
executing Memory Script

sql statement: alter system set  db_name =  ''MAZE'' comment= ''duplicate'' scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area     422670336 bytes

Fixed Size                     1344616 bytes
Variable Size                356518808 bytes
Database Buffers              58720256 bytes
Redo Buffers                   6086656 bytes

contents of Memory Script:
{
   sql clone "alter system set  db_name =
 ''MAZE'' comment=
 ''Modified by RMAN duplicate'' scope=spfile";
   sql clone "alter system set  db_unique_name =
 ''MAZE'' comment=
 ''Modified by RMAN duplicate'' scope=spfile";
   shutdown clone immediate;
   startup clone force nomount
   restore clone primary controlfile;
   alter clone database mount;
}
executing Memory Script

sql statement: alter system set  db_name =  ''MAZE'' comment= ''Modified by RMAN duplicate'' scope=spfile

sql statement: alter system set  db_unique_name =  ''MAZE'' comment= ''Modified by RMAN duplicate'' scope=spfile

Oracle instance shut down

Oracle instance started

Total System Global Area     422670336 bytes

Fixed Size                     1344616 bytes
Variable Size                356518808 bytes
Database Buffers              58720256 bytes
Redo Buffers                   6086656 bytes

Starting restore at 25-APR-13
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=133 device type=DISK
allocated channel: ORA_AUX_DISK_2
channel ORA_AUX_DISK_2: SID=10 device type=DISK
allocated channel: ORA_AUX_DISK_3
channel ORA_AUX_DISK_3: SID=134 device type=DISK
allocated channel: ORA_AUX_DISK_4
channel ORA_AUX_DISK_4: SID=11 device type=DISK

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/backup/rman/ctl_n_spfile_c-1370075061-20130425-00
channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/backup/rman/ctl_n_spfile_c-1370075061-20130425-00 tag=TAG20130425T115818
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03
output file name=/u01/app/oracle/oradata1/maze/control01.ctl
output file name=/u01/app/oracle/oradata2/maze/control02.ctl
Finished restore at 25-APR-13

database mounted
RMAN-05538: WARNING: implicitly using DB_FILE_NAME_CONVERT
Using previous duplicated file /u01/app/oracle/oradata1/maze/system01.dbf for datafile 1 with checkpoint SCN of 19409868
Using previous duplicated file /u01/app/oracle/oradata1/maze/sysaux01.dbf for datafile 2 with checkpoint SCN of 19409867
Using previous duplicated file /u01/app/oracle/oradata1/maze/undotbs01.dbf for datafile 3 with checkpoint SCN of 19409869
Using previous duplicated file /u01/app/oracle/oradata2/maze/users01.dbf for datafile 4 with checkpoint SCN of 19409867
Using previous duplicated file /u01/app/oracle/oradata1/maze/example01.dbf for datafile 5 with checkpoint SCN of 19409870
Using previous duplicated file /u01/app/oracle/oradata1/maze/rcts01.dbf for datafile 6 with checkpoint SCN of 19410389
Using previous duplicated file /u01/app/oracle/oradata1/maze/undotbs02.dbf for datafile 7 with checkpoint SCN of 19410350
Using previous duplicated file /u01/app/oracle/oradata1/maze/appstbs_01.dbf for datafile 8 with checkpoint SCN of 19410350

contents of Memory Script:
{
   catalog clone datafilecopy  "/u01/app/oracle/oradata1/maze/system01.dbf",
 "/u01/app/oracle/oradata1/maze/sysaux01.dbf",
 "/u01/app/oracle/oradata1/maze/undotbs01.dbf",
 "/u01/app/oracle/oradata2/maze/users01.dbf",
 "/u01/app/oracle/oradata1/maze/example01.dbf",
 "/u01/app/oracle/oradata1/maze/rcts01.dbf",
 "/u01/app/oracle/oradata1/maze/undotbs02.dbf",
 "/u01/app/oracle/oradata1/maze/appstbs_01.dbf";
   switch clone datafile  1 to datafilecopy
 "/u01/app/oracle/oradata1/maze/system01.dbf";
   switch clone datafile  2 to datafilecopy
 "/u01/app/oracle/oradata1/maze/sysaux01.dbf";
   switch clone datafile  3 to datafilecopy
 "/u01/app/oracle/oradata1/maze/undotbs01.dbf";
   switch clone datafile  4 to datafilecopy
 "/u01/app/oracle/oradata2/maze/users01.dbf";
   switch clone datafile  5 to datafilecopy
 "/u01/app/oracle/oradata1/maze/example01.dbf";
   switch clone datafile  6 to datafilecopy
 "/u01/app/oracle/oradata1/maze/rcts01.dbf";
   switch clone datafile  7 to datafilecopy
 "/u01/app/oracle/oradata1/maze/undotbs02.dbf";
   switch clone datafile  8 to datafilecopy
 "/u01/app/oracle/oradata1/maze/appstbs_01.dbf";
}
executing Memory Script

cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata1/maze/system01.dbf RECID=11 STAMP=813689712
cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata1/maze/sysaux01.dbf RECID=12 STAMP=813689712
cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata1/maze/undotbs01.dbf RECID=13 STAMP=813689712
cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata2/maze/users01.dbf RECID=14 STAMP=813689712
cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata1/maze/example01.dbf RECID=15 STAMP=813689712
cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata1/maze/rcts01.dbf RECID=16 STAMP=813689712
cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata1/maze/undotbs02.dbf RECID=17 STAMP=813689712
cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata1/maze/appstbs_01.dbf RECID=18 STAMP=813689712

datafile 1 switched to datafile copy
input datafile copy RECID=11 STAMP=813689712 file name=/u01/app/oracle/oradata1/maze/system01.dbf

datafile 2 switched to datafile copy
input datafile copy RECID=12 STAMP=813689712 file name=/u01/app/oracle/oradata1/maze/sysaux01.dbf

datafile 3 switched to datafile copy
input datafile copy RECID=13 STAMP=813689712 file name=/u01/app/oracle/oradata1/maze/undotbs01.dbf

datafile 4 switched to datafile copy
input datafile copy RECID=14 STAMP=813689712 file name=/u01/app/oracle/oradata2/maze/users01.dbf

datafile 5 switched to datafile copy
input datafile copy RECID=15 STAMP=813689712 file name=/u01/app/oracle/oradata1/maze/example01.dbf

datafile 6 switched to datafile copy
input datafile copy RECID=16 STAMP=813689712 file name=/u01/app/oracle/oradata1/maze/rcts01.dbf

datafile 7 switched to datafile copy
input datafile copy RECID=17 STAMP=813689712 file name=/u01/app/oracle/oradata1/maze/undotbs02.dbf

datafile 8 switched to datafile copy
input datafile copy RECID=18 STAMP=813689712 file name=/u01/app/oracle/oradata1/maze/appstbs_01.dbf

contents of Memory Script:
{
   set until scn  19416113;
   recover
   clone database
    delete archivelog
   ;
}
executing Memory Script

executing command: SET until clause

Starting recover at 25-APR-13
using channel ORA_AUX_DISK_1
using channel ORA_AUX_DISK_2
using channel ORA_AUX_DISK_3
using channel ORA_AUX_DISK_4

starting media recovery

archived log for thread 1 with sequence 113 is already on disk as file /u01/archlogs/maze/1_113_811523932.arc
archived log for thread 1 with sequence 114 is already on disk as file /u01/archlogs/maze/1_114_811523932.arc
archived log file name=/u01/archlogs/maze/1_113_811523932.arc thread=1 sequence=113
archived log file name=/u01/archlogs/maze/1_114_811523932.arc thread=1 sequence=114
media recovery complete, elapsed time: 00:00:24
Finished recover at 25-APR-13
Oracle instance started

Total System Global Area     422670336 bytes

Fixed Size                     1344616 bytes
Variable Size                356518808 bytes
Database Buffers              58720256 bytes
Redo Buffers                   6086656 bytes

contents of Memory Script:
{
   sql clone "alter system set  db_name =
 ''MAZE'' comment=
 ''Reset to original value by RMAN'' scope=spfile";
   sql clone "alter system reset  db_unique_name scope=spfile";
   shutdown clone immediate;
   startup clone nomount;
}
executing Memory Script

sql statement: alter system set  db_name =  ''MAZE'' comment= ''Reset to original value by RMAN'' scope=spfile

sql statement: alter system reset  db_unique_name scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area     422670336 bytes

Fixed Size                     1344616 bytes
Variable Size                356518808 bytes
Database Buffers              58720256 bytes
Redo Buffers                   6086656 bytes
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "MAZE" RESETLOGS ARCHIVELOG
  MAXLOGFILES     16
  MAXLOGMEMBERS      3
  MAXDATAFILES      100
  MAXINSTANCES     8
  MAXLOGHISTORY      292
 LOGFILE
  GROUP  1 ( '/u01/app/oracle/oradata1/maze/redo01.log', '/u01/app/oracle/oraredo2/maze/redo04.log' ) SIZE 50 M  REUSE,
  GROUP  2 ( '/u01/app/oracle/oraredo2/maze/redo02.log' ) SIZE 50 M  REUSE,
  GROUP  3 ( '/u01/app/oracle/oraredo2/maze/redo03.log' ) SIZE 50 M  REUSE
 DATAFILE
  '/u01/app/oracle/oradata1/maze/system01.dbf'
 CHARACTER SET AL32UTF8


contents of Memory Script:
{
   set newname for tempfile  1 to
 "/u01/app/oracle/oradata1/maze/temp01.dbf";
   switch clone tempfile all;
   catalog clone datafilecopy  "/u01/app/oracle/oradata1/maze/sysaux01.dbf",
 "/u01/app/oracle/oradata1/maze/undotbs01.dbf",
 "/u01/app/oracle/oradata2/maze/users01.dbf",
 "/u01/app/oracle/oradata1/maze/example01.dbf",
 "/u01/app/oracle/oradata1/maze/rcts01.dbf",
 "/u01/app/oracle/oradata1/maze/undotbs02.dbf",
 "/u01/app/oracle/oradata1/maze/appstbs_01.dbf";
   switch clone datafile all;
   switch clone datafile  2 to datafilecopy
 "/u01/app/oracle/oradata1/maze/sysaux01.dbf";
   switch clone datafile  3 to datafilecopy
 "/u01/app/oracle/oradata1/maze/undotbs01.dbf";
   switch clone datafile  4 to datafilecopy
 "/u01/app/oracle/oradata2/maze/users01.dbf";
   switch clone datafile  5 to datafilecopy
 "/u01/app/oracle/oradata1/maze/example01.dbf";
   switch clone datafile  6 to datafilecopy
 "/u01/app/oracle/oradata1/maze/rcts01.dbf";
   switch clone datafile  7 to datafilecopy
 "/u01/app/oracle/oradata1/maze/undotbs02.dbf";
   switch clone datafile  8 to datafilecopy
 "/u01/app/oracle/oradata1/maze/appstbs_01.dbf";
}
executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to /u01/app/oracle/oradata1/maze/temp01.dbf in control file

cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata1/maze/sysaux01.dbf RECID=1 STAMP=813689760
cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata1/maze/undotbs01.dbf RECID=2 STAMP=813689760
cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata2/maze/users01.dbf RECID=3 STAMP=813689760
cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata1/maze/example01.dbf RECID=4 STAMP=813689760
cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata1/maze/rcts01.dbf RECID=5 STAMP=813689760
cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata1/maze/undotbs02.dbf RECID=6 STAMP=813689760
cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata1/maze/appstbs_01.dbf RECID=7 STAMP=813689760


datafile 2 switched to datafile copy
input datafile copy RECID=1 STAMP=813689760 file name=/u01/app/oracle/oradata1/maze/sysaux01.dbf

datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=813689760 file name=/u01/app/oracle/oradata1/maze/undotbs01.dbf

datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=813689760 file name=/u01/app/oracle/oradata2/maze/users01.dbf

datafile 5 switched to datafile copy
input datafile copy RECID=4 STAMP=813689760 file name=/u01/app/oracle/oradata1/maze/example01.dbf

datafile 6 switched to datafile copy
input datafile copy RECID=5 STAMP=813689760 file name=/u01/app/oracle/oradata1/maze/rcts01.dbf

datafile 7 switched to datafile copy
input datafile copy RECID=6 STAMP=813689760 file name=/u01/app/oracle/oradata1/maze/undotbs02.dbf

datafile 8 switched to datafile copy
input datafile copy RECID=7 STAMP=813689760 file name=/u01/app/oracle/oradata1/maze/appstbs_01.dbf
Reenabling controlfile options for auxiliary database
Executing: alter database add supplemental log data

contents of Memory Script:
{
   Alter clone database open resetlogs;
}
executing Memory Script

database opened
Finished Duplicate Db at 25-APR-13
RMAN>exit


SQL> select instance_name, host_name, status from v$instance;

INSTANCE_NAME    HOST_NAME                 STATUS
---------------- ------------------------- ------------
maze             lnx144                    OPEN

Your cloned database is ready to  use!!!

Remotely connecting to database with "sysdba" privilege

Remote login to a database with "sysdba" privileges.

I have two servers lnx133 & lnx144.

Goal is to be on server lnx144 & connect to database on lnx133 with a user having sysdba system privilege.
DB server lnx133 

Details:
lnx133 server has a database "maze"
lnx144 is remote server
password file --> found in $ORACLE_HOME/dbs needs to have an entry for the user with which we intend to remotely login.,

example "system"
On lnx133

SQL> grant sysdba to system;
Grant succeeded.

Below query gives confirmation
SQL> select * from v$pwfile_users;

USERNAME                       SYSDB SYSOP SYSAS
------------------------------                     ----- ----- -----
SYS                                     TRUE  TRUE  FALSE
SYSTEM                            TRUE  FALSE FALSE

You can also use strings command to read the binary file.

[oracle@lnx133 dbs]$ strings orapwmaze

]\[Z

ORACLE Remote Password file

INTERNAL

FB16A89DB241980E

c+'spTtN

F3A63E088B7E19ED

#fGF`B

SYSTEM

1A2A51E8BB0B485D

From any remote server ( in our case lnx144), I can use the following syntax to login to maze@lnx133 with "sysdba" privilege.

Note: we would need the entry in tnsnames.ora@lnx144 to be able to resolve to the database 'maze'@lnx133.

[oracle@lnx144 ~]$ tnsping maze

TNS Ping Utility for Linux: Version 11.2.0.2.0 - Production on 25-APR-2013 12:20:30

Copyright (c) 1997, 2010, Oracle.  All rights reserved.

Used parameter files:

/u01/app/oracle/product/11g/11.2.0.2/dbhome_1/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.133)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = maze)))

OK (0 msec)

Now we are ready to connect to remote database 'maze' on lnx133.

[oracle@lnx144 ~]$ sqlplus system/abc123@maze as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Thu Apr 25 12:21:02 2013

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

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select instance_name, host_name, status from v$instance;

INSTANCE_NAME    HOST_NAME                 STATUS

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

maze                       lnx133                                OPEN

So why would we need a user to login with sysdba system privilege? 

Well sysdba system privilege gives user privileges to startup, shutdown, create, drop, alter database (mount, open,  recover, backup, archivelog, restrict session..... )

To test with another user scott who doesn't have "sysdba" system privilege.

[oracle@lnx144 ~]$ sqlplus scott/abc123@maze

SQL*Plus: Release 11.2.0.2.0 Production on Thu Apr 25 12:24:09 2013

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

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> shut immediate;

ORA-01031: insufficient privileges

SQL> exit

Now lets try the same with a user "system" which has "sysdba" privilge.

[oracle@lnx144 ~]$ sqlplus system/abc123@maze as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Thu Apr 25 12:29:51 2013

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

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> shut immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> exit

Another important thing to be able to start up database by remote connection is only possible if you have a static entry in listener.ora (lnx133 in our case)... if you don't then you will be greeted by this error.

[oracle@lnx144 ~]$ sqlplus system/abc123@maze as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Thu Apr 25 13:03:21 2013

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

ERROR:

ORA-28547: connection to server failed, probable Oracle Net admin error

Update the static entry & reloaded listener, listener.ora now looks as follows

[oracle@lnx133 admin]$ cat listener.ora

# listener.ora Network Configuration File: /u01/app/oracle/product/11g/11.2.0.2/dbhome_1/network/admin/listener.ora

# Generated by Oracle configuration tools.

SID_LIST_LISTENER =

  (SID_LIST =

     (SID_DESC =

        (ORACLE_HOME = /u01/app/oracle/product/11g/11.2.0.2/dbhome_1)

          (SID_NAME = maze)

     )

  )

LISTENER =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

      (ADDRESS = (PROTOCOL = TCP)(HOST = lnx133)(PORT = 1521))

      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

    )

  )

ADR_BASE_LISTENER = /u01/app/oracle

Ready to start database remotely

[oracle@lnx144 ~]$ sqlplus system/abc123@maze as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Thu Apr 25 13:10:25 2013

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

Connected to an idle instance.

SQL> startup;

ORACLE instance started.

Total System Global Area  422670336 bytes

Fixed Size                  1344616 bytes

Variable Size             335547288 bytes

Database Buffers           79691776 bytes

Redo Buffers                6086656 bytes

Database mounted.

Database opened.

SQL> select instance_name, host_name, status from v$instance;

INSTANCE_NAME    HOST_NAME                 STATUS

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

maze                              lnx133                      OPEN

SQL> sho user

USER is "SYS"

I hope it helps.....administering remote databases!!!!

Using Datapump to move a table from Production to Development Database

Yeah nothing special but good to have it in here, hopefully beginners could get some help.

Prod -->
Take a backup at source database (in this case Production)
expdp username/password directory=dumpdir dumpfile=somefilename.dmp logfile=somefilename.log tables=schema.table_name

successful execution of the expdp command will create dump files in the directory "dmpdir" pointing to a location on disk(server).
scp those files from source to target (development database in our case).
$scp *dmp targetserver:/location_on_server

use the above dumps to import the tables in an existing same schema with a new name

impdp username/password directory=dmpdir dumpfile=abovedumpfilename.dmp logfile=somefilename.log
tables=earlier_username.table_name remap_table=earlier_username.table_name:new_table_name

Also if there are multiple dumpfiles you could use the following syntax

impdp username/password directory=dmpdir dumpfile=abovedumpfilename%U.dmp logfile=somefilename.log tables=earlier_username.table_name remap_table=earlier_username.table_name:new_table_name

Grid Installation on standalone Server

I hope this will help people with Grid Installation on standalone server, please leave in your comments to make this better.

Auto-start/stop of Oracle databases on RHEL 5

To configure Oracle databases to auto-start/stop by having the entry in /etc/oratab set to Y, requires some details to be worked on linux side. Following would require "root" privileges or co-ordination with system administrator.

Here are the steps to enable that configuration.
Step 1) Set the 3rd field in /etc/oratab to "Y" >>> this enables to oracle databases to auto-start

Step 2) As "root" user create a script dbora at following location >>> /etc/init.d
script name --> dbora
contents are as below

#!/bin/bash

# chkconfig: 35 99 10  

# Script to start and stops Oracle processes

# Set ORA_HOME to be equivalent to the $ORACLE_HOME

# from which you wish to execute dbstart and dbshut;

# Set ORA_OWNER to the user id of the owner of the

# Oracle database in ORA_HOME.

#

ORA_HOME= /app/oracle/product/11.2/dbhome_1

ORA_OWNER=oracle

case "$1" in

   'start')

      # Start the TNS Listener

      su - $ORA_OWNER -c "$ORA_HOME/bin/lsnrctl start"

      # Start the Oracle databases:

      # The following command assumes that the oracle login

      # will not prompt the user for any values

      su - $ORA_OWNER -c $ORA_HOME/bin/dbstart

      touch /var/lock/subsys/dbora

      ;;

   'stop')

       # Stop the TNS Listener

      su - $ORA_OWNER -c "$ORA_HOME/bin/lsnrctl stop"

      # Stop the Oracle databases:

      # The following command assumes that the oracle login

      # will not prompt the user for any values

      su - $ORA_OWNER -c $ORA_HOME/bin/dbshut

      rm -f /var/lock/subsys/dbora

      ;;

esac

# End of script dbora

The above script can include other components which might be called upon to start/stop in appropriate order. Here they only start/stop listener & database.

Step 3) Set script permissions:

chmod 755 /etc/init.d/dbora

Step 4) Register the Service /sbin/chkconfig --add dbora
This deals with just a simple set up, some variations may be encountered while you are dealing with different release of Oracle databases on same server & if databases are using ASM.

Unix know hows

As a DBA you are expected to know quite a few things about OS . This post intends to note down few things that every DBA comes across in day-day work.



$lsvg ---> on AIX to list out all the volume groups on server
$ vgdisplay --> on RHEL to list out all the volume groups
$lsvg -l vgname --> will list out the file systems part of the Volume group.

AIX
Total memory allocated --> lsattr -El sys0 | grep realmem (memory in KB)
or
prtconf -m

On linux
cat /proc/meminfo | grep MemTotal

Memory used of the total allocated-->
vmstat command will give the output
svmon -G
 svmon -GO unit=auto

find blocksize of a file system

$ dumpe2fs /dev/sda1 | grep 'Block size' ---> on RHEL

Finding network configuration--> lists all nic's, associated ip's & their status
/sbin/ifconfig -a

for finding the cpu usage
sar -u

for reading a file using vi but size of file is larger than default size of VI Editor.
vi -y newsize filename
example
vi -y 20000000 aler*g

find which files have been modified in the last few days
find location -mtime no_of_days
example--> lists the files that have been updated in last 2 days at the location /u01/app/oracle/product
find /u01/app/oracle/product -mtime 2

Comment all lines in a file
Open the file using 'vi' editor
Press Escape and type :1,$s/^/##/g
Hit return key

1,$s/^/##/g can be broken down as <------------ this can be understood as follows
1: To start from 1st line 
$s :To end at the last line
/^ : Search for the start of the line
/##:Replace with ##
/g: Replace all occurrences in this file (globally)


*** This happens quite a bit during house-keeping, to find out the largest files in a directory, specially where you have hundreds if not thousands of trace files.

du -ak | sort -n
or 
du -am | sort -n

In reverse order ( largest files to smallest.. )
du - ak | sort -nr

This one is handy to find old audit dumps & remove them>> go to audit dump location & run this below command
find . -name "*.aud" -mtime +600 -exec rm -rf {} \;


tar command
to create a tar file
tar cvf tar_file_name.tar dirname
above c --> create archive 
           v --> Verbose
           f --> following is the archive file name

To extract a tar file
tar xvf  tar_file_name.tar

To find sum of filesize of a particular pattern
SUM=0
for i in `ls -ls |grep FULL | nawk '{print $6}'`; do SUM=`expr $SUM + $i`; done
echo $SUM


To find the type of file system on linux
$ df -T 

Check the number of processors/cpu's in linux
$ grep processor /proc/cpuinfo

** to grep for a pattern involving spaces
for example you want to grep a line with Feb  6 in date
ls -ltr | grep "Feb  6"
please note that there are 2 spaces between "Feb" and "6"
for Feb 10
ls -ltr | grep "Feb 10"
only one single space

TSM_101 for DBA's

 Some handy commands to deal with storage management layers.

TSM

• To query if a particular backup file or a regular file on server has been backed up to TSM backup server >>>>>>>> dsmc q b "location/filename"

• To query if a particular archivelog on server has been backed up to TSM backup server >>>>>>>>> dsmc q ar "location/archivelogname"

• To restore backups from TSM backup server >>>>> dsmc restore "location/filename"
• To restore backups from TSM backup server to a different directory >> dsmc restore "source_directory_location" "destination_directory_location"

• To restore archivelogs from TSM backup server >>>>> dsmc ret "location/archivelogname"

• To restore from Older backups from TSM backup server  >>> dsmc  restore -inactive -pick -subdir=yes "location/*" -- It lists a series of files, just type the line number & enter O, it will restore.
• To query older backups from TSM backup server >>> dsmc q b -ina -subdir=yes "locationondisk"
• Say a file sample.txt has been backed on server lnx101 & we would like to restore this on server lnx202, there are two approaches
• One is to restore it on lnx101 & scp those files to lnx202
• Second is to restore it on lnx202 directly from tsm backup server, for this from lnx202 connect to tsm command line
• dsmc -se=ip_of_tsmbackupserver vitrualnode=lnx101                                                          here ip_of_tsmbackupserver can be found in dsm.opt file

• at tsm command line we can use the following commands                                             tsm>restore -ina -subdir=yes "location_on_source" "location_where_we_want_on_target"