Mussaib DBA Tutorials: July 2020

Tuesday, 28 July 2020

How to take user grants Backup

spool /production/10_july_20_export_dump/grants_scripts/MUSAIB_permissions_10july2020.sql
set heading off
SET NEWPAGE NONE
SET FEEDBACK OFF
select 'grant ' || privilege || ' on ' || owner || '.' || table_name || ' to ' || grantee || ';'
from dba_tab_privs
where owner in ('MUSAIB')
order by owner, table_name, grantee;
select 'quit;' from dual;
select '/' from dual;
spool off;

quit;

/

Resizeing UNDO Tablespace datafile

UNDO TABLESPACE Before Size :

Tablespace Size Free Used Max Ext

Name File Count (MB) (MB) (MB) (MB) % USED Graph

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

SYSTEM 1 .80 .00 .79 32 99.2301829

SYSAUX 1 .66 .03 .63 32 94.8988971

USERS 1 .00 .00 .00 32 20 ********

UNDOTBS1 1 1.00 .96 .03 32 3.06396484 *********

SQL> alter database datafile 'C:\APP\MMUSSAIB\ORADATA\TEST\UNDOTBS01.DBF' resize 2G;

SQL>
set feed off

column "Graph" heading "Graph" format a11

set pages 999

set lines 999

column "tablespace_name" heading "Tablespace | Name" format a20

column "FileCount" heading "File | Count" format 999999

column "Size(MB)" heading "Size | (MB)" format 999,999,999.99

column "Free(MB)" heading "Free | (MB)" format 999,999,999.99

column "Used(MB)" heading "Used | (MB)" format 999,999,999.99

column "Max Ext(MB)" heading "Max Ext | (MB)" format 999,999,999

column "%Free" heading "% | Free" format 999.99

column "%Free Ext" heading "% | Free Ext" format 999.99

column tablespace_name heading "Tablespace | Name" format a20
SELECT
ts.tablespace_name, "File Count",

TRUNC("SIZE(MB)", 2) "Size(MB)",

TRUNC(fr."FREE(MB)", 2) "Free(MB)",

TRUNC("SIZE(MB)" - "FREE(MB)", 2) "Used(MB)",

df."MAX_EXT" "Max Ext(MB)",

100-((fr."FREE(MB)" / df."SIZE(MB)") * 100) "% USED",

RPAD('*', TRUNC(CEIL((fr."FREE(MB)" / df."SIZE(MB)") * 100)/10), '*') "Graph"

FROM

(SELECT tablespace_name,

SUM (bytes) / (1024 * 1024*1024) "FREE(MB)"

FROM dba_free_space

GROUP BY tablespace_name) fr,

(SELECT tablespace_name, SUM(bytes) / (1024 * 1024*1024) "SIZE(MB)", COUNT(*)

"File Count", SUM(maxbytes) / (1024 * 1024*1024) "MAX_EXT"

FROM dba_data_files

GROUP BY tablespace_name) df,

(SELECT tablespace_name

FROM dba_tablespaces) ts

WHERE fr.tablespace_name = df.tablespace_name (+)

AND fr.tablespace_name = ts.tablespace_name (+)

ORDER BY "% USED" desc

/

UNDO TABLESPACE After Increasing Size :

Sunday, 12 July 2020

Control File Restore from RMAN Backup

Note: Renamed Control file to create an issue

Location Of Control FIle:/u01/app/oracle/oradata/TEST/controlfile/o1_mf_hjpbhtdb_.ctl,
/u01/app/oracle/fast_recovery_area/TEST/controlfile/o1_mf_ hjpbhtg8_.ctl

SQL> show parameter control

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time integer 7
control_files string /u01/app/oracle/oradata/TEST/controlfile/o1_mf_hjpbhtdb_.ctl, /u01/app/oracle/fast_recovery_area/TEST/controlfile/o1_mf_ hjpbhtg8_.ctl
control_management_pack_access string DIAGNOSTIC+TUNING

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

