Disable Active Dataguard in 11g

-
Need to modify hidden parameter "_query_on_physical" to false to disable active dataguard.

Query to check parameter value:

SET PAUSE ON
SET PAUSE 'Press Return to Continue'
SET PAGESIZE 60
SET LINESIZE 300

COLUMN ksppinm FORMAT A50
COLUMN ksppstvl FORMAT A50

SELECT
  ksppinm,
  ksppstvl
FROM
  x$ksppi a,
  x$ksppsv b
WHERE
  a.indx=b.indx
AND
  substr(ksppinm,1,1) = '_' and a.ksppinm like '_%physical%';
ORDER BY ksppinm
/

KSPPINM                                           ,KSPPSTVL
--------------------------------------------------,--------------------------------------------------
_query_on_physical                                ,TRUE


SQL> alter system set "_query_on_physical"=False scope=spfile;
SQL>
SQL>
SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup;
ORACLE instance started.

Total System Global Area, 422670336,bytes
Fixed Size              ,   1336960,bytes
Variable Size           , 352323968,bytes
Database Buffers        ,  62914560,bytes
Redo Buffers            ,   6094848,bytes
Database mounted.
Database opened.
SQL> select name,open_mode from v$database;

NAME     ,OPEN_MODE
---------,----------------
DG1      ,READ ONLY
SQL>
SQL>

SQL> COLUMN ksppinm FORMAT A50
COLUMN ksppstvl FORMAT A50

SELECT
  ksppinm,
  ksppstvl
FROM
  x$ksppi a,
  x$ksppsv b
WHERE
  a.indx=b.indx
AND
  substr(ksppinm,1,1) = '_' and a.ksppinm like '_%physical%';
ORDER BY ksppinm


KSPPINM                                           ,KSPPSTVL
--------------------------------------------------,--------------------------------------------------
_query_on_physical                                ,FALSE

.
SQL> select DEST_ID,dest_name,status,type,srl,recovery_mode from v$archive_dest_status where dest_id=1;

   DEST_ID,DEST_NAME                           ,STATUS   ,TYPE          ,SRL,RECOVERY_MODE
----------,------------------------------------,---------,--------------,---,-----------------------
         1,LOG_ARCHIVE_DEST_1                  ,VALID    ,LOCAL         ,NO ,IDLE


SQL> alter database recover managed standby database disconnect;


SQL> select DEST_ID,dest_name,status,type,srl,recovery_mode from v$archive_dest_status where dest_id=1;

   DEST_ID,DEST_NAME                               ,STATUS   ,TYPE          ,SRL,RECOVERY_MODE
----------,----------------------------------------,---------,--------------,---,-----------------------
         1,LOG_ARCHIVE_DEST_1                      ,VALID    ,LOCAL         ,NO ,MANAGED


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

NAME     ,OPEN_MODE       ,DATABASE_ROLE
---------,----------------,----------------
DG1      ,MOUNTED         ,PHYSICAL STANDBY

SQL> alter database recover managed standby database cancel;


SQL>  select DEST_ID,dest_name,status,type,srl,recovery_mode from v$archive_dest_status where dest_id=1;

   DEST_ID,DEST_NAME                      ,STATUS   ,TYPE          ,SRL,RECOVERY_MODE
----------,-------------------------------,---------,--------------,---,-----------------------
         1,LOG_ARCHIVE_DEST_1             ,VALID    ,LOCAL         ,NO ,IDLE



SQL> alter database recover managed standby database using current logfile disconnect from session;

SQL> select DEST_ID,dest_name,status,type,srl,recovery_mode from v$archive_dest_status where dest_id=1;

   DEST_ID,DEST_NAME                      ,STATUS   ,TYPE          ,SRL,RECOVERY_MODE
----------,-------------------------------,---------,--------------,---,-----------------------
         1,LOG_ARCHIVE_DEST_1             ,VALID    ,LOCAL         ,NO ,MANAGED REAL TIME APPLY
SQL>

Note: Green highlighted to check difference between normal apply and real time apply.







Comments

Post a Comment

Popular posts from this blog

Profile password verify function issue - From root

-
alter profile user limit password_verify_function verify_fun; Here we altered profile password_verify_function to limit verify_fun, but it has not reflected in profile limit. We got below output. Select * from dba_profiles where resource_name=’PASSWORD_VERIFY_FUNCTION’; PROFILE              RESOURCE_NAME             RESOURCE        LIMIT           COM  --------             -------------------------  -------------- ---------------- ----  DEFAULT              PASSWORD_VERIFY_FUNCTION   PASSWORD        NULL            NO  USERS                PASSWORD_VERIFY_FUNCTION   PASSWORD         FROM ROOT       YES  select * from profname$;  PROFILE#   NAME                     FLAGS  ---------- ------------------------- ----------  0         DEFAULT                   0  1         USERS                      1  Update flag values to 0. update profname$ set flags=0;   commit; select * from profname$;  PROFILE#   NAME                      FLAGS  ---------- ------------------------ ----------  0      
Read more

Change character set of Oracle database and using CSSAN and DMU

-
Change character set of Oracle database: shutdown the db: 1.shutdown immediate database start in restrict mode 2.startup restrict change the character set internal use 3.Alter database character set internal_use AL32UTF8; change the character set 4.Alter database character set AL32UTF8; bounce the database 5.shutdown immediate; 6. startup;  SELECT value$ FROM sys.props$ WHERE name = 'NLS_CHARACTERSET'; ===================== Change character set from WE8MSWIN1252 to AL32UTF8: SQL> SELECT value$ FROM sys.props$ WHERE name = 'NLS_CHARACTERSET'; VALUE$ --------------------------------------------------------------------------- WE8MSWIN1252 SQL> SELECT value$ FROM sys.props$ WHERE name = 'NLS_NCHAR_CHARACTERSET'; VALUE$ --------------------------------------------------------------------------- AL16UTF16 SQL> exit Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and
Read more

Upload patch to OEM (12c/13c)

-
Image
EM 12c, EM 13c: How to upload a patch manually (Doc ID 1931686.1): Manually uploaded patches are shown in the Enterprise >> Provisioning and Patching >> Saved Patches page. This is also where you upload patches (from Desktop) First download the patch either from   Offline  - via a computer with a browser connected to the internet via  https://support.oracle.com  , search for the patch and then download to this computer locally. If this computer does not have access to the OMS then files needs to be transfered to a computer that does has access. or Online  - via the Enterprise Manager Cloud Console.  Go to Enterprise >> Provisioning and Patching >> Patches and Updates, search for the patch and download desktop (locally) OR download to Software Library The patch file, the format is p<patch_number>_<version>_<platform>.zip The patch metadata file, the format is p<patch_number>_<version>.American There is also the option to download the
Read more