About Me

My photo
Bangalore, India
I am an Oracle Certified Professional working in SAP Labs. Everything written on my blog has been tested on my local environment, Please test before implementing or running in production. You can contact me at amit.rath0708@gmail.com.

Saturday, August 15, 2015

Cross Platform Transportable Tablespace using RMAN in Oracle Database

Cross Platform Transportable Tablespace with Different Endianness

Reference data for this Post:-
1. Tablespace to be moved : TEST
2. Source Operating system :- Solaris[tm] OE (64-bit) 
3. Target Operating system :- Linux x86 64-bit
4. Datafiles residing in :- ASM
5. Source Database version :- 11.2.0.2
6. Target Database Version :- 11.2.0.4

Steps to perform Cross platform Transportable Tablespace on Source Database:-

1. Pre Check the data for the tablespace to be moved(optional)

SQL> select table_name from user_tables;

TABLE_NAME         OWNER
--------------------        ----------------
AMIT_1 AMIT
AMIT_2 AMIT
AMIT_3 AMIT

SQL> select count(1) from AMIT_2;

  COUNT(1)
----------
  11496960
  
SQL> select distinct tablespace_name from dba_segments where owner='AMIT';

TABLESPACE_NAME
------------------------------
TEST

2. Check whether both Platform have different Endianness 

Check the target operating system detail from below query:-

Source :-

select d.name,d.PLATFORM_NAME,tp.ENDIAN_FORMAT  FROM V$TRANSPORTABLE_PLATFORM tp, V$DATABASE d  WHERE tp.PLATFORM_NAME = d.PLATFORM_NAME;

NAME      PLATFORM_NAME                                                                                         ENDIAN_FORMAT
--------- ----------------------------------------------------------------------------------------------------- --------------
AMIT     Solaris[tm] OE (64-bit)                                                                                      Big

Target :-

select d.name,d.PLATFORM_NAME,tp.ENDIAN_FORMAT  FROM V$TRANSPORTABLE_PLATFORM tp, V$DATABASE d  WHERE tp.PLATFORM_NAME = d.PLATFORM_NAME;

NAME      PLATFORM_NAME                                                                                         ENDIAN_FORMAT
--------- ----------------------------------------------------------------------------------------------------- --------------
TEST     Linux x86 64-bit                                                                                           Little

If yes , then we have to convert the endianness of datafile as per target database.

3. Change the Tablespace to Read Only mode 

SQL> alter tablespace test read only;

Tablespace altered.

4. Check whether tablespace can be transported without any errors

SQL> EXEC SYS.DBMS_TTS.TRANSPORT_SET_CHECK(ts_list => 'TEST', incl_constraints => TRUE);

PL/SQL procedure successfully completed.

SQL> SELECT * FROM transport_set_violations;

no rows selected

5. Restrictions for Cross platform transportable tablespace

a. Both Source and target should use the same Characterset and National Character set
b. Tablespace has to be Self Contained means Objects with underlying objects (such as materialized views) or contained objects (such as partitioned tables) are not transportable unless all of the underlying or contained objects are in the tablespace set
c. Compatibility must be greater than 10.0 for both

6. Export the metadata of tablespace using datapump

$ expdp directory=DP_DUMP dumpfile=expdp_transport1.dmp logfile=expdp.log transport_tablespaces=test exclude=xmlschema

