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.
Showing posts with label RMAN. Show all posts
Showing posts with label RMAN. Show all posts

Saturday, December 17, 2016

RMAN restore fails with RMAN-06023 but backups are available

Today while I was restoring one RMAN backup to a new host , I got very different error while restoring even though backup pieces are available and cataloged also. PFB error details :-

Error:-

creating datafile file number=1 name=/u01/oracle/amit/system01.dbf
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 07/27/2015 03:19:59
ORA-01180: can not create datafile 1
ORA-01110: data file 1: '/u01/oracle/amit/system01.dbf'

Checked backup was also available for datafile 1

RMAN> list backup of datafile 1;


List of Backup Sets

===================
.
.
   List of Backup Pieces for backup set 30299 Copy #1
    BP Key  Pc# Status      Piece Name
    ------- --- ----------- ----------
    30299   1   EXPIRED     /u00/oracle/admin/amit/backup/hot_amit_30306_1_20140701

  Backup Set Copy #2 of backup set 30299
  Device Type Elapsed Time Completion Time     Compressed Tag
  ----------- ------------ ------------------- ---------- ---
  DISK        00:52:02     2015-07-27 03:18:48 YES        HOT_DISK_amit

    List of Backup Pieces for backup set 30299 Copy #2
    BP Key  Pc# Status      Piece Name
    ------- --- ----------- ----------
    30310   1   AVAILABLE   /ora_backup/test/amit/hot_amit_30306_1_20140701

Cause :-

When we restore using a backup controlfile and run the catalog command to catalog backup pieces, Oracle does two things, First it does an implicit crosscheck for files available in the recovery area and then it does an explicit crosscheck for the location you mentioned to catalog the pieces.

While doing an implicit catalog, if there is any file available(archive log, online log, any datafile) of different incarnation from the control file, Oracle register that file details to the controlfile and changes the incarnation value for the control file as per the available file in the Recovery area.

Now when we try to restore our datafiles from backup pieces, controlfile is not able to recognize the backup pieces as they are from different incarnation.

So we get "ORA-01110: data file 1: '/u01/oracle/amit/system01.dbf'" even if we have backup pieces available.

Solution :-

1. Clean up you recovery area before doing a catalog for backup pieces. Run a Crosscheck and delete expired backup details.
2. Disable Recovery area details before running catalog command, you can do it by removing db_recovery_file_dest and db_recovery_file_dest_size parameters in parameter file.

After doing any of the above try to restore the backup , it will complete without any issues.

I hope this article helped you

Thanks
Amit Rath

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, December 19, 2014

How to create a Standby Database in RAC using RMAN Duplicate command

We can also use RMAN duplicate command to create a STANDBY RAC database from Production Standalone/RAC database. We can either use Backups of Production Database or we can create from Active Database also.

PFB steps to create a Standby Database in RAC using Active Database Duplication Method :-

Primary Database is TEST
Standby Database is STBY(RAC)

Changes to be done on Primary side :-

1. Database has to be archive log mode

SQL> SELECT log_mode FROM v$database;

LOG_MODE
------------
ARCHIVELOG

2. Change primary database to Force Logging mode :-

SQL> ALTER DATABASE FORCE LOGGING;

Database altered.

3. Initialization parameters in Primary database :-

SQL>  show parameter db_name

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_name                              string      TEST
SQL> show parameter db_unique_name

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_unique_name                       string      TEST

4. Set the DG_CONFIG setting of LOG_ARCHIVE_CONFIG parameter. for this DB_UNIQUE_NAME of  Standby database has to be different from primary.

SQL> ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(TEST,STBY)';

System altered.

5. Set suitable remote archivelog destinations.

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=STBY NOAFFIRM ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=STBY';

System altered.

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE;

System altered.

-- NOTE :- Service and DB_UNIQUE_NAME has to be of Standby database

6. Set parameters related to LOG_ARCHIVE and password file parameters :-

SQL> ALTER SYSTEM SET LOG_ARCHIVE_FORMAT='%t_%s_%r.arc' SCOPE=SPFILE;

System altered.

SQL> ALTER SYSTEM SET LOG_ARCHIVE_MAX_PROCESSES=8;

System altered.

SQL> ALTER SYSTEM SET REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE SCOPE=SPFILE;

System altered.

7. It is recommended to make sure that Primary can switch roles to become a Standby. For that we need to set some -convert parameters if there are path differences between the servers :-

SQL> ALTER SYSTEM SET FAL_SERVER=STBY;

