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> 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> 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>

ORA-31623: a job is not attached to this session via the specified handle

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, Automatic Storage Management, OLAP, Data Mining

and Real Application Testing options

UDE-31623: operation generated ORACLE error 31623
ORA-31623: a job is not attached to this session via the specified handle
ORA-06512: at "SYS.DBMS_DATAPUMP", line 3326
ORA-06512: at "SYS.DBMS_DATAPUMP", line 4551

ORA-06512: at line 1

=======================
Found error in the alert log:


Errors in file /u01/app/oracle/admin/DB1/diagnostic/diag/rdbms/DB1/trace/DB1_ora_814.trc
ORA-04031: unable to allocate 56 bytes of shared memory ("streams pool","unknown object","streams pool","fixed allocation callback")

=======================
And in the database stream pool size was 0.

SQL> show parameter streams_pool_size


NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
streams_pool_size big integer 0
SQL>

Resized to 64M.

SQL> alter system set streams_pool_size=64M scope=both;

SQL> show parameter streams

NAME                                 TYPE        VALUE

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

streams_pool_size                    big integer 64M

SQL> alter system switch logfile;

System altered.

And now you can start export again. 😀

ORA-00119: invalid specification for system parameter LOCAL_LISTENER

 

My database was working fine, and suddenly one day I got this error:

[oracle@amey ~]$ sp
SQL*Plus: Release 12.1.0.2.0 Production on Wed Jun 23 19:49:48 2021
Copyright (c) 1982, 2014, Oracle.  All rights reserved.
Connected to an idle instance.
SQL> startup
ORA-00119: invalid specification for system parameter LOCAL_LISTENER
ORA-00132: syntax error or unresolved network name 'LISTENER_CHAMP'
SQL> 
SQL> select name, open_mode from v$database;
select name, open_mode from v$database
*
ERROR at line 1:
ORA-01034: ORACLE not available
Process ID: 0
Session ID: 0 Serial number: 0

In tnsname.ora file add following code containing port number and hostname.

LISTENER_CHAMP =

  (ADDRESS = (PROTOCOL = TCP)(HOST = amey.lad.com)(PORT = 1521))

[oracle@amey ~]$ cat /u01/app/oracle/product/12.1.0.2/db_1/network/admin/tnsnames.ora 

# tnsnames.ora Network Configuration File: /u01/app/oracle/product/12.1.0.2/db_1/network/admin/tnsnames.ora

# Generated by Oracle configuration tools.

CHAMP =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = amey.lad.com)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = champ)

    )

  )

LISTENER_CHAMP =

  (ADDRESS = (PROTOCOL = TCP)(HOST = amey.lad.com)(PORT = 1521))

[oracle@amey ~]$ 

[oracle@amey ~]$ sp

SQL*Plus: Release 12.1.0.2.0 Production on Wed Jun 23 19:58:02 2021

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

Connected to an idle instance.

SQL> startup

ORACLE instance started.

Total System Global Area 4630511616 bytes

Fixed Size     2933592 bytes

Variable Size 1509952680 bytes

Database Buffers 3103784960 bytes

Redo Buffers    13840384 bytes

Database mounted.

Database opened.

SQL> select name, open_mode from v$database;

NAME   OPEN_MODE

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

CHAMP   READ WRITE

SQL> exit

User Management

 
How to create users in the Oracle database?

create user AMEY identified by "Test123$" DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP;
grant connect, resource to AMEY;
grant create session to AMEY;

Creating and assigning profile to user:

CREATE PROFILE AMEYS_PROFILE LIMIT

  FAILED_LOGIN_ATTEMPTS 3  -- Account locked after 3 failed logins.

  PASSWORD_LOCK_TIME 5     -- Number of days account is locked for. UNLIMITED required explicit unlock by DBA.

  PASSWORD_LIFE_TIME 30    -- Password expires after 90 days.

  PASSWORD_GRACE_TIME 3    -- Grace period for password expiration.

  PASSWORD_REUSE_TIME 120  -- Number of days until a specific password can be reused. UNLIMITED means never.

  PASSWORD_REUSE_MAX 10    -- The number of changes required before a password can be reused. UNLIMITED means never.

