ORACLE 12C - RECOVER ALL ONLINE REDO LOG FILES USING RMAN

 STEP 1:

 Startup the database and check the members in the logfile.

STEP 2:

 Simulate the failure

                                                      

STEP 3:

 Startup the database in mount stage ,connect it to the rman and execute

The command.

                                                          

STEP 4:

 Now you can see the no copy datafile in the spcified location.so rman

Does not know where the files are located.so now we have to catalog

The backup files in the dbs location. 

                                                         

You will see all the files in the dbs location will be corrupted on his

Header.

 STEP 5:

 You can mount the database in nomount mode using pfile and connect

It to the rman.

                                                       

STEP 6:

 The database is in nomount stage on RMAN.so we could restore the

Control file from the backup.

                                                 

STEP 7:

 You can restore the database using rman backup.

                                                            

STEP 8;

And then recover the database,here you can find the error in the log files 

which was log sequence number would be mismatched.

STEP 9:

Disconnect the rman utility and connect to the sql prompt by issue the following command in the pic

User management and Profile Managment on Oracle 12C

To create tablespace

sql> create tablespace raneshtab datafile '/u01/app/oracle/oradata/prod21/raneshtab.dbf' size 10m;

To create User

sql> create user ranesh identified by ranesh default tablespace raneshtab temporary tablespace temp;

To grant permission to user

sql>grant create session,create table to ranesh;

To revoke any permission from user

sql>revoke create table from ranesh;

To change Password of user

sql> alter user ranesh identified by ranesh1

To allocate quota on tablespace

sql> alter user ranesh quota 10m on raneshtab;

To change default tablespace or temporary tablespace

sql>alter user ranesh default tablespace test;

sql> alter user ranesh default temporary tablespace temp;

To check default permanent &temporary tablespace for a user

sql> select default tablespace temporary tablespace from dba users where username="ranesh";

To lock or unlock a user

sql> alter user ranesh account lock;

sql> alter user ranesh account uinlock;

To change default permanent tablespace

sql> alter database default tablespace mydata;

To change default temporary tablespace

sql> alter database default temporary tablespace temp;

To check system privileges for a user

sql>select privilege from dba_sys _privs where grantee='ranesh';

To check roles assigned to a user;

sql>select granted role from dba _role _privs where grantee='ranesh';

To drop a user

sql> drop user ranesh;

or

sql> drop user ranesh cascade;

Profile Managment

sql> create profile my_profile limit failed_login_attempts 3,password_lock_time 1/24/60,session_per_user 1,idle_time 5;

To assign a profile to user

sql> alter user ranesh profile my_profile;

To alter a profile value

sql>alter profile my_profile limit session_per_user 3;

To check and resource_limit value

sql> show parameter resource_limit

sql>alter system set resource_limit=true scope=both;

To kill a session

sql>select sid serial# from v$session where username='ranesh';

Flashback restore point

Step1:Startup database and Create Table 

[oracle@trichy ~]$ sqlplus / as sysdba

 SQL*Plus: Release 12.2.0.1.0 Production on Sun Oct 11 22:41:53 2020

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

 Connected to an idle instance.

 SQL> startup

ORACLE instance started.

 Total System Global Area 2046820352 bytes

Fixed Size     8622480 bytes

Variable Size   587206256 bytes

Database Buffers  1442840576 bytes

Redo Buffers     8151040 bytes

Database mounted.

Database opened.

SQL> create table product(id int,name varchar2(20));

 Table created.

 SQL> insert into product values(111,'inba');

 1 row created.

 SQL> insert into product values(222,'siva');

 1 row created.

 SQL> insert into product values(333,'shan');

 1 row created.

 SQL> commit;

 Commit complete.

 SQL> select * from product;

 ID NAME

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

       111 inba

       222 siva

       333 shan

 Step2:To create Restore point

 SQL> desc V$RESTORE_POINT;

 Name    Null?    Type

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

 SCN     NUMBER

 DATABASE_INCARNATION#     NUMBER

 GUARANTEE_FLASHBACK_DATABASE     VARCHAR2(3)

 STORAGE_SIZE     NUMBER

 TIME     TIMESTAMP(9)

 RESTORE_POINT_TIME     TIMESTAMP(9)

 PRESERVED     VARCHAR2(3)

 NAME     VARCHAR2(128)

 PDB_RESTORE_POINT     VARCHAR2(3)

 CLEAN_PDB_RESTORE_POINT     VARCHAR2(3)

 PDB_INCARNATION#     NUMBER

 CON_ID

 SQL> create restore point  respt_inba  guarantee flashback database;

 Restore point created.

 Step3:To drop the Table

 SQL> drop table product purge;

 Table dropped.

 SQL> select * from product;