Control File 1:Renameing to create issue for practice.

[oracle@musaib ~]$ cd /u01/app/oracle/oradata/TEST/controlfile/
[oracle@musaib controlfile]$ ls -ltr
total 9808
-rw-r-----. 1 oracle oracle 10043392 Jul 12 21:23 o1_mf_hjpbhtdb_.ctl

[oracle@musaib controlfile]$ mv o1_mf_hjpbhtdb_.ctl control_file_bkp.ctl
[oracle@musaib controlfile]$ ls
control_file_bkp.ctl

Control File 2:Renameing to create issue for practice.

[oracle@musaib controlfile]$ cd /u01/app/oracle/fast_recovery_area/TEST/controlfile/
[oracle@musaib controlfile]$ ls
o1_mf_hjpbhtg8_.ctl
[oracle@musaib controlfile]$ mv o1_mf_hjpbhtg8_.ctl control_file_bkp.ctl
[oracle@musaib controlfile]$ ls
control_file_bkp.ctl

[oracle@musaib controlfile]$ !sql
sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Sun Jul 12 21:24:19 2020

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

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> shut immediate;
ORACLE instance shut down.
SQL> startup nomount;
ORACLE instance started.

Total System Global Area 2516582400 bytes
Fixed Size 2927528 bytes
Variable Size 671089752 bytes
Database Buffers 1828716544 bytes
Redo Buffers 13848576 bytes

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
[oracle@musaib controlfile]$ rman target /

Recovery Manager: Release 12.1.0.2.0 - Production on Sun Jul 12 21:24:54 2020

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

connected to target database: TEST (not mounted)

RMAN> restore controlfile from '/u01/app/oracle/TEST/TEST_C_20200712_0cv558v3.ctl';

Starting restore at 12-JUL-20
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=22 device type=DISK

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/u01/app/oracle/oradata/TEST/controlfile/o1_mf_hjpbhtdb_.ctl
output file name=/u01/app/oracle/fast_recovery_area/TEST/controlfile/o1_mf_hjpbhtg8_.ctl
Finished restore at 12-JUL-20

RMAN> alter database mount;

Statement processed
released channel: ORA_DISK_1

RMAN> recover database;

Starting recover at 12-JUL-20
Starting implicit crosscheck backup at 12-JUL-20
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=22 device type=DISK
Crosschecked 3 objects
Finished implicit crosscheck backup at 12-JUL-20

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

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

List of Cataloged Files
=======================
File Name: /u01/app/oracle/fast_recovery_area/TEST/archivelog/2020_07_12/o1_mf_1_23_hjpddgcq_.arc

using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 23 is already on disk as file /u01/app/oracle/fast_recovery_area/TEST/archivelog/2020_07_12/o1_mf_1_23_hjpddgcq_.arc
archived log for thread 1 with sequence 24 is already on disk as file /u01/app/oracle/fast_recovery_area/TEST/onlinelog/o1_mf_3_hjpbhw31_.log
archived log file name=/u01/app/oracle/fast_recovery_area/TEST/archivelog/2020_07_12/o1_mf_1_23_hjpddgcq_.arc thread=1 sequence=23
archived log file name=/u01/app/oracle/fast_recovery_area/TEST/onlinelog/o1_mf_3_hjpbhw31_.log thread=1 sequence=24
media recovery complete, elapsed time: 00:00:00
Finished recover at 12-JUL-20

RMAN> alter database open resetlogs;

Statement processed

RMAN> select name,open_mode from v$database;

NAME OPEN_MODE
--------- --------------------
TEST READ WRITE

RMAN> exit

Recovery Manager complete.

