Database RAC Dataguard RMAN Performance Tuning Feedback
Database Migration & Upgradation
DataGuard
ASM & RAC
Exadata
RMAN Backup & Recovery
Oracle Cloud AWS Azure Google Cloud
DevOps Ansible Kubernetes Terraform
Shell Scripting Powershell Scripting
Infomratica Others

Wednesday, February 22, 2023

Tablespace Creation in Oracle

Tablespaces are Crazy in oracle 


2 Types are tablespaces based on the size:

a) Small file Tablespace 
b) Big file Tablespace 

3 Types of Tablespaces in Oracle 

a) Normal Tablespaces like SYSTEM, SYSAUX, USERS, EXAMPLE and ANY Other Custom Tablespace 
b) Undo Tablespaces
c) Tamp Tablespaces

Small File Tablespaces:

CREATE TABLESPACE TBS1
DATAFILE '/u01/app/oracle/oradata/DEVDB/datafile/tbs1.dbf'
SIZE 10M AUTOEXTEND ON;

CREATE UNDO TABLESPACE UNDOTBS11
DATAFILE '/u01/app/oracle/oradata/DEVDB/datafile/undotbs11.dbf'
SIZE 10M AUTOEXTEND ON;

CREATE TEMPORARY TABLESPACE TEMPTBS1
TEMPFILE '/u01/app/oracle/oradata/DEVDB/datafile/temptbs1.dbf' 
SIZE 10M AUTOEXTEND ON;

select TABLESPACE_NAME,BIGFILE from dba_tablespaces;

BIG File Tablespaces:

CREATE BIGFILE TABLESPACE BIGTBS1
DATAFILE '/u01/app/oracle/oradata/DEVDB/datafile/bigtbs1.dbf'
SIZE 10M AUTOEXTEND ON;

CREATE BIGFILE UNDO TABLESPACE BIGUNDOTBS11
DATAFILE '/u01/app/oracle/oradata/DEVDB/datafile/bigfileundotbs1.dbf'
SIZE 10M AUTOEXTEND ON;

CREATE BIGFILE TEMPORARY TABLESPACE BIGTEMPTBS1
TEMPFILE '/u01/app/oracle/oradata/DEVDB/datafile/bigfiletemptbs1.dbf' 
SIZE 10M AUTOEXTEND ON;

select TABLESPACE_NAME,BIGFILE from dba_tablespaces;


SQL> CREATE BIGFILE TABLESPACE TBS1
DATAFILE '/u01/app/oracle/oradata/DEVDB/datafile/tbs1.dbf'
SIZE 10M AUTOEXTEND ON;
  2    3
Tablespace created.

SQL> 

SQL> CREATE UNDO TABLESPACE UNDOTBS11
DATAFILE '/u01/app/oracle/oradata/DEVDB/datafile/undotbs11.dbf'
SIZE 10M AUTOEXTEND ON;
  2    3
Tablespace created.

SQL> CREATE TEMPORARY TABLESPACE TEMPTBS1
TEMPFILE '/u01/app/oracle/oradata/DEVDB/datafile/temptbs1.dbf'
SIZE 10M AUTOEXTEND ON;
  2    3
Tablespace created.

SQL> CREATE BIGFILE TABLESPACE BIGTBS1
DATAFILE '/u01/app/oracle/oradata/DEVDB/datafile/bigtbs1.dbf'
SIZE 10M AUTOEXTEND ON;
  2    3
Tablespace created.

SQL> CREATE BIGFILE UNDO TABLESPACE BIGUNDOTBS11
DATAFILE '/u01/app/oracle/oradata/DEVDB/datafile/bigfileundotbs1.dbf'
SIZE 10M AUTOEXTEND ON;
  2    3
Tablespace created.

SQL> CREATE BIGFILE TEMPORARY TABLESPACE BIGTEMPTBS1
TEMPFILE '/u01/app/oracle/oradata/DEVDB/datafile/bigfiletemptbs1.dbf'
SIZE 10M AUTOEXTEND ON;
  2    3
Tablespace created.

SQL>

SQL> select TABLESPACE_NAME,BIGFILE from dba_tablespaces;

TABLESPACE_NAME                BIG
------------------------------ ---
SYSTEM                         NO
SYSAUX                         NO
UNDOTBS1                       NO
TEMP                           NO
USERS                          NO
TBS1                           YES
UNDOTBS11                      NO
TEMPTBS1                       NO
BIGTBS1                        YES
BIGUNDOTBS11                   YES
BIGTEMPTBS1                    YES

11 rows selected.
SQL>

SQL> select NAME,BIGFILE from v$tablespace;

