Using autotrace in SQL*Plus

autotrace supports the following options:

• on – Enables all options.
• on explain – Displays returned rows and the explain plan.
• on statistics – Displays returned rows and statistics.
• trace explain – Displays the execution plan for a select statement without actually executing it. (set autotrace trace explain)
• traceonly – Displays execution plan and statistics without displaying the returned rows. This option should be used when a large result set is expected.

set autotrace off
set autotrace on
set autotrace traceonly

set autotrace on explain
set autotrace on statistics
set autotrace on explain statistics

set autotrace traceonly explain
set autotrace traceonly statistics
set autotrace traceonly explain statistics

set autotrace off explain
set autotrace off statistics
set autotrace off explain statistics

Prerequisites
The explain plan feature of autotrace requires a plan_table which can be created with $ORACLE_HOME/rdbms/admin/utlxplan.sql

The statistic feature requires that the user is granted select on v_$sesstat, v_$statname and v_$session.

An Oracle installation comes with $ORACLE_HOME/sqlplus/admin/plustrce.sql which installs the role plustrace. plustrace is granted those select rights. If now plustrace is granted to a user, he will then be able to turn autotrace on. Alternatively, plustrace can be granted to public.

SQL> set timing on
SQL> set autotrace on
SP2-0618: Cannot find the Session Identifier. Check PLUSTRACE role is enabled
SP2-0611: Error enabling STATISTICS report

Solution:
To use this feature, you must have the PLUSTRACE role granted to you and a PLAN_TABLE table created in your schema

Connect as system/manager

SQL> @?\sqlplus\admin\plustrce.sql;
SQL> grant plustrace to scott;
SQL> conn scott/tiger@sample;
SQL> @?/rdbms/admin/utlxplan.sql;
SQL> set autotrace on explain
SQL> select sysdate from dual;

SYSDATE
---------
22-AUG-08

Elapsed: 00:00:00.06

Execution Plan
----------------------------------------------------------
Plan hash value: 1388734953

-----------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-----------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2 (0)| 00:00:01 |
| 1 | FAST DUAL | | 1 | 2 (0)| 00:00:01 |
-----------------------------------------------------------------


SQL> set autotrace on statistics;
SP2-0618: Cannot find the Session Identifier. Check PLUSTRACE role is enabled
SP2-0611: Error enabling STATISTICS report

Renaming datafies in single/multiple tablespaces

Procedure for Renaming Datafiles in a Single Tablespace

To rename datafiles in a single tablespace, complete the following steps:

1.Take the tablespace that contains the datafiles offline. The database must be open.

For example:

ALTER TABLESPACE users OFFLINE NORMAL;

2. Rename the datafiles using the operating system.

3. Use the ALTER TABLESPACE statement with the RENAME DATAFILE clause to change the filenames within the database.

For example, the following statement renames the datafiles /u02/oracle/rbdb1/user1.dbf and /u02/oracle/rbdb1/user2.dbf to/u02/oracle/rbdb1/users01.dbf and /u02/oracle/rbdb1/users02.dbf, respectively:

ALTER TABLESPACE users
RENAME DATAFILE '/u02/oracle/rbdb1/user1.dbf',
'/u02/oracle/rbdb1/user2.dbf'
TO '/u02/oracle/rbdb1/users01.dbf',
'/u02/oracle/rbdb1/users02.dbf';

Always provide complete filenames (including their paths) to properly identify the old and new datafiles. In particular, specify the old datafile name exactly as it appears in the DBA_DATA_FILES view of the data dictionary.

4. Back up the database. After making any structural changes to a database, always perform an immediate and complete backup.

Procedure for Relocating Datafiles in a Single Tablespace

Here is a sample procedure for relocating a datafile.

Assume the following conditions:

* An open database has a tablespace named users that is made up of datafiles all located on the same disk.

* The datafiles of the users tablespace are to be relocated to different and separate disk drives.

* You are currently connected with administrator privileges to the open database.

* You have a current backup of the database.

Complete the following steps:

1. If you do not know the specific file names or sizes, you can obtain this information by issuing the following query of the data dictionary view DBA_DATA_FILES:

SQL> SELECT FILE_NAME, BYTES FROM DBA_DATA_FILES
2> WHERE TABLESPACE_NAME = 'USERS';

FILE_NAME BYTES
------------------------------------------ ----------------
/u02/oracle/rbdb1/users01.dbf 102400000
/u02/oracle/rbdb1/users02.dbf 102400000

2. Take the tablespace containing the datafiles offline:

ALTER TABLESPACE users OFFLINE NORMAL;