[oracle@musaib controlfile]$ !sql
sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Sun Jul 12 21:29:59 2020

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

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> show parameter control

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time integer 7
control_files string /u01/app/oracle/oradata/TEST/c
ontrolfile/o1_mf_hjpbhtdb_.ctl
, /u01/app/oracle/fast_recover
y_area/TEST/controlfile/o1_mf_
hjpbhtg8_.ctl
control_management_pack_access string DIAGNOSTIC+TUNING
SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
[oracle@musaib controlfile]$ cd /u01/app/oracle/fast_recovery_area/TEST/
[oracle@musaib TEST]$ ls -ltr
total 12
drwxr-x---. 3 oracle oracle 4096 Jul 12 20:47 archivelog
drwxr-x---. 2 oracle oracle 4096 Jul 12 20:49 onlinelog
drwxr-x---. 2 oracle oracle 4096 Jul 12 21:26 controlfile
[oracle@musaib TEST]$ cd controlfile/
[oracle@musaib controlfile]$ ls -ltr
total 19904
-rw-r-----. 1 oracle oracle 10043392 Jul 12 21:24 control_file_bkp.ctl
-rw-rw----. 1 oracle oracle 10338304 Jul 12 21:30 o1_mf_hjpbhtg8_.ctl
[oracle@musaib controlfile]$

DB Rebuild from Level0 Backup

PROD :

RMAN >RUN
{
ALLOCATE CHANNEL ch11 TYPE DISK MAXPIECESIZE 10G;
BACKUP
FORMAT '/u01/app/oracle/TEST/%d_D_%T_%u_s%s_p%p'
DATABASE
CURRENT CONTROLFILE
FORMAT '/u01/app/oracle/TEST/%d_C_%T_%u.ctl'
SPFILE
FORMAT '/u01/app/oracle/TEST/%d_S_%T_%u'
PLUS ARCHIVELOG
FORMAT '/u01/app/oracle/TEST/%d_A_%T_%u_s%s_p%p';
RELEASE CHANNEL ch11;
}

Backup location: '/u01/app/oracle/TEST/

[oracle@musaib oracle]$ ls -ltr
total 44
drwxrwxr-x. 19 oracle oracle 4096 Apr 19 01:59 diag
drwxr-xr-x. 2 oracle oracle 4096 Apr 19 01:59 checkpoints
drwxr-x---. 2 oracle oracle 4096 Apr 19 02:06 musaib
drwxr-xr-x. 4 oracle oracle 4096 Apr 19 02:06 product
drwxr-x---. 4 oracle oracle 4096 Apr 19 02:06 cfgtoollogs
drwxr-x---. 3 oracle oracle 4096 Apr 19 02:06 MUSAIB
drwxr-x---. 3 oracle oracle 4096 Jul 12 20:46 admin
drwxr-x---. 4 oracle oracle 4096 Jul 12 20:47 fast_recovery_area
drwxr-x---. 4 oracle oracle 4096 Jul 12 20:47 oradata
drwxr-x---. 4 oracle oracle 4096 Jul 12 20:49 audit
drwxrwxr-x. 2 oracle oracle 4096 Jul 12 21:21 TEST
[oracle@musaib oracle]$ cd TEST/

SCP BACKUP PIECES TO TEST SERVER FROM PROD:

[oracle@musaib TEST]$ scp * oracle@192.168.189.133:/u01/clone/
The authenticity of host '192.168.189.133 (192.168.189.133)' can't be established.
RSA key fingerprint is fd:f7:4b:c8:8f:a8:40:f0:dd:6b:ac:94:18:81:03:43.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '192.168.189.133' (RSA) to the list of known hosts.
oracle@192.168.189.133's password:
TEST_A_20200712_09v558uo_s9_p1 100% 30MB 30.3MB/s 00:01
TEST_A_20200712_0ev558v6_s14_p1 100% 4608 4.5KB/s 00:00
TEST_C_20200712_0cv558v3.ctl 100% 9856KB 9.6MB/s 00:01
TEST_D_20200712_0av558up_s10_p1 100% 1163MB 40.1MB/s 00:29
TEST_D_20200712_0bv558v1_s11_p1 100% 9888KB 9.7MB/s 00:00
TEST_S_20200712_0dv558v5 100% 96KB 96.0KB/s 00:00

