Oracle Database Commands and Practical Examples

Monday 24 June 2019

Online Redo log file drop and recreate

SQL> select GROUP#,THREAD#,STATUS from v$log;

GROUP# THREAD# STATUS
---------- ---------- ----------------
1 1 INACTIVE
2 1 INACTIVE
3 1 CURRENT

SQL> @d

NAME INSTANCE_NAME DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE CURRENT_SCN FAILOVER_SCN
--------- ---------------- ------------------------------ -------------------- ---------------- ------------------ ----------------------------------------
SMRPATCH SMRPATCH7 SMRPATCH7 READ WRITE PRIMARY NO 0

SQL> select member from v$logfile;

MEMBER
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
/u01/app/oracle/oradata/SMRPATCH7/redo03.log
/u01/app/oracle/oradata/SMRPATCH7/redo02.log
/u01/app/oracle/oradata/SMRPATCH7/redo01.log

SQL> @d

NAME INSTANCE_NAME DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE CURRENT_SCN FAILOVER_SCN
--------- ---------------- ------------------------------ -------------------- ---------------- ------------------ ----------------------------------------
SMRPATCH SMRPATCH7 SMRPATCH7 READ WRITE PRIMARY NO 0

SQL> alter database drop logfile group 1;

Database altered.

SQL> select GROUP#,THREAD#,STATUS from v$log;

GROUP# THREAD# STATUS
---------- ---------- ----------------
2 1 INACTIVE
3 1 CURRENT

SQL> alter database drop logfile group 3;
alter database drop logfile group 3
*
ERROR at line 1:
ORA-01623: log 3 is current log for instance SMRPATCH7 (thread 1) - cannot drop
ORA-00312: online log 3 thread 1: '/u01/app/oracle/oradata/SMRPATCH7/redo03.log'

SQL> alter database add logfile group 1('/u04/MasterDB/oradata/SMRPATCH7/redo01.log')size 50m;

Database altered.

SQL> select GROUP#,THREAD#,STATUS from v$log;

GROUP# THREAD# STATUS
---------- ---------- ----------------
1 1 UNUSED
2 1 INACTIVE
3 1 CURRENT

SQL> alter database drop logfile group 2;

Database altered.

SQL> select GROUP#,THREAD#,STATUS from v$log;

GROUP# THREAD# STATUS
---------- ---------- ----------------
1 1 UNUSED
3 1 CURRENT

SQL> alter database add logfile group 2('/u04/MasterDB/oradata/SMRPATCH7/redo02.log')size 50m;

Database altered.

SQL> alter system switch logfile;

System altered.

SQL> select GROUP#,THREAD#,STATUS from v$log;

GROUP# THREAD# STATUS

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

1 1 CURRENT

2 1 INACTIVE

3 1 INACTIVE

SQL> alter database drop logfile group 3;

Database altered.

SQL> alter database add logfile group 1('/u04/MasterDB/oradata/SMRPATCH7/redo02.log')size 50m;

alter database add logfile group 1('/u04/MasterDB/oradata/SMRPATCH7/redo02.log')size 50m

ERROR at line 1:

ORA-01184: logfile group 1 already exists

SQL> alter database add logfile group 1('/u04/MasterDB/oradata/SMRPATCH7/redo03.log')size 50m;

alter database add logfile group 1('/u04/MasterDB/oradata/SMRPATCH7/redo03.log')size 50m

ERROR at line 1:

ORA-01184: logfile group 1 already exists

SQL> alter database add logfile group 3 ('/u04/MasterDB/oradata/SMRPATCH7/redo03.log')size 50m;

Database altered.

SQL> select member from v$logfile;

MEMBER

/u04/MasterDB/oradata/SMRPATCH7/redo03.log

/u04/MasterDB/oradata/SMRPATCH7/redo02.log

/u04/MasterDB/oradata/SMRPATCH7/redo01.log

SQL> exit

#################### 2 node RAC Example ##############################

SQL>

NAME INSTANCE_NAME DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE CURRENT_SCN FAILOVER_SCN

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

SMRPROD SMRPROD1 SMRPROD READ WRITE PRIMARY NO 0

SQL> select member from v$logfile;

MEMBER

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

+DATA/SMRPROD/ONLINELOG/group_1.277.1051537329

+FRA1/SMRPROD/ONLINELOG/group_1.296.1051537329

+DATA/SMRPROD/ONLINELOG/group_2.278.1051537329

