Table Reorg Steps

Posted on December 10, 2012 by Naveen K. SR

Take the sizes of tables , associate indexes and take the invalid object information , Check for table,index status

Example:

SELECT OWNER, SEGMENT_NAME, SEGMENT_TYPE, TABLESPACE_NAME, BYTES/1024/1024/1024 FROM DBA_SEGMENTS WHERE SEGMENT_NAME IN (<TABLENAMES>);

SELECT OWNER, INDEX_NAME, INDEX_TYPE, TABLE_NAME, STATUS FROM DBA_INDEXES WHERE TABLE_NAME IN (<TABLENAMES>);

SELECT OWNER, SEGMENT_NAME, SEGMENT_TYPE, TABLESPACE_NAME, BYTES/1024/1024/1024 FROM DBA_SEGMENTS WHERE SEGMENT_NAME IN (<INDEXNAMES>);

SELECT LAST_ANALYZED FROM DBA_TABLES WHERE TABLE_NAME IN (<>);

SELECT COUNT(*) FROM DBA_OBJECTS WHERE STATUS=’INVALID’;

2. Export table statistics

Exporting the tables stats is very important; else, tables will start gathering the stats from beginning for the whole table and sometimes that will affects the performance of the queries respect to these tables. Once the reorg has been completed we need to import the stats back to the table, so that the tables will use the old stats while querying the table data

(a) Create Stats Table to store the statistics

Exporting the stats to one of the table say ‘MY_STATS_TABLE’ as temporary table. Once the stats has been imported you can drop the table