**************************************************************************

SCP Pfile FROM PROD TO TEST SERVER

[oracle@musaib dbs]$ ls -ltr
total 19648
-rw-r--r--. 1 oracle oracle 2992 Feb 3 2012 init.ora
-rw-rw----. 1 oracle oracle 10043392 Jul 12 20:35 snapcf_musaib.f
-rw-r-----. 1 oracle oracle 0 Jul 12 20:46 lkinstmusaib
-rw-rw----. 1 oracle oracle 1544 Jul 12 20:46 hc_musaib.dat
-rw-r-----. 1 oracle oracle 24 Jul 12 20:47 lkTEST
-rw-r-----. 1 oracle oracle 7680 Jul 12 20:49 orapwtest
-rw-rw----. 1 oracle oracle 10043392 Jul 12 21:21 snapcf_test.f
-rw-rw----. 1 oracle oracle 1544 Jul 12 21:57 hc_test.dat
-rw-r-----. 1 oracle oracle 3584 Jul 12 21:57 spfiletest.ora
-rw-rw-r--. 1 oracle oracle 1068 Jul 12 22:03 inittest.ora
[oracle@musaib dbs]$ scp inittest.ora oracle@192.168.189.133:/u01/clone/
oracle@192.168.189.133's password:
inittest.ora
100% 1068 1.0KB/s 00:00
[oracle@musaib dbs]$

************************************************************************************

ON TEST SERVER:

