Error 1017 received logging on to the standby

If you are working in a standby environment this is a quite common error that you will see in the alert log. There are various situations when you will see this error.Although you have a latest copy of password file copied in both PRIMARY and STANDBY DB, there is a chance that you will see this error. This is a generic error that comes to oracles's mind and warns you about it.

Fetching gap sequence in thread 1, gap sequence 44212-44255
Error 1017 received logging on to the standby

------------------------------------------------------------
Check that the primary and standby are using a password file
and remote_login_passwordfile is set to SHARED or EXCLUSIVE,
and that the SYS password is same in the password files.
      returning error ORA-16191

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

FAL[client, USER]: Error 16191 connecting to ORCL for fetching gap sequence
Archived Log entry 5 added for thread 1 sequence 44257 rlc 839361298 ID 0xa5d555eb dest 2:
Tue Jan 19 12:03:39 2013
Archived Log entry 6 added for thread 1 sequence 44260 rlc 839361298 ID 0xa5d555eb dest 2:
Tue Jan 19 12:03:41 2013
Archived Log entry 7 added for thread 1 sequence 44259 rlc 839361298 ID 0xa5d555eb dest 2:
Tue Jan 19 12:03:42 2013
RFS[5]: Opened log for thread 1 sequence 44261 dbid -1517523940 branch 839361298
Tue Jan 19 12:03:43 2013
Error 1017 received logging on to the standby

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

Check that the primary and standby are using a password file
and remote_login_passwordfile is set to SHARED or EXCLUSIVE,
and that the SYS password is same in the password files.
      returning error ORA-16191

SOLUTION :

1. Copy the latest copy of PASSWORD file from PRIMARY instance to STANDBY instance and replace it with the old one. 

Usually password file is in format of  ORAPWDINSTANCENAME

example : ORAPWDORCL (where ORCL is my instance name).

2. Check if the PASSWORD FILE is set to either EXCLUSIVE mode or SHARED.

3. Test if you can connect to both PRIM and STANDBY as SYSDBA using password.

Note :

IF you don't know the password for SYS, you can recreate password file using this link.

How to create new password file.

Create ORAPWD ORACLE PASSWORD FILE

REMOTE_PASSWORD_LOGIN parameter is set in init.ora parameter remote_login_passwordfile.  This parameter must be set to either SHARED or EXCLUSIVE. When set to SHARED, the password file can be used by multiple databases, yet only the SYS user is recognized.  When set to EXCLUSIVE, the file can be used by only one database, yet multiple users can exist in the file.  The parameter setting can be confirmed by:

SQL> show parameter password

NAME                          TYPE        VALUE
----------------------------- ----------- ----------
remote_login_passwordfile     string      EXCLUSIVE


To Create the password file.  This is done by executing the following command

$ orapwd file=filename  password=password entries=max_users

The file name is the name of the file that will hold the orapwd password information.  The file location will default to the current directory unless the full path is specified.  The contents are encrypted and are unreadable. The password required is the one for the SYS user of the database.

The max_users is the number of database users that can be granted SYSDBA or SYSOPER.  This parameter should be set to a higher value than the number of anticipated users to prevent having to delete and recreate the password file.

USING ORAPWD:
The syntax of the ORAPWD command is as follows:

ORAPWD FILE=filename [ENTRIES=numusers] [FORCE={Y|N}] [IGNORECASE={Y|N}] [NOSYSDBA={Y|N}]
Command arguments are summarized in the following table.

PARAMETER DESCRIPTION :

FILE: Name to assign to the password file. See your operating system                                                             documentation for name requirements. You must supply a                                                                        complete path. If you supply only a file name, the file is written to                                                          the current directory.

ENTRIES(Optional): Maximum number of entries (user accounts) to permit in the file.

FORCE(Optional):         If y, permits overwriting an existing password file.

IGNORECASE(Optional): If y, passwords are treated as case-insensitive.

NOSYSDBA(Optional): For Data Vault installations. See the Data Vault installation guide                                                           for your platform for more information.
There are no spaces permitted around the equal-to (=) character.


The command prompts for the SYS password and stores the password in the created password file.


EXAMPLE

The following command creates a password file named orapworcl that allows up to 30 privileged users with different passwords.


Create a new password file:
orapwd file=orapwSID password=oracle entries=5

