Cloning a database manually

Using the following steps one can clone a database manually. In the following example PRD represents the source and DEV represents target/new database.
1) Get list of datafiles on the PRD database. In the following example this database has 3 datafiles.

SQL> SELECT name FROM v$datafile;
NAME
——————————————————————————–
/u03/oradata/PRD/system01.dbf
/u03/oradata/PRD/undotbs01.dbf
/u03/oradata/PRD/sysaux01.dbf

3 rows selected.

2) On the PRD (source) database, run the following query to find the last archived logs, the archive logs created after backup begin is run needs to be copied, so make a note of sequence # shown needs to be copied on the DEV (target) database to restore.

SQL> SELECT sequence#, TO_CHAR(next_time, ‘DD-MON-YYYY HH24:MI:SS’)
FROM (SELECT sequence#, next_time FROM V$archived_log ORDER BY next_time DESC)
WHERE rownum < 2;

SEQUENCE# TO_CHAR(NEXT_TIME,’D
———- ——————–
6 12-JAN-2009 20:40:15

3) Execute the following SQL to make the datafiles in backup mode.  This command is supported in 10g for 9i, each tablespace would need to placed in backup mode by using “ALTER TABLESPACE name BEGIN BACKUP;”

SQL> ALTER DATABASE BEGIN BACKUP;

Database altered.

4) Now the datafiles can be copied from PRD (source) to DEV (target) machine from the list created in step (1)

$ cd /u03/oradata/DEV
$ cp -pi /u03/oradata/PRD/system01.dbf .
$ cp -pi /u03/oradata/PRD/undotbs01.dbf .
$ cp -pi /u03/oradata/PRD/sysaux01.dbf .

4) Execute the following SQL to make the datafiles out of backup mode.

SQL> ALTER DATABASE END BACKUP;

Database altered.

5) Perform some logswitches on the PRD (source) database, this step will create archive logs on the source database.

SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;

System altered.

SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;

System altered.

SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;

System altered.

6) On the PRD (source) database, run the SQL in step (2) again to get the current sequence# of archived logs. The archived log files created for the sequence# between step (2) and step (6) would need to copied to the DEV (target) machine. In this example archive logs with sequence# 6-12 would need to be copied on the target host.

SEQUENCE# TO_CHAR(NEXT_TIME,’D
———- ——————–
14 12-JAN-2009 20:43:05

1 row selected.

7) Modify the pfile by copying the pfile from PRD (source) to target or if the source is using spfile, run the following SQL to create the pfile “CREATE PFILE=’/tmp/initDEV.ora’ TO SPFILE;” The parameters in the pfile needs to be modified for the target database. In the following example the following parameters where modified where “PRD” was replaced with “DEV”.

*.audit_file_dest=’/u01/app/oracle/admin/DEV/adump’
*.background_dump_dest=’/u01/app/oracle/admin/DEV/bdump’
*.user_dump_dest=’/u01/app/oracle/admin/DEV/udump’
*.core_dump_dest=’/u01/app/oracle/admin/DEV/cdump’
*.control_files=’/u03/oradata/DEV/control01.ctl’,'/u03/oradata/DEV/control02.ctl’,'/u03/oradata/DEV/control03.ctl’
*.db_name=’SMOXY’
*.log_archive_format=’DEV_%t_%s_%r.arc’
*.log_archive_dest_1=’LOCATION=/u03/oradata/DEV/arch’

8 ) On the PRD (source) instance create the backup control file to trace using the following SQL

SQL> ALTER DATABASE BACKUP CONTROLFILE TO TRACE;

Database Altered

Modify the control file creation script in udump folder, section where the resetlogs and change REUSE TO SET, modify the name PRD to DEV, change the path of the datafiles if needed, only the following section would needs to be run.