+FRA1/SMRPROD/ONLINELOG/group_2.297.1051537331

+DATA/SMRPROD/ONLINELOG/group_3.287.1051537877

+FRA1/SMRPROD/ONLINELOG/group_3.298.1051537877

+DATA/SMRPROD/ONLINELOG/group_4.288.1051537877

+FRA1/SMRPROD/ONLINELOG/group_4.299.1051537877

8 rows selected.

SQL> select GROUP#,THREAD#,STATUS from v$log;

GROUP# THREAD# STATUS

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

1 1 INACTIVE

2 1 CURRENT

3 2 INACTIVE

4 2 CURRENT

SQL>

NAME INSTANCE_NAME DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE CURRENT_SCN FAILOVER_SCN

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

SMRPROD SMRPROD1 SMRPROD READ WRITE PRIMARY NO 0

SQL> alter database add logfile thread 1 group 5 ('+DATA','+FRA1') size 250M,group 6 ('+DATA','+FRA1') size 250M,group 7 ('+DATA','+FRA1') size 250M;

Database altered.

SQL> alter database add logfile thread 2 group 8 ('+DATA','+FRA1') size 250M, group 9 ('+DATA','+FRA1') size 250M,group 10 ('+DATA','+FRA1') size 250M;

Database altered.

SQL> select GROUP#,THREAD#,STATUS from v$log;

GROUP# THREAD# STATUS

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

1 1 INACTIVE

2 1 CURRENT

3 2 INACTIVE

4 2 CURRENT

5 1 UNUSED

6 1 UNUSED

7 1 UNUSED

8 2 UNUSED

9 2 UNUSED

10 2 UNUSED

10 rows selected.

SQL>

NAME INSTANCE_NAME DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE CURRENT_SCN FAILOVER_SCN

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

SMRPROD SMRPROD1 SMRPROD READ WRITE PRIMARY NO 0

SQL> alter database drop logfile group 1;

Database altered.

SQL> alter database drop logfile group 3;

Database altered.

SQL> select GROUP#,THREAD#,STATUS from v$log;

GROUP# THREAD# STATUS

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

2 1 CURRENT

4 2 CURRENT

5 1 UNUSED

6 1 UNUSED

7 1 UNUSED

8 2 UNUSED

9 2 UNUSED

10 2 UNUSED

8 rows selected.

SQL> alter system archive log current;

System altered.

SQL> /

System altered.

SQL> alter database drop logfile group 2;

Database altered.

SQL> alter system switch logfile;

System altered.

SQL> alter database drop logfile group 4;

Database altered.

SQL> select GROUP#,THREAD#,STATUS from v$log;

GROUP# THREAD# STATUS

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

5 1 ACTIVE

6 1 ACTIVE

7 1 CURRENT

8 2 CURRENT

9 2 INACTIVE

10 2 INACTIVE

6 rows selected.

SQL> select member from v$logfile;

MEMBER

+DATA/SMRPROD/ONLINELOG/group_5.290.1051801493

+FRA1/SMRPROD/ONLINELOG/group_5.327.1051801493

+DATA/SMRPROD/ONLINELOG/group_6.291.1051801493

+FRA1/SMRPROD/ONLINELOG/group_6.328.1051801493

+DATA/SMRPROD/ONLINELOG/group_7.292.1051801495

+FRA1/SMRPROD/ONLINELOG/group_7.329.1051801495

+DATA/SMRPROD/ONLINELOG/group_8.293.1051801503

+FRA1/SMRPROD/ONLINELOG/group_8.330.1051801505

+DATA/SMRPROD/ONLINELOG/group_9.294.1051801505

+FRA1/SMRPROD/ONLINELOG/group_9.331.1051801505

+DATA/SMRPROD/ONLINELOG/group_10.295.1051801505

+FRA1/SMRPROD/ONLINELOG/group_10.332.1051801507

12 rows selected.

SQL>

SQL> select BYTES/1024/1024 from v$log;

BYTES/1024/1024

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

250

6 rows selected.

SQL>

physical standby to snapshot standby database for Oracle 10g

standby snapshot for Oracle 10g
================================

Snapshot standby for 10g

create restore point Apr23 guarantee flashback database;

select name, time, storage_size,GUARANTEE_FLASHBACK_DATABASE from V$restore_point;

alter database activate standby database;

select name, open_mode, controlfile_type from V$database;

alter database open;