System altered.

SQL> ALTER SYSTEM SET DB_FILE_NAME_CONVERT=' ',' ' SCOPE=SPFILE;   -- Primay server destination and Standby server destination

System altered.

SQL> ALTER SYSTEM SET LOG_FILE_NAME_CONVERT=' ',' '  SCOPE=SPFILE;  -- Primay server destination and Standby server destination

System altered.

SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;

8. As some parameters cannot be changed while database is open , we need to restart database to before they take effect :-

SQL> shu immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL> startup
ORACLE instance started.

Total System Global Area  960495616 bytes
Fixed Size                  1222816 bytes
Variable Size             247465824 bytes
Database Buffers          700448768 bytes
Redo Buffers               11358208 bytes
Database mounted.
Database opened.
SQL>

9. In both servers entries of Primary as well as Standby are needed in tnsnames.ora file and we are able to do  tnsping.

###################ORACLE_DATA_GAURD############################

TEST =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = primary_server)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = TEST)
    )
  )

STBY =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = SCAN-IP)(PORT = 9010))
    (CONNECT_DATA =
       (SERVER = DEDICATED)
       (SERVICE_NAME = STBY.wellsfargo.com)
    )
  )

STBY1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = VIP-Firstnode)(PORT = 9001))
    (CONNECT_DATA =
       (SERVER = DEDICATED)
       (SERVICE_NAME = STBY.wellsfargo.com)
       (INSTANCE_NAME = STBY1)
    )
  )
STBY2 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = VIP-SECONDnode)(PORT = 9001))
    (CONNECT_DATA =
       (SERVER = DEDICATED)
       (SERVICE_NAME = STBY.wellsfargo.com)
       (INSTANCE_NAME = STBY2)
    )
  )

TEST_DGMGRL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = primary_server)(PORT = 9001))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = TEST_DGMGRL)
    )
  )
STBY_DGMGRL =
(DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = SCAN-IP)(PORT = 9001))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = STBY_DGMGRL)
    )
  )
###################ORACLE_DATA_GAURD############################

10. Add static entries to listener.ora file

PRIMARY

  (SID_DESC =
 (GLOBAL_DBNAME = test.db.com)
 (SID_NAME = TEST)
 (ORACLE_HOME = /opt/oracle/product/database/11.2.0.4)
   )
   (SID_DESC =
   (GLOBAL_DBNAME=TEST_DGMGRL)
   (ORACLE_HOME=/opt/oracle/product/database/11.2.0.4)
   (SID_NAME=test)
   )

STANDBY

  First node :-

 (SID_DESC =
    (GLOBAL_DBNAME = STBY.wellsfargo.com)
    (SID_NAME = STBY1)
    (ORACLE_HOME = /opt/oracle/product/database/11.2.0.4)
  )
 (SID_DESC =
    (GLOBAL_DBNAME = STBY_DGMGRL.wellsfargo.com)
    (SID_NAME = STBY1)
    (ORACLE_HOME = /opt/oracle/product/database/11.2.0.4)
  )

Second Node :-

  (SID_DESC =
    (GLOBAL_DBNAME = STBY.wellsfargo.com)
    (SID_NAME = STBY2)
    (ORACLE_HOME = /opt/oracle/product/database/11.2.0.4)
  )
  (SID_DESC =
    (GLOBAL_DBNAME = STBY_DGMGRL.wellsfargo.com)
    (SID_NAME = STBY2)
    (ORACLE_HOME = /opt/oracle/product/database/11.2.0.4)
  )

11. Reload listener after adding these entries

Lsnrctl reload

12. Add Standby redo logs to primary database :-

ALTER DATABASE ADD STANDBY LOGFILE ('/STBY_redo01.log') SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE ('/STBY_redo02.log') SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE ('/STBY_redo03.log') SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE ('/STBY_redo04.log') SIZE 50M;

13. Copy the passw0rd file from Primary and copy to Standby

14. Copy the init.ora file from primary and change below parameters :-

CREATE PFILE='/initTEST.ora' from spfile;

scp orapwTEST user@STBY_server:$ORACLE_HOME/dbs/orapwSTBY1
scp initTEST.ora user@STBY_server:$ORACLE_HOME/dbs/initSTBY1.ora

Steps to be done in STANDBY Side :-

15 . Update Pfile for below parameters for Standby server

    *.audit_file_dest='/ora_backup/duplicate_test/STBY/adump'
    *.diagnostic_dest='/ora_backup/duplicate_test/STBY/diag'