If the password file already exists:
orapwd file=orapwSID password=oracle entries=5 FORCE=Y

Backup and restore of Database before upgrade using RMAN

Make sure you always take Database backup before any upgrades and patches.It is always good idea to do this. In the below example I am showing how to take a backup of full database to some location and how to restore it back from backup in-case of any failures.

backup database before upgrade:

rman "target / nocatalog"

RUN
{
ALLOCATE CHANNEL C1 TYPE DISK;
BACKUP DATABASE FORMAT '/u02/backup/backup_%U' TAG "before_upgrade";
BACKUP CURRENT CONTROLFILE FORMAT '/u02/backup/controlfilebkp.ctl';
}



Restoring database from backup in-case of upgrade fail:

RMAN> restore controlfile from '/u02/backup/controlfilebkp.ctl';

Starting restore at 18-JUL-12
using channel ORA_DISK_1

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output filename=/u01/app/oracle/oradata/test/control01.ctl
output filename=/u01/app/oracle/oradata/test/control02.ctl
output filename=/u01/app/oracle/oradata/test/control03.ctl
Finished restore at 18-JUL-12

copy controlfile to location control02, control03 :

[oracle@linux5 test]$ cp control01* control02.ctl
[oracle@linux5 test]$ cp control01* control03.ctl
[oracle@linux5 test]$ pwd
/u01/app/oracle/oradata/test
[oracle@linux5 test]$


RMAN> startup mount;

database is already started
database mounted
released channel: ORA_DISK_1

RMAN> restore database;

Starting restore at 18-JUL-12
Starting implicit crosscheck backup at 18-JUL-12
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK
Crosschecked 2 objects
Finished implicit crosscheck backup at 18-JUL-12

Starting implicit crosscheck copy at 18-JUL-12
using channel ORA_DISK_1
Finished implicit crosscheck copy at 18-JUL-12

searching for all files in the recovery area
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /u01/app/oracle/flash_recovery_area/TEST/archivelog/2013_07_18/o1_mf_1_8_8yj3n3ww_.arc

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /u01/app/oracle/oradata/test/system01.dbf
restoring datafile 00002 to /u01/app/oracle/oradata/test/undotbs01.dbf
restoring datafile 00003 to /u01/app/oracle/oradata/test/sysaux01.dbf
restoring datafile 00004 to /u01/app/oracle/oradata/test/users01.dbf
restoring datafile 00005 to /u01/app/oracle/oradata/test/example01.dbf
channel ORA_DISK_1: reading from backup piece /u02/backup/backup_06of1qf9_1_1
channel ORA_DISK_1: restored backup piece 1
piece handle=/u02/backup/backup_06of1qf9_1_1 tag=BEFORE_UPGRADE
channel ORA_DISK_1: restore complete, elapsed time: 00:00:15
Finished restore at 18-JUL-12


RMAN> recover database;

Starting recover at 18-JUL-12
using channel ORA_DISK_1

starting media recovery

archive log thread 1 sequence 8 is already on disk as file /u01/app/oracle/flash_recovery_area/TEST/archivelog/2013_07_18/o1_mf_1_8_8yj3n3ww_.arc
archive log thread 1 sequence 9 is already on disk as file /u01/app/oracle/oradata/test/redo03.log
archive log filename=/u01/app/oracle/flash_recovery_area/TEST/archivelog/2013_07_18/o1_mf_1_8_8yj3n3ww_.arc thread=1 sequence=8
archive log filename=/u01/app/oracle/oradata/test/redo03.log thread=1 sequence=9
media recovery complete, elapsed time: 00:00:02
Finished recover at 18-JUL-12


Now login as sysdba and reset the logs before you can start using the database:

sql>sqlplus /"As sysdba

SQL> alter database open resetlogs;

ORA-01113: file 13 needs media recovery

oracle@Linux01:[/u01/app/oracle/datafiles/orcl] $ sqlplus /"As sysdba"

SQL*Plus: Release 11.2.0.4.0 Production on Thu Jan 21 10:09:56 2012

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


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

SQL> ALTER DATABASE
DATAFILE '/u01/app/oracle/datafiles/orcl/USER_DATA_04.dbf'
ONLINE  2 ;

*
ERROR at line 3:
ORA-01916: keyword ONLINE, OFFLINE, RESIZE, AUTOEXTEND or END/DROP expected