NAME                           BIG
------------------------------ ---
SYSAUX                         NO
SYSTEM                         NO
UNDOTBS1                       NO
USERS                          NO
TEMP                           NO
TBS1                           YES
UNDOTBS11                      NO
TEMPTBS1                       NO
BIGTBS1                        YES
BIGUNDOTBS11                   YES
BIGTEMPTBS1                    YES

11 rows selected.

SQL>

Regards,
Mallik

Posted by at No comments:

Thursday, February 16, 2023

RMAN Level_0 and RMAN level_1 scripts

RMAN Level_0 and RMAN level_1 scripts 

Level_0:

export ORACLE_SID=DEVDB
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/19.0.0.0/dbhome_1
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
export CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib
export PATH=$PATH:$ORACLE_HOME/bin
rman target / log=/u01/backup/Level_0_`date +%d%b%Y%H%M`.log <<EOF
run {
allocate channel ch1 device type disk;
allocate channel ch2 device type disk;
backup as backupset incremental level 0 database format '/u01/backup/Fullback_%d_%T_%U'
plus archivelog format '/u01/backup/Archive_%T_%U';
backup current controlfile format '/u01/backup/Controlback_%d_%T_%U';
backup spfile format '/u01/backup/spfile_%d_%T_%U';
release channel ch1;
release channel ch2;
}
quit;
EOF


Level_1:

export ORACLE_SID=DEVDB
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/19.0.0.0/dbhome_1
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
export CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib
export PATH=$PATH:$ORACLE_HOME/bin
rman target / log=/u01/backup/Level_1_`date +%d%b%Y%H%M`.log <<EOF
run {
allocate channel ch1 device type disk;
allocate channel ch2 device type disk;
backup as backupset incremental level 1 database format '/u01/backup/Fullback_%d_%T_%U'
plus archivelog format '/u01/backup/Archive_%T_%U';
backup current controlfile format '/u01/backup/Controlback_%d_%T_%U';
backup spfile format '/u01/backup/spfile_%d_%T_%U';
release channel ch1;
release channel ch2;
}
quit;
EOF

Logs:

Level_0

[oracle@oraclelab1 backup]$ more Level_0_16Feb20230040.log

Recovery Manager: Release 19.0.0.0.0 - Production on Thu Feb 16 00:40:42 2023
Version 19.17.0.0.0

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

connected to target database: DEVDB (DBID=1016989223)

RMAN> 2> 3> 4> 5> 6> 7> 8> 9> 10>
using target database control file instead of recovery catalog
allocated channel: ch1
channel ch1: SID=316 device type=DISK

allocated channel: ch2
channel ch2: SID=38 device type=DISK


Starting backup at 16-FEB-23
current log archived
channel ch1: starting archived log backup set
channel ch1: specifying archived log(s) in backup set
input archived log thread=1 sequence=72 RECID=406 STAMP=1128904041
channel ch1: starting piece 1 at 16-FEB-23
channel ch2: starting archived log backup set
channel ch2: specifying archived log(s) in backup set
input archived log thread=1 sequence=73 RECID=407 STAMP=1128904057
input archived log thread=1 sequence=74 RECID=408 STAMP=1128904220
input archived log thread=1 sequence=75 RECID=409 STAMP=1128904225
input archived log thread=1 sequence=76 RECID=410 STAMP=1128904389
input archived log thread=1 sequence=77 RECID=411 STAMP=1128904407
input archived log thread=1 sequence=78 RECID=412 STAMP=1128904453
channel ch2: starting piece 1 at 16-FEB-23
channel ch1: finished piece 1 at 16-FEB-23
piece handle=/u01/backup/DEVDB_Archivelog_DEVDB_20230216_8j1kje4c_275_1_1 tag=TAG20230216T004043 comment=NONE
channel ch1: backup set complete, elapsed time: 00:00:01
channel ch1: starting archived log backup set
channel ch1: specifying archived log(s) in backup set
input archived log thread=1 sequence=79 RECID=413 STAMP=1128904456
input archived log thread=1 sequence=80 RECID=414 STAMP=1128904582
input archived log thread=1 sequence=81 RECID=415 STAMP=1128904599
input archived log thread=1 sequence=82 RECID=416 STAMP=1128904843
channel ch1: starting piece 1 at 16-FEB-23
channel ch2: finished piece 1 at 16-FEB-23
piece handle=/u01/backup/DEVDB_Archivelog_DEVDB_20230216_8k1kje4c_276_1_1 tag=TAG20230216T004043 comment=NONE
channel ch2: backup set complete, elapsed time: 00:00:01
channel ch1: finished piece 1 at 16-FEB-23
piece handle=/u01/backup/DEVDB_Archivelog_DEVDB_20230216_8l1kje4d_277_1_1 tag=TAG20230216T004043 comment=NONE
channel ch1: backup set complete, elapsed time: 00:00:01
Finished backup at 16-FEB-23

