STANDBY DATABASE

-

 


NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

service_names                        string      prod



alter system set log_archive_dest_2='service=prod db_unique_name=prod';


alter system set log_archive_dest_state_2=enable;


alter system set fal_server=proddr;


alter system set log_archive_dest_1='location=/u01/app/oracle/product/19/db_1/dbs/arch';


alter system set standby_file_management='auto';

 

 RMAN> backup database format'/u01/prod_bkp/bkp_full_%U';


RMAN> backup archivelog all format '/u01/prod_bkp/bkp_archive_%U';


RMAN> backup current controlfile for standby format '/u01/prod_bkp/bkp_ctl_%U';



alter system set log_archive='dg_config=(prod ,proddr)';



RMAN> restore standby controlfile from '/u01/proddr_bkp/bkp_ctl_052ursvq_1_1';


RMAN> catalog start with '/u01/proddr_bkp';



---------------------------------------


ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(PRIMARY_DB,STANDBY_DB)';

ALTER SYSTEM SET LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=PRIMARY_DB';

ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=STANDBY_DB LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=STANDBY_DB';

ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE;

ALTER SYSTEM SET LOG_ARCHIVE_FORMAT='%t_%s_%r.arc';

ALTER SYSTEM SET LOG_ARCHIVE_MAX_PROCESSES=4;

ALTER SYSTEM SET FAL_SERVER=STANDBY_DB;

ALTER SYSTEM SET FAL_CLIENT=PRIMARY_DB;

ALTER SYSTEM SET DB_FILE_NAME_CONVERT='/primary/data','/standby/data';

ALTER SYSTEM SET LOG_FILE_NAME_CONVERT='/primary/data','/standby/data';

ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;

----------------------------------------------------

Enable ARCHIVELOG Mode:


SHUTDOWN IMMEDIATE;

STARTUP MOUNT;

ALTER DATABASE ARCHIVELOG;

ALTER DATABASE OPEN;


ALTER DATABASE FORCE LOGGING;


ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(PROD,PRODDR)';

ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(prod,orcl)';

ALTER SYSTEM SET LOG_ARCHIVE_DEST_1='LOCATION=/u01/app/oracle/product/19/db_1/dbs/arch) DB_UNIQUE_NAME=PROD';

ALTER SYSTEM SET LOG_ARCHIVE_DEST_1='LOCATION=/u01/app/oracle/proddruct/19/db_1/dbs/arch) DB_UNIQUE_NAME=proddr';


ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=orcl DB_UNIQUE_NAME=orcl'scope=both;

ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE;

ALTER SYSTEM SET LOG_ARCHIVE_FORMAT='%t_%s_%r.arc';

ALTER SYSTEM SET LOG_ARCHIVE_MAX_PROCESSES=4;

ALTER SYSTEM SET FAL_SERVER=orcl;

ALTER SYSTEM SET FAL_CLIENT=PROD;

ALTER SYSTEM SET DB_FILE_NAME_CONVERT='/u01/app/oracle/oradata/PROD/','/u01/app/oracle/oradata/PRODDR/';

ALTER SYSTEM SET LOG_FILE_NAME_CONVERT='/u01/app/oracle/oradata/PROD/','/u01/app/oracle/oradata/PRODDR/';

ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;


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

ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE;


SQL> startup NOMOUNT PFILE='/u01/app/oracle/product/19/db_1/dbs/initproddr.ora';


sqlplus /nolog


connect / as sysdba


 select instance_name, status, database_status from v$instance;

------------------------------------

select name, open_mode, db_unique_name, database_role from v$database;


START MRP :


ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;


STOP MRP :


ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;


alter system set log_archive_dest_state_2='defer';


alter system set log_archive_dest_state_2='enable';


alter system switch logfile;

 

select error,status from v$archive_dest where dest_id=2;


select error,status from v$archive_dest where dest_id=1;



select process ,status ,sequence# from v$managed_standby;

  

select dest_id,status,destination,error from v$archive_dest where dest_id=2;

  

##checking order of arhive logs

  

select sequence#,first_time,next_time,applied,archived from  v$archived_log

where name='orcl' order by first_time;


#GAP checking


select status,gap_status from v$archive_dest_status where dest_id=2;