3. Copy the datafiles to their new locations and rename them using the operating system. You can copy the files using the DBMS_FILE_TRANSFER package discussed in "Copying Files Using the Database Server".

Note:
You can temporarily exit SQL*Plus to execute an operating system command to copy a file by using the SQL*Plus HOST command.

4. Rename the datafiles within the database.

The datafile pointers for the files that make up the users tablespace, recorded in the control file of the associated database, must now be changed from the old names to the new names.

Use the ALTER TABLESPACE...RENAME DATAFILE statement.

ALTER TABLESPACE users
RENAME DATAFILE '/u02/oracle/rbdb1/users01.dbf',
'/u02/oracle/rbdb1/users02.dbf'
TO '/u03/oracle/rbdb1/users01.dbf',
'/u04/oracle/rbdb1/users02.dbf';

5.Back up the database. After making any structural changes to a database, always perform an immediate and complete backup.

Procedure for Renaming and Relocating Datafiles in Multiple Tablespaces

You can rename and relocate datafiles in one or more tablespaces using the ALTER DATABASE RENAME FILE statement. This method is the only choice if you want to rename or relocate datafiles of several tablespaces in one operation. You must have the ALTER DATABASE system privilege.

Note:
To rename or relocate datafiles of the SYSTEM tablespace, the default temporary tablespace, or the active undo tablespace you must use this ALTER DATABASE method because you cannot take these tablespaces offline.

To rename datafiles in multiple tablespaces, follow these steps.

1. Ensure that the database is mounted but closed.

Note:
Optionally, the database does not have to be closed, but the datafiles (or tempfiles) must be offline.

2. Copy the datafiles to be renamed to their new locations and new names, using the operating system. You can copy the files using the DBMS_FILE_TRANSFER package discussed in "Copying Files Using the Database Server".

3. Use ALTER DATABASE to rename the file pointers in the database control file.

For example, the following statement renames the datafiles/u02/oracle/rbdb1/sort01.dbf and /u02/oracle/rbdb1/user3.dbf to /u02/oracle/rbdb1/temp01.dbf and /u02/oracle/rbdb1/users03.dbf, respectively:

ALTER DATABASE
RENAME FILE '/u02/oracle/rbdb1/sort01.dbf',
'/u02/oracle/rbdb1/user3.dbf'
TO '/u02/oracle/rbdb1/temp01.dbf',
'/u02/oracle/rbdb1/users03.dbf;

Always provide complete filenames (including their paths) to properly identify the old and new datafiles. In particular, specify the old datafile names exactly as they appear in the DBA_DATA_FILES view.

4. Back up the database. After making any structural changes to a database, always perform an immediate and complete backup.

Restore Point in Flashback Database

Remember the concept of savepoints in SQL? In a transaction, you can create a savepoint, make some modifications, create another savepoint, and so on. If the changes are not what you expected, all you have to do is roll them back to a specific savepoint.

Now pan over to a new functionality introduced in Oracle Database 10g, Flashback Database, which allows you to rewind the database to a previous point in time. Wouldn't it be nice to have functionality similar to savepoint in this situation—that is, to be able to rewind to a specific named point, not just a point in time?

In Oracle Database 10g Release 2, you can do that using a new functionality called restore points. Here's how it works. Suppose you have a long month-end processing involving several batch programs you have to run sequentially. Here is the sequence of events:

1. Create a restore point rp1
2. Run batch job 1
3. Create a restore point rp2
4. Run batch job 2

and so on. The batch job 2 fails in the middle of execution, and you need to take the database to a consistent state. You don't have to take it all the way to the beginning of the run. Because the restore point rp2 was created before the execution of the batch job, you can simply flashback the database to that restore point.

You create a restore point with

create restore point before_monthend_200503;

Restore point BEFORE_MONTHEND_200503 is now created based on the current database time and SCN. If you want to ensure that the database can be flashed back to a particular restore point, you can specify a guarantee by creating guaranteed restore points as shown below:

create restore point before_monthend_200503
guarantee flashback database;

You can confirm the existence of this restore point by SELECTing from a dynamic performance view V$RESTORE_POINT:

SQL> select * from v$restore_point;

SCN DATABASE_INCARNATION# GUA STORAGE_SIZE
---------- --------------------- --- ------------
TIME
---------------------------------------------------
NAME
---------------------------------------------------

1429811 1 YES 8192000
27-MAR-05 05.18.39.000000000 PM
BEFORE_MONTHEND_200503

Later when you want to flashback the database to that restore point, you could simply issue:

flashback database to restore point before_monthend_200503;

If you examine the alert log, it will show a line similar to:

