ORA-01031: insufficient privileges to sys@standby as sysdba

The ORA-01031 is very generic error and normally users will receive this error when users don’t have appropriate privilege.

$oerr ora 01031
01031, 00000, "insufficient privileges"
// *Cause: An attempt was made to change the current username or password
//         without the appropriate privilege. This error also occurs if
//         attempting to install a database without the necessary operating
//         system privileges.
//         When Trusted Oracle is configure in DBMS MAC, this error may occur
//         if the user was granted the necessary privilege at a higher label
//         than the current login.
// *Action: Ask the database administrator to perform the operation or grant
//          the required privileges.
//          For Trusted Oracle users getting this error although granted the
//          the appropriate privilege at a higher label, ask the database
//          administrator to regrant the privilege at the appropriate label.

On standby
$ sqlplus
Enter user-name: /as sysdba

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>


From Primary or Standby
$ sqlplus
 
Enter user-name: sys@standby as sysdba
Enter password:
ERROR:
ORA-01031: insufficient privileges


Error in alert log of PRIMARY database

Error 1034 received logging on to the standby
PING[ARC0]: Heartbeat failed to connect to standby 'STANDBY'. Error is 1034.
Error 1034 received logging on to the standby
FAL[server, ARC2]: Error 1034 creating remote archivelog file 'STANDBY'

 Metalink notes for insufficient privileges  as sysdba
OERR: ORA 1031 "insufficient privileges" [ID 18622.1]
Troubleshooting ORA-1031: Insufficient Privileges While Connecting As SYSDBA [ID 730067.1]

But

Original problem in my case  was with listener file. Instance name is crated in small letter but in listener.ora sid given in CAPS it caused the problem. After changing sid in listner.ora it is working fine.

Read more

Creating Redo Log Groups and Members

Create all required groups and members of redo log files during database creation. However, there are situations where you might want to create additional groups or members.

Creating Redo Log Groups

To create new redo log groups and members, you must have the ALTER DATABASE system privilege.

To create a new group of redo log files, use the SQL statement ALTER DATABASE with the ADD LOGFILE clause.

The following statement adds a new group of redo logs to the database:

 ALTER DATABASE

  ADD LOGFILE ('/oracle/dbs/log1c.rdo', '/oracle/dbs/log2c.rdo') SIZE 500K;

You can also specify the number that identifies the group using the GROUP clause:

ALTER DATABASE

  ADD LOGFILE GROUP 10 ('/oracle/dbs/log1c.rdo', '/oracle/dbs/log2c.rdo')

      SIZE 500K;

Using group numbers can make administering redo log groups easier. However, the group number must be between 1 and MAXLOGFILES.

In some cases, it might not be necessary to create a complete group of redo log files. A group could already exist, but not be complete because one or more members of the group were dropped (for example, because of a disk failure). In this case, you can add new members to an existing group.

Creating Redo Log Members

To create new redo log members for an existing group, use the SQL statement ALTER DATABASE with the ADD LOGFILE MEMBER clause.

The following statement adds a new redo log member to redo log group number 2:

ALTER DATABASE ADD LOGFILE MEMBER '/oracle/dbs/log2b.rdo' TO GROUP 2;

Notice that filenames must be specified, but sizes need not be. The size of the new members is determined from the size of the existing members of the group.

When using the ALTER DATABASE statement, you can alternatively identify the target group by specifying all of the other members of the group in the TO clause, as shown in the following example:

ALTER DATABASE ADD LOGFILE MEMBER '/oracle/dbs/log2c.rdo'

    TO ('/oracle/dbs/log2a.rdo', '/oracle/dbs/log2b.rdo');

Note:

Fully specify the filenames of new log members to indicate where the operating system files should be created. Otherwise, the files will be created in either the default or current directory of the database server, depending upon your operating system. You may also note that the status of the new log member is shown as INVALID. This is normal and it will change to active (blank) when it is first used.

Read more

Multiplex & Move Oracle control files to different directory

1, shutdown database.

shutdown immediate;

