Check The status of the cold backup before Perform Clone by creating control file method

=================================================================

select hxfil FILENUMBER, fhsta STATUS, fhscn SCN, fhrba_seq SEQUENCE,fhafs,FHDBI,fhdbn from x$kcvfh order by fhsta;

FILENUMBER STATUS SCN SEQUENCE FHAFS FHDBI FHDBN
---------- ---------- ---------------- ---------- ---------------- ---------- ---------
6 4 2401335881 1 0 3300273167 PROD
2 4 2401335881 1 0 3300273167 PROD
3 4 2401335881 1 0 3300273167 PROD
4 4 2401335881 1 0 3300273167 PROD
5 4 2401335881 1 0 3300273167 PROD
11 4 2401335881 1 0 3300273167 PROD
7 4 2401335881 1 0 3300273167 PROD
8 4 2401335881 1 0 3300273167 PROD
9 4 2401335881 1 0 3300273167 PROD
10 4 2401335881 1 0 3300273167 PROD
1 8196 2401335881 1 0 3300273167 PROD

11 rows selected.
================================================================================================================================================================

See the status column, its 8194 (for system datafile) and 4 for non system datafiles,
the value 8196 and 4 denoted, online fuzzy, means that the database was opened, when the datafiles were copied,
if it was a cold backup, in that case the status column must be 8192 and 0 (Zero)

Startup Fails With ORA-01012: Not Logged On

Problem Description
Users cant able to login to database server. Though it allow sysdba to login, it does not allow to run any query. Sometimes it will show database is connected to an idle instance. But when we startup database it shall throw error ORA-01081: cannot start already-running .

[oracle@mezon01 ~]$ sqlplus sys/System_2018@IIRDB

SQL*Plus: Release 11.2.0.4.0 Production on Thu May 24 08:03:07 2018

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

ERROR:
ORA-12520: TNS:listener could not find available handler for requested type of
server

[oracle@mezon01 ~]$ sqlplus -prelim “/as sysdba”

SQL*Plus: Release 11.2.0.4.0 Production on Thu May 24 08:04:06 2018

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

SQL> show parameter sga_t;
ORA-01012: not logged on
Process ID: 0
Session ID: 0 Serial number: 0

 

SQL> startup;
ORA-01012: not logged on
SQL> shutdown immediate;
ORA-01012: not logged on

[oracle@mezon01 ~]$
Changes
Oracle has been forcefully shutdown at OS level or crashed.

CAUSE
An orphaned shared memory segment belonging to the ORACLE_SID still exists from a previous instance startup.

The command
ps -ef | grep $ORACLE_SID

shows no processes but with ORACLE_SID set the Oracle ‘sysresv’ utility shows a shared memory segment for a non-existing instance, e.g.

$ sysresv

IPC Resources for ORACLE_SID “IIRDB” :

Shared Memory:
ID              KEY
100270098 0x00000000
100302867 0x00000000
100335643 0x14203f64

Semaphores:
ID              KEY
No semaphore resources used
Oracle Instance not alive for sid “IIRDB”

Solution
On OS level, remove the orphaned shared memory segment using:

ipcrm -m <problem shared memory id>

ipcrm -m 100270098
ipcrm -m 100302867
ipcrm -m 100335643

$sqlplus ‘/as sysdba’

Connected to an idle instance.

SQL> startup Oracle instance started
Total System Global Area   10689474560 bytes
Fixed Size                     2237776 bytes
Variable Size               6375344816 bytes
Database Buffers            4294967296 bytes
Redo Buffers                  16924672 bytes
Oracle Instance Started.
Oracle Database Opened.

 

Deleting the files between particular dates

Code:

find ./ -mtime +30 -type f -print

 

The above command will show all files modified 30 days ago or more - use the print statement first to be sure that you find the correct files and do check it's what you want. Then you can run the command again with the delete function added like this;

Code:

find ./ -mtime +30 -type f -exec rm {} \;

 

Oracle Data Pump (expdp, impdp) in Oracle Database 10g, 11g, 12c, 18c, 19c

https://oracle-base.com/articles/10g/oracle-data-pump-10g

# su - oracle

# .oraenv  (select prod)

# sqlplus / as sysdba

SQL> CREATE OR REPLACE DIRECTORY exp_db AS '/u02/db_export';

SQL> GRANT READ, WRITE ON DIRECTORY exp_db TO system;

# expdp system/manager@prod full=Y directory=exp_db dumpfile=prod.dmp logfile=expPROD.log

 

Difference between RESETLOGS and NORESETLOGS