16. Start the STANDBY database with above pfile

export ORACLE_SID=STBY1
sql> startup nomount pfile='$ORACLE_HOME/dbs/initSTBY1.ora'
ORACLE instance started.

Total System Global Area  960495616 bytes
Fixed Size                  1222816 bytes
Variable Size             264243040 bytes
Database Buffers          683671552 bytes
Redo Buffers               11358208 bytes
               
17. Connect to RMAN and use RMAN duplicate command to create STANDBY


rman target sys/****@TEST_DGMGRL auxiliary sys/****@STBY_DGMGRL
run
{
allocate channel ch1 device type disk;
allocate channel ch2 device type disk;
allocate auxiliary channel ch3 device type disk;
allocate auxiliary channel ch4 device type disk;
DUPLICATE TARGET DATABASE
  FOR STANDBY
  from active database
   DORECOVER
   spFILE
        SET db_unique_name='STBY' COMMENT 'Is STANDBY'
    SET LOG_ARCHIVE_DEST_2='SERVICE=TEST ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=TEST'
                set control_files='+DATA_0220/STBY/controlfile/control01.ctl','+FRA_0220/STBY/controlfile/control02.ctl'
                set db_create_file_dest='+DATA_0220/'
                set db_create_online_log_dest_1='+DATA_0220/'
                set diagnostic_dest='/ora_backup/duplicate_test/STBY/diag'
                set audit_file_dest='/ora_backup/duplicate_test/STBY/adump'
                set db_recovery_file_dest='+FRA_0220/'
                set db_file_name_convert='/ora_backup/TEST1/restore_test/TEST/DATA/','+DATA_0220/STBY/'
                set log_file_name_convert='/ora_backup/TEST1/restore_test/TEST/','+DATA_0220/STBY/'
                set FAL_SERVER='TEST'
set cluster_database='false'
set instance_number='1'
set LOG_ARCHIVE_DEST_1='location=+FRA_0220/STBY/';

}  

Above command will create the STANDBY database

18. Create pfile from spfile

        create pfile='/ora_backup/duplicate_test/STBY/initTEST.ora' from spfile;

19. Add the below parametErs for RAC database

STBY1.log_archive_format='%t_%s_%r.dbf'
STBY2.log_archive_format='%t_%s_%r.dbf'
STBY2.thread=2
STBY1.thread=1
STBY1.undo_tablespace='UNDOTBS1'
STBY2.undo_tablespace='UNDOTBS2'
STBY2.instance_number=2
STBY1.instance_number=1
STBY1.local_listener='(ADDRESS = (PROTOCOL = TCP)(HOST = first_node)(PORT = 9001))'
STBY2. local_listener='(ADDRESS = (PROTOCOL = TCP)(HOST = second_node)(PORT = 9001))'
*.cluster_database=TRUE

20. Start the instance with above pfile

==>sqlplus

SQL*Plus: Release 11.2.0.4.0 Production on Thu Dec 18 12:51:57 2014

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

Enter user-name: /as sysdba

Connected to:
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

SQL> startup force pfile='/ora_backup/duplicate_test/STBY/initTEST.ora';
ORACLE instance started.

Total System Global Area 1060585472 bytes
Fixed Size                  2260000 bytes
Variable Size             679478240 bytes
Database Buffers          369098752 bytes
Redo Buffers                9748480 bytes
Database mounted.
Database opened.
SQL>
SQL> select name,open_mode from v$database;

NAME      OPEN_MODE
--------- --------------------
TEST     READ ONLY

SQL> create spfile='+DATA_0220/STBY/spfileSTBY.ora' from pfile='/ora_backup/duplicate_test/STBY/initTEST.ora';

File created.

SQL> startup force
ORACLE instance started.

Total System Global Area 1060585472 bytes
Fixed Size                  2260000 bytes
Variable Size             679478240 bytes
Database Buffers          369098752 bytes
Redo Buffers                9748480 bytes
Database mounted.
Database opened.

SQL> show parameter cluster

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
cluster_database                     boolean     TRUE
cluster_database_instances           integer     2
cluster_interconnects                string
SQL>
SQL> show parameter listenner
SQL> show parameter listener

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
listener_networks                    string
local_listener                       string      (ADDRESS = (PROTOCOL = TCP)(HO
                                                 ST = first_node)(PORT = 9001))
remote_listener                      string      scan_ip:9010

21. Login to second instance 

Export ORACLE_SID=STBY2
==>sqlplus

SQL*Plus: Release 11.2.0.4.0 Production on Thu Dec 18 13:01:32 2014

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

Enter user-name: /as sysdba
Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 1060585472 bytes
Fixed Size                  2260000 bytes
Variable Size             679478240 bytes
Database Buffers          369098752 bytes
Redo Buffers                9748480 bytes
Database mounted.
Database opened.

SQL> select name,open_mode,database_role from v$database;

NAME      OPEN_MODE            DATABASE_ROLE
--------- -------------------- ----------------
TEST        READ ONLY WITH APPLY PHYSICAL STANDBY

SQL> select INST_ID,INSTANCE_NAME,HOST_NAME,VERSION,LOGINS from gv$instance;

   INST_ID INSTANCE_NAME    HOST_NAME                 VERSION           LOGINS
---------- ---------------- ------------------------- ----------------- ----------
         2 STBY2         wuprd02a0220              11.2.0.4.0        ALLOWED
         1 STBY1         wuprd01a0220              11.2.0.4.0        ALLOWED

SQL> show parameter listener

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
listener_networks                    string
local_listener                       string      (ADDRESS = (PROTOCOL = TCP)(HO
                                                 ST = second_node)(PORT = 9001))
remote_listener                      string      scan_ip:9010
SQL>

Check all the parameters in both instances are same or not

22. Start the recovery process

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

Database altered.

23. Add the details of Standby database in OCR

Srvctl add database –d STBY –o $ORACLE_HOME -p 
Srvctl add instance –d STBY –i STBY1-n firstnode
Srvctl add instance –d STBY –I STBY2 –n secondnode
Srvctl status database –d STBY

24. Create the broker configuration details ( for both Primary and Standby):-

SQL>  alter system set dg_broker_start=FALSE;

System altered.

SQL> alter system set dg_broker_config_file1='/opt/oracle/product/database/11.2.0.4/dbs/dr1STBY.dat';

SQL> alter system set dg_broker_config_file2='/opt/oracle/product/database/11.2.0.4/dbs/dr2STBY.dat';

System altered.

SQL> alter system set dg_broker_start=TRUE;

System altered.

Create the configuation

DGMGRL> connect /
Connected.
DGMGRL> create configuration DR as primary database is TEST connect identifier is TEST;
Configuration "dr" created with primary database "TEST"

DGMGRL> add database STBY as connect identifier is STBY  maintained as physical;
Database "STBY" added
DGMGRL> show configuration

Configuration - dr

  Protection Mode: MaxPerformance
  Databases:
    TEST   - Primary database
    STBY - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
DISABLED

DGMGRL> enable configuration
Enabled.

Check if every thing is fine by checking Standby Database details 

DGMGRL> show database STBY

Database - STBY

  Role:            PHYSICAL STANDBY
  Intended State:  APPLY-ON
  Transport Lag:   0 seconds (computed 1 second ago)
  Apply Lag:       0 seconds (computed 1 second ago)
  Apply Rate:      0 Byte/s
  Real Time Query: ON
  Instance(s):
    STBY1 (apply instance)
    STBY2

Database Status:
SUCCESS

DGMGRL> exit

Standby Creation in RAC using RMAN duplicate command completed.

Standby creation for standalone using RMAN duplicate command

I hope this article helped you

Regards,
Amit Rath

Wednesday, December 17, 2014

RMAN-06136: ORACLE error from auxiliary database: ORA-01503: CREATE CONTROLFILE failed

Yesterday I was doing a duplicate of a RAC database using RMAN Duplicate command and I got below error :-

sql statement: CREATE CONTROLFILE REUSE SET DATABASE "RASTAND" RESETLOGS ARCHIVELOG
  MAXLOGFILES    192
  MAXLOGMEMBERS      3
  MAXDATAFILES     1024
  MAXINSTANCES    32
  MAXLOGHISTORY     4672
 LOGFILE
  GROUP   1  SIZE 256 M ,
  GROUP   2  SIZE 256 M ,
  GROUP  10  SIZE 256 M
 DATAFILE
  '+DATA_0110/rastand/datafile/system.327.866524759'
 CHARACTER SET AL32UTF8

released channel: ch1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 12/17/2014 05:20:32
RMAN-05501: aborting duplication of target database
RMAN-06136: ORACLE error from auxiliary database: ORA-01503: CREATE CONTROLFILE failed
ORA-12720: operation requires database is in EXCLUSIVE mode

Problem :-

It seems while trying to create control file for a RAC database it failed by giving above error.

Reason :-

When we do a RMAN Dupliacate for a RAC Database , while creating control file for Auxiliary instance , it needs the database in Exclusive mode to create controlfile

Solution :-

In duplicate command mention Cluster_Database parameter to FALSE and it will resolve the issue. PFB :-

run
{
allocate auxiliary channel ch1 type disk;
duplicate target database to 'RASTAND'
backup location '/ora_backup/duplicate_test/RASTAND/backup/2014_12_16'
spfile
set cluster_database='FALSE'
set instance_number='1'
set db_file_name_convert='/ora_backup/TEST1/restore_test/AMIT/DATA','+DATA_0110';
}

We can also change the Cluster_database parameter in pfile also with which we start our auxiliary instance then command to do the Duplicate will be 

run
{
allocate auxiliary channel ch1 type disk;
duplicate target database to 'RASTAND'
backup location '/ora_backup/duplicate_test/RASTAND/backup/2014_12_16';
}

I hope this article helped you.

Regards,
Amit Rath

How to Create a STANDBY database using RMAN Duplicate command in Oracle

We can also use RMAN duplicate command to create a STANDBY database from Production database. We can either use Backups of Production Database or we can create from Active Database also.

PFB steps to create a standby Database using Active Database Duplication Method :-

Primary Database is TEST
Standby Database is STBY

Changes to be done on Primary side :-

1. Database has to be archive log mode

SQL> SELECT log_mode FROM v$database;

LOG_MODE
------------
ARCHIVELOG

2. Change primary database to Force Logging mode :-

SQL> ALTER DATABASE FORCE LOGGING;

Database altered.

3. Initialization parameters in Primary database :-

SQL>  show parameter db_name

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_name                              string      TEST
SQL> show parameter db_unique_name

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_unique_name                       string      TEST

4. Set the DG_CONFIG setting of LOG_ARCHIVE_CONFIG parameter. for this DB_UNIQUE_NAME of  Standby database has to be different from primary.

SQL> ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(TEST,STBY)';

System altered.

5. Set suitable remote archivelog destinations.

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=STBY NOAFFIRM ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=STBY';

System altered.

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE;

System altered.

-- NOTE :- Service and DB_UNIQUE_NAME has to be of Standby database

6. Set parameters related to LOG_ARCHIVE and password file parameters :-

SQL> ALTER SYSTEM SET LOG_ARCHIVE_FORMAT='%t_%s_%r.arc' SCOPE=SPFILE;

System altered.

SQL> ALTER SYSTEM SET LOG_ARCHIVE_MAX_PROCESSES=8;

System altered.

SQL> ALTER SYSTEM SET REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE SCOPE=SPFILE;

System altered.

7. It is recommended to make sure that Primary can switch roles to become a Standby. For that we need to set some -convert parameters if there are path differences between the servers :-

SQL> ALTER SYSTEM SET FAL_SERVER=STBY;

System altered.

SQL> ALTER SYSTEM SET DB_FILE_NAME_CONVERT=' ',' ' SCOPE=SPFILE;   -- Primay server destination and Standby server destination

System altered.

SQL> ALTER SYSTEM SET LOG_FILE_NAME_CONVERT=' ',' '  SCOPE=SPFILE;  -- Primay server destination and Standby server destination

System altered.

SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;

8. As some parameters cannot be changed while database is open , we need to restart database to before they take effect :-

SQL> shu immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL> startup
ORACLE instance started.

Total System Global Area  960495616 bytes
Fixed Size                  1222816 bytes
Variable Size             247465824 bytes
Database Buffers          700448768 bytes
Redo Buffers               11358208 bytes
Database mounted.
Database opened.
SQL>

9. In both servers entries of Primary as well as Standby are needed in tnsnames.ora file and we are able to do  tnsping.

###################ORACLE_DATA_GAURD############################

TEST =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = primary_server)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = TEST)
    )
  )

STBY =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = STBY_server)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = STBY)
    )
  )

TEST_DGMGRL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = primary_server)(PORT = 9001))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = TEST_DGMGRL)
    )
  )
STBY_DGMGRL =
(DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = STBY_server)(PORT = 9001))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = STBY_DGMGRL)
    )
  )

###################ORACLE_DATA_GAURD############################

10. Add static entries to listener.ora file

PRIMARY

        (SID_DESC =
      (GLOBAL_DBNAME = test.db.com)
      (SID_NAME = TEST)
      (ORACLE_HOME = /opt/oracle/product/database/11.2.0.4)
        )
        (SID_DESC =
        (GLOBAL_DBNAME=TEST_DGMGRL)
        (ORACLE_HOME=/opt/oracle/product/database/11.2.0.4)
        (SID_NAME=test)
        )

STANDBY

      (SID_DESC =
      (GLOBAL_DBNAME = STBY.db.com)
      (SID_NAME = STBY)
      (ORACLE_HOME = /opt/oracle/product/database/11.2.0.4)
    )
      (SID_DESC =
      (GLOBAL_DBNAME = STBY_DGMGRL)
      (SID_NAME = STBY)
      (ORACLE_HOME = /opt/oracle/product/database/11.2.0.4)
    )

11. Reload listener after adding these entries

Lsnrctl reload

12. Add Standby redo logs to primary database :-

ALTER DATABASE ADD STANDBY LOGFILE ('/STBY_redo01.log') SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE ('/STBY_redo02.log') SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE ('/STBY_redo03.log') SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE ('/STBY_redo04.log') SIZE 50M;

13. Copy the passw0rd file from Primary and copy to Standby

14. Copy the init.ora file from primary and change below parameters :-

CREATE PFILE='/initTEST.ora' from spfile;

scp orapwTEST user@STBY_server:$ORACLE_HOME/dbs/orapwSTBY
scp initTEST.ora user@STBY_server:$ORACLE_HOME/dbs/initSTBY.ora

Steps to be done in STANDBY Side :-

15. Start the STANDBY database with above pfile

export ORACLE_SID=STBY
sql> startup nomount pfile='$ORACLE_HOME/dbs/initSTBY.ora'
ORACLE instance started.

Total System Global Area  960495616 bytes
Fixed Size                  1222816 bytes
Variable Size             264243040 bytes
Database Buffers          683671552 bytes
Redo Buffers               11358208 bytes
               
16. Connect to RMAN and use RMAN duplicate command to create STANDBY

rman target sys/****@TEST_DGMGRL auxiliary sys/****@STBY_DGMGRL
DUPLICATE TARGET DATABASE
  FOR STANDBY
  FROM ACTIVE DATABASE
  DORECOVER
  SPFILE
    SET db_unique_name='STBY' COMMENT 'Is STANDBY'
    SET LOG_ARCHIVE_DEST_2='SERVICE=TEST ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=TEST'
                set control_files='/ora_backup/TEST/duplicate_test/STBY/control01.ctl','/ora_backup/TEST/duplicate_test/STBY/control02.ctl'
                set db_create_file_dest='/ora_backup/TEST/duplicate_test/STBY/DATA/'
                set db_create_online_log_dest_1='/ora_backup/TEST/duplicate_test/STBY/LOG/'
                set diagnostic_dest='/ora_backup/TEST/duplicate_test/STBY/diag'
                set audit_file_dest='/ora_backup/TEST/duplicate_test/STBY/adump'
                set db_recovery_file_dest='/ora_backup/TEST/duplicate_test/STBY/FRA'
                set db_file_name_convert='/ora_backup/TEST1/restore_test/TEST/DATA/','/ora_backup/TEST/duplicate_test/STBY/DATA/'
                set log_file_name_convert='/ora_backup/TEST1/restore_test/TEST/','/ora_backup/TEST/duplicate_test/STBY/'
                set log_archive_dest_1='location=/ora_backup/TEST1/duplicate_test/STBY/FRA/'
                set FAL_SEVER=TEST
   ;

Above command will create the STANDBY database

17. Create the broker configuration details ( for both Primary and Standby):-

SQL>  alter system set dg_broker_start=FALSE;

System altered.

SQL> alter system set dg_broker_config_file1='/opt/oracle/product/database/11.2.0.4/dbs/dr1STBY.dat';

System altered.

SQL> alter system set dg_broker_start=TRUE;

System altered.

Create the configuation

Create configuration DR as primary database is TEST connect identifier is TEST;
Add database STBY as connect identifier is STBY maintained as physical;

show configuration
enable configuration

EDIT DATABASE STBY SET STATE = 'APPLY-ON' [WITH APPLY INSTANCE = ];  -- For RAC database

Check if every thing is fine by checking Standby Database details 

show database stby

DGMGRL> show database "STANDBY"

Database - STANDBY

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

Database Status:
SUCCESS

Standby Creation using RMAN duplicate command completed.

Standby creation in RAC using RMAN duplicate.

I hope this article helped you

Regards,
Amit Rath