SQL> ALTER DATABASE DATAFILE '/u01/app/oracle/datafiles/orcl/USER_DATA_04.dbf' ONLINE;
ALTER DATABASE DATAFILE '/u01/app/oracle/datafiles/orcl/USER_DATA_04.dbf' ONLINE
*
ERROR at line 1:
ORA-01113: file 13 needs media recovery
ORA-01110: data file 13: '/u01/app/oracle/datafiles/orcl/USER_DATA_04.dbf'


SQL> recover datafile '/u01/app/oracle/datafiles/orcl/USER_DATA_04.dbf';
Media recovery complete.
SQL> ALTER DATABASE DATAFILE '/u01/app/oracle/datafiles/orcl/USER_DATA_04.dbf' ONLINE;

Database altered.

SQL>

Recover data using Flashback Query

How to restore the old data using flashback query
My intention is , I want to get back past data of database after erroneously updated and committed.

We know that committed data can never be flashed back. But with 10g new flashback feature we can get back past data even they are committed. 

Before proceed ensure that,

•The UNDO_RETENTION initialization parameter is set to a value so that you can back your data far in the past that you might want to query.

•UNDO_MANAGEMENT is set to AUTO.

•In your UNDO TABLESPACE you have enough space.

With an example I will demonstrate the whole procedure.

1)I have created a table named test_flash_table with column name and salary.

SQL> create table test_flash_table(name varchar2(10), salary number);
Table created.

SQL> insert into test_flash_table values('ABCD',10);
1 row created.

SQL> commit;
Commit complete.

The table contains one row.

2)I erroneously updated column salary of Arju and commited data.

SQL> update test_flash_table set salary=20 where name='ABCD';
1 row updated.

SQL> commit;
Commit complete.

3)After some moments I found that I have made wrong update. Now be sure to query. Also select that time SCN by TIMESTAMP_TO_SCN.

SQL> select name, salary,systimestamp, TIMESTAMP_TO_SCN(SYSTIMESTAMP-interval '8' minute) SCN from test_flash_table as of timestamp (SYSTIMESTAMP-interval '8' Minute);

NAME SALARY SYSTIMESTAMP SCN
---------- ---------- ---------------------------------------- ----------
ABCD 10 29-APR-12 12.34.03.452330 AM -04:00 869222

4)Now update the data based on the SCN.

SQL> update test_flash_table set salary=(select salary from test_flash_table as of scn 869222 where name='ABCD') where name='ABCD';
1 row updated.

SQL> select * from test_flash_table where name='Arju';
NAME SALARY
---------- ----------
ABCD 10

RMAN Backup archielogs from SCN

If you want to backup from SCN until the latest one generated use below as below:

oracle@Linux01:[/u01/app/oracle] $ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Tue Jan 19 11:49:42 2013

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

connected to target database: ORCL (DBID=33581878474)


RMAN> BACKUP ARCHIVELOG FROM SEQUENCE 15 format '/u01/app/oracle/backup/ORCL_ARCHBKP_%U' tag 'ARCH_BKP';

Starting backup at 19-JAN-13
current log archived
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=44 device type=DISK
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=15 RECID=15 STAMP=901018555
input archived log thread=1 sequence=16 RECID=16 STAMP=901025590
input archived log thread=1 sequence=17 RECID=17 STAMP=901032775
input archived log thread=1 sequence=18 RECID=18 STAMP=901103089
input archived log thread=1 sequence=19 RECID=19 STAMP=901189465
input archived log thread=1 sequence=20 RECID=20 STAMP=901443077
input archived log thread=1 sequence=21 RECID=21 STAMP=901471668
input archived log thread=1 sequence=22 RECID=22 STAMP=901479677
input archived log thread=1 sequence=23 RECID=23 STAMP=901540229
channel ORA_DISK_1: starting piece 1 at 19-JAN-13
channel ORA_DISK_1: finished piece 1 at 19-JAN-13
piece handle=/u01/app/oracle/backup/ORCL_ARCHBKP_01qroqc6_1_1 tag=ARCH_BKP comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:02:28
Finished backup at 19-JAN-13


IF you need to backup from SCN until SCN use below cmd:

Example :

RMAN> 

BACKUP ARCHIVELOG FROM SEQUENCE 288 UNTIL SEQUENCE 388 ;