select * from product

              *

ERROR at line 1:

ORA-00942: table or view does not exist

 SQL> shut immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL>

Step4:To recover the table using restore point

 SQL> startup mount

ORACLE instance started.

 Total System Global Area 2046820352 bytes

Fixed Size     8622480 bytes

Variable Size   587206256 bytes

Database Buffers  1442840576 bytes

Redo Buffers     8151040 bytes

Database mounted.

SQL> flashback database to restore point respt_inba;

 Flashback complete.

 SQL> alter database open resetlogs;

 Database altered.

 SQL> select * from product;

 ID NAME

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

       111 inba

       222 siva

       333 shan

 Screen shots

                                                               

 

 

 

 

Recover the Lost Datafile Using RMAN on Oracle 12C

 

ORA-01157: cannot identify/lock data file %s - see DBWR trace file

 

Cause: The background process was either unable to find one of the data files or

failed to lock it because the file was already in use.

The database will prohibit access to this file but other files will be unaffected.

However, the first instance to open the database will need to access all online data files.

Accompanying error from the operating system describes why the file could not be identified.

 

Action: Have the operating system make the file available to the database.

 

 

[oracle@trichydoyen]$cd  /u01/doyendb/data

 

[oracle@trichydoyen]$ls

 

system01.dbf sysaux01.dbf undotbs01.dbf users01.dbf

 

[oracle@trichydoyen]$mv users01.dbfusers02.dbf

 

[oracle@trichydoyen]$!Sq

 

SQL*Plus: Release 12.2.0.1.0 Production on FRI OCT 06 10:57:38 2020

 

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

 

Connected to:

Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

 

SQL>alterdatabaseopen;

alterdatabaseopen*ERRORatline1:

ORA-01157:cannotidentify/lockdatafile4-seeDBWRtracefile

ORA-01110:datafile5:'u01/data/users01.dbf'

 

SQL>select open_mode from v$database;

 

OPEN_MODE

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

MOUNTED

 

SQL>SELECT MEMBER FROM V$LOG G, V$LOGFILE F WHERE G.GROUP# = F.GROUP# AND G.STATUS = 'CURRENT';

 

MEMBER

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

/u01/doyen/redo/redo01.log

 

SQL> RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL

ORA-00279: change 55636 generated at 06 10 2020 11:37:38 needed for thread 1

ORA-00289: suggestion :/u01/app/oracle/product/12.2.0.1/db_1/dbs/arcr_1_111.arc

ORA-00280: change 55636 for thread 1 is in sequence #111

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

 

/u01/doyendb/redo/redo01.log

 

Log applied.

Media recovery complete.

 

SQL>alter database open resetlogs;

 

Database altered.

 

 

SQL>select open_mode from v$database;

 

OPEN_MODE

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

READ WRITE

 

 

Recover User and Table using Flashback Database

FLASHBACK DATABASE command is a fast alternative to performing an incomplete recovery. 

Flashback must be enabled before any flashback operations are performed.

If the database is in NO ARCHIVE LOG it must be switched to ARCHIVE LOG mode.

To Turn on Flash back:-

[oracle@trichy ~]$ sqlplus / as sysdba

 SQL*Plus: Release 12.2.0.1.0 Production on Sat Oct 10 23:34:25 2020

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

 Connected to an idle instance.

 SQL> startup

ORACLE instance started.

 Total System Global Area 2046820352 bytes

Fixed Size     8622480 bytes

Variable Size   587206256 bytes

Database Buffers  1442840576 bytes

Redo Buffers     8151040 bytes

Database mounted.

Database opened.

SQL> select flashback_on from v$database;

 FLASHBACK_ON

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

NO

 SQL> archive log list;

Database log mode        Archive Mode

Automatic archival        Enabled

Archive destination        /u01/app/oracle/product/12.2.0.1/db_1/dbs/arch

Oldest online log sequence     14

Next log sequence to archive   16

Current log sequence        16

SQL> shut immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> STARTUP MOUNT EXCLUSIVE

ORACLE instance started.

 

Total System Global Area 2046820352 bytes

Fixed Size     8622480 bytes

Variable Size   587206256 bytes

Database Buffers  1442840576 bytes

Redo Buffers     8151040 bytes

Database mounted.

SQL> ALTER DATABASE FLASHBACK ON;

ALTER DATABASE FLASHBACK ON

*

ERROR at line 1:

ORA-38706: Cannot turn on FLASHBACK DATABASE logging.