[oracle@musaib ~]$ export ORACLE_SID=test
[oracle@musaib ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Sun Jul 12 23:20:29 2020

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

Connected to an idle instance.

SQL> startup nomount pfile='/u01/clone/inittest.ora';
ORACLE instance started.

Total System Global Area 2516582400 bytes
Fixed Size 2927528 bytes
Variable Size 671089752 bytes
Database Buffers 1828716544 bytes
Redo Buffers 13848576 bytes
SQL> exit

Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
[oracle@musaib ~]$ rman target /

Recovery Manager: Release 12.1.0.2.0 - Production on Sun Jul 12 23:22:54 2020

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

connected to target database: TEST (not mounted)

RMAN> restore controlfile from '/u01/clone/TEST_C_20200712_0cv558v3.ctl';

Starting restore at 12-JUL-20
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=22 device type=DISK

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/u01/app/oracle/oradata/TEST/controlfile/o1_mf_hjpbhtdb_.ctl
output file name=/u01/app/oracle/fast_recovery_area/TEST/controlfile/o1_mf_hjpbhtg8_.ctl
Finished restore at 12-JUL-20

RMAN> catalog start with '/u01/clone';

Starting implicit crosscheck backup at 12-JUL-20
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=22 device type=DISK
Crosschecked 3 objects
Finished implicit crosscheck backup at 12-JUL-20

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

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

List of Cataloged Files
=======================
File Name: /u01/app/oracle/fast_recovery_area/TEST/archivelog/2020_07_12/o1_mf_1_23_hjpddgcq_.arc
File Name: /u01/app/oracle/fast_recovery_area/TEST/archivelog/2020_07_12/o1_mf_1_1_hjpktbm3_.arc
File Name: /u01/app/oracle/fast_recovery_area/TEST/archivelog/2020_07_12/o1_mf_1_1_hjpgosk4_.arc
File Name: /u01/app/oracle/fast_recovery_area/TEST/archivelog/2020_07_12/o1_mf_1_23_hjpds6ry_.arc
File Name: /u01/app/oracle/fast_recovery_area/TEST/archivelog/2020_07_12/o1_mf_1_24_hjpds6s7_.arc
File Name: /u01/app/oracle/fast_recovery_area/TEST/archivelog/2020_07_12/o1_mf_1_22_hjpds6rf_.arc

searching for all files that match the pattern /u01/clone

List of Files Unknown to the Database
=====================================
File Name: /u01/clone/TEST_S_20200712_0dv558v5
File Name: /u01/clone/TEST_C_20200712_0cv558v3.ctl
File Name: /u01/clone/TEST_D_20200712_0bv558v1_s11_p1
File Name: /u01/clone/TEST_A_20200712_09v558uo_s9_p1
File Name: /u01/clone/TEST_A_20200712_0ev558v6_s14_p1
File Name: /u01/clone/TEST_D_20200712_0av558up_s10_p1
File Name: /u01/clone/inittest.ora

Do you really want to catalog the above files (enter YES or NO)? YES
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /u01/clone/TEST_S_20200712_0dv558v5
File Name: /u01/clone/TEST_C_20200712_0cv558v3.ctl
File Name: /u01/clone/TEST_D_20200712_0bv558v1_s11_p1
File Name: /u01/clone/TEST_A_20200712_09v558uo_s9_p1
File Name: /u01/clone/TEST_A_20200712_0ev558v6_s14_p1
File Name: /u01/clone/TEST_D_20200712_0av558up_s10_p1

List of Files Which Were Not Cataloged
=======================================
File Name: /u01/clone/inittest.ora
RMAN-07517: Reason: The file header is corrupted

RMAN> restore database;

Starting restore at 12-JUL-20
using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /u01/app/oracle/oradata/TEST/datafile/o1_mf_system_hjpbfb7y_.dbf
channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/TEST/datafile/o1_mf_sysaux_hjpbcm3v_.dbf
channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/TEST/datafile/o1_mf_undotbs1_hjpbh2gq_.dbf
channel ORA_DISK_1: restoring datafile 00006 to /u01/app/oracle/oradata/TEST/datafile/o1_mf_users_hjpbh1dl_.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/TEST/TEST_D_20200712_0av558up_s10_p1
channel ORA_DISK_1: piece handle=/u01/app/oracle/TEST/TEST_D_20200712_0av558up_s10_p1 tag=TAG20200712T212129
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:35
Finished restore at 12-JUL-20

RMAN> recover database;

Starting recover at 12-JUL-20
using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 23 is already on disk as file /u01/app/oracle/fast_recovery_area/TEST/archivelog/2020_07_12/o1_mf_1_23_hjpddgcq_.arc
archived log for thread 1 with sequence 24 is already on disk as file /u01/app/oracle/fast_recovery_area/TEST/archivelog/2020_07_12/o1_mf_1_24_hjpds6s7_.arc
archived log for thread 1 with sequence 1 is already on disk as file /u01/app/oracle/fast_recovery_area/TEST/archivelog/2020_07_12/o1_mf_1_1_hjpgosk4_.arc
archived log file name=/u01/app/oracle/fast_recovery_area/TEST/archivelog/2020_07_12/o1_mf_1_23_hjpddgcq_.arc thread=1 sequence=23
archived log file name=/u01/app/oracle/fast_recovery_area/TEST/archivelog/2020_07_12/o1_mf_1_24_hjpds6s7_.arc thread=1 sequence=24
archived log file name=/u01/app/oracle/fast_recovery_area/TEST/archivelog/2020_07_12/o1_mf_1_1_hjpgosk4_.arc thread=1 sequence=1
archived log file name=/u01/app/oracle/fast_recovery_area/TEST/archivelog/2020_07_12/o1_mf_1_1_hjpktbm3_.arc thread=1 sequence=1
unable to find archived log
archived log thread=1 sequence=2
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 07/12/2020 23:29:09
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 2 and starting SCN of 1910939

RMAN> alter database open resetlogs;

Statement processed

RMAN> select name,open_mode from v$database;

NAME OPEN_MODE
--------- --------------------
TEST READ WRITE

Saturday, 11 July 2020

Dropping undo & Creating New UNDO

Old ---undo
New----UNDOTBS_NEW

SQL> select TABLESPACE_NAME,FILE_NAME from dba_data_files where tablespace_name ='undo';

TABLESPACE_NAME
------------------------------
FILE_NAME
--------------------------------------------------------------------------------
UNDOTBS1
E:\MUSAIB\DATA\UNDOTBS10.DBF

UNDOTBS1
E:\MUSAIB\DATA\UNDOTBS11.DBF

UNDOTBS1
E:\MUSAIB\DATA\UNDOTBS12.DBF

3 rows selected.

SQL> create undo tablespace UNDOTBS_NEW datafile 'E:\MUSAIB\DATA\UNDOTBS151.DBF' size 30G reuse autoextend off;

Tablespace created.

SQL> alter tablespace UNDOTBS_NEW add datafile 'E:\MUSAIB\DATA\UNDOTBS152.DBF' size 30G reuse autoextend off;

Tablespace altered.

SQL>
SQL> alter system set undo_tablespace=UNDOTBS_NEW;

System altered.

SQL> SHOW parameter undo;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
temp_undo_enabled boolean FALSE
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS_NEW

SQL> drop tablespace undo including contents and datafiles;

Tablespace dropped.

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

k:\>sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Sat Dec 21 09:23:04 2019

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

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> select name,open_mode from v$database;

NAME OPEN_MODE
--------- --------------------
MUSAIB READ WRITE

SQL> SELECT SUM(BYTES/1024/1024/1024) FROM DBA_DATA_FILES WHERE TABLESPACE_NAME LIKE '%UNDO%';

SUM(BYTES/1024/1024/1024)
-------------------------
60

SQL>

Friday, 10 July 2020

How to change VNC password on Linux

~/.vnc/passwdis the default location where the VNC password is stored. The password is stored at this location when the vncserver starts for a first time. To update or change your VNC password you should use vncpasswd command. vncpasswd will promt you two times to enter your new password:

$ vncpasswd
Password:
Verify:
The vncpasswd command also accepts a password input from STDIN which also allows to store password file into different location. The following example will change the VNC password to MYVNCPASSWORD and store it to ~/.secret/vncpass given that the .secret directory exists:

$ echo MYVNCPASSWORD | vncpasswd -f > ~/.secret/passvnc
$

Tuesday, 7 July 2020

How To Unlock a User in Oracle

Summary: In this tutorial, you will learn how to unlock a user in Oracle by using the ALTER USER ACCOUNT UNLOCK statement.

To unlock a user in Oracle, you follow these steps:

First, log in to the Oracle Database as a SYS user.
Then, use ALTER USER statement to unlock the user as follows:

ALTER USER username IDENTIFIED BY password ACCOUNT UNLOCK;

Note that if you unlock an account without resetting the password, then the password remains expired, therefore, the IDENTIFIED BY password clause is necessary. The first time the user logs in to the Oracle Database, he needs to change his password.

Let’s see an example of unlock a user.

Suppose user alice is locked. When alice logs in, she will see the following message:

Enter user-name: alice@pdborcl
Enter password:
ERROR:
ORA-28000: the account is locked

To unlock the user alice, you use these steps:

First, log in to the Oracle Database using theot user:

Enter user-name: ot@orclpdb
Enter password: <ot_password>

Then, use the ALTER USER statement to unlock user alice:

ALTER USER alice IDENTIFIED BY abcd1234 ACCOUNT UNLOCK;

Note that abcd1234 is the new password that alice will use to log in the Oracle Database.

Now the user alice should be able to log in the database with the new password.

In this tutorial, you have learned how to unlock a user in the Oracle Database by using the ALTER USER ACCOUNT UNLOCK statement.

Monday, 6 July 2020

How to Compile Invalid Package Body in Oracle 11g

Alter Package Syntax

Alter Package package_name Compile Body;

Alter Package Example

ALTER PACKAGE emp_pkg COMPILE BODY;

Output

Package altered.

Mussaib DBA Tutorials

Search This Blog