EXEC DBMS_STATS.CREATE_STAT_TABLE(OWNNAME =>'<OWNERNAME>’,STATTAB =>'<TABLENAME>’, TBLSPACE =>'<TABLESPACENAME>’);

Example:

EXEC DBMS_STATS.CREATE_STAT_TABLE(OWNNAME =>’SCOTT’,STATTAB =>’MY_STATS_TABLE’, TBLSPACE =>’USER’);

(b) Export the stats to the above created table

EXEC

DBMS_STATS.EXPORT_TABLE_STATS(‘<OWNERNAME>’,'<TABLENAME>’,NULL,'<STATS TABLENAME>’,NULL,TRUE);

Example:

EXEC DBMS_STATS.EXPORT_TABLE_STATS(‘SCOTT’,’EMP’,NULL,’MY_STATS_TABLE’,NULL,TRUE);

3. Perform Table & associated index reorg

(a) Table Reorg:

For Non-LOB Tables:

ALTER TABLE <USERNAME>.<TABLE NAME> MOVE;

Example:

ALTER TABLE SCOTT.EMP MOVE;

For Non-LOB Tables:

ALTER TABLE <USERNAME>.<TABLE_NAME> MOVE LOB (COLUMN_NAME) STORE AS (TABLESPACE NEWTABLESPACE);

Example:

ALTER TABLE SCOTT.EMP MOVE LOB (PHOTO) STORE AS TABLESPACE DATA;

(b) Rebuild the associated indexes:

If the table has associated indexes which we got it from the step-1 queries, we need to perform the rebuild as they were become INVALID once after the reorg of the parent table.

For Normal Indexes:

ALTER INDEX <USERNAME>.<INDEX NAME> REBUILD ONLINE;

Example:

ALTER INDEX SCOTT.EMP_IDX REBUILD ONLINE;

For Bitmap indexes:

ALTER INDEX <USERNAME>.<INDEX NAME> REBUILD;

Example:

ALTER INDEX SCOTT.EMP_BIT_IDX REBUILD;

4.Import table stats

EXEC DBMS_STATS.IMPORT_TABLE_STATS(‘<USERNAME>’,'<TABLENAME>’,NULL,'<STATS TABLENAME>’,NULL,TRUE);

Example:

EXEC DBMS_STATS.IMPORT_TABLE_STATS(‘SCOTT’,’EMP’,NULL,’MY_STATS_TABLE’,NULL,TRUE);

5. Repeat step 1

6. Please collect the invalid object count

SELECT COUNT(*) FROM DBA_OBJECTS WHERE STATUS=’INVALID’;

SELECT OWNER, OBJECT_NAME, OBJECT_TYPE, STATUS FROM DBA_OBJECTS WHERE STATUS=’INVALID’;

7. Run utlrp.sql (If necessary)

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

8. Again Collect the Invalid Object information

9. Execute the below query and compare the values before and after the the reorg, you will be surprised

SELECT OWNER, SEGMENT_NAME, SEGMENT_TYPE, TABLESPACE_NAME, BYTES/1024/1024/1024 FROM DBA_SEGMENTS WHERE SEGMENT_NAME IN (<TABLENAMES>);

Posted in Oracle | Leave a comment

STATSPACK MANAGEMENT

Posted on December 10, 2012 by Naveen K. SR

Check if we have TOOLS tablespace created and have minimum of 2 GB free space in TOOLS tablespace. If not create tablespace TOOLS with 2 GB.

Make sure that JOB_QUEUE_PROCESSES parameter is not 0 (Zero). If it is 0 the auto stats will not collect.

Run $ORACLE_HOME/rdbms/admin/spcreate.sql

The above scripts prompt you to enter the values for below parameters – Provide the values as below –
1. perfstat_password -> perfstat123
2. default_tablespace -> TOOLS
3. temporary_tablespace -> TEMP

The above script will create statspack package. Make sure that there should not be any errors.

Once the statspack is created run the procedure to create snapshots

SQL> connect perfstat/perfstat123

SQL> execute statspack.snap;

Run twice to ensure that the manual stats are collected.

Run $ORACLE_HOME/rdbms/admin/spreport and check two or more snaps are collected and you are able to generate statspack report.

Run the below procedure to collect the stats for every 1 Hour –

variable jobno number;

variable instno number;

begin

select instance_number into :instno from v$instance;

dbms_job.submit(:jobno, ‘statspack.snap;’, trunc(sysdate+1/48,’HH’), ‘trunc(SYSDATE+1/48,”HH”)’, TRUE, :instno);

commit;

end;

/

If the retention period need to be set for the STATPACK snapshots

For example, the retention period is 30 days and stats collection frequency is 1 Hr.

Once the procedure is created enable auto purging of 30 days old snaps data by running the below job –

execute statspack_custom.purge_schedule(30, trunc(sysdate+1/24,’HH’), ‘SYSDATE+1’);

Validate the job status from user_jobs –

show user

USER is “PERFSTAT”

select JOB,LAST_DATE,LAST_SEC,THIS_DATE,THIS_SEC,NEXT_DATE,NEXT_SEC,WHAT from user_jobs;

JOB LAST_DATE LAST_SEC THIS_DATE THIS_SEC NEXT_DATE NEXT_SEC WHAT

—- ——— ——– ——— ——– ——— ——– ———————–

1312 27-AUG-12 23:30:15 28-AUG-12 00:00:00 statspack.snap;

1313 27-AUG-12 23:00:04 28-AUG-12 23:00:04 statspack_custom.purge(30);

Posted in Oracle | Leave a comment

Adding datafiles using BRTOOLS in Oracle

Posted on October 27, 2011 by Naveen K. SR

BRTOOLS are very useful tool to manage tablespaces and datafiles for the SAP-Oracle databases.

oracle> brtools

BR0651I BRTOOLS 7.10 (39)

BR0280I BRTOOLS time stamp: 2011-10-26 20.01.37

BR0656I Choice menu 1 – please make a selection

——————————————————————————-

BR*Tools main menu

1 = Instance management

2 – Space management

3 – Segment management

4 – Backup and database copy

5 – Restore and recovery

6 – Check and verification

7 – Database statistics

8 – Additional functions

9 – Exit program

Standard keys: c – cont, b – back, s – stop, r – refr, h – help

——————————————————————————-

BR0662I Enter your choice:

BR0280I BRTOOLS time stamp: 2011-10-26 20.01.50

BR0663I Your choice: ‘2’

BR0280I BRTOOLS time stamp: 2011-10-26 20.01.50

BR0656I Choice menu 5 – please make a selection

——————————————————————————-

Database space management

1 = Extend tablespace

2 – Create tablespace

3 – Drop tablespace

4 – Alter tablespace

5 – Alter data file

6 – Move data file

7 – Additional space functions

8 – Reset program status

Standard keys: c – cont, b – back, s – stop, r – refr, h – help

——————————————————————————-

BR0662I Enter your choice:

BR0280I BRTOOLS time stamp: 2011-10-26 20.01.53

BR0663I Your choice: ‘1’

BR0280I BRTOOLS time stamp: 2011-10-26 20.01.53

BR0657I Input menu 81 – please enter/check input values

——————————————————————————-

BRSPACE options for tablespace extension

1 – BRSPACE profile (profile) …… [initORCL.sap]

2 – Database user/password (user) .. [/]

3 ~ Tablespace name (tablespace) … []

4 – Confirmation mode (confirm) …. [yes]

5 – Scrolling line count (scroll) .. [20]

6 – Message language (language) …. [E]

7 – BRSPACE command line (command) . [-p initORCL.sap -s 20 -l E -f tsextend]

Standard keys: c – cont, b – back, s – stop, r – refr, h – help

——————————————————————————-

BR0662I Enter your choice:

BR0280I BRTOOLS time stamp: 2011-10-26 20.01.59

BR0663I Your choice: ‘3’

BR0681I Enter string value for “tablespace” []: DATA

BR0280I BRTOOLS time stamp: 2011-10-26 20.02.21

BR0683I New value for “tablespace”: ‘DATA’

BR0280I BRTOOLS time stamp: 2011-10-26 20.02.21

BR0657I Input menu 81 – please enter/check input values

——————————————————————————-

BRSPACE options for tablespace extension

1 – BRSPACE profile (profile) …… [initORCL.sap]

2 – Database user/password (user) .. [/]

3 ~ Tablespace name (tablespace) … [DATA]

4 – Confirmation mode (confirm) …. [yes]

5 – Scrolling line count (scroll) .. [20]

6 – Message language (language) …. [E]

7 – BRSPACE command line (command) . [-p initORCL.sap -s 20 -l E -f tsextend -t DATA]

Standard keys: c – cont, b – back, s – stop, r – refr, h – help

——————————————————————————-

BR0662I Enter your choice:

BR0280I BRTOOLS time stamp: 2011-10-26 20.02.29

BR0663I Your choice: ‘c’

BR0259I Program execution will be continued…

BR0291I BRSPACE will be started with options ‘-p initORCL.sap -s 20 -l E -f tsextend -t DATA’

BR0280I BRTOOLS time stamp: 2011-10-26 20.02.29

BR0670I Enter ‘c[ont]‘ to continue, ‘b[ack]’ to go back, ‘s[top]’ to abort:

BR0280I BRTOOLS time stamp: 2011-10-26 20.02.38

BR0257I Your reply: ‘c’

BR0259I Program execution will be continued…

###############################################################################

BR1001I BRSPACE 7.10 (39)

BR1002I Start of BRSPACE processing: sehbwmck.tse 2011-10-26 20.02.38

BR0484I BRSPACE log file: /oracle/ORCL/sapreorg/sehbwmck.tse

BR0280I BRSPACE time stamp: 2011-10-26 20.02.45

BR1009I Name of database instance: ORCL

BR1010I BRSPACE action ID: sehbwmck

BR1011I BRSPACE function ID: tse

BR1012I BRSPACE function: tsextend

BR0280I BRSPACE time stamp: 2011-10-26 20.03.36

BR0657I Input menu 303 – please enter/check input values

——————————————————————————-

Options for extension of tablespace DATA (1. file)

1 * Last added file name (lastfile) ……. [/oracle/ORCL/sapdata10/zfigl_76/zfig1.data76]

2 * Last added file size in MB (lastsize) . [4096]

3 – New file to be added (file) ……….. [/oracle/ORCL/sapdata10/zfigl_77/zfig1.data77]

4 ~ Raw disk / link target (rawlink) …… []

5 – Size of the new file in MB (size) ….. [4096]

6 – File autoextend mode (autoextend) ….. [no]

7 # Maximum file size in MB (maxsize) ….. []

8 # File increment size in MB (incrsize) .. []

9 – SQL command (command) …………….. [alter tablespace DATA add datafile ‘/oracle/ORCL/sapdata10/zfigl_77/zfig1.data77’ size 4096M autoextend off]

Standard keys: c – cont, b – back, s – stop, r – refr, h – help

——————————————————————————-

BR0662I Enter your choice:

BR0280I BRSPACE time stamp: 2011-10-26 20.03.56

BR0663I Your choice: ‘c’

BR0259I Program execution will be continued…

BR1091I Next data file can be specified now

BR0280I BRSPACE time stamp: 2011-10-26 20.03.56

BR0675I This is a optional action – do you want to execute it now?

BR0676I Enter ‘y[es]’ to execute the action, ‘n[o]/c[ont]‘ to skip it, ‘s[top]’ to abort:

BR0280I BRSPACE time stamp: 2011-10-26 20.04.06

BR0257I Your reply: ‘n’

BR0678I The action will be skipped…

BR0280I BRSPACE time stamp: 2011-10-26 20.04.06

BR0370I Directory /oracle/ORCL/sapreorg/sehbwmck created

BR0280I BRSPACE time stamp: 2011-10-26 20.04.12

BR0319I Control file copy created: /oracle/ORCL/sapreorg/sehbwmck/cntrlORCL.old 81149952

BR0280I BRSPACE time stamp: 2011-10-26 20.04.12

BR0370I Directory /oracle/ORCL/sapdata10/zfigl_77 created

BR0280I BRSPACE time stamp: 2011-10-26 20.04.12

BR1088I Extending tablespace DATA …

BR0280I BRSPACE time stamp: 2011-10-26 20.05.14

BR1016I SQL statement ‘alter tablespace DATA add datafile ‘/oracle/ORCL/sapdata10/zfigl_77/zfig1.data77′ size 4096M autoextend off’ executed successfully

BR1051I Tablespace PSAPZFIGL extended successfully with file: /oracle/ORCL/sapdata10/zfigl_77/zfig1.data77 4096M

BR0280I BRSPACE time stamp: 2011-10-26 20.05.14

BR0340I Switching to next online redolog file for database instance ORCL …

BR0321I Switch to next online redolog file for database instance ORCL successful

BR0280I BRSPACE time stamp: 2011-10-26 20.05.18

BR0319I Control file copy created: /oracle/ORCL/sapreorg/sehbwmck/cntrlORCL.new 81149952

BR0280I BRSPACE time stamp: 2011-10-26 20.05.18

BR0670I Enter ‘c[ont]‘ to continue, ‘b[ack]’ to go back, ‘s[top]’ to abort:

BR0280I BRSPACE time stamp: 2011-10-26 20.05.25

BR0257I Your reply: ‘c’

BR0259I Program execution will be continued…

BR0280I BRSPACE time stamp: 2011-10-26 20.05.25

BR1020I Number of tablespaces processed: 1

BR1003I BRSPACE function ‘tsextend’ completed

BR1008I End of BRSPACE processing: sehbwmck.tse 2011-10-26 20.05.25

BR0280I BRSPACE time stamp: 2011-10-26 20.05.25

BR1005I BRSPACE completed successfully

###############################################################################

BR0292I Execution of BRSPACE finished with return code 0

BR0280I BRTOOLS time stamp: 2011-10-26 20.05.25

BR0256I Enter ‘c[ont]’ to continue, ‘s[top]‘ to cancel BRTOOLS:

BR0280I BRTOOLS time stamp: 2011-10-26 20.05.29

BR0257I Your reply: ‘s’

BR0679I Do you really want to cancel BRTOOLS? Enter y[es]/n[o]:

BR0280I BRTOOLS time stamp: 2011-10-26 20.05.33

BR0257I Your reply: ‘y’

BR0260E BRTOOLS cancelled by user

BR0280I BRTOOLS time stamp: 2011-10-26 20.05.33

BR0654I BRTOOLS terminated with errors

oracle>

Posted in Oracle | 2 Comments

Manually Corrupting Oracle Data Block

Posted on March 29, 2011 by Naveen K. SR

Manually Corrupting Oracle Data Block

When we’re practicing the backup and recovery scenarios, we come-up with the block corruption recovery cases. So how to make the blocks to get corrupt? And how the recoveries can be done? And here is the solution for how to make the Oracle data blocks to get corrupted.

At the same time, this will also help us to recover the corrupted blocks from the production databases.

Before we begin:

This is only for the learning, practicing, and educational purpose, please do not try these methods on the production, testing, and/or development databases.

The way we corrupt the Oracle data blocks that allows the learners’ to practice the scenarios on backup and recoveries. That makes when the actual database block level corruption has occurred, we will be in a position to recover in the block level.

1. For the purpose of this practice, will create a separate tablespace and a new schema user.

a. Create a Tablespace say “test_corrupt”

SQL> create tablespace test_corrupt

2 datafile ‘/u01/app/oracle/oradata/orcl/test_corrupt01.dbf’ size 10m;

Tablespace created.

b. Create an user say “test” and assign quota to the “test_corrupt” tablespace

SQL> create user test identified by test123

2 default tablespace test_corrupt

3 quota unlimited on test_corrupt;

User created.

c. Grant the necessary privileges

SQL> grant create session, resource to test;

Grant succeeded.

2. Create a table as test user

SQL> conn test/test123

Connected.

SQL> create table emp(eno number(7))

2 tablespace test_corrupt;

Table created.

3. Insert some dummy records into the table by writing a simple ‘for loop’ block as shown:

SQL> begin

2 for i in 1..10000

3 loop

4 insert into emp values(i);

5 end loop;

6 end;

8 /

PL/SQL procedure successfully completed.

SQL> select count(*) from emp;

COUNT(*)

———-

10000

4. Take the RMAN backup, either full database or tablespace level backup before corrupting the block.

Connect to the RMAN

$ rman target / catalog scott/tiger@rc

Recovery Manager: Release 11.2.0.1.0 – Production on Mon Mar 28 16:00:39 2011

connected to target database: ORCL (DBID=1272957858)

connected to recovery catalog database

Report the schema to know about the target database

RMAN> report schema;

Report of database schema for database with db_unique_name ORCL

List of Permanent Datafiles

===========================

File Size(MB) Tablespace RB segs Datafile Name

—- ——– ——————– ——- ————————

1 690 SYSTEM YES /u01/app/oracle/oradata/orcl/system01.dbf

2 540 SYSAUX NO /u01/app/oracle/oradata/orcl/sysaux01.dbf

3 80 UNDOTBS1 YES /u01/app/oracle/oradata/orcl/undotbs01.dbf

4 10 TEST_CORRUPT NO /u01/app/oracle/oradata/orcl/test_corrupt01.dbf

6 20 DEF_PERM NO /u01/app/oracle/oradata/orcl/def_perm01.dbf

List of Temporary Files

=======================

File Size(MB) Tablespace Maxsize(MB) Tempfile Name

—- ——– ——————– ———– ——————–

1 33 TEMP 32767 /u01/app/oracle/oradata/orcl/temp01.dbf

Take the backup of the tablespace

RMAN> backup tablespace test_corrupt;

Starting backup at 28-MAR-11

using channel ORA_DISK_1

channel ORA_DISK_1: starting full datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

input datafile file number=00004 name=/u01/app/oracle/oradata/orcl/test_corrupt01.dbf

channel ORA_DISK_1: starting piece 1 at 28-MAR-11

channel ORA_DISK_1: finished piece 1 at 28-MAR-11

piece handle=/backup/rman_backups/rman_ORCL_0um8c6ve tag=TAG20110328T161437 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01

Finished backup at 28-MAR-11

5. Take the tablespace offline so that we can make changes to the datafile.

SQL> conn / as sysdba

connected

SQL> alter tablespace test1 offline;

Tablespace altered.

6. To corrupt a datafile, there are many freeware editors available. Here we are using UltraEdit GUI editor (Available for both Windows and Linux Versions) to make changes in the datafile.

Download from the websites and install it. Once the installation has been completed, the files can be edited by using UltraEdit with the help of executable called uex

Open the datafile “/u01/app/oracle/oradata/orcl/test_corrupt01.dbf” using UltraEdit

$ uex /u01/app/oracle/oradata/orcl/test_corrupt01.dbf

The screen will look as like below:

Do not try to edit the file in the beginning, because those portion of the file will contain the datafile header information. Modifying the header file will corrupt the whole file itself.

So, scroll down to find out the blocks where the data is present, try to type some blunders in the space wherever the mouse pointer is blinking. Once the change has been completed, save the file.

7. After corrupting the blocks, bring back the tablespace to online

SQL> alter tablespace test online;

Tablespace altered.

You can notice that Oracle doesn’t through any errors when we brought tablespace online, because we had not modified the datafile header. If in case we had done, then that will through an errors while bringing back the tablespace to online. Oracle will through an error only when it tries to access the data from corrupted blocks.

8. To query the data, connect to the “test” user and query the table “emp”

SQL> conn test/test123

Connected

SQL> select * from emp;

ENO

———-

661

662

663

664

…

7903

7904

7905

ERROR:

ORA-01578: ORACLE data block corrupted (file # 4, block # 142)

ORA-01110: data file 4: ‘/u01/app/oracle/oradata/orcl/test_corrupt01.dbf’

4605 rows selected.

The query returns 4605 records and then complains of block corruption in file 4, and the block numbered 142 is being reported as corrupt.

This is the methods to be followed in order to corrupt the Oracle data blocks.

Lets us see, how to identify the corrupted blocks and recover them.

9. Let us see what are the blocks are corrupted in “test_corrupt01.dbf” datafile by running dbv utility.

$ which dbv

/u01/app/oracle/product/11.2.0/dbhome_1/bin/dbv

$ dbv file=/u01/app/oracle/oradata/orcl/test_corrupt01.dbf

DBVERIFY: Release 11.2.0.1.0 – Production on Mon Mar 28 15:51:01 2011

DBVERIFY – Verification starting : FILE = /u01/app/oracle/oradata/orcl/test_corrupt01.dbf

Page 142 is marked corrupt

Corrupt block relative dba: 0x0100008e (file 4, block 142)

Bad check value found during dbv:

Data in bad block:

type: 6 format: 2 rdba: 0x0100008e

last change scn: 0x0000.00134653 seq: 0x1 flg: 0x06

spare1: 0x0 spare2: 0x0 spare3: 0x0

consistency value in tail: 0x46530601

check value in block header: 0xe6bd

computed block checksum: 0xcc0a

DBVERIFY – Verification complete

Total Pages Examined : 1280

Total Pages Processed (Data) : 19

Total Pages Failing (Data) : 0

Total Pages Processed (Index): 0

Total Pages Failing (Index): 0

Total Pages Processed (Other): 131

Total Pages Processed (Seg) : 0

Total Pages Failing (Seg) : 0

Total Pages Empty : 1129

Total Pages Marked Corrupt : 1

Total Pages Influx : 0

Total Pages Encrypted : 0

Highest block SCN : 1263187 (0.1263187)

This utility scans all the blocks in a given datafile and outputs the corrupt blocks. In this case, there was only one block marked as corrupt. Make a note of all the corrupted blocks as we need to recover them to previous state.

10. Start RMAN session and recover all the corrupted blocks.

The beauty of RMAN is that it recovers only corrupted blocks and we need to recover only those corrupt blocks instead of entire datafile.

$ rman target / catalog scott/tiger@rc

Recovery Manager: Release 11.2.0.1.0 – Production on Mon Mar 28 16:00:39 2011

connected to target database: ORCL (DBID=1272957858)

connected to recovery catalog database

Report the Schema to know the status of target database, gathers the information from control file

RMAN> report schema;

Report of database schema for database with db_unique_name ORCL

List of Permanent Datafiles

===========================

File Size(MB) Tablespace RB segs Datafile Name

—- ——– ——————– ——- ————————

1 690 SYSTEM YES /u01/app/oracle/oradata/orcl/system01.dbf

2 540 SYSAUX NO /u01/app/oracle/oradata/orcl/sysaux01.dbf

3 80 UNDOTBS1 YES /u01/app/oracle/oradata/orcl/undotbs01.dbf

4 10 TEST_CORRUPT NO /u01/app/oracle/oradata/orcl/test_corrupt01.dbf

6 20 DEF_PERM NO /u01/app/oracle/oradata/orcl/def_perm01.dbf

List of Temporary Files

=======================

File Size(MB) Tablespace Maxsize(MB) Tempfile Name

—- ——– ——————– ———– ——————–

1 33 TEMP 32767 /u01/app/oracle/oradata/orcl/temp01.dbf

11. Recover the corrupted blocks using recover command

RMAN> recover datafile 4 block 142;

Starting recover at 28-MAR-11

starting full resync of recovery catalog

full resync complete

using channel ORA_DISK_1

channel ORA_DISK_1: restoring block(s)

channel ORA_DISK_1: specifying block(s) to restore from backup set

restoring blocks of datafile 00004

channel ORA_DISK_1: reading from backup piece /backup/rman_backups/rman_ORCL_0vm8c862

channel ORA_DISK_1: piece handle=/backup/rman_backups/rman_ORCL_0vm8c862 tag=TAG20110328T163513

channel ORA_DISK_1: restored block(s) from backup piece 1

channel ORA_DISK_1: block restore complete, elapsed time: 00:00:01

starting media recovery

media recovery complete, elapsed time: 00:00:01

Finished recover at 28-MAR-11

RMAN reports success of block recovery command.

12. Will query the table again by logging in to the SQL*Plus:

SQL> conn test/test123

Connected.

SQL> select * from emp order by eno;

ENO

———-

…

9997

9998

9999

10000

10000 rows selected.

SQL> select count(*) from emp;

COUNT(*)

———-

10000

This will executes successfully and the records will be restored back to the table.

Posted in Oracle | 8 Comments

Changing the DBID in Oracle Databases

Posted on March 25, 2011 by Naveen K. SR

Changing DB ID for the Oracle Database

When you clone the database, the DB ID remains same as like the source database, if you need to change to the different DB ID, then this note will be useful. This is very much useful as in the case of working with RMAN.

Follow the below steps to change the DB ID in Oracle10g and Oracle11g databases:

1. Identify the DBID of the database

SQL> select dbid from v$database;

DBID

———-

1272957858

2. Shutdown the database in normal mode

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

3. Start the database to mount phase

SQL> startup mount

ORACLE instance started.

Total System Global Area 150667264 bytes

Fixed Size 1335080 bytes

Variable Size 92274904 bytes

Database Buffers 50331648 bytes

Redo Buffers 6725632 bytes

Database mounted.

SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – Production

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

4. In the Terminal Window, execute the nid command

[oracle@apps ~]$ which nid

/u01/app/oracle/product/11.2.0/dbhome_1/bin/nid

[oracle@apps ~]$ nid target=/

DBNEWID: Release 11.2.0.1.0 – Production on Fri Mar 25 16:32:17 2011

Connected to database RC (DBID=1272957858)

Connected to server version 11.2.0

Control Files in database:

/u01/app/oracle/oradata/rc/control01.ctl

/u01/app/oracle/oradata/rc/control02.ctl

Change database ID of database RC? (Y/[N]) => Y

Proceeding with operation

Changing database ID from 1272957858 to 2943969233

Control File /u01/app/oracle/oradata/rc/control01.ctl – modified

Control File /u01/app/oracle/oradata/rc/control02.ctl – modified

Datafile /u01/app/oracle/oradata/rc/system01.db – dbid changed

Datafile /u01/app/oracle/oradata/rc/sysaux01.db – dbid changed

Datafile /u01/app/oracle/oradata/rc/undotbs01.db – dbid changed

Datafile /u01/app/oracle/oradata/rc/def_perm01.db – dbid changed

Control File /u01/app/oracle/oradata/rc/control01.ctl – dbid changed

Control File /u01/app/oracle/oradata/rc/control02.ctl – dbid changed

Instance shut down

Database ID for database RC changed to 2943969233.

All previous backups and archived redo logs for this database are unusable.

Database has been shutdown, open database with RESETLOGS option.

Succesfully changed database ID.

DBNEWID – Completed succesfully.

5. Bounce back the database to mount phase

[oracle@apps ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Fri Mar 25 16:33:14 2011

Connected to an idle instance.

SQL> startup mount

ORACLE instance started.

Total System Global Area 150667264 bytes

Fixed Size 1335080 bytes

Variable Size 92274904 bytes

Database Buffers 50331648 bytes

Redo Buffers 6725632 bytes

Database mounted.

6. Open the database with resetlog option

SQL> alter database open resetlogs ;

Database altered.

SQL>

7. Identify the new changed DBID

SQL> select dbid from v$database;

DBID

———-

2943969233

Posted in Oracle | 3 Comments

Oracle11g Tables

Posted on March 21, 2011 by Naveen K. SR

Interval Partition

SQL> create table interval_part(eno number(4),ename varchar2(20),salary number(6))

2 partition by range(eno)

3 interval(1000)

4 (partition part1 values less than (1000),

5 partition part2 values less than (2000),

6 partition part3 values less than (3000),

7 partition part4 values less than (4000));

Table created.

System Partition

SQL> create table system_part(eno number(4), ename varchar2(20), salary number(6))

2 partition by system

3 (partition part1,

4 partition part2,

5 partition part3,

6 partition part4);

Table created.

SQL> insert into system_part partition(part1) values (1, ‘aaa’, 35000);

1 row created.

Virtual Column-Based Partition

SQL> create table virtual_part(eno number(2), salary number(7), incentives number(6), tot_salary generated always as(salary+(0.50*incentives)) virtual)

2 partition by range(tot_salary)

3 (partition part1 values less than (5000),

4 partition part2 values less than (10000),

5 partition part3 values less than (15000),

6 partition part4 values less than (20000),

7 partition part5 values less than (maxvalue));

Table created.

Composite Partitions

Range-Range: Single-Column Partition and Single-Column Subpartition

SQL> create table range_range(eno number(4), ename varchar2(12), deptno number(3))

2 partition by range(eno)

3 subpartition by range(eno)

4 (partition part1 values less than (1000)

5 (subpartition sub_part1 values less than(500),

6 subpartition sub_part2 values less than(1000)),

7 partition part2 values less than (2000)

8 (subpartition sub_part3 values less than(1500),

9 subpartition sub_part4 values less than(2000)));

Table created.

Range-Range: Multi-Column Partition and Single-Column Subpartition

SQL> create table range_range(eno number(4), ename varchar2(12), deptno number(3))

2 partition by range(eno,deptno)

3 subpartition by range(eno)

4 (partition part1 values less than (1000,10)

5 (subpartition sub_part1 values less than(500),

6 subpartition sub_part2 values less than(1000)),

7 partition part2 values less than (2000,20)

8 (subpartition sub_part3 values less than(1500),

9 subpartition sub_part4 values less than(2000)));

Table created.

Range-Range: Single-Column Partition and Multi-Column Subpartition

SQL> create table range_range(eno number(4), ename varchar2(12), deptno number(3))

2 partition by range(eno)

3 subpartition by range(eno,deptno)

4 (partition part1 values less than (1000)

5 (subpartition sub_part1 values less than(500,10),

6 subpartition sub_part2 values less than(1000,20)),

7 partition part2 values less than (2000)

8 (subpartition sub_part3 values less than(1500,30),

9 subpartition sub_part4 values less than(2000,40)));

Table created.

Range-Range: Multi-Column Partition and Multi-Column Subpartition

SQL> create table range_range(eno number(4), ename varchar2(12), deptno number(3))

2 partition by range(eno,deptno)

3 subpartition by range(eno,deptno)

4 (partition part1 values less than (1000,10)

5 (subpartition sub_part1 values less than(500,5),

6 subpartition sub_part2 values less than(1000,10)),

7 partition part2 values less than (2000,20)

8 (subpartition sub_part3 values less than(1500,15),

9 subpartition sub_part4 values less than(2000,20)));

Table created.

Composite: List-List Partition

SQL> create table telecom_list_list(no number(10),provider varchar2(20),state varchar2(5))

2 partition by list(state)

3 subpartition by list(provider)

4 (partition south values(‘KAR’,’TMN’,’KER’)

5 (subpartition sub_part1 values (‘ARTL’,’VDFN’,’TATA’),

6 subpartition sub_part2 values (‘ARCL’,’BSNL’,’UNOR’)),

7 partition north values(‘DLH’,’HR’,’UP’)

8 (subpartition sub_part3 values (‘ARTL’,’VDFN’,’TATA’),

9 subpartition sub_part4 values (‘ARCL’,’BSNL’,’UNOR’)),

10 partition west values(‘RJ’,’GOA’,’PNJ’)

11 (subpartition sub_part5 values (‘ARTL’,’VDFN’,’TATA’),

12 subpartition sub_part6 values (‘ARCL’,’BSNL’,’UNOR’)),

13 partition east values(‘BNG’,’ORS’,’ASM’)

14 (subpartition sub_part7 values (‘ARTL’,’VDFN’,’TATA’),

15 subpartition sub_part8 values (‘ARCL’,’BSNL’,’UNOR’)));

Table created.

Composite: List-Hash Partition

SQL> create table telecom_list_hash(no number(10),provider varchar2(20),state varchar2(5))

2 partition by list(state)

3 subpartition by hash(provider)

4 (partition south values(‘KAR’,’TMN’,’KER’)

5 (subpartition sub_part1,

6 subpartition sub_part2),

7 partition north values(‘DLH’,’HR’,’UP’)

8 (subpartition sub_part3,

9 subpartition sub_part4),

10 partition west values(‘RJ’,’GOA’,’PNJ’)

11 (subpartition sub_part5,

12 subpartition sub_part6),

13 partition east values(‘BNG’,’ORS’,’ASM’)

14 (subpartition sub_part7,

15 subpartition sub_part8));

Table created.

Composite: List-Range Partition

SQL> create table telecom_list_range(no number(10),free_sms number(10),state varchar2(5))

2 partition by list(state)

3 subpartition by range(free_sms)

4 (partition south values(‘KAR’,’TMN’,’KER’)

5 (subpartition sub_part1 values less than (100),

6 subpartition sub_part2 values less than (200)),

7 partition north values(‘DLH’,’HR’,’UP’)

8 (subpartition sub_part3 values less than (100),

9 subpartition sub_part4 values less than (200)),

10 partition west values(‘RJ’,’GOA’,’PNJ’)

11 (subpartition sub_part5 values less than (100),

12 subpartition sub_part6 values less than (200)),

13 partition east values(‘BNG’,’ORS’,’ASM’)

14 (subpartition sub_part7 values less than (100),

15 subpartition sub_part8 values less than (200)));

Table created.

Composite: Interval-Range: Range Single-Column Subpartition

SQL> create table interval_range(eno number(4),ename varchar2(12),deptno number(3))

2 partition by range(eno)

3 interval(500)

4 subpartition by range(deptno)

5 (partition part1 values less than (1000)

6 (subpartition sub_part1 values less than(5),

7 subpartition sub_part2 values less than(10)),

8 partition part2 values less than (2000)

9 (subpartition sub_part3 values less than(15),

10 subpartition sub_part4 values less than(20)));

Table created.

Composite: Interval-Range: Range Multi-Column Subpartition

SQL> create table interval_range(eno number(4),ename varchar2(12),deptno number(3))

2 partition by range(eno)

3 interval(500)

4 subpartition by range(eno,deptno)

5 (partition part1 values less than (1000)

6 (subpartition sub_part1 values less than(500,5),

7 subpartition sub_part2 values less than(1000,10)),

8 partition part2 values less than (2000)

9 (subpartition sub_part3 values less than(1500,15),

10 subpartition sub_part4 values less than(2000,20)));

Table created.

Composite: Interval-Hash Partition

SQL> create table interval_hash(eno number(4),ename varchar2(12),deptno number(3))

2 partition by range(eno)

3 interval(500)

4 subpartition by hash(deptno)

5 (partition part1 values less than (1000)

6 (subpartition sub_part1,

7 subpartition sub_part2),

8 partition part2 values less than (2000)

9 (subpartition sub_part3,

10 subpartition sub_part4));

Table created.

Composite: Interval-List Partition

SQL> create table interval_list(eno number(4),ename varchar2(12),deptno number(3))

2 partition by range(eno)

3 interval(500)

4 subpartition by list(deptno)

5 (partition part1 values less than (1000)

6 (subpartition sub_part1 values (’10’,’20’),

7 subpartition sub_part2 values (’30’,’40’)),

8 partition part2 values less than (2000)

9 (subpartition sub_part3 values (’50’,’60’),

10 subpartition sub_part4 values (’70’,’80’)));

Table created.

Posted in Oracle | Leave a comment

Upgrading Oracle 10.2.0.1 to 11.1.0.6

Posted on February 16, 2011 by Naveen K. SR

Upgrading from Oracle 10.2.0.1 to 11.1.0.6

What is Oracle Database Upgrade?

Oracle Upgrade is a process of changing the environment of the database without modifying the existing data and configurations to avail a robust features in the latest version and releases of the Oracle product, that internally will delivers more automation, new features, and flexibilities so that the data can be secure

Technically, Oracle Upgrade means changing the versions or the releases of the Oracle database within the same or in different servers or with different OS platforms (ex: version 9i to 10g, 10g to 11g, and also the release 10.1 to 10.2, 11.1 to 11.2) to the higher version/release that intern will upgrade the existing physical files state to the newer release/version

For upgrading, we will be using startup upgrade command. The startup migrate is deprecated from Oracle 10g for migration (Migration means moving the database from one OS platform to another, or from lower version/release to higer version/release as like upgrade, but the main difference here is we are logically extracting the physical files contents of data and we are loading it back to the higer version/release within the same or across the differnt OS servers, here the physcial files state doesnot change as like upgrade)

To know more about Version, release, and patch …

If you upgrade/migrate the database, the FIRST number and the SECOND number will changes. The FOURTH digit indicate the patchset number, this number will change whenever you apply the patchsets to the database.

Why the upgrading is needed?

Upgrading to Oracle Database 11g provides the latest in efficient, reliable, secure data management for mission-critical on-line transaction processing applications, query-intensive data warehouses, and content management and Web2.0 applications. The right planning, preparation, and upgrade steps will make the upgrade process simpler, faster, and more predictable from start to finish.

Types of upgrading

You can do the upgrading the databases in two methods like

1. Manual Upgrading by running the scripts manually in proper order

2. Using Database Upgrade Assistant (DBUA) utility, Oracle recommends to go with this

Compatibility Matrix

Minimum Version of the database that can be directly upgraded to Oracle 11g Release 1

Source Database	Target Database
9.2.0.4.0 OR higher	11.1.x
10.1.0.2.0 OR higher	11.1.x
10.2.0.1.0 OR higher	11.1.x

Choosing an upgrade method

The first method is called a manual upgrade. The manual upgrade approach gives you a great deal of control, but it also adds to the level of risk in the upgrade because you must perform the steps in the proper order.

The second method relies on the Database Upgrade Assistant (DBUA). DBUA is an interactive tool that guides you through the upgrade process. DBUA evaluates your present database configuration and recommends modifications that can be implemented during the upgrade process. These recommendations may include the sizing of files and the specifications for the new SYSAUX tablespace (if upgrading from a pre-Oracle 10g database). After you accept the recommendations, DBUA performs the upgrade in the background while a progress panel is displayed. DBUA is very similar in approach to Database Configuration Assistant (DBCA).

Selecting the proper upgrade method requires you to evaluate the technical expertise of your team, the data that is to be migrated, and the allowable downtime for the database during the migration. In general, using DBUA will be the method of choice for very large databases, whereas smaller databases may use an indirect method.

Before upgrading

Prior to beginning the migration, you should back up the existing database and database software. If the upgrades fails for some reason and you are unable to revert the database or software to its earlier version, you will be able to restore your backup and re-create your database.

You should develop and test scripts that will allow you to evaluate the performance and functionality of the database following the upgrade. This evaluation may include the performance of specific database operations or the overall performance of the database under a significant user load.

Prior to executing the upgrade process on a production database, you should attempt the upgrade on a test database so that any missing components (such as operating system patches) can be identified and the time required for the upgrade can be measured.

Prior to performing a upgrade, you should analyze the data dictionary tables. During the upgrade process to Oracle Database 11g, the data dictionary will be analyzed if it has not been analyzed already, so performing this step in advance will aid the performance of the upgrade.

Upgrading from Oracle 10.2.0.1 to 11.1.0.6

Prerequisites

Install the Oracle 11g Release 1 software

Throughout this documentation, we have used the following standards

	Source: Oracle 10g (10.2.0.1.0)	Target: Oracle 11g (11.1.0.6.0)
OS Platform	RHEL AS 4 Update 2	RHEL AS 4 Update 2
OS User	oracle	oracle
DB_NAME	orcl	orcl
ORACLE_BASE	/u01/app/o10g	/u01/app/o11g
ORACLE_HOME	/u01/app/o10g/product/10.2.0/db_1	/u01/app/o11g/product/11.1.0/db_1

Manually Upgrading

The manual upgrading the database includes:

1. Run pre-upgrading script ‘utlu111i.sql‘; if found problems, fix them

2. Backup of Oracle10g database

3. Bounce the Oracle11g database to ‘startup upgrade‘ and run ‘catupgrd.sql‘ script

4. Run post-upgrade script ‘utlu111s.sql‘

5. Change the database from upgrade mode to normal mode by running ‘catuppst.sql‘

6. Check for invalid objects; if found, run ‘utlrp.sql‘

1. Running the Pre-Upgrade tool

The Pre-Upgrade Information Tool is a SQL script that ships with Oracle Database 11g and must be copied to and run from the environment of the database being upgraded. Complete the following steps to run the Pre-Upgrade Information Tool:

1. Log into the system as the owner of the Oracle Database 11g

2. Copy the Pre-Upgrade Information Tool (utlu111i.sql) from the Oracle 11g’s ORACLE_HOME/rdbms/admin directory to a directory outside of the Oracle Home, such as the temporary directory on your system. Make a note of the new location of this file

3. Log into the system as the owner of the Oracle Database 10g, the database to be upgraded

4. Start SQL*Plus with sysdba privilege

5. Bounce the database to open phase

6. Set the system to spool results to a log file for later analysis and to gather the information

SQL> SPOOL pre_upgrade_info.log

7. Run the Pre-Upgrade Information Tool:

SQL> @/tmp/utlu111i.sql

8. Turn off the spooling of script results to the log file:

SQL> SPOOL OFF

Check the output of the Pre-Upgrade Information Tool in pre_upgrade_info.log for any identified problems prior to the upgrade.

9. Edit the pre_upgrade_info.log file to troubleshoot the problems

$ cat ~/pre_upgrade_info.log

SQL> @/tmp/utlu111i.sql

Oracle Database 11.1 Pre-Upgrade Information Tool 02-11-2011 16:16:52

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

Database:

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

–> name: ORCL

–> version: 10.2.0.1.0

–> compatible: 10.2.0.1.0

–> blocksize: 8192

–> platform: Linux IA (32-bit)

–> timezone file: V2

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

Tablespaces: [make adjustments in the current environment]

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

–> SYSTEM tablespace is adequate for the upgrade.

…. minimum required size: 693 MB

…. AUTOEXTEND additional space required: 243 MB

–> UNDOTBS1 tablespace is adequate for the upgrade.

…. minimum required size: 262 MB

…. AUTOEXTEND additional space required: 62 MB

–> SYSAUX tablespace is adequate for the upgrade.

…. minimum required size: 438 MB

…. AUTOEXTEND additional space required: 178 MB

–> DEF_TEMP tablespace is adequate for the upgrade.

…. minimum required size: 61 MB

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

Update Parameters: [Update Oracle Database 11.1 init.ora or spfile]

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

— No update parameter changes are required.

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

Renamed Parameters: [Update Oracle Database 11.1 init.ora or spfile]

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

— No renamed parameters found. No changes are required.

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

Obsolete/Deprecated Parameters:[Update Oracle Database11.1 init.ora or spfile]

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

–> “background_dump_dest” replaced by “diagnostic_dest”

–> “user_dump_dest” replaced by “diagnostic_dest”

–> “core_dump_dest” replaced by “diagnostic_dest”

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

Components: [The following database components will be upgraded or installed]

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

–> Oracle Catalog Views [upgrade] VALID

–> Oracle Packages and Types [upgrade] VALID

–> JServer JAVA Virtual Machine [upgrade] VALID

–> Oracle XDK for Java [upgrade] VALID

–> Oracle Workspace Manager [upgrade] VALID

–> OLAP Analytic Workspace [upgrade] VALID

–> OLAP Catalog [upgrade] VALID

–> EM Repository [upgrade] VALID

–> Oracle Text [upgrade] VALID

–> Oracle XML Database [upgrade] VALID

–> Oracle Java Packages [upgrade] VALID

–> Oracle interMedia [upgrade] VALID

–> Spatial [upgrade] VALID

–> Data Mining [upgrade] VALID

–> Expression Filter [upgrade] VALID

–> Rule Manager [upgrade] VALID

–> Oracle OLAP API [upgrade] VALID

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

Miscellaneous Warnings

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

WARNING: –> Database is using an old timezone file version.

…. Patch the 10.2.0.1.0 database to timezone file version 4

…. BEFORE upgrading the database. Re-run utlu111i.sql after

…. patching the database to record the new timezone file version.

WARNING: –> Database contains stale optimizer statistics.

…. Refer to the 11g Upgrade Guide for instructions to update

…. statistics prior to upgrading the database.

…. Component Schemas with stale statistics:

…. SYS

…. OLAPSYS

…. SYSMAN

WARNING: –> Database contains schemas with objects dependent on network

packages.

…. Refer to the 11g Upgrade Guide for instructions to configure Network ACLs.

…. USER SYSMAN has dependent objects.

WARNING: –> EM Database Control Repository exists in the database.

…. Direct downgrade of EM Database Control is not supported. Refer to the

…. 11g Upgrade Guide for instructions to save the EM data prior to upgrade.

PL/SQL procedure successfully completed.

This scripts will generates to check whether all the properties will match during upgradings,

Tablespaces: Verifies that the tablespaces are suitable to upgrade, if any problems of space, increase the size of tablespaces

Update parameters: Check the update parameters from 10.2.0.1 to 11.1.0.6

Renamed Parameters: Check the renamed parameters in 11.1.0.6

Obsolete/Deprecated parameters: Remove the parameters which are deprecated in the 11.1.0.6 version. More about the deprecated parameters, refer the Oracle 11g documentation

Components: Verify all the components are VALID, if they show INVALID, run ‘utlrp.sql‘ from Oracle10g’s ORACLE_HOME to make them VALID.

Miscellaneous Warnings: The timezone file version should be upgraded from 2 to 4.

The remaining miscellaneous warnings can be ignored.

Upgrading the timezone file version from 2 to 4

Following are the critical steps to be executed based on above warnings. These commands are to be executed while connecting to database from 10g Oracle Home

WARNING: –> Database is using an old timezone file version.

…. Patch the 10.2.0.1.0 database to timezone file version 4

…. BEFORE upgrading the database. Re-run utlu111i.sql after

…. patching the database to record the new timezone file version.

Finding the Version of existing timezone files in Oracle 10g:

SQL> select * from v$timezone_file;

FILENAME VERSION

———— ———-

timezlrg.dat 2

For 10.2.0.1, check the metalink note ID 413671.1. The table which defines the patch to be applied to upgrade the timezone.

If there is no official patchset for the version you are currently having then you can Identify patch for a different patchset, but with same release.

For example if you run 10.2.0.1 and there is no particular patch release. In this case we can make use of patch 5632264 for 10.2.0.2 and 10.2.0.3, and this will be applicable to 10.2.0.1 as well.

Please follow the metalink note ID 396387.1

Once you identify the correct patchset (5632264 for 10.2.X), download the same and unzip it.

1. Unzip the patch file p5632264_10203_Linux-x86-64.zip

$ unzip p5632264_10203_Linux-x86-64.zip

2. Change directory to the 5632264 and list, which will contain the timezone files

3. Backup the existing timezone files in the Oracle10g’s ORACLE_HOME, these will be present under $ORACLE_HOME/oracore/zoneinfo directory

4. Copy the newly extracted files to the ORACLE_HOME path

$ cp -r 5632264/files/oracore/zoneinfo $ORACLE_HOME/oracore/zoneinfo

5. If the new timezone changes has to come to an effect, restart the database and select the timezone

SQL> select * from v$timezone_file;

FILENAME VERSION

———— ———-

timezlrg.dat 4

NOTE:

Do not follow this method of applying the patch for all the patch-sets. That will corrupt the ORACLE_HOME path and may need to reinstall the software again. For each individual patch installation, please refer to the Oracle Patchset Documentation’s

To Solve Stale Optimizer Statistics Warning

WARNING: –> Database contains stale optimizer statistics.

…. Refer to the 11g Upgrade Guide for instructions to update

…. statistics prior to upgrading the database.

…. Component Schemas with stale statistics:

…. SYS

…. OLAPSYS

…. SYSMAN

Gather the dictionary statistics by executing the PL/SQL blocks

SQL> EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;

PL/SQL procedure successfully completed.

SQL> EXEC DBMS_STATS.GATHER_SCHEMA_STATS(‘SYS’);

PL/SQL procedure successfully completed.

SQL> EXEC DBMS_STATS.GATHER_SCHEMA_STATS(‘OLAPSYS’);

PL/SQL procedure successfully completed.

SQL> EXEC DBMS_STATS.GATHER_SCHEMA_STATS(‘SYSMAN’);

PL/SQL procedure successfully completed.

NOTE:

Gather the dictionary statistics by executing DBMS_STATS.GATHER_SCHEMA_STATS for all the users as per the warning.

Re-run the pre-upgrade script again

1. Log into the system as the owner of the Oracle Database 11g

3. Log into the system as the owner of the Oracle Database 10g, the database to be upgraded

4. Start SQL*Plus with sysdba privilege

5. Bounce the database to open phase

6. Set the system to spool results to a log file for later analysis and to gather the information

7. Run the Pre-Upgrade Information Tool:

SQL> @/tmp/utlu111i.sql

8. Turn off the spooling of script results to the log file:

9. Check for upgrading warnings again

$ cat pre_upgrade_info.log

SQL> @/tmp/utlu111i.sql

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

Miscellaneous Warnings

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

WARNING: –> Database contains schemas with objects dependent on network

packages.

…. Refer to the 11g Upgrade Guide for instructions to configure Network ACLs.

…. USER SYSMAN has dependent objects.

WARNING: –> EM Database Control Repository exists in the database.

…. Direct downgrade of EM Database Control is not supported. Refer to the

…. 11g Upgrade Guide for instructions to save the EM data prior to upgrade.

2. Backup the Oracle 10g database

Perform the Oracle 10g database backup in any of the methods like cold backup, hot backup or RMAN backups

Backup the ORACLE_HOME software path too.

3. Create parameter file for Oracle 11g

Backup the Oracle10g’s parameter file and paste under Oracle11g’s default path

$ cp /u01/app/o10g/product/10.2.0/db_1/dbs/initorcl.ora /u01/app/o11g/product/11.1.0/db_1/dbs/initorcl.ora

Edit the Oracle 11g’s parameter file to remove the Obsolete/Deprecated parameters, as listed in the pre_upgrade_info.log file

4. Upgrade the database

1. If the Oracle10g database is still running, shutdown the database

2. Export the variables for Oracle 11g

$ export ORACLE_SID=orcl

$ export ORACLE_BASE=/u01/app/o11g

$ export ORACLE_HOME=/u01/app/o11g/product/11.1.0/db_1

$ export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH

$ export PATH=$ORACLE_HOME/bin:$PATH

3. Start the SQL*Plus with sysdba privilege

4. Start the Oracle11g database using the ‘startup upgrade‘

SQL> startup upgrade

5. Verify the version of Oracle

SQL> select * from v$version;

BANNER

———————————————————————-

Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 – Production

PL/SQL Release 11.1.0.6.0 – Production

CORE 11.1.0.6.0 Production

TNS for Linux: Version 11.1.0.6.0 – Production

NLSRTL Version 11.1.0.6.0 – Production

6. Upgrade the data dictionary tables via the catupgrd.sql script.

SQL> @$ORACLE_HOME/rdbms/admin/catupgrd.sql

From hereafter the database downtime will starts and the upgrading a database will take its own time to completes depending upon the size of the database and environment.

Once the upgrades completes, the database will shutdown automatically

5. Post Upgrading Steps

1. Once the upgrade completed, reinitialize the system parameters for normal startup and bounce back the database by login again as sys with sysdba privilege, and start the database to normal mode

SQL> startup

2. Check the dba_registry for the components and its status

SQL> select comp_name, status, version from dba_registry;

3. Run ‘utlu111s.sql‘ script to display the results of the upgrade

SQL> @$ORACLE_HOME/rdbms/admin/utlu111s.sql

4. Run ‘catuppst.sql‘ script from $ORACLE_HOME/rdbms/admin directory, to perform upgrade actions that change the database from UPGRADE mode to the NORMAL mode

SQL> @$ORACLE_HOME/rdbms/admin/catuppst.sql

5. Find for the invalid objects after the upgrade

SQL> select count(*) from dba_objects where status=’INVALID’;

COUNT(*)

———

1572

6. Run ‘$ORACLE_HOME/rdbms/admin/utlrp.sql‘ script to recompile the INVALID objects

SQL> @$ORACLE_HOME/rdbms/admin/utlrp.sql

7. Again find the status for invalid objects after recompiling them

SQL> select count(*) from dba_objects where status=’INVALID’;

COUNT(*)

———-

This will completes the manually upgrading the Oracle database from 10.2.0.1 to 11.1.0.6

Using the Database Upgrade Assistant (DBUA)

You can start the Database Upgrade Assistant (DBUA) via the shipped utility in Oracle 11g, the dbua command (in UNIX environments) or by selecting ªDatabase Upgrade Assistantº from the Oracle Configuration and Migration Tools menu option (in Windows environments).

The scripts we had executing in the Manually in the previous method will be executed and taken care by the DBUA utility

Follow the steps to upgrade the Oracle 10.2.0.1 database to 11.1.0.6:

1. Login in as Oracle 11g user, and initiate all the variables need for the environment

$ export ORACLE_BASE=/u01/app/o11g

$ export ORACLE_HOME=/u01/app/o11g/product/11.1.0/db_1

$ export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH

$ export PATH=$ORACLE_HOME/bin:$PATH

2. Confirm the Oracle 10g database name and ORACLE_HOME path is entered in /etc/oratab file

$ cat /etc/oratab

orcl:/u01/app/o10g/product/10.2.0/db_1:N

3. Execute the dbca utility from Oracle 11g’s path

$ $ORACLE_HOME/bin/dbua

4. A Welcome screen will appear, click Next

5. Select the database you want to upgrade from the list of available databases. You can upgrade only one database at a time. For all the list of databases, the database names should be listed in /etc/oratab file (Step 2). Click Next

6. The dbua utility will check the warnings should be taken care during the upgrading

Here again we need to solve the warning like update the timezone file version as a critical warning, and remaining warning you can skip. If you continue with this screen, at the point of in time the dbua will through an error and the upgrading the database will fail.

Its a better practice to solve the warnings.

Upgrading the timezone file version from 2 to 4

Following are the critical steps to be executed based on above warnings. These commands are to be executed while connecting to database from 10g Oracle Home

WARNING: –> Database is using an old timezone file version.

…. Patch the 10.2.0.1.0 database to timezone file version 4

…. BEFORE upgrading the database. Re-run utlu111i.sql after

…. patching the database to record the new timezone file version.

Finding the Version of existing timezone files in Oracle 10g:

SQL> select * from v$timezone_file;

FILENAME VERSION

———— ———-

timezlrg.dat 2

For 10.2.0.1, check the metalink note ID 413671.1. The table which defines the patch to be applied to upgrade the timezone.

If there is no official patchset for the version you are currently having then you can Identify patch for a different patchset, but with same release.

For example if you run 10.2.0.1 and there is no particular patch release. In this case we can make use of patch 5632264 for 10.2.0.2 and 10.2.0.3, and this will be applicable to 10.2.0.1 as well.

Please follow the metalink note ID 396387.1

Once you identify the correct patchset (5632264 for 10.2.X), download the same and unzip it.

1. Unzip the patch file p5632264_10203_Linux-x86-64.zip

$ unzip p5632264_10203_Linux-x86-64.zip

2. Change directory to the 5632264 and list, which will contain the timezone files

3. Backup the existing timezone files in the Oracle10g’s ORACLE_HOME, these will be present under $ORACLE_HOME/oracore/zoneinfo directory

4. Copy the newly extracted files to the ORACLE_HOME path

$ cp -r 5632264/files/oracore/zoneinfo $ORACLE_HOME/oracore/zoneinfo

5. If the new timezone changes has to come to an effect, restart the database and select the timezone

SQL> select * from v$timezone_file;

FILENAME VERSION

———— ———-

timezlrg.dat 4

NOTE:

To Solve Stale Optimizer Statistics Warning

WARNING: –> Database contains stale optimizer statistics.

…. Refer to the 11g Upgrade Guide for instructions to update

…. statistics prior to upgrading the database.

…. Component Schemas with stale statistics:

…. SYS

…. OLAPSYS

…. SYSMAN

Gather the dictionary statistics by executing the PL/SQL blocks

SQL> EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;

PL/SQL procedure successfully completed.

SQL> EXEC DBMS_STATS.GATHER_SCHEMA_STATS(‘SYS’);

PL/SQL procedure successfully completed.

SQL> EXEC DBMS_STATS.GATHER_SCHEMA_STATS(‘OLAPSYS’);

PL/SQL procedure successfully completed.

SQL> EXEC DBMS_STATS.GATHER_SCHEMA_STATS(‘SYSMAN’);

PL/SQL procedure successfully completed.

NOTE:

Gather the dictionary statistics by executing DBMS_STATS.GATHER_SCHEMA_STATS for all the users as per the .warning.

This will solve the main warnings that got from the above step, the remaining warning can be ignored.

Rerun the dbua utility again and clear the previous steps, now the warnings will be ease to upgrading the database, you can ignore these warnings

Click ‘Yes’, this will continue with the further steps of upgrading.

7. The next screen will allow to select the ORACLE_BASE and the Diagnostic Destination, Click Next

8. You can then select to have your database files moved during the upgrade process. Click Next

9. DBUA will then prompt you for a flash recovery area destination for the storage of backup and-recovery-related files. Click Next

10. DBUA will then prompt you to recompile invalid PL/SQL objects following the upgrade. If you do not recompile these objects after the upgrade, the first user of these objects will be forced to wait while Oracle performs a run-time recompilation. Click Next

11. DBUA will prompt for back up the database as part of the upgrade process. If already backed up the database prior to starting DBUA, elect to skip this step. If you choose to have DBUA back up the database, it will shut down the database and perform an offline backup. DBUA will also create a batch file in that directory to automate the restoration of those files to their earlier locations. Click Next

12. If Oracle detects multiple Oracle Net listeners on the server, you will then be prompted to select a listener for your database, and the network configuration details will be displayed for your review and editing. Click Continue

13. A final summary screen displays your choices for the upgrade, and the upgrade starts when you accept them. Click Finish

14. The DBUA will start upgrading the database, the upgrading logs can be read in the ORACLE_BASE path

15. The DBUA will complete the upgrading the database, Click ‘OK’

16. After the upgrade has completed, DBUA will display the Upgrade Results screen, showing the steps performed, the related log files, and the status.

17. The section of the screen titled Password Management allows you to manage the passwords and the locked/unlocked status of accounts in the upgraded database.

If you are not satisfied with the upgrade results, you can choose the Restore option. If you used DBUA to perform the backup, the restoration will be performed automatically; otherwise, you will need to perform the restoration manually.

When you exit DBUA after successfully upgrading the database, DBUA removes the old database’s entry in the network listener configuration file, inserts an entry for the upgraded database, and reloads the file.

After Upgrading

Following the upgrade, you should double-check the configuration and parameter files related to the database, particularly if the instance name changed in the migration process. These files include

The tnsnames.ora file

The listener.ora file

NOTE:

You will need to manually reload the modified listener.ora file if you are not using DBUA to perform the upgrade.

Additional post-upgrade steps should include verifying the values for all environment variables (such as ORACLE_HOME and PATH) and upgrading the recovery catalog.

You should review your database initialization parameters to make sure deprecated and obsolete parameters have been removed; these should have been identified during the migration process. Be sure to recompile any programs you have written that rely on the database software libraries.

Once the upgrade has completed, perform the functional and performance tests identified before the upgrade began. If there are issues with the database functionality, attempt to identify any parameter settings or missing objects that may be impacting the test results. If the problem cannot be resolved, you may need to revert to the prior release.

Posted in Oracle | 16 Comments

ASM to Non-ASM

Posted on December 16, 2010 by Naveen K. SR

Migrating ASM Database to Non-ASM

1.Find the actual paths of all the files like spfile, controlfile, datafiles, tempfiles, and onlinelog files.

2.Create the parameterfile: A parameterfile can be created from spfile which has been stored in ASM diskgroup

SQL> create pfile from spfile=’+dg1/orcl/parameterfile/spfile.260.737833097′;

File created.

3.Shutdown the database

SQL> shu immediate

4.Make the parameter file ready:

Edit the created pfile

$ vi $ORACLE_HOME/dbs/initorcl.ora

Make the necessary changes

*.control_files=’/u01/oracle/oradata/’

*.db_create_file_dest=’/u01/oracle/oradata’

*.db_create_online_log_dest_1=’/u01/oracle/oradata’

Save and create

5.Connect to RMAN

$ rman target /

Recovery Manager: Release 10.2.0.1.0 – Production on Thu Dec 16 14:50:07 2010

connected to target database (not started)

6.Start the database in NOMOUNT phase usind RMAN

RMAN> startup nomount

Oracle instance started

Total System Global Area 629145600 bytes

Fixed Size 1220964 bytes

Variable Size 176164508 bytes

Database Buffers 448790528 bytes

Redo Buffers 2969600 bytes

Now the database instance has been started with the new parameter file

7.Create the Controlfile: Controlfile can be created using RMAN

RMAN> restore controlfile from ‘+dg1/orcl/controlfile/backup.268.737822625’;

Starting restore at 16-DEC-10

using channel ORA_DISK_1

channel ORA_DISK_1: copied control file copy

output filename=/u01/oracle/oradata/ORCL/controlfile/o1_mf_6jmpkwff_.ctl

Finished restore at 16-DEC-10

8.Mount the database: Using the created controlfile mount the database using RMAN

RMAN> alter database mount;

database mounted

released channel: ORA_DISK_1

9.Backup the datafiles: Backup of datafiles can be done using RMAN

RMAN> backup as copy database format ‘/u01/oracle/oradata/ORCL/datafile/%U’;

Starting backup at 16-DEC-10

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile copy

input datafile fno=00003 name=+DG1/orcl/datafile/sysaux.258.737822695

output filename=/u01/oracle/oradata/ORCL/datafile/data_D-ORCL_I-1265601965_TS-SYSAUX_FNO-3_0alvn73i tag=TAG20101216T145754 recid=11 stamp=737909932

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:05

channel ORA_DISK_1: starting datafile copy

input datafile fno=00001 name=+DG1/orcl/datafile/system.257.737822751

output filename=/u01/oracle/oradata/ORCL/datafile/data_D-ORCL_I-1265601965_TS-SYSTEM_FNO-1_0blvn75j tag=TAG20101216T145754 recid=12 stamp=737909992

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:56

channel ORA_DISK_1: starting datafile copy

input datafile fno=00002 name=+DG1/orcl/datafile/undotbs.261.737822817

output filename=/u01/oracle/oradata/ORCL/datafile/data_D-ORCL_I-1265601965_TS-UNDOTBS_FNO-2_0clvn77b tag=TAG20101216T145754 recid=13 stamp=737910007

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15

channel ORA_DISK_1: starting datafile copy

input datafile fno=00004 name=+DG1/orcl/datafile/def_perm.256.737822843

output filename=/u01/oracle/oradata/ORCL/datafile/data_D-ORCL_I-1265601965_TS-DEF_PERM_FNO-4_0dlvn77q tag=TAG20101216T145754 recid=14 stamp=737910016

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:08

channel ORA_DISK_1: starting datafile copy

input datafile fno=00005 name=+DG1/orcl/datafile/tbs1.262.737823963

output filename=/u01/oracle/oradata/ORCL/datafile/data_D-ORCL_I-1265601965_TS-TBS1_FNO-5_0elvn782 tag=TAG20101216T145754 recid=15 stamp=737910021

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07

channel ORA_DISK_1: starting datafile copy

copying current control file

output filename=/u01/oracle/oradata/ORCL/datafile/cf_D-ORCL_id-1265601965_0flvn789 tag=TAG20101216T145754 recid=16 stamp=737910027

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:04

Finished backup at 16-DEC-10

10.Switch the database: Switch the database to the copy of datafiles created in the last step

RMAN> switch database to copy;

datafile 1 switched to datafile copy “/u01/oracle/oradata/ORCL/datafile/data_D-ORCL_I-1265601965_TS-SYSTEM_FNO-1_0blvn75j”

datafile 2 switched to datafile copy “/u01/oracle/oradata/ORCL/datafile/data_D-ORCL_I-1265601965_TS-UNDOTBS_FNO-2_0clvn77b”

datafile 3 switched to datafile copy “/u01/oracle/oradata/ORCL/datafile/data_D-ORCL_I-1265601965_TS-SYSAUX_FNO-3_0alvn73i”

datafile 4 switched to datafile copy “/u01/oracle/oradata/ORCL/datafile/data_D-ORCL_I-1265601965_TS-DEF_PERM_FNO-4_0dlvn77q”

datafile 5 switched to datafile copy “/u01/oracle/oradata/ORCL/datafile/data_D-ORCL_I-1265601965_TS-TBS1_FNO-5_0elvn782”

11.Open the database: Open the database using backup controlfile

Connect to the SQL prompt

SQL> recover database using backup controlfile until cancel;

ORA-00279: change 186477 generated at 12/16/2010 14:46:06 needed for thread 1

ORA-00289: suggestion :

/u01/oracle/product/10.2.0/db_1/dbs/arch1_87_737821229.dbf

ORA-00280: change 186477 for thread 1 is in sequence #87

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

cancel

Media recovery cancelled.

12.Open the database: Open the database with resetlogs

SQL> alter database open resetlogs;

Database altered.

13.Create the TEMP files: Though the RMAN will backup only the datafiles, we need to manually update the temporary tablespaces by adding the tempfiles

SQL> alter tablespace def_temp add tempfile;

Tablespace altered.

Identify the No, of tempfiles

SQL> select name from v$tempfile;

NAME

——————————————————————————–+DG1/orcl/tempfile/def_temp.264.737822965

/u01/oracle/oradata/ORCL/datafile/o1_mf_def_temp_6jmqf6q4_.tmp

Remove the tempfile which is part of ASM diskgroup

SQL> alter tablespace def_temp drop tempfile ‘+DG1/orcl/tempfile/def_temp.264.737822965’;

Tablespace altered.

14.Adding onlinelog files:

Identify the existing onlinelog files and they status

SQL> select group#, status from v$log;

GROUP# STATUS

———- —————-

1 ACTIVE

2 CURRENT

Add some more logfile group

SQL> alter database add logfile size 5m;

Database altered.

SQL> alter database add logfile size 5m;

Database altered.

15.Identify the Logfile group status and and start removing the groups which are part of ASM diskgroup

SQL> select group#, status from v$log;

GROUP# STATUS

———- —————-

1 ACTIVE

2 CURRENT

3 UNUSED

4 UNUSED

16.Perform the manual logswitch so that the logfiles which are part of ASM diskgroup becomes INACTIVE

SQL> alter system switch logfile;

System altered.

17.Remove the INACTIVE groups which are part of ASM diskgroup

SQL> select group#, status from v$log;

GROUP# STATUS

———- —————-

1 INACTIVE

2 INACTIVE

3 INACTIVE

4 CURRENT

SQL> alter database drop logfile group 1;

Database altered.

SQL> alter database drop logfile group 2;

Database altered.

18.Identify the proper onlinelog files which are part of filesystem

SQL> select group#, member from v$logfile;

GROUP# MEMBER

———- ——————————————————————————–

1 /u01/oracle/oradata/ORCL/onlinelog/o1_mf_1_6jmqhsl8_.log

2 /u01/oracle/oradata/ORCL/onlinelog/o1_mf_2_6jmqhxdr_.log

19.Delete the ASM diskgroup files: If you want to delete the ASM diskgroup files that can be done by using RMAN

RMAN> delete noprompt force copy;

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=27 devtype=DISK

specification does not match any archive log in the recovery catalog

List of Datafile Copies

Key File S Completion Time Ckp SCN Ckp Time Name

——- —- – ————— ———- ————— —-

17 1 A 16-DEC-10 186477 16-DEC-10 +DG1/orcl/datafile/system.257.737822751

18 2 A 16-DEC-10 186477 16-DEC-10 +DG1/orcl/datafile/undotbs.261.737822817

19 3 A 16-DEC-10 186477 16-DEC-10 +DG1/orcl/datafile/sysaux.258.737822695

20 4 A 16-DEC-10 186477 16-DEC-10 +DG1/orcl/datafile/def_perm.256.737822843

21 5 A 16-DEC-10 186477 16-DEC-10 +DG1/orcl/datafile/tbs1.262.737823963

List of Control File Copies

Key S Completion Time Ckp SCN Ckp Time Name

——- – ————— ———- ————— —-

16 A 16-DEC-10 186477 16-DEC-10 /u01/oracle/oradata/ORCL/datafile/cf_D-ORCL_id-1265601965_0flvn789

deleted datafile copy

datafile copy filename=+DG1/orcl/datafile/system.257.737822751 recid=17 stamp=737910182

deleted datafile copy

datafile copy filename=+DG1/orcl/datafile/undotbs.261.737822817 recid=18 stamp=737910182

deleted datafile copy

datafile copy filename=+DG1/orcl/datafile/sysaux.258.737822695 recid=19 stamp=737910182

deleted datafile copy

datafile copy filename=+DG1/orcl/datafile/def_perm.256.737822843 recid=20 stamp=737910182

deleted datafile copy

datafile copy filename=+DG1/orcl/datafile/tbs1.262.737823963 recid=21 stamp=737910182

deleted control file copy

Deleted 5 objects

20.Delete the necessary files which are part of ASM diskgroup by connect to ‘asmcmd’ interactive mode

Now entire database is placed out of the ASM diskgroup

Posted in ASM, Oracle | 2 Comments

Non ASM to ASM

Posted on December 15, 2010 by Naveen K. SR

Converting Non-ASM Database to ASM Database

1.Create the ASM instance and start the instance. Add the diskgroups say DG1. DG1 is used for datafile, controlfile, redologfile, and tempfiles.

2.Create a database ‘ORCL’ in ‘/u01/oracle/oradata/’

3.Shutdown the database if it is running using normal mode

SQL> shu

4.If your database was running in spfile, create pfile from spfile

SQL> create pfile from spfile;

5.Edit the pfile

$ vi $ORACLE_HOME/dbs/initORCL.ora

6.Make the necessary changes

CONTROL_FILES= ‘+DG1’

DB_CREATE_FILE_DEST=’DG1′

DB_CREATE_ONLINE_LOG_DEST_1=’DG1′

……

7.Execute the RMAN utility,

$ export ORACLE_SID=ORCL

$ rman target /

Recovery Manager: Release 10.2.0.1.0 – Production on Thu Dec 9 14:01:50 2010

connected to target database (not started)

RMAN>

8.Start the RDBMS instance to nomount phase using RMAN

RMAN> startup nomount

Oracle instance started

Total System Global Area 629145600 bytes

Fixed Size 1220964 bytes

Variable Size 171970204 bytes

Database Buffers 452984832 bytes

Redo Buffers 2969600 bytes

9.Restore the controlfile from the existing path using the RMAN

RMAN> restore controlfile from ‘/u01/oracle/oradata/controlfile/cntrl_01.ctl’;

Starting restore at 09-DEC-10

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=36 devtype=DISK

channel ORA_DISK_1: copied control file copy

output filename=+DG1/orcl/controlfile/backup.256.737301987

Finished restore at 09-DEC-10

This will create a copy of controfile in +DG1 diskgroup. You can verify this creation by using

$ export ORACLE_SID=+ASM

$ asmcmd -p

ASMCMD [+] > ls

DG1

ASMCMD [+] > cd DG1/ORCL/CONTROLFILE/

ASMCMD [+DG1/ORCL/CONTROLFILE] > ls -l

Type Redund Striped Time Sys Name

CONTROLFILE HIGH FINE DEC 09 14:00:00 Y Backup.261.737302375

10.Alter the database to mount stage using RMAN

RMAN> alter database mount;

database mounted

released channel: ORA_DISK_1

Database brought to the mount state by using the controlfile which had been created in the ASM diskgroup, you can examine this by

$ export ORACLE_SID=ORCL

$ sqlplus / as sysdba

SQL> select name from v$controlfile;

NAME

———————————————–

DG1/orcl/controlfile/backup.261.737302375

11.Backup the database using the RMAN to the diskgroup ‘+DG1’

RMAN> backup as copy database format ‘+dg1’;

Starting backup at 09-DEC-10

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=32 devtype=DISK

channel ORA_DISK_1: starting datafile copy

input datafile fno=00003 name=/u01/oracle/oradata/datafile/SYSAUX01.dbf

output filename=+DG1/orcl/datafile/sysaux.257.737302111 tag=TAG20101209T140830 recid=1 stamp=737302214

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:46

channel ORA_DISK_1: starting datafile copy

input datafile fno=00001 name=/u01/oracle/oradata/datafile/SYSTEM01.dbf

output filename=+DG1/orcl/datafile/system.258.737302219 tag=TAG20101209T140830 recid=2 stamp=737302318

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:45

channel ORA_DISK_1: starting datafile copy

input datafile fno=00002 name=/u01/oracle/oradata/datafile/UNDOTBS01.dbf

output filename=+DG1/orcl/datafile/undotbs.259.737302325 tag=TAG20101209T140830 recid=3 stamp=737302340

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25

channel ORA_DISK_1: starting datafile copy

input datafile fno=00004 name=/u01/oracle/oradata/datafile/DEF_PERM01.dbf

output filename=+DG1/orcl/datafile/def_perm.260.737302349 tag=TAG20101209T140830 recid=4 stamp=737302365

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25

channel ORA_DISK_1: starting datafile copy

copying current control file

output filename=+DG1/orcl/controlfile/backup.261.737302375 tag=TAG20101209T140830 recid=5 stamp=737302378

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07

Finished backup at 09-DEC-10

Now RMAN has created the copy of the database.

12.Switch the database files path from existing path to the new ASM diskgroup path

RMAN> switch database to copy;

datafile 1 switched to datafile copy “+DG1/orcl/datafile/system.258.737302219”

datafile 2 switched to datafile copy “+DG1/orcl/datafile/undotbs.259.737302325”

datafile 3 switched to datafile copy “+DG1/orcl/datafile/sysaux.257.737302111”

datafile 4 switched to datafile copy “+DG1/orcl/datafile/def_perm.260.737302349”

13.Perform incomplete recovery and open the database with the RESETLOGS option

SQL> recover database using backup controlfile until cancel;

ORA-00279: change 7937583 generated at 12/09/2010 20:33:55 needed for thread 1

ORA-00289: suggestion : +DG1

ORA-00280: change 7937583 for thread 1 is in sequence #36

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

CANCEL

Media recovery cancelled.

Then, bring the database to open phase with resetlogs option

RMAN> alter database open resetlogs;

database opened

You can verify the datafile paths for the tablespaces using the views like dba_data_files or v$datafile

SQL> select tablespace_name, file_name from dba_data_files;

TABLESPACE_NAME FILE_NAME

—————- ———————————————

SYSTEM +DG1/orcl/datafile/system.258.737302219

UNDOTBS +DG1/orcl/datafile/undotbs.259.737302325

SYSAUX +DG1/orcl/datafile/sysaux.257.737302111

DEF_PERM +DG1/orcl/datafile/def_perm.260.737302349

We had one more tablespace DEF_TEMP is a default temporary tablespace made up of tempfiles. RMAN will take the structure copy of the temporary tablespaces but not the tempfile, Will see more about how to restore the tempfile in step 15

14.If you want to delete the old copy of datafiles, you can delete them using RMAN

RMAN> delete copy of database;

released channel: ORA_DISK_1

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=270 devtype=DISK

List of Datafile Copies

Key File S Completion Time Ckp SCN Ckp Time Name

——- —- – ————— ———- ————— —-

8 1 A 09-DEC-10 392632 09-DEC-10 /u01/oracle/oradata/system01.dbf

9 2 A 09-DEC-10 392632 09-DEC-10 /u01/oracle/oradata/undotbs01.dbf

10 3 A 09-DEC-10 392632 09-DEC-10 /u01/oracle/oradata/sysaux01.dbf

11 4 A 09-DEC-10 392632 09-DEC-10 /u01/oracle/oradata/def_perm01.dbf

Do you really want to delete the above objects (enter YES or NO)? YES

deleted datafile copy

datafile copy filename=/u01/oracle/oradata/system01.dbf recid=8 stamp=541172332

deleted datafile copy

datafile copy filename=/u01/oracle/oradata/undotbs01.dbf recid=9 stamp=541172332

deleted datafile copy

datafile copy filename=/u01/oracle/oradata/sysaux01.dbf recid=10 stamp=541172332

deleted datafile copy

datafile copy filename=/u01/oracle/oradata/def_perm01.dbf recid=11 stamp=541172333

Deleted 4 object

15.Add the tempfiles to the DEF_TEMP tablespace, though the RMAN doesn’t take the backup of temporary files physically.

SQL> alter tablespace def_temp add tempfile;

Tablespace altered.

Now the temporary file will be created in the ASM diskgroup, though we had set the DB_CREATE_FILE_DEST parameter. You can examine it by using v$tempfile view orif you want to examine the creation of tempfile in ASM diskgroup

If you want to examine the creation of tempfile

ASMCMD [+] > cd dg1/orcl/tempfile

ASMCMD [+DG1/ORCL/TEMPFILE] > ls

DEF_TEMP.265.737391815

16.We had only moved the datafiles and tempfiles till yet. To change the Non-ASM online redolog files to ASM redologs, add logfile groups

SQL> alter database add logfile;

Database altered.

Here the logfile group will be created in the ASM diskgroup with size 100m, if you want it for different size use the keyword ‘size’

17.Add somemore logfile groups

SQL> alter database add logfile;

Database altered.

SQL> alter database add logfile;

Database altered.

If you want to examine the path of the redolog files

SQL> select group#, member from v$logfile;

GROUP# MEMBER

———- ————————————————-

1 /u01/oracle/oradata/redologfile/log_01_01.log

2 /u01/oracle/oradata/redologfile/log_02_01.log

3 +DG1/orcl/onlinelog/group_3.262.737304349

4 +DG1/orcl/onlinelog/group_4.263.737304363

5 +DG1/orcl/onlinelog/group_5.264.737304371

18.Identify the status of the logfile groups

SQL> select group#, status from v$log;

GROUP# STATUS

———- —————-

1 ACTIVE

2 CURRENT

3 UNUSED

4 UNUSED

5 UNUSED

19.Perform the manual log switches, so that the group 1 and group 2 turns to be INACTIVE

SQL> alter system switch logfile;

System altered.

20.Remove the group which are placed in the OS filesystems once they status turns to be INACTIVE i.e. group 1 and group 2

SQL> alter database drop logfile group 1;

Database altered.

SQL> alter database drop logfile group 2;

Database altered.

21.Place the parameter file in the ASM diskgroup

SQL> create spfile=’+DG1′ from pfile;

You can verify the creation by

ASMCMD [+] > cd dg1/orcl/parameterfile

ASMCMD [+dg1/orcl/parameterfile] > ls

spfile.267.737406545

22.Edit the pfile and make the changes

$ vi $ORACLE_HOME/dbs/initORCL.ora

Commit (#) all the parameters and enter

spfile=’+DG1/ORCL/PARAMETERFILE/spfile.267.737406545

23.Save and quit

24.Restart the database, the database will start using spfile from ASM diskgroup

Now your database is completely placed in the ASM diskgroups.

Posted in ASM, Oracle | Leave a comment

Configuration of Samba

Posted on December 10, 2010 by Naveen K. SR

What is Samba?

Samba is a file server that used to share the files and directories between Linux and Windows operating systems.

Implementation of Samba can be done in multiple ways. Here is simple steps how the implementations can be done.

Make sure that while installing Linux, you had installed Samba Server Configuration Tool from Server Configuration Tools. This is the tools which helps us to build an samba service between Windows and Linux and vice-versa

Set the IP’s on both the systems, check the connectivity. If the things are working fine then proceed with these steps

Case 1:

Server: Windows Machine with IP 200.168.1.2

Client: Linux Machine with IP 200.168.1.3

On Windows Machine:

1.Give sharing to the folder or drive in Windows Machine and enter “Share name” as ‘share’

On Linux Machine:

2.In Linux Machine, login as root user.

3.copy /etc/samba/smb.conf file to /etc/samba/smb.conf.bak – to keep the original file secure

# cp /etc/samba/smb.conf /etc/samba/smb.conf.bak

4.Edit /etc/samba/smb.conf file and make the following changes

# vi /etc/samba/smb.conf

Un-comment entry like

# hosts allow = [Windows IP address]

hosts allow = 200.168.1.2
At the end of the file, un-comment following lines and make the entries

# This is the Share Name which you had given in Windows Machine. This is Mandatory

path = /share

# Remaining things are optional, set them as per your requirement

comment = My Linux share

public = no

writable = yes

printable = no

create mask = 0765

…………

5.Save and exit from /etc/samba/smb.conf

6.Create directory on Linux Machine to view windows shared folder

# mkdir /WinShare

7.Start the samba (smb) service if it not started or restart the service once

# service smb start

# service smb restart

8.Mount Windows share folder on Linux Machine using

# mount -t smbfs //200.168.1.2/share /WinShare

It will ask the Windows Machine user password, key-in the password

The Windows Machine share directory will mounted in the Linux Machine

9.Access Windows’ share on Linux using

$ cd /WinShare
The Windows share directory which you had mounted that will be not permanent after the restart. If you want to make it as a permanent, make an entry in /etc/fstab file

1.Login as root user and edit /etc/fstab file

# vi /etc/fstab

2.Make the entry

//200.168.1.2/share /WinShare smbfs defaults 1 2

3.Save and quit the /etc/fstab file

Case 2:

Server: Linux Machine with IP 200.168.1.3

Client: Windows Machine with IP 200.168.1.2

On Linux Machine:

1.Login as root user and create a user for samba service

# useradd -g users -d /u01/sam -s /bin/false sam

# password sam

sam123

The user’s home directory i.e., ‘ /u01/sam‘ will be the sharable directory in the Windows Machine.

If you want to share different directories, change the home directory for the user as

# usermod -d /u02/otherdirectory sam

2.Add this user for samba service password file

# smbpasswd -a sam

sam987

This is not the OS user password (sam123). This password is for samba service (sam987). The user will get added to the /etc/samba/smbusers file and the password will get updated to the /etc/samba/smbpasswd file.

Initially both files wont be there in the directory, after executing the commands the files will be created.

3.Edit /etc/samba/smb.conf file

# vi /etc/samba/smb.conf

At the end of the file, un-comment following lines and make the entries

# This is the share directory that will get shared from Linux to Windows Machine. This is Mandatory

path = /u01/sam

# Remaining things are optional, set them as per your requirement

comment = My Linux share

public = no

writable = yes

printable = no

create mask = 0765

…………..

4.Start the samba service

# service smb start

# service smb restart

On Windows Machine:

5.Open the Windows Explorer

6.Select Tools > Map Network Drive

7.Choose the Drive alphabet

8.In the Folder, enter

\\200.168.1.3\sam

Here sam is a user name on the Linux Machine, the one which we had made as the samba user on Linux Machine

9.Check ‘Reconnect at login’ (Optional)

10.Click on Finish

11.After trying to connect, Windows will pop-up connecting window. Enter

Username: sam

Password: sam987

Here we need to enter the samba service password i.e., sam987

12.Check ‘Remember my password’ (Optional)

13.Now the Drive of the Linux Machine “/u01/sam” will be display as the drive in the Windows Explorer

Posted in Linux | 1 Comment

Manually Corrupting Oracle Data Block

Before we begin:

Changing DB ID for the Oracle Database

Interval Partition

System Partition

Virtual Column-Based Partition

Composite Partitions

Range-Range: Single-Column Partition and Single-Column Subpartition

Range-Range: Multi-Column Partition and Single-Column Subpartition

Range-Range: Single-Column Partition and Multi-Column Subpartition

Range-Range: Multi-Column Partition and Multi-Column Subpartition

Composite: List-List Partition

Composite: List-Hash Partition

Composite: List-Range Partition

Composite: Interval-Range: Range Single-Column Subpartition

Composite: Interval-Range: Range Multi-Column Subpartition

Composite: Interval-Hash Partition

Composite: Interval-List Partition

Upgrading from Oracle 10.2.0.1 to 11.1.0.6

What is Oracle Database Upgrade?

Why the upgrading is needed?

Types of upgrading

Compatibility Matrix

Choosing an upgrade method

Before upgrading

Upgrading from Oracle 10.2.0.1 to 11.1.0.6

Prerequisites

Manually Upgrading

1. Running the Pre-Upgrade tool

Upgrading the timezone file version from 2 to 4

To Solve Stale Optimizer Statistics Warning

NOTE:

Re-run the pre-upgrade script again

2. Backup the Oracle 10g database

3. Create parameter file for Oracle 11g

4. Upgrade the database

5. Post Upgrading Steps

This will completes the manually upgrading the Oracle database from 10.2.0.1 to 11.1.0.6

Using the Database Upgrade Assistant (DBUA)

Upgrading the timezone file version from 2 to 4

To Solve Stale Optimizer Statistics Warning

NOTE:

After Upgrading

NOTE:

Migrating ASM Database to Non-ASM

Converting Non-ASM Database to ASM Database

What is Samba?

Case 1:

On Windows Machine:

On Linux Machine:

Case 2:

On Linux Machine:

On Windows Machine:

Archives

Meta