Wednesday, September 20, 2017

dropping tablespace is not releasing space in red hat

issue :

Drop tablespace including contents and datafiles . The space is not released at disk level
SQL> select distinct owner from dba_segments where tablespace_name='APPCHE_DATA';

no rows selected

SQL> drop tablespace APPCHE_DATA  INCLUDING CONTENTS AND DATAFILES ;

Tablespace dropped.

SQL> select BYTES/1024/1024,FILE_NAME,autoextensible,maxbytes/1024/1024 as mb from dba_Data_files where TABLESPACE_NAME='APPCHE_INDEX';

BYTES/1024/1024 FILE_NAME                                                                                            AUT         MB
--------------- ---------------------------------------------------------------------------------------------------- --- ----------
            100 /appl/oradata/MyappP/db01/MyappP_APPCHE_index_01.dbf                                               YES       8192
            100 /appl/oradata/MyappP/db02/MyappP_APPCHE_index_02.dbf                                               YES       8192
            100 /appl/oradata/MyappP/db03/MyappP_APPCHE_index_03.dbf                                               YES       8192
            100 /appl/oradata/MyappP/db04/MyappP_APPCHE_index_04.dbf                                               YES       8192

SQL> select distinct owner from dba_segments where tablespace_name='APPCHE_INDEX';

no rows selected

SQL>  drop tablespace APPCHE_INDEX  INCLUDING CONTENTS AND DATAFILES ;

Tablespace dropped.

SQL> exit


> df -h |grep db
                      3.9G  804M  3.1G  21% /appl/oradata/xdb01
                      3.9G  804M  3.1G  21% /appl/oradata/xdb02
/dev/mapper/vg04-db01
                       50G   35G   15G  71% /appl/oradata/MyappP/db01
/dev/mapper/vg06-db02
                       50G   35G   15G  71% /appl/oradata/MyappP/db02
/dev/mapper/vg07-db03
                       50G   35G   15G  71% /appl/oradata/MyappP/db03
/dev/mapper/vg08-db04
                       50G   35G   15G  71% /appl/oradata/MyappP/db04

solution:


check whether the removed files are waiting using lsof command. In our case one of the process is using those files


> /usr/sbin/lsof | grep deleted
oracle_64 64540    oracle  282u      REG              253,3 22548586496       20 /appl/oradata/MyappP/db02/MyappP_APPCHE_data_02.dbf (deleted)
oracle_64 64540    oracle  283u      REG              253,6 22548586496       21 /appl/oradata/MyappP/db01/MyappP_APPCHE_data_01.dbf (deleted)
oracle_64 64540    oracle  284u      REG              253,1 22548586496       20 /appl/oradata/MyappP/db04/MyappP_APPCHE_data_04.dbf (deleted)
oracle_64 64540    oracle  285u      REG              253,2 22548586496       20 /appl/oradata/MyappP/db03/MyappP_APPCHE_data_03.dbf (deleted)
oracle_64 64540    oracle  286u      REG              253,3   104865792       21 /appl/oradata/MyappP/db02/MyappP_APPCHE_index_02.dbf (deleted)
oracle_64 64540    oracle  287u      REG              253,6   104865792       22 /appl/oradata/MyappP/db01/MyappP_APPCHE_index_01.dbf (deleted)
oracle_64 64540    oracle  288u      REG              253,1   104865792       21 /appl/oradata/MyappP/db04/MyappP_APPCHE_index_04.dbf (deleted)
oracle_64 64540    oracle  289u      REG              253,2   104865792       21 /appl/oradata/MyappP/db03/MyappP_APPCHE_index_03.dbf (deleted)

Now find the session and get the connection closed

oracle@ovmp1[LSNR_ADMIN]:/appl/oradata/MyappP/db01
> . oraenv
ORACLE_SID = [LSNR_ADMIN] ? MyappP
The Oracle base remains unchanged with value /appl/oracle
oracle@ovmp1[MyappP]:/appl/oradata/MyappP/db01
> sql

SQL*Plus: Release 12.1.0.2.0 Production on Thu Sep 21 13:33:17 2017

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production


