ORACLE DBA AMY LAD: November 2021

Move Redolog files from One drive to another Drive in Oracle (Windows)

SQL> select member from v$logfile;
MEMBER
--------------------------------------------------------------------------------
C:\APP\ORACLE\ORADATA\ORCL\ONLINELOG\O1_MF_1_JRR7NHRN_.LOG
E:\ORCL\ONLINELOG\O1_MF_1_JRR7NHY2_.LOG
C:\APP\ORACLE\ORADATA\ORCL\ONLINELOG\O1_MF_2_JRR7NLC7_.LOG
E:\ORCL\ONLINELOG\O1_MF_2_JRR7NLJJ_.LOG
C:\APP\ORACLE\ORADATA\ORCL\ONLINELOG\O1_MF_3_JRR7NNPZ_.LOG
E:\ORCL\ONLINELOG\O1_MF_3_JRR7NNX3_.LOG
6 rows selected.

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

Move redolog files from E:\ORCL\ONLINELOG\ to required destination C:\APP\ORACLE\ORADATA\ORCL\ONLINELOG\, cut paste.

SQL> startup mount
ORACLE instance started.
Total System Global Area 5100272704 bytes
Fixed Size 9038912 bytes
Variable Size 905969664 bytes
Database Buffers 4177526784 bytes
Redo Buffers 7737344 bytes
Database mounted.

SQL> alter database rename file 'E:\ORCL\ONLINELOG\O1_MF_1_JRR7NHY2_.LOG' to 'C:\APP\ORACLE\ORADATA\ORCL\ONLINELOG\O1_MF_1_JRR7NHY2_.LOG';
Database altered.
SQL> alter database rename file 'E:\ORCL\ONLINELOG\O1_MF_2_JRR7NLJJ_.LOG' to 'C:\APP\ORACLE\ORADATA\ORCL\ONLINELOG\O1_MF_2_JRR7NLJJ_.LOG';
Database altered.
SQL> alter database rename file 'E:\ORCL\ONLINELOG\O1_MF_3_JRR7NNX3_.LOG' to 'C:\APP\ORACLE\ORADATA\ORCL\ONLINELOG\O1_MF_3_JRR7NNX3_.LOG';
Database altered.

SQL> select member from v$logfile;
MEMBER
--------------------------------------------------------------------------------
C:\APP\ORACLE\ORADATA\ORCL\ONLINELOG\O1_MF_1_JRR7NHRN_.LOG
C:\APP\ORACLE\ORADATA\ORCL\ONLINELOG\O1_MF_1_JRR7NHY2_.LOG
C:\APP\ORACLE\ORADATA\ORCL\ONLINELOG\O1_MF_2_JRR7NLC7_.LOG
C:\APP\ORACLE\ORADATA\ORCL\ONLINELOG\O1_MF_2_JRR7NLJJ_.LOG
C:\APP\ORACLE\ORADATA\ORCL\ONLINELOG\O1_MF_3_JRR7NNPZ_.LOG
C:\APP\ORACLE\ORADATA\ORCL\ONLINELOG\O1_MF_3_JRR7NNX3_.LOG
6 rows selected.

SQL> alter database open;
Database altered.
SQL>

Move Controlfile from one Drive to another in Oracle (Windows)

SQL> show parameter control
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time integer 7
control_files string C:\APP\ORACLE\ORADATA\ORCL\CONTROLFILE\O1_MF_JRR7NH3Z_.CTL,E:\ORCL\CONTROLFILE\O1_MF_JRR7NH4B_.CTL
control_management_pack_access string NONE

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

Move Controlfile from E drive to C Drive Path.

SQL> startup nomount
ORACLE instance started.
Total System Global Area 5100272704 bytes
Fixed Size 9038912 bytes
Variable Size 905969664 bytes
Database Buffers 4177526784 bytes
Redo Buffers 7737344 bytes

SQL> alter system set control_files='C:\APP\ORACLE\ORADATA\ORCL\CONTROLFILE\O1_MF_JRR7NH3Z_.CTL', 'C:\APP\ORACLE\ORADATA\ORCL\CONTROLFILE\O1_MF_JRR7NH4B_.CTL' scope=spfile;
System altered.

SQL> shut immediate
ORA-01507: database not mounted

ORACLE instance shut down.

SQL> show parameter control
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time integer 7
control_files string C:\APP\ORACLE\ORADATA\ORCL\CONTROLFILE\O1_MF_JRR7NH3Z_.CTL,C:\APP\ORACLE\ORADATA\ORCL\CONTROLFILE\O1_MF_JRR7NH4B_.CTL
control_management_pack_access string NONE

SQL> alter database mount;
Database altered.

SQL> alter database open;
Database altered.
SQL>

Change DB_RECOVERY_FILE_DEST location in Oracle (Windows)

C:\Users\Amey Lad>sqlplus "/as sysdba"
SQL*Plus: Release 19.0.0.0.0 - Production on Thu Nov 11 01:22:18 2021
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.

Connected to:
Oracle Database 19c Standard Edition 2 Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 5
Next log sequence to archive 7
Current log sequence 7

SQL> show parameter DB_RECOVERY_FILE_DEST
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string E:\
db_recovery_file_dest_size big integer 7851M

SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST ='C:\app\oracle\oradata\ORCL\flash_recovery_area' SCOPE=spfile;
System altered.
SQL>

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

SQL> startup
ORACLE instance started.
Total System Global Area 5100272704 bytes
Fixed Size 9038912 bytes
Variable Size 905969664 bytes
Database Buffers 4177526784 bytes
Redo Buffers 7737344 bytes
Database mounted.
Database opened.

SQL> show parameter DB_RECOVERY_FILE_DEST
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string C:\app\oracle\oradata\ORCL\fla
sh_recovery_area
db_recovery_file_dest_size big integer 7851M
SQL> alter system switch logfile;
System altered.

C:\app\oracle\oradata\ORCL\flash_recovery_area\ORCL\ARCHIVELOG\2021_11_11>dir

Volume in drive C is Windows-SSD

Volume Serial Number is 520F-57F0

Directory of C:\app\oracle\oradata\ORCL\flash_recovery_area\ORCL\ARCHIVELOG\2021_11_11

11-11-2021 01:44 <DIR> .

12-11-2021 18:30 <DIR> ..

11-11-2021 01:41 3,584 O1_MF_1_10_JRR9TWTL_.ARC

11-11-2021 01:43 1,473,024 O1_MF_1_11_JRR9ZWPX_.ARC

11-11-2021 01:23 89,046,528 O1_MF_1_7_JRR8S30M_.ARC

11-11-2021 01:40 165,888 O1_MF_1_8_JRR9S9R2_.ARC

11-11-2021 01:41 7,168 O1_MF_1_9_JRR9TSV7_.ARC

5 File(s) 90,696,192 bytes

2 Dir(s) 333,616,369,664 bytes free

C:\app\oracle\oradata\ORCL\flash_recovery_area\ORCL\ARCHIVELOG\2021_11_11>

ORACLE DBA AMY LAD

Friday 12 November 2021

Move Redolog files from One drive to another Drive in Oracle (Windows)

Move Controlfile from one Drive to another in Oracle (Windows)

Change DB_RECOVERY_FILE_DEST location in Oracle (Windows)

How to put newly created Oracle database in archivelog mode?

Search This Blog