Media Recovery Applied UNTIL CHANGE 1429814

Restore points—especially guaranteed restore points—are quite useful in many database-related tasks. A good example is QA databases, where you may want to establish a restore point, run some tests, and flashback to the restore point to make the database look as if nothing happened. Then you can perform another round of testing and again restore it to the restore point.

Peek into the Flash Recovery Area

You may have configured Flash Recovery Area to back up different types of files. But how do you know what types of backups are available there?

A new view, V$FLASH_RECOVERY_AREA_USAGE, shows what's available in the flashback area.

SQL> select * from V$FLASH_RECOVERY_AREA_USAGE;

FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
------------ ------------------ ------------------------- ---------------
CONTROLFILE 0 0 0
ONLINELOG 0 0 0
ARCHIVELOG .02 .02 1
BACKUPPIECE 68.98 1.02 10
IMAGECOPY 0 0 0
FLASHBACKLOG .95 0 3

Using this view you can immediately see what kind of files are available in the Flash Recovery Area. It only shows a percentage however, so how do you determine actual values? Simply query the view $RECOVERY_FILE_DEST.

SQL> select * from V$RECOVERY_FILE_DEST;

NAME
----------------------------------------------------------
SPACE_LIMIT SPACE_USED SPACE_RECLAIMABLE NUMBER_OF_FILES
----------- ---------- ----------------- ---------------
/home/oracle
2147483648 1502122496 22201856 14

This query shows that the total size of the recovery area is 16384000. Flashback logs occupy 0.95% of the column SPACE_LIMIT as shown in the previous query, so you can calculate the actual size of the space occupied. It also shows you how much space can be reclaimed from the different types of backups in the Flash Recovery Area. For instance, you can reclaim 1.02% of the space occupied by backup pieces, perhaps due to obsolete backups. Using this view you can make intelligent predictions about Flash Recovery Area usage and sizing.

Source: Oracle 10g R2 Top Features for DBAs

Master table reorganization with a primary key materialized view

An example is as follows:

1) This test is done with the scott.dept table

connect scott/tiger

2) If the dept table do not have primary key, add it using the following command:

alter table dept add constraint pk_dept primary key(deptno);

3) As a primary key exists on master table (dept), default materialized view log is
created with primary key. In case a primary key does not exist on the master table
an error ORA-12014 will be raised.

create materialized view log on dept;

4) Creation of materialized view ( default is primary key):
ORA-23415 will be raised in case the materialized view log do not record the
primary key

create materialized view s_dept refresh fast as select * from dept;

5) Perform insert in materialized view and commit it:

insert into dept (deptno) values (49);
commit;

If you select from the materialized view log you should see a row count of 1.

select count(*) from mlog$_dept;
1 row selected

6) Now we want to perform a reorganization of the master table
The below procedure will devalidate internal triggers which populates
the materialized view log when modification are done on the master table:

execute DBMS_MVIEW.BEGIN_TABLE_REORGANIZATION('SCOTT','DEPT');

7) Now export the dept table:
exp scott/tiger tables=dept

8) Truncate the table to prepare it for import.

truncate table dept;

9) Import table dept.

imp scott/tiger tables=dept ignore=y

10) Turn on internals triggers.

execute DBMS_MVIEW.END_TABLE_REORGANIZATION('SCOTT','DEPT');

11) Refresh the materialized view, default is refresh method indicated in DBA_MVIEWS.

execute dbms_mview.refresh('S_DEPT','f');

12) If the materialized view is a rowid materialized view, then the error
ORA-12034 will appear at refresh time, and the materialized view will need
a complete refresh.

execute dbms_mview.refresh('S_DEPT','c');

Source: Metalink ID 254593.1

Block storage parameters

PCTFREE - The percentage of space reserved for future update of existing data.

PCTUSED - The percentage of minimum space used for insertion of new row data. This value determines when the block gets back into the FREELISTS structure.

FREELIST - Structure where Oracle maintains a list of all free available blocks.

Suppose you have specified PCTUSED as 40 %. and PCTFREE as 10 %.

• Oracle will keep on inserting new rows till the space is 90 % used. It will reserve the remaining 10% for future updates.
• To start adding new rows again to the block, Oracle will check the space in the Block and the PCTUSED parameter.
• When the space falls below 40 %, Oracle will start adding new rows to the block.

Automatic Segment Space Management (ASSM) tablespaces automate freelist management and remove the ability to specify PCTFREE, PCTUSED, FREELISTS, and FREELIST GROUPS storage parameters.