SQL>  select addr from v$process where spid=64540 ;

ADDR
----------------
00000000DE25ACF0

SQL> select sid,username,program,machine from v$session where paddr='00000000DE25ACF0';

       SID USERNAME
---------- ------------------------------
PROGRAM
------------------------------------------------
MACHINE
----------------------------------------------------------------
        37 SYS
sqlplus@ovmp1.telecom.tcnz.net (TNS V1-V3)
ovmp1.telecom.tcnz.net


SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
oracle@ovmp1[MyappP]:/appl/oradata/MyappP/db01
 
This will release the space back


Posted by at No comments:
Labels: ,

Thursday, September 14, 2017

RMAN-06136: ORACLE error from auxiliary database: ORA-19502



Issue:  duplicate failing during open reset logs (duplicate failed with RMAN-06136:, )


 contents of Memory Script:
{
   Alter clone database open resetlogs;
}
executing Memory Script

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 09/15/2017 12:53:31
RMAN-05501: aborting duplication of target database
RMAN-03015: error occurred in stored script Memory Script
RMAN-06136: ORACLE error from auxiliary database: ORA-19502: write error on file "/appl/oradata/xdb01/TestT/TestT_redo04a.rdo", block number 616449 (block size=512)
ORA-27061: waiting for async I/Os failed
Linux-x86_64 Error: 28: No space left on device
Additional information: 4294967295
Additional information: 1048576

Recovery Manager complete.

 check the database for unused log files
oracle@OVM2[TestT]:/appl/oracle/admin/TestT/rman/rman_restore
> sql

SQL*Plus: Release 12.1.0.2.0 Production on Fri Sep 15 13:04:16 2017

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning option

SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC
---------- ---------- ---------- ---------- ---------- ---------- ---
STATUS           FIRST_CHANGE# FIRST_TI NEXT_CHANGE# NEXT_TIM     CON_ID
---------------- ------------- -------- ------------ -------- ----------
         1          1          0  536870912        512          2 YES
UNUSED                       0                     0                   0

         4          1          0  536870912        512          2 YES
CLEARING_CURRENT             0                     0                   0

         3          1          0  536870912        512          2 YES
UNUSED                       0                     0                   0


    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC
---------- ---------- ---------- ---------- ---------- ---------- ---
STATUS           FIRST_CHANGE# FIRST_TI NEXT_CHANGE# NEXT_TIM     CON_ID
---------------- ------------- -------- ------------ -------- ----------
         2          1          0  536870912        512          2 YES
UNUSED                       0                     0                   0
drop that unused logfile

SQL> alter database drop logfile group 1;

Database altered.

SQL> alter database drop logfile group 2 ;

Database altered.

SQL> select member from v$logfile ;

MEMBER
--------------------------------------------------------------------------------
/appl/oradata/xdb02/TestT/TestT_redo04b.rdo
/appl/oradata/xdb01/TestT/TestT_redo04a.rdo
/appl/oradata/xdb01/TestT/TestT_redo03a.rdo
/appl/oradata/xdb02/TestT/TestT_redo03b.rdo

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning option
remove the deleted log files manually

 oracle@OVM2[TestT]:/appl/oracle/admin/TestT/rman/rman_restore