2, edit Oracle init.ora file, e.g., C:\Oracle\10g\database\inittest.ora, find the line with control file location, change the location from old directory to new directory, e.g.:

         OLD:
         control_files=("H:\Oradata\test\control01.ctl")

         NEW:
         control_files=("I:\Oradata\test\control01.ctl",  "J:\Oradata\test\control02.ctl", "K:\Oradata\test\control03.ctl")

save init.ora file.

3, create spfile from pfile:

create spfile from pfile;

4, Copy control files from old directory to new directory

    copy H:\Oradata\test\control01.ctl I:\Oradata\test\control01.ctl
    copy H:\Oradata\test\control01.ctl J:\Oradata\test\ control02.ctl
    copy H:\Oradata\test\control01.ctl K:\Oradata\test\control03.ctl

5, startup database.

SQL> startup
ORACLE instance started.
Total System Global Area 1426063360 bytes
Fixed Size 2004264 bytes
Variable Size 352324312 bytes
Database Buffers 1056964608 bytes
Redo Buffers 14770176 bytes
Database mounted.
Database opened.
SQL>

Read more

Automate AWR Report Generation

By default snapshots of the relevant data are taken every hour and retained for 7 days. Most of the times database running in default AWR settings. DBA's generates the awr report when something  happened in their database or periodically to check if anything is wrong in databases

Some times databases are running smooth without any problems. Due to the default retention of AWR snapshot setting we may loose some good information about database.When problem occurs in the database we may need to compare the bad time report (when problem occurred) vs good time report (when db running smooth). In this case we don't have any information about database when running without any problem or we don't have same period snapshot AWR report to compare it with current reports.

To avoid this situation DBA can automate the AWR reports generation using below script.
It automatically generates the awr report of last one hour and no manual intervention is required by DBAs onece it is schedule in cronjob

Crontab Entry

05 * * * * sh /oracle/AWR/generate_awr.sh ORCL  >> /oracle/AWR/awr_rpt.log

Cronjob to be executed every hour at 05 mins on daily basis  and shell output is redirected to log file

SCRIPT STARTS HERE
##############################################################################
1 ) generate_awr.sh

#------------------------------------------------------------------------------
#!/usr/bin/ksh
# File: generate_awr.sh
# Description:
# shell script to run under the UNIX "cron" utility to automatically generate Oracle "AWR" reports in HTML   against the database accessed via passed parameter
# Usage :
# sh generate_awr.sh oracle_sid
# Parameters:
#  One parameter to be  passed. Oracle_sid to be passed as parameter.
#  Environment variables are captured from /etc/oratab entry.

dbSid=${1}

export oraSid=${dbSid}
#echo $oraSid
export ORACLE_SID=`grep "$oraSid:" /etc/oratab|cut -d: -f1 -s`
export ORACLE_HOME=`grep "$oraSid:" /etc/oratab|cut -d: -f2 -s`
export ORAENV_ASK=NO
#. oraenv ${dbSid}
unset TWO_TASK
export PATH=$PATH:$ORACLE_HOME/bin

#echo $ORACLE_SID
#echo $ORACLE_HOME
#echo $PATH

sqlplus -S " / as sysdba" @/oracle/AWR/create_awr_report_for_database.sql

# Finish
#------------------------------------------------------------------------------

2) create_awr_report_for_database.sql

/* ---------------------------------------------------------------------------
Original script byhttp://damir-vadas.blogspot.in/2009/11/automated-awr-reports-in-oracle-10g11g.html

 Filename: create_awr_report_for_database.sql
 Purpose : In directory defined with v_dir, create awr reports for last one hour snapshots, so put in crontab  to run at every hour on daily basis

 Remarks : Run as privileged user

#  If No report generated in report folder (v_dir) create directory manually once before running the script.
 --------------------------------------------------------------------------- */
set serveroutput on
set linesize 166
set pagesize 600
set trimout on

