Blog / Database Blog

CREATING A DATABASE USING REMOTE DATABASE FILES

Shankar Narayanan 4 min read

In this Blog we are going to learn about how create the database in local server by using another database files from remote server. For this blog i have used my oracle 12c database.

Before started to create we should get the database files from remote server by using SCP command.

PRE CREATION OF DATABASE:

Required database files are PARAMETER FILE(PFILE),DATAFILE, CONTROL FILE, REDOLOG FILE. Then we should create a path location for that remote files, its should be same as a remote path location for database files. Else database cannot identify the files.

Step 1: Startup the database with remote database pfile:

[oracle@oracle ~]$ export ORACLE_SID=gdb1

[oracle@oracle ~]$ export ORACLE_HOME=/u01/app/oracle/product/12.2.0.1/db_1

[oracle@oracle ~]$ export PATH=$PATH:$ORACLE_HOME/bin

[oracle@oracle ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Sat Jan 23 11:18:36 2021

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

Connected to an idle instance.

SQL> startup

ORACLE instance started.

Total System Global Area  524288000 bytes

Fixed Size     8794696 bytes

Variable Size   381685176 bytes

Database Buffers   130023424 bytes

Redo Buffers     3784704 bytes

ORA-65101: container database set up incorrectly

SQL> alter system set ENABLE_PLUGGABLE_DATABASE=FALSE Scope=spfile;

alter system set ENABLE_PLUGGABLE_DATABASE=FALSE Scope=spfile

*

ERROR at line 1:

ORA-32001: write to SPFILE requested but no SPFILE is in use

Note: Above error is came because of database is running in pfile, there is no spfile for this database so we can’t change anything using command like “scope=spfile”.

For that we creating a spfile from pfile using below command:

SQL> create spfile from pfile;

File created.

SQL> shut abort

ORACLE instance shut down.

SQL>  startup nomount

ORACLE instance started.

Total System Global Area  524288000 bytes

Fixed Size     8794696 bytes

Variable Size   381685176 bytes

Database Buffers   130023424 bytes

Redo Buffers     3784704 bytes

SQL> alter system set ENABLE_PLUGGABLE_DATABASE=FALSE Scope=spfile;

System altered.

SQL> shut abort

ORACLE instance shut down.

SQL> startup mount

ORACLE instance started.

Total System Global Area  524288000 bytes

Fixed Size     8622624 bytes

Variable Size   377490912 bytes

Database Buffers   134217728 bytes

Redo Buffers     3956736 bytes

Database mounted.

Step 2: Now we should take a backup of control file by using below command:

SQL> alterdatabase backup controlfile to trace  as ‘/u01/app/oracle/diag/rdbms/man4/man4/trace/ctl1.sql’;

Database altered.

SQL> shut abort

ORACLE instance shut down.

SQL>

Step 3: Next bounce the database and recreate the control file from copy of backup control file: 

SQL> startup nomount

ORACLE instance started.

Total System Global Area  524288000 bytes

Fixed Size     8622624 bytes

Variable Size   377490912 bytes

Database Buffers   134217728 bytes

Redo Buffers     3956736 bytes

SQL> CREATE CONTROLFILE REUSE DATABASE “NDB1” NORESETLOGS  ARCHIVELOG

MAXLOGFILES 5

MAXLOGMEMBERS 5

MAXDATAFILES 100

MAXINSTANCES 1

MAXLOGHISTORY 292

LOGFILE

GROUP 1 ‘/u01/ndb1/files/redo/redo01.log’  SIZE 100M BLOCKSIZE 512,

GROUP 2 ‘/u01/ndb1/files/redo/redo02.log’  SIZE 100M BLOCKSIZE 512,

GROUP 3 ‘/u01/ndb1/files/redo/redo03.log’  SIZE 100M BLOCKSIZE 512

— STANDBY LOGFILE

DATAFILE

‘/u01/ndb1/files/data/system01.dbf’,

‘/u01/ndb1/files/data/sysaux01.dbf’,

‘/u01/ndb1/files/data/undotbs01.dbf’,

‘/u01/ndb1/files/data/users01.dbf’,

‘/u01/ndb1/files/data/system02.dbf’,

‘/u01/app/oracle/product/12.2.0.1/db_1/dbs/tbs1.dbf’,

‘/u01/app/oracle/product/12.2.0.1/db_1/dbs/tbs2.dbf’,

‘/u01/app/oracle/product/12.2.0.1/db_1/dbs/test03.dbf’,

‘/u01/ndb1/files/data/TESTTBS01.dbf’

CHARACTER SET US7ASCII

;  2    3    4    5    6    7    8    9   10   11   12   13   14   15   16   17   18   19   20   21   22   23

Control file created.

Step 4: After successfully recreate the controfile the now we should do the MEDIA RECOVERY FOR BACKUP CONTROLFILE:

SQL> recover database using backup controlfile;

ORA-00279: change 8423555 generated at 09/07/2020 09:16:52 needed for thread 1

ORA-00289: suggestion :

/u01/app/oracle/product/12.2.0.1/db_1/dbs/arch1_2_1027778234.dbf

ORA-00280: change 8423555 for thread 1 is in sequence #2

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

/u01/ndb1/files/redo/redo01.log

ORA-00310: archived log contains sequence 1; sequence 2 required

ORA-00334: archived log: ‘/u01/ndb1/files/redo/redo01.log’

SQL> recover database using backup controlfile;

ORA-00279: change 8423555 generated at 09/07/2020 09:16:52 needed for thread 1

ORA-00289: suggestion :

/u01/app/oracle/product/12.2.0.1/db_1/dbs/arch1_2_1027778234.dbf

ORA-00280: change 8423555 for thread 1 is in sequence #2

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

/u01/ndb1/files/redo/redo03.log

ORA-00339: archived log does not contain any redo

ORA-00334: archived log: ‘/u01/ndb1/files/redo/redo03.log’

SQL> recover database using backup controlfile;

ORA-00279: change 8423555 generated at 09/07/2020 09:16:52 needed for thread 1

ORA-00289: suggestion :

/u01/app/oracle/product/12.2.0.1/db_1/dbs/arch1_2_1027778234.dbf

ORA-00280: change 8423555 for thread 1 is in sequence #2

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

/u01/ndb1/files/redo/redo02.log

Log applied.

Media recovery complete.

Step 5: Once media recovery completes we should open the database by using resetlogs.

When the database restarting after the recovery process at the time every database sholud open as a resetlogs command.

SQL> alter database open resetlogs;

Database altered.

SQL> select name,open_mode from v$database;

NAME   OPEN_MODE

——— ——————–

NDB1  READ WRITE

Step 6: POST CREATION

In this stage we should verify the all tablespace, datafiles and tables of remote database is there in local server.

SQL> show user

USER is “SYS”

SQL> select table_name,tablespace_name from dba_tables where tablespace_name=’TESTTBS’;

no rows selected

SQL> select tablespace_name,file_name from dba_data_files;

TABLESPACE_NAME

——————————

FILE_NAME

——————————————————————————–

SYSTEM

/u01/ndb1/files/data/system01.dbf

SYSTEM

/u01/ndb1/files/data/system02.dbf

SYSTEM

/u01/app/oracle/product/12.2.0.1/db_1/dbs/test03.dbf

TABLESPACE_NAME

——————————

FILE_NAME

——————————————————————————–

SYSAUX

/u01/ndb1/files/data/sysaux01.dbf

UNDOTBS

/u01/ndb1/files/data/undotbs01.dbf

USERS

/u01/ndb1/files/data/users01.dbf

TABLESPACE_NAME

——————————

FILE_NAME

——————————————————————————–

TBS1

/u01/app/oracle/product/12.2.0.1/db_1/dbs/tbs1.dbf

TBS2

/u01/app/oracle/product/12.2.0.1/db_1/dbs/tbs2.dbf

TBS3

/u01/app/oracle/product/12.2.0.1/db_1/dbs/MISSING00008

TABLESPACE_NAME

——————————

FILE_NAME

——————————————————————————–

TBS0

/u01/app/oracle/product/12.2.0.1/db_1/dbs/MISSING00010

TBS0

/u01/app/oracle/product/12.2.0.1/db_1/dbs/MISSING00011

TESTTBS

/u01/ndb1/files/data/TESTTBS01.dbf

12 rows selected.

SQL>  select table_name,tablespace_name from dba_tables where tablespace_name=’TESTTBS’ and table_name=’PROS’;

no rows selected

SQL>

Now that we have successfully create a database using remote database files. Then we have done the verification on local server all the tablespace with that datafile and tables are created.

Related blogs

Database Blog

Query to Identify Initial and Latest Login Sessions in Oracle Database

Please use the query below to identify and provide the SID and Serial#. &nbsp; col first_seen for a30 col last_seen for a30 SELECT MIN(sample_time) AS first_see…

Venkatesh GK 1 min read
Database Blog

Steps to Renew Default OHS Keystore Wallet in Oracle Enterprise Manager (OEM)

Please follow the below steps. &nbsp; Prerequisites &nbsp; Take a backup of the existing wallet. Ensure you have appropriate Oracle user privileges. Verify OMS…

Venkatesh GK 1 min read
Database Blog

How to Unlock Locked SQL Server Logins

How to Unlock Locked SQL Server Logins Introduction / Issue During day-to-day SQL Server administration, users may report that they are unable to connect to the…

SaiKumar 3 min read