> cd /appl/oradata/xdb02/TestT/
oracle@OVM2[TestT]:/appl/oradata/xdb02/TestT
> ls -lrt
total 1572900
-rw-r--r--. 1 oracle oinstall       842 May 17  2016 drop_tn.sql
-rw-r--r--. 1 oracle oinstall      5094 May 17  2016 drop_tn.log
-rw-r-----. 1 oracle oinstall 536871424 Sep 15 12:52 TestT_redo01b.rdo
-rw-r-----. 1 oracle oinstall 536871424 Sep 15 12:53 TestT_redo02b.rdo
-rw-r-----. 1 oracle oinstall 536871424 Sep 15 12:53 TestT_redo03b.rdo
oracle@OVM2[TestT]:/appl/oradata/xdb02/TestT
> rm TestT_redo01b.rdo TestT_redo02b.rdo
oracle@OVM2[TestT]:/appl/oradata/xdb02/TestT
> cd ..
oracle@OVM2[TestT]:/appl/oradata/xdb02
> cd ..
oracle@OVM2[TestT]:/appl/oradata
> cd xdb01
oracle@OVM2[TestT]:/appl/oradata/xdb01
> cd TestT
oracle@OVM2[TestT]:/appl/oradata/xdb01/TestT
> ls -lrt
total 1594764
-rw-r-----. 1 oracle oinstall 536871424 Sep 15 12:52 TestT_redo01a.rdo
-rw-r-----. 1 oracle oinstall 536871424 Sep 15 12:53 TestT_redo02a.rdo
-rw-r-----. 1 oracle oinstall 536871424 Sep 15 12:53 TestT_redo03a.rdo
-rw-r-----. 1 oracle oinstall  22396928 Sep 15 13:05 TestT_control01.ctl
oracle@OVM2[TestT]:/appl/oradata/xdb01/TestT
> rm TestT_redo01a.rdo TestT_redo02a.rdo
try open resetlogs, you may face errors like this if some logs are  uncleared.


oracle@OVM2[TestT]:/appl/oradata/xdb01/TestT
> rman auxiliary /

Recovery Manager: Release 12.1.0.2.0 - Production on Fri Sep 15 13:05:30 2017

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

connected to auxiliary database: TestT (DBID=1412574424, not open)

RMAN>  Alter clone database open resetlogs;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of alter db command at 09/15/2017 13:05:36
RMAN-06136: ORACLE error from auxiliary database: ORA-00392: log 4 of thread 1 is being cleared, operation not allowed
ORA-00312: online log 4 thread 1: '/appl/oradata/xdb02/TestT/TestT_redo04b.rdo'
ORA-00312: online log 4 thread 1: '/appl/oradata/xdb01/TestT/TestT_redo04a.rdo'

RMAN> exit
clear the redo log 
oracle@OVM2[TestT]:/appl/oradata/xdb01/TestT
> sql

SQL*Plus: Release 12.1.0.2.0 Production on Fri Sep 15 13:05:40 2017

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning option

SQL> alter database clear logfile group 4 ;

Database altered.

SQL> exit

 now try open resetlogs
oracle@OVM2[TestT]:/appl/oradata/xdb01/TestT
> rman auxiliary /

Recovery Manager: Release 12.1.0.2.0 - Production on Fri Sep 15 13:06:15 2017

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

connected to auxiliary database: TestT (DBID=1412574424, not open)

RMAN>  Alter clone database open resetlogs;

database opened

RMAN> exit


Recovery Manager complete.
oracle@OVM2[TestT]:/appl/oradata/xdb01/TestT
> sql

SQL*Plus: Release 12.1.0.2.0 Production on Fri Sep 15 13:06:49 2017

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning option

SQL> select open_mode from v$database ;

OPEN_MODE
--------------------
READ WRITE

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning option

Posted by at No comments:
Labels: ,

Monday, April 17, 2017

ORA-06502: PL/SQL: numeric or value

Sometimes you will see this error while doing duplicate database 

Oracle instance shut down
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 04/18/2017 13:10:23
RMAN-05501: aborting duplication of target database
RMAN-06136: ORACLE error from auxiliary database: ORA-06502: PL/SQL: numeric or value error

Recovery Manager complete.

Then connect to the source database and its catalog database. now unregister the unique name which you are going to re-use. In my scenario, I tried to duplicate the database as Testt13 and it's already exists.

rman target /
connect catalog rman/password@Rmancatalog

Recovery Manager: Release 11.2.0.4.0 - Production on Tue Apr 18 13:35:43 2017

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

connected to target database: TESTPG (DBID=1734399999)
connected to recovery catalog database

RMAN> list db_unique_name of database;


List of Databases
DB Key  DB Name  DB ID            Database Role    Db_unique_name
------- ------- ----------------- ---------------  ------------------
5548648 TESTPG  1734399999       PRIMARY          TESTPG_DG1
5548648 TESTPG  1734399999       STANDBY          TESTT10
5548648 TESTPG  1734399999       STANDBY          TESTT13
5548648 TESTPG  1734399999       STANDBY          TESTPG
5548648 TESTPG  1734399999       STANDBY          TESTSG
5548648 TESTPG  1734399999       STANDBY          TESTPG_DG2