CREATE CONTROLFILE SET DATABASE “DEV” RESETLOGS ARCHIVELOG
MAXLOGFILES 50
MAXLOGMEMBERS 5
MAXDATAFILES 500
MAXINSTANCES 1
MAXLOGHISTORY 2045
LOGFILE
GROUP 1 (
‘/u03/oradata/DEV/redo01a.log’,
‘/u03/oradata/DEV/redo01b.log’
) SIZE 100M,
GROUP 2 (
‘/u03/oradata/DEV/redo02a.log’,
‘/u03/oradata/DEV/redo02b.log’
) SIZE 100M,
GROUP 3 (
‘/u03/oradata/DEV/redo03a.log’,
‘/u03/oradata/DEV/redo03b.log’
) SIZE 100M,
GROUP 4 (
‘/u03/oradata/DEV/redo04a.log’,
‘/u03/oradata/DEV/redo04b.log’
) SIZE 100M
DATAFILE
‘/u03/oradata/DEV/system01.dbf’,
‘/u03/oradata/DEV/undotbs01.dbf’,
‘/u03/oradata/DEV/sysaux01.dbf’
CHARACTER SET WE8ISO8859P1;

9) On the target machine, and start the instance in nomount, make sure it will use the pfile created in step (7

export ORACLE_SID=DEV
SQL> startup nomount

10) Using the script create the control file created in step ( 8 )

11) Now database can be recovered, at this step Oracle will prompt for the archive logs copied in step (2) and (6) or a specific point in time can be specified. When all the archive logs are applied, type CANCEL
SQL> RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL;

ORA-00279: change 9603 generated at 01/12/2009 20:40:43 needed for thread 1
ORA-00289: suggestion : /u03/oradata/DEV/arch/DEV_1_9_675981354.arc
ORA-00280: change 9603 for thread 1 is in sequence #9

21:07:48 Specify log: {=suggested | filename | AUTO | CANCEL}
/u03/oradata/DEV/arch/DEV_1_9_675981354.arc
ORA-00279: change 9614 generated at 01/12/2009 20:40:56 needed for thread 1
ORA-00289: suggestion : /u03/oradata/DEV/arch/DEV_1_10_675981354.arc
ORA-00280: change 9614 for thread 1 is in sequence #10
ORA-00278: log file ‘/u03/oradata/DEV/arch/DEV_1_9_675981354.arc’ no longer needed for this recovery

……..

21:11:25 Specify log: {=suggested | filename | AUTO | CANCEL}
CANCEL

Media recovery cancelled.

Or (for point in time recovery)
SQL> RECOVER DATABASE UNTIL TIME ’2009-01-11:15:14:30′ USING BACKUP CONTROLFILE;
…
Media recovery complete

12) Now the database can be opened with the reset logs option.
ALTER DATABASE OPEN RESETLOGS;

Database altered.

13) Add datafile to temporary tablespace, the size of the datafile can be adjusted as needed.
SQL> ALTER TABLESPACE TEMP ADD TEMPFILE ‘/u03/oradata/DEV/temp01.dbf’ SIZE 100M REUSE AUTOEXTEND OFF;

14) Change global name of the cloned database
SQL> ALTER DATABASE RENAME global_name TO DEV;

SQL> SELECT * FROM global_name;

GLOBAL_NAME
——————————————————————————–
DEV

Writing a message to the alert log for testing

SQL> execute sys.dbms_system.ksdwrt(2,('ORA-07445: Nadvi is testing AlertLog Monitoring'));

Now check the alert log.

[oracle@lab trace]$ tail -f alert*

Thu Feb 10 11:30:28 2011

ORA-07445: Nadvi is testing AlertLog Monitoring

Changing SQL*Plus prompt

Below is the straight forward syntax according to your choice.

SQL> set sqlprompt 'nadvi@TESTDATABASE> '

nadvi@TESTDATABASE>

And is below one is querying database to specify exact user & hostname:

SQL> undefine usr db

col usr new_value usr

col db new_value db

set termout off

select lower(user) usr, instance_name db

from v$instance

/

set termout on

set sqlprompt '&&usr.@&&db.> 'SQL> SQL> SQL> SQL>   2    3

USR                            DB

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

sys                            testdatabase

SQL> SQL>sys@testdatabase>

Generate DDL for all Database Links & Indexes

Below script is going to generate DDL for DBLINK with values instead of password. So that you can create links without knowing the password.

set pagesize 0

set long 90000

SELECT DBMS_METADATA.GET_DDL('DB_LINK',a.db_link,a.owner) || '/' FROM dba_db_links a

/

The following can be used to create a script for DB Links without password.

The SQL script for creating the DB Link would need to be run the user who owns the DB Link if it’s not a PUBLIC.

SELECT 'create '||DECODE(U.NAME,'PUBLIC','public ')||'database link '||CHR(10)