select process ,status ,sequence# from v$managed_standby;


alter database recover managed standby database disconnect from session;


process checking -


select client_process,process,thread#,sequence#,status from v$managed_standby 

where client_process='LGWR' or process='MRP0';


checking both prm and stan are in sync or not



SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received",

APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference"

FROM

(SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN

(SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,

(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN

(SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL

WHERE

ARCH.THREAD# = APPL.THREAD#

ORDER BY 1;


sequence# checking


 select max(sequence#) from v$archived_log;

 

 select max(sequence#) from v$archived_log where applied='YES';

 


--------------------------------------------------------------

rman target sys@PRODDR



-- On primary


SELECT DEST_ID, DEST_NAME, STATUS, ERROR FROM V$ARCHIVE_DEST WHERE TARGET='proddr';



 checking the pwd file same or not on bith db -

 

 md5sum orapwprod


 systemctl disable firewalld


:%s/ prod/prodr


SQL> show parameter remote_login_passwordfile;



select group#,thread#,sequence#,archived,status from v$standby_log;


ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 11 '/u01/app/oracle/oradata/DEVDB/onlinelog/redo11.log' SIZE 50M;

ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 12 '/u01/app/oracle/oradata/DEVDB/onlinelog/redo12.log' SIZE 50M;

ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 13 '/u01/app/oracle/oradata/DEVDB/onlinelog/redo13.log' SIZE 50M;

ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 14 '/u01/app/oracle/oradata/DEVDB/onlinelog/redo14.log' SIZE 50M;



orapwd file=$ORACLE_HOME/dbs/orapworcl entries=10 force=y


orapwd file=orapwdevdb password=sadashiv#123 entries=10 force=y


 orapwd file=orapwprimary password=sonali#123 entries=10 force=y

 

 orapwd file=orapwprod password=sadashiv#123 entries=10 force=y



sqlplus sys/sys@prod as sysdba


rman target sys/sadashiv#123@prod  auxiliary sys/sadashiv#123@orcl1;


rman target sys/sys@prod  auxiliary sys/sys@orcl1;


rman target sys/sonali#123@primary1  auxiliary sys/sonali#123@standby1;



alter sysetm register;


 sqlplus sys/sys@ORCL1

mkdir -p /u01/app/oracle/admin/devdb/adump

mkdir -p /u01/data/db_files/devdb/

mkdir -p /u01/FRA/devdb


sadashiv#123


scp orapwporcl oracle@192.168.93.134:/u01/app/oracle/product/19.3/db_home/dbs


ALTER SYSTEM SET DB_RECOVERY_FILE_DEST = '/u01/app/oracle/recovery_area' SCOPE=BOTH;


ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE = 20G SCOPE=BOTH;


ALTER SYSTEM SET LOG_ARCHIVE_DEST_2 = 'SERVICE=orcl ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcl' SCOPE=BOTH;


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


SHOW PARAMETER LOCAL_LISTENER;


ALTER SYSTEM SET LOCAL_LISTENER = '(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.93.132)(PORT=1521))' SCOPE=BOTH;


./runInstaller -executePrereqs -silent -responseFile /u01/app/oracle/product/19/db_1/install/response/db_install.rsp


./runInstaller -silent -responseFile /u01/app/oracle/product/19/db_1/install/response/db_install.rsp


./runInstaller -ignorePrereq -waitforcompletion -silent  -responseFile /u01/app/oracle/product/19.3/db_home/install/response/db_install.rsp


CREATE CONFIGURATION 'my_dg_config' AS PRIMARY DATABASE IS 'prod' CONNECT IDENTIFIER IS 'prod';


ADD DATABASE 'orcl' AS CONNECT IDENTIFIER IS 'orcl' MAINTAINED AS PHYSICAL;


 create spfile='/tmp/initorcl.ora' from pfile;

 

SQL> alter system set service_names='orcl$' scope=spfile;


RMAN> connect auxiliary sys@ORCL


rman target sys/sadashiv#123@ORCL1 auxiliary sys/sadashiv#123@PROD;


duplicate target database for standby from active database nofilenamecheck;


ALTER DATABASE FLASHBACK OFF;


alert location- 

/u01/app/oracle/diag/rdbms/orcl/orcl/trace


control file location

/u01/app/oracle/oradata/PROD


 alter database create standby controlfile as '/u01/app/oracle/oradata/PROD/orcl.ctl';

----------------------------------------------

mkdir /u01/bkp


rman target /


backup database format '/u01/bkp/full_bkp_%U';


backup archivelog all format '/u01/bkp/arch_%U';


backup current controlfile for standby format '/u01/bkp/control_ctl_%U';


 backup incremental from scn 3209301 database format '/u01/bkp/incr_bkp_%U';

 

scp /u01/bkp * 


standby


mkdir /u01/bkp


db in - no mount 


rman target /


restore controlfile from '/u01/bkp/control_ctl_112vg1oa_1_1


alter database mount;


catalog start with '/u01/bkp/';


restore database ;


recover database ;

 

mrp start 


check rfs status


alter database open resetlogs;


RMAN> restore standby controlfile from service 'ORCL1'; (db in no mount )


recover database from service 'ORCL1';


restore database from service 'ORCL1'


RMAN> restore standby controlfile from service 'ORCL1';


-------------------------------------

START REAL TIME REDO APPLY IN DG :


Directly apply the redologs without waiting to fill the standby redolog file MRP will apply it realtime no lag and no gap fast sync.


alter database recover managed standby database using current logfile;


--------------------------------

Incremental backup and restore in DG -


Determine the SCN on the Standby Database:-


SELECT CURRENT_SCN FROM V$DATABASE;


 RMAN> CONNECT TARGET sys/sys@primary_db;


 backup current controlfile for standby format '/u01/bkp/control_ctl_%U';


RMAN> RUN {

    ALLOCATE CHANNEL ch1 DEVICE TYPE disk;

    BACKUP INCREMENTAL FROM SCN 3209819 DATABASE FORMAT '/u01/bkp/incr_backup_%U';

    RELEASE CHANNEL ch1;

    }


RMAN> CONNECT TARGET sys/sys@stadby_db;


 restore controlfile from '/u01/bkp/control_ctl_112vg1oa_1_1


 RUN {

    ALLOCATE CHANNEL ch1 DEVICE TYPE disk;

    CATALOG START WITH '/u01/bkp/';

    RECOVER DATABASE NOREDO;

    RELEASE CHANNEL ch1;

    }



----------------------------------


CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%U'; 



exec DBMS_SERVICE.CREATE_SERVICE (service_name => 'prod1_rw', network_name => 'prod1_rw', failover_method => 'BASIC', failover_type => 

'SELECT', failover_retries => 30, failover_delay => 10);

----------------------------------------------

creating clinet connection -


BEGIN

    DBMS_SERVICE.CREATE_SERVICE (

        service_name => 'prod1_rw',

        network_name => 'prod1_rw',

        failover_method => 'BASIC',

        failover_type => 'SELECT',

        failover_retries => 30,

        failover_delay => 10

    );

END;

/


define db primary -


create or replace procedure prod1_rw_service

is

v_role VARCHAR(30);

begin

select DATABASE_ROLE into v_role from V$DATABASE;

if v_role = 'PRIMARY' then

DBMS_SERVICE.START_SERVICE('prod1_rw');

else

DBMS_SERVICE.STOP_SERVICE('prod1_rw');

end if;

end;

/



TRIGGER TO START SERVICE ON DB STARTUP:

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

create or replace TRIGGER db_startup

after startup on database

begin

prod1_rw_service;

end;

/


TRIGGER TO START SERVICE ON DB ROLECHANGE:

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

create or replace TRIGGER db_role_change

after db_role_change on database

begin

prod1_rw_service;

end;

/



SQL> exec prod1_rw_service;


tnsnames.ora file config on client -


prod =

  (DESCRIPTION =

    (ADDRESS_LIST=

      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.93.132)(PORT = 1521))

      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.93.134)(PORT = 1521))

    )

   (CONNECT_DATA = (SERVICE_NAME = prod1_rw)

     (FAILOVER_MODE=(TYPE=SELECT)(METHOD=BASIC)(RETRIES=30)(DELAY=10))

   )

  ) 

-------------------------------------------------------------------

Oracle Data Guard Manual Switchover and Failover on Physical Standby : 


select name, open_mode, db_unique_name, database_role from v$database;


On primary:

===========

select STATUS, GAP_STATUS from V$ARCHIVE_DEST_STATUS where DEST_ID = 2;


On standby:

===========

select NAME, VALUE, DATUM_TIME from V$DATAGUARD_STATS;


On primary:

===========

select SWITCHOVER_STATUS from V$DATABASE;


alter database commit to switchover to physical standby with session shutdown;


startup mount;


Convert standby to primary: 


select SWITCHOVER_STATUS from V$DATABASE;


alter database commit to switchover to primary with session shutdown;


alter database open;


On new standby on MRP -


alter database recover managed standby database disconnect;

------------------------------

Failover primary database :


select name, open_mode, db_unique_name, database_role from v$database;


On standby:

===========

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH;

SQL> select SWITCHOVER_STATUS from V$DATABASE;


You must see TO PRIMARY or SESSIONS ACTIVE. Switch standby to primary


SQL> alter database commit to switchover to primary with session shutdown;

SQL> alter database open;


Rebuild Primary After Failover :


Post failover, there are two methods of rebuilding your failed primary


Method 1:Rebuild from scratch à RMAN duplicate

Method 2: Flashback database à only if Flashback was enabled


Get the SCN at which standby became primary: 


select to_char(standby_became_primary_scn) from v$database;


Flashback crashed primary(proddb):


startup mount;


flashback database to scn <standby_became_primary_scn>;


alter database convert to physical standby;


alter database recover managed standby database disconnect;

--------------------------------------------------


Convert Physical Standby into Snapshot Standby: 


On standby:

===========

SQL> alter database recover managed standby database cancel;

SQL> select name, open_mode from v$database; >> make sure its mounted

SQL> alter database convert to snapshot standby;

SQL> alter database open; >> open the DB

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


Verifying snapshot standby:

On standby:

===========

SQL> select name, guarantee_flashback_database from v$restore_point;

SQL> create table student(sno number(2), s_name varchar2(10));

SQL> insert into student values(1,'RAM');

SQL> insert into student values (2,'Max');

SQL> commit;

SQL> select * from student;


Revert back snapshot standby to physical standby:


On standby:

===========

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

SQL> shut immediate;

SQL> startup mount;

SQL> alter database convert to physical standby;

SQL> shutdown immediate

SQL> startup mount;

SQL> alter database recover managed standby database disconnect;

SQL> select * from student;


-----------------------------------------

Configure Data Guard Broker :


Edit listeners

 (SID_DESC=

     (GLOBAL_DBNAME=orcl_DGMGRL)

     (SID_NAME=orcl)

     (ORACLE_HOME=/u01/app/oracle/product/19.3/db_home)

   )

   

   Stop MRP on standby :

   

 On standby:

===========

SQL> alter database recover managed standby database cancel;

SQL> alter system set LOG_ARCHIVE_DEST_2='' SCOPE=BOTH sid='*';


On primary:

===========

SQL> alter system set LOG_ARCHIVE_DEST_2='' SCOPE=BOTH sid='*';


Enable broker :

On primary:

===========

SQL> alter system set dg_broker_start=true;

SQL> show parameter dg_broker_start;


On standby:

===========

SQL> alter system set dg_broker_start=true;

SQL> show parameter dg_broker_start;


Register primary with broker:


On primary:

===========

dgmgrl sys/sadashiv#123@prod1

DGMGRL> create configuration my_dg_config as primary database is prod connect identifier is prod1;

DGMGRL> show configuration;


Register standby with broker;


DGMGRL> add database orcl as connect identifier is orcl1;

DGMGRL> show configuration;


Enable Data Guard broker:

Once primary and standby are registered, we must enable broker


DGMGRL> DISABLE CONFIGURATION;

DGMGRL> SHOW CONFIGURATION;

DGMGRL> SHOW DATABASE prod;

DGMGRL> SHOW DATABASE orcl;


Turn on/off the apply process in DG :


edit database orcl set state=APPLY-OFF;

edit database orcl set state=APPLY-ON;


Turn on/off the TRANSPORT from the source db :


edit database prod set state=TRANSPORT-OFF

edit database prod set state=TRANSPORT-ON

------------------------------------------


Oracle Data Guard Broker Switchover and Failover:


Switchover Using DGMGRL :


On primary:

===========

dgmgrl sys/sys@prod


DGMGRL> show configuration;


DGMGRL> SWITCHOVER TO orcl;


DGMGRL> show configuration;


Revert Back Using DGMGRL:


On current primary(orcl):

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

dgmgrl sys/sys@orcl


DGMGRL> show configuration;


DGMGRL> switchover to prod;


DGMGRL> show configuration;


Data Guard Broker Failover :-


On primary:

===========

ps -ef|grep pmon


grid      2636     1  0 Apr15 ?        00:00:12 asm_pmon_+ASM

oracle   16914     1  0 14:03 ?        00:00:00 ora_pmon_prod

oracle   17722 15893  0 15:08 pts/0    00:00:00 grep pmon


kill -9 16914 


Failover to Standby


On standby:

===========

dgmgrl sys/sys@orcl


DGMGRL> show configuration;

DGMGRL> FAILOVER TO orcl;

Performing failover NOW, please wait...

Failover succeeded, new primary is "orcl"


DGMGRL> show configuration;


Rebuild Primary After Failover :-


On current primary (orcl):

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

dgmgrl sys/sys@orcl

(note stb (prod - should be in mount )

DGMGRL> show configuration;

DGMGRL> reinstate database prod;

Reinstating database "proddb", please wait...


Verify proddb post reinstate:

On failed primary (prod):

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

select name,open_mode from v$database;

select process, status, sequence# from v$managed_standby;


Switchover to get original configuration:


On current primary (proddb_st):

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

dgmgrl sys/sys@orcl

DGMGRL> switchover to prod;

----------------------------------------

Configure Fast Start Failover :


On primary(proddb):

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

dgmgrl sys/sys@proddb


DGMGRL> show database prod StaticConnectIdentifier;

DGMGRL> show database orcl StaticConnectIdentifier;


Define FastStartFailoverTarget:

On primary (proddb):

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

dgmgrl sys/sys@prod

DGMGRL> SHOW FAST_START FAILOVER

DGMGRL> EDIT DATABASE prod SET PROPERTY FastStartFailoverTarget = 'orcl';

DGMGRL> EDIT DATABASE orcl SET PROPERTY FastStartFailoverTarget = 'prod';

DGMGRL> show database verbose prod;

DGMGRL> show database verbose orcl;


Define FastStartFailoverThreshold :

DGMGRL> EDIT CONFIGURATION SET PROPERTY FastStartFailoverThreshold=30;

DGMGRL> show fast_start failover;

DGMGRL> EDIT CONFIGURATION SET PROPERTY FastStartFailoverLagLimit = 30;


Enable FSFO : Never start observer on production database


On Standby Server:

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


dgmgrl sys/sys@orcl


DGMGRL> ENABLE FAST_START FAILOVER;

DGMGRL> show configuration;

DGMGRL> start observer;


Test FSFO Configuration:

On primary (prod):

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

sqlplus / as sysdb

SQL> shut abort;


On new primary (orcl):

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

sqlplus / as sysdba

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


Reinstate Failed Primary:

On failed primary (prod):

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

sqlplus / as sysdba

SQL> startup mount;


dgmgrl sys/sys@prod

DGMGRL> show configuration;


On current primary (orcl):

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

dgmgrl sys/sys@orcl

DGMGRL> show configuration;

DGMGRL> switchover to prod;


Disable FSFO:

dgmgrl sys/sys@prod

DGMGRL> DISABLE FAST_START FAILOVER;


Stop observer:

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

dgmgrl sys/sys@orcl

DGMGRL> stop observer;


-----------------------------------------

Convert Physical Standby Into Logical Standby :


Primary Database Changes

DGMGRL> show configuration


Configuration - my_dg


  Protection Mode: MaxPerformance

  Members:

  prod     - Primary database

    orcl - (*) Physical standby database


Fast-Start Failover: DISABLED


Configuration Status:

SUCCESS   (status updated 59 seconds ago)


In the primary database, retrieve the list of objects that are not supported


set pagesize 25;

column owner format a5;

select distinct owner, table_name from dba_logstdby_unsupported order by owner,table_name;

Check the reason behind those unsupported objects


select column_name,data_type from dba_logstdby_unsupported where owner='OE' and table_name= 'CUSTOMERS';

Identify the unsupported schemas on primary


Col owner format a20;

select owner from dba_logstdby_skip where statement_opt = 'INTERNAL SCHEMA';


Make sure each table row in primary database can be uniquely identified. To fix the table, make sure you add a primary key


SELECT OWNER, TABLE_NAME,BAD_COLUMN FROM DBA_LOGSTDBY_NOT_UNIQUE;

Redo Apply needs to be stopped on the Physical Standby now


DGMGRL> edit database ip7_st set state = apply-off;

Build Log Miner directory on primary


SQL> exec dbms_logstdby.build


Open Physical Standby


On Standby Server

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

SQL> alter database recover to logical standby orcl;

SQL> shutdown immediate

SQL> startup mount

SQL> alter database open resetlogs;

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


Data Guard Broker Changes :

DGMGRL> remove database orcl;


Start Logical Apply Service on standby server


On Standby

==========

SQL> alter database start logical standby apply immediate;


Now add the new logical standby to the broker configuration


DGMGRL> add database orcl_stb as connect identifier is orcl_stb;

DGMGRL> enable database orcl_stb


DGMGRL> show configuration;


Configuration - my_dg


  Protection Mode: MaxPerformance

  Members:

  prod     - Primary database

    orcl_stb - Logical standby database


Fast-Start Failover: DISABLED


Configuration Status:

SUCCESS   (status updated 5 seconds ago)


Verify logical standby:


On standby:

===========

ps -ef|grep lsp


SQL> SELECT name, value FROM v$logstdby_stats;


Shutting Down Logical Standby :


Switch log on primary


Alter system switch logfile;


On standby, stop LSP:


Alter database stop logical standby apply;

Shutdown immediate;

To start the LSP process on standby


Startup;

Alter database start logical standby apply immediate;

-----------------------------------------

EDIT DATABASE 'orcl' SET PROPERTY 'Log_Archive_Dest_State_2'='DEFER';


 CREATE CONFIGURATION 'my_dg_config' AS PRIMARY DATABASE IS 'prod' CONNECT IDENTIFIER IS 'prod';

 

 ADD DATABASE 'prod' AS CONNECT IDENTIFIER IS 'prod';

 

 ENABLE DATABASE 'orcl';


ALTER DATABASE CONVERT TO PHYSICAL STANDBY;


RMAN> run

{

duplicate target database for standby from active database 

NOFILENAMECHECK

spfile

parameter_value_convert 'PROD','orcl'

set db_name='PROD'

set db_unique_name='orcl'

set db_file_name_convert '/u01/prod/oradata','/u01/prod/oradata'

set log_file_name_convert '/u01/prod/oradata/redolog',

'/u01/prod/oradata/redolog'

;

}


ALTER SYSTEM SET db_recovery_file_dest_size = 5G SCOPE=BOTH;


-


backup database plus archivelog;


 alter database create standby controlfile as '/u01/bkp/orcl.ctl';

 

 

 BACKUP DATAFILE 1 FORMAT '/u01/bkp/datafile1_%U.bkp';

 


log_archive_dest_2                   string      SERVICE=prod1 ASYNC VALID_FOR=

                                                 (ONLINE_LOGFILES,PRIMARY_ROLE)

                                                  DB_UNIQUE_NAME=prod

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

ALTER DATABASE BACKUP CONTROLFILE TO TRACE AS '/u01/app/oracle/controlfile_trace.sql' RESETLOGS;


/u01/app/controlfile_trace.sql


@/u01/app/controlfile_trace.sql



STARTUP NOMOUNT 


CREATE CONTROLFILE REUSE DATABASE "ORCL" RESETLOGS ARCHIVELOG

    MAXLOGFILES 16

    MAXLOGMEMBERS 3

    MAXDATAFILES 100

    MAXINSTANCES 8

    MAXLOGHISTORY 292

DATAFILE

    '/u01/app/oracle/oradata/ORCL/datafile/o1_mf_system_mc8t4cp0_.dbf',

    '/u01/app/oracle/oradata/ORCL/datafile/o1_mf_sysaux_mc8t9k3d_.dbf',

    '/u01/app/oracle/oradata/ORCL/datafile/o1_mf_undotbs1_mc8t9k44_.dbf',

    '/u01/app/oracle/oradata/ORCL/datafile/o1_mf_users_mc8t4ctg_.dbf'

'/u01/app/oracle/oradata/ORCL/datafile/o1_mf_temp_mc8thz20_.tmp



LOGFILE

    GROUP 1 '/u01/app/oracle/oradata/ORCL/onlinelog/o1_mf_1_mc8thqfn_.log' SIZE 50M,

    GROUP 2 '/u01/app/oracle/oradata/ORCL/onlinelog/o1_mf_2_mc8thqgr_.log' SIZE 50M,

    GROUP 3 '/u01/app/oracle/oradata/ORCL/onlinelog/o1_mf_3_mc8thqjs_.log' SIZE 50M

    -- Add additional redo log files here


CHARACTER SET AL32UTF8;


TEMPFILE

    '/u01/app/oracle/oradata/ORCL/tempfile/temp01.dbf' SIZE 20M REUSE;



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


Database drop with commands:


shut immediate;


startup mount ;


ALTER SYSTEM ENABLE RESTRICTED SESSION;


DROP DATABASE;


rm -rf /path_to_your_database_files/*



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

Comments

Post a Comment

Popular posts from this blog

RAC NOTES

-
================================== Oracle Clusterware Startup Process : =================================== 1.File Read Order: - OLR (Oracle Local Registry) - VD (Voting Disk) - OCR (Oracle Cluster Registry) 2.Process Start Order: - OHASD (Oracle High Availability Services Daemon) - Main Wrapper script (etc/inittab) , OLR  - CSSD (Cluster Synchronization Services Daemon) - Main  Voting disk - GPNP profile - KFED/KFOD - CRSD (Cluster Ready Services Daemon)  - Main look for OCR and GPNP profile  Since Oracle 11g Release 2, the ASM spfile (Automatic Storage Management Server Parameter File) is automatically stored in the first disk group created during Grid Infrastructure installation. However, because the Voting Disk and OCR are stored in ASM, ASM must be started for Clusterware to function. This creates a challenge: how do you start ASM if its spfile is also stored on an ASM disk group? Clusterware Startup Sequence and Resolution: =====================================...
Read more

ORACLE CLOUD INFRASTRUCTURE

-
OCI : ======= Oracle Cloud Infrastructure (OCI) is Oracle's cloud computing platform that provides a wide range of cloud services, including computing, storage, networking, databases, and security. It is designed for running enterprise workloads with high performance, security, and reliability. OCI Service Models OCI offers services across three major cloud models: 1)Infrastructure as a Service (IaaS) ------------------------------------- Provides fundamental cloud resources like compute, storage, and networking. Examples: Compute (Bare Metal, Virtual Machines, GPUs) Block, File & Object Storage Networking (Load Balancers, VPN, FastConnect) Oracle Cloud VMware Solution Dedicated Bare Metal Servers 2)Platform as a Service (PaaS) : ------------------------------- Provides managed services for databases, application development, and AI/ML without managing underlying infrastructure. Examples: Oracle Autonomous Database (Self-driving, self-securing, and self-repairing DB) Oracle Dat...
Read more

DATAPUMP (export import) IN ORACLE

-
----------------------------------- EXPDP (Data Pump Export) Parameters : ------------------------------------ Parameter       Description                                                   Example DIRECTORY   Specifies the directory object where dump files are written. DIRECTORY=DATA_PUMP_DIR DUMPFILE   Specifies the name(s) of the dump file(s).                  DUMPFILE=expdp_dump_%U.dmp (for parallel) LOGFILE       Specifies the name of the log file.                          LOGFILE=expdp.log SCHEMAS       Exports one or more schemas.                                  SCHEMAS=HR,SCOTT TABLES ...
Read more