RMAN> resync catalog;

starting full resync of recovery catalog
full resync complete



removing the unwanted unique names


RMAN> unregister db_unique_name 'TESTT13';

database db_unique_name is "TESTT13", db_name is "TESTPG" and DBID is 1734399999

Want to unregister the database with target db_unique_name (enter YES or NO)? YES
database with db_unique_name TESTT13 unregistered from the recovery catalog

RMAN> unregister db_unique_name 'TESTT10';

database db_unique_name is "TESTT10", db_name is "TESTPG" and DBID is 1734399999

Want to unregister the database with target db_unique_name (enter YES or NO)? YES
database with db_unique_name TESTT10 unregistered from the recovery catalog

RMAN> resync catalog;

starting full resync of recovery catalog
full resync complete

RMAN> list db_unique_name of database;


List of Databases
DB Key  DB Name  DB ID            Database Role    Db_unique_name
------- ------- ----------------- ---------------  ------------------
5548648 TESTPG  1734399999       PRIMARY          TESTPG_DG1
5548648 TESTPG  1734399999       STANDBY          TESTPG
5548648 TESTPG  1734399999       STANDBY          TESTSG
5548648 TESTPG  1734399999       STANDBY          TESTPG_DG2

RMAN> exit



Now retry the duplicate it will work
Posted by at No comments:
Labels:

Wednesday, March 29, 2017

Dropping redo log memmbers - oracle 11gR2



SQL> select member from v$logfile;

MEMBER
--------------------------------------------------------------------------------
/appl/oradata/xdb01/CprodD/CprodD_redo03a.rdo
/appl/oradata/xdb02/CprodD/CprodD_redo03b.rdo
/appl/oradata/xdb01/CprodD/CprodD_redo02a.rdo
/appl/oradata/xdb02/CprodD/CprodD_redo02b.rdo
/appl/oradata/xdb01/CprodD/CprodD_redo01a.rdo
/appl/oradata/xdb02/CprodD/CprodD_redo01b.rdo

6 rows selected.

SQL> alter database drop logfile member '/appl/oradata/xdb02/CprodD/CprodD_redo03b.rdo';
alter database drop logfile member '/appl/oradata/xdb02/CprodD/CprodD_redo03b.rdo'
*
ERROR at line 1:
ORA-01609: log 3 is the current log for thread 1 - cannot drop members
ORA-00312: online log 3 thread 1:
'/appl/oradata/xdb01/CprodD/CprodD_redo03a.rdo'
ORA-00312: online log 3 thread 1:
'/appl/oradata/xdb02/CprodD/CprodD_redo03b.rdo'


The above can be deleted after switching the current log file.



SQL>  alter database drop logfile member '/appl/oradata/xdb02/CprodD/CprodD_redo02b.rdo';

Database altered.

SQL> alter database drop logfile member '/appl/oradata/xdb02/CprodD/CprodD_redo01b.rdo';

Database altered.

SQL> alter system checkpoint;

System altered.

SQL> alter system switch logfile;

System altered.

SQL>  alter database drop logfile member '/appl/oradata/xdb02/CprodD/CprodD_redo03b.rdo';

Database altered.

SQL> select member from v$logfile;

MEMBER
--------------------------------------------------------------------------------
/appl/oradata/xdb01/CprodD/CprodD_redo03a.rdo
/appl/oradata/xdb01/CprodD/CprodD_redo02a.rdo
/appl/oradata/xdb01/CprodD/CprodD_redo01a.rdo

SQL>
Posted by at 1 comment:

Wednesday, March 1, 2017

ORACLE error from auxiliary database: ORA-06502: PL/SQL: numeric or value error

RMAN duplicate db fails with ORA-06502

Oracle instance shut down
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 03/02/2017 10:02:21
RMAN-05501: aborting duplication of target database
RMAN-06136: ORACLE error from auxiliary database: ORA-06502: PL/SQL: numeric or value error