DECLARE
   CURSOR c_instance
   IS
        SELECT   instance_number, instance_name
          FROM   gv$instance
      ORDER BY   1;

   c_dir CONSTANT   VARCHAR2 (256) := 'oracle/AWR';
   v_dir            VARCHAR2 (256) := 'oracle/AWR/daily_awr';
   v_dbid           v$database.dbid%TYPE;
   v_dbname         v$database.name%TYPE;
   v_inst_num       v$instance.instance_number%TYPE := 1;
   v_begin          NUMBER;
   v_end            NUMBER;
   v_start_date     VARCHAR2 (20);
   v_end_date       VARCHAR2 (20);
   v_options        NUMBER := 8;        -- 0=no options, 8=enable addm feature
   v_file           UTL_FILE.file_type;
   v_file_name      VARCHAR (50);
BEGIN
   -- get database id
   SELECT   dbid, name
     INTO   v_dbid, v_dbname
     FROM   v$database;

   -- get end snapshot id
   SELECT   MAX (snap_id)
     INTO   v_end
     FROM   dba_hist_snapshot
     WHERE  TO_CHAR (END_INTERVAL_TIME, 'yyyy/mm/dd hh24') <= to_char(sysdate,'yyyy/mm/dd hh24');

   DBMS_OUTPUT.put_line ('end snap_id ' || v_end);

   -- get start snapshot id
   SELECT   MAX (snap_id)
     INTO   v_begin
     FROM   dba_hist_snapshot
    WHERE   snap_id < v_end;

   DBMS_OUTPUT.put_line ('begin snap_id ' || v_begin);

   SELECT   TO_CHAR (END_INTERVAL_TIME, 'YYMMDD_HH24MI')
     INTO   v_start_date
     FROM   dba_hist_snapshot
    WHERE   snap_id = v_begin AND instance_number = v_inst_num;

   DBMS_OUTPUT.put_line ('v_start_date ' || v_start_date);

   SELECT   TO_CHAR (END_INTERVAL_TIME, 'HH24MI')
     INTO   v_end_date
     FROM   dba_hist_snapshot
    WHERE   snap_id = v_end AND instance_number = v_inst_num;

   DBMS_OUTPUT.put_line ('v_end_date ' || v_end_date);

   -- Thanx to Yu Denis Sun - we must have directory defined as v_dir value!