Starting backup at 16-FEB-23
channel ch1: starting incremental level 0 datafile backup set
channel ch1: specifying datafile(s) in backup set
input datafile file number=00003 name=/u01/app/oracle/oradata/DEVDB/datafile/o1_mf_sysaux_ktbgb34z_.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/DEVDB/datafile/o1_mf_undotbs1_ktbgb356_.dbf
channel ch1: starting piece 1 at 16-FEB-23
channel ch2: starting incremental level 0 datafile backup set
channel ch2: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/oradata/DEVDB/datafile/o1_mf_system_ktbgb353_.dbf
input datafile file number=00007 name=/u01/app/oracle/oradata/DEVDB/datafile/users.dbf
channel ch2: starting piece 1 at 16-FEB-23
channel ch1: finished piece 1 at 16-FEB-23
piece handle=/u01/backup/DEVDB_LEVEL_0_DEVDB_20230216_8m1kje4e_278_1_1 tag=TAG20230216T004046 comment=NONE
channel ch1: backup set complete, elapsed time: 00:00:15
channel ch2: finished piece 1 at 16-FEB-23
piece handle=/u01/backup/DEVDB_LEVEL_0_DEVDB_20230216_8n1kje4e_279_1_1 tag=TAG20230216T004046 comment=NONE
channel ch2: backup set complete, elapsed time: 00:00:15
Finished backup at 16-FEB-23

Starting backup at 16-FEB-23
current log archived
channel ch1: starting archived log backup set
channel ch1: specifying archived log(s) in backup set
input archived log thread=1 sequence=83 RECID=417 STAMP=1128904861
channel ch1: starting piece 1 at 16-FEB-23
channel ch1: finished piece 1 at 16-FEB-23
piece handle=/u01/backup/DEVDB_Archivelog_DEVDB_20230216_8o1kje4t_280_1_1 tag=TAG20230216T004101 comment=NONE
channel ch1: backup set complete, elapsed time: 00:00:01
Finished backup at 16-FEB-23

Starting backup at 16-FEB-23
channel ch1: starting datafile copy
copying current control file
output file name=/u01/backup/Controlback_DEVDB_20230216_cf_D-DEVDB_id-1016989223_8p1kje4u tag=TAG20230216T004102 RECID=20 STAMP=1128904863
channel ch1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 16-FEB-23

Starting backup at 16-FEB-23
channel ch1: starting full datafile backup set
channel ch1: specifying datafile(s) in backup set
including current SPFILE in backup set
channel ch1: starting piece 1 at 16-FEB-23
channel ch1: finished piece 1 at 16-FEB-23
piece handle=/u01/backup/spfile_DEVDB_20230216_8q1kje50_282_1_1 tag=TAG20230216T004104 comment=NONE
channel ch1: backup set complete, elapsed time: 00:00:01
Finished backup at 16-FEB-23

Starting Control File and SPFILE Autobackup at 16-FEB-23
piece handle=/u01/app/oracle/fast_recovery_area/DEVDB/autobackup/2023_02_16/o1_mf_s_1128904865_kytcl9b3_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 16-FEB-23

released channel: ch1

released channel: ch2

RMAN>

Recovery Manager complete.
[oracle@oraclelab1 backup]$


Level_1

[oracle@oraclelab1 backup]$ more Level_1_16Feb20230042.log

Recovery Manager: Release 19.0.0.0.0 - Production on Thu Feb 16 00:42:06 2023
Version 19.17.0.0.0

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

connected to target database: DEVDB (DBID=1016989223)

RMAN> 2> 3> 4> 5> 6> 7> 8> 9> 10>
using target database control file instead of recovery catalog
allocated channel: ch1
channel ch1: SID=316 device type=DISK

allocated channel: ch2
channel ch2: SID=31 device type=DISK