RMAN> exit

solution


1. remove db_unique_name parameter from init.ora
    and retry the duplicate. you can set the db_unique_name later once the duplicate complets fine. 

2. cleanup the catalog if required.
    remove the dbunique name related to duplicate work

oracle@oravm1[TestPG]:/var/applogs/oracle/rmanback
> rman target / catalog rman/passs@Rmancatalog

Recovery Manager: Release 11.2.0.4.0 - Production on Thu Mar 2 10:01:55 2017

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

connected to target database: TestPG (DBID=1734336466)
connected to recovery catalog database
re
RMAN> sync catalog;

starting full resync of recovery catalog
full resync complete

RMAN> list incarnation of database;


List of Database Incarnations
DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time
------- ------- -------- ---------------- --- ---------- ----------
5548648 5548649 TestPG  1734336466       CURRENT 1          31-JAN-14
5548648 42491323 TestPG  1734336466       ORPHAN  11928117675339 12-MAY-16

RMAN> exit


Recovery Manager complete.
oracle@oravm1[TestPG]:/var/applogs/oracle/rmanback
> sqlplus rman@Rmancatalog

SQL*Plus: Release 11.2.0.4.0 Production on Thu Mar 2 10:04:11 2017

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

Enter password:

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning option

SQL> select site_key,db_key,database_role,cf_create_time,db_unique_name from rc_site where db_key=5548648;

  SITE_KEY     DB_KEY DATABAS CF_CREAT
---------- ---------- ------- --------
DB_UNIQUE_NAME
--------------------------------------------------------------------------------
   5548650    5548648 PRIMARY 31/01/14
TestPG_DG1

   5560736    5548648 STANDBY 09/03/15
TestPG_DG2

  42491321    5548648 STANDBY 12/05/16
TestPG_DG3


  SITE_KEY     DB_KEY DATABAS CF_CREAT
---------- ---------- ------- --------
DB_UNIQUE_NAME
--------------------------------------------------------------------------------
  40708303    5548648 STANDBY
TESTSG

  46494327    5548648 STANDBY
TESTSG1

  46265758    5548648 STANDBY
TESTSG_DG1_MDR


  SITE_KEY     DB_KEY DATABAS CF_CREAT
---------- ---------- ------- --------
DB_UNIQUE_NAME
--------------------------------------------------------------------------------
  42469535    5548648 STANDBY
TESTT4

  35485732    5548648 STANDBY
TESTT9


8 rows selected.

SQL> set linesize 200
SQL> 1
  1* select site_key,db_key,database_role,cf_create_time,db_unique_name from rc_site where db_key=5548648
SQL> /

  SITE_KEY     DB_KEY DATABAS CF_CREAT DB_UNIQUE_NAME
---------- ---------- ------- -------- ------------------------------------------------------------------------------------------------------------------------
  40708303    5548648 STANDBY          TESTSG
  46494327    5548648 STANDBY          TESTSG1
  35485732    5548648 STANDBY          TESTT9
  46265758    5548648 STANDBY          TESTSG_DG1
  42491321    5548648 STANDBY 12/05/16 TestPG_DG3
   5560736    5548648 STANDBY 09/03/15 TestPG_DG2
  42469535    5548648 STANDBY          TESTT4
   5548650    5548648 PRIMARY 31/01/14 TestPG_DG1

8 rows selected.

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning option
oracle@oravm1[TestPG]:/var/applogs/oracle/rmanback




Remove the db unique names which is not related to source database. These entries are created whenever we duplicate the database and it got failed.
 
> rman target / catalog rman/passs@Rmancatalog

Recovery Manager: Release 11.2.0.4.0 - Production on Thu Mar 2 10:05:18 2017

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

connected to target database: TestPG (DBID=1734336466)
connected to recovery catalog database

RMAN> unregister db_unique_name TESTT4;

database db_unique_name is "TESTT4", db_name is "TestPG" and DBID is 1734336466

Want to unregister the database with target db_unique_name (enter YES or NO)? YES
database with db_unique_name TESTT4 unregistered from the recovery catalog

RMAN> unregister db_unique_name TESTSG;