Each segment has one or more freelist (in the segment header) that keeps track of data blocks under the high-water mark. When records are inserted into the segment, Oracle examines the freelist to determine which data blocks have available space to store the new rows. When rows are deleted (or updated), and the amount of free space in the data block falls below a threshold value (PCTUSED), the data block will be returned to the freelist. The high water mark is the boundary between used and unused space in a segment.

more...

more...

startup migrate/startup upgrade

startup migrate
=> Used to upgrade a database till 9i.
See the step 11 from the 9.2 manual upgrade guide :
http://download-east.oracle.com/docs/cd/B10501_01/server.920/a96530/upgrade.htm#1009472

=> Used to downgrade a database till 9i.
See the step 5 from the 9.2 downgrade guide :
http://download-east.oracle.com/docs/cd/B10501_01/server.920/a96530/downgrad.htm#248958

=> Used to downgrade a database since 10g.
See the step 18 from the 10.2 downgrade guide :
http://download-east.oracle.com/docs/cd/B19306_01/server.102/b14238/downgrade.htm#sthref415

startup upgrade
=> Used to upgrade a database since 10g.
See the step 7 from the 10.2 manual upgrade guide :
http://download-east.oracle.com/docs/cd/B19306_01/server.102/b14238/upgrade.htm#CACGGHJC

Database Migration from Solaris to AIX

Check Pre-Conditions on database SRTR1

Check list of platforms that we can convert source database into as below.

SQL> SELECT * FROM v$db_transportable_platform;

Shutdown and start newly created SRTR1 database in "READ ONLY" mode.

SQL> shutdown immediate;
Comment out below parameters:
*.audit_trail ='DB'
*.audit_file_dest='/backup/SRTR1/oradata'

SQL> startup mount;
SQL> alter database open read only;

SQL> select name,open_mode from v$database;

NAME OPEN_MODE
--------- ----------
SRTR1 READ ONLY


Transfer SRTR1 files from Solaris to AIX

Backup control file to trace using the below command and shutdown database
SQL> alter database backup controlfile to trace;

The trace file is generated in the udump directory by the format xxxxxx.trc. Copy the control file trace to AIX_Server:/backup.

Copy the init.ora file of SRTR1 to AIX_Server:/backup

$ scp initSRTR1.ora AIX_Server:/backup/SRTR1

Go to /optware/oracle/oradata on Solaris_Server and run the below command to get a count of the datafiles of SRTR1

Sql> spool SRTR1_data_file_count.log
Sql> select count(*) from dba_data_files;
Sql> spool off

Shutdown the database SRTR1 on Solaris_Server.

Sql > select instance_name from v$instance;

SRTR1

Sql> shutdown immediate;

Change back the init parameters and copy the init.ora file of SRTR1 to AIX_Server:/backup
$ cd /optware/oracle/SRTR1
Delete below entries:
*.db_cache_size =1000M
*.pga_aggregate_target =2000M
*.shared_pool_size =1000M

Uncomment below parameters:
*.db_cache_size =4000M
*.pga_aggregate_target =9000M
*.shared_pool_size =5000M

$ cd /optware/oracle/SRTR1
$ scp initSRTR1.ora AIX_Server:/backup/SRTR1

Comment out SRTR1 entries in oratab file to avoid future auto startup of the database incase server is rebooted.
$cd /var/opt/oracle
And comment out below entry:

SRTR1:/optware/oracle/10.2.0.4:Y

Transfer the files from Solaris_Server to AIX_Server

Make directories with 50Gb each in size within the filesystems to have the datafiles to be copied in multiple streams. Below are the directories to be created:

/u11=171GB db=57.3 GB db1=57.9 GB db2=56.3 GB
$ cd /backup/SRTR1/u11
$ mkdir db db1 db2

$ mv *hrlarge.0*dbf db; mv *hrlarge.12.dbf db; mv *hrlarge.13.dbf db
$ mv *hrlarge*.dbf db1; mv *usrtblspc*dbf db1;mv srtr1.pttbl.01.dbf db1
$ mv *.dbf db2

cd /backup/SRTR1/u11/db
tar -cvf - *.dbf | compress | ssh AIX_Server "cd /backup/u11 ;zcat | tar xvf -" &

cd /backup/SRTR1/u11/db1
tar -cvf - *.dbf | compress | ssh AIX_Server "cd /backup/u11 ;zcat | tar xvf -" &

cd /backup/SRTR1/u11/db2
tar -cvf - *.dbf | compress | ssh AIX_Server "cd /backup/u11 ;zcat | tar xvf -" &


/u13=169.8 GB db=57.3 GB db1= 56.6 GB db2= 55.8 GB
$ cd /backup/SRTR1/u13
$ mkdir db db1 db2