Use RESETLOGS after incomplete recovery (when the entire redo stream wasn't applied). RESETLOGS will initialize the logs, reset your log sequence number, and start a new "incarnation" of the database.

Use NORESETLOGS when doing complete recovery (when the entire redo stream was applied). Oracle will continue using the existing (valid) log files.

NORESETLOGS

The NORESETLOGS option does not clear the redo log files during startup and the online redo logs to be used for recovery. Only used in scenario where MANUAL RECOVERY is started, CANCEL is used, and then RECOVER DATABASE is started.

RESETLOGS

CAUTION: Never use RESETLOGS unless necessary.

Once RESETLOGS is used then the redo log files cannot be used and any completed transactions in those redo logs are lost!!

Before using the RESETLOGS option take an offline backup of the database.

The RESETLOGS option clears all the online redo logs and modifies all the online data files to indicate no recovery is needed. After resetting the redo logs none of the existing log files or data file backups can be used. In the control file, the log sequence number is modified, which is very important for recovery purposes. The recovery will be applied only to the log files whose sequence number is greater than log sequence number in the control file. One has to be very cautious when using RESETLOGS option. It is important to remember that all datafiles must be online otherwise they will become useless once the database is up

Clone Pluggable database

==========================

SQL> select name,open_mode from v$pdbs;

NAME        OPEN_MODE

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

PDB$SEED        READ ONLY

MIGPDB         READ WRITE

SQL> alter session set container=MIGPDB;

Session altered.

SQL> select file_name from dba_data_files;

SQL> select file_name from dba_data_files;

FILE_NAME

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

/u02/oradata/MIGCDB/AFF38B6D8283019AE053AC10028C9AE1/datafile/o1_mf_system_hpojb1ry_.dbf

/u02/oradata/MIGCDB/AFF38B6D8283019AE053AC10028C9AE1/datafile/o1_mf_sysaux_hpojb1s2_.dbf

/u02/oradata/MIGCDB/AFF38B6D8283019AE053AC10028C9AE1/datafile/o1_mf_undotbs1_hpojb1s2_.dbf  etc...

SQL> show con_name

CON_NAME

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

MIGPDB

SQL> shutdown immediate;

Pluggable Database closed.

 

SQL> startup open read only

Pluggable Database opened.

 

# mkdir /u02/oradata/MIGCDB/demopdb

SQL > alter session set container=cdb$root;

SQL > show con_name

CON_NAME

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

CDB$ROOT

SQL> alter system set db_create_file_dest='/u02/oradata/MIGCDB/demopdb';

SQL> create pluggable database DEMOPDB from MIGPDB FILE_NAME_CONVERT=('/u02/oradata/MIGCDB/AFF38B6D8283019AE053AC10028C9AE1/datafile/','/u02/oradata/MIGCDB/demopdb');

 

SQL> select name,open_mode from v$pdbs;

 

NAME        OPEN_MODE

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

PDB$SEED          READ ONLY

MIGPDB              READ ONLY

DEMOPDB          MOUNTED

 

SQL> alter session set CONTAINER=MIGPDB;

 

Session altered.

 

SQL> shutdown immediate;

Pluggable Database closed.

SQL> startup

Pluggable Database opened.

SQL> show con_name

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

SQL> alter session set container=DEMOPDB;

 

Session altered.

 

SQL> startup

Pluggable Database opened.

  SQL> alter session set container=cdb$root;

Connected.

SQL> select name,open_mode from v$pdbs;

 

NAME        OPEN_MODE

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

PDB$SEED        READ ONLY

MIGPDB             READ WRITE

DEMOPDB         READ WRITE

 

Multitenant : Clone a Remote PDB

https://oracle-base.com/articles/12c/multitenant-clone-remote-pdb-or-non-cdb-12cr1#prerequisites

Connect to the remote CDB and prepare the remote PDB for cloning.

export ORAENV_ASK=NO

export ORACLE_SID=cdb3

. oraenv

export ORAENV_ASK=YES

 

sqlplus / as sysdba

Create a user in the remote database for use with the database link. In this case, we will use a local user in the remote PDB.

ALTER SESSION SET CONTAINER=pdb5;

 

CREATE USER remote_clone_user IDENTIFIED BY remote_clone_user;

GRANT CREATE SESSION, CREATE PLUGGABLE DATABASE TO remote_clone_user;

Open the remote PDB in read-only mode.

CONN / AS SYSDBA

ALTER PLUGGABLE DATABASE pdb5 CLOSE;

ALTER PLUGGABLE DATABASE pdb5 OPEN READ ONLY;

EXIT;

Switch to the local server and create a "tnsnames.ora" entry pointing to the remote database for use in the USING clause of the database link.

PDB5 =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCP)(HOST = ol7-121.localdomain)(PORT = 1521))

    )

    (CONNECT_DATA =

      (SERVICE_NAME = pdb5)

    )

  )

Connect to the local database to initiate the clone.

export ORAENV_ASK=NO

export ORACLE_SID=cdb1

. oraenv

export ORAENV_ASK=YES

 

sqlplus / as sysdba

Create a database link in the local database, pointing to the remote database.

DROP DATABASE LINK clone_link;

 

CREATE DATABASE LINK clone_link

  CONNECT TO remote_clone_user IDENTIFIED BY remote_clone_user USING 'pdb5';

 

-- Test link.

DESC user_tables@clone_link

Create a new PDB in the local database by cloning the remote PDB. In this case we are using Oracle Managed Files (OMF), so we don't need to bother with FILE_NAME_CONVERT parameter for file name conversions.

CREATE PLUGGABLE DATABASE pdb5new FROM pdb5@clone_link;

 

Pluggable database created.

 

SQL>

We can see the new PDB has been created, but it is in the MOUNTED state.

SELECT name, open_mode FROM v$pdbs WHERE name = 'PDB5NEW';

 

NAME                           OPEN_MODE

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

PDB5NEW                        MOUNTED

 

SQL>

The PDB is opened in read-write mode to complete the process.

ALTER PLUGGABLE DATABASE pdb5new OPEN;

 

SELECT name, open_mode FROM v$pdbs WHERE name = 'PDB5NEW';

 

NAME                           OPEN_MODE

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

PDB5NEW                        READ WRITE

 

SQL>