Starting backup at 16-FEB-23
current log archived
channel ch1: starting archived log backup set
channel ch1: specifying archived log(s) in backup set
input archived log thread=1 sequence=72 RECID=406 STAMP=1128904041
channel ch1: starting piece 1 at 16-FEB-23
channel ch2: starting archived log backup set
channel ch2: specifying archived log(s) in backup set
input archived log thread=1 sequence=73 RECID=407 STAMP=1128904057
input archived log thread=1 sequence=74 RECID=408 STAMP=1128904220
input archived log thread=1 sequence=75 RECID=409 STAMP=1128904225
input archived log thread=1 sequence=76 RECID=410 STAMP=1128904389
input archived log thread=1 sequence=77 RECID=411 STAMP=1128904407
input archived log thread=1 sequence=78 RECID=412 STAMP=1128904453
input archived log thread=1 sequence=79 RECID=413 STAMP=1128904456
channel ch2: starting piece 1 at 16-FEB-23
channel ch1: finished piece 1 at 16-FEB-23
piece handle=/u01/backup/DEVDB_Archivelog_DEVDB_20230216_8s1kje70_284_1_1 tag=TAG20230216T004208 comment=NONE
channel ch1: backup set complete, elapsed time: 00:00:01
channel ch1: starting archived log backup set
channel ch1: specifying archived log(s) in backup set
input archived log thread=1 sequence=80 RECID=414 STAMP=1128904582
input archived log thread=1 sequence=81 RECID=415 STAMP=1128904599
input archived log thread=1 sequence=82 RECID=416 STAMP=1128904843
input archived log thread=1 sequence=83 RECID=417 STAMP=1128904861
input archived log thread=1 sequence=84 RECID=418 STAMP=1128904928
channel ch1: starting piece 1 at 16-FEB-23
channel ch2: finished piece 1 at 16-FEB-23
piece handle=/u01/backup/DEVDB_Archivelog_DEVDB_20230216_8t1kje70_285_1_1 tag=TAG20230216T004208 comment=NONE
channel ch2: backup set complete, elapsed time: 00:00:01
channel ch1: finished piece 1 at 16-FEB-23
piece handle=/u01/backup/DEVDB_Archivelog_DEVDB_20230216_8u1kje71_286_1_1 tag=TAG20230216T004208 comment=NONE
channel ch1: backup set complete, elapsed time: 00:00:01
Finished backup at 16-FEB-23

Starting backup at 16-FEB-23
channel ch1: starting incremental level 1 datafile backup set
channel ch1: specifying datafile(s) in backup set
input datafile file number=00003 name=/u01/app/oracle/oradata/DEVDB/datafile/o1_mf_sysaux_ktbgb34z_.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/DEVDB/datafile/o1_mf_undotbs1_ktbgb356_.dbf
channel ch1: starting piece 1 at 16-FEB-23
channel ch2: starting incremental level 1 datafile backup set
channel ch2: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/oradata/DEVDB/datafile/o1_mf_system_ktbgb353_.dbf
input datafile file number=00007 name=/u01/app/oracle/oradata/DEVDB/datafile/users.dbf
channel ch2: starting piece 1 at 16-FEB-23
channel ch1: finished piece 1 at 16-FEB-23
piece handle=/u01/backup/DEVDB_LEVEL_1_DEVDB_20230216_8v1kje72_287_1_1 tag=TAG20230216T004210 comment=NONE
channel ch1: backup set complete, elapsed time: 00:00:01
channel ch2: finished piece 1 at 16-FEB-23
piece handle=/u01/backup/DEVDB_LEVEL_1_DEVDB_20230216_901kje72_288_1_1 tag=TAG20230216T004210 comment=NONE
channel ch2: backup set complete, elapsed time: 00:00:01
Finished backup at 16-FEB-23

Starting backup at 16-FEB-23
current log archived
channel ch1: starting archived log backup set
channel ch1: specifying archived log(s) in backup set
input archived log thread=1 sequence=85 RECID=419 STAMP=1128904931
channel ch1: starting piece 1 at 16-FEB-23
channel ch1: finished piece 1 at 16-FEB-23
piece handle=/u01/backup/DEVDB_Archivelog_DEVDB_20230216_911kje73_289_1_1 tag=TAG20230216T004211 comment=NONE
channel ch1: backup set complete, elapsed time: 00:00:01
Finished backup at 16-FEB-23

Starting backup at 16-FEB-23
channel ch1: starting datafile copy
copying current control file
output file name=/u01/backup/Controlback_DEVDB_20230216_cf_D-DEVDB_id-1016989223_921kje75 tag=TAG20230216T004212 RECID=21 STAMP=1128904933
channel ch1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 16-FEB-23

Starting backup at 16-FEB-23
channel ch1: starting full datafile backup set
channel ch1: specifying datafile(s) in backup set
including current SPFILE in backup set
channel ch1: starting piece 1 at 16-FEB-23
channel ch1: finished piece 1 at 16-FEB-23
piece handle=/u01/backup/spfile_DEVDB_20230216_931kje76_291_1_1 tag=TAG20230216T004214 comment=NONE
channel ch1: backup set complete, elapsed time: 00:00:01
Finished backup at 16-FEB-23

Starting Control File and SPFILE Autobackup at 16-FEB-23
piece handle=/u01/app/oracle/fast_recovery_area/DEVDB/autobackup/2023_02_16/o1_mf_s_1128904935_kytcnhdt_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 16-FEB-23

released channel: ch1

released channel: ch2

RMAN>

Recovery Manager complete.
[oracle@oraclelab1 backup]$ 

Regards,
Mallik
Posted by at No comments:
Subscribe to: Posts (Atom)

Automation Script | Archivelog Generation Hourly Monitoring

1. List out all the running databases and pic one database where we want to monitore the archive log generation from last 1 month. [oracle@o...