Create Password file for database in ASM Diskgroup - Oracle 12c RAC only

-- Verify Password file exists or not

$ crsctl stat res ora.HCMPRD.db -f | grep PWFILE
PWFILE=

-- Create password file in asm

orapwd file='+DATA/HCMPRD/orapwHCMPRD' dbuniquename='HCMPRD'

-- Now verify password file created or not

$ crsctl stat res ora.HCMPRD.db -f | grep PWFILE
PWFILE=+DATA/HCMPRD/orapwHCMPRD

-- you can see also in ASM_CLIENT

ASMCMD [+DATA/HCMPRD/PASSWORD] > ls
pwdHCMPRD.4594.1016696757
pwdHCMPRD.493.1016696613

-- you can find from asm diskgroup

$ asmcmd
ASMCMD> pwget --dbuniquename HCMPRD
+DATA/HCMPRD/orapwHCMPRD
ASMCMD>


-- Alternative method to create password file 12c asm

This method is valid only for oracle 12c onwards.

SYNTAX –
ASMCMD> pwcreate –dbuniquename {db_unique_name} {file_path}  {sys_password}

Example:

Here we will create a password file with dbuniquename HCMPRD.

ASMCMD> pwcreate --dbuniquename HCMPRD +DATA/PWDFILE/pwdHCMPRD oracle


-- You can check using srvctl also:

$ srvctl config database -d HCMPRD
Database unique name: HCMPRD
Database name:
Oracle home: /u01/app/oracle/product/12.1.0.2/dbhome_1
Oracle user: oracle
Spfile: +DATA/HCMPRD/PARAMETERFILE/spfile.2557.2026287697
Password file: +DATA/HCMPRD/orapwHCMPRD
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools:
Disk Groups: DATA
Mount point paths:
Services:
Type: RAC
Start concurrency:
Stop concurrency:
OSDBA group: dba
OSOPER group: dba
Database instances: HCMPRD1,HCMPRD2
Configured nodes: example1a,example1b
Database is administrator managed
$

Restore multi-million rows from existing table via pl/sql block

There is request to copy multi million rows from one archive table to original table with downtime.
I tried with export and import to copy fast but not succeeded even after many hours.

So I tried with below approach and it completed fast. This pl/sql block copy 10 thousand rows at a time and commit. So that we can avoid huge rollback segment and huge switch logs etc.

-- Here is the Pl/SQL block

declare 
cursor C1 is
select id,comment_text,comment_by,comment_date,cell_number,language
from scott.big_table_archive;
begin
for r1 in C1
loop
insert into scott.big_table values (
r1.id,
r1.comment_text,
r1.comment_by,
r1.comment_date,
r1.cell_number,
r1.language);
if (c1%rowcount mod 10000=0) then
commit;
end if;
end loop;
commit;
end;
/

-- if you want to add existing records again to original table, then you need to use merge command. If any PK constraints are there and updated records are there, then below pl/sql block will help you:

MERGE INTO scott.big_table a
USIING 
   (SELECT * FROM scott.big_table_archive)  b
ON (b.id=a._id and b.comment_text=a.comment_text)  /* PK */
WHEN MATCHED THEN
UPDATE
    SET a.comment_by=b.comment_by,
        a.comment_date=b.comment_date,
        a.Tcell_number=b.cell_number,
        a.language=b.language
WHEN NOT MATCHED THEN
INSERT  
    VALUES 
       (r1.ID,
        b.comment_text,
        b.comment_by,
        b.comment_date,
        b.cell_number,
        b.language
 );

Try this and post your feedback.