2 Replies Latest reply: Feb 26, 2013 7:27 AM by ShishirTekadeR RSS

    registering a 10g database to an 11g catalog database hangs at resync

    993419
      I have a problem in which I try to migrate a 10g database to use a new recovery catalog database, 11g. I connect to target and catalog but after issuing register database it hangs as follows:


      server123:oracle 1> rman target / catalog catuser/catpw@CATALOG456

      Recovery Manager: Release 10.2.0.2.0 - Production on Tue Feb 26 13:23:11 2013

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

      connected to target database: TRGT (DBID=823102274)
      connected to recovery catalog database

      RMAN> register database;

      database registered in recovery catalog
      starting full resync of recovery catalog

      [[--hangs here (have let it try to do the resync for +3 hours)--]]


      When I look from the recovery catalog I see it can connect to it:

      SQL> r
      1 SELECT s.inst_id,
      2 s.sid,
      3 s.serial#,
      4 p.spid,
      5 s.username,
      6 s.program
      7 FROM gv$session s
      8 JOIN gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_id
      9* WHERE s.type != 'BACKGROUND'

      INST_ID SID SERIAL# SPID USERNAME PROGRAM
      ---------- ---------- ---------- ---------- ---------- ---------------------------------------------
      1 126 19297 6229 catuser rman@server123 (TNS V1-V3)



      However, from v$session_wait I can see it is idling:

      SQL> select sid,wait_class,seconds_in_wait,wait_time,state,event from v$session_wait where SID = '126';

      SID WAIT_CLASS SECONDS_IN_WAIT
      ---------- ---------------------------------------------------------------- ---------------
      WAIT_TIME STATE EVENT
      ---------- ------------------- ----------------------------------------------------------------
      126 Idle 507
      0 WAITING SQL*Net message from client





      Theres plenty of space left in datafiles in the recovery catalog database. The target is 10g

      SQL> select * from v$version;

      BANNER
      ----------------------------------------------------------------
      Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bi
      PL/SQL Release 10.2.0.2.0 - Production
      CORE 10.2.0.2.0 Production
      TNS for HPUX: Version 10.2.0.2.0 - Production
      NLSRTL Version 10.2.0.2.0 - Production

      and the recovery catalog is 11g

      SQL> select * from v$version;

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



      There are many other databases registered to that recovery catalog database, 10g's and 11g's. I can connect from the recovery catalog database server to the target database server, even using sqlplus. I have done crosschecks and delete expired archivelogs/backups/copies while connected only to target.

      I didn't find anything relating to this except this but its about 10g/10g setup and in 11g the indexes are in place: http://pythianpang.wordpress.com/2010/07/29/rman-resync-catalog-hang-10g-workaround/

      Any thoughts?
        • 1. Re: registering a 10g database to an 11g catalog database hangs at resync
          mseberg
          Hello;

          There are several MOS documents which may address this :

          Resync Catalog Hangs when number of datafiles above 32k [ID 463852.1]

          RMAN: Resync Operation Hangs when SORT_AREA_SIZE is Too Small on Target Database [ID 136209.1]

          Bug 9363515 - RMAN resync hangs when MEMORY_TARGET is set and PGA_AGGREGATE_TARGET becomes zero [ID 9363515.8]

          Rman Backup / Resync Hanging [ID 1525802.1]

          Best Regards

          mseberg
          • 2. Re: registering a 10g database to an 11g catalog database hangs at resync
            ShishirTekadeR
            Restore 10g RMAN Backup to 11g:=====

            The steps for 10g database:

            1- Run the utlu112i.sql Pre-upgrade script. You can find this script under @$ORACLE_HOME/rdbms/admin/. It must be copied from the 11g database software.

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

            This script adds a column named tz_version to table named registry$database. Pre-upgrade script updates this column with the value of the following query.

            SQL> select version from v$timezone_file;

            So it performs following operation.

            SQL> ALTER TABLE registry$database ADD (tz_version NUMBER);
            SQL> UPDATE registry$database set tz_version =4;

            SQL> ALTER PACKAGE “SYS”.”DBMS_REGISTRY” COMPILE BODY;
            SQL> ALTER VIEW “SYS”.”DBA_REGISTRY_DATABASE” COMPILE;

            2- Connect to 10g database and take RMAN full backup.

            #rman target /
            RMAN> backup as backupset database;

            3- Copy 10g database backup files and archive files to 11g database server.

            The steps for 11g database:

            1- Create temporary pfile in $ORACLE_HOME/dbs

            *.audit_file_dest=’/oracle/admin/TALIPDB/adump’
            *.compatible=’11.2.0.0.0′
            *.control_files=’+DATA/talipdb/controlfile/current.257.787742981′,’+DATA/talipdb/controlfile/current.258.787742983′
            *.db_block_size=8192
            *.db_create_file_dest=’+DATA’
            *.db_create_online_log_dest_1=’+RECO’
            *.db_domain=”
            *.db_name=’TALIPDB’
            *.diagnostic_dest=’/oracle’
            *.job_queue_processes=0
            *.open_cursors=300
            *.pga_aggregate_target=1G
            *.processes=150
            *.remote_login_passwordfile=’EXCLUSIVE’
            *.sga_target=2G
            *.undo_tablespace=’UNDOTBS1′

            2- Open the database in NOMOUNT state.

            # rman target /

            RMAN> startup nomount;

            3- Restore controlfile from backup.

            RMAN> restore controlfile from ‘/oracle/ora11g/talipdb/backup/c-784951186-20120620-02′;

            4- Open the database in MOUNT state.

            RMAN> alter database mount;

            5- Catalog RMAN backup files and archive log files.

            RMAN> catalog start with ‘/oracle/ora11g/talipdb/backup’;
            RMAN> catalog start with ‘/oracle/ora11g/talipdb/archive’;

            6- Restore 10g database backup to +DATA diskgroup and perform incomplete recovery.

            RMAN> run
            {
            allocate channel c1 device type disk;
            SET NEWNAME FOR DATAFILE 1 TO ‘+DATA’;
            SET NEWNAME FOR DATAFILE 2 TO ‘+DATA’;
            SET NEWNAME FOR DATAFILE 3 TO ‘+DATA’;
            SET NEWNAME FOR DATAFILE 4 TO ‘+DATA’;
            restore database until sequence 4;
            switch datafile all;
            recover database until sequence 4;
            }

            7- Open the database with RESETLOGS UPGRADE.

            # sqlplus / as sysdba
            SQL> alter database open resetlogs upgrade;

            8- Run the upgrade script.

            SQL> SPOOL upgrade.log
            SQL> @$ORACLE_HOME/rdbms/admin/catupgrd.sql
            SQL> SPOOL off

            9- If our 10g and 11g database os platforms are different then you must run utlmmig.sql script.

            ————–Changing 32 bit to 64 bit————–
            SQL> SHUTDOWN IMMEDIATE
            SQL> STARTUP UPGRADE
            SQL> SPOOL migrate.log
            SQL> @$ORACLE_HOME/rdbms/admin/utlmmig.sql
            SQL> SPOOL off
            ——————————————-

            10- Now, you can open the database.

            SQL> SHUTDOWN IMMEDIATE
            SQL> STARTUP

            11- Run the Post-Upgrade script to check problems.

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

            12- Compile invalid objects.

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

            13- Drop the temporary file and create new one in +DATA diskgroup.

            SQL> alter tablespace temp drop tempfile ‘/data_TALIPDB/temp01.dbf’;
            SQL> alter tablespace temp add tempfile ‘+DATA’ size 1024M;


            *****************************************
            Best Regards,
            Shishir Tekade.
            My Blog: http://shishirtekade.blogspot.com