select name, open_mode, controlfile_type from V$database;

need to create one table(for confirmation)

5.Revert to standby setup
Now we finished testing and want to revert to the original state.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.

Total System Global Area 217157632 bytes
Fixed Size 2209600 bytes
Variable Size 159385792 bytes
Database Buffers 50331648 bytes
Redo Buffers 5230592 bytes
Database mounted.

SQL> flashback database to restore point PRE_ACTIV_SNAP;

SQL> alter database convert to physical standby;

SQL> shutdown immediate;
ORA-01507: database not mounted

ORACLE instance shut down.

SQL> startup mount;
ORACLE instance started.

Total System Global Area 217157632 bytes
Fixed Size 2209600 bytes
Variable Size 159385792 bytes
Database Buffers 50331648 bytes
Redo Buffers 5230592 bytes
Database mounted.

SQL> select name, open_mode, controlfile_type from V$database;

NAME OPEN_MODE CONTROL
-------------------------------------------------- -------------------- -------
MOUNTED STANDBY

Now the standby database is back again in MOUNT mode after revert.

https://varunyadav27.blogspot.com/2019/06/physical-standby-database-to-snapshot.html

Physical standby database to Snapshot Database 11g , 12c

standby snanshot for Oracle11g,12c
=================================

SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------
NO

ALTER SYSTEM SET db_recovery_file_dest_size = 30g SCOPE = BOTH;

ALTER SYSTEM SET db_recovery_file_dest = '' SCOPE = BOTH;

ALTER SYSTEM SET db_flashback_retention_target = 2880 SCOPE = BOTH;

alter database flashback on;
*****************************************************************************************
Step 1: Cancel the Managed Recovery Process (MRP) on the physical standby database, shut it down and place it in Mount mode.

----->alter database recover managed standby database cancel;
----->shut immediate
----->startup mount

Step 2: Once the standby database is mounted, convert the Physical standby database to snapshot standby database.

----->alter database convert to snapshot standby;

Step 3: You can now open the snapshot standby database and check its mode.

----->alter database open;

----->select status,instance_name,database_role,open_mode from v$database,v$Instance;

STATUS INSTANCE_NAME DATABASE_ROLE OPEN_MODE
----------- --------------- ---------------- ------------------
OPEN SNAPSHOT STANDBY READ WRITE

Standby:

select process,status,sequence# from v$managed_standby;

Steps on converting back a snapshot standby database to physical standby database.
Step 1: Shut down the snapshot standby database and open it in Mount mode.

----->shut immediate

----->startup mount

Convert the snapshot standby database to physical standby database.

----->alter database convert to physical standby;

Step 3: Once done, bounce the physical standby database and start the Managed Recovery Process (MRP) on it.

----->shut immediate;

----->startup mount;

select status,instance_name,database_role,open_mode from v$database,v$Instance;
STATUS INSTANCE_NAME DATABASE_ROLE OPEN_MODE
------------ -------------- ---------------- ----------------
OPEN PHYSICAL STANDBY MOUNTED

----->alter database recover managed standby database disconnect from session;

#################### Example 12 C ##############################################
SQL> startup mount
ORACLE instance started.

Total System Global Area 1.2885E+10 bytes
Fixed Size 7657384 bytes
Variable Size 6073354328 bytes
Database Buffers 6777995264 bytes
Redo Buffers 25894912 bytes
Database mounted.
SQL> alter database convert to snapshot standby;

Database altered.

SQL> alter database open;

Database altered.

SQL> select status,instance_name,database_role,open_mode from v$database,v$Instance;

STATUS INSTANCE_NAME DATABASE_ROLE OPEN_MODE
------------ ---------------- ---------------- --------------------
OPEN SMRPRODB SNAPSHOT STANDBY READ WRITE

SQL> SET LINESIZE 400

COLUMN action_time FORMAT A20
COLUMN action FORMAT A10
COLUMN status FORMAT A10
COLUMN description FORMAT A40
SQL> SQL> SQL> SQL> SQL> SQL> COLUMN version FORMAT A10
COLUMN bundle_series FORMAT A10