$ mv *hrlarge.0*dbf db; mv *hrlarge.10.dbf db; mv *hrlarge.11.dbf db; mv *hrlarge.14.dbf db
$ mv *hrlarge*.dbf db1; mv *ewlarge.0* db1;
$ mv *.dbf db2; mv db1/srt1.hrlarge.27.dbf db2;

cd /backup/SRTR1/u13/db
tar -cvf - *.dbf | compress | ssh AIX_Server "cd /backup/u13 ;zcat | tar xvf -" &

cd /backup/SRTR1/u13/db1
tar -cvf - *.dbf | compress | ssh AIX_Server "cd /backup/u13 ;zcat | tar xvf -" &

cd /backup/SRTR1/u13/db2
tar -cvf - *.dbf | compress | ssh AIX_Server "cd /backup/u13 ;zcat | tar xvf -" &


/u02=4.6 GB
cd /backup/SRTR1/u02
tar -cvf - *.dbf | compress | ssh AIX_Server "cd /backup/u02 ;zcat | tar xvf -" &

/u09=4.2 GB
cd /u11/GDPFCNV1/u09
tar -cvf - *.dbf | compress | ssh AIX_Server "cd /backup/u09 ;zcat | tar xvf -" &

Stop here, once /u02 and /u09 finish, start /12 and /14

/u12=106.9 GB db=52.8 GB db1=54.1 GB
$ cd /backup/SRTR1/u12
$ mkdir db db1

$ mv *psindex.0*dbf db; mv *psindex.11.dbf db; mv *psindex.13.dbf db; mv *psindex.15.dbf db; mv *psindex.17.dbf db
$ mv *.dbf db1

cd /backup/SRTR1/u12/db
tar -cvf - *.dbf | compress | ssh AIX_Server "cd /backup/u12 ;zcat | tar xvf -" &

cd /backup/SRTR1/u12/db1
tar -cvf - *.dbf | compress | ssh AIX_Server "cd /backup/SRTR1/u12 ;zcat | tar xvf -" &

/u14=92.69 GB db=48.12 GB db1=44.56 GB
$ cd /u11/SRTR1/u14
$ mkdir db db1
$ mv *.0* db; mv *.1* db
$ mv *.dbf db1


cd /u11/SRTR1/u14/db
tar -cvf - *.dbf | compress | ssh AIX_Server "cd /backup/SRTR1/u14 ;zcat | tar xvf -" &

cd /u11/SRTR1/u14/db1
tar -cvf - *.dbf | compress | ssh AIX_Server "cd /backup/SRTR1/u14 ;zcat | tar xvf -" &



Also make sure that you have copied the correct control file trace generated and init.ora files to /backup on AIX_Server
Locations of the files on Solaris_Server:
a. trace file location: /backup/SRTR1/oradump
b. init.ora file location: /optware/oracle/SRTR1

Check if all datafiles have been copied correctly

Note: After completion of above confirm the transfer of files.

Ways to check the datafiles have been copied correctly

Compare the datafile count of the file systems of PHPRDH and SRTR1. Run PHPRDH_datafile_count.ksh and SRTR1_datafile_count.ksh

Ex- cat SRTR1_datafile_count.ksh on SOLARIS