ORA-38709: Recovery Area is not enabled.

 SQL> alter system set db_recovery_file_dest_size=10g scope=spfile;

 System altered.

 SQL> alter system set db_recovery_file_dest='/u01/arch' scope=spfile;

 System altered.

 SQL> Shut immediate

ORA-01109: database not open

 Database dismounted.

ORACLE instance shut down.

SQL> STARTUP MOUNT EXCLUSIVE

ORACLE instance started.

 Total System Global Area 2046820352 bytes

Fixed Size     8622480 bytes

Variable Size   587206256 bytes

Database Buffers  1442840576 bytes

Redo Buffers     8151040 bytes

Database mounted.

SQL> ALTER DATABASE FLASHBACK ON;

 Database altered.

 SQL> ALTER DATABASE OPEN;

 Database altered.

 SQL> set time on;

23:46:53 SQL>

23:46:55 SQL> create user remo identified by remo;

 User created.

 23:47:12 SQL> grant connect,resource to remo;

 Grant succeeded.

 23:47:19 SQL> Øgrant all privileges to remo;

 Grant succeeded.

 23:47:39 SQL> conn remo/remo                   

Connected.

23:49:41 SQL> CREATE TABLE t5 (id  NUMBER(10));

 Table created.

 23:51:51 SQL> insert into t5 values(1);

 1 row created.

 23:52:01 SQL> /

 1 row created.

 23:52:03 SQL> /

 1 row created.

 23:52:03 SQL> commit;

 Commit complete.

 23:52:08 SQL> conn / as sysdba

Connected.

23:52:54 SQL>  SELECT current_scn,TO_CHAR(SYSTIMESTAMP, 'YYYY-MM-DD HH24:MI:SS') FROM v$database;

 CURRENT_SCN TO_CHAR(SYSTIMESTAM

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

    6373386 2020-10-10 23:53:07

 23:53:07 SQL> drop user remo cascade;

 User dropped.

 23:53:39 SQL> SHUTDOWN IMMEDIATE

Database closed.

Database dismounted.

ORACLE instance shut down.

23:54:18 SQL> set time off

SQL> STARTUP MOUNT EXCLUSIVE

ORACLE instance started.

 Total System Global Area 2046820352 bytes

Fixed Size     8622480 bytes

Variable Size   587206256 bytes

Database Buffers  1442840576 bytes

Redo Buffers     8151040 bytes

Database mounted.

SQL> flashback database to timestamp to_date('2020-10-10 23:52:54','YYYY-MM-DD HH24:MI:SS');

 Flashback complete.

 SQL> ALTER DATABASE OPEN RESETLOGS;

 Database altered.

 SQL>

SQL>

SQL> conn remo/remo

Connected.

SQL> select * from t5;

 ID

----------

 1

 1

 1

 SQL>   

 Screen Shots 

                                                                       

Oracle 12c-Manual Data Guard Failover Step by Step

 When we have planned maintenance activity we can simply switch roles of Primary and Standby database. But what if Primary Database crashes and we can’t recover it in a time.

Data Guard has a solution for that. We can Fail Over Primary database role to Standby Database and we don’t need to switch Standby Database role to Primary Database.

Types of FailOver:

1.Manual FailOver:

If the Primary database fails, one of the standby databases can be made to assume the Primary role. This role change of standby database to primary, in case of failure of existing primary, is called failover. There may or may not be data loss. This is not a planned activity and is performed only in catastrophic cases where the Primary database fails.

2. Fast-start FailOver: 

We need to configure this in data guard broker. So when Primary Database is unavailable. Dgbroker will fail over primary database role to One of standby database configured previously in dgbroker.

Failover Considerations: Things we need to take care while using this option.

In this option old primary database will be disabled from the Data Guard configuration.

If primary database crashed and generated archived not copied to standby database then data loss is possible.

This option is critical and should only be used in EMERGENCY.

Note: To use this option we should use Standby Database is most current[Maximum synchronized]

 STEP 1:

 Check Standby Database role

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

 STEP 2:

 Stop the MRP Process if it is running.

 Sql>alter database recover managed standby database cancel;

 STEP 3:

 Apply the following command to finish database recovery

 Sql>alter database recover managed standby database finish;

 Step 4:

 Use the following command to activate standby database to a primary.

 Sql>alter database activate Standby database;

 Step 5:

 Check open mode and database role from a v$database

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

 Step 6:

 Shut down primary database.

 Step 7:

 Startup database

 Step 8:

 Check open mode and database role from the v$database.

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

PRACTICE :