database db_unique_name is "TESTSG", db_name is "TestPG" and DBID is 1734336466

Want to unregister the database with target db_unique_name (enter YES or NO)? YES
database with db_unique_name TESTSG unregistered from the recovery catalog

RMAN> unregister db_unique_name TESTSG1
2> ;

database db_unique_name is "TESTSG1", db_name is "TestPG" and DBID is 1734336466

Want to unregister the database with target db_unique_name (enter YES or NO)? YES
database with db_unique_name TESTSG1 unregistered from the recovery catalog

RMAN> unregister db_unique_name TESTT9;

database db_unique_name is "TESTT9", db_name is "TestPG" and DBID is 1734336466

Want to unregister the database with target db_unique_name (enter YES or NO)? YES
database with db_unique_name TESTT9 unregistered from the recovery catalog

RMAN> exit


Recovery Manager complete.
oracle@oravm1[TestPG]:/var/applogs/oracle/rmanback
> sqlplus rman@Rmancatalog

SQL*Plus: Release 11.2.0.4.0 Production on Thu Mar 2 10:07:42 2017

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

Enter password:

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning option

SQL>  select site_key,db_key,database_role,cf_create_time,db_unique_name from rc_site where db_key=5548648
  2  /

  SITE_KEY     DB_KEY DATABAS CF_CREAT
---------- ---------- ------- --------
DB_UNIQUE_NAME
--------------------------------------------------------------------------------
   5548650    5548648 PRIMARY 31/01/14
TestPG_DG1_MDR

   5560736    5548648 STANDBY 09/03/15
TestPG_DG2_HTC

  42491321    5548648 STANDBY 12/05/16
TestPG_DG3_MDR


  SITE_KEY     DB_KEY DATABAS CF_CREAT
---------- ---------- ------- --------
DB_UNIQUE_NAME
--------------------------------------------------------------------------------
  46265758    5548648 STANDBY
TESTSG_DG1_MDR


SQL> set linesize 200
SQL> 1
  1*  select site_key,db_key,database_role,cf_create_time,db_unique_name from rc_site where db_key=5548648
SQL> /

  SITE_KEY     DB_KEY DATABAS CF_CREAT DB_UNIQUE_NAME
---------- ---------- ------- -------- ------------------------------------------------------------------------------------------------------------------------
   5548650    5548648 PRIMARY 31/01/14 TestPG_DG1
   5560736    5548648 STANDBY 09/03/15 TestPG_DG2
  42491321    5548648 STANDBY 12/05/16 TestPG_DG3
  46265758    5548648 STANDBY          TESTSG_DG1

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning option
oracle@oravm1[TestPG]:/

Posted by at No comments:
Labels:

Activating physical standby database to primary /normal database

Activating the physical standby database to primary database. 

# recover process is running

SQL> !ps -ef|grep mrp
oracle   28937     1  0 Feb24 ?        00:00:38 ora_mrp0_TestSG
oracle   49454 49444  0 09:02 pts/0    00:00:00 /bin/ksh -c ps -ef|grep mrp
oracle   49456 49454  0 09:02 pts/0    00:00:00 grep mrp
# cancelling the recover process
SQL> alter database recover managed standby database cancel;

Database altered.

#checking database status


SQL> select name,database_role from v$database;

NAME      DATABASE_ROLE
--------- ----------------
TESTSG   PHYSICAL STANDBY

# completing the recovery

SQL> alter database recover managed standby database finish;

Database altered.

#checking the database role and activating the database


SQL> select name,database_role from v$database;

NAME      DATABASE_ROLE
--------- ----------------
TESTSG   PHYSICAL STANDBY

SQL> alter database activate physical standby database;

Database altered.

SQL> select name,database_role from v$database;

NAME      DATABASE_ROLE
--------- ----------------
TESTSG   PRIMARY

SQL> alter database open ;

Database altered.

#checking the database mode


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

NAME      DATABASE_ROLE    OPEN_MODE
--------- ---------------- --------------------
TESTSG   PRIMARY          READ WRITE
Posted by at No comments:
Labels:
Subscribe to: Posts (Atom)