/

ALTER USER AMEY PROFILE AMEYS_PROFILE;

how to get metadata of user?
set lines 200
set long 1000
set pagesize 1000
select dbms_metadata.get_ddl('USER','AMEY')|| '; '  from dual;
select 'grant ' || privilege || ' to ' || grantee ||'; ' from dba_sys_privs where grantee='AMEY' order by grantee;
select 'grant ' || granted_role || ' to ' || grantee ||'; ' from dba_role_privs where grantee='AMEY' order by grantee;
select 'Grant ' || PRIVILEGE  || ' on ' || OWNER || '.' || TABLE_NAME || ' to ' || grantee  ||'; '  from dba_TAB_PRIVS where GRANTEE in ('AMEY');

How to get the Size of database user?

select sum(bytes)/1024/1024 from dba_segments where owner='AMEY';
Or
select sum(bytes)/1024/1024/1024 from dba_segments where owner='&1';

How to get encrypted password of Oracle User?
select name, password from sys.user$ where name='AMEY';

How to put the same password for the user?

CREATE USER AT
  IDENTIFIED BY VALUES '28D48922ED178DA7'
  DEFAULT TABLESPACE AT
  TEMPORARY TABLESPACE TEMP
  PROFILE DEFAULT
  ACCOUNT UNLOCK;

how to check objects of user?
select OBJECT_TYPE,count(OBJECT_NAME) from dba_objects where owner='&1' group by OBJECT_TYPE;

Object Count for user:

select count(1) from dba_objects where owner='AMEY';

How Grant Read-Only access to one user to Another?
select 'grant select on ' ||OWNER||'.'||TABLE_NAME|| ' to AMIT;' from dba_tables where owner='AMEY';

To Check provided Grants:
select * from dba_tab_privs where grantee='AMIT' and grantor='AMEY';

How to Check Oracle Database and SYNC status ?

 
select name,open_mode,database_role from v$database;

select pr.thread# "Node", pr.primary "Primary",dr.Standby "DR",pr.primary-dr.Standby "Difference"
from (select thread#,max(sequence#) as primary from v$archived_log where
resetlogs_change#=(select resetlogs_change# from v$database) group by thread#) Pr,
(select thread#,max(sequence#) as Standby from v$archived_log where
resetlogs_change#=(select resetlogs_change# from v$database) and applied='YES' group by thread#) dr
where pr.thread#=dr.thread#;

RMAN Backup completion check query in %

 From the below query, you can check how much RMAN backup completed in %.

set echo on timing on
set lines 200
col username        format a10
col OPNAME          format a35
col SOFAR           format 999,999,999
col TOTALWORK       format 999,999,999
col 'Work Done %'   format 999.99
col Start_time      format a20
select SID,username,OPNAME,SOFAR,TOTALWORK,SOFAR/TOTALWORK*100 "Work Done %",
to_char(START_TIME,'DD-MON-YY hh24:mi:ss') "Start_time",
to_char(sysdate + TIME_REMAINING/3600/24,'DD-MON-YY hh24:mi:ss') "End_at"
from  v$session_longops
where sofar!=TOTALWORK and totalwork!=0
      and OPNAME like 'RMAN%';

Query to Check RMAN backup

 Following Query is used to check rman backup:

set lines 200 pages 200
col STATUS format a15
col hrs format 999.99
col start_time for a15
col INPUT_TYPE for a12
col END_TIME for a15
select
SESSION_KEY,SESSION_RECID,SESSION_STAMP ,INPUT_TYPE, STATUS,
to_char(START_TIME,'mm/dd/yy hh24:mi') start_time,
to_char(END_TIME,'mm/dd/yy hh24:mi') end_time,
elapsed_seconds/3600 hrs,
OUTPUT_DEVICE_TYPE
from V$RMAN_BACKUP_JOB_DETAILS
order by 1;

Put session_recid and session_stamp from above query and you will get log for that RMAN backup:

set lines 200
set pages 1000
select output
from GV$RMAN_OUTPUT
where session_recid =11038
and session_stamp =1070757497
order by recid;