Sunday, February 10, 2013

0 Oracle Stream (2): Replica whole database

 

DBMS_STREAMS_ADM.PRE_INSTANTIATION_SETUP

Execute the dbms_streams_adm.pre_instantiation_setup as strmadmin

 

DECLARE
  streams_tbs dbms_streams_tablespace_adm.tablespace_set;
BEGIN
    dbms_streams_adm.pre_instantiation_setup( 
        maintain_mode => 'GLOBAL', 
        tablespace_names => streams_tbs, 
        source_database => 'oradb1.panda1.locadomain', 
        destination_database => 'oradb2.panda2.localdomain', 
        perform_actions => true, 
        bi_directional => true, 
        include_ddl => true, 
        start_processes => true, 
        exclude_schemas => NULL, 
        exclude_flags => dbms_streams_adm.exclude_flags_unsupported + dbms_streams_adm.exclude_flags_dml + dbms_streams_adm.exclude_flags_ddl
    );
END; 

 

Create the backup

Create the database dump and archive the latest log.

 

rman nocatalog target / 
backup database format '/u01/app/oracle/backup/%d_t%t_s%s_p%p';
sql'alter system archive log current'; 

 

Get the SCN

 

SET SERVEROUTPUT ON
declare 
  until_scn number; 
  begin 
  until_scn:= dbms_flashback.get_system_change_number; 
  dbms_output.put_line('until scn: '||until_scn); 
  end; 
 /

 

Restore the database backup to the target

  • The YYYYY is the SCN we retrieve from the previous step.
  • The backup file and archive log files must be in the same directory as it on the source . For example, if the backup file is under /u01/backup, it must be on the target’s /u01/backup .
  • The target database server must be in the no mount state otherwise, we would get  “RMAN-05500: the auxiliary database must be not mounted when issuing a DUPLICATE command” error.

 

rman nocatalog target /
connect auxiliary sys/XXXX@oradb2; 
 
 run 
  {   set until scn YYYYYY
  duplicate target database to 'oradb2' 
  backup location '/u01/app/oracle/backup'
  nofilenamecheck   open restricted;   } 

 

RMAN-06457: UNTIL SCN (1763856) is ahead of last SCN in archived logs (1763674)

Sometime, I get the RMAN-06457 error . Here are the steps for performing the trouble shooting.

alter system checkpoint;
alter system archive log current
select thread#, status, enabled, checkpoint_time, checkpoint_change# from v$thread;
select GROUP#,STATUS,FIRST_CHANGE#,NEXT_CHANGE# from v$log;

 

After restore completed successfully, the target database has been set in the restricted mode.

image

Fix the db link and global name on the target db

Because the source db has been restore to the target,we need to fix the db link and the global db name

select db_link from all_db_links;
drop database link ORADB2.PANDA2.LOCALDOMAIN;
 
create database link ORADB1.panda1.localdomain connect to strmadmin
identified by XXXX using 'ORADB1';
select count(*) from HR.employees@ORADB1.panda1.localdomain;
 
alter database rename global_name to oradb2.panda2.localdomain;
select * from global_name;

 

DBMS_STREAMS_ADM.POST_INSTANTIATION_SETUP

  • Run below as strmadmin on the target db.
  • The YYYYY is the SCN number –l

 

declare 
  streams_tbs dbms_streams_tablespace_adm.tablespace_set; 
  begin 
  dbms_streams_adm.post_instantiation_setup( 
  maintain_mode => 'GLOBAL', 
  tablespace_names => streams_tbs, 
  source_database => 'oradb1.panda1.localdomain', 
  destination_database => 'oradb2.panda2.localdomain', 
  perform_actions => true, 
  bi_directional => true, 
  include_ddl => true, 
  start_processes => true, 
  instantiation_scn => YYYYYY
  exclude_schemas => '*', 
  exclude_flags => dbms_streams_adm.exclude_flags_unsupported + 
  dbms_streams_adm.exclude_flags_dml + dbms_streams_adm.exclude_flags_ddl); 
  end; 

 

ORA-27492: unable to run job "SYS.AQ_JOB$_85": scheduler unavailable

I always get the ORA-27492 error on the step 49. It is because the Oracle 11g does not allow jobs run on the restricted mode. The solution is either enable this job to run on the restricted mode or remove the restricted mode from database.

 

Enable the job can run in restricted mode

BEGIN
  dbms_scheduler.set_attribute ( 'AQ_JOB$_85', 'allow_runs_in_restricted_mode',true);
END;
/

After fixing the job permission issue, we can execute DBMS_STREAMS_ADM.RECOVER_OPERATION to continue the set up.

 

Disable the restricted session

In the end of setup, we need to disable the database from restricted mode.

alter system disable restricted session

 

Trouble shooting the DBMS_STREAMS_ADM package

If the DBMS_STREAMS_ADM.PRE_INSTANTIATION_SETUP fail, we can query below table for trouble shooting.

SELECT * FROM dba_recoverable_script

From dba_recoverable_script, we can know the script id .

Either we can trouble shooting why it fail or we can try to roll it back or purge it.

exec DBMS_STREAMS_ADM.RECOVER_OPERATION('SCRIPT_ID','ROLLBACK');
exec DBMS_STREAMS_ADM.RECOVER_OPERATION('SCRIPT_ID','PURGE');

if we can fix the error, we can continue the setup by issuing

exec DBMS_STREAMS_ADM.RECOVER_OPERATION('SCRIPT ID','FORWARD');

Query DBA_RECOVERABLE_SCRIPT_BLOCKS to see what BLOCK is running .

 

SELECT FORWARD_BLOCK,
       FORWARD_BLOCK_DBLINK,
       STATUS
  FROM DBA_RECOVERABLE_SCRIPT_BLOCKS
  WHERE SCRIPT_ID = '<SCRIPT ID>' AND
        BLOCK_NUM = <BLOCK NUM>;

 

Query DBA_RECOVERABLE_SCRIPT_ERRORS to retrieve the error message

select *
from DBA_RECOVERABLE_SCRIPT_ERRORS
  WHERE SCRIPT_ID = 'SCRIPT ID'

 

After fix the problem, we can run to continue to process

exec DBMS_STREAMS_ADM.RECOVER_OPERATION('SCRIPT ID','FORWARD');

 

Remove the configuration

Run below on both source and target db

exec dbms_streams_adm.remove_streams_configuration;

 

Reference

0 comments:

Post a Comment

 

SQL Panda Copyright © 2011 - |- Template created by O Pregador - |- Powered by Blogger Templates