SELECT TO_CHAR(action_time, 'DD-MON-YYYY HH24:MI:SS') AS action_time,
SQL> SQL> SQL> 2 action,
3 status,
4 description,
version,
patch_id,
bundle_series
FROM sys.dba_registry_sqlpatch
ORDER by action_time;
5 6 7 8 9
ACTION_TIME ACTION STATUS DESCRIPTION VERSION PATCH_ID BUNDLE_SER
-------------------- ---------- ---------- ---------------------------------------- ---------- ---------- ----------
02-FEB-2019 08:40:52 APPLY SUCCESS DATABASE PATCH SET UPDATE 12.1.0.2.18101 12.1.0.2 28259833 PSU
6

02-FEB-2019 08:49:29 APPLY SUCCESS Database PSU 12.1.0.2.181016, Oracle Jav 12.1.0.2 28440711
aVM Component (OCT2018)

SQL> @dbstatus

NAME INSTANCE_NAME DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE CURRENT_SCN FAILOVER_SCN
--------- ---------------- ------------------------------ -------------------- ---------------- ------------------ ----------------------------------------
SMRPRODB SMRPRODB SMRPRODB_DR READ WRITE SNAPSHOT STANDBY YES 354178037

SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area 1.2885E+10 bytes
Fixed Size 7657384 bytes
Variable Size 6073354328 bytes
Database Buffers 6777995264 bytes
Redo Buffers 25894912 bytes
Database mounted.
SQL> alter database convert to physical standby;

Database altered.

SQL> shut immediate;
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.

SQL> startup mount;
ORACLE instance started.

Total System Global Area 1.2885E+10 bytes
Fixed Size 7657384 bytes
Variable Size 6073354328 bytes
Database Buffers 6777995264 bytes
Redo Buffers 25894912 bytes
Database mounted.
SQL> select status,instance_name,database_role,open_mode from v$database,v$Instance;

STATUS INSTANCE_NAME DATABASE_ROLE OPEN_MODE
---------- ---------------- ---------------- --------------------
MOUNTED SMRPRODB PHYSICAL STANDBY MOUNTED

SQL> alter database recover managed standby database disconnect from session;

Database altered.

SQL> @dbstatus

NAME INSTANCE_NAME DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE CURRENT_SCN FAILOVER_SCN
--------- ---------------- ------------------------------ -------------------- ---------------- ------------------ ----------------------------------------
SMRPRODB SMRPRODB SMRPRODB_DR MOUNTED PHYSICAL STANDBY YES 0

SQL> @standby

NAME OPEN_MODE DATABASE_ROLE
--------- -------------------- ----------------
SMRPRODB MOUNTED PHYSICAL STANDBY

PROCESS STATUS SEQUENCE#
--------- ---------- ----------
ARCH CLOSING 4172
ARCH CONNECTED 0
ARCH CONNECTED 0
ARCH CONNECTED 0
ARCH CONNECTED 0
ARCH CONNECTED 0
ARCH CONNECTED 0
ARCH CONNECTED 0
ARCH CONNECTED 0
ARCH CONNECTED 0
ARCH CONNECTED 0

PROCESS STATUS SEQUENCE#
--------- ---------- ----------
ARCH CONNECTED 0
ARCH CONNECTED 0
ARCH CONNECTED 0
ARCH CONNECTED 0
ARCH CONNECTED 0
ARCH CONNECTED 0
ARCH CONNECTED 0
ARCH CONNECTED 0
ARCH CONNECTED 0
ARCH CONNECTED 0
ARCH CONNECTED 0

PROCESS STATUS SEQUENCE#
--------- ---------- ----------
ARCH CONNECTED 0
ARCH CONNECTED 0
ARCH CONNECTED 0
ARCH CONNECTED 0
ARCH CONNECTED 0
ARCH CONNECTED 0
ARCH CONNECTED 0
ARCH CONNECTED 0
RFS IDLE 4173
RFS IDLE 0
MRP0 APPLYING_L 4173

PROCESS STATUS SEQUENCE#
--------- ---------- ----------
OG

33 rows selected.

Thread Last Sequence Received Last Sequence Applied Difference
---------- ---------------------- --------------------- ----------
1 4172 4172 0

SQL> SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference"
2 FROM
(SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,
3 4 (SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL
WHERE
ARCH.THREAD# = APPL.THREAD#
; 5 6 7

Thread Last Sequence Received Last Sequence Applied Difference
---------- ---------------------- --------------------- ----------
1 4175 4175 0

SQL>

##########################################################################

HOME

Monday 24 June 2019

Online Redo log file drop and recreate

physical standby to snapshot standby database for Oracle 10g

Physical standby database to Snapshot Database 11g , 12c