ls -ltr /backup/SRTR1/d11/*.dbf | wc -l
ls -ltr /backup/SRTR1/d13/*.dbf | wc -l
ls -ltr /backup/SRTR1/d12/*.dbf | wc -l
ls -ltr /u11/SRTR1/d14/*.dbf | wc -l
ls -ltr /backup/SRTR1/d02/*.dbf | wc -l
ls -ltr /u11/SRTR1/d09/*.dbf | wc –l

cat SRTR1_datafile_count.ksh on AIX

ls -ltr /backup/SRTR1/d11/*.dbf | wc -l
ls -ltr /backup/SRTR1/d13/*.dbf | wc -l
ls -ltr /backup/SRTR1/d12/*.dbf | wc -l
ls -ltr /backup/SRTR1/d14/*.dbf | wc -l
ls -ltr /backup/SRTR1/d02/*.dbf | wc -l
ls -ltr /backup/SRTR1/d09/*.dbf | wc –l


Run ps –ef | grep SRTR1 on Solaris_Server which will show copy processes since we run them in background.

Conversion
Bring up the database SRTR1and Convert the datafiles of SRTR1on the target server (AIX_Server)

Note: Create any missing directories

1. Enable the entry for SRTR1in /etc/oratab for the 10.2.0.3_Q108 ORACLE_HOME(already there)

# SRTR1:/optware/oracle/10.2.0.3_Q108:Y


2. Copy the init.ora file from /backup to/ dbms/oracle/local/SRTR1/etc

Cp initSRTR1.ora /dbms/oracle/local/SRTR1/etc

3. Go to /dbms/oracle/local/SRTR1/etc and
Edit the following in init.ora for SRTR1located in /dbms/oracle/local/SRTR1/etc to reflect the correct file systems.

AUDIT_FILE_DEST ='/backup/ SRTR1/oradump /adump'
background_dump_dest='/backup/SRTR1/oradump/bdump'
core_dump_dest='/backup/ SRTR1/oradump /cdump'
user_dump_dest='/backup/ SRTR1/oradump /udump'
control_files='/backup/SRTR1/u11/SRTR1.control.01.ctl',
'/backup/SRTR1/u13/SRTR1.control.02.ctl',
'/backup/SRTR1/u13/SRTR1.control.03.ctl'
db_name=’SRTR1’
log_archive_dest_1='LOCATION=/backup/SRTR1/archive'
log_archive_format='SRTR1_%s_%t_%r.arc'
service_names=' SRTR1.world',' SRTR1'

4. Run the env file in /dbms/oracle/local/SRTR1/etc to set up environment for SRTR1

oracle@erpstedb202p[GDPFPRG] /dbms/oracle/local/SRTR1/etc > . SRTR1.env


5. Make sure the environment is set correctly for SRTR1. Use below

echo $ORACLE_SID

echo $ORACLE_HOME

echo $PATH

6. Create a link in $ORACLE_HOME/dbs for init.ora

$ cd $ORACLE_HOME/dbs
$ ln -s /dbms/oracle/local/SRTR1/etc/initSRTR1.ora initSRTR1.ora

7. Make a copy of the trace file under /backup to /optware/oracle/work/UPGRADE_PROD and rename it from xxxx.trc to controlfile_create_SRTR1.sql
$ cp SRTR1_ora_22678.trc /optware/oracle/work/UPGRADE_PROD
$ cd /optware/oracle/work/UPGRADE_PROD
$ mv xxxxxx.trc create_controlfile_SRTR1.sql

8. Modify the create_controlfile_SRTR1.sql under /optware/oracle/work/UPGRADE to reflect the correct paths for data files and the correct SID name like below

a. Modify “REUSE” to “SET” in the control file trace and
b. /u11/SRTR1/u14/*.dbf to /backup/SRTR1/u14/*.dbf
c. /u11/SRTR1/u09/*.dbf to /backup/GDPFDNV1/u09/*.dbf


9. Check if all the datafiles are in correct location where the database needs to be staged. Cd to /optware/oracle/work/upgrade. Log on to sqlplus and Startup nomount the database
$cd /optware/oracle/work/UPGRADE
$ sqlplus
SQL> startup nomount

10. Run the control file create sql
SQL> @controlfile_create_SRTR1.sql

Note: Check for any errors in the alert log while creating the control files and resolve them
before proceeding with next step.

11. Open the database with reset logs option
SQL> alter database open resetlogs;


12. Add a temp file to the temporary tablespace.

Sql> ALTER TABLESPACE PSTEMP ADD TEMPFILE '/backup/SRTR1/SRTR1.pstemp.10.dbf' SIZE 2000M REUSE AUTOEXTEND OFF;

13. Check list of platforms that we can convert source database into as below
SQL> SELECT * FROM v$db_transportable_platform;

Open the database SRTR1in READ ONLY MODE;
SQL> shutdown immediate
SQL> startup mount
SQL> alter database open read only;


14. Make sure that the database in READ ONLY mode

SQL> select NAME,OPEN_MODE from v$database;

NAME OPEN_MODE
--------- ----------
SRTR1 READ ONLY

Sql> exit


13. Create a directory named convertdb under /optware/oracle/10.2.0.3_Q108/dbs

$cd $ORACLE_HOME/dbs
$mkdir convertdb

15. Open another session and monitor the alert log to see any error during conversion
16. At this point the RMAN convert database can be used:
$ cd /optware/oracle/10.2.0.3_Q108/
$ rman

--Connect to the target
RMAN> connect target;

--the below converts the datafiles under /backup/SRTR1/dXX to AIX format and places them under /dXX/oradata/GDPFPRG/. It also creates transport script in /optware/oracle/10.2.0.3_Q108/dbs/convertdb/transportscript. It took 4.5 hours on PHUATH

RMAN> CONVERT DATABASE NEW DATABASE 'GDPFPRG'
transport script '/optware/oracle/10.2.0.3_Q108/dbs/convertdb/transportscript'
to platform 'AIX-Based Systems (64-bit)'
db_file_name_convert
'/backup/SRTR1/u11' '/u11/oradata/GDPFPRG',
'/backup/SRTR1/u13' '/u13/oradata/GDPFPRG',
'/backup/SRTR1/u12' '/u12/oradata/GDPFPRG',
'/backup/SRTR1/u14' '/u14/oradata/GDPFPRG',
'/backup/SRTR1/u02' '/u02/oradata/GDPFPRG',
'/backup/SRTR1/u09' '/u09/oradata/GDPFPRG' ;


Note: After successful conversion of the data files success messages are prompted and the converted data files are created under /oradata/GDPFPRG/db. The conversion should take approximately 6 hours.

The conversion success message at the end of the conversion is as the sample below

************************************************************
input datafile fno=00101 name=/backup/SRTR1/u13/SRTR1.ptrpts.01.dbf
converted datafile=/u13/oradata/GDPFPRG/SRTR1.ptrpts.01.dbf
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting datafile conversion
input datafile fno=00102 name=/backup/SRTR1/u11/SRTR1.rwlarge.01.dbf
converted datafile=/u11/oradata/GDPFPRG/SRTR1.rwlarge.01.dbf
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting datafile conversion
input datafile fno=00103 name=/backup/SRTR1/u13/SRTR1.scapp.01.dbf
converted datafile=/u13/oradata/GDPFPRG/SRTR1.scapp.01.dbf
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting datafile conversion
input datafile fno=00104 name=/backup/SRTR1/u11/SRTR1.bplarge.01.dbf
converted datafile=/u11/oradata/GDPFPRG/SRTR1.bplarge.01.dbf
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:01
Run SQL script /optware/oracle/10.2.0.3_Q108/dbs/convertdb/transportscript on the target platform to create database
Edit init.ora file /optware/oracle/10.2.0.3_Q108/dbs/init_00k7onuv_1_0.ora. This PFILE will be used to create the database on the target platform
To recompile all PL/SQL modules, run utlirp.sql and utlrp.sql on the target platform
To change the internal database identifier, use DBNEWID Utility
Finished backup at 19-FEB-09



15. After successful completion of RMAN conversion exit out.
RMAN > exit

Task6. Bring up database SRTR1

01. A new control file creation script “transportscript” is created under /optware/oracle/10.2.0.3_Q108/dbs/convertdb



02. Rename the datafiles from SRTR1to SRTR1by executing the script rename_files_from_SRTR1_to_gdpfprg.ksh located in /optware/oracle/work/UPGRADE_PROD

03. Point the Oracle Home of SRTR1to 10.2.0.3_Q108 in /etc/oratab like below.


GDPFPRG:/optware/oracle/10.2.0.3_Q108_dbv:Y

04. Run the env file located in /dbms/oracle/local/GDPFPRG/etc.

$ /dbms/oracle/local/GDPFPRG/etc > . GDPFPRG.env


05. Make sure the environment is set correctly for GDPFPRG. Use below

echo $ORACLE_SID

echo $ORACLE_HOME

echo $PATH


06. Go to $ORACLE_HOME/dbs and create a soft link for initGDPFPRG.ora in $ORACLE_HOME/dbs to point to /dbms/oracle/local/GDPFPRG/etc. Use below

ln -s /dbms/oracle/local/GDPFPRG/etc/initGDPFPRG.ora initGDPFPRG.ora

07. Make sure the initSRTR1to reflect
AUDIT_FILE_DEST =' /u01/oradump/GDPFPRG/adump'
background_dump_dest=' /u01/oradump/GDPFPRG/bdump'
core_dump_dest=' /u01/oradump/GDPFPRG/cdump'
user_dump_dest=' /u01/oradump/GDPFPRG/udump'
control_files ='/u11/oradata/GDPFPRG/gdpfprg.control.01.ctl'
,'/u13/oradata/GDPFPRG/gdpfprg.control.02.ctl'
,'/u13/oradata/GDPFPRG/gdpfprg.control.03.ctl'
db_name=’GDPFPRG’
log_archive_dest_1='LOCATION=/u07/oradata/GDPFPRG'
undo_tablespace ='PSRBS1'
log_archive_format='GDPFPRG_%s_%t_%r.arc'
service_names=' GDPFPRG.world',' GDPFPRG'


08. Make a copy of create_controlfile_SRTR1.sql in /optware/oracle/work/UPGRADE_PROD to create_controlfile_GDPFPRG.sql (RECOMMENDED)

$/optware/oracle/work/UPGRADE_PROD>cp create_controlfile_SRTR1.sql create_controlfile_GDPFPRG.sql


OR use the transport script to create a control file…

*************************
Modify "transportscript" script to reflect the converted data files location, intiPHUATH.ora location and the location for utlxxx scripts as per the ORACLE_HOME. Also make other any other necessary changes.

*********************

09. Modify the create_controlfile_GDPFPRG.sql to reflect the following:

SRTR1 to GDPFPRG


Redo log locations as follows:

GROUP 1 (
'/dev/roraGDPFPRGr1_01',
'/dev/roraGDPFPRGr2_01'
) SIZE 495M,
GROUP 2 (
'/dev/roraGDPFPRGr1_02',
'/dev/roraGDPFPRGr2_02'
) SIZE 495M,
GROUP 3 (
'/dev/roraGDPFPRGr1_03',
'/dev/roraGDPFPRGr2_03'
) SIZE 495M,
GROUP 4 (
'/dev/roraGDPFPRGr1_04',
'/dev/roraGDPFPRGr2_04'
) SIZE 495M,
GROUP 5 (
'/dev/roraGDPFPRGr1_05',
'/dev/roraGDPFPRGr2_05'
) SIZE 495M

Modify the datafile locations as below

'/backup/SRTR1/u11' to '/u11/oradata/GDPFPRG',
'/backup/SRTR1/u13' to '/u13/oradata/GDPFPRG',
'/backup/SRTR1/u12' to '/u12/oradata/GDPFPRG',
'/backup/SRTR1/u14' to '/u14/oradata/GDPFPRG',
'/backup/SRTR1/u02' to '/u02/oradata/GDPFPRG',
'/backup/SRTR1/u09' to '/u09/oradata/GDPFPRG';



10. Open another session and monitor alert log for SRTR1in /u01/oradump/GDPFPRG/bdump
11. cd to /optware/oracle/work/UPGRADE_PROD directory Run the create_controfile_GDPFPRG.sql script in your work directory

Ex: $SQLPLUS

Sql> startup nomount

SQL>@/optware/oracle/work/UPGRADE_PROD/ create_controlfile_GDPFPRG.sql

SQL> alter database open resetlogs;

12. Add temp file to PSTEMP tablespace


Sql>ALTER TABLESPACE PSTEMP ADD TEMPFILE '/u11/oradata/GDPFPRG/gdpfprg.pstemp.10.dbf' SIZE 2000M REUSE AUTOEXTEND OFF;

13. Shutdown and startup just to see if any errors in the alert log. Closely monitor the alert log during this

Sql> shutdown immediate

Sql> startup

14. Run utlirp.sql by shutting down and startup in upgrade mode.

Sql> shutdown immediate

Sql> startup upgrade;

Sql> @?/rdbms/admin/utlirp.sql

15. Run utlrp.sql after shutting down the database

Sql> shutdown immediate

Sql> startup

Sql> @ ?/rdbms/admin/utlrp.sql

16. Go to /optware/oracle/work/UPGRADE_PROD and run add_undo_files_GDPFPRG.sql

Sql> spool add_undo_files_GDPFPRG.log

Sql> @add_undo_files_GDPFPRG.sql


17. Drop the temporary tablespace PSTEMP

Sql> drop tablespace PSTEMP including datafiles and contents cascade constraints;

drop tablespace pstemp including contents and datafiles;


18. Create temporary tablespace PSTEMP

Sql> CREATE TEMPORARY TABLESPACE "PSTEMP" TEMPFILE '/dev/roraGDPFPRGt_001' SIZE 3990M REUSE ;

19. Go to /optware/oracle/work/UPGRADE_PROD and run add_temp_files_GDPFPRG.sql to add temp files
Sql > spool add_temp_files_GDPFPRG.log

Sql> add_temp_files_GDPFPRG.sql

20. Create block changing data file

Sql> alter database enable block change tracking using file '/u13/oradata/GDPFPRG/GDPFPRG.bct.01.dbf' reuse;

21. Update the PS OWNER


SQL> select * from psdbowner;

DBNAME OWNERID
-------- --------
PHPRDH SYSADM

SQL> update psdbowner set dbname='GDPFPRG';

Sql> commit;

SQL> select * from psdbowner;

DBNAME OWNERID
-------- --------
SRTR1 SYSADM

24. Make the sysaux tablespace autoextend
Make sure that the database SRTR1is up and running.
25. Bring up the listener.
26. Shutdown the SRTR1database
SQL> select instance_name from v$instance ;

SRTR1

SQL> shutdown immediate