Como este é apenas um ambiente de laboratório de estudos, vou criar apenas um diretório na máquina que vai abrigar todos os arquivos ligados ao novo banco de dados:
[oracle@oel7 restore]$ mkdir BSS
[oracle@oel7 restore]$ chmod 775 BSS/
[oracle@oel7 restore]$ cd BSS/
[oracle@oel7 BSS]$ pwd
/restore/BSS
Agora, o ideal é setar todas as variáveis de ambientes necessárias. Farei isso direto no bash_profile do usuário oracle. Lembrando que estou utilizando um binário já instalado do Oracle 12C:
[oracle@oel7 binarios]$ cd
[oracle@oel7 ~]$ vi .bash_profile
# .bash_profile
# Get the aliases and functions
if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi
# User specific environment and startup programs
PATH=$PATH:$HOME/bin
export PATH
ORACLE_SID=BSS
ORACLE_BASE=/restore/binarios/
ORACLE_HOME=/restore/binarios/12.2.0.1
LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib
PATH=$ORACLE_HOME/bin:$PATH
NLS_LANG=American_America.WE8MSWIN1252
NLS_NUMERIC_CHARACTERS=.,
NLS_SORT=binary
NLS_DATE_FORMAT='DD-MM-YYYY HH24:MI:SS'
export ORACLE_SID ORACLE_BASE ORACLE_HOME LD_LIBRARY_PATH PATH NLS_LANG NLS_NUMERIC_CHARACTERS NLS_SORT NLS_DATE_FORMAT
Carregando o bash_profile:
[oracle@oel7 ~]$ . .bash_profile
[oracle@oel7 ~]$ echo $ORACLE_SID; echo $ORACLE_HOME; echo $ORACLE_BASE
BSS
/restore/binarios/12.2.0.1
/restore/binarios/
[oracle@oel7 ~]$
A próxima etapa é escolher qual tipo de autenticação será utilizada (por um password file ou via sistema operacional). Eu opto pela segunda.
Depois, precisamos popular um arquivo pfile com parâmetros mínimos, como no exemplo abaixo:
[oracle@oel7 BSS]$ cd $ORACLE_HOME/dbs
[oracle@oel7 dbs]$ vi init.ora
db_name='BSS'
memory_target=2G
processes = 150
db_block_size=8192
db_recovery_file_dest='/restore/BSS'
db_recovery_file_dest_size=2G
diagnostic_dest='/restore/BSS'
dispatchers='(PROTOCOL=TCP) (SERVICE=ORCLXDB)'
open_cursors=300
remote_login_passwordfile='EXCLUSIVE'
undo_tablespace='UNDOTBS1'
control_files = ('/restore/BSS/controlfile.ctl1', '/restore/BSS/controlfile.ctl2')
compatible ='12.0.0'
Agora, podemos criar um SPFILE a partir do PFILE:
[oracle@oel7 dbs]$ sqlplus / as sysdba
SQL*Plus: Release 12.2.0.1.0 Production on Thu Jan 28 15:23:12 2021
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to an idle instance.
SQL> CREATE SPFILE FROM PFILE='/restore/binarios/12.2.0.1/dbs/init.ora';
File created.
SQL>
[oracle@oel7 dbs]$ ls -lthr spfileBSS.ora
-rw-r----- 1 oracle oinstall 1.5K Jan 28 15:23 spfileBSS.ora
[oracle@oel7 dbs]$
Inicializando a instância:
[oracle@oel7 dbs]$ sqlplus / as sysdba
SQL*Plus: Release 12.2.0.1.0 Production on Thu Jan 28 15:26:28 2021
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 2147483648 bytes
Fixed Size 8622776 bytes
Variable Size 1291849032 bytes
Database Buffers 838860800 bytes
Redo Buffers 8151040 bytes
SQL> !ps -ef | grep pmon | grep BSS
oracle 7040 1 0 15:26 ? 00:00:00 ora_pmon_BSS
oracle 7143 6068 0 15:26 pts/2 00:00:00 /bin/bash -c ps -ef | grep pmon | grep BSS
SQL>
Agora basta criar o banco de dados com o comando do exemplo abaixo:
CREATE DATABASE BSS
USER SYS IDENTIFIED BY BSS
USER SYSTEM IDENTIFIED BY BSS
LOGFILE GROUP 1 ('/restore/BSS/redo01a.log','/restore/BSS/redo01b.log') SIZE 100M BLOCKSIZE 512,
GROUP 2 ('/restore/BSS/redo02a.log','/restore/BSS/redo02b.log') SIZE 100M BLOCKSIZE 512,
GROUP 3 ('/restore/BSS/redo03a.log','/restore/BSSredo03b.log') SIZE 100M BLOCKSIZE 512
MAXLOGHISTORY 1
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 1024
CHARACTER SET AL32UTF8
NATIONAL CHARACTER SET AL16UTF16
EXTENT MANAGEMENT LOCAL
DATAFILE '/restore/BSS/system01.dbf'
SIZE 700M REUSE AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED
SYSAUX DATAFILE '/restore/BSS/sysaux01.dbf'
SIZE 550M REUSE AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED
DEFAULT TABLESPACE users
DATAFILE '/restore/BSS/users01.dbf'
SIZE 500M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED
DEFAULT TEMPORARY TABLESPACE tempts1
TEMPFILE '/restore/BSS/temp01.dbf'
SIZE 20M REUSE AUTOEXTEND ON NEXT 640K MAXSIZE UNLIMITED
UNDO TABLESPACE undotbs1
DATAFILE '/restore/BSS/undotbs01.dbf'
SIZE 200M REUSE AUTOEXTEND ON NEXT 5120K MAXSIZE UNLIMITED;
Resultado:
[oracle@oel7 BSS]$ sqlplus / as sysdba
SQL*Plus: Release 12.2.0.1.0 Production on Thu Jan 28 15:30:29 2021
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
SQL> CREATE DATABASE BSS
2 USER SYS IDENTIFIED BY BSS
3 USER SYSTEM IDENTIFIED BY BSS
4 LOGFILE GROUP 1 ('/restore/BSS/redo01a.log','/restore/BSS/redo01b.log') SIZE 100M BLOCKSIZE 512,
5 GROUP 2 ('/restore/BSS/redo02a.log','/restore/BSS/redo02b.log') SIZE 100M BLOCKSIZE 512,
6 GROUP 3 ('/restore/BSS/redo03a.log','/restore/BSSredo03b.log') SIZE 100M BLOCKSIZE 512
MAXLOGHISTORY 1
7 8 MAXLOGFILES 16
9 MAXLOGMEMBERS 3
MAXDATAFILES 1024
10 11 CHARACTER SET AL32UTF8
12 NATIONAL CHARACTER SET AL16UTF16
EXTENT MANAGEMENT LOCAL
13 14 DATAFILE '/restore/BSS/system01.dbf'
SIZE 700M REUSE AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED
15 16 SYSAUX DATAFILE '/restore/BSS/sysaux01.dbf'
17 SIZE 550M REUSE AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED
DEFAULT TABLESPACE users
18 19 DATAFILE '/restore/BSS/users01.dbf'
20 SIZE 500M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED
21 DEFAULT TEMPORARY TABLESPACE tempts1
22 TEMPFILE '/restore/BSS/temp01.dbf'
SIZE 20M REUSE AUTOEXTEND ON NEXT 640K MAXSIZE UNLIMITED
23 24 UNDO TABLESPACE undotbs1
25 DATAFILE '/restore/BSS/undotbs01.dbf'
26 SIZE 200M REUSE AUTOEXTEND ON NEXT 5120K MAXSIZE UNLIMITED;
Database created.
SQL>
Agora precisamos rodar os scripts que constroem o dicionário de dados do banco:
[oracle@oel7 BSS]$ cd $ORACLE_HOME/rdbms/admin/
[oracle@oel7 BSS]$ sqlplus / as sysdba
SQL*Plus: Release 12.2.0.1.0 Production on Thu Jan 28 15:30:29 2021
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
SQL> @catalog.sql
[...]
PL/SQL procedure successfully completed.
TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP CATALOG 2021-01-28 15:37:20
Session altered.
Session altered.
SQL>
SQL> @catproc.sql
[...]
SQL> Rem *********************************************************************
SQL> Rem END catproc.sql
SQL> Rem *********************************************************************
SQL>
Recompilando os objetos:
SQL> @utlrp.sql
[...]
SQL> Rem ===========================================================================
SQL> Rem END utlrp.sql
SQL> Rem ===========================================================================
SQL>
[oracle@oel7 admin]$ cd $ORACLE_HOME/sqlplus/admin
[oracle@oel7 admin]$ sqlplus SYSTEM as sysdba
SQL*Plus: Release 12.2.0.1.0 Production on Thu Jan 28 16:01:07 2021
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Enter password:
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
SQL> @pupbld.sql
Session altered.
DROP SYNONYM PRODUCT_USER_PROFILE
*
ERROR at line 1:
ORA-01434: private synonym to be dropped does not exist
PL/SQL procedure successfully completed.
DROP VIEW PRODUCT_PRIVS
*
ERROR at line 1:
ORA-00942: table or view does not exist
View created.
Grant succeeded.
DROP PUBLIC SYNONYM PRODUCT_PROFILE
*
ERROR at line 1:
ORA-01432: public synonym to be dropped does not exist
Synonym created.
DROP SYNONYM PRODUCT_USER_PROFILE
*
ERROR at line 1:
ORA-01434: private synonym to be dropped does not exist
Synonym created.
DROP PUBLIC SYNONYM PRODUCT_USER_PROFILE
*
ERROR at line 1:
ORA-01432: public synonym to be dropped does not exist
Synonym created.
Session altered.
SQL>
Validando o banco de dados:
[oracle@oel7 admin]$ sqlplus / as sysdba
SQL*Plus: Release 12.2.0.1.0 Production on Thu Jan 28 16:02:19 2021
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
SQL> select instance_name,status from gv$instance;
INSTANCE_NAME STATUS
---------------- ------------
BSS OPEN
SQL> ALTER SYSTEM CHECKPOINT;
System altered.
SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
[oracle@oel7 admin]$ rman target /
Recovery Manager: Release 12.2.0.1.0 - Production on Thu Jan 28 16:02:52 2021
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
connected to target database: BSS (DBID=64177763)
RMAN> report schema;
using target database control file instead of recovery catalog
Report of database schema for database with db_unique_name BSS
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 700 SYSTEM YES /restore/BSS/system01.dbf
2 550 SYSAUX NO /restore/BSS/sysaux01.dbf
3 260 UNDOTBS1 YES /restore/BSS/undotbs01.dbf
4 500 USERS NO /restore/BSS/users01.dbf
List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 20 TEMPTS1 32767 /restore/BSS/temp01.dbf
RMAN>
Portuguese
English
Spanish