Export: Release 11.2.0.2.0 - Production on Sat Jul 11 12:06:42 2015

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Username: /as sysdba

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
Starting "SYS"."SYS_EXPORT_TRANSPORTABLE_01":  /******** AS SYSDBA directory=DP_DUMP dumpfile=expdp_transport1.dmp logfile=expdp.log transport_tablespaces=test exclude=xmlschema
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Master table "SYS"."SYS_EXPORT_TRANSPORTABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TRANSPORTABLE_01 is:
  /u00/oracle/admin/AMIT/dpdump/expdp_transport1.dmp
******************************************************************************
Datafiles required for transportable tablespace TEST:
  +DATADG/AMIT/datafile/test.349.884746017
Job "SYS"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at 12:06:55

7. Convert the datafile endianness as per the target operating system .

As we see in Step 2 that both Source and target have different endianness, we have convert the endianness of datafile as per target

Start the conversion :-

$ rman target /

Recovery Manager: Release 11.2.0.2.0 - Production on Sat Jul 11 12:09:47 2015

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: AMIT (DBID=387143729)

RMAN> convert tablespace "TEST" to platform 'Linux x86 64-bit' FORMAT='/ora_backup/dpdump/AMIT/%U';

Starting conversion at source at 2015-07-11 12:10:08
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=112 device type=DISK
channel ORA_DISK_1: starting datafile conversion
input datafile file number=00045 name=+DATADG/AMIT/datafile/test.349.884746017
converted datafile=/ora_backup/dpdump/AMIT/data_D-AMIT_I-387143729_TS-TEST_FNO-45_02qbpap1
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:55
Finished conversion at source at 2015-07-11 12:11:04

8. Once conversion completed, SCP the files to target platform( Converted datafile and Dumpfile for metadata of tablespace)

$ scp data_D-AMIT_I-387143729_TS-TEST_FNO-45_02qbpap1 server1:/ora_backup/test/restore_test/amit/backup
$ scp expdp_transport1.dmp server1:/ora_backup/test/restore_test/amit/backup

9. Change the tablespace to Read Write mode

SQL> alter tablespace test read write;

Tablespace altered.

Steps to perform Cross platform Transportable Tablespace on Target Database:-

1. Check the Version of target database(optional)

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE    11.2.0.4.0      Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 – Production

2. Convert the datafile as per the target Database file details 

$ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Sat Jul 11 14:20:12 2015

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: TEST1 (DBID=1261817935)

RMAN> convert datafile '/ora_backup/test/restore_test/amit/backup/data_D-AMIT_I-387143729_TS-TEST_FNO-45_02qbpap1' db_file_name_convert='/ora_backup/test/restore_test/amit/backup','+DATA1_test/test1/datafile/';

Starting conversion at target at 2015-07-11 14:20:55
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=235 device type=DISK
channel ORA_DISK_1: starting datafile conversion
input file name=/ora_backup/test/restore_test/amit/backup/data_D-AMIT_I-387143729_TS-TEST_FNO-45_02qbpap1
converted datafile=+DATA1_test/test1/datafile/data_d-AMIT_i-387143729_ts-test_fno-45_02qbpap1
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:15
Finished conversion at target at 2015-07-11 14:21:11

3. Create the appropriate Users and roles as they were present in Source database

Create user Amit identified by *****;
Grant login, schema to amit;

If this tablespace is to be associated to different user then remap_schema has to be used while importing the metadata.

4. Import the metadata of the tablespace in target Database 

$ impdp directory=datapump dumpfile=expdp_transport1.dmp logfile=impdp1.log transport_datafiles=+DATA1_test/test1/datafile/TEST.348.884787657

Import: Release 11.2.0.4.0 - Production on Sat Jul 11 14:23:53 2015

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Username: /as sysdba

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
Master table "SYS"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded
Source time zone is -05:00 and target time zone is +00:00.
Starting "SYS"."SYS_IMPORT_TRANSPORTABLE_01":  /******** AS SYSDBA directory=datapump dumpfile=expdp_transport1.dmp logfile=impdp1.log transport_datafiles=+DATA1_test/test1/datafile/TEST.348.884787657
Processing object type TRANSPE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORORTABLT/TABLE
Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Job "SYS"."SYS_IMPORT_TRANSPORTABLE_01" successfully completed at Sat Jul 11 14:23:59 2015 elapsed 0 00:00:03

5. Check the new datafile details in target database 

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
+DATA1_test/test1/datafile/system.365.884775499
+DATA1_test/test1/datafile/sysaux.326.884775501
+DATA1_test/test1/datafile/undotbs1.380.884775501
+DATA1_test/test1/datafile/users.381.884775501
+DATA1_test/test1/datafile/test.348.884787657

SQL> select tablespace_name from dba_tablespaces;

TABLESPACE_NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
TEMP
USERS
TEST

6 rows selected.

6. Make the tablespace read write in Target database 

SQL> alter tablespace test read write;

Tablespace altered.

7. Check the data associated with tablespace in target and compare it with source for verification(Optional)

SQL> conn amit/Passw0rd
Connected.
SQL> select table_name from user_tables;

TABLE_NAME
------------------------------
AMIT_2
AMIT_3
AMIT_1

SQL> select count(1) from AMIT_2;

  COUNT(1)
----------
  11496960

Cross Platform Transportable Tablespace with different Endianness completed.

I hope this article helped you.

Thanks
Amit Rath

Monday, July 27, 2015

How to restore a 10.2 Rman backup on a 11.2 Oracle Database host

Yesterday I got a requirement to restore one of my 10g database Rman backup , now all our 10g database servers were decommissed. So I have to restore it on a 11g host . PFB steps to restore and upgrade a 10.2 version database to 11.2 version :-

1. Copy the 10g Backup to 11G server

2. Create a Pfile for to start the instance

*.audit_file_dest='/opt/oracle/admin/amit/adump'
*.audit_trail='DB_EXTENDED'
*.compatible='11.2.0.0.0'    #######s set compatible for 11.2 version######
*.control_files='+DATA_DG/amit/controlfile/control01.ctl','+DATA_DG/amit/controlfile/control02.ctl'#Set by RMAN
*.db_block_size=8192
*.db_create_file_dest='+DATA_DG'
*.db_create_online_log_dest_1='+DATA_DG'
*.db_domain='domain.com'
*.db_name='amit'#Reset to original value by RMAN
*.db_recovery_file_dest='+DATA_DG'
*.db_recovery_file_dest_size=7000M
*.diagnostic_dest='/opt/oracle'
*.open_cursors=300
*.pga_aggregate_target=2G
*.processes=550
*.sga_target=5G
*.undo_tablespace='UNDO'  ##### as the backup is of 10g database , we have to keep it UNDO#####

3. Start the instance with this pfile

export ORACLE_SID=amit
sqlplus "/ as sysdba"
startup nomount pfile='/ora_backup/test/amit/initamit.ora'
create spfile from pfile='/ora_backup/test/amit/initamit.ora';
shu immediate
startup nomount

SQL> ORACLE instance started.

Total System Global Area 5344731136 bytes
Fixed Size                  2262656 bytes
Variable Size            1040189824 bytes
Database Buffers         4278190080 bytes
Redo Buffers               24088576 bytes

4. Login to RMAN prompt and restore the controlfile

export ORACLE_SID=amit
rman log=/ora_backup/test/amit/restore.log << EOF
connect target /
restore controlfile from '/ora_backup/test/amit/ctl_c-1234324679-20140701-00';

Starting restore at 2015-07-24 06:17:21
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=505 device type=DISK

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
output file name=+DATA_DG/amit/controlfile/control01.ctl
output file name=+DATA_DG/amit/controlfile/control02.ctl
Finished restore at 2015-07-24 06:17:25

5. Mount the database with this control file

alter database mount;

database mounted
released channel: ORA_DISK_1

6. Catalog the 10g backup pieces

catalog start with '/ora_backup/test/amit/';
Starting implicit crosscheck backup at 2015-07-24 06:17:32
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=505 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=523 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=559 device type=DISK
allocated channel: ORA_DISK_4
.
.
.

File Name: /ora_backup/test/amit/hot_amit_30309_1_20140701
File Name: /ora_backup/test/amit/hot_amit_30310_1_20140701
File Name: /ora_backup/test/amit/hot_amit_30311_1_20140701
File Name: /ora_backup/test/amit/hot_amit_30312_1_20140701

7. Restore and recover the database using sequence number+1

run
{
allocate channel c1 device type disk;
allocate channel c2 device type disk;
allocate channel c3 device type disk;
allocate channel c4 device type disk;
allocate channel c5 device type disk;
SET NEWNAME FOR DATAFILE 1 TO '+DATA_DG';
SET NEWNAME FOR DATAFILE 2 TO '+DATA_DG';
SET NEWNAME FOR DATAFILE 3 TO '+DATA_DG';
SET NEWNAME FOR DATAFILE 4 TO '+DATA_DG';
SET NEWNAME FOR DATAFILE 5 TO '+DATA_DG';
SET NEWNAME FOR DATAFILE 6 TO '+DATA_DG';
SET NEWNAME FOR DATAFILE 7 TO '+DATA_DG';
SET NEWNAME FOR DATAFILE 8 TO '+DATA_DG';
SET NEWNAME FOR DATAFILE 9 TO '+DATA_DG';
SET NEWNAME FOR DATAFILE 10 TO '+DATA_DG';
SET NEWNAME FOR DATAFILE 11 TO '+DATA_DG';
SET NEWNAME FOR DATAFILE 12 TO '+DATA_DG';
SET NEWNAME FOR DATAFILE 13 TO '+DATA_DG';
SET NEWNAME FOR DATAFILE 14 TO '+DATA_DG';
SET NEWNAME FOR DATAFILE 15 TO '+DATA_DG';
SET NEWNAME FOR DATAFILE 16 TO '+DATA_DG';
SET NEWNAME FOR DATAFILE 17 TO '+DATA_DG';
SET NEWNAME FOR DATAFILE 18 TO '+DATA_DG';
SET NEWNAME FOR DATAFILE 19 TO '+DATA_DG';
SET NEWNAME FOR DATAFILE 20 TO '+DATA_DG';
SET NEWNAME FOR DATAFILE 21 TO '+DATA_DG';
SET NEWNAME FOR DATAFILE 22 TO '+DATA_DG';
SET NEWNAME FOR DATAFILE 23 TO '+DATA_DG';
SET NEWNAME FOR DATAFILE 24 TO '+DATA_DG';
SET NEWNAME FOR DATAFILE 25 TO '+DATA_DG';
SET NEWNAME FOR DATAFILE 26 TO '+DATA_DG';
SET NEWNAME FOR DATAFILE 27 TO '+DATA_DG';
SET NEWNAME FOR DATAFILE 28 TO '+DATA_DG';
SET NEWNAME FOR DATAFILE 29 TO '+DATA_DG';
SET NEWNAME FOR DATAFILE 30 TO '+DATA_DG';
SET NEWNAME FOR DATAFILE 31 TO '+DATA_DG';
SET NEWNAME FOR DATAFILE 32 TO '+DATA_DG';
SET NEWNAME FOR DATAFILE 33 TO '+DATA_DG';
SET NEWNAME FOR DATAFILE 34 TO '+DATA_DG';
SET NEWNAME FOR DATAFILE 35 TO '+DATA_DG';
SET NEWNAME FOR DATAFILE 36 TO '+DATA_DG';
SET NEWNAME FOR DATAFILE 37 TO '+DATA_DG';
SET NEWNAME FOR DATAFILE 38 TO '+DATA_DG';
SET NEWNAME FOR DATAFILE 39 TO '+DATA_DG';
restore database until sequence 10123;
switch datafile all;
recover database until sequence 10123;
}

allocated channel: c1
channel c1: SID=505 device type=DISK

allocated channel: c2
channel c2: SID=523 device type=DISK

allocated channel: c3
channel c3: SID=559 device type=DISK

allocated channel: c4
channel c4: SID=577 device type=DISK

allocated channel: c5
channel c5: SID=613 device type=DISK

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME
.
.
.
Starting restore at 2015-07-24 06:18:01

channel c1: starting datafile backup set restore
channel c1: specifying datafile(s) to restore from backup set
channel c1: restoring datafile 00005 to +DATA_DG
channel c1: restoring datafile 00006 to +DATA_DG
channel c1: restoring datafile 00021 to +DATA_DG
channel c1: restoring datafile 00038 to +DATA_DG
channel c1: reading from backup piece /ora_backup/test/amit/hot_amit_30307_1_20140701
channel c2: starting datafile backup set restore
.
.
.Starting recover at 2015-07-24 07:08:30

starting media recovery

channel c1: starting archived log restore to default destination
channel c1: restoring archived log
archived log thread=1 sequence=10123
.
.
.
media recovery complete, elapsed time: 00:00:08
Finished recover at 2015-07-24 07:11:21
released channel: c1
released channel: c2
released channel: c3
released channel: c4
released channel: c5

8. Rename the online log files according to the target

ALTER DATABASE DROP LOGFILE GROUP 1;
ALTER DATABASE DROP LOGFILE GROUP 2;
ALTER DATABASE DROP LOGFILE GROUP 3;
alter database rename file '/u02/oracle/amit/redo.g04.m01.log' to '+DATA_DG/amit/ONLINELOG/group41.log';
alter database rename file '/u03/oracle/amit/redo.g04.m02.log' to '+DATA_DG/amit/ONLINELOG/group42.log';
alter database rename file '/u02/oracle/amit/redo.g05.m01.log' to '+DATA_DG/amit/ONLINELOG/group51.log';
alter database rename file '/u03/oracle/amit/redo.g05.m02.log' to '+DATA_DG/amit/ONLINELOG/group52.log';

9 . open the database with upgrade option

ALTER DATABASE OPEN RESETLOGS UPGRADE;

10. Create Undo tablespace for 11g database and change the default to the new one

create undo tablespace UNDOTBS1 datafile '+DATA_DG' size 1g;
alter system set undo_tablespace=UNDOTBS1;

11. Create New temporary tablespace and change the default one

create temporary tablespace TEMP1 tempfile '+DATA_DG' size 1g;
alter database default temporary tablespace temp1;
drop tablespace temp including contents and datafiles;

12. Now run the Catupgrd.sql script, it will error out as the preupgrade tasks were not applied on 10g backup

shu immediate
startup upgrade

SQL> @?/rdbms/admin/catupgrd.sql
DOC>#######################################################################
DOC>#######################################################################
DOC>
DOC>   The first time this script is run, there should be no error messages
DOC>   generated; all normal upgrade error messages are suppressed.
DOC>
DOC>   If this script is being re-run after correcting some problem, then
DOC>   expect the following error which is not automatically suppressed:
DOC>
DOC>   ORA-00001: unique constraint () violated
DOC>              possibly in conjunction with
DOC>   ORA-06512: at "", line NN
DOC>
DOC>   These errors will automatically be suppressed by the Database Upgrade
DOC>   Assistant (DBUA) when it re-runs an upgrade.
DOC>
DOC>#######################################################################
DOC>#######################################################################
DOC>#
DOC>######################################################################
DOC>######################################################################
DOC>    The following statement will cause an "ORA-01722: invalid number"
DOC>    error if the user running this script is not SYS.  Disconnect
DOC>    and reconnect with AS SYSDBA.
DOC>######################################################################
DOC>######################################################################
DOC>#

no rows selected

DOC>######################################################################
DOC>######################################################################
DOC>    The following statement will cause an "ORA-01722: invalid number"
DOC>    error if the database server version is not correct for this script.
DOC>    Perform "ALTER SYSTEM CHECKPOINT" prior to "SHUTDOWN ABORT", and use
DOC>    a different script or a different server.
DOC>######################################################################
DOC>######################################################################
DOC>#

no rows selected

DOC>#######################################################################
DOC>#######################################################################
DOC>   The following statement will cause an "ORA-01722: invalid number"
DOC>   error if the database has not been opened for UPGRADE.
DOC>
DOC>   Perform "ALTER SYSTEM CHECKPOINT" prior to "SHUTDOWN ABORT",  and
DOC>   restart using UPGRADE.
DOC>#######################################################################
DOC>#######################################################################
DOC>#

no rows selected

DOC>#######################################################################
DOC>#######################################################################
DOC>     The following statement will cause an "ORA-01722: invalid number"
DOC>     error if the Oracle Database Vault option is TRUE.  Upgrades cannot
DOC>     be run with the Oracle Database Vault option set to TRUE since
DOC>     AS SYSDBA connections are restricted.
DOC>
DOC>     Perform "ALTER SYSTEM CHECKPOINT" prior to "SHUTDOWN ABORT", relink
DOC>     the server without the Database Vault option, and restart the server
DOC>     using UPGRADE mode.
DOC>
DOC>
DOC>#######################################################################
DOC>#######################################################################
DOC>#

no rows selected

DOC>#######################################################################
DOC>#######################################################################
DOC>   The following statement will cause an "ORA-01722: invalid number"
DOC>   error if Database Vault is installed in the database but the Oracle
DOC>   Label Security option is FALSE.  To successfully upgrade Oracle
DOC>   Database Vault, the Oracle Label Security option must be TRUE.
DOC>
DOC>   Perform "ALTER SYSTEM CHECKPOINT" prior to "SHUTDOWN ABORT",
DOC>   relink the server with the OLS option (but without the Oracle Database
DOC>   Vault option) and restart the server using UPGRADE.
DOC>#######################################################################
DOC>#######################################################################
DOC>#

no rows selected

DOC>#######################################################################
DOC>#######################################################################
DOC>   The following statement will cause an "ORA-01722: invalid number"
DOC>   error if bootstrap migration is in progress and logminer clients
DOC>   require utlmmig.sql to be run next to support this redo stream.
DOC>
DOC>   Run utlmmig.sql
DOC>   then (if needed)
DOC>   restart the database using UPGRADE and
DOC>   rerun the upgrade script.
DOC>#######################################################################
DOC>#######################################################################
DOC>#

no rows selected

DOC>#######################################################################
DOC>#######################################################################
DOC>   The following error is generated if the pre-upgrade tool has not been
DOC>   run in the old ORACLE_HOME home prior to upgrading a pre-11.2 database:
DOC>
DOC>   SELECT TO_NUMBER('MUST_HAVE_RUN_PRE-UPGRADE_TOOL_FOR_TIMEZONE')
DOC>                       *
DOC>      ERROR at line 1:
DOC>      ORA-01722: invalid number
DOC>
DOC>     o Action:
DOC>       Shutdown database ("alter system checkpoint" and then "shutdown abort").
DOC>       Revert to the original oracle home and start the database.
DOC>       Run pre-upgrade tool against the database.
DOC>       Review and take appropriate actions based on the pre-upgrade
DOC>       output before opening the datatabase in the new software version.
DOC>
DOC>#######################################################################
DOC>#######################################################################
DOC>#

Session altered.


Table created.


Table altered.

SELECT TO_NUMBER('MUST_HAVE_RUN_PRE-UPGRADE_TOOL_FOR_TIMEZONE')
                 *
ERROR at line 1:
ORA-01722: invalid number

To remove this error , Please see below link
http://amit7oracledba.blogspot.com/2015/06/ora-01722-during-upgrade-102-to-112_30.html

we have to disable the data vault option and update the timezone detail of current Database server to this Restored10g database.

13. Once both Errors removed , we will again run the Catupgrd.sql to upgrade the database , PFB output :-

...
.
.

200        -- Output final actions time
201        IF display_xml THEN
202           DBMS_OUTPUT.PUT_LINE (
203                           '" upgradeTime="' || substr(time_result,5,8) ||
204                           '">');
205        ELSE
206           DBMS_OUTPUT.PUT_LINE('.' ||
207                                LPAD(' ',46) || ' ' ||
208                                LPAD(' ',15) ||
209                                LPAD(substr(time_result,5,8),10));
210        END IF;
211     END IF; -- actions_end_time is not null
212
213     IF up_end_time IS NOT NULL THEN
214        elapsed_time := up_end_time - up_start_time;
215        time_result := to_char(elapsed_time);
216        IF display_xml THEN
217           DBMS_OUTPUT.PUT_LINE('');
219        ELSE
220           DBMS_OUTPUT.PUT_LINE('Total Upgrade Time: ' ||
221                    substr(time_result, 5,8));
222        END IF;
223     ELSE
224        IF display_xml THEN
225              DBMS_OUTPUT.PUT_LINE('');
226        ELSE
227           DBMS_OUTPUT.PUT_LINE('Upgrade Incomplete');
228        END IF;
229     END IF;
230        IF display_xml THEN
231         DBMS_OUTPUT.PUT_LINE('
');232           DBMS_OUTPUT.PUT_LINE('
');233        END IF;
234  END;
235  /
.
Oracle Database 11.2 Post-Upgrade Status Tool           07-27-2015 02:26:47
.
Component                               Current      Version     Elapsed Time
Name                                    Status       Number      HH:MM:SS
.
Oracle Server
.                                         VALID      11.2.0.4.0  00:24:14
Final Actions
.                                                                00:00:28
Total Upgrade Time: 00:24:47

PL/SQL procedure successfully completed.

SQL>
SQL> SET SERVEROUTPUT OFF
SQL> SET VERIFY ON
SQL> commit;

Commit complete.

SQL>
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

We can see upgrade completed successfully in approx 24 mins and version is now 11.2.0.4.0

14. After Upgrade completed successfully, Oracle Instance is down, Start up the instance and check for any invalid objects :-

SQL> ORACLE instance started.

Total System Global Area 5344731136 bytes
Fixed Size                  2262656 bytes
Variable Size            1040189824 bytes
Database Buffers         4278190080 bytes
Redo Buffers               24088576 bytes
Database mounted.
Database opened.
SQL>
TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_BGN  2015-07-27 02:27:12

DOC>   The following PL/SQL block invokes UTL_RECOMP to recompile invalid
DOC>   objects in the database. Recompilation time is proportional to the
DOC>   number of invalid objects in the database, so this command may take
DOC>   a long time to execute on a database with a large number of invalid
DOC>   objects.
DOC>
DOC>   Use the following queries to track recompilation progress:
DOC>
DOC>   1. Query returning the number of invalid objects remaining. This
DOC>      number should decrease with time.
DOC>         SELECT COUNT(*) FROM obj$ WHERE status IN (4, 5, 6);
DOC>
DOC>   2. Query returning the number of objects compiled so far. This number
DOC>      should increase with time.
DOC>         SELECT COUNT(*) FROM UTL_RECOMP_COMPILED;
DOC>
DOC>   This script automatically chooses serial or parallel recompilation
DOC>   based on the number of CPUs available (parameter cpu_count) multiplied
DOC>   by the number of threads per CPU (parameter parallel_threads_per_cpu).
DOC>   On RAC, this number is added across all RAC nodes.
DOC>
DOC>   UTL_RECOMP uses DBMS_SCHEDULER to create jobs for parallel
DOC>   recompilation. Jobs are created without instance affinity so that they
DOC>   can migrate across RAC nodes. Use the following queries to verify
DOC>   whether UTL_RECOMP jobs are being created and run correctly:
DOC>
DOC>   1. Query showing jobs created by UTL_RECOMP
DOC>         SELECT job_name FROM dba_scheduler_jobs
DOC>            WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>
DOC>   2. Query showing UTL_RECOMP jobs that are running
DOC>         SELECT job_name FROM dba_scheduler_running_jobs
DOC>            WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>#

PL/SQL procedure successfully completed.


TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_END  2015-07-27 02:27:56

DOC> The following query reports the number of objects that have compiled
DOC> with errors.
DOC>
DOC> If the number is higher than expected, please examine the error
DOC> messages reported with each object (using SHOW ERRORS) to see if they
DOC> point to system misconfiguration or resource constraints that must be
DOC> fixed before attempting to recompile these objects.
DOC>#

OBJECTS WITH ERRORS
-------------------
                  0

DOC> The following query reports the number of errors caught during
DOC> recompilation. If this number is non-zero, please query the error
DOC> messages in the table UTL_RECOMP_ERRORS to see if any of these errors
DOC> are due to misconfiguration or resource constraints that must be
DOC> fixed before objects can compile successfully.
DOC>#

ERRORS DURING RECOMPILATION
---------------------------
                          0


Function created.


PL/SQL procedure successfully completed.


Function dropped.


PL/SQL procedure successfully completed.

SQL> Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE    11.2.0.4.0      Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production

SQL> select name,open_mode from v$database;

NAME      OPEN_MODE
--------- --------------------
AMIT      READ WRITE

Restore of a 10G backup to a 11g host completed successfully.

I hope this article helped you.

Thanks
Amit Rath

Friday, July 24, 2015

Flashback Technology : Flashback to a restore point having standby database also enabled

Yesterday we were in a scenario where we have to do some changes in our production database which if needed can be rollbacked.

We planned to go with a restore point option so that if change not needed by Development team, we can move back to before change time.

This was a big database and it has standby also enabled with it. So if we do a flashback on primary then incarnation of primary will differ from standby and recovery will be stopped.

Below are the steps which we used to handle both primary and standby in case of a Flashback database in Primary :-

1. Backup Primary database

2. Note the Current SCN of primary :-

SQL> select current_scn from v$database;

CURRENT_SCN
-----------
   15883324

3. Create a restore point 

SQL> CREATE RESTORE POINT before_upgrade GUARANTEE FLASHBACK DATABASE;

Restore point created.

4. Do your changes in database 

Now you want to do the flashback in your primary Database as the changes which you did, development team does not require those. PFB steps :-

SQL> startup force mount
ORACLE instance started.

Total System Global Area 4275781632 bytes
Fixed Size                  2260088 bytes
Variable Size            1124074376 bytes
Database Buffers         3137339392 bytes
Redo Buffers               12107776 bytes
Database mounted.
SQL> flashback database to restore point before_upgrade;

Flashback complete.

SQL> alter database open RESETLOGS;

Database altered.

Now when you check your standby database , MRP is stopped and it's showing that incarnation is different from primary database.

MRP0: Incarnation has changed! Retry recovery...
Errors in file /opt/oracle/diag/rdbms/amit/amit1/trace/amit_pr00_17847.trc:
ORA-19906: recovery target incarnation changed during recovery
Managed Standby Recovery not using Real Time Apply
Recovery interrupted!

Move your standby database to that SCN which was before doing a flashback.

Standby Database :-

SQL> flashback database to scn 15883324;

Flashback complete.

Now start the MRP and check the alert log, it will show MRP started successfully

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE  THROUGH ALL SWITCHOVER DISCONNECT  USING CURRENT LOGFILE
Attempt to start background Managed Standby Recovery process (dr3fia)
Fri Jul 24 06:10:35 2015
MRP0 started with pid=49, OS id=14805
MRP0: Background Managed Standby Recovery process started (dr3fia)
 started logmerger process
Fri Jul 24 06:10:40 2015
Managed Standby Recovery starting Real Time Apply
Parallel Media Recovery started with 12 slaves
Media Recovery start incarnation depth : 1, target inc# : 6, irscn : 15883360
Waiting for all non-current ORLs to be archived...
All non-current ORLs have been archived.
Media Recovery Log +Amit_DG/amit/archivelog/2015_07_24/thread_1_seq_7.704.885880789
Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE  THROUGH ALL SWITCHOVER DISCONNECT  USING CURRENT LOGFILE

I hope this article helped you.

Thanks
Amit Rath

ORA-16700: the standby database has diverged from the primary database

Yesterday I faced below error in my standby database :-

Problem :-

DGMGRL> show database amit_dr

Database - amit_dr

  Role:            PHYSICAL STANDBY
  Intended State:  APPLY-ON
  Transport Lag:   (unknown)
  Apply Lag:       0 seconds (computed 1 second ago)
  Apply Rate:      148.00 KByte/s
  Real Time Query: OFF
  Instance(s):
   amit_dr

  Database Error(s):
    ORA-16700: the standby database has diverged from the primary database
    ORA-16766: Redo Apply is stopped

Database Status:
ERROR

Cause :- 

This happened because when due to an incomplete recovery in primary database, it has to be opened using resetlogs option and incarnation of primary and standby becomes different due to resetlogs option.

we will get below error in our standby database alert logfile

MRP0: Incarnation has changed! Retry recovery...
Errors in file /opt/oracle/diag/rdbms/amit/amit1/trace/amit_pr00_17847.trc:
ORA-19906: recovery target incarnation changed during recovery
Managed Standby Recovery not using Real Time Apply
Recovery interrupted!
Fri Jul 24 05:58:48 2015
Archived Log entry 113 added for thread 1 sequence 1 ID 0xf0d327b6 dest 1:
Recovered data files to a consistent state at change 15883769
Fri Jul 24 05:58:48 2015
 started logmerger process
Fri Jul 24 05:58:48 2015
Managed Standby Recovery starting Real Time Apply
Warning: Recovery target destination is in a sibling branch
of the controlfile checkpoint. Recovery will only recover
changes to datafiles.
Datafile 1 (ckpscn 15883769) is orphaned on incarnation#=5
MRP0: Detected orphaned datafiles!

Solution :-

We do not have to rebuild our standby, just move standby database using flashback to a previous SCN value when the incarnation of primary and standby was same. Start the MRP manually or bounce the configuration in DG broker.

SQL> flashback database to scn 15883324;

Flashback complete.

Details from alert log on standby :-

flashback database to scn 15883324
Flashback Restore Start
Flashback Restore Complete
Flashback Media Recovery Start
Fri Jul 24 06:06:06 2015
Setting recovery target incarnation to 5
 started logmerger process
Parallel Media Recovery started with 12 slaves
Fri Jul 24 06:06:06 2015
Media Recovery Log +AMIT_DG/amit/archivelog/2015_07_24/thread_1_seq_7.704.885880789
Incomplete Recovery applied until change 15883325 time 07/24/2015 05:52:43
Flashback Media Recovery Complete
Setting recovery target incarnation to 6
Completed: flashback database to scn 15883324

Attempt to start background Managed Standby Recovery process (dr3fia)
Fri Jul 24 06:10:35 2015
MRP0 started with pid=49, OS id=14805
MRP0: Background Managed Standby Recovery process started (dr3fia)
 started logmerger process
Fri Jul 24 06:10:40 2015
Managed Standby Recovery starting Real Time Apply
Parallel Media Recovery started with 12 slaves
Media Recovery start incarnation depth : 1, target inc# : 6, irscn : 15883360
Waiting for all non-current ORLs to be archived...
All non-current ORLs have been archived.
Media Recovery Log +AMIT_DG/amit/archivelog/2015_07_24/thread_1_seq_7.704.885880789
Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE  THROUGH ALL SWITCHOVER DISCONNECT  USING CURRENT LOGFILE

DGMGRL> show configuration

Configuration - amit_dr

  Protection Mode: MaxPerformance
  Databases:
    amit  - Primary database
    amit_dr - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

I hope this article helped you.

Thanks
Amit Rath

Saturday, July 11, 2015

ORA-25191: cannot reference overflow table of an index-organized table

Yesterday I faced below issue when I was trying to grant some privileges to a table :-

grant select,insert,update,delete on AMIT.SYS_IOT_OVER_14456 TO TEST;
                                             *
ERROR at line 1:
ORA-25191: cannot reference overflow table of an index-organized table

Issue :-

This issue comes when we deal with Index Organized tables(IOT).

Solution :-

Issue this statement against the Parent Index Organized table containing the specified overflow table.

SQL> select IOT_NAME from dba_tables where owner='AMIT' and table_name='SYS_IOT_OVER_14456';

IOT_NAME
------------------------------
AMIT_TABLE

SQL> grant select,insert,update,delete on AMIT.AMIT_TABLE to TEST;

Grant succeeded.

Any operation related to Index Oraganized table has to be done by above method else it will give ORA-25191.

I hope this article helped you.

Thanks
Amit Rath

Tuesday, June 30, 2015

ORA-01722 during upgrade 10.2 to 11.2

Yesterday I was trying to upgrade my 10.2 database to 11.2 version in new host and I got below error when I ran catupgrd.sql to upgrade it:-

Issue :-

SELECT TO_NUMBER('MUST_HAVE_RUN_PRE-UPGRADE_TOOL_FOR_TIMEZONE')
                 *
ERROR at line 1:
ORA-01722: invalid number

Cause :-

I checked the Timezone value in registry$database table, I got below value

SQL> col PLATFORM_NAME for a30
SQL> set lin 400
SQL> select * from sys.registry$database;

PLATFORM_ID PLATFORM_NAME                  EDITION                        TZ_VERSION
-----------             ------------------------------                ------------------------------   ----------
         13               Linux x86 64-bit

TZ_Version coloumn was blank, so was getting ORA-01722: invalid number error

Solution :-

Update the Tz_version column of registry$database table with 11.2 version details :-

SQL> create table registry_backup as select * from registry$database;

SQL> INSERT into registry$database (platform_id, platform_name, edition, tz_version) VALUES ((select platform_id from v$database), (select platform_name from v$database),NULL,(select version from v$timezone_file));

1 row created.

SQL> select * from sys.registry$database;

PLATFORM_ID PLATFORM_NAME                  EDITION                        TZ_VERSION
-----------             ------------------------------           ------------------------------    ----------
         13              Linux x86 64-bit
         13             Linux x86 64-bit                                                                 14

SQL> delete from sys.registry$database where TZ_VERSION is NULL;

1 row deleted.

SQL> select * from registry_backup;

PLATFORM_ID PLATFORM_NAME                  EDITION                        TZ_VERSION
-----------             ------------------------------           ------------------------------    ----------
         13              Linux x86 64-bit

SQL> commit;

Commit complete.

SQL> select * from sys.registry$database;

PLATFORM_ID PLATFORM_NAME                  EDITION                        TZ_VERSION
-----------             ------------------------------           ------------------------------    ----------
         13              Linux x86 64-bit                                                                14

Timezone issue resolved with above solution.

Again I ran catupgrd.sql and now this time I got a new error :-

Issue :-

SELECT TO_NUMBER('DATA_VAULT_OPTION_ON') FROM v$option
                 *
ERROR at line 1:
ORA-01722: invalid number

Cause :-

We have to disable the data vault option before running the upgrade and enable it once donewith upgrade

Solution :-

==>chopt disable dv

Writing to /opt/oracle/product/database/11.2.0.4/install/disable_dv.log...
/usr/bin/make -f /opt/oracle/product/database/11.2.0.4/rdbms/lib/ins_rdbms.mk dv_off ORACLE_HOME=/opt/oracle/product/database/11.2.0.4
/usr/bin/make -f /opt/oracle/product/database/11.2.0.4/rdbms/lib/ins_rdbms.mk ioracle ORACLE_HOME=/opt/oracle/product/database/11.2.0.4

Now I ran the Catupgrd.sql and it ran without any issues and upgrade got successfully completed.

After the upgrade was done , I enable the data vault option by running below command :-

==>chopt enable dv

I hope this article helped you 

Regards,
Amit Rath

Friday, April 3, 2015

ERROR-Agent is blocked. Blocked reason is: Agent is out-of-sync with repository

Yesterday I faced Below error when I was trying to upload agent :-

hostname:agent:AGENT_HOME/bin> ./emctl upload
Oracle Enterprise Manager 11g Release 1 Grid Control 11.1.0.1.0
Copyright (c) 1996, 2010 Oracle Corporation.  All rights reserved.
---------------------------------------------------------------
EMD upload error: uploadXMLFiles skipped :: OMS version not checked yet. If this issue persists check trace files for ping to OMS

hostname:agent:AGENT_HOME/bin> ./emctl pingOMS
Oracle Enterprise Manager 11g Release 1 Grid Control 11.1.0.1.0
Copyright (c) 1996, 2010 Oracle Corporation.  All rights reserved.
---------------------------------------------------------------
EMD pingOMS error: Invalid ping response "ERROR-Agent is blocked. Blocked reason is: Agent is out-of-sync with repository. This mos reinstalled or recovered. Please contact an EM administrator to unblock the agent by performing an agent resync from the console. P nblock the agent"

Solution :-

Login to Problamatic agent :-

1. Stop the agent :-

AGENT_HOME/BIN> ./emctl stop agent 

2. Secure the agent which will generate a new key :-

AGENT_HOME/BIN> ./emctl secure agent 

3. Start the agent: 

AGENT_HOME/BIN> ./emctl start agent 

4. Login to the OEM Grid Console :-

Navigate to Setup >> Agents >> problematic agent home page >> Click on Agent resynchronization

Once synchronization completed.

5. Try to ping OMS

AGENT_HOME/BIN> ./emctl pingOMS

6. Once Ping Successful . Try to upload the agent

AGENT_HOME/BIN> ./emctl upload 

Agent is now sync with repository

I hope this article helped you.

Thanks
Amit Rath

Wednesday, March 18, 2015

NID-00120: Database should be mounted exclusively

Today I was trying to change DBID ad DBNAME of my RAC database ad got below error :-

==>nid target=/ DBNAME=RASTAND

DBNEWID: Release 11.2.0.4.0 - Production on Wed Mar 18 12:18:32 2015

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to database AMIT(DBID=2971225332)

NID-00120: Database should be mounted exclusively

Change of database name failed during validation - database is intact.
DBNEWID - Completed with validation errors.

Solution :-

To change DBId and Database Name of a RAC database , we need to set cluster_database parameter to false and Database should be mounted in One node.

SQL> alter system set cluster_database=FALSE scope=spfile sid='*';

System altered.

SQL> shu immediate
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area 1060585472 bytes
Fixed Size                  2260000 bytes
Variable Size             817890272 bytes
Database Buffers          230686720 bytes
Redo Buffers                9748480 bytes
Database mounted.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, Oracle Label Security,
OLAP, Data Mining and Real Application Testing options
[server][oracle][RASTAND1]
/usr/local/oracle$
==>nid target=/ DBNAME=RASTAND

DBNEWID: Release 11.2.0.4.0 - Production on Wed Mar 18 12:28:23 2015

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to database AMIT (DBID=2971225332)

Connected to server version 11.2.0

Control Files in database:
    +DATA_0220/rastand/controlfile/control01.ctl
    +FRA_0220/rastand/controlfile/control02.ctl

Change database ID and database name AMIT to RASTAND? (Y/[N]) => Y

Proceeding with operation
Changing database ID from 2971225332 to 3013823719
Changing database name from AMIT to RASTAND
    Control File +DATA_0220/rastand/controlfile/control01.ctl - modified
    Control File +FRA_0220/rastand/controlfile/control02.ctl - modified
    Datafile +DATA_0220/rastand/system01.db - dbid changed, wrote new name
    Datafile +DATA_0220/rastand/sysaux01.db - dbid changed, wrote new name
    Datafile +DATA_0220/rastand/undotbs1.db - dbid changed, wrote new name
    Datafile +DATA_0220/rastand/undotbs2.db - dbid changed, wrote new name
    Datafile +DATA_0220/rastand/users_1.db - dbid changed, wrote new name
    Datafile +DATA_0220/rastand/ts_aud01.db - dbid changed, wrote new name
    Datafile +DATA_0220/rastand/data01.db - dbid changed, wrote new name
    Datafile +DATA_0220/rastand/idx01.db - dbid changed, wrote new name
    Datafile +DATA_0220/rastand/arch01.db - dbid changed, wrote new name
    Datafile +DATA_0220/rastand/test_01.db - dbid changed, wrote new name
    Datafile +DATA_0220/rastand/amit_01.db - dbid changed, wrote new name
    Datafile +DATA_0220/rastand/amit_02.db - dbid changed, wrote new name
    Datafile +DATA_0220/rastand/amit_03.db - dbid changed, wrote new name
    Datafile +DATA_0220/rastand/temp01.db - dbid changed, wrote new name
    Control File +DATA_0220/rastand/controlfile/control01.ctl - dbid changed, wrote new name
    Control File +FRA_0220/rastand/controlfile/control02.ctl - dbid changed, wrote new name
    Instance shut down

Database name changed to RASTAND.
Modify parameter file and generate a new password file before restarting.
Database ID for database RASTAND changed to 3013823719.
All previous backups and archived redo logs for this database are unusable.
Database is not aware of previous backups and archived logs in Recovery Area.
Database has been shutdown, open database with RESETLOGS option.
Succesfully changed database name and ID.
DBNEWID - Completed succesfully.

DBID chaged successfully in RAC database

I hope this article helped you.

Regards,
Amit Rath