||DECODE(U.NAME,'PUBLIC',Null, U.NAME||'.')|| L.NAME||chr(10)

||'connect to ' || L.USERID || ' identified by '

||L.PASSWORD||' using ''' || L.host || ''''

||chr(10)||';' TEXT

FROM sys.link$ L, sys.user$ U

WHERE L.OWNER# = U.USER#;

Script to generate Index DDL from a table with DBMS_METADATA.GET_DEPENDENT_DDL

Here is a simple script to generate the DDL code for all indexes in a particular table.

set heading off;

set echo off;

Set pages 999;

set long 90000;

SELECT DBMS_METADATA.GET_DEPENDENT_DDL('INDEX',UPPER('&table_name'),UPPER('&schema_name'))

from dual

/

set heading on;

set echo on;

Script to find Oracle Bind Variables

Simple script to find bind variables with or without sql_id.

select

sql_id,

t.sql_text SQL_TEXT,

b.name BIND_NAME,

b.value_string BIND_STRING

from

v$sql t

join v$sql_bind_capture b

using (sql_id)

where

b.value_string is not null

– and sql_id=’974ju5zc5whfn’;

Cloning Oracle Database Software

Cloning an 11gR2 Oracle Home from source machine PROD to target machine TEST.

On source (PROD):

ORACLE_HOME location is : /u01/app/oracle/product/11.2.0/dbhome_1

Go to directory on source machine one level higher and tar the db_home1 directory and all sub-directories

PROD:$ pwd

PROD:$ /u01/app/oracle/product/11.2.0  
PROD:$ tar -cvf 11gr2.tar dbhome_1

On Target (TEST)
Create the directory /u01/app/oracle/product/11.2.0 if it does not exist

Mkdir –p /u01/app/oracle/product/11.2.0

On Source

scp or ftp the tar file to this location on the target machine

PROD:$ scp -rp 11gr2.tar oracle@TEST:/u01/app/oracle/product/11.2.0  

On target

Uncompress the tar file

TEST:$ cd /u01/app/oracle/product/11.2.0

TEST:$ tar -xvf 11gr2.tar

Clone the Oracle Home using the perl script clone.pl which is located under the $ORACLE_HOME/clone/bin

TEST:$ cd /u01/app/oracle/product/11.2.0/dbhome_1/clone/bin

TEST:$ perl clone.pl ORACLE_BASE=/u01/app/oracle ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1 ORACLE_HOME_NAME=11GR2_HOME

./runInstaller -clone -waitForCompletion  "ORACLE_BASE=/u01/app/oracle" "ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1" "ORACLE_HOME_NAME=11GR2_HOME" -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:

 /u01/app/oracle/oraInventory/logs/cloneActions2010-06-28_12-19-47PM.log

.................................................................................................... 100% Done.

Installation in progress (Monday, June 28, 2010 12:20:29 PM WST)

..............................................................................                                                  78% Done.

Install successful

Linking in progress (Monday, June 28, 2010 12:21:06 PM WST)

Link successful

Setup in progress (Monday, June 28, 2010 12:23:34 PM WST)

Setup successful

End of install phases.(Monday, June 28, 2010 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 "/u01/app/oracle/product/11.2.0/dbhome_1/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 "/u01/app/oracle/product/11.2.0/dbhome_1/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.

/u01/app/oracle/product/11.2.0/dbhome_1/root.sh

To execute the configuration scripts:

    1. Open a terminal window

    2. Log in as "root"

    3. Run the scripts

The cloning of 11GR2_HOME was successful.

Please check '/u01/app/oracle/oraInventory/logs/cloneActions2010-06-28_12-19-47PM.log' for more details.

Run the root.sh script as root

# pwd

/u01/app/oracle/product/11.2.0/dbhome_1

# ./root.sh

Check /u01/app/oracle/product/11.2.0/dbhome_1/install/root_PROD_2010-06-29_07-42-31.log for the output of root script

TEST:$cat /u01/app/oracle/product/11.2.0/dbhome_1/install/root_TEST_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=  /u01/app/oracle/product/11.2.0/dbhome_1

Entries will be added to the /var/opt/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.

We can now check that the OraInventory has been updated with the 11g Release 2 Oracle Home details.

Oracle password with @ or Special characters

First let's see what it looks like from within sqlplus:

sqlplus /nolog

SQL> alter user scott identified by "scott@test";
User altered.

SQL> connect scott/"scott@test";
Connected.

SQL> select user from dual;
USER
------------------------------
SCOTT

SQL> connect scott/ scott@test
ERROR:
ORA-12154: TNS:could not resolve the connect identifier specified

As long as the connect string was in double quotes, no problem.

Now let's try it from a linux command line:

Linux> sqlplus scott/'scott@test'
ORA-12154: TNS:could not resolve the connect identifier specified

Linux> sqlplus scott/'scott\@test'
ERROR:
ORA-01017: invalid username/password; logon denied

Linux> sqlplus scott/"scott\@test"
ERROR:
ORA-01017: invalid username/password; logon denied

Linux> sqlplus 'scott/scott\@test'
ERROR:
ORA-01017: invalid username/password; logon denied

Linux > sqlplus scott/scott\@test
ERROR:
ORA-12154: TNS:could not resolve the connect identifier speci

Nothing worked!!!

Now finally find the below syntax and works like a charm!

sqlplus scott/\”scott@test\”@TEST_DB

Though it worked, but I am not going to use ‘@’ anymore with oracle users.

Lessons learned!!

Oracle ASM for Oracle GoldenGate extract

Oracle GoldenGate supports data capture from an Oracle Database using ASM. A few additional setup steps are required in order to use ASM for Oracle redo and/or archive logs:

• Extract requires a connection into an ASM instance to be able to read the transaction logs. The connection has to go through the Oracle Database listener and because the ASM instance is only mounted (not open) an entry for the ASM instance must be added to the listener configuration file in order to let incoming connections go through.

See the Oracle GoldenGate for Windows and Unix Administrator Guide as well as Oracle Support note 340277.1 for more details.

The solution is to

1.    Edit the listener.ora on ASM server  

2.    Edit the tnsnames.ora on the client

3.    Setup your remote login password for your ASM instance on the ASM server

4.    Set your SYS password using ORAPWD for the ASM instance

5.    Use the properly formatted connect string to connect with your tool

1) Edit the listener.ora on ASM server

a) Logon to the ASM/Database server

b) Locate your listener.ora (typically located in the $ORACLE_HOME/network/admin)

c) Add a SID_LIST_LISTENER entry for your ASM instance (see example below)

SID_LIST_LISTENER_RAC1 =

  (SID_LIST =

    (SID_DESC =

      (GLOBAL_DBNAME = ASM)

      (ORACLE_HOME = /u01/app/oracle/product/11.1.0/db_1)

      (SID_NAME = +ASM1)

    )

  )

  d) Stop the listener

lsnrctl stop

  

e) restart the listener

lsnrctl start

2) Edit the tnsnames.ora on the client

   a) Logon to the client machine that will be used to connect to the ASM instance

          NOTE: the client machine can also be the ASM/Database server (ie for DBCONTROL)

   b) Locate your tnsnames.ora (typically in the $ORACLE_HOME/network/admin)

   c) Add an entry (tnsalias) for your ASM instance (see example)

EXAMPLE

ASM =

  (DESCRIPTION =

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

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = +ASM1)

    )

  )

3) Setup your remote login password for your ASM instance on the ASM server

   a) Logon to the ASM/Database server

   b) Locate the parameter file for your ASM instance (typically

$ORACLE_HOME/dbs/init+ASM.ora [unix] or $ORACLE_HOME/dbs/init+ASM.ora [windows] )

   c) Edit the parameter file and add

remote_login_passwordfile = exclusive ... for stand alone ASM setups

remote_login_passwordfile = shared ... for ASM setups that also use Real Application Cluster (RAC)

 

   d) Save the file

4) Set your SYS password using ORAPWD for the ASM instance

   a) Logon to the ASM/Database server

   b) Locate your orapw file for your ASM instance (typically $ORACLE_HOME/dbs/orapw+ASM)

   c) Rename the file to orapw.old

   d) Run orapwd to reset the password (see example below)

EXAMPLE

mv "orapw+ASM1" "orapw+ASM.old"

orapwd file=orapw+ASM1 password=DBA******

5) Use the properly formatted connect string for your tool

Connnect to the ASM instance using the password (#4 above) and the tnsalias (#2 above)

EXAMPLES

sqlplus "sys/******@asm as sysdba"

sqlplus "sys@asm as sysdba" ... then supply the password when prompted