execute immediate('create or replace directory awrdir as '''||v_dir||'''');

   -- let's go to real work...write awrs to files...
   FOR v_instance IN c_instance
   LOOP
      DBMS_OUTPUT.put_line (
         'v_instance.instance_name:' || v_instance.instance_name
      );
      v_file :=
         UTL_FILE.fopen (
            'AWRDIR',
               'awr_'
            || v_instance.instance_name
            || '_'
            || v_instance.instance_number
            || '_'
            || v_start_date
            || '_'
            || v_end_date
            || '.html',
            'w',
            32767
         );

      FOR c_report
      IN (SELECT   output
            FROM   TABLE(DBMS_WORKLOAD_REPOSITORY.awr_report_html (
                            v_dbid,
                            v_instance.instance_number,
                            v_begin,
                            v_end,
                            v_options
                         )))
      LOOP
         UTL_FILE.PUT_LINE (v_file, c_report.output);
      END LOOP;

      UTL_FILE.fclose (v_file);
   END LOOP;

EXECUTE IMMEDIATE ('drop directory AWRDIR');
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.PUT_LINE (SQLERRM);

      IF UTL_FILE.is_open (v_file)
      THEN
         UTL_FILE.fclose (v_file);
      END IF;

      BEGIN
    EXECUTE IMMEDIATE ('drop directory AWRDIR');
      EXCEPTION
         WHEN OTHERS
         THEN
            NULL;
      END;
END;
/
exit;

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

Read more

Restoring an RMAN Backup to Another Node /Clone Database

In certain circumstances, it may be desirable to restore a database from an RMAN backup onto a machine other than the original host. 
For example,Restore and recover data at a given point in time for UAT/Testing, or to duplicate a production instance.
The example assumes:
the target database is on host A
the database is to be restored onto host B
the directory structure of host B is different to host A
the ORACLE_SID will not change for the restored database
a recovery catalog is being used
the backups were carried out to disk (for illustrative purposes, and to disassociate from any media manager specific issues)

The following steps are required:
1. backup the target on host A
2. list the datafile locations on host A
3. make the backup available to host B
4. make a copy of the init.ora available to host B
5. edit the init.ora to reflect directory structure changes
6. configure SQL*Net connectivity from host to the recovery catalog and duplicated database
7. set up a password file for the duplicated database
8. startup nomount the duplicated database
9. RMAN restore the controlfile(s)
10. mount the database
11. restore and rename the datafiles
12. recover and open the database
These steps are expanded further below.

1.Backup the Target on Host A
The target database needs to be backed up using RMAN. The following  commands of RMAN doing an online database backup. In this example, the backup sets are written to disk.

RMAN>
run {
allocate channel t1 type disk;
allocate channel t2 type disk;
allocate channel t3 type disk;
#backup the whole db
backup tag whole_database_open format '/oracle/backups/df_%u' database;
# switch out of the current logfile
sql 'alter system archive log current';
#backup the archived logs
backup archivelog all format '/oracle/backups/al_%u';
# backup a copy of the controlfile that contains records for the other backups just made
backup current controlfile tag = cf1 format '/oracle/backups/cf_%u';
}

2.List Datafile Locations on Host A
The datafile numbers and location on host A are required. These datafile locations will change on host B 
select file#, name from v$datafile;
file#   name
-----   -----------------------------------------------------------
1       /oracle/OFA_base/u01/oradata/TEST/system01.dbf
2       /oracle/OFA_base/u01/oradata/TEST/rbs01.dbf
3       /oracle/OFA_base/u01/oradata/TEST/temp01.dbf
4       /oracle/OFA_base/u01/oradata/TEST/tools01.dbf
5       /oracle/OFA_base/u01/oradata/TEST/users01.dbf
6       /oracle/OFA_base/u01/oradata/TEST/users02.dbf
7       /oracle/OFA_base/u01/oradata/TEST/rbs02.dbf
8       /oracle/OFA_base/u01/oradata/TEST/rcvcat.dbf

The log file names should also be recorded
select group#, member from v$logfile;
group#    member
---------   -----------------------------------------------------------
1            /oracle/OFA_base/u01/oradata/TEST/redo01.log
2            /oracle/OFA_base/u01/oradata/TEST/redo02.log
3            /oracle/OFA_base/u01/oradata/TEST/redo03.log

3 Make the Backups Available to Host B
3.1 Disk Backups
During restore, RMAN will expect the backup sets to be located in the same directory as written to during the backup. For disk backups, the DBA can accomplish this in many ways: set up an NFS directory, mounted on both host A and host B create the same directory structure on host A and host B, use of symbolic links on host B,FTP backup to host B.
If backup directory is not same at HOST B then catalog backup files using RMAN.
3.2 Tape Backups
The media management software must be configured such that host B is a media manager client, and can read the backup sets. The media management vendor should be consulted for support on this issue.

4. and 5. init.ora on host B
The "init.ora" needs to be made available on host B. Any location specific parameters must be ammended. For example,
*_dump_dest
log_archive_dest*
control_files

6. SQL*Net configuration
If running rman from host A:
a. connectivity to the catalog remains unchanged
b. configure tnsnames.ora on host A to connect to duplicated db on host B configure listener.ora on host B to accept connections for duplicated database
If running rman from host B:
a. configure tnsnames.ora on host B to connect to catalog listener.ora on catalog host remains unchanged
b. configure tnsnames.ora on host B to connect to duplicated db on host B configure listener.ora on host B to accept connections for duplicated database
If running rman from host C (ie, neither host A or host B):
a. connectivity to the catalog remains unchanged
b. configure tnsnames.ora on host C to connect to duplicated db on host B configure listener.ora on host B to accept connections for duplicated database

7. Setup Password File
In order to allow RMAN remote connections, a password file must be setup for the duplicated database. 
orapwd file=$ORACLE_HOME/dbs/orapw$ORACLE_SID password=password1

8. Startup nomount the database
SVRMGR> startup nomount pfile=<location of init.ora>

9. RMAN restore the controlfile(s)
Restore the controlfile(s). 
RMAN>
run{
allocate channel c1 type disk;
restore controlfile;
}

10. Mount the database
SVRMGR> alter database mount;

11. Rename and restore the datafiles
Rename and Restore the Files, and perform database recovery. RMAN can be used to change the location of the datafiles from the location on host A (see Section 2) to the new location on host B.
RMAN>
run {
allocate channel c1 type disk;
allocate channel c2 type disk;
allocate channel c3 type disk;
set newname for datafile 1 to '/oracle/datafiles/system01.dbf';
set newname for datafile 2 to '/oracle/datafiles/rbs01.dbf';
set newname for datafile 3 to '/oracle/datafiles/temp01.dbf';
set newname for datafile 4 to '/oracle/datafiles/tools01.dbf';
set newname for datafile 5 to '/oracle/datafiles/users01.dbf';
set newname for datafile 6 to '/oracle/datafiles/users02.dbf';
set newname for datafile 7 to '/oracle/datafiles/rbs02.dbf';
set newname for datafile 8 to '/oracle/datafiles/rcvcat.dbf';
restore database;
switch datafile all;
}


12. Recover and open the database
Perform incomplete recovery:
SVRMGR> recover database using backup controlfile until cancel;
Forward the database applying archived redo log files until you decide to stop recovery by typing cancel at the prompt (assuming that you have required archived redo log files in the log_archive_dest directory). You may archive the source database redo log files and apply them at the target database if required.
SVRMGR> alter database open resetlogs;
Note: this will create the online redo logs in the same location as that on host A. If this directory location does not exist, then this will fail with:
ora344 : unable to recreate online log <name>
The workaround is to rename the logfiles prior to opening the database:
SVRMGR> alter database rename file '<host A location>' to '<host B location>';
Alternatively, the logfile groups can be dropped and recreated. 

Read more

Full Database Recovery or Disaster Recovery.

You use the RESTORE and RECOVER commands to restore and recover the database.RMAN checks last good backup set and restore the datafiles to the state they were in when that backup set was created. When restoring database files with RMAN, it reads the datafile header and makes the determination as to whether the file needs to be restored.
 
If you cannot restore datafiles to their default locations, then you must update the control file to reflect the new locations of the datafiles. Use the RMAN SET NEWNAME command within a RUN command to specify the new filename. Afterward, use a SWITCH command, which is equivalent to using the SQL statement ALTER DATABASE RENAME FILE, to update the names of the datafiles in the control file. SWITCH DATAFILE ALL updates the control file to reflect the new names for all datafiles for which a SET NEWNAME has been issued in a RUN command.(Restore files to another location will  be demonstrated in another post)

The recovery is done by allocating a channel for I/O and then issuing the RMAN restore database command. The database must be in MOUNT state when restoring or recovering the entire database.

SQL> startup mount;
   or
RMAN> startup force mount;

Then connect to rman and perform:
RMAN> restore database;
RMAN> recover database;
RMAN> alter database open;

Another option with controlfile restoration
SET DBID <database_id>; # use database id from RMAN, not required if using recovery catalog
CONNECT TARGET <target_connect_string>;
STARTUP NOMOUNT;
RUN
{
# You need to allocate channels if not using recovery catalog.
ALLOCATE CHANNEL CH1 TYPE DISK;
# Optionally you can use SET NEWNAME and SWITCH commands to restore datafiles to a new location.
RESTORE CONTROLFILE FROM AUTOBACKUP;
ALTER DATABASE MOUNT;
RESTORE DATABASE;
RECOVER DATABASE;
ALTER DATABASE OPEN RESETLOGS;

Another Method: Restore Spfile and Controlfile
1 Assuming that we have a full backup under /bkp
2 Start RMAN
$ rman target / nocatalog
3 Start the DB (nomount) and restore the pfile
RMAN> set DBID=248804095;
startup nomount;
run
{
restore spfile to pfile '/oracle/product/10.2.0/dbs/initTEST.ora'  from'/bkp/c2488040952009013004';
shutdown immediate;
}
4 Start the DB with the pfile that we just restore:
RMAN> set DBID=248804095;
RMAN> startup nomount pfile = '/oracle/product/10.2.0/dbs/initTEST.ora';
5 Restore the control files
run
{
restore controlfile from '/bkp/c2488040952009013004';
alter database mount;
}
6 Restore the data files
run
{
restore database;
recover database;
}
7 Start the DB
RMAN> alter database open resetlogs;

Use RESETLOGS after a point in time recovery  or recover a database using a backup of the control file. RESETLOGS will initialize the logs, reset your log sequence number, and start a new "incarnation" of the database. 

During the recovery, RMAN automatically restores backups of any needed archived redo logs. If  RMAN restores archived redo logs to the flash recovery area during a recovery, then it automatically deletes the restored logs after applying them to the datafiles. Otherwise, you can use the DELETE ARCHIVELOG command to delete restored archived redo logs from disk when they are no longer needed for recovery. For example, you can enter the following command: RECOVER DATABASE DELETE ARCHIVELOG;

Read more

Open Physical Standby For Read Write Testing and Flashback

Open the Standby database in read write mode for any reporting or testing and then move it back to standby database using the flashback technology.

Using a combination of Data Guard, restore points, and Flashback Database, a physical standby database can be opened temporarily in read/write mode for development, reporting, or testing purposes, and then flashed back to a point in the past to be reverted back to a physical standby database. When the database is flashed back, Data Guard automatically synchronizes the standby database with the primary database, without the need to re-create the physical standby database from a backup copy of the primary database.

Perform the following steps to activate the physical standby database as a production database and later resynchronize it with the primary database.

Step 1 - In Standby database

A) Set up a flash recovery area. 

If Flash Recovery Area ( FRA ) is not configured in the standby then enable it and make sure to give enough space for to FRA

SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE=5G; 

SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST='/u01/oracle/flashback';

B) Cancel Redo Apply and create a guaranteed restore point.

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;  

SQL> CREATE RESTORE POINT Standby_flashback_testing GUARANTEE FLASHBACK DATABASE; 

To Confirm the details of restore point and its scn and time stamp run

SQL> select NAME,SCN,TIME from v$restore_point; 

NAME                          SCN              TIME 

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

STANDBY_FLASHBACK_TESTING     22607810         12-APR-09 01.10.21.000000000 P

Step 2 - In Primary Database

A) On the primary database, switch logs so the SCN of the restore point will be archived on the physical standby database. When using standby redo log files, this step is essential to ensure the database can be properly flashed back to the restore point.

SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;

B) Defer log archive destinations pointing to the standby that will be activated.

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=DEFER;

Step 3 - In Standby database

A) Activate the physical standby database:

SQL> ALTER DATABASE ACTIVATE STANDBY DATABASE;

Once its done you can check the cotnrolfile status will be changed from Stnadby to Current

SQL> select CONTROLFILE_TYPE from v$database; 

CONTROL 

------- 

CURRENT

B) Then open the database.

SQL> ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE; 

SQL> ALTER DATABASE OPEN;

Step 4 - In Standby database

Once the standby database has been activated, you can run reporting tools or perform other testing and activities for days or even weeks, independent of the primary database

NOTE—

Any results stored in the activated database will be lost when you later flash back the database. Results that should be saved must be copied out of the activated database before flashing it back.

For example :

SQL> create table testing ( col1 varchar2 (100)); 

Table created. 

SQL> insert into testing values ( 'testing for flashback on standby database'); 

1 row created. 

SQL> commit; 

Commit complete.

Revert the active standby database back to Physical standby database 

Step 1 - In standby database

A1. Mount the database.

A2. Flashback the database to restore point.

SQL> STARTUP MOUNT FORCE;

ORACLE instance started. 

Total System Global Area  289406976 bytes 

Fixed Size                  1290208 bytes 

Variable Size             159383584 bytes 

Database Buffers          125829120 bytes 

Redo Buffers                2904064 bytes 

Database mounted. 

SQL> FLASHBACK DATABASE TO RESTORE POINT Standby_flashback_testing ;

You can confirm the same by checking the controlfile status. It will be now backup controlfile

SQL> select controlfile_type from v$database; 

CONTROL 

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

BACKUP

B) Convert to Standby database

SQL> ALTER DATABASE CONVERT TO PHYSICAL STANDBY;  

SQL> STARTUP MOUNT FORCE;  

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT; 

SQL> select controlfile_type from v$database;

CONTROL 

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

STANDBY

Step 2 - In standby database 

A) Put the standby database in managed recovery mode.Let archive gap resolution fetch all missing archived redo log files and allow Redo Apply to apply the gap. 

Step 3 - In Primary database 

A) Re-enable archiving to the physical standby database:

SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;  

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE; 

Step 4 - In Standby database 

A) Open the database in Read only mode and ensure that all the transaction done in active mode are no more

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;  

SQL> ALTER DATABASE OPEN READ ONLY;  

SQL> select * from testing;  

select * from testing  

*  

ERROR at line 1:  

ORA-00942: table or view does not exist 

B) Drop the restore point

SQL> STARTUP FORCE MOUNT;  

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;  

SQL> DROP RESTORE POINT Standby_flashback_testing ; 

Caution: 

While the database is activated, it is not receiving redo data from the primary database and cannot provide disaster protection. It is recommended that there be at least two physical standby databases participating in the configuration so that the primary database remains protected against data loss. 

Read more

Disable a table Constraints

ORA-02297: cannot disable constraint -dependencies exist

Whenever you try to disable a constraint of a table it fails with error message ORA-02297: cannot disable constraint -dependencies exist as below.

SQL> alter table transaction disable constraint TRANSACTION_PK;
alter table transaction disable constraint TRANSACTION_PK
*
ERROR at line 1:
ORA-02297: cannot disable constraint (OMS.TRANSACTION_PK) - dependencies exist

Cause :
Disable constraint command fails as the table is parent table and it has foreign key that are dependent on this constraint.

Solution :
Two solutions exist for this problem.

1)Find foreign key constraints on the table and disable those foreign key constraints and then disable this table constraint.

Following query will check dependent table and the dependent constraint name. After that disable child first and then parent constraint.

SQL> SELECT p.table_name "Parent Table", c.table_name "Child Table",
       p.constraint_name "Parent Constraint", c.constraint_name "Child Constraint"
       FROM user_constraints p
       JOIN user_constraints c ON(p.constraint_name=c.r_constraint_name)
       WHERE (p.constraint_type = 'P' OR p.constraint_type = 'U')
       AND c.constraint_type = 'R'
       AND p.table_name = UPPER('&table_name');
Enter value for table_name: transaction
old   7:      AND p.table_name = UPPER('&table_name')
new   7:      AND p.table_name = UPPER('transaction')

Parent Table                   Child Table                    Parent Constraint            Child Constraint
----------------------------   --------------------------     -------------------------    ---------------------
TRANSACTION                    USER_SALARY_RECORD             TRANSACTION_PK               SYS_C005564
TRANSACTION                    TRANSACTION_DETAIL             TRANSACTION_PK               TRNSCTN_DTL_TRNSCTN_FK

SQL> alter table USER_SALARY_RECORD disable constraint SYS_C005564;
Table altered.

SQL> alter table TRANSACTION_DETAIL  disable constraint TRNSCTN_DTL_TRNSCTN_FK;
Table altered.

SQL> alter table transaction disable constraint TRANSACTION_PK;
Table altered.


2)Disable the constraint with cascade option.

SQL> alter table transaction disable constraint TRANSACTION_PK cascade;
Table altered.

Read more